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,
I have a general question on data denormalization. I have a model that is almost entirely based on data coming from a single ERP system. In my model I have a wide FACT table with ledger transactions (retrieved from the ERP using a SQL that joins data from multiple tables). I will also need to add to the model DIMENSION tables such as: Ledger accounts, cost centres, projects, etc.
What's the best approach here - is it better to adjust the SQL query for the FACT table so that includes all the necessary data and then normalize it in Power Query or is it better to retrieve all DIMENSION tables using seperate SQL calls to the database? Is there some rule of thumb for this or is it a matter of trial and error?
Kind regards
Igor
Are you are planning to direct query, then make sure everything is a well place in start schema(Prefer) .
If not prefer to bring data in Star Schema. If for some reason you can not. First, try to correct it in Edit Query mode and then in Data model view.
Refer
https://docs.microsoft.com/en-us/power-bi/guidance/
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
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 |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |