RDA Corporation

Blog

Developing Tabular Models in SQL Server Data Tools – Part 1

By Bill Anton on May 16th, 2012 // 2 Comments

files

The following information is the first of a two-part piece on the new Analysis Services Tabular option available in Microsoft SQL Server 2012. 

Before we dig into the actual development process, I’d first like to provide some context to help promote a good understanding of how this new technology fits in with the Microsoft business intelligence analytics stack and why it’s going to be a big deal to companies interested in leveraging business intelligence going forward. In part two, we’ll cover the how by providing a brief overview of the process for developing a tabular model.

At a high level, Analysis Services Tabular models (hence forth Tabular) can be thought of as the “In-Memory” cousin of Analysis Services OLAP Cubes (hence forth Multidimensional). There’s obviously a lot more to it than that, but it’s a decent analogy for now. Below is a diagram of the BI Semantic Model that Microsoft SQL Server BI reps are promoting to developers and IT folk:

BI Semantic Model

As you can see, the BI Semantic Model (hence forth BISM) sits between the source data and client access tools. You can think of BISM as an expansion of the former Unified Dimensional Model (UDM). Within the BISM container, Tabular and Multidimensional are displayed side by side across the three conceptual layers. And this makes sense because the general purpose of each technology is essentially the same:

  • Provide superior query speed for analytical queries over large sets of data when compared to the alternative of running the equivalent queries against a Data Warehouse (you do have a DW don’t you?)
  • Provide encapsulation of the “Business Logic” to ensure one version of the truth
  •  Provide a layer where data from disparate systems can be combined

A note on the last point: just for the record, I am not a fan of, nor would I ever recommend, forgoing a DW and building the BISM directly on top of disparate systems. Although it technically can be done and lots of vendors like to claim this ability as a “feature,” my experience is that the hidden costs (mainly in the form of additional complexity in maintenance, extensibility, and scalability) far outweigh the upfront cost of building the DW.

Below is a list of pros and cons to consider when trying to decide if Tabular is right for your next project:

Pros:

  • InMemory storage mode provides very fast query response.
  • DistinctCount measures and expressions involving Parent-Child hierarchies perform very well with Tabular InMemory storage.
  • DAX has a much flatter learning curve than MDX.
  • There is out of the box support for lots of data sources (relational, multidimensional, flat file, and data feeds).
  • Tabular requires a less complex development effort, there are no attribute relationships to define or aggregations to build.
  • Tabular can be used as a source for Power View.

Cons:

  • Hardware needed to support InMemory storage mode for large data sets can be cost-prohibitive. If this is the case, Multidimensional is a good option to consider.
  • DAX doesn’t quite have all the flexibility of MDX when it comes to certain complex calculations.
  • Many to Many relationships are not supported and can be difficult to emulate with DAX.
  • Tabular doesn’t support Data Mining Structures or Models. For this you will need to stick with Multidimensional.

In summary, Tabular is a great option when the business priorities call for rapid development of small to medium data sets where query speed is a top priority.  Also, being able to run Power View reports on top of Tabular models is a very strong factor in favor of Tabular models. After only a handful of demos to current and potential clients, Power View has been an absolute jaw-dropper It will be a very important piece of the BI stack moving forward.

And for those situations where we are faced with larger datasets and complex modeling scenarios, I’d recommend starting with a Multidimensional model. You can always build Tabular models on subsets of the larger dataset in order to reap some of the benefits that come with Tabular models.

Stay tuned for part two, where I’ll walk us through building a Tabular model.

For more information on RDA’s capabilities related to business intelligence, check out our BI capabilities page.

Image Source

About the Author: Bill Anton is a former Software Engineer with RDA. His primary focus is designing and implementing data warehouses and business intelligence solutions based on the Microsoft platform. He is passionate about turning data into information!

  • Kpax

    Very informative post.

    Thanks a lot Bill.. This is what I was looking for.

    • Bill

      Thanks Kpax – glad you found the information helpful.