Merge two different organization keys, from different data warehouse

[Lmggud]

Hallo.

Assignment:
I have to build a report, in Excel, that shows the sum of measures, when the user drills up and down our organization hierarchy.


Challenge:
Data comes from two different data warehouse, that uses different organization keys, for the same department.
Department names is the same in the two Data warehouses.


Tools:
I use Power Query and Power Pivot.
I am trying to building a star schema.

The Fact table contain, the Organization key and the measures.
Dimension tables contain, Organization key and the name of our different departments.

I have attached a Image that shows a illustration of the Organization Keys, i hope it helps.

Problem: I can’t figure out how I can build a dimension, that will allow me to merge the two organization hierarchy’s/ Organization keys.

I hope someone can explain to me, how I can solve this problem.

 

[Matt Allington]

You need a mapping table to map the keys. If it were me, I would Load the mapping keys into the dim table, substitute the keys from one dw in the fact table, and join on the key you selected to use.

 

[Lmggud]

Hello.

Thank you for your reply.

Will it be possible to make the mapping table in Power Query?

One of my challenges is, that the organization keys change daily, and i will have to remake the mapping table every time I update the report.

 

[Matt Allington]

When you say change, do you mean new keys are added, or do you mean today the key mapping is ABC => 123 and tomorrow ABC =>456 ? Hopefully the former. I would not do it in Power Query. I would do it in Excel and load into PQ from there. You can then just maintain the Excel workbook prior to refresh.

 

[Lmggud]

Hello.

Thank you for your reply ?

The keys at change every day, for security reasons.

Today ABC is 123
Tomorrow ABC is 456.

I have made an Excel file and uploadet it to power query. It works, but it is another manual step.

 

[Matt Allington]

You can't do much about it if this change happens. You need to harmonise the keys across orgs to make the reporting work. If there is another way to do that, then you should investigate. eg, if the descriptions are unique and don't change, then you could maybe use those.