From the course: Microsoft Power BI Data Analyst Associate (PL-300) Cert Prep by Microsoft Press

Improve performance by reducing granularity - Power BI Tutorial

From the course: Microsoft Power BI Data Analyst Associate (PL-300) Cert Prep by Microsoft Press

Start my 1-month free trial Buy for my team

Improve performance by reducing granularity

“

- [Instructor] In this sub lesson, we're going to improve performance by reducing granularity. So what we're going to look to do is create and manage aggregations. And aggregations in Power BI can improve query performance over very large direct query semantic models. By using aggregations, you cache data at the aggregate level in memory. Aggregations in Power BI can be manually configured in the data model, or they can be automatically managed by enabling the automatic aggregation feature in model settings in premium subscriptions. Some benefits of aggregations. Aggregations in Power BI let you reduce table sizes so you can focus on important data and improve query performance. Some advantages include, better query performance over big data, optimized data refreshes and balanced architectures. So how do we go about creating aggregation tables? Depending on the data source type, an aggregation table can be created at the data source as a table or a view. Or within Power Query, we can create it as a native query or for the greatest performance as an import table created within Power Query. You then use the manage aggregations dialogue in Power BI Desktop to define aggregations for aggregation columns with summarization, detail table and detail column properties. And aggregation tables will be hidden once complete. Aggregation tables can each choose a storage mode. And if you're using role level security, the expression should filter both the aggregation table and the detail table. So let's go to the Power BI Desktop and take a look. Okay, so I am in the Power BI Desktop and I have five tables that have been brought in to my data model using direct query. So I've got my internet sales table, a date table, promotion product and sales territory, a standard star schema. So what I need to do is create an aggregation table. I'm going to do it in Power Query. So I'm going to click on transform data. And what I will do here is I'm going to go ahead and build an aggregation table. So I'm just going to go ahead and say reference. And here I'm just going to create a table called internet sales aggregation, like so. And now what I want to do is perform the aggregation. So I'm going to keep the product key, order date, sales territory, order quantity and sales amount. And I'm just going to go here and say, remove other columns. I'm going to choose these four key columns and perform a group by. I'm going to add in two new groupings, like so. So I'm creating a sales amount sum and order quantity sum that is an operation of sum on each of these columns. I'll go ahead and click okay. And now we have an aggregation table. So something that I want to ensure is making sure the data types between the aggregation columns in the aggregate table and the detail query are the same. So I can see that I've got this one right here as set to decimal number. And this one right here is set to fixed decimal number. So I need to go ahead and make sure that the data types match. So now I've got the matching. Each of these are set to decimal number. Decimal here, good on the aggregation table, decimal and decimal. So now I'm going to click close and apply. That will bring the aggregation table into the model. I'm just going to move my aggregation table over here. Now I'm going to perform my relationship. So I'm going to go ahead and take my order date key and drag it to date key. That looks good. I am going to take my sales territory key and drag it to sales territory key here. Looks good. I'm just going to move this over a little bit here. I am going to then take my product key right here and drag it down to this product key. And that looks good as well. All right, so I have all my relationships built out. Just going to move this over a little bit more again, just to spread these relationships out a little bit here. So next what I'm going to do here is I'm going to go and set my internet sales aggregation table to import. So I am going to highlight on the table itself, go down to advanced and change this to import. I'm going to get a storage mode warning, letting me know that any of the tables that are connected directly to it are going to have to switch to dual and I want them to switch to dual. So I'm going to go ahead and click okay. Go ahead and refresh the model. Now we'll see that this table is brought in as import, date, sales, territory and product are all brought in as dual. And what I want to do here next is I'm going to go in and manage my aggregation. So I'm going to right click on the table, say manage aggregations. And here's where I'm going to go and configure. So what I'm going to do here is go to my order quantity sum, go to sum, go to the internet sales in my detail table, pick up my order quantity, run the sales amount, say sum, internet sales in my detail table and choose this as some sales amount. And just like that, I have my table configured that I want from aggregations perspective. I do not need to go and actually set summarizations and add detail tables here. And this is the case when I'm doing a relationship based aggregation, which I'm doing here. I'm going to go ahead and click apply all. And now you'll notice the table becomes hidden. So the table's hidden. And this way the end users won't be able to get at this table in the report view. Okay, so there you have it, an example of how and why we want to go ahead and create aggregations, along with how we are actually going to go ahead and do that within the Power BI desktop. And with that, the end of the sub lesson.

Contents