Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Measures having non applicable dimensions

2 posters

Go down

Measures having non applicable dimensions Empty Measures having non applicable dimensions

Post  DMModeler Mon Sep 03, 2012 1:03 pm

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

DMModeler

Posts : 6
Join date : 2010-05-14

Back to top Go down

Measures having non applicable dimensions Empty Re: Measures having non applicable dimensions

Post  BoxesAndLines Tue Sep 04, 2012 9:14 am

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
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum