So after completing the advanced data modelling concepts.
I have imported some tables from SQL and I am still hitting a brick wall on certain more specifc topics around advanced modelling - especially on M2M relationship.
If someone can please give me some direction If my model is on right path.
Layer 4 of my model is what cocnfuses my mind the most!
So I cannot link layer 4 directly into my fact tables I have many “Product-ID”). What I have done instead is consolidated all unique “Product-ID” into 1 "Master Product ID (Layer 3) table, using it as a Supporting Table.(2nd Screen Shot )
*(3rd Screen Shot) Not sure where I am going wrong? I know it meant to be 1-2-M Model, but in this specifc instance I get static numbers or really odd numbers when i report from “Product Cost History”. If i turn the relatinship to a “Single filtering” the number becomes static.
To me the model here is not really setup correctly with lookup tables and fact tables. I’ve had a good look at the image and it looks like this should just be two layers just like many of the examples I have covered throughout the courses.
I’ve labelled below what I believe are Lookup vs Facts and should be on top or bottom layer of the model.
I would also look to consolidate the Product information into one table if you can.
The only thing that I’m unsure exactly how you would do at the moment is adding the Product List Price but check out this tutorial for more ideas around this.
So my recommendation is to simplify this more here. It doesn’t look any more complicated than other examples of models like this, but at the moment this model is looking rather complex unnecessarily so in my view.
