Power BI data model: Local vs Global dim_tables

datadonuts — Thu, 13 Jan 2022 15:19:10 GMT

Hi to the community,

I am working for client with multiple corporate entities in a Power BI development project. Analyzsing the data structure of their ERP System (RDB) I noticed, that all dimension tables have dublicates for each of the companies entities, but no unique identifier for a customer ID or product ID. Even some tables are called dim_global_something, the structure is all the same and look like this:

Obviousely that causes m2m relationships to the fact tables in my PBI data model and makes the ETL pretty complicated to get to a functional model. As the ERP system is a third party product, we cannot go to the source to change the data or table structure.

My Question: is that a common scenario in RDBMS based ERP Systems e.g. SAP and how to deal with that? Any tips from experienced data model experts in the community?

Thanks so much

Glad to hear from you

Benedikt

Re: Power BI data model: Local vs Global dim_tables

amitchandak — Thu, 13 Jan 2022 16:43:07 GMT

datadonuts , Assuming this is a multi-tenant env.

In such case we create concatenated keys [company ID] & "_" & [DIm ID]

say Dim ID can be more than 10 digit

[company ID] *1000000000000 + [DIm ID] // number key multiplied by 12 digit

in both fact and dimension.

Ideal, is to have data warehouse generate surrogate key or do master data management

topic Power BI data model: Local vs Global dim_tables in DAX Commands and Tips

Power BI data model: Local vs Global dim_tables

Re: Power BI data model: Local vs Global dim_tables