Supporting Table (Uniq ID ) not working


#1

Hi Sam. Please could I get your eyes on this complex data model.

I am trying to get the top 2 tables (region and Territ) to be links in the Data Model.
What I’ve done is created a new table called Uniq ID (2nd screen shot) to get many ID, so I can get a 1-Many Rel.
By doing it this way it drills into the Employee Tables into main Fact table. Logically my model makes sense not sure why on page 2 my column fields are static on total sale.

Thanks in advance.

14.NorthWindDatabase.pbix (672.9 KB)


#2

Firstly this model is way too complicated.

It needs to be simplified immensely.

Please review this course in depth and re-arrange things as per the many best practices advised here

With a model like this you are just placing bandaids over something that need surgery.

The first thing I would do is simplify the amount of table you have within the query editor by merging the many small tables into larger lookup tables.

For example, add these to a master lookup table instead of breaking them out like this.

image

Also same here (these are pointless by themselves )

Think about how much simpler it will be once you’ve done this.

Also maybe look to merge these

image

Also these

image

All of these will make auditing that much easier.

Give all these changes a go first before moving on.


#3

Sam your a legend!
I think what I wanted to ask you, I have actually solved now. it’s fine makes sense what I wanted.
I’ve just made my fact “orders” table massive, just under 45,000 rows because was merging on many different columns to get as many ID columns in Order table.

Image Lookup top table - just appended all 9 other tables into 1. Perfect.


#4

Nice one, looks much better and way simpler. I’m sure you feel the same way now.