How to refresh a Tabular Model

 

Power View works with tabular models, the most easy way to work with them is to use the PowerPivot gallery in SharePoint and publish a Excel File with a PowerPivot model to create a Power View report on top of it, from there you can easily setup the data refresh using the SharePoint UI, there is a lot of details in this link in how to do it Schedule a Data Refresh (PowerPivot for SharePoint)

But for my case I wanted to have my model deployed in Analysis Services to use it from a SharePoint farm where PowerPivot wasn’t installed, so I published my model to AS in tabular mode but I didn’t find any easy UI tool to refresh my data. I searched in my memory how the AS cubes are processed and remembered that the tool for this work is SSIS (SQL Server Integration Services).

I decided to try and document the process in case somebody else need a quick guide. 

 

Create the SSIS package

1. Open SQL Server Data Tools and Create a Integration Services Project

2. Drag the Analysis Services Processing Task to the Control Flow Area

3. Create a New Analysis Services connection with a right click in the Connection Manager Tab.

4. Double Click the Analysis Services Processing Task you dragged in the Control Flow Area and select your connection in Processing Settings

5. Click in the Add Button for the Object List and select your Tabular model database

6. You can double check you select the proper object when the Type is Database

 

Note: If you select the cube, later on when you execute the package it will fail with the following error [Analysis Services Execute DDL Task] Error: Cube cannot be processed when Analysis Services is in Tabular mode. Please process Database object instead. (go to the end to the post to see the screenshot of the errors and the wrong option)'

7. Click in the Start button for testing the execution

And check the results in the Execution Result tab, if everything is ok you won’t get any error here

 

Create SQL Agent Job to Schedule the Package

 

1. Connect to SQL Server Management Studio and create a new SQL Server Agent Job

 

2. Give it a name, configure the schedule and set any other property you need

 

3. In the steps section, create a new step of type SQL Server Integration Services Package and select the package you just created (for simplicity I’m using file system as the package source)

 

 

Conclusion

 

This is just a simple set of steps to have my tabular model being refreshed daily, mostly for  demonstration of the capability, you should consider your business requirements when you want to implement your refresh there are more details on how to schedule packages in SQL Server Agent Job for Packages .

When implementing your SSIS package you should consider add logging and tracing so in case something goes wrong you have better information, there is a set of guidelines in Designing and Implementing Packages (Integration Services)

 

Appendix: Error if you select Cube in the objects to process in the SSIS package

 

 

 

 

This posting is provided "AS IS" with no warranties, and confers no rights

Comments

• Anonymous
  January 21, 2013
  D-U-D-E!!! You just rule!!! Thank you very much for this deatailed step-by-step review!!! AWESOME! Helped me a lot! Thanks to people like you who proactively share their experience - the world just gets better :) Respect! Michael Shparber

• Anonymous
  January 21, 2013
  I'm glad to hear was helpful. Thanks -Jaime

• Anonymous
  March 14, 2013
  thanks

• Anonymous
  May 16, 2013
  The comment has been removed

• Anonymous
  November 19, 2013
  Thanks a lot....

• Anonymous
  October 18, 2014
  Excellent help. But the final stage i got the same error of Ben. Whats the solution ?

• Anonymous
  October 20, 2014
  I have not experienced such error personally , I recommend you to ask in the integration services forums social.msdn.microsoft.com/.../home