SSAS Processing

 

In this article, you will learn about SSAS Processing. Processing SSAS objects in SSIS can be as easy as using the Analysis Services Processing Task. However, if your SSAS cubes require adding or processing specific partitions or changing the names of cubes or servers, then you will need to consider other approaches. In fact, many, if not most, solutions require using other processing methods.

SSAS in SQL Server 2014 has two types of models, multidimensional and tabular. Both of these models require processing. For multidimensional models, you are processing dimensions and cube partitions. For tabular models, you are processing tables and partitions. However, both models have similar processing options.

Enhance your IT skills and proficiency by taking up the "SSAS Training". Enroll now!                                                                                                                                                                                          

SSAS Process

Learn - SSAS Processing

The primary ways to process SSAS models through SSIS include the following:

• Analysis Services Processing Task: Can be defined with a unique list of dimensions, tables, and partitions to process. However, this task does not allow modifications of the objects through expressions or configurations.
• Analysis Services Execute DDL Task: Can process objects through XMLA scripts. The advantage of this task is the capability to make the script dynamic by changing the script contents before it is executed.
• Script Task: Can use the API for SSAS, which is called AMO (or Analysis Management Objects). With AMO, you can create objects, copy objects, process objects, and so on.
• Execute Process Task: Can run ascmd.exe, which is the SSAS command-line tool that can run XMLA, MDX, and DMX queries. The advantage of the ascmd.exe tool is the capability to pass in parameters to a script that is run.

To demonstrate the use of some of these approaches, this next tutorial demonstrates processing a multidimensional model using the Analysis Services Processing Task to process the dimensions related to the sales quotas, and then the Analysis Services Execute DDL Task to handle the processing of the partitions.

Before beginning these steps, create a new partition in SSAS for the Sales Targets Measure called Sales_Quotas_2014. This is for demonstration purposes. An XMLA script has been created and included in the downloadable content at Wiley for this Tutorial called Sales_Quotas_2014.xmla.

1. In your SSIS project for this Loading a Data Warehouse Topic, create a new package and rename it SSAS_SalesTargets.dtsx.
2. Since this is the only package that will be using the SSAS connection, you will create a package connection, rather than a project connection. Right-click in the Connection Managers window and choose New Analysis Services Connection. In the Add Analysis Services Connection Manager window, click the Edit button to bring up the connection properties, as shown below screenshot.

a. Specify your server in the “Server or file name” text box (such as localhost if you are running SSAS on the same machine).
b. Change the “Log on to the server” option to Use Windows NT Integrated Security.
c. In the Initial catalog dropdown box, choose the Adventure Works SSAS database, which by default is named Adventure Works DW Multidimensional. Please remember that you will need to download and install the sample SSAS cube database, which is available from WWW.WROX.COM/GO/PROSSIS2014.
d. Click OK to save your changes to the Connection Manager and then click OK in the Add Analysis Services Connection Manager window.
e. Finally, rename the connection in the SSIS Connection Managers window to AdventureWorksAS.
3. To create the dimension processing, drag an Analysis Services Processing Task from the SSIS Toolbox onto the Control Flow and rename the task Process Dimensions.
4. Double-click the Process Dimensions Task to bring up the editor and navigate to the Processing Settings property page.
a. Confirm that the Analysis Services Connection Manager dropdown is set to AdventureWorksAS.
b. Click the Add button to open the Add Analysis Services Object window. As shown in the below screenshot, check the Date, Employee, and Sales Territory dimensions and then click OK to save your changes.

[Related Article: SSAS Interview Questions]

 

c. For each dimension, change the Process Options dropdown to Process Default, which will either perform a dimension update or, if the dimension has never been processed, fully process the dimension.
d. Click the Change Settings button, and in the Change Settings editor, click the Parallel selection option under the Processing Order properties. Click OK to save your settings.
e. Click OK to save your changes to the Analysis Services Processing Task.
5. Before continuing, you will create an SSIS package variable that designates the XMLA partition for processing. Name the SSIS variable Sales_Quota_Partition and define the variable with a String data type and a value of “Fact Sales Quota.”
6. Drag an Analysis Services Execute DDL Task onto the Data Flow and drag the green precedence constraint from the Process Dimensions Task onto the Analysis Services Execute DDL Task. Rename the Analysis Services Execute DDL Task Process Partition.
a. Edit the Process Partition Task and navigate to the DDL property page.
b. Change the Connection property to AdventureWorksAS and leave the SourceType as Direct Input, as shown below screenshot.

c. Change to the Expressions property page of the editor and click the Expressions property in the right window. Click the ellipsis on the right side of the text box, which will open the Property Expressions Editor. Choose Source from the dropdown, as shown below screenshot.

.

d. Now you need to add the XMLA code that will execute when the package is run. The expressions will dynamically update the code when this task executes. Click the ellipsis on the right side of the Source property (refer to the above screenshot) to open Expression Builder.
e. Enter the following code in the Expression text box, which is also shown in the below screenshot:

[Related Topic: How to Create Cube in SSAS with Example]

This code generates the XMLA and includes the Sales_Quota_Partition variable. The good news is that you don’t need to know XMLA; you can use SSMS to generate it for you. To automatically generate the XMLA code that will process a Sales Quota partition, open SSMS, and connect to SSAS. Expand the Databases folder, then the Adventure Works SSAS database, then the Cubes folder; then expand the Adventure Works cube, and finally expand the Sales Targets measure group. Right-click the Sales Quota 2014 partition and choose Process, as shown below screenshot.

[Related Topic: How to Implement Proactive Caching in SSAS - MSBI]

The processing tool in SSMS looks very similar to the SSAS processing task in SSIS except that the SSMS processing tool has a Script button near the title bar. Click the Script button.
g. Click OK in the open windows to save your changes. The purpose of creating the script and saving the file is to illustrate that you can build your own processing with XMLA and either execute the code in SSMS (clicking on the Execute button) or execute the file through the Analysis Services Execute DDL Task.
7. The SSIS package that you have just developed should look similar to the below screenshot.

If you were to fully work out the development of this package, you would likely have a couple more tasks involved in the process. First, the current partition is entered in the variable, but you haven’t yet put the code in place to update this variable when the package is run. For this task, either you could use an Execute SQL Task to pull the value for the current partition from a configuration table or the system date into the variable, or you could use a Script Task to populate the variable.

Second, if you have a larger solution with many partitions that are at the weekly or monthly grain, you would need a task that created a new partition, as needed, before the partition was run. This could be an Analysis Services Execute DDL Task similar to the one you just created for the processing Task, or you could use a Script Task and leverage AMO to create or copy an existing partition to a new partition.

As you have seen, processing SSAS objects in SSIS can require a few simple steps or several more complex steps depending on the processing needs of your SSAS solution.

List of Related Microsoft Certification Courses:

SSIS	Power BI
SSRS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator