Dec 12, 2013

I’m a BIG fan of the new SSAS Tabular Models, which is a new data model that Microsoft has introduced in SQL Server 2012. In many ways, it’s a technology competing with the 'traditional' multidimensional model. To me, this is the future of Business Intelligence (BI), particularly self-service BI. The tabular data model represents a clear change to the way we've done things in the past and could very well replace the old multidimensional model at some point down the track. If you've attended one of my 10778 courses, you’ll know I get very excited as we approach Module 10. That’s the point in the course where we leave the old, musty stuff behind and look to a shining future where we all can understand what people are talking about. So, what’s so different about it? Basically, we make use of the excellent xVelocity engine, which is now built into the SQL Server Analysis Services engine. Well, sort of. In fact, you will need to install another instance of SSAS, but with the ‘Tabular’ mode (not ‘Multidimensional’ mode), xVelocity is already built in. “That’s great Adam, but where do you get one of these Tabular Data Models? I’m sure they don’t just appear magically when you install SSAS Tabular”. Well, you’re right; you do have to build it yourself but fortunately, it’s ridiculously easy to do that. You could even build your model using Excel 2010 (or 2013 if you’ve got it). Yep, I said Excel, the best product Microsoft has ever produced. I’m not going to go into the details here (that’s what the course is for), but I will note the good and not-so-good things about Tabular Data Models at present. Remember, it’s an evolving technology and promises a great future. Let’s get the not so good stuff out of the way first, and then we can focus on the goodies.

The Not-So-Good

• Tabular models still have fewer capabilities than traditional SSAS cubes, which might make it hard to justify using them particularly if you have the multidimensional skill set already.
• Tabular SSAS is a version 1.0 product.  As such, it has a list of well-known design annoyances and features that will improve in the next version (SQL Server 2014).
• The DAX expression editor in PowerPivot and the Visual Studio-based SQL Server Data Tools (SSDT – new name for BIDS) designs are quirky.  You have to know its strengths and weaknesses, and be willing to use it knowing there’s plenty of room for improvement.
• The entire tabular model must fit in memory.  There’s no swapping, caching or distributed processing option available for large models. If you've got a lot of data, you’ll need a lot of memory.
• With 40 or more tables and some complexity to your model, the version 1.0 tabular model designer can become cumbersome and error-prone.  On occasion, you’ll need to shut down Visual Studio altogether and then fire it back up.
• Every time you rename a table or column, every time you change a single property setting, the information is written to the model workspace database on the local SSAS tabular server. After the changes have been committed, control is given back to you and the model designer.
• Some key SSAS features are not currently supported such as parent-child hierarchies, cube actions, many-to-many relationships, and display folders. There are methods (workarounds really; check out www.codeplex.com) to enable these capabilities, but they’re not supported to the same degree as they are in multidimensional SSAS.

The Good

• I have no doubt that this in-memory, tabular model technology is the future. It makes sense: it’s easy and very accessible. It’s not just me; many people have come to a similar conclusion. For example, Oracle has just recently made a major announcement that they’ll be adding in-memory column store to their 12C product (that also shows that innovation doesn’t just flow one way incidentally).
• Data that resides in memory is faster than data on a disk. Duh! Its efficiency is improved further because you don’t need indexes, partitions, file groups and other things to try and optimise on-disk data stores. Check out my previous blog on Hekaton.
• Many useful and valuable features are supported now, and they work very well.
• Upgrading your PowerPivot model to a full-blown SSAS tabular model is seamless. This allows your business users to author models with a familiar tool i.e. Excel, and then promote them to a server hosted environment.
• Tabular models are managed by SQL Server Analysis Services. Although some components still have wrinkles or are not supported, the core product is mature. That simply means that many of the features will certainly be available in future builds.
• Any client applications that support SSAS multidimensional will also support tabular. Anything built to work with SSAS cubes natively will work with PowerPivot and tabular as if it were a cube.

That’s a lot to take in, but I think it’s safe to say that SSAS Tabular Models are here to stay and it's something that is very worth your while studying. It may very well be the next thing you’ll be doing.