…brew your own Business Intelligence

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:

ArchitectureDiagram

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 (herehere, 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.

6 thoughts on “BISM Tabular – Proceed with Caution

  1. Saul says:

    I had a similar problem, might be wrong but, is it really true that power view does not recognize the inactive relationships when cross filtering even if i used a USERELATIONSHIP function in a meadure? It was too late when i tested this 🙁

    1. Bill says:

      Sorry to hear you experienced similar problems.

      In regards to your question about USERELATIONSHIP, check this blog post by Javier Guillen…
      http://javierguillen.wordpress.com/2012/03/05/userelationship-and-direction-of-context-propagation/
      …perhaps you were using the USERELATIONSHIP in the wrong direction?

  2. MarkGStacey says:

    We’ve done a lot of BISM tabular, and I think the problem lies with PowerView not tabular. We have reports that work fantastically in Excel, try to duplicate in PV and get horrible performance….

    1. Bill says:

      Hi Mark – thanks for dropping by 🙂

      that’s definitely a good/fair point to make. Unfortunately, I didn’t test with Excel – in this case using PowerView as the frontend was an absolute requirement.

      Another point to make is that Excel issues MDX queries (not DAX) and (as far as I’m aware) Tabular breaks up the MDX and refactors some of it into DAX while the rest is pushed right down to the storage engine as MDX. So if we see better performance (from a semantically equivalent MDX query) in Excel then one might argue there’s work to be done on either the PowerView or Tabular side (probably both).

      At the time of the project referenced in the post (2013-Q1), the DAX queries generated by the problematic PowerView reports did indeed seem less than optimal (maybe that’s already changed in the product). Unfortunately, I don’t have a copy of the generated queries to share (and I’m not sure I’d be within my rights to share them openly on this blog) but I have little doubt that there is room for improvement.

      So I think the real question is whether it’s the responsibility of the PowerView team (to generate better DAX) or the Tabular team (to optimize incoming DAX)…definitely above my pay-grade.

Leave a Reply