SSAS – Create Tabular Cube From PowerPivot database

Converting a PowerPivot model into an Analysis Services Tabular model can be seen as the wormhole bridge between the Business and I.T. worlds.  Self service B.I. gives the desktop Excel user the power to create sophisticated data models without much engagement with I.T. other than securing some data sources.   This gets the business going on refining concepts and ideas that avoids typical holdups and breakdowns – such as I.T. demanding detailed requirements specifications – and the business not knowing what they want until they see it.

You have your PowerPivot model ready and sitting on the file system – the offical guide to converting your PowerPivot model to Tabular is here … http://msdn.microsoft.com/en-us/library/gg492155.aspx.   You start with running SQL Server Data Tools, you select an Analysis Services Project, and you will see “Import From PowerPivot”.

ImportPowerpivotToTabular

Choose this and then browse for your PowerPivot xlsx document on your file system (direct import from the PowerPivot gallery in Sharepoint not yet supported).   Note that if your PowerPivot database contains some static content fed in from Excel worksheets (linked tables) this data is treated as a copy and paste and is stored in the Model.bim file.

Keeping going and you will see the PowerPivot model will get imported into your Model.Bim object looking very similar to the screen layout in your Excel PowerPivot development environment.  Right click on the project and select Properties and confirm deployment location for your Development Tabular instance.  Now right click the Project and select Deploy.

Now you have ported the model created by your Excel business user into a Corporate Analysis Services cube.   Tabular will add more scalability than PowerPivot model can provide and open other features like per user security to the data.  If the PowerPivot model will still remain the master source of changes, subsequent changes will need to be reimported back into Tabular using this same process.   If you havn’t been involved in the development process then it is likely you will want to confirm the design meets your Enterprise development standards before you put this into production.  Ideally you sit the Excel user down and educate on any obvious gaps in meeting standards to ensure future PowerPivot models are more closely aligned.

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s