Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Isildur13
Frequent Visitor

Power Query staging queries

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? 🙂

1 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

 

 

 

 

View solution in original post

3 REPLIES 3
Isildur13
Frequent Visitor

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? 🙂

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
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors