Best practice for importing SQL data to PBI

[luckygirl]

Hi, 
I am quite new to PBI, and would like to know expert suggessions.

I am importing data from SQL multiple SQL databases/ tabels as import queries. I would like to know the what is the best practice in the following senario. 

1. write one SQL query joining tables and import to PBI desktop
2. import data in seperate SQL queries to PBI desktop join them in the PBI data model using primary/forign keys
Thank you.

Thanks. 

[Cookistador]

Hello @luckygirl 

If you decide to perform the transformation in Power Query, try to keep the native query activated as much as possible.

Keeping the "native query" active, when possible, means to have power query send the query to the source database, and have that source database do the data processing. This is often more efficient than having Power Query pull all the data and then do the processing itself

When the native query is greyed out, it means it's no longer active. Try to perform all SQL-supported operations at the start. Using unsupported actions will deactivate the native query.

[Ritaf1983 · Accepted solution]

Hi @luckygirl 

Power BI works best when your data model is built using a Star Schema. This means importing dimension and fact tables separately, and connecting them using relationships (via primary/foreign keys) within Power BI. This approach is not only easier to understand and maintain logically, but also leads to better performance and more efficient data compression.

please refer to few excellent resources to help you understand why the Star Schema is preferred:

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

https://learn.microsoft.com/en-us/training/modules/modern-analytics-data-modeling/

https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[luckygirl]

Thanks, that makes sense. 

[Greg_Deckler]

@luckygirl Well, some of this depends on your skill level and what is the desired skill level of maintaining the solution. "Best practice" is generally to move the transformation of data as close to the source as possible. That would generally mean SQL views that perform the table joins. However, you could also do the transformations in Power Query and that would generally lower the skill level required to maintain the solution. There are a lot of analysts that can navigate around the Power Query Editor interface but aren't necessarily skilled at SQL.