cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GTR
Helper III
Helper III

Data Modeling Question: Handling Type 4 Changes

I have a products table that includes a (Senior Manager - Manager - Employee) hierarchy for the employee that is in charge of handling that product. 

 

PRODUCT     SENIOR MANAGER     MANAGER     EMPLOYEE

1                     ...............................     ...................    ....................

2                     ...............................     ...................    ....................

3                     ...............................     ...................    ....................

 

Currently, we are handling any change as a Type 2 according to Kimball's Data Warehouse Toolkit. Lately, we have had a lot of turnover in one particular department and have seen a rapid increase in the amount of Type 2's thus doubling our PRODUCTS table since every Product that belonged to one person transfers to another (most employees have about10,000-100,000 products assigned to them). 

 

After much research, we decided to go with a Mini-Dimension. However, every example I have seen uses fact/numeric or banding columns in this new mini-dimension. 

 

My question is, how would I handle a type 4 using employee name? We are also open to another method that may work better but we are trying to avoid any bridge tables or snowflaking in our star schema design. 

 

Thanks in advance.

6 REPLIES 6
Phil_Seamark
Microsoft
Microsoft

Is your data stored in a database?  If so, I recommend you shape your data in the database before accessing via Power BI and don't try to use Power BI to do a job which a DB is very good at.

 

One tip is to have an Employee table with a key.  Don't use the name, in case you come across an instance where you have two people with the same name.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark yes it is in a database and I am using Microsoft Access VBA at the moment to handle Type 2's in order to stage the data for loading to PowerBI. 

 

As for the tip, for this specific dataset, we only use employee name and employee code as an attribute for the employee so this data sits in our products table along with the product. 

 

So the question stands, what is the best way to handle a rapidly changing type 2 dimension that doesn't involve banding/ranging since you cannot put a range on an employee name?  

greggyb
Resident Rockstar
Resident Rockstar

I'd break out product and employee hierarchy to separate dimensions. Your fact table can capture a foreign key for product at time of transaction and a separate foregin key for employee hierarchy at time of transaction.

 

If your employee hierarchy is rapidly changing, then break out each management level to its own dimension. DimProduct, DimSeniorManager, DimManager, DimEmployee.

 

The fact table just records the keys that are accurate as of transaction time.

 

To me, product and employee are pretty orthogonal business concepts, so capturing them in a single dimension seems guaranteed to lead to issues.

@greggyb thank you for the suggestion. I will look into breaking out the employee categories into separate dimensions. We only use the Senior Manager, Manager, Employee hierarchy to record who is responsible for that product so this is not a normal Employee information table where it lists all employee history. 

 

I will report back the results after branching out to add more dimensions. 

 

Thanks

Greg_Deckler
Super User
Super User

 

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

I don't see a reply in your message, it's blank aside from your signature. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.