Measures having non applicable dimensions
2 posters
Page 1 of 1
Measures having non applicable dimensions
Hi
We are dealing with a financial dwh solution. The measures in the fact are of type non additive in nature and a source system would compute these measures using complex formulae. The fact table is typically connected to eight dimensions.
The not so common feature of the measures in the fact is, while the measure is getting computed at the source application the source would consider different dimensions which would influence the final value of the fact. Hence in one case, to arrive at the measure the source might use all the dimensions (related attributes) and produce the output fact, which is a plain vanilla case. However in other situations the computing engine at source might consider only three dimensions and produce final measure and in another case the source might consider only five dimensions to arrive at fact and so on. Now to a have dimensional model in place,
(a) One approach is to have a record called 'Not applicable NA' in each dimension and whenever a dimension is not applicable, use the surrogate key for the 'NA' record in the fact and use the surrogate keys for other dimensions which reflect actual values. Here users would use only fact table, easy.
(b) Alternately, we might consider building a seperate set of fact tables based on the most probable dimension combinations (currently we think there are around 6 to 8 such combninations) to avoid 'NA' surrogate keys in a rampant way. The down side is for users to get reporting they need to switch between multiple facts.
Can some one please suggest what approach can be taken in such situation as a best practice.
Thanks
We are dealing with a financial dwh solution. The measures in the fact are of type non additive in nature and a source system would compute these measures using complex formulae. The fact table is typically connected to eight dimensions.
The not so common feature of the measures in the fact is, while the measure is getting computed at the source application the source would consider different dimensions which would influence the final value of the fact. Hence in one case, to arrive at the measure the source might use all the dimensions (related attributes) and produce the output fact, which is a plain vanilla case. However in other situations the computing engine at source might consider only three dimensions and produce final measure and in another case the source might consider only five dimensions to arrive at fact and so on. Now to a have dimensional model in place,
(a) One approach is to have a record called 'Not applicable NA' in each dimension and whenever a dimension is not applicable, use the surrogate key for the 'NA' record in the fact and use the surrogate keys for other dimensions which reflect actual values. Here users would use only fact table, easy.
(b) Alternately, we might consider building a seperate set of fact tables based on the most probable dimension combinations (currently we think there are around 6 to 8 such combninations) to avoid 'NA' surrogate keys in a rampant way. The down side is for users to get reporting they need to switch between multiple facts.
Can some one please suggest what approach can be taken in such situation as a best practice.
Thanks
DMModeler- Posts : 6
Join date : 2010-05-14
Re: Measures having non applicable dimensions
Option A. That is the purpose of the default row in dimensions. You don't build a new fact table based on dimension optionality. You simple mark the dimension as Not Applicable. If users are accessing the warehouse without the benefit of a semantic layer (BI tool), then you can always build a series of views that present you various combinations of facts and dimensions.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Measures Additive Over Some Dimensions
» Measures as Dimensions: Banding Approach Help
» Modeling for ad-hoc queries across measures and using nonconforming dimensions
» Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
» Question about Dimensions with SKeys to join with other dimensions.
» Measures as Dimensions: Banding Approach Help
» Modeling for ad-hoc queries across measures and using nonconforming dimensions
» Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
» Question about Dimensions with SKeys to join with other dimensions.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum