Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
21 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
45 | |
15 | |
12 |