Report Builder and Tabular Model Databases

Here is an easy way to leverage your existing skills in traditional SQL Server reporting tools with the new Tabular Mode of SQL Server Analysis Services to get started taking advantage of the xVelocity in-memory analytics engine (VertiPaq) without needing to go through a SharePoint configuration.

First, you will need a SQL Server 2012 Analysis Services (SSAS) running in Tabular Mode. To setup SSAS in Tabular Mode, make this selection at install time and notice the new icon for SSAS in SSMS which will indicate to you visually that you are working with a tabular SSAS, not a traditional multidimensional SSAS instance:

Setup page with Analysis Services config options

Note that you can install a multidimensional instance on the same server as a tabular mode instance of SSAS, but you will want to be cognizant of the large memory requirements that the tabular databases in particular, as well as traditional SSAS multidimensional can place on your server.

Again, sticking with the idea of keeping things simple and easy to get started on this new paradigm in SQL Server BI with 2012, let’s build a simple tabular model using PowerPivot. You can also build tabular models in Visual Studio 2010 using the BI projects type in SQL Server Data Tools (formerly known as BIDS) but I’m going to stick in Excel for now since that interface has been around since SQL Server 2008 R2 was released in 2010. BTW, the interface to build models like this in Visual Studio is nearly identical to the PowerPivot Excel experience. I have, however, highlighted 3 new features in PowerPivot v2 that are new that I put into this very simple model: hierarchies and KPIs, as well as the E-R diagram view:

So perhaps you’ve done something similar in PowerPivot already but are only aware of sharing those models with report writers or your business through SharePoint. In the SharePoint interface, a report library will allow users to build Excel reports or Report Builder reports in 2008 R2. in SQL Server 2012, you can also build PowerView reports from tabular models that originated in either PowerPivot or SSDT / BIDS / Visual Studio.

But now that SSAS has built-in support for the Vertipaq engine, you can go straight to SSAS with your PowerPivot model to share that with the rest of your business. Notice in this screenshot that I have restored an SSAS database directly from PowerPivot just as I have in the past with a backup of a traditional SSAS database:

Now that my model is stored in SSAS, the server can control user access to it and keep it in server memory. This will also afford me all the same administrative features that I would expect to have with an SSAS cube such as Roles, partitions, cube processing, etc. I can also browse the cube with MDX because my model is now a cube structure within SSAS:

And since it uses Vertipaq and is column compression in-memory analytics, it is super-fast without any need to worry about building the proper pre-aggregated structures and attribute properties that go along with MOLAP-style cube projects.

Hopefully this is simple and straightforward enough that you can now start allowing users to build reports off of the model right from SSAS. Just like in a SharePoint BI site, users can access the cube from tools like Excel or Report Builder to build their reports, except that they have be a bit more savvy to connect into SQL Server instead of SharePoint libraries. But in a prototyping environment, this can be useful without SharePoint. You can just fire-up Excel to make a scorecard from the hierarchies and KPIs that I built in the sample PowerPivot above such as this:

And when I want to do that in Report Builder, I need select the tabular model database from Analysis Services like this:

And build a scorecard in Report Builder that can look something like this, which is coming directly from a SSAS Tabular database that originated as a PowerPivot model that I restored to SSAS to allow SSAS to manage it like a cube, meaning that I can access it from reporting tools through the SSAS connector:

Once you have become comfortable with this updated BI solution architecture in SQL Server, you will want to add in the SharePoint layer to this so that your solution before moving into production. That will allow your business users to better interact with your solution and the BI knowledge will be better leveraged throughout your organization with collaboration and a much more intuitive report interface through a SharePoint BI site. I will work toward adding that SharePoint layer to this blog post as a follow-up for a production-ready BI site.