Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table

View previous topic View next topic Go down

Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table

Post  obiapps on Sun Oct 10, 2010 4:48 pm

Given I have 3 tables, Workforce Event Fact, Department Dimension (Type II SCD), and a Department Hierarchy (Type II SCD), and assuming the DEPTIDs in the Department Dimension table are reflected in a BASEDEPTID field in the Dept Dimension hierarchy table, would it make more sense to connect the Hierarchy table to the Department Dimension table or have a surrogate key directly to the fact table. Currently, I have a surrogate key in the Fact table that joins to the DEPTID in the Department dimension table. The DEPTID can join to the the Hierarchy table on a field called BASE_DEPTID. From a modeling standpoint, would it make more sense to have 2 surrogate keys in the fact (one for Department Dim, and a separate one for Dept Hierarchy) or can I just have one and join HIerarchy to Dept instead of the Fact itself. Thanks.

obiapps

Posts : 21
Join date : 2010-09-28

View user profile

Back to top Go down

Re: Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table

Post  Jeff Smith on Wed Oct 13, 2010 10:39 am

Why not combine the department dimension and the department hierachy into a single dimension?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table

Post  obiapps on Wed Oct 13, 2010 9:23 pm

Main reason is because the source system already has it separated into 2 tables..one for departments and a second "flattened" fixed-depth table for the hierarchy. My question is, what is the difference, if any, in creating a surrogate key in the event fact to each table, or just creating a surrogate key to the Dept Dimension table and then joining the hierarchy table to that table. Since both the Hierarchy and the Dept Dimension tables are SCD Type II, would I be limiting myself to using the "Current" Hierarchy across all time by using the second approach? From a Dimensional Modeling standpoint, I want to know the pros and cons with each design. Thanks.

obiapps

Posts : 21
Join date : 2010-09-28

View user profile

Back to top Go down

Re: Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table

Post  Jeff Smith on Fri Oct 15, 2010 9:27 am

If you put the hierarchy key on the fact, you will have a snap shot of the hierachy each time you load the fact (keeping history).

If you snow flake the hierachy to the other dimension table, you can either keep history or restate history, depending upon how you update the hierarchy on the dimension table. If you update all the rows for the current hiearchy, then you're restating history. If changes to the hierarchy results in new rows in the dimension, then you'll be keeping history.

If you want to be able to keep history and restate history, then put the hierarchy key on the fact table and put it on the dimension in a way that it gets updated with the current hierarchy.

For example, I work for a health care insurance company. Members can switch groups. I need to be able to accurately state what actually happened historically with claims. Both Member and group are put on the claim fact table.

But when it's time for the group to renew, I want to be able to restate the claim activity based on the group's current membership. So I put the Group_Key on the Member Dimension and update the group key whenever the member changes groups. Of course, I call the group key on the member dimension the Current Group Key.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table

Post  obiapps on Fri Oct 22, 2010 12:22 am

In my case, the hierarchy and the dimension are both type 2 SCD (each have a CURRENT indicator, as well as Effective Start and Effective End dates). Only the Dimension has a key on the event fact table. Therefore, the current version of the hierarchy will be used for all historical fact data. In this scenario, I assume I am restating history which actually meets the business requirement. Thank you for the explanation.

obiapps

Posts : 21
Join date : 2010-09-28

View user profile

Back to top Go down

Re: Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table

Post  hang on Sat Oct 23, 2010 12:00 am

From dimensional modeling standpoint, the hierarchy dimension should be de-normalised into department dimension. The main focus or challenge of dimensional modeling is to remodel OLTP structure and create a dimensional data store. The most important principle in Dimensional modeling is de-normalise dimensions and normalise facts. Once the hierarchy becomes the part of department dimension, the complex relationship becomes self contained. The hierarchy restructuring is nothing more than the attributes changes which are tracked by SCD 2. Therefore you would just have a single FK in your fact to bring the whole hierarchical department information to live. With denormalised department hierarchy, there is no need to synchronise the correlations between two SCD 2 dimensions in your ETL.

hang

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

View user profile

Back to top Go down

Re: Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table

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