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 on “Connect”> “Analysis Services…” and enter the connection data for Analysis Services
2) Expand the “Dimensions” folder (as in the figure below) and press the F7 (Object Explorer Details) key to expand the details of the objects.
3) Select the dimensions you want to process, right click and select the option "Process".
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 configuring the processing mode for each dimension, click on the “Script” button to generate the XLMA for that 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @QueryXLMA VARCHAR(MAX) = '<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel> <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"> <Object> <DatabaseID>Cubo_Venda_Restaurado</DatabaseID> <DimensionID>Dim Produto</DimensionID> </Object> <Type>ProcessFull</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch>' EXEC(@QueryXLMA) AT INTERFACE_CUBOS GO |
How to process multiple dimensions of a cube by command line
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
DECLARE @QueryXLMA VARCHAR(MAX) = '<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel> <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>Cubo_Venda_Restaurado</DatabaseID> <DimensionID>Dim Produto</DimensionID> </Object> <Type>ProcessFull</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> <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>Cubo_Venda_Restaurado</DatabaseID> <DimensionID>Dim Forma Pagamento</DimensionID> </Object> <Type>ProcessFull</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> <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>Cubo_Venda_Restaurado</DatabaseID> <DimensionID>Dim Cliente</DimensionID> </Object> <Type>ProcessFull</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch>' EXEC(@QueryXLMA) AT INTERFACE_CUBOS GO |
How to process a cube by command line
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @QueryXLMA VARCHAR(MAX) = '<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel> <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"> <Object> <DatabaseID>Cubo_Venda_Restaurado</DatabaseID> <CubeID>Cubo_Venda</CubeID> </Object> <Type>ProcessFull</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch>' EXEC(@QueryXLMA) AT INTERFACE_CUBOS GO |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
param ( $ServerName="vm-dba\sql2016", $DBName="Cubo de Vendas", $ProcessTypeDim="ProcessFull", $Transactional="Y", $Parallel="Y", $MaxParallel=8, $MaxCmdPerBatch=5, $PrintCmd="N" ) $loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null if ($Transactional -eq "Y") { $TransactionalB=$true } else { $TransactionalB=$false } if ($Parallel -eq "Y") { $ParallelB=$true } else { $ParallelB=$false } $server = New-Object Microsoft.AnalysisServices.Server $server.connect($ServerName) if ($server.name -eq $null) { Write-Output ("Servidor '{0}' não encontrado" -f $ServerName) break } $DB = $server.Databases.FindByName($DBName) if ($DB -eq $null) { Write-Output ("Database '{0}' não encontrado" -f $DBName) break } Write-Output("Inicio do processamento {0}" -f (Get-Date -uformat "%H:%M:%S") ) Write-Output("----------------------------------------------------------------") Write-Output("Servidor : {0}" -f $Server.Name) Write-Output("Database : {0}" -f $DB.Name) Write-Output("Situação : {0}" -f $DB.State) Write-Output("Tamanho : {0}MB" -f ($DB.EstimatedSize/1024/1024).ToString("#,##0")) Write-Output("----------------------------------------------------------------") Write-Output("Início do processamento do banco iniciado. Horário: {0}" -f (Get-Date -uformat "%H:%M:%S")) $server.CaptureXml=$TRUE # Não executa os comandos, apenas captura a saída # Processa as dimensões foreach ($dim in $DB.Dimensions) { $dim.Process($ProcessTypeDim) } $server.CaptureXML = $FALSE # Terminou de capturar os comandos. Todos estão em Server.CaptureLog $cmdBatch = @" <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel MaxParallel="##MaxParallel##"> ##ProcessCmd## </Parallel> </Batch> "@ $cmdBatch = $cmdBatch -replace("##MaxParallel##", $MaxParallel) # $ErrorActionPreference = "SilentlyContinue" $currentCmdNo=0; $currentCmdInBatchNo=0; $processCmd=""; $currentBatchNo=0 $TotalCmdCount = $Server.CaptureLog.Count foreach ($cmdLine in $Server.CaptureLog) { $currentCmdNo = $currentCmdNo + 1 $processCmd = $processCmd + $cmdLine + "`n" $currentCmdInBatchNo=$currentCmdInBatchNo + 1 if ($currentCmdInBatchNo -ge $MaxCmdPerBatch -or $currentCmdNo -eq $TotalCmdCount) { #MaxCmdPerBatch reached, execute commands $processCmd = $cmdBatch -replace("##ProcessCmd##", $processCmd) if ($PrintCmd -eq "Y") { Write-Output($processCmd) } $currentBatchNo = $currentBatchNo + 1; Write-Output("=== Iniciando batch No {0}. Time: {1} ..." -f $currentBatchNo, (Get-Date -uformat "%H:%M:%S")) $Result = $Server.Execute($processCmd) # Report errors and warnings foreach ($res in $Result) { foreach ($msg in $res.Messages) { if ($msg.Description -ne $null) { Write-Output("{0}" -f $msg.Description) } } } # Reset temp values $processCmd = ""; $currentCmdInBatchNo=0; } } Write-Output("----------------------------------------------------------------") Write-Output("Processamento do banco de dados finalizado. Horário: {0}" -f (Get-Date -uformat "%H:%M:%S")) Write-Output("----------------------------------------------------------------") Write-Output("Listando objetos que não foram processados") |
How to process a cube using Powershell
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 |
param ( $ServerName="vm-dba\sql2016", $DBName="Cubo de Vendas", $ProcessType="ProcessFull", $Transactional="Y", $Parallel="Y", $MaxParallel=8, $MaxCmdPerBatch=5, $PrintCmd="N" ) $loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null if ($Transactional -eq "Y") { $TransactionalB=$true } else { $TransactionalB=$false } if ($Parallel -eq "Y") { $ParallelB=$true } else { $ParallelB=$false } $server = New-Object Microsoft.AnalysisServices.Server $server.connect($ServerName) if ($server.name -eq $null) { Write-Output ("Servidor '{0}' não encontrado" -f $ServerName) break } $DB = $server.Databases.FindByName($DBName) if ($DB -eq $null) { Write-Output ("Database '{0}' não encontrado" -f $DBName) break } Write-Output("Inicio do processamento {0}" -f (Get-Date -uformat "%H:%M:%S") ) Write-Output("----------------------------------------------------------------") Write-Output("Servidor : {0}" -f $Server.Name) Write-Output("Database : {0}" -f $DB.Name) Write-Output("Situação : {0}" -f $DB.State) Write-Output("Tamanho : {0}MB" -f ($DB.EstimatedSize/1024/1024).ToString("#,##0")) Write-Output("----------------------------------------------------------------") Write-Output("Início do processamento do banco iniciado. Horário: {0}" -f (Get-Date -uformat "%H:%M:%S")) $server.CaptureXml=$TRUE # Não executa os comandos, apenas captura a saída #Processa os cubos foreach ($cube in $DB.Cubes) { foreach ($mg in $cube.MeasureGroups) { foreach ($part in $mg.Partitions) { $part.Process($ProcessType) } } } $server.CaptureXML = $FALSE # Terminou de capturar os comandos. Todos estão em Server.CaptureLog $cmdBatch = @" <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel MaxParallel="##MaxParallel##"> ##ProcessCmd## </Parallel> </Batch> "@ $cmdBatch = $cmdBatch -replace("##MaxParallel##", $MaxParallel) # $ErrorActionPreference = "SilentlyContinue" $currentCmdNo=0; $currentCmdInBatchNo=0; $processCmd=""; $currentBatchNo=0 $TotalCmdCount = $Server.CaptureLog.Count foreach ($cmdLine in $Server.CaptureLog) { $currentCmdNo = $currentCmdNo + 1 $processCmd = $processCmd + $cmdLine + "`n" $currentCmdInBatchNo=$currentCmdInBatchNo + 1 if ($currentCmdInBatchNo -ge $MaxCmdPerBatch -or $currentCmdNo -eq $TotalCmdCount) { #MaxCmdPerBatch reached, execute commands $processCmd = $cmdBatch -replace("##ProcessCmd##", $processCmd) if ($PrintCmd -eq "Y") { Write-Output($processCmd) } $currentBatchNo = $currentBatchNo + 1; Write-Output("=== Iniciando batch No {0}. Time: {1} ..." -f $currentBatchNo, (Get-Date -uformat "%H:%M:%S")) $Result = $Server.Execute($processCmd) # Report errors and warnings foreach ($res in $Result) { foreach ($msg in $res.Messages) { if ($msg.Description -ne $null) { Write-Output("{0}" -f $msg.Description) } } } # Reset temp values $processCmd = ""; $currentCmdInBatchNo=0; } } Write-Output("----------------------------------------------------------------") Write-Output("Processamento do banco de dados finalizado. Horário: {0}" -f (Get-Date -uformat "%H:%M:%S")) Write-Output("----------------------------------------------------------------") Write-Output("Listando objetos que não foram processados") |
Automating Analysis Services Cubes Processing with Powershell
To be able to automate the processing of Cubes using PowerShell, just create a Step in a SQL Server Agent job as shown below and paste the Powershell script in the “Command” field:
That's it folks!
I hope you enjoyed the post and even more!
