Abstract Generic Dimension - Help Needed

[tfm]

Hi users,
We are evaluating the model of a new solution in our DW and we are having some trouble with our Contract Dimension. Actually we have one Contract Dimension for each product of the company, so if we have the products A, B and C we also have the dimensions DIM_CONTRACT_A, DIM_CONTRACT_B and DIM_CONTRACT_C.
One member of my team suggested to create a single Contract Dimension (Abstract Dimension) and use a super type attribute to define what product the contract is. Doing so, we will have the super types A, B and C. The problem is, we have a lot of particularity for each product contract, for example, A super type contracts have the proposal number attribute, but B and C don’t, so we should set this attribute to ‘Not Applicable’ on these cases. This happens to many other attributes, because of the particularity of each contract.



To avoid business users to see many ‘Not Applicable’ attributes we would like to control their view by the semantic layer, allowing the user to see only the attributes Applicable to each kind of contract.
Do you think this is a good approach? Any suggestion? Thanks in advance.  

[ngalemmo]

What does the business need to know about a contract?

One mistake that is too easy to make is to over-engineer things. Attributes that seem important turn out to be of no importance to analytics.

What would be wrong with a flat contract dimension with sparsely populated attributes?

[nick_white]

or use super/subtype dimensions where only the common attributes sit in the supertype dimension

[tfm]

What does the business need to know about a contract?

One mistake that is too easy to make is to over-engineer things.  Attributes that seem important turn out to be of no importance to analytics.  

What would be wrong with a flat contract dimension with sparsely populated attributes?  

Each specific attribute is necessary because our areas have some different attributes for their contracts (The product contracts are controlled by different areas).
We want a flat contract dimension with sparsely populated attributes, that’s an usual approach?  We don’t have any dimension with so sparse populated attributes today.
Our main concern is about the business user reaction with blank or ‘Not Applicable’ attributes, that’s why we want to control the user view by a semantic layer.
Do you think this is the best solution?

[zoom]

My suggestion is not to create a super/subtype dimension but to create a flat abstract dim with all of the attributes from all sources. Common attribute like contract_name is going to be the same for all the source, but unique attributes per source can be named differently. You can have "source name" column to identify a specific contract source in the dim. Using that source name you can hide  ‘Not Applicable’ attributes in the view. BTW You need a View if you have educated super user who can drag and drop attributes from a dim to create their own report. If you have canned reports, then you do not need different views, you need different report per a source... just show specific attributes in the report according to a user from a specific source.

[Sponsored content]