Hype about DirectQuery for Power BI datasets and Azure Analysis Services

There is a big hype about a new feature Microsoft provided under the name “DirectQuery for Power BI datasets and Analysis Services”:

I agree with most of what they are writing, also regarding the importance of these changes.

BUT, there is one thing I don’t like: Analysis Services on premises, especially Analysis Services Multidimensional is left out!

To go a little bit deeper into this topic it makes sense to divide the topic into two aspects:

Direct Query
Extending Models / Composite Models

Direct Query

I don’t want to go into too much details here, but the main difference between a live connection and the direct query connection is, that live connections don’t allow for combining multiple data sources. You are sort of locked into an Analysis Services model.

So from my point of view the main benefit of Direct Query is the ability to combine an Analysis Services model with other data. This will still not be possible for Analysis Services multidimensional! (and also not for tabular on premises).

The benefit for multidimensional would be much higher than for tabular, because the tabular model is by design more open to different data sources. In a multidimensional model it is not possible respectively very difficult/with many limitations to add data with many columns, text columns and so on. For example having a well modelled multidimensional cube and adding just some text comments from a Sharepoint list is impossible.

Within tabular, you can combine all the data you need in the model, so the possibility to add more data via Direct Query (alone, so without the composite features) doesn’t add so much benefit.

As the result of the limitations of the live mode rather than Direct Query mode, competitor products like Tableau are still better frontends for AS Multidimensional than Power BI.

Here is an example screenshot for the combination of an AS cube with a simple additional table sitting in a relational database. Tableau offers the possibility to combine the models, to add calculations (members and measures – same functionality as within Excel Pivot Tables on AS), and it has a nice hierarchical slicer, working with parent-child hierarchies.

So if you want Power BI to be a better frontend for Analysis Services Multidimensional, please vote for this idea:

DirectQuery for Analysis Services multidimensional

Extending Models / Composite Models

There is not much to add here. It is really a game changer, and I agree to all superlatives used in describing this. I see huge potential in my current job role in being responsible for a reporting system in a segment of our company. I’m in the middle between self service and centralized IT that is responsible for the whole company group. Composite models II (as some are calling them) can help very much in developing models on different responsibility levels (group – segment – natco – individual) and still keeping consistency and quality. I think we need a new term for “single version of the truth”. Maybe it is rather a “differentiated, sophisticated version of the truth”.

But – I don’t see chances to get this for multidimensional models so using this composite features is one further step towards moving from Multidimensional to Tabular.