I write this blog post as a continuation for my previous post, Perform Analytics over Large Data Volumes: Aggregations in Power BI . If you have not going through the post and newbie to Aggregation concept, I recommend to read that first.
Note: This post has written a way of you can use this as a tutorial to learn how to use Composite Model concept to leverage the performance in your data models specially when you are dealing with large data volumes.
I assume that you have downloaded the demo content for my previous article on Aggregations.
Composite Models
Our data model is in DirectQuery mode where everytime cosume the dataset concert the DAX code into SQL code and hit the backend source and fetch the data. What if we can bring some of the tables into Import mode while rest of the data in DirectQuery Mode. In simple terms this is the idea of Composite Models. You can maintain data in both DirectQuery and Import mode.
Even though we implement Aggregations in our data model, still we can do much more to optimize the query performance. In this step I'm going to bring SalesByProductYear table into Import Mode. Once it brought the data resides in in-memory vertipaq database engine. The data retrival will be faster than now.
Configure Storage Mode
In order to bring the table into Import mode, you need to go to Model tab and select the table. In this case the table is SalesByProductYear. Then go to Properties and expand Advanced section then select the Storage mode into Import.
As soon as you select the Storage mode into Import you will get a info dialog like below.
One of the statement is once you turn into Import mode you cannot make it back to DirectQuery mode. Unlike directQuery you have to refresh the data portion in vertipaq because as you know data will be update over time. Hence part of your data in DirectQuery and other part in Import there will be mismatch of your data unless you refresh the data forcefully.
Next one is because of this change weak relationships may be introduced. Aggregations will not work with weak relationships. So you have to do something for that. It says number of weak relationships can reduce by make it to Dual mode. OK here we go. This make save our effort of building aggregations.
To read more on Strong and Weak relationships read the article from SQLBI.
I unchecked the changes and going to click OK. I'll make dual whatever the required tables manually.
Now my SalesByProductYear table is in Import Mode. 😊 Let's run the query again in DAX studio and see whats happen.
Why is my Aggregation Failed?
Because, in theory Aggregation won't work part by part. Which means in aggregation query one part in Import mode and other part in DirectQuery mode. In order to make it work, you need to bring the Product table into Dual mode.
Let's try to turn Product table storage mode into Dual and see.
You will get a message like this.
I clicked OK and I got and error like this.
I'm receiving this error because in my aggregation table ProductKey column I used it from Product table. It also possible to use it from Sales table. Because, you know the Sales table is a fact table and it also contain the ProductKey column. Just other side of the relationship.
I reconigured the Aggregation as below.
Let's try again and see to bring Product table into Dual mode. This time I could brought it into Dual mode successfully.
Now we were able to implement Composite model successfully. Let's run our DAX query again and see the result.
You can see aggregation re-write attempt succeeded also if you pay attention closely you can notice, the query generated is different thank earlier, when we use DirectQuery. This is the query format generated when the data is in Vertipaq storage. And other interesting fact is the query execution time has been reduced from 69 ms to 7 ms. This is the power of Vertipaq engine.
Let's try to bring other table also into Import mode. SalesByYear table is much smaller. In this step I brought the table into Import storage mode.
Now I'm going to throw different query to my model. Let's retrieve SalesAmount by Year.
Now we have a different problem. I'm expecting to retrieve data from SalesByYear table. But actually when look at the Query result the data has been retrieve from SalesByProductYear table. We need to force the Engine to pick smaller table when there are multiple candidates. We needed to get Yearly sales and there are two aggregation tables which can fulfill the requirement.
Force Engine to Select Right Aggregation Table
In order to do this, right click the aggregation table and go back to Manage Aggregations. There is a configuration called Precedence. Which is a whole number which you can define the order of selecting aggregation should be considered by query execution. Higher the precedence, the engine will select first. By allowing to configure numeric number Power BI allows to define the order of execution specially when you have many aggregations in complex models. In that time you can maintain a Excel document and maintain the sequence properly based on the query audits.
Now my SalesByYear table Aggregation Precedence is 30 while SalesByProductYear remaining as 0. When I execute the same query again theoretically the engine should pick SalesByYear table. Let's see..
Now you can see the Vertipaq engine has picked SalesByYear table by looking at the query.
Composite Models Allows to Achieve Balanced Architecture
Composite models allows you to have Balanced Architecture. Which means you can bring your aggregated tables into Import mode while other large tables in DirectQuery mode. This feature is a great candidate for Big Data Scenario. Where your transaction fact you can leave as DirectQuery which can never fit into memory.
Summary
In this post, you learned How to use Composite Model concept along with the Aggregations. The objective is to teach the readers the concept Composite Models and how to apply in real world scenarios. I recommend you to follow same steps I did in order to achieve better results.
Download
Download the Demo content from here.
Hi Nisal, This blog is very informative, Thanks for sharing and keep posting
ReplyDeletePower BI Consulting Services
very nice and provide me informative content thanks for sharing for more information Looking for the best Power BI Business Intelligence Services
ReplyDeleteThanks for sharing information.We also offer packers and movers in manikonda, Hyderabad and across all towns and cities in India.
ReplyDeleteI think there is a need to provide more and more aspects and find utilities about Power BI and more complex tools.
ReplyDeletePowerbi Read Soap