Employee Hierarchy Dimension Table
2 posters
Page 1 of 1
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
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
kenny- Posts : 11
Join date : 2009-10-30
Re: Employee Hierarchy Dimension Table
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.
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.
Bridge Table Columns
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
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
kenny- Posts : 11
Join date : 2009-10-30
![-](https://2img.net/i/empty.gif)
» Employee Dimension and Reports to Hierarchy
» Employee Dimension and Employee "Profile" Dimension?
» Hierarchy in dimension table
» Prod Dimension used with Product Hierarchy Bridge table
» Modeling Employee and Employee Role dimension.
» Employee Dimension and Employee "Profile" Dimension?
» Hierarchy in dimension table
» Prod Dimension used with Product Hierarchy Bridge table
» Modeling Employee and Employee Role dimension.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum