Multidimensional and Tabular models

As mentioned in the introduction, this book only concerns itself with Analysis Services Multidimensional models. With Analysis Services 2012, however, there is a second type of Analysis Services: Tabular models. Analysis Services Tabular models do much the same thing as Analysis Services Multidimensional models—an end user would probably not be able to tell if they were querying one type or the other—but the development experience and the underlying technology of the two types of models are very different.

The development experience for Analysis Services Tabular models is very similar to that of Power Pivot: the basic concepts are relational, you load data into tables and you create relationships between tables; there's a big contrast between this and the world of dimensions, attributes, measure groups, and cubes that we'll encounter in this book. The approach the Tabular model takes makes it very easy to create simple models, but arguably makes it harder to deal with complex requirements: for example, the ability to handle many-to-many relationships is built into Analysis Services Multidimensional, but needs to be coded into measure definitions in Analysis Services Tabular.

Analysis Services Tabular also uses a different way of storing its data to Analysis Services Multidimensional. Tabular models store their data in an in-memory, column store database and this can mean that certain operations (such as distinct counts) are much faster. That said, for most data volumes, both versions of Analysis Services perform adequately and any performance differences are likely to be negligible.

Even when installing Analysis Services, you need to be aware that there are two types of models: an instance of Analysis Services can run either in Multidimensional mode or Tabular mode, and you can't change from one to the other after installation. What's more, you should have a thorough understanding of the strengths and weaknesses of both types of models before you start your project and choose carefully between them. There is no easy way to convert a solution developed for Analysis Services Multidimensional to Tabular, or vice versa. A blog post that provides a good overview of the two different models and when you should use which is available at http://tinyurl.com/ TabularMulti.