The Power BI Composite model is an incredibly powerful tool that can really take your reports to new heights, but there are some challenges you’ll need to overcome. In this article we want to show you how to avoid the frustrations we had when creating our composite model reports.
Complex reports may contain data from many different sources and you need to connect and visualize them for your end user. When your raw data tables contain millions of rows, your reports can come up against some limitations, right? Many data analysts using Power BI process gigabytes of data, pushing past the limits of the Power BI Pro license (1024 MB per single dataset). Of course, you can use Power BI Premium for higher capacity, but as its pricing starts at $4,995, let’s forget that for now.
So, why aren’t we simply summarizing data before loading it into Power BI? There is another way to tackle this problem—building a composite model—that will help make your reports significantly smaller and faster. Sound interesting? Let’s have a closer look at what the composite model is and how to build it.
Aggregation in Power Query editor, or within a SQL query, is possible, of course, but very limiting. With the composite model, you can manage different layers of aggregation, so reports can be much more complex and interactive.
A great thing about the composite model is that, when you hit un-aggregated data, the report will still work using the slower direct query. So, the composite model is a combination of aggregation and direct queries, making your model more complex and powerful, but also much faster and smaller, than imported data.
The basic data model in this example is four tables: Reservations (direct query with all raw data), Reservations Monthly (aggregated table set for import), and two tables connecting the previous ones, Enterprise (dual mode) and Calendar (dual mode). In the composite model, you’ll be using the Reservations table for the following report, but the report itself will use Reservations Monthly (preferring the faster option) as data source.
Here’s a step-by-step tutorial on how to make your own composite model. You’ll need data in a direct query compatible source (you can check the listed ones by Microsoft), your aggregated data, and Dual Mode tables (as pictured above).
1. Create your tables
Let’s create your aggregated query in import mode. It can be complex with dozens of columns or very simple, like this one:
SELECT
[EnterpriseId],
CAST ( [DateUtc] as date ) AS ‘Date’,
COUNT ( [reservationId] ) AS ‘Reservations’
FROM [dbo].[reservations]
Add your direct query to the aggregated one (e.g., [dbo].[reservations]) as well as the Dual Mode tables needed to connect the aggregated table to the direct query one. To make it happen, create tables in Direct Query mode and then change their storage mode to Dual Mode (in Model View).
Fortunately, you can make a SQL query even with Calendar, with no need of real data in the server—but that’s another blog post. Let’s google it for now.
Now you should have loaded all four tables, with aggregated data connected to direct query data through Enterprise and Calendar (because we’ve aggregated data on a monthly basis with a possible split per enterprise). Obviously, Enterprise and Calendar tables list unique values.
2. Manage aggregations
When you have connected the tables into a proper dataset, you’ll need to use the Manage Aggregations feature. On any of the report views you can click the right button on the table with your aggregated data (in our case, Reservations Aggregated) and choose to Manage Aggregations.
You’ll get into a pop-up window with a simple table. Fill it in like this:
When the model is simple, you only need to use correct summarization. If you want to make a more complex dataset, using more aggregated tables for the same data but, for example, different granularity, then select the Precedence number (higher number = simpler and faster tables).
Friendly reminder: Save often and DO NOT connect your Direct Query table to tables through weak connections. Otherwise, Power BI might show you an empty table, you’ll get stuck, and you’ll need to force quit without saving your work—like this:
When you manage aggregations successfully, the Aggregation table will hide and you’ll use columns, measures, and calculations over the Direct Query one. Don’t worry, if you use aggregation correctly it will use your aggregated data. You just need to use the correct calculation in your aggregation table.
3. Use & verify
Now your aggregations are prepared for use in a new report. Let’s make a simple calculation with a table visual.
Let’s verify the outcome with the Performance Analyzer tool (in Menu under View -> Performance Analyzer). Hit the ‘Start Recording’ button and then the ‘Refresh Visuals’ button. As you can see in the screenshot below, it can save a lot of time if you do it correctly. The fast one is from the aggregated data source, the slow one from Direct Query.
If you are not sure whether your calculation is really using the aggregated source or not, you can use DAX Studio (a great tool from daxstudio.org). Don’t close your current Power BI report, you’ll use it within DAX Studio too. Open DAX Studio and choose your current report file as the source.
Click Menu, then Home section -> ‘All Queries’ button and then select the All Queries page on the bottom of the DAX Studio window.
When you refresh your visuals (e.g. hit the ‘Refresh visuals’ button in the Power BI Performance Analyzer tool), this window will show you whether data are from an aggregated source or not. The full black circle means it’s working. Now you can publish your report to Power BI Service, and see how it works within the cloud.
Don’t underestimate this part, as I found it quite challenging. I made the report within the Desktop application, sent it to the cloud and… found out it wasn’t working at all. When your report is loading very, very slowly, showing different error messages, or not loading visuals, etc., it’s not the behavior that the Microsoft Power BI team intended—even the composite model report should be loading quickly and working properly.
What were the challenges I found and fixed in my report? Here are my tips:
Do NOT create a calculated column in Direct Query or Dual tables through DAX.
- Your report will load slowly.
- When possible, create all columns you need in Power Query Editor.
Do NOT use page / report filters for filtering out blank values by Direct Query table column.
- Report load in Service (cloud) will be very slow.
- Make another table in import mode, connect to a dual mode table, and filter by those values (make sure it’s within aggregated columns).
Do NOT use weak connections
- For example, connected a direct query/dual mode table to another direct query mode table from a different database –more about this topic here.
- You’ll encounter errors like:
- ‘Cannot load model’
- ‘Couldn’t load the model schema associated with the report.’
- ‘There was an error when processing the data in the dataset.’
- ‘Couldn’t retrieve the data for this visual.’
- Use the same sources when possible or an Imported table (in other words, use strong connections between tables).
Make your direct queries simpler/avoid using Power Query parameters in SQL query
- Reports in Power BI Service (cloud) will need time to time verify your direct query while loading. Making it simpler makes your report load faster.
- SQL queries don’t much like Power Query parameters, so avoid them in Direct Query tables (especially when your report load is slow).
- When you need to process a very complex SQL query, and it’s possible, try to ask your backend / data team for such a view in the database model.
Don’t forget to follow all steps: Create, Manage, Verify!
When you want to make your reports faster and smaller, with additional cool features like direct query data (that comes in quite handy when users would like to have more than you already have in your aggregations), now you can choose the Composite model. I hope you can avoid the challenges I had during my first composite model report creation.
💡ADDITIONAL TIP:
If you are concerned that your direct queries will be hitting too much data, you can use the so-called ‘conditional drill’ technique. You can learn more about this from the Guy in the Cube YouTube channel, for example.
Where to go next?
- Check more blog posts written by our data team.
- Learn how to create a clean, one-page report using Bookmarks in Power BI.
- Read about how to share your Excel Sheets through Power BI Dataflows.
