Publishing Tabular Models to SSAS

This is a follow-up from my blog post last month here on SQL Server Pro about utilizing common traditional reporting tools like Excel and Report Builder with the new SQL Server 2012 Tabular models. I’ve received a few notes about some confusion concerning the processes to publish PowerPivot-generated models and the Visual Studio SSDT-generated models to SSAS 2012. I was sent a question pointing to the currently empty Books on Line entry on the “Restore from PowerPivot” technique that I was advocating.

Related: SQL Server 2012 Business Intelligence Enhancements

So, it seems like a good idea to dive a little deeper into 2 methods to publish your tabular models into SSAS 2012, again without the benefit of SharePoint 2010. In my mind, SharePoint 2012 provides the cleanest and best mechanism to publish PowerPivot-generated models because all you have to do is Save-As and publish to your PowerPivot library and then both the model and the report, if you have any charts in your XLSX, will get published automatically for you. The fact that SharePoint stores the model as an in-memory SSAS cube is golden and saves you many of these steps. That being said, let’s have at it without SharePoint and fill-in those blanks from the on-line help:

STEPS TO PUBLISH MODELS TO SSAS 2012 DIRECTLY FROM POWERPIVOT

You’ve created a PowerPivot data model that you want to share so that your business can build reports from it. Users will be able to use any tool that can connect to SSAS through the Analysis Services client drivers, including Report Builder, Excel and Power View. You cannot publish it directly from a local client SSMS because SSAS will want to “restore” a database from PowerPivot, which means you need to transfer your XLSX to the server first.

1. Share a folder from the SSAS server and add that shared folder to your local PC/laptop where you’ve created the PowerPivot Excel workbook
2. Open SSMS and connect to the SSAS 2012 Tabular instance where you want to make this model available
3. Right-click on the instance-level and select “Properties.” Click “Show Advanced Properties” and change the directory value in the 1st entry for “AllowedBrowsingFolders” to the name of the local server folder where the PowerPivot spreadsheet now sits on the server.
4. Enter a database name, which is what SSAS will name your “cube,” and now SSAS will spin that up in memory as an in-memory model when users access it, using the server’s resources, not your local laptop.

STEPS TO PUBLISH MODELS TO SSAS 2012 FROM VISUAL STUDIO (SSDT)

Microsoft no longer calls the Visual Studio templates and framework for BI developers as BIDS. It is now SQL Server Data Tools (SSDT). You can do the same things above with an interface that looks & smells just like PowerPivot. You start by choosing “Analysis Services Tabular Project” in Visual Studio, or you can start by importing the PowerPivot that you created above into Visual Studio to manage the model through SSDT. Compare these screenshots below from SSDT in Visual Studio to my previous PowerPivot model from last month here and you’ll see that they are nearly identical. One big difference, though, is that you will use a server workspace in Visual Studio to store the model in memory for in-memory analytics when creating the models instead of on your local machine as is the case with PowerPivot.

1. Right-click on your project in Solution Explorer and choose “Build.” Check for errors in your model and correct any that you find
2. Right-click on your project in Solution Explorer and select “Properties”. Set your server name, instance name and database name (cube name)
3. Right-click on your project in Solution Explorer and choose “Deploy”

When you deploy the model, you will see that same dialog box that you are familiar with in PowerPivot which processes the data from the source and then deploys the generated cube on your SSAS 2012 server.

This method is much cleaner than passing XSLX files from local machine to a server as stated in the PowerPivot scenario above. However, business users that do not have Visual Studio or are not comfortable in that environment may wish to stick in Excel. In that case, you can still import that Excel file into a Tabular Model Project in SSDT and deploy from Visual Studio instead of “Restore from PowerPivot” if you are a DBA and prefer this method to the first.

Whichever method you use, you now have an SSAS cube which you can manage by processing the cube manually, scheduling refreshes, partitioning, role-based security, etc., all of the things that traditionally went into managing multi-dimensional cube in SSAS pre-2012 and non-tabular. But your time to production just increased exponentially because of the ease of creating these models and because of PowerPivot.

I’ll strive to lay-out those management processes in up-coming blog entries for SQL Server Pro BI Blog here in this space. But you should now see that this is another area where SharePoint 2010 comes in handy. You can set scheduled refreshes from your PowerPivot models in SharePoint that will automatically process data that was updated from your data sources, so that you don’t need to manually generate jobs in SQL Server to do that.