The short answer: yes. And it’s a metrics layer and a metrics store and it’s headless BI too for what it’s worth.
I’ve been meaning to blog about this question for a long time now, for two reasons:
- Customers migrating to Power BI often ask me if Microsoft also has a product or service that can act as a semantic layer, so I need to explain that Power BI is already a semantic layer.
- I read the deliberations of the cool kids of analytics (Benn Stancil for example, who I enjoy a lot) and get the feeling that Microsoft and Power BI inhabit a separate universe that is invisible to them. This exchange on Twitter is a prime example.
The reason I haven’t blogged about this question yet is that earlier this year Aurimas Račas wrote a truly outstanding blog post on this subject, which I strongly recommend you read:
https://aurimas.eu/blog/2022/08/metrics-layers-and-power-bi/
What more could I say? Well Aurimas’s blog post has the virtue of being impartial and I thought it would be useful adding a few thoughts from the perspective of a Microsoft insider. These opinions are my own, not official Microsoft opinions, but I don’t think any of my colleagues would disagree with them.
So is Power BI a semantic layer?
Back to the main question. Whether or not we do a good job of promoting Power BI as a semantic layer to customers we certainly think of it as one internally; I see it referred to as one internally all the time. Indeed we always thought of Power BI’s ancestors Azure Analysis Services and SQL Server Analysis Services, all through their 20+ year history, as semantic layers too – who remembers the terms BI Semantic Model or UDM?. One of the points Aurimas makes is that this is an awareness problem more than anything else: because Power BI can be used as a self-service BI tool as well as an enterprise BI tool and because more people use it as a self-service tool, the perception of it as such prevents some people from seeing it as an enterprise BI tool. On the Power BI CAT team we certainly work with a lot of large customers that use Power BI as an enterprise BI tool and semantic layer successfully: Walmart’s finance team is a greate example and their recent case study here (this older video is good too) explicitly mentions that they use Power BI as a “semantic model library” on billions of rows of data.
Preference for thin(ness)
Another great point that Aurimas makes is that the current preference in BI tools is for them to be thin layers that “delegate the computational workloads to the (cloud) databases/warehouses where the data is stored”. Back when I first started in BI the debate was between MOLAP and ROLAP and while the pendulum has swung in different directions over the years we’re still arguing over the same points with Import mode versus DirectQuery. My personal opinion is the currently unfashionable one: Import mode and the Vertipaq engine will always outperform an approach that involves generating SQL against an external database, however fast and scalable that database claims to be, for anything more than basic BI requirements (I’m watching Google Malloy with great interest though, along with whatever SQL additions Julian Hyde is working on). The official Microsoft guidance is that Import mode should be your default choice and at present, as this video by Alberto Ferrari shows, the performance differences between Import mode and DirectQuery mode are significant. As the Walmart case study referenced above mentions, you can always mix Import mode and DirectQuery mode in composite models and build aggregations if you’re working with data volumes tha are too large for Import mode alone. We are continuing to invest in improvements to DirectQuery such as Horizontal Fusion and I think that will close the gap between Import and DirectQuery a lot.
DAX or SQL?
In the same way the MOLAP vs ROLAP debate has dragged on for twenty-plus years, people have always argued whether SQL is the right language for expressing BI queries and calculations or if another language – MDX when I started, DAX today – is necessary. To be honest I think if SQL was the right choice the argument would be settled by now and we’d already have a whole ecosystem of BI products allowing you to define complex measures in SQL in a way that developers found easy to understand. Even if you accept that another language is necessary (and the people working on Google Malloy agree on that point) then there’s the question of whether DAX is a good solution to the problem or whether a different approach would be better. DAX is certainly hard to learn but that’s more because of the concepts involved rather than the syntax itself; Marco Russo’s post here is a great explanation of why DAX is simple but not easy. Since the concepts are the issue I strongly suspect that any other language that was as powerful as DAX would be just as difficult to learn. What’s more we’re working on making DAX debugging easier, for example with the EvaluateAndLog function, and making writing calculations easier with the upcoming visual calcs feature, and there are a lot of other similar improvements we could and should implement in the future.
Conclusion
Will these points change anyone’s mind? Probably not, especially since these questions are religious more than anything else: you’re either a Microsoft fan or someone who would never, ever consider using a Microsoft product; you’re either a SQL die-hard or you aren’t. Does this matter? I’ve seen Power BI’s all-conquering usage figures and I’m not sure it does. I love theoretical questions about semantic layers as much as anyone else but what really matters is whether a tool is used and providing value to businesses.
I don’t know we have those questions around SQL vs DAX. DAX is really designed to have blazing fast interactivity and flexibility in the report. If would be then I would be putting SQL vs M or Python, for that matter.
>>The official Microsoft guidance is that *Import* mode should be your default choice and at present
Unsurprising. This would make more sense if tabular models didn’t rely on loading all the data into *RAM*. Speaking of pendulums, what about the use of RAM? I recall when MOLAP used to sip conservatively at memory , and an entire SQL OLAP Services instance would run on 32 bit hardware (3 GB addressable RAM with IMAGE_FILE_LARGE_ADDRESS_AWARE enabled!)
Now the quick-and-dirty answer is to just load everything into memory (“but … but that’s with 10x compression” ;-).
Given that the use of RAM is the thing that drives a higher spend with this Microsoft product, it is unsurprising that they want everyone to demand so much of it!
I have to say that I’m a big fan of tabular modeling and Power BI, but the overall user experience really has rough edges and the product falls apart in certain places (dataflows, portal, oauth2, partitioning, PBI desktop, lack of source control, etc). Also the tech support is very bad, and high priority bugs aren’t fixed.
It would be a much better tool if there was more open-sourcing. I suspect the cool kids may overlook Power BI for that reason alone. Lots of other tools are available, which have open source technologies at the core. Just like .Net, Microsoft should make the core of this tabular engine open-source, and then they should begin working a LOT harder on the peripherals (like developer tooling.) Quality tools are the things that everyone is happy to spend their money on!!!
Great article Chris
For me a semantic model is not only something with a technical function (DAX filter propagations, molap, rolap … vertipaq) but also a functionality to show a nice diagram (or table relations) for analysts (pbi/array model makers & ML models)
And then even if we need to redo the work on the power BI model, it could be useful for each lakehouses PAAS to propose a SQL semantic functionality <–at least just display the SQL gold tables and their relations…"cherry on the cake", the power bi connector which would interpret this
Every BI tool has a semantic layer and Power BI is no exception. So, the answer to this question is “yes”. However, I think this is the wrong question to ask. Semantic layers in BI tools have existed for years starting when Business Objects created their “Universe”. Power BI is no exception. While Power BI can also serve as a semantic layer for Excel, it can’t support other BI tools (i.e. Tableau, Looker, Qlik) or personas (i.e. Data Scientists via AutoML platforms, Jupyter notebooks) seamlessly. So, I would rephrase the question as “Is Power BI a **Universal** Semantic Layer?”. The answer to that question is “no”.
Tableau can connect to a Power BI dataset and has been able to for a long time; with the REST API data scientists can now connect to Power BI too; but I know that’s not the point you’re making. What would make it a “universal” semantic layer? Support for querying Power BI with SQL?
Yes, you are correct. You can connect Tableau on Windows to SSAS/Power BI using the OLEDB connector but Tableau really prefers to talk SQL, not OLAP. Similarly, a data scientist would prefer to speak DataFrames using Python instead of REST. I think you get my point, though. “Universal” means supporting multiple inbound dialects including SQL. Great post Chris.
An universal semantic layer needs a lot of parts, but in essence allows for a full separation between KNOW and FLOW aspects of information. It requires a level of interoperability not seen in current products. It should support plug-ins for businesses rule languages and platform rule languages and have an automated translation function between them. All I can say is “good luck with that”
I thought the linked article on metrics layers basically made the case for Analysis Services, as a clearly-defined and manageable layer between the sources and the visualization layers 🙂
Seriously though, I find the push to make the PBI service an all-encompassing superset of AAS, SSRS, etc. problematic, as the price then reflects that.
There are a huge number of customers who need solutions yet will never use PBI Premium.
Thanks for linking to Aurimas’ article. It highlights a continuing problem for Power BI. Microsoft can’t build features fast enough, and when it does, no one knows about it! A great step in the right direction was adding External Tools, but a huge stumble is that there is no central directory for external tools. Imagine custom visuals without AppSource, or R packages without CRAN. If you are going to implement a distributed development approach you need an index! Aurimas complains “You cannot transfer changes made in Tabular Editor directly to Power BI Desktop” but I’ll bet he’s never heard of Action BI Toolkit (pbi-tools). https://github.com/pbi-tools/pbi-tools
I still remember my first book on the Tabular Model, “Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model”
Analyze tabular data using the BI Semantic Model (BISM) in Microsoft SQL Server 2012 Analysis Services
Since then, the term “Semantic Model” has been linked to the analysis database for me.
Always a treat to read your articles, Chris.
I agree that it is a semantic layer and that’s what I teach my students in our Masters of Science in Business Analytics programme. But I also believe it would be nicer if everything were SQL (although that’s a personal preference.)
One thing to consider as an update is SQL Server 2022’s ability to be included in Power BI composite models using Direct Query. Granted, it’s very new and not everyone will upgrade to SS22 just for that, but it should be on their roadmap.
And a final thought… Many of our customers look at Power BI’s ability to share data through XMLA to outside apps/reporting tools as justification for using Power BI as a central hub for semantic models.
Hi Chris – I really enjoyed this piece. I find it bizarre how so many of the big players in the ‘headless BI’ space discuss semantic layers as if it’s a problem that’s never been considered before!
One of the things that I’m thinking about with the governance of metrics is how can we bring in visibility and democratisation. Similar to Minerva at AirBNB – how do we get to the point where an analyst in the business can commit a new metric in git, and we can have a pull request pipeline to have that serve the wider business? Ideally you would want meta-data surrounding that metric. Who wrote it, who owns it, some lineage, tags etc. Is this possible with Power BI?
Not yet, but hopefully one day…