Forum Discussion
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
I don't see a reply in your message, it's blank aside from your signature.
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.
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?
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.
