How to handle several single description field dimensions

View previous topic View next topic Go down

How to handle several single description field dimensions

Post  buddyd77 on Wed Apr 04, 2012 3:46 pm

I'm designing a DW in the Health Insurance Domain and I have 17 dimensions that have 2 fields which are 'key' and 'description'. A few examples of these dimensions are State(MA, NJ, FL etc..), Entity, Loss, Severity, Specialty, Market Segment etc....
My question is should I create these 17 individual dimensions which each just have a key and a description or should I put these into a generic text dimension that will store all the descriptions. This way the StateKey FK in the fact table will point to the same dimension as the EntityKey FK in the fact table and so on for those 17 FKs.
This will simplify the model very much but will it cause issues down the road with this catch-all dimension?

Thanks in advance,
Buddy

buddyd77

Posts : 2
Join date : 2012-04-04

View user profile

Back to top Go down

Re: How to handle several single description field dimensions

Post  ngalemmo on Wed Apr 04, 2012 5:44 pm

I usually place these in 'list of values' tables and do not use them in the actual dimensional model. Instead, what entities are these attributes of? The code and description should go into these dimensions. During ETL you use the list of values to populate the description in the appropriate dimension. You also use the list of values to detect changes in the description. When they occur, you update the description for that code in the dimension(s) where the description appears.

These small tables are useful for BI tools as they allow you to provide information for drop down lists without the tool having to do a select distinct against the dimension table.

There may be cases where some of these are dimensions in their own right. To reduce the number of FK's in the fact, you should combine related attributes into junk (or mini) dimensions and include those in the model. Keep the list of values tables for the reasons stated above.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: How to handle several single description field dimensions

Post  hang on Wed Apr 04, 2012 7:27 pm

Simple answer is, don't do it in dimensional modeling, as it serves no benefit but introduce confusion and bad performance. As ngalemmo suggested, put these simple dimensions in their respective tables if performance is critical by select distinct, or when you need to connect them to high level fact. However you still denormalise them in the main dimension, or in a mini dimension for low cardinalities. Here's a good article about why you should avoid such a generic dimension: http://www.kimballgroup.com/html/designtipsPDF/DesignTips2006/KU83ResistAbstractGenericDimensions.pdf

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: How to handle several single description field dimensions

Post  buddyd77 on Wed Apr 04, 2012 9:05 pm

These attributes don't seem to be part of any specific entity. They just stand alone as a description. Maybe that's where I'm going wrong. I see them as 17 individual attributes which require their own dimension.(Therefore 17 dimensions that have these 2 fields: Key(PK), Description(varchar50). That's why I was think of having just 1 generic dimension instead of 17 that have the same 2 fields.
They don't seem related in any way and don't seem to fit into any larger entity to be included in another dimension. I will go back an analyze the overall model to see if some of these description attributes can be grouped together into 1 entity or added to another dimension that already exists.

I don't understand your advice when you say "You still denormalise them in the main dimension, or in a mini dimension for low cardinalities."

Thanks for you quick response

buddyd77

Posts : 2
Join date : 2012-04-04

View user profile

Back to top Go down

Re: How to handle several single description field dimensions

Post  hang on Thu Apr 05, 2012 12:19 am

I am referring to those small dimensions that may live in other dimension as attributes. Creating separate dimension for them has two purposes. One is to have FK in the fact table only available at higher level, say an attribute in a main dimension. The other is to have distinct lookup list to speed up dropdown. However you should not replace the denormalised attributes by FK simply because you have a table for them, and that is the difference between relational and dimensional modeling.

Back to the generic table approach in dimensional modeling. It defeats the purpose of dimensional modeling, ease of use and query performance. The recommended article has explained this in great details. To me it's obvious that it reduces the clarity of the dimension and makes the dimension table unnecessarily much bigger, which is bad for performance. It might be convenient for you to group many types things together into a single table vertically for maintenance purpose, but it will never be easy for someone else trying to understand how it should be used, and that's what we should try to avoid as a dimensional modeler. Overloading one field by different types of data is different from denormalising dimensions that also group different things together.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: How to handle several single description field dimensions

Post  Sponsored content


Sponsored content


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