Thursday, May 20, 2010

Using Analysis Services Execute DDL Task in SSIS

SQL Server Integration Services (SSIS) is a Business Intelligence tool used to perform Extract, Transform & Load (ETL) operations. There are few tasks available in SSIS control flow to create, drop, update, and process CUBE objects. There are different types of Analysis Services tasks available in SSIS. For example:
  • Analysis Services Execute DDL Task
  • Analysis Services Processing Task
  • Data Mining Query Tasks

 This post will explain you how to use Analysis Services Execute DDL Task.

Analysis Services Execute DDL Task
SQL Server Analysis Services Execute DDL Task can be used to create, modify, and delete Analysis Services objects. The Analysis Services Execute DDL Task is similar to the Execute SQL Task, the difference is that using the Analysis Services Execute DDL Task we can issue DDL statements against an Analysis Services. The DDL statements can be used to create cubes, dimensions, KPI’s, Calculation, Cube Partitions Roles or any other OLAP objects.

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

How to use Analysis Services Execute DDL Tasks
In this example, I will be using Analysis Services Execute DDL Task to create a database in Analysis Services. To begin, suppose you have "Sales" database in Analysis Services in Development environment and you want to create the same database in new environment (e.g. Test, Production). Below are the steps to achieve this goal:
Step 1: Connect to Analysis Services in development environment and generate XMLA script of "Sales: database. Save this file with Sales.xmla name to physical location e.g. D:\Test\Sales.xmla

Stpe 2: Create new SSIS package. Add new Analysis Services connection manager. Give required Server name and leave Initial Catalog blank. Click on Test Connection to make sure it is connected and finally click OK to close Connection Manager wizard.


Stpe 3: You can add a package variable "ServerName" to assign SSAS server name. Map this variable with ServerName in Expression of connection manager properties as shown below. Make sure this variable is configured in config file.


Step 4: Create new file connection manager for xmla file. This Connection Manager will be renamed with "Sales.xmla".


Step 5: Drag and Drop Analysis Services Execute DDL Task. Rename this with "Analysis Services Execute DDL Task - Create Sales Cube". Now Double click on the task to open Analysis Services Execute DDL task Editor. Set Connection as Name "localhost.SSAS", Source Type as "File Connection", and Source as "Sales.xmla". Click OK to complete.


Step 6: Save this package. Now you can move this package along with sales.xmla file and config file to any environment and execute. It will create Sales cube in that box. Make sure that the path for xmla file is same as development environment othewise you need to add the path in config file to make it independent of environment.

Note: Before executing this package, change the value of ServerName variable with current environment in config file. Click here to see how to create config file in SSIS.

5 comments:

  1. Hi Hari,
    Beneficial article. My question: I would like to build cube DBs in different servers; How to modify the cube DB name and data source (DS) information when creating cube DBs in SSIS package at runtime?

    Thanks!
    Amir.

    ReplyDelete
  2. Hi Amir,
    Once you are done with cube development, create xmla file and execute this as I mentioned in my article.

    However, to modify Data Source & connection string for cube DBs, Find and Replace existing value with new environment value using Script Task prior to executing Analysis Services Execute DDL Task.

    If you wish, I can post an article about how to Find and Replace a string using Script Task.

    ReplyDelete
  3. Thanks Hari. If you could post it, that would be much appreciated.
    I've found a programmable way in:
    http://www.cozyroc.com/ssis/script-task
    But if there is any simpler way that would be great.

    Thanks.
    Amir K.

    ReplyDelete
  4. hi
    can u please post an article to Find and Replace a string using Script Task.

    Waiting for your reply

    ReplyDelete
  5. @Zenith - I have recently posted an article to Find and Replace a string using Script Task. Here is the link:
    http://sql-bi-dev.blogspot.com/2010/09/ssis-script-task-to-find-and-replace.html

    ReplyDelete

Here are few FREE resources you may find helpful.