Much of the existing content for converting a PowerPivot workbook to a SQL Server Analysis Services (SSAS) tabular instance assumes that the reader is a developer. This post details the process of upsizing a PowerPivot workbook to SSAS from the point of view of a knowledge worker and or a SharePoint administrator.
This post assumes you are using PowerPivot as a data source, so no UI is present in the workbook hosting the PowerPivot model. The display of the data would happen from another workbook that connects to the PowerPivot workbook as a data source or from some other reporting tool like PowerView or Excel Services.
What is upsizing PowerPivot (aka convert PowerPivot to tabular model, aka convert PowerPivot to SSAS):
Upsizing PowerPivot is simply moving the solution from a SharePoint centric storage mode to a SSAS centric storage mode. The end result is that the PowerPivot model and data will not be stored in SharePoint.
PowerPivot stores models in Excel Workbooks when using a SharePoint centric mode and stores the models in native SSAS files when using SSAS storage mode.
The key takeaway is that the same query engine is used regardless of where the model is stored, Excel client, SharePoint or SSAS.
Why upsize a PowerPivot workbook:
You may want to consider upsizing your PowerPivot workbook after it exceeds 100MB in size for the following reasons.
- SharePoint was not optimized to host very large files and it has a hard limit of 2GB for a single file.
- You will find that SharePoint features may fail or behave oddly when you are using very large files, this is also a burden on the SharePoint system that could adversely affect other users.
- When a PowerPivot workbook is stored in SharePoint the PowerPivot model and all of the data must be streamed from the SharePoint database to a special instance of SSAS to build the database.
- If your users are experiencing intermittent poor performance this could be because the cache time has expired and the backend SSAS database needs to be rebuilt.
- In SharePoint 2013 the default behavior of Excel Services data \ “Refresh all connections” is to have the PowerPivot data completely reload itself from source data. For large workbooks this could cause poor user experience.
Issues to consider before upsizing:
- Your administrators will need to install and configure a tabular instance of SSAS in your network.
- You will need to install “SQL Server Data Tools” on your system.
- SSAS does not use the SharePoint security system, if you want to restrict who can access the workbook data you will need to configure it on the SSAS database.
- This consists of assigning Windows users and or group’s access to the database.
- Can be done with “SQL Server Data Tools”
Steps to upsize a PowerPivot workbook:
- Have your administrator setup an tabular instance of SSAS in your network
- Use the SQL 2012 media, pick Analysis Service and Tabular mode
- Make sure to have the SSAS service run under a domain account, it will need to access the file share made in step 2
- You must install SQL 2012 SP1 if you are using Excel 2013 workbook as the source.
- The “SQL Server Browser” service must be enabled and running
- Run setup, pick “Installation” then “New SQL Server stand-alone installation or add features to an existing installation”
- On the “Setup Role” screen pick “SQL Server Feature Installation”
- On the “Feature Selection” screen pick “SQL Server Data Tools” (see image below)
- The domain account used in step 1.c should have read access to all of the data sources used.
- Should get Success message as shown below
- Ensure that the “SQL Server Agent” service is running on the SSAS server
- Ensure that the account running the “SQL Server Agent” service has permissions to process the new SSAS tabular database. You can create a new role for the database that has “Process” permissions and assign the agent account to that.
- Create a new SQL Server Agent job, with one step.
- The step should be setup with Type = SQL Server Analysis Services Command, Run as = SQL Server Agent Service Account, Server = Name of you SSAS Tabular server (see image below)
- Command should be:
- <Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Type>ProcessDefault</Type> <Object> <DatabaseID>TabularProject1</DatabaseID> </Object></Process>
Congratulations you have now upsized your PowerPivot model to a powerful full featured SSAS database.