Filling in Sparsely Populated pivot tables in OBIEE

In this post, i will be explaining how to satisfy some complex requirements like calculating average with respect to all the records in dimension even though there are no relevant fact records.

Most of the reports like simple Order Quantity Report can be done easily with the functionality provided by the OBIEE Answers. Some complex requirements will require additional setup. For example, if instead of subtotaling Order Quantity by summing, we wish to subtotal by averaging Quantities by the number of Customers, whether or not they sold any products.

Here is the initial report that has subtotaling with a sum aggregation rule, [the default].

Here is the same report subtotaling with an average aggregation rule. 

Note that subtotals will be as desired when all cells in the column are populated. Now the FMRADIO against BR Country_Code, for example, does not have the desired results.  The subtotal displayed is 1100.   The desired value is 220 [1100/5] since there are 5 Customers. Similarly for CHARGER against BR, the desired result is 380 [1900/5].

The problem is caused by the absence of rows returned for the respective product lines. See screen shot.

No rows are returned as there are no rows in the fact table for that intersection of dimensions, (i.e. Customer [DARREN] [RAMESH] [STEVE] [DOUG], Country [BR] and products with subtype=FMRADIO).  To solve this problem, we need to join the dimensions in such a way that all values are returned, even if there is no Quantity.
To accomplish this, we will create a physical table alias based on the problematic dimension that is referenced in our report which is DIM_CUSTOMER and we will call this as DIM_CUSTOMER_ALLVALUES

NOTE: The next step is very critical.  The new alias we have created needs to be joined to the other dimension tables.  As there is usually no physical join that can be used, we will just use the clause 1=1, which results in a Cartesian join when executed by an RDBMS.  See screenhots.  Note that this join must be defined as a complex join.

Our physical diagram will look as in below screen shot once we are done creating joins.

Next step is to add this new alias to the logical fact table as one of the sources as shown in the screenshot below.

We will also create a new logical column called “All Values” in the Logical Fact Table and the formula will be just '1' for this new column as shown below. The aggregation rule will be SUM for this column in our case.

Add the All Values column to your presentation catalog.  We are ready to complete our report.

To the original request, add a filter based upon the All Values column. 

Modify the formula of Original Order Quantity column to IFNULL("Fact - Orders"."Original Order Quantity",0) in Edit Column Formula and change the aggregation rule on the column to average.

Now run the report:

We can see that even though there are no values in the fact table for some of the dimensional combinations, OBIEE is substituting the Original Order Quantity as "0" for those and then calculating the Sub Totals properly.

This approach can be used in all the scenarios where we face issues with the fact tables being sparsely populated (Normal in Data Warehouse environments).

Hope this helps.
Siva