Analysis Services - How to Use XLMA or Powershell to Process Cubes and Dimensions via Command Line (T-SQL) or SQL Agent Job

Views: 707
Reading Time: 7 minutes

Hello people,
How are you today ?

In this post, I will demonstrate how to use the XML for Analysis (XLMA) language to be able to process Analysis Services cubes and dimensions through Transact-SQL (T-SQL) queries, which can be used in a SQL Agent job to automate this processing. , which, in conjunction with ETL loads for dimension and fact data generation, allow you to automate the entire process of loading and updating your Analysis Services cubes.

In order to perform the routines described in this post, you will need to have created a Linked Server for integration between SQL Server and Analysis Services (Here in the example, I call this object INTERFACE_CUBOS).

If you have not created this Linked Server and do not know how to do this, visit the post Analysis Services (SSAS) - How to query information and process command line (XLMA) commands through SQL Server and follow the step by step to create it.

If you are not very familiar with SQL Server Analysis Services (SSAS) and would like to learn how to create your first Multidimensional Cube, learn more by visiting the post. Analysis Services - Creating Your First Multidimensional Cube in the Star Model (Star Schema).

Processing Types

Before demonstrating how to perform cube and dimension processing via the command line, I need to explain what each type of processing is.

Process Default: Performs the least effort possible (with the least amount of tasks) to process the structure and data. The server converts this option to the one that best suits your environment right now.
Applicable: All Objects

Process full: Processes all structure and data, deleting and recreating objects. This means that processing discards everything that exists and re-creates the analytical structure and then processes the data for this structure. If any new attributes are added to the dimension, a process full should roll.
Applicable: All Objects

Process Update: When any attribute changes occur in the dimension, either by adding deleting or updating, this process should be performed. It has sufficient intelligence to process only the differential of structure and data that is new, but is slower (to apply "intelligence").
Applicable: Dimension

Process Data: Discards all stored data and processes all data again, ignoring any changes in structure and indexes. The focus is only on the data.
Applicable: Dimension, Cube, Measure and Partition

Process Add (Incremental): Processes only new data, ignoring existing data as well as any new changes to structure or indexes.
Applicable: Dimension and Partition

Generating XLMA to Process Analysis Services Cubes and Dimensions through Management Studio (SSMS)

A very simple way to be able to generate XLMA scripts to automate the processing of dimensions, cubes, databases, and partitions is through SQL Server Management Studio, as I will show below:

1) Click “Connect”> “Analysis Services…” and enter the Analysis Services connection data.

2) Expand the “Dimensions” folder (as in the picture below) and press the F7 (Object Explorer Details) key to expand the details of the objects.

3) Select the dimensions you wish to process, right-click and select the "Process" option.

4) Select the processing mode you want for each of your partitions. In the example, I chose Process Full mode for the 3 dimensions.

5) After setting the processing mode for each dimension, click the “Script” button to generate XLMA for this action.

6) Done. Your XLMA has been generated. You can also repeat this for processing cubes, partitions, databases, etc.

7) You can now create Jobs using your XLMA to automate Analysis Services processing.

8) After the job has been created, simply run it to process the data.

Processing Analysis Services cubes and dimensions via command line using T-SQL (XLMA)

For many times in the life of a BI analyst, he will need to automate the processing of cube and dimension information so that information is always up to date. To do this, you can use Integration Services Tasks (SSIS) or XLMA, which lets you create and run scripts through SQL Server, allowing you to create jobs with these commands easily and with much greater routine customization power than SSIS.

If you do not know how to run XLMA scripts through SQL Server, read the post Analysis Services (SSAS) - How to query information and process command line (XLMA) commands through SQL Server.

How to process one dimension of a cube by command line

How to process multiple dimensions of a cube by command line

How to process a cube by command line

How to process Analysis Services cubes using PowerShell

If you are a fan of creating shell scripts, I will also present a solution that I found on this link to process cubes and dimensions using PowerShell.

How to process all dimensions of a cube using PowerShell

How to process a cube using Powershell

Automating Analysis Services Cubes Processing with Powershell

To be able to automate Cube processing using PowerShell, simply create a Step in a SQL Server Agent job as shown below and paste the Powershell script into the “Command” field:

That's it folks!
I hope you enjoyed the post and even more!