Analysis Services · BISM

So, what is the BI Semantic Model?

Over six years ago now I wrote what proved to be a very popular post here on my blog called “So, what is the UDM?”. It was written in response to the widespread confusion around the time of the release of Analysis Services 2005 about what the term “UDM” actually meant. In a sense “UDM” was just another name for an Analysis Services cube, but it also represented the nebulous concept of Analysis Services being a semantic layer suitable for all your reporting needs; however people often thought it was a new product that somehow replaced Analysis Services cubes and got themselves tied in all sorts of knots as a result. Thankfully, use of the term died out pretty quickly as everyone started referring to Analysis Services as, well, Analysis Services.

Fast forward to the present day and I can see a similar amount of confusion about the term “BI Semantic Model” or BISM for many of the same reasons. What is the BI Semantic Model exactly? It is… Analysis Services 2012 plus PowerPivot. Let’s be clear though: it is not just the new Analysis Services Tabular Model, although the term BISM is all too often used as if it did mean that. It’s not even a specific bit of software. Analysis Services 2012 consists of two parts, the Multidimensional Model which is the Analysis Services of cubes and dimensions that we already had in 2008 R2 and earlier versions, and the new Tabular model which is the Analysis Services of tables, relationships, in-memory storage and column store. BISM refers to both models plus PowerPivot, or rather it refers to the way that Analysis Services and PowerPivot can be used as a semantic layer on top of other data for reporting purposes.

So what’s the point of a term like BISM then if it doesn’t refer to something tangible? Why not just call Analysis Services “Analysis Services” and PowerPivot “PowerPivot”? Well there’s certainly some conceptual stuff going on here (as outlined in the Vision and Roadmap blog post) but just as we had with the term UDM I’d say there’s also some marketing-led obfuscation here as well, for three reasons:

  • A single term like BISM suggests that Analysis Services 2012 and PowerPivot are a single, cohesive product, whereas the Tabular and Multidimensional models are actually very different beasts. If you’re going to be working with Analysis Services 2012 on a project the first decision you’ll have to make is which type of model to use, and if you change your mind later you’ll have to start development again from scratch and learn a lot of new skills. I hope one day that the two models will merge again but it won’t happen soon.
  • Microsoft has correctly identified that many people want to do BI but were put off by the complexity of building Multidimensional models in previous versions of Analysis Services. The simplicity of the Tabular model goes a long way to solving this problem; Tabular also replaces Report Builder models which were really a just a simple semantic layer for people who didn’t like SSAS or had valid reasons to stay with relational reporting. In order not to scare off this market a new name is necessary to avoid the negative connotations that come with “Analysis Services” and “cubes”.
  • Calling something a “semantic model” suggests that it’s a nice, light, thin, easy-to implement layer on top of your relational data warehouse, with no data duplication (which is often seen as a Bad Thing) involved. In actual fact anyone who has used the Multidimensional model will know you almost always use MOLAP storage which involves all the data being copied in Analysis Services; and I suspect when people start using the Tabular model they will be using it in Vertipaq mode (where again all the data gets copied into Analysis Services) rather than in DirectQuery mode (where all queries are translated to SQL which is then run against SQL Server).

Now I’m not going to suggest that there’s anything wrong with these marketing objectives – anything that sells more Analysis Services is good for me – or that the Tabular model is bad, or anything like that. All I want to do is suggest that in the SQL Server technical community we stop using the term BISM and instead refer to Analysis Services Multidimensional, Analysis Services Tabular and PowerPivot so we’re always clear about what we’re talking about in blog posts, articles, books and so on. I don’t think using the term BISM is useful in any technical context, just as the term UDM wasn’t in the past, because it is a blanket term for several different things. I also think that so many people are confused about what the term BISM actually means that it is becoming counter-productive to use it: people will not buy into the MS BI stack if they’re not really sure what it consists of.

What does everyone think about this?

38 thoughts on “So, what is the BI Semantic Model?

  1. Jason Horner – United States – Hi, my name is Jason I'm an international trainer and independent consultant. I spend most of my day helping clients solve business problems mostly in the Data and Advanced Analytics spaces. Sometimes this involves various and sundry cloud technologies including Azure Data Factory, Azure SQL Data Warehouse, Azure Data Lake Store, Azure Databricks, HDInsight and Azure SQL Database. I'm fluent in several languages including: SQL, C#, Python, and PowerShell. I'm a Microsoft Certified Master of SQL Server (MCM) and have been recognized for my technical excellence and evangelism efforts by Microsoft by being awarded the Most Valuable Professional (MVP) for the last 5+ years. In my off hours I like to snowboard, karaoke, ride roller coasters and play arcade games
    Jason Horner says:

    Good post.I’m curious why you are bringing PowerPivot into the mix, Isn’t PowerPivot simply a delivery mechanism for a BISM?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Well, several impeccable sources describe PowerPivot as being part of BISM (eg But the fact you are asking this question really just proves the point I’m making 🙂

  2. The original intent for the term “BISM” was (as many readers of this blog are aware) for it to refer primarily to Analysis Services Tabular models (and I guess PowerPivot with Tabular being an evolution of PowerPivot). It first came out with the early Denali CTPs, which of course didn’t even include Multidimensional. Then, due to the customer backlash that ensued, Microsoft added Multidimensional into Denali and thereby had to say “well, actually the BISM also includes Multidimensional cubes”.

    I couldn’t agree more that it is causing a lot of confusion. There were lots of questions at last year’s PASS like “is it a cube? Is it an OLAP database?” And the presenter’s response was often to look a little flustered and say “well, let’s just call it a BISM.”

    On a related note, the terms Multidimensional vs. Tabular seem to imply that one is a cube and the other is only 2 dimensional. If this ends up with Tabular models not being referred to as cubes, I think this is an opportunity lost. The term “cube” is the only one that is capable of reaching the business user’s vocabulary, is an established term, and it sells the power of the tool (in either mode).

    I guess the term UDM 2.0 for tabular wasn’t going to fly.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Well, some of the things you say here aren’t correct: for example Multidimensional was *always* part of Denali, and so it’s not true to say it was resurrected after the big “SSAS is dead” kerfuffle. So BISM has always referred to Multidimensional and Tabular. Also I’d disagree with you when you say that a Tabular model is a cube, because I think a Tabular model is definitely two-dimensional (even if it looks cube-like from an API point of view), and calling it a cube rather than, say, a model is liable to cause even more confusion. But yes, I think we all agree there’s way too many confusing terms flying around already.

      1. I guess I was wrong about the early Denali CTPs, so technically the BISM has always included Multidimensional. And I agree this is mainly marketing claptrap that generates more confusion than excitement in the Microsoft BI community. On the positive side (sorry, I have to try …), it does at least help communicate the design goals Microsoft is aiming for (successfully or unsuccessfully) with new releases of Analysis Services.

        If I remember correctly, the UDM would give us the best of both worlds: OLAP (or more accurately Analysis Services 2000) and relational reporting …
        – More flexible relationships between entities. Rating: 4 out of 5. Much better capabilities than in Analysis Services 2000.
        – Real-time BI. Rating: 0 out of 5. I think it’s fair to say that hardly anyone uses proactive caching and it doesn’t work very well.
        – Reduce data duplication. Rating: 0 out of 5. Did anyone try to process cubes directly off the systems of record (successfully)?
        – Less aggregated, operational reporting, closer to transactional level. Rating: 1 out of 5. Yes, the modeling capabilities support this a little better, but it often doesn’t perform as well as relational reporting as soon as we have a few hundred rows on the report.

        Overall, I’d say Analysis Services 2005 did take some steps in direction that the UDM claimed, and it was a huge step forward from Analysis Services 2000 primarily due to better modeling capabilities, but it failed in other design goals.

        Fast forward to today. My personal feeling is Analysis Services 2012 will fare better in meeting its BISM claims than the UDM, and it will do so through the tabular model.
        – Ease of development. Tabular will make it easier to build and support cubes (correction: BI models). Despite my love for traditional cubes, I have to admit Multidimensional is more complicated than it needs to be.
        – Better performance for less aggregated reporting. Yes, it does perform a lot better, and there will be more reports that don’t have to be relational due to performance limitations at the transactional level.
        – Less data duplication – and maybe a little more “real” time – through DirectQuery. I’d like to reserve judgment on this one for now.

      2. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
        Chris Webb says:

        Indeed. But just to restate what my original point was (for the benefit of anyone else reading these comments too): this post wasn’t about the merits of the technology, it was about the terminology. And if you and I need to have a discussion like this about what the term “BISM” actually means, that only confirms the point I was making because the terminology is nowhere near as clear as it should be. Hence my appeal to abandon the term “BISM” and use others that are a lot more specific.

  3. You can’t ever really kill BISM, there is a BISM file: The funny thing is that you can’t use a BISM file to connect to a multidimensional database, just tabular and PowerPivot models. It’s another instance of the technology not quite aligning with the marketing. Analysis Services will align eventually, but it will take a while.

    I personally would love to get rid of BISM myself and go all tabular. However, I see in my blog analytics that the term “BI semantic model” has a lot more traction than tabular. Heck, even VertiPaq has more traction than tabular. Aligning with marketing speak, though meaningless, does help some people find more information about the new technology in Analysis Services in SQL Server 2012.

    However Chris if you ever choose to get rid of all of your BISM tags in one fell swoop, I’ll do the same.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Maybe if we can all agree on Tabular and never utter the B*SM word again, that will kill off the search engine traffic in time…

  4. Thank you for this great clarification post!
    I wish Microsoft would put some more emphasis on the fact that the tabular model is different from the multi-dimensional model instead of trying to lob it all together as a Semantic Model. I like the tabular model approach (although I am somewhat concerned, as you seem to be, about the “no migrate path” between both models), but think that the multi-dimensional model will remain popular because that’s what everyone in data warehousing has been taught…

  5. Chris, I agree with you. BISM is a fuzzy thing.
    And what a strange idea to present two analysis services.
    My idea is it should be nicer to have analysis services, powerpivot services and powerpivot for sharepoint.
    But It is good to be able to query those 3 BISM services with one language (MDX).

  6. brokenninja – London, Surrey – I'm a Microsoft BI expert with extensive experience designing and building BI solutions with Microsoft technology from SQL Server 7 through to SQL Server 2008 with ProClarity, Sharepoint, excel, reporting services and bespoke application and web development. I also have knowledge and experience of the next generation of Microsoft BI technology being released under BI Semantic Model, and large scale data warehousing with the Microsoft appliance fast track. Available for contract positions.
    brokenninja says:

    Just focus on the tech I say. Which is clearly defined as more tangeable bits of software that have very specific names and does very specific things.Technical folks will get to grips with what’s what, the problem is when the business folks start passing around these terms as solutions without understanding what’s involved in terms of complexity, skills and hardware.

    Personally I never had a problem with the UDM as one stop vision to feed the reporting layer. The only problem I had was that it was a dream which made the term meaningless. Reporting tools in the stack integrated comparitively poorly with SSAS and it didn’t always make sense to use SSAS for all reporting needs due unsuited performance profiles of queries. I even remember demo’s encouraging folks to build cubes straight off of their transactional data stores, does anyone do that? I can understand the need for flexible and speedy delivery of BI but the UDM wasn’t it, that’s why I think the term died.

    Am really excited about the new stuff and I would prefer to focus on the specific tech involved, best practice and where and what should be done and used. Before we wrap it up into the all singing and all dancing branded noun it has just has to:
    – Perform well
    – Integrate well
    – Be slick to implement
    – Be flexible and easy to change

  7. Thank you for clarification. From a customer point of view, I always thought B**M would build a technical join between the multi-dimensional and the tabular world. (I also thought UDM would do that a few years ago.)
    To see I was wrong I had to download Denali RC0 and see the “Do you want to install SSAS in tabular or in OLAP Mode” question. So can anybody describe the real technical advantages of the tabular model? And if the pre-Denali Version of Powerpivot for Sharepoint stores its data in SSAS 2008 R2 Cubes (so there is a transition from tablular to multidimensional data??) What is now the relationship between Powerpivot, Powerpivot for Sharepoint and SSAS 2012 in tablular mode?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      There are a number of real technical advantages for using Tabular, in my (still limited) experience: performance on distinct count measures is way better than Multidimensional with less processing overhead; detail level reporting performance is much better; and some calculations are much faster in DAX than MDX. It’s also much easier to develop with than Multidimensional if you’re not used to the concepts of cubes and dimensions. So it is definitely something that is worth using.

      There is no easy transition from Multidimensional to Tabular, even if I’m sure we’ll see some tools appear that do some of the work involved automatically. PowerPivot V1 and PowerPivot for Sharepoint in 2008 R2 both store their data in what is effectively the same engine that is used in Tabular, ie the Vertipaq engine (and not in Multidimensional cubes) so there’s quite a close relationship between PowerPivot, PowerPivot for Sharepoint and SSAS 2012 Tabular – they’re all very similar.

  8. Your post does remove a lot of confusion over what BISM actually stands for. And I do agree with you that more simple the terminologies become it will become much easier (even for a layman) to perceive things.

  9. I agree. BISM = UDM. Tabular and Multdimensional without BISM are much better. Semantic models seems to be related to real practical models of theoretical schemas like relational and multidimensional views of starschemas. it is the practical implementation of starschemas in tabular or multidimensional models that we are seeing here.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      No, Tabular models can be queried in DAX and MDX. Multidimensional models at the moment can only be queried with MDX, but DAX support is promised soon…

      1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
        Chris Webb says:

        Well, I think DAX needed to be invented because it needed to be consistent with Excel formulas. But I agree, the situation we’re in at the moment with two languages is not great.

  10. Couldn’t agree more. Very well put.

    What doesn’t help is when you meet a customer alongside Microsoft, having already explained this in terms that your customer understands. I’ve done this before; only to see all confusion-clearing efforts thrown to the wind.

    They’re Microsoft, who are they to be argued with? It is, after all, their software… 😉

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.