Jaa

How to refresh a Tabular Model

  • Artikkeli

 

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

image

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

image

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

image

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

image

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

image

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

image

 

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

image

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

image

 

Create SQL Agent Job to Schedule the Package

 

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

image

 

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

 

image

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)

 

image

 

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

 

image

 

image 

 

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