Using Integration Services with tabular models

  • Article

The astute user may notice that the Analysis Services tasks included with Integration Services in BIDS are unchanged for Denali. That is true. The good news is that you can use Integration Services with tabular models. Because the underlying structure of a tabular model is the same as the underlying structure of a multidimensional model, the tasks we already had “just work” with tabular models.

I thought about giving you an example of how to use Integration Services to work with tabular models, then I thought better of it. You are better off reading some of the great examples that already exist.  Vidas Matelis has a great example SSAS partition management script you can look at. You can also review the Project Real white paper by Dave Wickert – at the end there is an illustrated step by step process for automated partition creation. The only thing you’ll do differently for tabular models is that you’ll throw in a Process Recalc at the end as needed after certain processing operations.

There are some differences between multidimensional and tabular models when working with Integration Services. The differences are summarized below.

Connection Manager

You can only establish a connection to a tabular model by specifying a server name and database. You cannot make a reference to a tabular project in your solution.

Analysis Services Processing Task

The Analysis Services Processing Task works with tabular models. The task may look a bit funny, since it exposes the underlying UDM structure of the cube, but it works just fine for processing. Some usage notes:

  • Only some of the processing options are appropriate for tabular models. Process Full, Process Default, Process Data, and Process Clear are fine processing choices for tabular models. Process Indexes, not so much.
  • Processing options that apply only to tabular models do not appear in this processing task. If you want to Process Recalc (and you do want to do this after a Process Add, Process Clear, or Process Data), you have to use the Execute DDL task instead.
  • There are lots of buttons and configuration options that don’t work in the UI. Impact Analysis doesn’t work. This is harmless.
Analysis Services Execute DDL Task

This task is fully supported and works great with tabular models.

Partition Processing Destination

This destination is not supported for tabular models.

With those limitations in mind, go ahead and use Integration Services with tabular models. Have fun.