When to add attribute to Dimension or Create new Dimensions

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.

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.

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.

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.

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 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.