By Nick Sutherland

Most of the BI professionals I’ve spoken to use enterprise BI – that is, they create analysis databases and reports on behalf of their business users, rather than letting the users explore the data warehouse themselves. The SSAS multidimensional model is most commonly used, with few using the newer tabular model for enterprise BI. I think this will change.

As organisations generate larger volumes of their own data, and external sources like web analytics become more important, it is increasingly difficult to make business decisions based on fixed-format reports. Self-service BI addresses this problem by making it easier for decision makers to explore data themselves. Excel is already the tool of choice for many users analysing organisational information, so extending this with PowerPivot was the next logical step for Microsoft, and it’s not surprising to see much of this self-service functionality will be built into Excel 2013.

Self-service BI tools like PowerPivot and PowerView can enrich and accelerate the decision-making process. But there also intrinsic disadvantages to the self-service approach:

Derived metrics or key performance indicators may be calculated differently by competing groups across organisations

• Documents containing business data may pose a security risk, and the data may be out of date
• User-created reports may become prototypes for new formats, which need to be deployed in a managed way throughout organisations

As self-service reports become more common, DBAs and BI professionals will be asked to resolve to the problems above by merging these documents into the enterprise BI framework. The easiest way to do this is to merge PowerPivot documents into a SSAS tabular model.

Therefore, BI professionals might wonder whether to port existing BI projects to the tabular model, or use the tabular model for all their work. Marco Russo and Alberto Ferrari gave a great presentation on the subject at this year’s TechEd conference. Microsoft also provides some guidance. The message from both seems to be:

• Tabular offers performance benefits; disk speed is less important, but you need a lot of RAM
• Tabular is better for complex many-to-many calculations (but you have to code them in DAX), and faster at Distinct Count
• Multidimensional is better with hierarchies, and supports language translations. It is also best for very large and complex models that would not fit in RAM if implemented with tabular
• Existing users of the multidimensional model should carry on using it, but consider the tabular model for certain projects
• Tabular is easier to learn, so new SSAS users should start there unless they need translations or strong hierarchy support

Right now, SSAS Compare works with multidimensional models. We’re considering adding support for tabular models – as one of our recent usability session participants said, “tabular’s the future!” Do you agree? Make your opinion known on our UserVoice page.