There are many operations that we may run while working with OLAP cubes such as cube and partitions processing, executing administrative tasks, performing analysis and more… Three query languages that are used to run these operations: multidimensional expressions (MDX), data mining expressions (DMX) and XML for Analysis (XMLA). We can mainly write and execute these queries in SQL Server Management Studio, but many times we need to implement them within a flow that executes other tasks over other services (database engine, file system…)

In SSIS, the below three tasks and two components are used to execute a command over SSAS:

1. Analysis Services Processing Task
2. Analysis Services Execute DDL Task
3. Data Mining Query Task
4. Dimension Processing component
5. Partition processing component

Note that tasks can be used within the package control flow, while components are used within data flow tasks.

In this article, we will give an overview of the first two tasks provided by Integration Services (SSIS) that allows manipulating OLAP cubes and executing commands over SSAS. These two tasks are Analysis Services Processing Task and Analysis Services Execute DDL Task.

Connecting to an SSAS instance

Before describing tasks and components, we should mention that we have to use the OLE DB Connection Manager to connect to an SSAS instance using Microsoft OLE DB for Analysis Services provider.

You can add an OLE DB connection manager from the connection managers tab, and then from the providers drop-down list, you have to choose the Analysis services provider.

Then, you should enter the SSAS server in the “Server or file name” text box, configure the authentication method and select the analysis database in the Initial catalog drop-down.

Analysis Services Processing Task

As mentioned in the SSIS toolbox, it provides the ability to process analysis services objects such as OLAP cubes and dimensions.

After adding this task to the package control flow, you can double click on it to open the task editor, which is composed of 3 tab pages: (1) General, (2) Processing settings and (3) Expressions.

The General tab is used to specify the task name and description:

The processing settings tab page is where you should configure the processing job.

This tab page is composed of 3 parts:

1. Analysis Services connection manager: where you should select a connection manager to read from SSAS OLAP cube

2. Processing configuration: this part contains the object you are looking to process, you can add a new object by clicking on Add button where a new form appears:

   

3. Batch settings summary: where you can specify the processing approach you need to use, such as the processing order, error handling. You can click on the “Change Settings” button to change the current values. The following screenshots are for the batch settings editor:

   

   

You can read more about SSAS OLAP Cube processing by referring to the following documentation:

• Processing Options and Settings (Analysis Services)

The Expression tab page is where you can evaluate the Analysis Services processing task properties as expression; this page is found in most of the SSIS tasks.

• Note that the Analysis Services processing task sends your configured task as XMLA query to SSAS instance to be executed.

Passing Dynamic XMLA commands

Many times, we need to build the XMLA command dynamically using a Script component or based on some variables or parameters. In that case, it is better to use expressions. You can build the XMLA command and store it within a variable, then evaluate “Processing commands” property as an expression by using this variable. A good example can be found in one of my previously published articles: An efficient approach to process an SSAS multidimensional OLAP cube.

Analysis Services Execute DDL Task

The second task used to manipulate SSAS OLAP Cubes is the Analysis Service Execute DDL Task. This task is very similar to the Execute SQL Task used to execute SQL commands over the database. Note that DDL Statement is written in the XMLA language. As an example, the following statement is to delete a partition from an OLAP cube:

The execute DDL task editor contains three tab pages:

1. General tab page: where you can specify the task name and description
2. DDL tab page: this is the main tab page and it contains the DDL statement configuration. There are three properties we need to specify:
   1. Connection: the analysis services connection manager
   2. Source Type: you have to select the DDL statement source. You can directly write the statement or read it from an external file connection or from an SSIS variable
   3. Source: Is where you have to enter the DDL statement source based on the source type you selected; if you selected “direct input”, you should write the DDL statement directly, if you selected “variable”, you should select the variable name, or you have to select the file connection manager if you chose to read from an external file
3. Expression tab page: this tab is used when you need to evaluate some of the execute DDL tasks as expression. If you need to build the DDL statement dynamically using a script or expression task, you can store this statement within a variable and evaluate “Source” property as an expression using this variable:

Conclusion

In this article, we have explained how to connect to SSAS instance using integration services (SSIS). We demonstrated two tasks that can be used to manipulate OLAP cubes, which are Analysis Services processing task and Analysis Services Execute DDL statement. Some other tasks and components can also be used, such as Data Mining Query Task, Dimension Processing and Partition processing components, which I may discuss in the future.

• Author
• Recent Posts

Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies.

Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.

On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.

Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.

View all posts by Hadi Fadlallah

Latest posts by Hadi Fadlallah (see all)

• An overview of SQL Server monitoring tools - December 12, 2023
• Different methods for monitoring MongoDB databases - June 14, 2023
• Learn SQL: Insert multiple rows commands - March 6, 2023