 |
|  |
 |
|  |  | |
When using many to many dimension relationships, special care needs to be given to measure values. A negative side effect of using many to many relationship is that the cube now duplicates measures values when there is more than one dimension record related to a single fact record. To resolve this issue the lookup fact can assign a weighted percentage to each of the dimension records that relate to the fact record. The total of all dimension weights for a single fact record should be 100%. Using a calculated measure in the cube, the weighted percentage can be multiplied by the measure value returning an accurate aggregated measure value at the many to many dimension level.
Some source systems require a more advanced implementation of many to many dimensions by using cascading many to many dimensions. In this case, a fact will relate to one or more records in dimension A and each record in dimension A will relate to one or more records in dimension B. Like a normal many to many implementation, there needs to be a lookup fact that relates the fact to dimension A. In a cascading many to many relationship, there needs to be a lookup fact that relates dimension A to dimension B. A recommended approach is to use a single lookup fact table with a record for every combination of relationships between the facts, dimension A, and dimension B.
Like the many to many dimension relationship, the cascading many to many relationship needs to give special care to the measure values. The first relationship between the fact record and dimension A records will be the implemented in the same fashion as a many to many dimension relationship. There also will be a weighted percentage given between dimension A and dimension B. To derive the weighted percentage between a fact record and a dimension B record, the weighted percentage between the fact and dimension A and the weighted percentage between dimension A and dimension B need to be multiplied together. Using a calculated measure in the cube, this derived weighted percentage (between a fact record, dimension A record, and dimension B record) can be multiplied by the measure value returning an accurate aggregated measure value at the cascaded many to many dimension level.
The concept of cascading many to many dimensions can be expanded to include any number of cascading dimensions.
BIAnalytix is an enterprise class BI solution that supports cascading many to many relationship data modeling.
| |
|
|
