Retrieving non-existent data for a global view of reality

When reviewing sales data, sometimes there is a need to show not only the data that exists, but also the data that does not exist. For instance, in the tables below we can see that no revenue was generated in the month of April and also that no revenue was generated from customer JKL between January and June.



Imagine that you have the following star schema: the classic time dimension with all the dates, another dimension with all the products that your company sells, and a fact table containing the sales transactions with its associated revenue.

The challenge is to build a report showing the revenue for each product and date during a given period of time. This report must also show days without sales and also the products that did not sell during this period.
Unfortunately, a query based on the above star schema is going to retrieve data only for the combinations of products and dates when sales were achieved. This is illustrated in the following table:

This article will show you how to retrieve the missing data to gain a global view of reality...
We will start with a report showing the revenue by product and date in the period between January 1 and January 15, 2001:

Figure 1: Sales from 01/01/2001 to 15/01/2001

Despite this being valuable information, the requirement is to show this information for all days and all products. Fortunately, Oracle Business Intelligence gives us the tools to retrieve this missing data. Let’s see how...
In order to accomplish our objective we have to create a new physical fact table. This table will contain a dummy column with non meaningful data but it is important that it is a numeric data. The purpose of this fact table is to allow us to do a drill across different fact tables (the original one, containing the transactional data; and the new one, containing the Cartesian product of the involved dimensions).

Figure 2: Creating a new fact table with dummy values

Figure 3: Creating the dummy column

Now we need to create an alias for the table and make complex joins between the dimensions and the fact table, as shown below:

Figure 4: Physical join

Note that the “1=1” join enables a Cartesian product between all the dimensions that will join to this fact table.

Figure 5: Physical diagram

After the physical star has been modelled, we need to create a new logical fact table in the Business Model & Mapping layer based on the new physical fact table. The aggregation rule for the dummy column needs to be set (don’t leave it as ‘None’).

Figure 6

Figure 7: Setting aggregation rule for the dummy column

Next we need to create the complex joins between the dimensions and the fact table:

Figure 8: Business Model Layer Diagram

Finally we need to add the new fact table to the presentation layer.
Now we are ready to build our report again. This time we add the dummy column together with the other columns used before. We hide the dummy column and we obtain the new report. As shown below, we get data for all selected days (Figure 9).

Figure 9: Sales from 01/01/2001 to 15/01/2001

As shown in Figure 10, we see that all products are in the table even if they have not been sold during the selected time period.

Figure 10: Observe how the products without any sales still appear in the table

The new physical fact table with a dummy column has allowed us to show all dates and all products in our report for a full view of reality and better business insight!
If you have any questions or comments about this tip, please feel free to leave a comment below.