Ever seen something like this ...?
The Power BI “Composite”-Model is available! (Currently Preview)
A milestone, a game changer.
And then why this title!?
Let's start at the beginning ...
What is “composite”?
The most efficient and fastest storage and query type is that of Power BI itself, namely “tabular” - the data is highly compressed in the memory and can therefore be queried quickly and securely and all relationships between the data are also included for quick filtering - this tabular model is the "dataset".
Even if the source were database tables or Excel files before, once they are imported, they are part of the tabular model, so the “Import” mode is always to be preferred.
With the use of a central dataset for several reports with the source as AAS or PowerBI (so-called “Live-Connection”), it was no longer possible to connect additional sources.
And now it's possible!
What is the big advantage of “composite”?
It is not only technically possible to connect tabular models in the memory with other sources and models.
The milestone is that self-service BI is truly coming to life from a business perspective.
There can be a central, aproved enterprise model and the possibility for key users to connect further business sources, whether it is the budget figures from Excel or raw material prices from the Internet.
Another possibility is to add more detailed data to the fast online data with direct database queries, so that not all very granular lines have to be in the model.
There is something similar as “aggregations”, but is not possible for live connections either.
Where is the danger now?
Not because the technology is slow, but because a model needs to be well thought out and designed and you have to understand what happens when you query it.
It is tempting to add a table right away.
A little example
Our central enterprise model has summarized sales per product per day.
This is loaded as a dataset to Power BI Service and is available as a base for all reports.
The very detailed individual booking lines, per customer, shop and product, are rarely needed.
These can be added to the model as direct database queries.
Both can be used in the report and same filters are also used for both.
The online model is linked to the tables for direct queries via relations.
And that is the key point!
How can a tabular model join with a relational table?
Power BI tries to generate an efficient and high-performance query here, sometimes it works with DAX alone, sometimes a combination with SQL.
The danger: the join
The join is sometimes resolved using single value lists and unions ...
In this example model, there are still relatively quick results.
Of course, we always come across with models that are poorly designed and therefore slow, but here it is a perfect and correct model.
The next attempt was with a real complex model.
I only integrated one external table via an integer key and the DAX statement had 50.000 rows! The query became extremely slow.
And this is not about the size of the connected object, but the tabular model, depending on the relation and of course the query itself.
The exact same query becomes a simple DAX again.
This will not always be possible, especially if you want to avoid large amounts of data in the model, but you could possibly use “aggregations” and the “dual” mode.
“Composite” for data sets and AAS is a great innovation and brings enormous possibilities for business application and self-service BI, a milestone of Power BI!
But the model has to be designed and tested with great care, which in turn contradicts spontaneous self-service.
Even perfect models can generate strange queries.
At the moment the feature is still in “Preview”, there will definitely be improvements here.
Used properly "Composite" with datasets will give limitless possibilities for enterprise models with self-service BI.