Promoting Excel Tabular Model to SSAS

PowerPivot for Excel is a fantastic addition to Microsoft’s Business Intelligence offering.  It provides a self-service capability for users to mash-up huge amounts of data from multiple sources, create advanced calculations that get refreshed alongside their pivot tables and the ability to very easily publish workbooks as web applications.  Excel users are already comfortable with the interface.  And the PowerPivot add-in is free for Excel 2010 and 2013 users. 

There are pros and cons to using any one of PowerPivot for Excel, PowerPivot for SharePoint, SSAS Tabular or SSAS multidimensional.  Melissa Coates has written a great article on when to use which type of model. While PowerPivot for Excel is a fairly robust and flexible tool for individual users, there are many reasons why you might promote an Excel Tabular model to SQL Server Analysis Services. 

PowerPivot for Excel can be a great starting point for users to decide what information they want to include in a model. It allows them to play with the data and refine the model until it houses the main information that they need to see on a regular basis, in a format they can easily consume.  What can happen is that the workbooks get emailed around and get out of synch between users.  Or the data gets too large and the model takes forever to refresh.  Or the in-memory calculations take too long to load.

When this happens you might want to promote your Excel Tabular model to SSAS, if your infrastructure supports it.  It means the flexibility for users to mash-up additional data is lost, but the gains in stability, scalability, security and other features can be well worth the move.  Microsoft has made this promotion of a model very easy to do.

1. Create a new SSAS Project

Open up SQL Server Data Tools.  In the File menu click New and then Project.  In the New Project dialog box, under Installed Templates expand Business Intelligence and select Analysis Services.  On the right select Import from PowerPivot.

Browse for a location and enter a name for your project, and then click OK.


In the Tabular model designer dialog you can enter the SSAS instance you will use for model.  It will need to be an SSAS instance running in Tabular mode.


In the Open dialog box select the PowerPivot model for Excel that you will be converting to SSAS.


It will take a minute or two to load the model into the project.

2. Check and refine your model

In the solution explorer, double click on the Model.bim top open up the imported model. 


You will see the tabs similar to the PowerPivot model near the bottom of the screen, and any warnings, errors or messages. 


Here you can do any edits to your model.  The interface is very similar to PowerPivot.  You can delete, hide or show data tabs from Client Tools by right clicking on the tab. 

You can manage the model from the Model menu.


You can edit the table properties and manage the relationships from the Table menu.


You can add, edit and manage calculations and columns within the table tab.


3. Deploy the Model to Analysis Services

From the Build menu, you can build and deploy your model.  You can troubleshoot any deployment issues by following the error messages that pop up in the Deployment dialog in the Message column.



4.  Connect to your SSAS Tabular Model

You can connect to the model through Excel or SSMS or any other client tool that can consume an SSAS model.  In Excel, you won’t be using PowerPivot, rather you will be using the SSAS Tabular model as a direct data source.

In Excel, click on the Data tab, click on From Other Sources and then From Analysis Services.


Enter the server name where you deployed your model, then select the database and model that you deployed.


You can now use this as a classic Pivot Table data source.


I’ve noticed that the Tabular model is not as robust or responsive as the multi-dimensional model when you get to larger sets of data.  This is because it does everything in memory.  The converse of this is also true for smaller sets.  Because it is in-memory it can retrieve information and do calculations more quickly for smaller data sets.  Multi-dimensional models have additional features that Tabular does not have.  Richard Lees did an interesting comparison of Tabular vs Multidimensional.

As mentioned at the beginning of this article, the flexibility of PowerPivot can be exchanged in favour of the stability and scalability of a traditional SSAS deployed model.  Having users develop their own tabular models in PowerPivot and then have I.T. manage and deploy it to a wider audience allows for a great partnership and a quick development cycle.

Microsoft MVP Summit

Last month I had the great honour of winning a Microsoft MVP Award.  Most of my friends and family don’t really understand what a big deal it is to win this award.  I was over the moon.  This is an award that Microsoft gives to folks who have contributed their time and energy to help others in the Microsoft community.  There are hundreds of MVPs around the world.  Many of them have been given an MVP every year for many years, in order to acknowledge their continued service to the community and to gain their expertise in evolving the Microsoft tool set.  They have to win it back every year.  No small feat.  I won mine in the SQL Server area of expertise, in part because of this blog that I write. It seems that the solutions I log here are helpful to a lot of people.

This week I am attending the MVP Summit in Seattle. I have found it humbling and exciting to be in the company of such great minds who are dedicated to excellence.  It is energizing to meet with like minded people who are dedicated to finding new and innovative solutions and to serving their customers in the best way possible.  I have enjoyed talking to MVPs and Microsoft employees from around the world who are experts in their fields, and am learning a lot from them.  I find it reassuring to know that there are people out there, a lot of people, who are continually innovating for the betterment of our future.  Some days I get tired, and forget that they’re out there.  I have found renewed energy in being here at the conference.  John Galt is alive and well.

There is one thing about this conference that I find really surprising.  The lack of women.  I know that women haven’t traditionally gravitated to I.T. careers, but I have seen that trend changing over the years.  I attended the SQL Pass conference 2 years ago and saw quite a few women there.  But here at the MVP conference I see only a smattering.  Why is that?  Are they too busy juggling home and work to have the time to pursue additional interests?  I’m very curious.  I would like to see more women pursue and win MVPs.  They can offer an additional perspective.  Technology is quickly becoming pervasive in our homes and workplaces. And there is no denying that, with regard to technology, the Microsoft community has a huge affect on the direction in which our society will go. I think there will be an unbalanced perspective if women do not also contribute their thoughts and communicate their values in shaping this direction. This concerns me deeply.

I head home tomorrow.  It has been a whirlwind of gaining information and connecting with my peers.  I am leaving with the understanding that with this award comes the responsibility of helping Microsoft and its community to evolve and grow.  It may be because I’m a geek, but I find that very exciting.

Compare Data and Database Schemas

I was thrilled to discover the functionality to compare database data and schemas is available in Microsoft Visual Studio.  It not only allows you to compare two databases, but it will update the target database or write an update script for you to allow you to update the target database.

Most of this functionality has existed in Visual Studio Team System Database Edition since 2005.  Team System Database edition is used by database developers and administrators and includes advanced tools for database change management and testing.  Database schema comparison is now available in Visual Studio Premium and Visual Studio Ultimate since 2010.  Unfortunately it’s not in Professional or Express.

Having the database schema compare functionality in Visual Studio is awesome for developers who straddle several worlds. It’s nice to be able to build an application that affects or relies on a database, and also be able to compare the database schemas among environments, even if building the database doesn’t fall within your purview, all within one tool. 

The comparison is really easy to do. Look in Tools->SQL Server


Select your source and target database


Click Compare and it will compare the databases.  Here I have compared the schemas of two databases.


The tool highlights the differences. In this case a new field called [newfield] exists in the table [Threads] in the source database but not the destination database.

Click the script icon to generate a SQL script for the changes to the destination database.


Or click the Update button to actually do the update of the destination database. 

How sweet is that? I know I’ll be using this regularly in all kinds of development.