In composite model both import and direct query data can be combined in one Power BI model. Imagine you want to create a report of sales and budget data, sales data comes from a SQL database and it needs to access in Direct Query to show live data but budget comes from an Excel file that must be imported into the model.
The Direct Query tables can be linked to Import tables:
A combination of data from both parts can be used in creating new measures:
And use the measure with Direct Query and Import columns, all in one visual:
Profiling SQL Server on refreshing the visual shows a query sent to the database and get SalesAmount aggregated by year and month, then the result is combined with budget values in the model:
Using Aggregation in Composite Mode
Aggregation is a feature can be used in Composite mode to able Power BI engine to switch between Direct Query and Import modes for having the best query performance.By using Aggregation the aggregated queries are handled by in-memory cache data while transaction-level queries are sent to underlying database or big data engine.
Imaging we have Sales table with billions of records and is required to create a pagination report showing data in transaction level by a table visual and another report to demonstrate Sales data aggregated monthly for each product. In this case:
- Sales table is recommended to be in Direct Query mode because importing whole data into the model in every refresh takes a long time and it consumes a large cash size.
- But using Direct Query tables when the visuals need data aggregated by month and product’s attributes makes the report too slow due to performance of queries which are sent to underlying database .
By using aggregation we can create a new table in Import mode with a higher granularity (on daily and product in our example) then map it to the main table and enjoy both Import and Direct Query benefits.
Here I quickly show how to setup aggregation, however for more details you can refer to Reza Rad’s posts by the link below:
Below is the diagram for my example includes InternateSales as fact and other tables as dimensions, all are in Direct Query mode:
First as Aggregation is still in preview we need to tick the box in Power Bi preview feature and restart your Power BI Desktop:
Now we need to create a new table for sales aggregated on Order_Date and ProductId by using either Power Query or a database view and then change storage mode of the new table to Import. In Power Query we create a new table as a reference of InternetSales and then use Group By to build the aggregated values:
The next step is linking the aggregation table to Product and Date table:
The important point here is Product and Date are still in Direct Query mode and using any of their attributes with InternateSales_Agg columns in a visual causes a query to be sent to the SQL Server. For this matter Power BI has introduced Dual as a new storage mode that allows tables be in both Import and Direct Query modes at the same time. Dual mode basically creates a copy of the Direct Query table in memory and use it when the rest of data come from Import tables with no need sending query to the data source.
To change the storage mode of Product and Date tables to Dual, form the Properties pane and Advanced section choose Dual:
By changing the storage mode of Product to Dual, you will be asked to confirm for converting ProductCategory and ProductSubCategory as they have a strong link to Product.
Now everything is ready to define the aggregation over InternetSales_Agg table. Select the table, do a right click and choose Manage aggregations to get to the dialog:
In aggregation dialog, the columns of InternateSales_Agg needs to be mapped to their relevant column in InternateSales table by using one of the summarization functions from the dropdown:
Except for Count and Count table rows functions, each pair of detail and aggregated columns must have the same data type.
When aggregation is created, InternetSales_Agg table get hidden to keep simplicity for the end users but any query refers to InternetSales table mapped columns is internally redirected to the InternateSales_Agg. For testing the aggregation I turned the SQL profiler on and created a visual table using the below columns:
- Month from Date table
- ProductCategorName from ProductCategory table
- SalesAmount, TaxAmt and OrderQuantity from InternetSales table
As result no trace of any query can be seen in Profiler:
But when adding Freight to the visual, Power BI engine switches to Direct Query and a query is sent to SQL server because Freight is not part of the defined aggregation:
Other blogs of this series