Using Analysis Services Processing Task & Analysis Services Execute DDL Task in SSIS

SQL Server Integration Services (SSIS) is a Business Intelligence tool which can be used by database developers or administrators to perform Extract, Transform & Load (ETL) operations. In my previous article Using WMI Data Reader and WMI Event Watcher Tasks in SSIS I discussed how to use the WMI Data Reader and WMI Event Watcher Tasks which is available in SQL Server 2005 Integration Services and later versions. In this article I will examine the use of the Analysis Services Processing Task & the Analysis Services Execute DDL Task which is available in SQL Server 2005 and later versions.

If you are new to SQL Server Integration Services then I would recommend you to start with my introductory article SQL Server 2008 Integration Services Tasks.  

Overview of SQL Server Analysis Services Tasks

SQL Server Analysis Services Tasks within SSIS can be used to create, modify, delete and process Analysis Services objects. The different types of Analysis Services tasks which are available in SQL Server 2005 Integration Services and later versions are Analysis Services Processing Task, Analysis Services Execute DDL Task and Data Mining Query Tasks.

The SQL Server Analysis Services Execute DDL Task is similar to the Execute SQL Task, however using the Analysis Services Execute DDL Task you can issue Data Definition Language statements against an Analysis Services system. The DDL statements can be used to create cubes, dimensions, KPI’s or any other analytical processing of OLAP objects.

The Analysis Services Processing Task can be used to process analysis services objects such as cubes, dimensions and mining models.

The Data Mining Query Task can be used to run prediction queries based on data mining models built into analysis services. A prediction query creates a prediction for new data by using the mining model chosen.

Example – Using Analysis Services Processing and Analysis Services Execute DDL Tasks

In this example, we will be using both the Analysis Services Processing Task and the Analysis Services Execute DDL Task. Using Analysis Services Processing Task, I will be processing the “Adventure Works DW Standard Edition” sample database which is available for SQL Server 2008. Once the database is processed successfully, the next step will be to backup the Analysis Services Database using the Analysis Services Execute DDL Task.

Configuring the Analysis Services Processing Task

  1. Create a new SQL Server Integration Services Project and rename the default package as AnalysisServicesTasks.dtsx
  2. Double click the AnalysisServicesTasks.dtsx package to open it in Design mode
  3. Drag and drop the “Analysis Services Processing Task” from the toolbox to the Control Flow window.
  4. Double click the Analysis Services Processing Task to open up the Analysis Services Processing Task Editor and then navigate to Processing Settings as shown in the snippet below.

  5. In the Analysis Services Processing Task Editor click New… next to “Analysis Services connection manager” to open up Add Analysis Services Connection Manger window as shown in the snippet below.

  6. In the Add Analysis Services Connection Manager window click Edit… button to open up the Connection Manager as shown in the snippet below. In this screen, you need to specify the correct Analysis Server or File name, provide the authentication information and then select the Analysis service database as shown in the snippet below and click Test Connection to verify the connection to the Analysis Service Database. If the connection is successful, click OK to close the Connection Manager window. And then click OK to close the Add Analysis Services Connection Manager window.

  7. Once you are back to the Analysis Services Processing Tasks Editor window, you will need to specify the required Processing Configuration information. This can be done by clicking the Add… button which will open up the Add Analysis Service Object window as shown in the snippet below. Here, you need to select the objects that you want to process and click OK to save the changes and to return back to Analysis Services Processing Task Editor window.

    Leave the rest of the options unchanged in Analysis Services Processing Task Editor.

This completes the configuration of the Analysis Services Processing Task and it will look similar to the snippet shown below.

Click OK to save changes to the Analysis Servers Processing Task.

Continues…

Leave a comment

Your email address will not be published.