Adding Default Rows with OBIEE

Stewart Bryson -

When looking at a typical dimensional model, there are cases when the application of one or more dimension tables may not make sense for particular fact rows. We handle these situations with special rows in the dimension table that specify this lack of relationship, sometimes known generically as "default" rows. The three basic cases of default rows are: the "placeholder" row, the "not applicable" row, and the "unknown" row.

The placeholder row is typically seen in cases with late-arriving dimension records. Perhaps a latency issue exists between two source systems, and we aren't yet aware which dimension row we should apply. So we make a temporary association with a placeholder row until the appropriate dimension row is loaded. Or perhaps we have data quality issues in the source system, and we'll never know which dimension record should be applied, but we want to load the fact rows anyway. This is usually referred to as the "unknown" dimension row. Finally, a particular dimension may not have a logical application for this row. For instance, an order may not have a particular promotion associated with it. This is typically referred to as the "not applicable" row.

OLTP systems may also make use of default rows to signify the lack of relationship, but more typically, they use a NULL value in the ID field of the lookup table. These differing methodologies can be seen in Oracle's own sample schemas. In the SH schema, which is modeled after a data warehouse, the rows in the SALES fact table that don't have a particular promotion in affect at the time of the sale have a link to the PROMOTIONS dimension table via the surrogate key 999.

SQL> select promo_name,
  2  promo_category
  3  from sh.promotions
  4  where promo_id=999;

PROMO_NAME                     | PROMO_CATEGORY
------------------------------ | ------------------------------
NO PROMOTION #                 | NO PROMOTION

1 row selected.

Elapsed: 00:00:00.01
SQL>

But when looking at the OE schema, which is modeled after a transactional OLTP system, the lack of a promotion is represented with a NULL value in the PROMOTION_ID column:

SQL> select count(*)
  2  from oe.orders
  3  where promotion_id is null;

  COUNT(*)
----------
       105

1 row selected.

Elapsed: 00:00:00.00
SQL>

Though not as straight-forward as a real star schema, it is possible to mimic the functioning of these default row scenarios in a logical star schema when using OBIEE to report off of an OLTP system. To demonstrate, I'll use the OE schema, creating a fact table out of the ORDERS table and a dimension table out of the PROMOTIONS table. The first thing I have to do is configure an outer join between the dimension and the fact, which I do in the Business Model:


Now, in Answers, we still get results back from the Orders Facts table for those orders that don't have an applicable promotion (which, for some reason, is all of them in the OE schema), but I wouldn't say it's exactly what we are looking for:

Technically it works, but the NULL values don't make for a great looking report. Also, as soon as a filter is applied on the dimension table, the outer join will disappear because all of the attributes have NULL values, and any attribute with a NULL value will fall out of the join as soon as it is filtered on. What would be better is if the default rows showed up just like other rows in the dimension table with values explaining the lack of association, such as "No Promotion" in the case at hand. This would also allow filtering on these columns since they no longer contain NULL values. So I place IfNull functions on all the attributes in the dimension table, transforming them to meaningful values:


Now, I can apply filters to any of the attributes in the Promotions dimension, and it works identically to a default dimension:

We're almost there. But the only issue now is the use of the filter dialogue in Answers. Answers does not yet know about the value of "No Promotion", because that transformation does not occur until the join is applied, and the filter dialogue simply queries the Promotions table in isolation. So when checking to see all the choices that we can apply in the filter dialogue, we don't see our choice for the default row:


To resolve this, I need to have the default value as an actual value in the logical dimension table. One way to do this is with a SELECT table that includes a UNION with the default values already existing in the dimension table. To construct this scenario, the physical table would look like this:


Now, instead of doing IfNull functions on all the attributes in the Promotions dimension table, I just modify the join in the physical layer from a foreign key join to a complex join, specifying the IfNull logic:


And now, when I click "All Choices" in the filter dialogue, I see 'No Promotion' as a choice, and the join causes the expected results in the Answers report as well: