Forum Discussion
Data Modeling Question: Handling Type 4 Changes
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.
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
