Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Analysis Services Processing Task in SQL Server Integration Services SSIS


By:   |   Last Updated: 2008-08-12   |   Comments (2)   |   Related Tips: More > Integration Services Analysis Services Tasks

Problem
We are looking to automate the processing of our SQL Server Analysis Services dimensions and cubes.  We'd like to add this processing to our existing SQL Server Integration Services (SSIS) packages which periodically update our data warehouse from our OLTP systems.  Can you give us the details on how the Analysis Services Processing Task can be used in an SSIS package?

Solution
The Analysis Services Processing Task allows you to process dimensions, measure group partitions, and mining models in an SSIS package.  While you can process all of these objects at one time, you can also select a subset of these objects to be processed as well.  For example you may update certain dimension and fact tables in your data warehouse on a periodic basis by running an SSIS package.  As a final step in the SSIS package, you would like to process just the dimensions and measure group partitions that use those data warehouse tables as their data source.  The Analysis Services Processing Task allows you to do that.

In this tip we will walk through the steps to use the Analysis Services Processing Task in an SSIS package.   We'll create a sample package that will process a dimension and a measure group partition in the Adventure Works DW Analysis Services database that comes with SQL Server 2005.   Our hypothetical scenario is that we run an SSIS package to update the Product and Currency Rate tables in our data warehouse on a daily basis.  We would like to add a step to the SSIS package to process the Product dimension and the Currency Rate fact table, thereby updating the information available in our SQL Server Analysis Services cube. 

Create the Sample SSIS Package

To begin launch Business Intelligence Development Studio (BIDS) from the Microsoft SQL Server 2005 program group and create a new Integration Services project.   An SSIS package named Package.dtsx will be created automatically and added to the project.  Rename the package to SSASProcessingTask_Demo.dtsx then perform the following steps on the SSIS package: 

Step 1: Add a Connection Manager for the SSAS server.  Right click in the Connection Managers area and select New Analysis Services Connection from the context menu.  Accept the defaults in the dialog to connect to the local SSAS Server (or edit as appropriate if you want to connect to an SSAS Server on another machine):

Step 2: Drag and drop the Analysis Services Processing Task from the Toolbox onto the Control Flow of the SSIS package.  Edit the Analysis Services Processing Task; select the connection manager defined in step 1 above and click the Add button to select the objects to be processed:

The Process Options selected work as follows:

  • Process Incremental on a measure group partition is used to load just new rows from the fact table.  It requires additional settings which we will complete in the next step.
  • Process Update for a dimension will update the dimension with any inserts, updates or deletes from the data warehouse.

Step 3: Click the Configure hyperlink in the Currency_Rates row shown in step 2 above.    Since we have selected Process Incremental as the Process Option we need to either specify a table or view to load the new fact rows from or specify a query; we'll specify a query and assume that the stg_FactCurrencyRate table is populated with just the new fact rows to be added to the measure group partition.

Next Steps

  • If you don't already have the AdventureWorks SSAS sample projects and databases available, you can download them here to get the starting point for this tip.  Click the AdventureWorksBICI.msi link.  Also click on the Release Notes link for the details on attaching the relational database.  The default install location for the project is C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project; you will see Enterprise and Standard folders.  We used the project in the Enterprise folder.
  • Take a look at the technical article Analysis Services 2005 Processing Architecture for an in-depth discussion of the processing options available for cubes, dimensions, and mining models.
  • You can download the sample SSIS project created in this tip here.


Last Updated: 2008-08-12


next webcast button


next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, June 07, 2013 - 12:45:53 PM - Roy Back To Top

It's amazing that an article so old is useful today, I think it's because of the quality with which it did, thank you very much. Greetings from Nicaragua.


Friday, June 07, 2013 - 12:37:22 PM - Roy Back To Top

Thanks!!! :-)


Learn more about SQL Server tools