The BI semantic model, MDX, DAX, and you

  • Articolo

When I was at PASS I fielded a bunch of questions about the BI semantic model, multidimensional (OLAP), and tabular (VertiPaq) models. I think there is some confusion about what the BI semantic model, multidimensional, and tabular models actually are, and when precisely you can use MDX and DAX with multidimensional and tabular models. Hopefully this post will clear up some of these issues. I will dive into the gory details and then provide a summary. If you are not interested in gory details, just skip to the bottom.

The BI Semantic Model

If you have not already done so, please see the official annoucement for the BI Semantic Model on the Analysis Services team blog to get the background on the BI semantic model, the multidimensional model, and the tabular model. The architecture slide shows the multidimensional and tabular architecture like so:

Let’s unpack the contents of just the BI semantic model box, and ignore the rest of the stack for now.

Say you want to write a BI semantic model. You go to one of two places: SQL Server Data Tools (formerly BIDS) or PowerPivot. You create one of three things – a multidimensional model, a tabular model, or a PowerPivot workbook. Each of these things is a BI semantic model.

Every model, be it multidimensional, tabular, or PowerPivot, looks like cubes/dimensions/measure groups/data sources/data source views/etc under the covers. They share a common Analysis Services file format. To prove this is true, crack open a PowerPivot workbook or script out/back up a tabular model and check the results. This means all models look the same to the outside world (where outside world is defined as AMO, ADOMD, MSOLAP provider and all consumers of these). It is this shared underlying structure that makes the BI semantic model work (that and marketing/branding power).

Even though we talk a lot about multidimensional and tabular being the same, and they will eventually look the same on the outside, there is a hard fork in the technology stack inside. If you are making a multidimensional model, you have measures in MDX and the OLAP storage engine. If you are making a tabular model, you have measures in DAX and the VertiPaq storage engine. The long term roadmap is for every BI semantic model to be queryable by MDX and DAX, not to allow MDX and DAX calculations to be intermingled inside of a single BI semantic model. To illustrate this hard fork, mentally draw a big red draw a big red circle around the multidimensional, MDX, ROLAP, and HOLAP pieces in the slide above, followed by a big red circle around the tabular, DAX, VertiPaq, and DirectQuery pieces. Inside the BI semantic model layer, they’re mutually exclusive pieces. I leave this drawing as an exercise for the reader as I am not allowed to modify this slide. :)

Sidebar – enforcing the DAX only restriction for tabular calculations

Now I just said above that you can only put DAX calculations in tabular models. The programming model doesn’t make this clear.

The AMO for Tabular sample adds DAX measures using the following code:

 StringBuilder measuresCommand = new StringBuilder();
AMO.MdxScript mdxScript = sandboxCube.MdxScripts["MdxScript"];
measuresCommand.Append(mdxScript.Commands[1].Text);
measuresCommand.AppendLine(newCalculatedMeasureExpression.Text);
mdxScript.Commands[1].Text = measuresCommand.ToString();
sandboxCube.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.Update);

Yes you read that right, they took DAX and shoved it into an MDX script. I just told you that MDX calculations are not supported in tabular models. How is it that this code could be correct?

Well this is the BI semantic model at work. Models under the covers are all defined as cubes/dimensions/measure groups/etc. The concept we had previously for putting calculations in models was MDX script. For the sake of time, we just reused this MDX script unit for putting DAX calculations into tabular models, and implemented validation preventing you from putting MDX into tabular models. This is how we enforce the DAX calculation only restriction for tabular.

When I talk about AMO, I talk about its lack of change from the past as a feature. “Wow isn’t that great, if you’ve invested in a lot of tools for AMO they’ll continue to work!” And that is true. However, it’s also a usability bug. The API is not intuitive for tabular models. We originally wanted to modify AMO so that there was a tabular version of the API, but this feature did not make it into SQL Server 2012. This is a feature we will be considering for future releases of SQL Server, but the feature doesn’t have a home yet.

Deploying BI semantic models – one instance per flavour

When you run the SQL Server setup, you get one of three kinds of instances – a multidimensional Analysis Services instance (DeploymentMode=0 in msmdsrv.ini), a tabular Analysis Services instance (DeploymentMode=2), or a PowerPivot for Sharepoint instance (DeploymentMode=1).

You can see from this that the separation between multidimensional and tabular technology stacks continues until deployment time. You have to deploy your model to the correct type of Analysis Services instance. Once you have deployed your model and are ready to start querying it, then the shared goodness of the BI semantic model begins.

Accessing BI semantic models – MDX and DAX queries

Now we can move our focus up a bit on that slide and look outside the BI semantic model box and talk about how the BI semantic model talks to the outside world. The slide up above looks so easy – there are some arrows between all reporting clients and the BI semantic model, and they all know how to talk to each other regardless of how they do it (DAX or MDX).

First, let us talk roadmap. The slide above is aspirational. The roadmap for achieving that aspiration was discussed at PASS in the What’s New for Analysis Services talk and I am not going to summarize or repeat it here. Hopefully my colleagues will update the team blog with the roadmap.

I will stick to what is true for SQL Server 2012 RTM. For SQL Server 2012 RTM:

  • 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/Crescent) can talk to tabular models (both kinds - VertiPaq and DirectQuery)
  • DAX cannot be used to talk to multidimensional models. This functionality is on the roadmap, but will not be present at RTM.

Issuing MDX queries is straightforward. Excel does it, the cube browser does it, SSMS has a button get at MDX queries. If you try to use MDX to query the DirectQuery model, the query fails with a reasonable error message saying MDX is not supported.

Now, issuing DAX. There are two ways to issue DAX – Power View (Crescent) can do it, or you can type it yourself. DAX queries go inside an MDX window. In SSMS, you can push the MDX query button and then type DAX in the query editor that appears. Also, you can do things like type DAX in the import wizard to import a whole table from one tabular model to another. If you try to issue DAX queries against multidimensional databases, you simply get a syntax error. 

The user interface doesn’t do you any favours in terms of helping you know that you can use DAX for querying tabular models. There is no built-in UI for entering DAX queries. We will be making DAX query editing a bit simpler with the DAX Editor sample, but the out of the box experience for querying in DAX is not great. DAX keywords will start being squiggled in SSMS in the post-CTP3 builds. (In the CTP3 build DAX keywords were not squiggled because we broke IntelliSense and syntax validation for all of MDX, one of the side effects of fixing MDX is that looks like valid DAX syntax is not supported).  We have some ideas for improving this experience in the future, but you’re stuck with this for now.

Summary

To summarize the above:

  • There are three flavours of the BI semantic model – multidimensional models, tabular models, and PowerPivot models.
  • These three flavours are all totally separate during development time. Multidimensional models use MDX calculations, and tabular/PowerPivot models use DAX calculations. These three flavours of projects are each deployed to a different type of Analysis Services instance.
  • Once your model is deployed, it then looks like a BI semantic model.
  • All BI semantic models (except DirectQuery models) can be queried by MDX. For SQL Server 2012, only PowerPivot and tabular models can be queried using DAX.

Hope this helps. Please post in the comments if you have more questions.

Comments

  • Anonymous
    October 19, 2011
    When MDX is used to query a tabular model, is it translated to DAX by the query engine?
  • Anonymous
    October 19, 2011
    Good question. No it does not.
  • Anonymous
    February 22, 2012
    i've heard that when Excel with  PowerPivot runs out of memory (when the in column database runs out of memory) the data is persisted to cube (SSAS? .cub file?) is that true?