In this tutorial, we’ll learn about staging queries using Power Query editor. This is a new concept that I’d like to discuss for us to have an understanding of when and where we can use this in the query editor.
Staging queries can be used to configure and expose the data sources parameters to the dimension and fact table queries of the dataset.
For Power BI datasets, we need to define parameters specific to the data sources, and develop a staging query which references those parameters. This is a recommended data access method.
Analyzing The Data Model
If we look at our model right now, we already have the essential elements, but it’s still too complicated. Therefore, we need to utilize the queries to integrate our tables or data into a more refined and optimized model. Staging queries via Power Query editor is an intermediary step to achieve this.
Here, we have tables like Channel Details, Customers, Products, Regions, and a range of Sales tables for 2014, 2015, and 2016.
Let’s see how our demo data might look from disparate data sources that we may have to connect in the future. If we check out the Sales Orders tables in this demo data, we’ll notice that it is exactly the same data that only differ in a year. We need to query this data so we can bring it into our model. Hence, we have to find a way to integrate them while still maintaining those queries, so they’d still continue to find the data.
Moreover, we shouldn’t leave them with the same look and shape like their initial query. That’s why we need to create this intermediary step called staging area for these tables. Then, within the query editor, we’re going to optimize the table or make it as a single table.
We have the Sales table, and the lookup tables such as Customers, Regions, and Products. We also have the Channel Details table, which we’re going to use as a staging table. Later on, we need to merge the Channel Details table into our fact table (Sales table). For the meantime, we’ll put the Channel Details table into our staging query. This is just to show you how we can utilize this table.
Setting Up The Model Via Power Query Editor
Now, let’s go back to our demo model. I’ll show you how I would suggest setting it up since we only want to create one table of sales out of these three tables.
We need to keep these three queries. So, we’re going to create a new group for them and name it Staging Queries.
Let’s also move the Channel Details table into our Staging Queries group.
Then, let’s move the Customers, Products, and Region tables inside the Data Model group.
This is just another good example of how to properly organize our tables.
Finalizing The Staging Query Via Power Query Editor
The only other thing to recognize with staging is the need for us to continue querying the data source. However, we shouldn’t let these tables go into our models.
To organize that, let’s start with right clicking on the Sales_2014 table. In this table, we’ll maintain the Include in report refresh option. Then, disable the load by unticking the Enable Load option.
Let’s also disable the Sales_2015, Sales_2016, and Channel Details table by unticking the Enable Load option. This is the last step before we commit these queries to our model.
As a result, this is what our staging query should look like. They’re still going to query, but we’re not going to bring them into our model.
This is to remove data source parameters from data retrieval queries and also, to make it easy to manage data sources and their queries.
***** Related Links *****
Organizing Your Queries: Power BI Query Editor Tutorial
Organizing Your Queries Into Groups In Power BI
Querying Data From Multiple Data Sources
Conclusion
Keep in mind that staging is about using these queries either from those that are already in a model and the ones that are in our staging area. Then, we need to visualize the model that we want to build out of these. We have to optimize, clean and shape our tables to get them into the model that we’re going to build relationships with.
In the other tutorials, we’ll learn how to append and merge these tables from the staging area. Hopefully, this gives you a good idea on how you can utilize this concept and how you should think about it from a query perspective.
All the best,
Sam
