In July there were many exciting new features that were released to Power BI, but perhaps the most eagerly awaited feature was the introduction of the Power BI Composite Models. While this feature is still in preview, it’s hard not to be enthusiastic about how this feature will solve problems that have been plaguing Power BI developers since the product’s release. In this post I’d like to share my top 5 reasons you should be excited about these new capabilities.

1 – Mixed Storage Capability

The introduction of the new Mixed Storage Mode now allows you to take advantage of using both imported and DirectQuery sources in the same model.

If you are not familiar with DirectQuery, then you should know that the default behavior of Power BI is to import the data from all of your selected data sources into a Power BI data model. There are many benefits and disadvantages of doing this. The most commonly considered disadvantage is that when data is imported into a data model, users will experience latency in their reports between the times that scheduled refreshes occur.

When using DirectQuery no data is imported into a Power BI data model. Instead, Power BI points directly to your data source, meaning there is no latency in the data. While this is a great benefit, users will often find DirectQuery has limited capabilities in other areas. For example, historically when you select a data source to leverage DirectQuery, no other data sources can be used in that Power BI.pbix file.

With the recent release of Power BI Composite Models (still in preview) this restriction changes. Power BI developers using DirectQuery can now select multiple data sources and those data sources can be either additional DirectQuery sources or they could be imported sources.

This is huge news and a relief to many that have struggled with this in the past. Now when you choose to use a DirectQuery source and import data from another source in the same .pbix file you will notice the Storage Mode indicator in the bottom right of the Power BI Desktop say “Mixed” if you are utilizing this feature.

2 – Storage Flexibility

When leveraging the new Composite Model preview, you will also gain greater flexibility in how each individual table is stored. Data sources can now easily be flipped back and forth between DirectQuery and Import storage modes. They way this works is when you right-click on a table in the Report View of the Power BI Desktop you can select Properties to view additional information. Here you can configure the storage mode of not only your data source, but also individual tables. In addition to Import and DirectQuery there is also a new option called Dual. The Dual storage model allows you to convert a source that is already DirectQuery to allow an individual table to be imported. The use case for this is with a table that doesn’t change very often it may make sense to switch to imported because it may help reports perform faster and latency is not an issue since the data rarely changes.

Since these three options can be a little confusing let’s list out how each are different.

·  Changing a table from DirectQuery to Import changes all tables that are part of the data source to Import.

·  Changing a table from Import to DirectQuery changes all tables that are part of the data source to DirectQuery (assuming the data source supports DirectQuery).

·  Changing a table from DirectQuery to Dual changes only the selected table to an imported table.

3 – Many to Many Relationships

Another special new capability that we gain with Composite Models is native support for many-to-many relationships. I see this as incredibly helpful to new Power BI users that are not familiar with strict requirements around relationships.

Traditionally, Power BI is only able to create relationships that are one-many. This means that one of the two tables in a relationship must have a column with unique values for the relationship to work. With this new feature the requirement that a table must have a column with unique values is no longer needed.

For those of you that have a data modeling background this may be a little different than what you traditionally envision a many-to-many relationship should be. Normally a many-to-many relationship includes a bridge table to bring the two tables together, but now with Power BI Composite Models the relationship can be directly defined between them.

This is certainly a feature to be weary of because it could produce unexpected results if your table should truly have unique records. Data integrity should always win out in favor of a feature that helps you push past your bad data. You should also note that there are currently a few DAX limitations when using many-to-many relationships.

4 – Your Voice Matters

The Power BI product team is listening. Did you read that? The team at Microsoft that builds Power BI is looking and listening carefully for your thoughts and ideas. This is exactly where amazing features like Composite Models come from.

So, do you have an idea of how to make Power BI better? Submit your ideas here and there’s a good chance your ideas may be part of the product in the future! https://ideas.powerbi.com/

5 – Never-ending Innovation

While Composite Models may seem like just another tool in your toolbelt that you can bring out when the time is right, it is also proof that the innovation within Power BI is never-ending. With Power BI Desktop updates occurring monthly and Power BI Service updates sometimes happening weekly you are regularly reminded of the constant improvements made to the product. Just think what Power BI will look like a year from now