Issue:
A fact table may not be able to directly link to its associated dimensions. Instead, it may require an intervening bridging table to link to the dimensions, via surrogate keys.
How should this be designed in the OBIEE repository?
Solution:
A classic example of this issue is encountered when attempting to model E-Business Suite tables, where dimensions such as Entity and Business Unit are set up to link to the GL Balances table via the GL Code Combination table.
In this scenario, the Entity and Business Unit dimensions are set up as views made up from the flex field tables. These views are linked to the SEGMENT1 and SEGMENT2 fields of the GL_CODE_COMBINATION table respectively. In turn, the GL_BALANCES table is linked to the GL_CODE_COMBINATION table via the CODE_COMBINATION_ID field.
The Time dimension, on the other hand, is linked directly to the GL_BALANCES table via the PERIOD_NAME field. The physical layer in the repository will therefore look something like this.
When defining the joins of these logical tables in the Business Model and Mapping Layer, we could just replicate what we have done in the Physical Layer. ie:
But if we do it this way, we will encounter the following warnings when checking for consistency:
1. Where a first alias of the GL Code Combination table is the ‘dimension’ of the GL Balances fact table in the first star schema.
2. The Dim Entity and Dim Business Unit tables are the dimensions of a second alias of the GL Code Combination table in the second star schema.
As can be seen in the screen-shot of the Business Model and mapping Layer above, there are 2 aliases of the GL Code Combination table: Bridge GL Code Combinations, and GL Code Combinations which take part in two separate star schemas.
In the Presentation Layer we leave out the two aliases of the GL Code Combination table so that the users only see the fact and dimension tables. The logical joins will be made by the BI Server in the background.
For Example:
Note: In the example above I have added a couple of other dimensions as well. These link to the remaining SEGMENT<n> fields in the GL_CODE_COMBINATION table.
When a query is run against this subject area, the log will show the joins made in the background, but the average user need not worry about this, and is shielded from their complexity.
Example query:
We can also incorporate the attributes of the ENTITY_VIEW and BUSINESS_UNIT_VIEW directly into the GL_CODE_COMBINATIONS table within the logical model. This would make for a fairly simple star schema design. However, doing this would restrict your ability to reuse the ENTITY_VIEW and BUSINESS_UNIT_VIEW dimensions elsewhere, as they would not be available as part of the model.
Inorder to demonstrate how to use the E-Business Suite tables as untouched as possible so as to facilitate a fairly straight-forward conversion of the source tables into a star schema. Coalescing the attributes of the snowflaked dimensions would have added an extra level of complexity into the model generation process.
A fact table may not be able to directly link to its associated dimensions. Instead, it may require an intervening bridging table to link to the dimensions, via surrogate keys.
How should this be designed in the OBIEE repository?
Solution:
A classic example of this issue is encountered when attempting to model E-Business Suite tables, where dimensions such as Entity and Business Unit are set up to link to the GL Balances table via the GL Code Combination table.
In this scenario, the Entity and Business Unit dimensions are set up as views made up from the flex field tables. These views are linked to the SEGMENT1 and SEGMENT2 fields of the GL_CODE_COMBINATION table respectively. In turn, the GL_BALANCES table is linked to the GL_CODE_COMBINATION table via the CODE_COMBINATION_ID field.
The Time dimension, on the other hand, is linked directly to the GL_BALANCES table via the PERIOD_NAME field. The physical layer in the repository will therefore look something like this.
When defining the joins of these logical tables in the Business Model and Mapping Layer, we could just replicate what we have done in the Physical Layer. ie:
But if we do it this way, we will encounter the following warnings when checking for consistency:
Business Model EBS: [39008] Logical dimension table Dim Entity has a source Dim Entity that does not join to any fact source. [39008] Logical dimension table Dim Business Unit has a source Dim Business Unit that does not join to any fact source.These warnings arise because the GL Code Combination table is not seen as a Fact table – an ultimate data source. To fix this we need to divide the logical layer into two separate star schemas:
1. Where a first alias of the GL Code Combination table is the ‘dimension’ of the GL Balances fact table in the first star schema.
2. The Dim Entity and Dim Business Unit tables are the dimensions of a second alias of the GL Code Combination table in the second star schema.
As can be seen in the screen-shot of the Business Model and mapping Layer above, there are 2 aliases of the GL Code Combination table: Bridge GL Code Combinations, and GL Code Combinations which take part in two separate star schemas.
In the Presentation Layer we leave out the two aliases of the GL Code Combination table so that the users only see the fact and dimension tables. The logical joins will be made by the BI Server in the background.
For Example:
Note: In the example above I have added a couple of other dimensions as well. These link to the remaining SEGMENT<n> fields in the GL_CODE_COMBINATION table.
When a query is run against this subject area, the log will show the joins made in the background, but the average user need not worry about this, and is shielded from their complexity.
Example query:
We can also incorporate the attributes of the ENTITY_VIEW and BUSINESS_UNIT_VIEW directly into the GL_CODE_COMBINATIONS table within the logical model. This would make for a fairly simple star schema design. However, doing this would restrict your ability to reuse the ENTITY_VIEW and BUSINESS_UNIT_VIEW dimensions elsewhere, as they would not be available as part of the model.
Inorder to demonstrate how to use the E-Business Suite tables as untouched as possible so as to facilitate a fairly straight-forward conversion of the source tables into a star schema. Coalescing the attributes of the snowflaked dimensions would have added an extra level of complexity into the model generation process.
