Wanted to get your eyes on this to see if i am doing this the correct way.
You normally say try and merge on as many lookup tables into the facts.
In this situation It has been really difficult to merge on pretty much nothing - as the Rows of Data is too big.
Am I doing the right thing here by creating a supporting table (top section)
1-2Many. (filtering both way) .
I found if the filtering is only single the “total sale” number is static in 2nd screen shot u see.
If you can please give me your views, advice on how to handle this level of complexity please.
What are these actually doing versus just using the lookup table that actually exist?
I don’t really think these are required in this case. I just doubt you will need to filter lookup table together that can’t then filter the fact table. It just doesn’t seem logical to me.
The whole idea with using this technique is so that the filters in your model act like a waterfall. One level to the next.
In this case they don’t though because you don’t have any connection from the second layer down to the fact table.
Multi-Directional
Yes I know they are multi directional.
The reason being is my lookup tables still have many ID, Product ID or Transactional ID ,so i’ve used a supporting table to get my unique ID filtering into the fact. Example. Lets take Transaction History.
-I cannot link Trans History Tab into FactPurchaseOrder. Because theres no Transaction ID.
-My supporting table has unique product id, so I am linking this with the Transaction History - Product ID column
-The reason why the relationship is MD (Product ID to Transaction History) is because the the dax total sale wont work.
-Product ID supporting table into the FactPurchaseOrder is 1-2-M.
do you see what I mean?
Hoping I am not confusing you here. Just wanted to know if this process/logic is correct on this advanced model. I would upload this file but it is big.
I still just don’t see why you need these additional central lookup tables.
Can you think of any insight you need that benefits from this setup?
I honestly can’t right now.
As mentioned the idea around multi layered models is for a continuous flow of relationship down towards your fact table.
From what I can see this does not happen so to me there is no point in these tables. They do not assist in adding any value to calculations you might need to complete.
Also filtering both ways again seems pointless to me. What calculations is this supporting? I totally do not recommend this.
I feel you’re making your model way more complicated than it needs to be. Try to simplify it.
Every table that has many values as it’s index rather than the unique values should move down to your fact table layer. You should not have them as lookup tables with multi directional relationships.
