Grain present at every level of a dimension

[kajaldas007]

I am currently modeling factory processes. Following is one of the interesting scenarios:

Suppose there is a dimension called Organization (let's call it Org) that contains the hierarchy (top down): Factory - Department - Business Unit - Machine. There are several measures, grain of some of them is machine, for some it is business unit, and for some it is factory. Initially I thought of having dimensions corresponding to each such level; i.e. one for Machine, one for Business Unit, and so on; but without any snow-flaking; i.e. each such dimension would be independent of each other and would contain it's own hierarchy completely up till factory level. Now, this issue is present for other dimensions as well. This means, in this approach, I will end up creating too many dimensions.

Then I thought this could be handled in another way as well - by having only one dimension with machine as the lowest level. To take care of facts at higher grain, there will be records in Org table that will contain values for factory and for lower levels till the level of grain; and will contain "NA" for levels lower than the grain. The following example may help:

OrgSK	Machine	Business Unit	Department	Factory	Level
1	M100	BU1	Dept1	F1	Machine
2	M200	BU2	Dept1	F1	Machine
3	NA	BU1	Dept1	F1	Business Unit
4	NA	NA	NA	F1	Factory

Please let me know what the recommended approach is in such scenarios. Please also let me know if there is even better approach.

[Vishy]

I have never seen this kind of design as doing this way is fine but think about getting data out of this design. If you want to find total value of a measure which is applicable to all the levels but at different point of time, how do you do that. You might have to create alias of your this single ORG dimension, so indirectly this single org dimension will be playing different roles.

How you would create a cube out of this using any BI tool ? Even in cubes case you will end doing role playing. This is kind of ragged hierarchy which was not forced by dimension itself but forced by measures.

I would say this way you will be inserting measures in the fact which are at different grains and you will also end up not following business process. Not following a business process while creating a fact is a big risk, you might get away for time being but you don't know in future what kind of requirements you get.

I would suggest you to have ID of each level rather then only having a ORG ID, and create seperate fact tables and join on corresponding ID's.

You can have a single surrogaet keys also.

This process might look clumsy but I am always very scared of mixing granularity and not following a business process.

[kajaldas007]

Hi Vishy,
Thanks for your feedback. I couldn't agree more with you as far as following business process is concerned. So far, my data model has followed the business processes. But, sometimes based on situation, some optimization might be necessary. In fact the approach that I am thinking about has not been implemented yet. However, before discarding this new approach, I want to be 100% sure about it's downsides. So, may I ask you to elaborate some of your statements?

If you want to find total value of a measure which is applicable to all the levels but at different point of time, how do you do that.

Just fyi, there are about 150 measures; but none of them is applicable for more than one granularity level along the org dimension. If this is what you meant, then we can safely discount this possibility.

How you would create a cube out of this using any BI tool ? Even in cubes case you will end doing role playing. This is kind of ragged hierarchy which was not forced by dimension itself but forced by measures.

I wouldn't say I will end up doing role-playing in the cube; but, I see your point. In the cube it wouldn't be difficult, though. We're using SSAS. separate DSVs will be created. And the logic will also be very simple - based on the "Level" column (refer to the example). Please let me know if my understanding is correct.

I would suggest you to have ID of each level rather then only having a ORG ID, and create seperate fact tables and join on corresponding ID's.

I have not said anything about fact table design in my original post. Yes, I may have to create separate fact tables based on relevance and business process.

You can have a single surrogaet keys also.

What did you exactly mean by single surrogate keys?

[Sponsored content]