Employee Hierarchy Dimension Table

Hello All,

I am trying to model a Hierarchical Table, I need some help on what is the best way to model this scenario
I want to Flatten the Hierarchy Horizontally in the Dimension Table, I want to keep it as a SCD Type 1 Overwrite information

The Hierarchy is 8 levels deep e.g CEO, President, SrVP, VP, Director,Sr Manager, Manager, Associate

Here is the source columns
====================
EmployeeId
ManagerId
Name
Title

============
I want the Target Table to look like this

Employee_SK
Level1_NK
Level1_Name
Level1_Title
Level2_NK
Level2_Name
Level2_Title
Level_n..._NK
Level_n..._Name
Level_n...._Title

For initially loading this table I can use some Hierarchical queries like START WITH CONNECT BY, or Recurvive SQL or Self Joins

My question is when a person moves up in the Hierarchy how to update all the dependent data, are there any techniques or intermediate tables which are supposed to be built to handle this scenario, New levels could be added too, what should consider while designing a Hierarchical Table.

Please advise

Kenny

You need to ask yourself if a flattened structure is appropriate for the hierarchy you describe.

In generall, a flatted hierarchy works best when the levels are well defined and stable. If the hierarchy is ragged or the levels are not well defined, a flattened form doesn't help queries as no one would know what level 3, for example, represents.

It is usually best to maintain ragged heirarchies in a bridge table as an exploded hierarchy. If you have a ragged hierarchy that has identified levels (such as company, division, department) you could build a flat version that only includes those identified entities... leaving out intermediate organizations that have no clear business definition. However you would still maintain a bridge to allow rollups at these other levels.

The easiest way to update a flat hierarchy is to simply regenerate it. If you need to maintain change history, compare the old and new versions and update accordingly.

Thanks Sir for your response
I was reading Kimball Bridge Table design for Variable Depth Hierarchies, I just want to confirm the structure of the Bridge Table
From Mr Kimball's example he has a Customer Dimension -> Customer Hierarchy Bridge -> Fact table

What does the Customer Hierarchy Bridge store the Surrogate Key of the Customer Dimension or the Natural Key of the Customer Dimension
I am plannig to reload the Bridge table as the Hierarchy is very dynamic Employees in Departments can be completely wiped off

What I am not sure about, Is the Bridge table supposed to be built on the Dimension table or the Source Table
If it is built on the Dimension Table then the EmployeeId and ManagerId both need to be present in the Dimension table

Please advise

kenny