Problem

A few months back, I was working on an OLAP cube for a client who was looking to deploy these cubes for each of the end customers so that they can use the cube and complete their reporting requirements. Initially, the requirements were pretty straightforward as each client was supposed to have only one OLAP database. We had an SSIS package setup that builds the data warehouse for the customer and then processes the OLAP cube once the warehouse is populated.

However, a new customer was on-boarded, and they had a specific requirement. They had multiple facilities over several countries and they wanted to have separate OLAP databases for each of their facilities. It was very crucial to understand if they were the only customer with such a requirement or there is a possibility of other customers as well. And after several discussions with the stakeholders, we concluded that there is a possibility of other customers too, operating across multiple facilities and they might want a similar solution.

Since we had only one ETL process and we had to process multiple OLAP databases in the same job, this caused us to modify the SSIS package and dynamically process all the cubes that belong to one customer.

For demonstration purposes, I'll proceed with the AdventureWorks database, which is deployed to several customers operating across multiple facilities/locations.

1. Client 1 - Operates across Location 1,2 and 3
2. Client 2 - Has only one location
3. Client 3 - Operates across location 4 and 5

The databases are deployed in a similar manner as in the screen print.

Please note: This article assumes that you are already familiar with SSIS components and hence provides details on how to implement the logic rather than a step-by-step guide.

Solution

This solution explains how to dynamically process multiple cubes for a client in the following manner:

1. Fetch a list of all the cubes for a particular client using the Script Task component.
2. Process all the cubes using Analysis Services Execute DDL Task.

Package

The entire package looks like this.

Step 1: Create a Connection Manager

In order to be able to connect to the SSAS instance, we need to create an OLAP connection manager that will be used by the package to perform all the activities.

1. Right-click on the Connection Managers and select New Connection Manager.
2. Select Type as "MSOLAP100" from the list and click OK.
3. In the Connection Manager dialog box, select Create a connection to a computer running Analysis Services and click Edit.
4. Enter Server or File Name as "localhost" and click Test Connection. You should have a successful connection.
5. Rename the connection as "Localhost_MSOLAP.conmgr".

Step 2: Declare the Variables

The variables in SSIS  play an important role when developing dynamic packages. Similarly, in our solution we will have four variables, namely:

1. vClientName (String): Used to store the name of the client for which the package is to be executed.
2. vOlapDatabaseList (Object): Used to store the list of OLAP databases for the specific customer.
3. vOlapDatabaseName (String): Used by the Analysis Services DDL Task to process one database at a time.
4. vOlapProcessXmla (String): To store the XMLA query for processing the database.

For the variable, "vOlapProcessXmla", I have used an XMLA script to process the OLAP database ,which is provided below. Please note, the variable "vOlapDatabaseName" has to be present before creating this variable. Paste the code provided below in the Expressions box and click Evaluate Expression. The expression should be evaluated without any errors.

"<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">
  <Process    xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"
              xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"
              xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\"
              xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\"
              xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\"
              xmlns:ddl200=\"http://schemas.microsoft.com/analysisservices/2010/engine/200\"
              xmlns:ddl200_200=\"http://schemas.microsoft.com/analysisservices/2010/engine/200/200\"
              xmlns:ddl300=\"http://schemas.microsoft.com/analysisservices/2011/engine/300\"
              xmlns:ddl300_300=\"http://schemas.microsoft.com/analysisservices/2011/engine/300/300\"
              xmlns:ddl400=\"http://schemas.microsoft.com/analysisservices/2012/engine/400\"
              xmlns:ddl400_400=\"http://schemas.microsoft.com/analysisservices/2012/engine/400/400\"
              xmlns:ddl500=\"http://schemas.microsoft.com/analysisservices/2013/engine/500\"
              xmlns:ddl500_500=\"http://schemas.microsoft.com/analysisservices/2013/engine/500/500\">
    <Object>
      <DatabaseID>"+ @[User::vOlapDatabaseName] +"</DatabaseID>
    </Object>
    <Type>ProcessFull</Type>
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
  </Process>
</Batch>"

Step 3: Get the OLAP Database List

This is a script task component and it fetches the list of databases for a specific client and stores the list in the variable "vOlapDatabaseList".

1. Double-click and open the Script Task component.
2. Update the following details:
   1. ReadOnlyVariables : vClientName
   2. ReadWriteVariables : vOlapDatabaseList
   3. Click Edit Script and paste the following code in it.

      using System.Collections.Generic;
      using Microsoft.AnalysisServices;

      public void Main()
      {
                  // Build the connection string
                  string connectionString = Dts.Connections["Localhost_MSOLAP"].ConnectionString;
                  // Fetch the client name
                  string clientName = Dts.Variables["vClientName"].Value.ToString();
                  // Create the server object and connect to the instance..
                  Server server = new Server();
                  server.Connect(connectionString);
                  // Creating a list to store names of all databases..
                  List<string> databaseNames = new List<string>();
                  // Get list of all databases in the instance..
                  foreach (var database in server.Databases)
                  {
                      if (database.ToString().Contains(clientName))
                      {
                          databaseNames.Add(database.ToString());
                      }
                  }
                  // Assign the list value to the SSIS package variable
                  Dts.Variables["vOlapDatabaseList"].Value = databaseNames;
                  // Check result in messagebox
                  // MessageBox.Show("Total databases: " + databaseNames.Count, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);
                  Dts.TaskResult = (int)ScriptResults.Success;
      }

Step 4: Creating the ForEach Loop Container

The ForEach loop Enumerator is used to iterate through the list of databases obtained in the previous step and process one cube at a time. This is achieved by binding the list variable "vOlapDatabaseList" with the "vOlapDatabaseName". This variable is used in the XMLA script to process the cube.

1. Double-click and open the ForEach Loop Enumerator.
2. In the Collection pane, select Enumerator as "Foreach From Variable Enumerator".
3. Select Variable as "vOlapDatabaseList".
4. In the Variable Mappings pane, add the variable "vOlapDatabaseName" and Index as 0.

Step 5: Configure the Analysis Services Execute DDL Task

1. Double-click and open the Analysis Services Execute DDL Task.
2. Select the DDL pane, select Connection as "Localhost_MSOLAP" (created in Step 1).
3. SourceType as Variable and Source as vOlapProcessXmla.

Step 6: Executing the package

Once all the previous steps are completed, we are ready to execute the package. This package will execute all the databases for the client mentioned in the variable "vClientName".

Takeaway

This article describes how to dynamically process multiple SSAS cubes in the same ETL package.

Other sources:

• https://docs.microsoft.com/en-us/sql/integration-services/control-flow/script-task
• https://docs.microsoft.com/en-us/sql/integration-services/control-flow/foreach-loop-container