Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.