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…

Pages: 1 2




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |