Measures having non applicable dimensions

View previous topic View next topic Go down

Measures having non applicable dimensions

Post  DMModeler on 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

View user profile

Back to top Go down

Re: Measures having non applicable dimensions

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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