When to add attribute to Dimension or Create new Dimensions

View previous topic View next topic Go down

When to add attribute to Dimension or Create new Dimensions

Post  SQLGiant on Wed Jun 20, 2012 4:03 pm

Hello,

I'm new to Data Warehousing and Dimensional Modeling. We are currently in a pilot mode to start collecting some data and setting up a pilot data mart.

Luckily I found out about the Kimball Group and have started reading through their materials from the start, but currently I'm looking for a good source of examples references of how the best ways to split up / or not to split up Dimension Attributes in the model.

For example we have should an employee dimension have a department attribute or should the department attributes be in their own dimension, or is it OK to have the attributes in both?

Another example for us is Project attributes. We run medical studies and would like the slice the data by Phase and Therapeutic Area. Should those both be Dimensions, or should they both be attributes on a Studies Dimension.

Thanks in advance for any good references. Please let me know if I need to narrow the question down more (i know it is very broad), or if I'm missing something completely.


SQLGiant

Posts : 2
Join date : 2012-06-20

View user profile

Back to top Go down

Re: When to add attribute to Dimension or Create new Dimensions

Post  umutiscan on Thu Jul 05, 2012 7:39 am

When you have department attributes in employee dimension or in other dimensions, handling department attributes in ETL processes will be more complex.

If you hold only department key in employee dimension, you have to take action when the department of the employee changes. If you hold some other department attributes, you have to track changes in department records while loading employee dimension. For example when the name of a department changes, you have to reflect these changes to employee dimension.

umutiscan

Posts : 51
Join date : 2010-11-26
Age : 36
Location : Istanbul, Turkey

View user profile

Back to top Go down

Re: When to add attribute to Dimension or Create new Dimensions

Post  SQLGiant on Thu Jul 05, 2012 9:53 am

Thank you for the reply! That all makes sense. I guess the question is, is it OK to have a Key to another dimension in a dimension. In the HR example that Kimball lays out in this article, he says that we can obtain the employees current state at any given time slice by querying the employee_transaction dimension, that to me would mean that it must contain a link to a department. Or is this a case for a factless fact table to link employee to department to keep that history and current relationship.


SQLGiant

Posts : 2
Join date : 2012-06-20

View user profile

Back to top Go down

Re: When to add attribute to Dimension or Create new Dimensions

Post  BoxesAndLines on Thu Jul 05, 2012 10:39 am

You should create a factless fact table instead of creating relationships between dimensions. If you need to know what employees belong to which departments and when, that's a fact! Once you start down the road of relating dimensions, you diemensional model starts to look more and more like a normalized data model.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: When to add attribute to Dimension or Create new Dimensions

Post  jchernev on Thu Jul 05, 2012 2:14 pm

For the sake of argument here - isn't a normal fact table doing the same thing as a factless fact table in terms of keeping track of many-to-many relationships between dimensions? The only difference (from a physical layout standpoint) is that we're not always recording actual facts. Also, the use case scenarios are different.

jchernev

Posts : 14
Join date : 2011-12-08

View user profile

Back to top Go down

Re: When to add attribute to Dimension or Create new Dimensions

Post  ngalemmo on Thu Jul 05, 2012 2:20 pm

jchernev wrote:For the sake of argument here - isn't a normal fact table doing the same thing as a factless fact table in terms of keeping track of many-to-many relationships between dimensions? The only difference (from a physical layout standpoint) is that we're not always recording actual facts. Also, the use case scenarios are different.

Yes. A fact-less fact is just a fact table without measures.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: When to add attribute to Dimension or Create new 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