Power BI Tables and DAX - Import and Write DAX or do everything in the Source

0
votes

Let's assume I have a data warehouse environment.

Would it be a better to practice to...

Write a SQL query that gathers everything you need via the fact and dimension tables to minimize the amount of DAX and M that you need to use?

OR

Import the fact table and the dimension tables separately and write some DAX and use M to get everything that you need to get as far as columns go?

Please help me understand the best practice.

Edit #1

This seems to suggest it's better to import everything into facts and dimensions separately vs combining everything:

https://community.powerbi.com/t5/Desktop/Facts-and-Dimensions-still-useful-with-Power-BI/td-p/402218

1
powerbidaxdata-warehousepowerbi-desktopm
The edit seems to be correct. Power BI prides itself by enabling a Model: you can import more than 1 big table, then why not do this? Having said that, the most important thing in any data model, is use Clean data. And best practice is to clean the data as early in the source as you can, and delay the analysis to the Model. – Hila DG

1 Answers

1
votes

Import the fact table and the dimension tables separately and write some DAX and use M to get everything that you need to get as far as columns go?

This. The dimensional model (facts and dimensions) serves the same function in Power BI as it does in a Data Mart: It reduces data duplication and separates the "things that we measure" from the "things that we filter, pivot, drill, and sort by".

See for instance: Design a data model in Power BI

Note that your Power BI data model might not be the same as the Data Warehouse dimensional model, but you'll normally bring in the relevant dimensions as-is. You might bring in the facts at a different grain than they are in the data warehouse.