BISM Tabular – Proceed with Caution

This year I’ve given a few presentations on a topic near and dear to my heart.  The presentation is titled 20 Minute Tabular Model and one of the major components of the presentation involves a high level compare/contrast with SSAS Multidimensional.  The reason this session is important to me is simple…I was part of a failed Tabular implementation 😦

Background

Earlier this year, I worked on a project where the goal was to provide ad-hoc reporting via Power View against the data in a multi-tenant data warehouse.  Now this was before the release of DAXMD (in Cumulative Update 4 for SQL Server SP1) which enabled Power View reports to run against data in an Analysis Services Multidimensional model…so by default we went with Tabular 😉

Note: As someone who works hard to embrace new technology I was thrilled to be on a project using 2 of the newest and hottest components in the SQL Server stack: Tabular and Power View.

Data Architecture

Here’s a quick diagram of the architecture:

So while there was a single warehouse for all tenants (it’s fairly easy to segment data in a dimensional model by simply having a key in all fact and dimension tables linking to the tenant) we decided to separate the data in the Analysis Services layer such that the data for each tenant was isolated in a separate tabular model.  This architecture provide us with a good bit of flexibility and simplified the following tasks:

1. Rolling out the new solution to existing tenants in a controlled manner
2. Bringing on new tenants
3. Scaling out across multiple servers and analysis services instances

Note: this may sound like a pain in the ass, maintaining 10s-100s of tabular models, but in reality, since each was identical in structure, everything could be scripted and automated with relative ease.

Reporting Layer

In the reporting layer, each tenant had their own Sharepoint site with a designated document library containing a set of standard Power View reports. There was actually a custom web application/portal through which each tenant could access the Power View reports (but that’s far beyond my expertise and the scope of this post).

Where Things Fell Apart

Despite how sexy and sound the architecture appears, we ultimately hit a brick wall.  To make matters worse, it took us over 2 months ($$) to realize this wasn’t going to work – it wasn’t until we started load testing the solution, just before the first group of tenants were scheduled to go live, that we realized there was a major performance problem.

So for the next few weeks, I spent the majority of my time troubleshooting the performance bottleneck.  This particular dataset had a lot of Many-2-Many relationships which I knew to be a potential source of performance problems w/ Tabular models…so I read every Russo/Ferrari blog post I could get my hands on.  These guys have posted extensively on the most efficient way to create a M2M-calculated measure via DAX so I ensured this pattern was applied where necessary. Unfortunately, this only resulted in a minor performance improvement. But there was still a handful of under performing Power View reports (> 1 minute load/refresh times).

I also spent a lot of time capturing the DAX queries generated by these problematic reports and reviewing xEvent traces to try and understand what the tabular engine was doing…I even ventured over to Jeffrey Wang’s blog where I may have done irreparable damage to my brain trying to decipher DAX query plans (here, here, and here).  I finally reached to out to Teo Lachev (b | t) a local SQL Server MVP, and we discussed the issue in detail.  I sent him a few traces and he was able to share the information with a few of his contacts on the SSAS product team inside Microsoft.  Ultimately the issue came down to the fact that Power View was generating DAX queries that weren’t being optimally solved by the query engine….translation: no short-term solution 😦

So we threw in the towel…scrapping the  tabular model and reverting to multidimensional. We knew DAXMD was just around the corner so the plan was to revert to multidimensional, setup some version 1 reports using SSRS, and planned on rolling out Power View once DAXMD was released and we could run Power View reports against multidimensional models.