Excel Services & PowerPivot for SharePoint

Troubleshooting Excel Services and PowerPivot for SharePoint rendering and refreshing in the browser.

Upsizing PowerPivot 2013 Workbooks to SSAS for Knowledge Workers

Upsizing PowerPivot 2013 Workbooks to SSAS for Knowledge Workers

  • Comments 2
  • Likes

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.

  1. Stability
    • 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.
  2. Performance
    • 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.
  3. If you want to have a PowerPivot workbook larger than 2GB your only options are to upsize or only use it in the Excel client (64bit), SharePoint only support files 2GB or smaller. 

 

Issues to consider before upsizing:

  1. Your administrators will need to install and configure a tabular instance of SSAS in your network.
  2. You will need to install “SQL Server Data Tools” on your system.
  3. 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”
  4. If you were not using the workbook as a data source you will need to create a separate workbook to view the data.  This would be in cases where both the data and the pivot tables that view the data are stored in the same workbook. 

 

Steps to upsize a PowerPivot workbook:

  1. Have your administrator setup an tabular instance of SSAS in your network
  2. Create a file share somewhere where both the service account used in step 1.c and the users building the model have full control.
  3. On the client PC that will author the model install the SQL Server Data Tools (formerly called BIDS) from the SQL Server 2012 media.
    • 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)

     

  4. Finish the installation wizard.
  5. Install SQL Server 2012 SP1 on the client computer, this allows for support of Office 2013 workbooks.
  6. Copy the workbook you want to upsize to the fileshare created in step 2
  7. Start the “SQL Server Data Tools” program
  8. Pick “Business Intelligence Settings” in the “Choose Default Environment Settings” popup (see below)
  9. Create a new Project.  Menu: File \ New \ Project
  10. Within the “Business Intelligence \ Analysis Service” template pick “Import from PowerPivot” project type.
  11. Enter a good project name, this project will hold the model and build the database on the SSAS server.
  12. Enter the tabular mode SSAS server\instance name in the next popup window (see below)
  13. Test the connection to ensure the SSAS Server was entered correctly and is functioning properly.
  14. Click “Yes” to the warning below, it is simply asking that you trust where the PowerPivot workbook is getting data from. And informing you that the data will not be imported, this is OK because the server will fetch data from the data sources defined in the PowerPivot model. If you have data in the model that is from a linked sheet that will need to be copied by hand into this solution via the “Past Append” function.
  15. Select the workbook you want to upsize in the file open dialog box that pops up next. This file should reside on the file share created in step 2 and referenced in step 6.
  16. If all goes well, several progress bars should pass by in the lower right corner of the screen and you should end up with an open Model.bim file like screen shot below:
  17. Click the Existing Connections (  ) toolbar button and review any existing connection(s) included in the model (see image below).
    • The domain account used in step 1.c should have read access to all of the data sources used.
  18. Deploy the project to the server via the menu item: Build \ Deploy
    • Should get Success message as shown below
  19. At this point you have a working data source that you can access from new workbooks and other data source consumers.  In Excel you just treat it like any other Analysis Services data source.  
  20. All is good, except that the new SSAS database is not getting refreshed with the latest data, it is a static snapshot of the data at the time you deployed it to the server.  You can setup an automated processing schedule by: (the following steps can be accomplished using the “SQL Server Management Studio” tool):
    • 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>
  21. Where DatabaseID should equal the name of the SSAS database you just created.
  22. Schedule the job to run daily or however often you want changes made in the source data to reflect in the SSAS tabular database.
  23. FYI, New data is fetched from the source data sources when the SSAS database is processed.

 

Congratulations you have now upsized your PowerPivot model to a powerful full featured SSAS database. 

Comments
  • This is one of my favorite PowerPivot features to graduate excellent personal BI developed models to a truly enterprise scalable data source.  The other key point is that many front-end reporting tools can also leverage those upgraded models.

  • do you have further steps on retrofitting the powerpivot excel file to point it to the tabular model?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment