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

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.

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

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.

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.

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.

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.