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

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 (7)

  1. 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

  2. Jaime Tarquino says:

    I'm glad to hear was helpful.

    Thanks

    -Jaime

  3. Ben says:

    Thanks! I followed your guide but I keep getting this error when I run the job:

      Code: 0xC0016016

      Source: Package

      Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

    End Error

      Code: 0xC000F427

      Source: Analysis Services Processing Task

      Description: To run a SSIS package outside of SQL Server Data Tools you must install Analysis Services Processing Task of Integration Services or higher.

    End Error

    DTExec: The package execution returned DTSER_FAILURE (1).

    Any idea where this is coming from?

  4. Dinup says:

    Thanks a lot….

  5. Hashir Habeebulla says:

    Excellent help.

    But the final stage i got the same error of Ben. Whats the solution ?

  6. Jaime Tarquino says:

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