How should I design relationships between 3 tables?

[jk8979356]

Forgive me as I don't fully understand the proper way to design table relationships. I have three tables of transaction data: 2019 Actual Sales, 2020 Sales Forecast, 2021 Sales Forecast. Each table has the same 31 data columns, except the 2020 Sales Forecast table which has an additional 2 calculated columns. These are tables full of transactional data with no unique identifier keys. I am trying to use Power Query / Power Pivot to combine these 3 tables into a single pivot with multiple data rows that each use the Quantity & Revenue columns for the pivot table "Values". How do I create the relationships so I can compare Values from the 3 tables for the common columns shown here. Keeping in mind each table has thousands of different entries for each data row type. 

[rh203 · Accepted solution]

Hi,

My personal view on this is that you will come unstuck somewhere without a dimension table of some kind to at least link as a one to many relationship between the various FACT tables you have.

For Power Pivot:

If it were me I would do the following:

1) Create a new table by appending all of the sales actual / forecast tables together and then removing the transactional data columns to just leave the 'dimension' elements such as 'Customer ID' / 'Customer Address' etc etc. Preferably do the same repeatedly until you create a dimension table for the various types of dimensions you have (eg a customer master table, an item table). Make sure that you use 'remove duplicates' on the key field in each of these dimension tables so that you end up with only one instance of each customer / item etc etc.

2) I would then personally merge the Forecast 2020 and 2021 tables together to create a single 'Forecast' FACT table and leave the 2019 actual a as the sales 'Actual' FACT table.

3) Create a relationship from the dimension tables from step 1 to the FACT tables created in step 2 using one-to-many relationships from a key field (e.g customer ID) to the same field in the two FACT tables.

Power Pivot can have some strange behaviour in my experience unless you follow the straight forward Dimension (one) to a FACT [transactional data] (many) setup. It doesn't support many to many relationships and I have found out myself the hard way that you can end up on a wild goose chase unless you keep it simple.

In all honesty, if all you are trying to achieve is to show all of the results together so you can slice and dice as you please, you will probably be best off appending all tables together with the addition of a simple 'date' column in power query, outputting this as a basic table in excel and then reading that table with a normal pivot table (not power pivot)

hope that helps

Ryan