Common dimensions are required for cross-facts reporting, and are oftentimes referred to as conformed dimensions. A dimension can be conformed through its data consolidation, or its logical data model. Data consolidation is useful for conforming the dimension data which has the same hierarchy level, and happens in MDM and ETL processes. A logical dimension is very useful for conforming the dimension data which has different hierarchy levels, and happens in the logical data model design.
For example, a bank’s goal performance report uses both budget data sourced from Oracle Planning and Budgeting Cloud Service (PBCS) by month, and loan and deposit data sourced from ERP Cloud by transaction date. Since budget data and transaction data are at different Time hierarchy levels, instead of creating two separate logical dimensions for date and month, a conformed Time logical dimension is desired in order for both facts to be reported in the same goal performance report.
In Oracle BI on-premises, creating a conformed logical dimension is very straightforward. For example, to create a conformed Time logical dimension in Oracle BI on-premises, follow these three simple steps:
- Create separate physical dimensions for date, month, quarter, and year
- Create a conformed Time logical dimension, and map it to all four physical dimensions per their hierarchy level
- Set the content Logical Level of the logical fact to the respective hierarchy level of the conformed Time logical dimension (PBCS fact to Month, Transaction fact to Detail Date)
But Oracle BI Cloud Service (BICS) does not provide any Logical Table Source (LTS) mapping capability. To implement the same conformed Time logical dimension in Oracle BICS, follow these three simple alternative steps:
1. Overload the dimension with all hierarchy levels
Instead of creating separate dimension tables for Month, Quarter, and Year, the physical database table D_TIME for the Time dimension is overloaded with Month, Quarter, and Year dimension data.
The DATE_ID is overloaded for Date, Month, Quarter, and Year dimension data and needs to be constructed uniquely to identify the corresponding overloaded dimensions, for example, 20170101 (yyyyMMdd) for the Date, 201701 (yyyyMM) for the Month, 201791 (yyyyQQ+90) for the Quarter, and 2017 (yyyy) for the Year.
2. Create the conformed logical dimension
The conformed logical dimension created here is just a normal logical dimension. To create the Time dimension, simply add the D_TIME database table to the model, rename logical columns, and complete the hierarchies.
The fact tables are now able to join to the conformed Time logical dimension in different hierarchy levels.
Since the DATE_KEY is overloaded, the PBCS Budget fact will join to the Month data in the conformed Time dimension, the transaction Loan Deposit fact will join to the regular Detail (Date) data in the conformed Time dimension.
3. Set fact measures to use the proper dimension hierarchy level
One last thing to do is to configure all measures in the PBCS Budget fact to be level-based measures. This will enforce the PBCS Budget fact to always work correctly with the conformed Time logical dimension even if the report is attempting to report at the Detail (Date) level. In Oracle BI on-premises, this is set through the Content Logical Level of the fact’s LTS, but in Oracle BICS, we have to handle this through fact’s measures.
Edit and set all PBCS Budget fact’s measures to use the Time dimension’s Month Aggregation Level.
In summary, the same practice presented here in the examples can be used to create any conformed logical dimensions. In Oracle BICS, it does not have the ability to overload the Logical Table Source (LTS) mappings as that in Oracle BI on-premises, instead, it’s required to overload the actual physical database dimension table with all possible hierarchy level data, and set all necessary fact’s measures to be level-based metrics in the logical data model.
Previous << A Best Practice to Create Dynamic Column Reports in BICS
