Home > SSAS > SSAS #20 – When Analysis Services meet Integration Services

SSAS #20 – When Analysis Services meet Integration Services

I recently designed a cube in Analysis Services from scratch. After some brief research on all the different ways to process Analysis Services objects, some of the questions I had finally have a clear answer.

If you have being working on Microsoft’s BI platform, you would know that SSAS and SSIS actually meet quite often. Did you design SSIS packages for fact table ETL and dimension ETL? Yes, you did.

The real integration points between SSIS and SSAS, though, involve SSAS cubes, where we need SSIS to process the database sources into the OLAP cube structure.

A big portion of SSAS integration with SSIS involves processing data and managing measure group partitions. In SSIS, Microsoft has provided us with some out-of-box basic cube processing capabilities, and also tools for handling more complicated situations. I’ll show you how I used the out-of-box basic tasks in SSIS to process my cube.

Before I jump into the details, I want to ask you two questions. One, what are some of the basic objects in SSAS? Two, what are the main types of SSAS objects that must be processed? I’ll blog about these two questions later.

3 Out-of-Box methods to process SSAS Objects in SSIS

1. Using the control flow object Analysis Services Processing Task

To me, this is the most straightforward approach. This is the method I used. I’ll show you how I used it in this post. You will see the drawbacks of this approach from the example I will show you.

image

2. Using the control flow object Analysis Services Execute DDL Task     

This object’s functionality goes beyond just Data Definition Language (DDL). It can run an SSAS XMLA script, which can not only run DDL, but also query and process. Because XMLA script can be modified in SSIS package before it is executed, this object is actually very powerful in terms of processing SSAS objects dynamically (without drawbacks of hard-coding the SSAS objects).

3. Two data flow destinations: Dimension processing destination and Partition processing destination

These two objects allow data directly from the pipeline to be pushed into a dimension or a partition. This is the only method where data is pushed directly to SSA. The above two methods essentially tell SSA to start pulling data from a data source      

Use control flow object Analysis Services Processing Task to process SSAS Objects

It’s pretty straightforward with this approach. I’ll blog about it in my next post.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: