By: Ray Barley | Comments (3) | Related: 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.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
