Power Query staging queries

[Isildur13]

Hi Community!

I have a question when it comes to ways of organizing your queries in PQ and the use of staging queries.

First of all I use parameters with servername and database name when connecting to a SQL Server.

Next thing is how to use the staging way correctly.

Do you create one single staging query which connect to the SQL datawarehouse with your parameters where you can see all the different views in your datawarehouse. Then you create some new queries which all reference the staging query and in these queries you select the different views you need for your dataset / model.

Or

Do you create a staging query for each SQL view you need in your dataset / model eventhough all views comes from the same datawarehouse.

What is the recommended approach when using a staging query in Power BI? 🙂

[Isildur13]

Hi @ChielFaber 

In my case we do not use dataflows but instead import each SQL view into the power query in the power bi report.

In such a scenario, will it still make sense to import each SQL view as a staging and then reference each view to create the dim / fact tables? Or would it make better sense to create on query which contain the connection to the SQL server / datawarehouse and then reference each query to the required SQL view from the datawarehouse in order to create the dim / fact tables?

Does that makes sense? 🙂

[ChielFaber · Accepted solution]

In that case I would personally load the seperate views and use them as the staging step. If your datawarehouse also contains other views/tables I wouldn't load them into Powerquery/PowerBI.  But this might also be personal preference.

For me the main principles would still remain.

- I would keep it simple and avoid loading data that doesn't end up in your report

[ChielFaber]

I would recommend creating different staging dataflows per view. This makes the different dataflows less complex and easier to monitor. When you load everything seperatly you have the following advantages:

- Less complexity

- Easier to analyze performance of the different views/sources

- You can set different refresh schedules

With the help of power automate and a PowerBI streaming dataset you can create a monitoring report for your dataflows. When all staging tables are in one dataflow you only see the performance of that one single dataflow. When you have seperate dataflows you can monitor the performance of all the different dataflows.

Also take a look at https://learn.microsoft.com/en-us/power-query/dataflows/best-practices-developing-complex-dataflows

Microsoft also recommends to keep it simple. Don't put too much complex dataflows together for performance and complexity reasons.