Jan 21, 2014

I believe there's some confusion about the BI Semantic Model (BISM), multidimensional models, and tabular models. Further to this is the question of "when can I use MDX and DAX?" Hopefully, I can shed some light on this topic and help you gain a better understanding of BISM. Let's take a look at the picture below. It's quite an accurate depiction of the architecture of the BISM.

If you were to create a BISM, you could do so in one of two places: SQL Server Data Tools (SSDT, formerly Business Intelligence Development Studio) or Excel PowerPivot. You will create one of three things: a multidimensional model, a tabular model or PowerPivot workbook. Each of them is a BISM. Wait...what? Yep, they're all BISMs. If we peek behind the curtains, we'd find something that looks remarkably like cubes/dimensions/measure groups/data sources/data source views/etc. They all share a common 'Analysis Services' file format. It's this shared structure that makes the BISM work. Try it out and see it with your eyes. Open up a cube in SSDT and look at the code (right click > View Code) or a tabular model (right click on the BIM > View Code). Before we get carried away, there is a hard fork in the technology underlying all of this. If you're making a multidimensional model, you have measures in MDX and the OLAP storage engine. If you're making a tabular model, you have measures in DAX and the VertiPaq storage engine. The long term road map here is for every BISM to be queryable by MDX and DAX. I want you to take a red marker and draw a circle around each of the Multidimensional/MDX/ROLAP/MOLAP and Tabular/DAX/VertiPaq/DirectQuery bits and know with a certainty that they are mutually exclusive pieces within the BISM layer. Let's move our attention away from BISMs and take a look at the diagram above, the client stuff. The diagram makes it look very simple, however, it is a little misleading as it prompts you to believe that all the client tools know how to talk to a BISM or each other. That's the aspiration. Let's stick to what's true for SQL Server 2012, shall we?

• MDX issuing clients (Excel) can talk to both multidimensional models and tabular models running in VertiPaq (in-memory) mode.
• MDX cannot be used to talk to DirectQuery models.
• DAX issuing clients (Power View) can talk to tabular models (both kinds - country and western... er, VertiPaq and DirectQuery)
• DAX cannot be used to talk to multidimensional models (this is on the roadmap though)

As you may know, issuing MDX queries is straightforward. Excel does it; the cube browser does it, and even SSMS does it. Excellent. However, if you use MDX to query a DirectQuery model it will fail and tell you that MDX is not supported. What about issuing DAX queries? Well, there are a couple of ways to do that. As you probably know, Power View does it behind the scenes or you can even type it in yourself in SSMS. To do the latter, simply push the MDX query button and start typing. Remember the third and fourth bullet points above though, these DAX queries can only be issued against a tabular model (at this stage). So, in summary, we have learned that there are three types of the BISM - multidimensional, tabular and PowerPivot. However, these are all completely separate during development and deployed to different instances of Analysis Services too. Once your model is deployed though, it looks like a BISM. All BISMs (except DirectQuery) can be queried by MDX, and for now with SQL Server 2012, only tabular and PowerPivot models can be queried by DAX.