Denormalization - Power Query or Source

[IgorM]

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

[amitchandak]

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/