Olá pessoal,
Como vocês estão hoje ?

Neste post, vou demonstrar como utilizar a linguagem XLMA (XML for Analysis) para conseguir processar cubos e dimensões do Analysis Services através de queries Transact-SQL (T-SQL), que podem ser utilizadas em um job do SQL Agent para automatizar esse processamento, que, em conjunto com cargas ETL para geração dos dados da dimensões e fatos, permitem que você automatize todo o processo de carga e atualização de seus cubos do Analysis Services.

Para que seja possível realizar as rotinas descritas neste post, você precisará ter criado um Linked Server de integração entre o SQL Server e o Analysis Services (Aqui no exemplo, eu chamo esse objeto de INTERFACE_CUBOS).

Caso você não tenha criado esse Linked Server e não saiba como fazer isso, acesse o post Analysis Services (SSAS) – Como consultar informações e processar comandos via linha de comando (XLMA) pelo SQL Server e siga o passo a passo para criá-lo.

Caso você não esteja muito familiarizado com o SQL Server Analysis Services (SSAS) e gostaria de aprender como criar seu primeiro Cubo Multidimensional, saiba mais acessando o post Analysis Services – Como criar seu primeiro cubo multidimensional no modelo estrela (Star schema).

Tipos de Processamento

Antes de demonstrar como realizar o processamento de cubos e dimensões via linha de comando, preciso explicar o que é cada tipo de processamento.

Process Default: Realiza o menor esforço possível (com a menor quantidade de tarefas) para processar a estrutura e os dados. O servidor converte esta opção na mais adequada à necessidade do seu ambiente, neste momento.
Aplicável: Todos Objetos

Process Full: Processa toda a estrutura e os dados, excluindo e recriando os objetos. Isso significa que o processamento discarta tudo o que existe e cria novamente a estrutura analítica e depois processa os dados para esta estrutura. Se algum novo atributo é adicionado à dimnensão, deve rolar um process full.
Aplicável: Todos Objetos

Process Update: Quando acontece alguma alteração de atributos na dimensão, seja adicionando apagando ou atualizando, este processo deve ser executado. Tem inteligência suficiente para processar somente o diferencial da estrutura e dos dados que são novos, porém é mais lento (para aplicar a “inteligência”).
Aplicável: Dimensão

Process Data: Descarta todos os dados armazenados e processa todos os dados novamente, ignorando se houve alteração na estrutura e também nos índices. O foco são só os dados.
Aplicável: Dimensão, Cubo, Measure e Partição

Process Add (Incremental): Processa somente os novos dados, ignorando os dados que já existem e também qualquer alteração nova na estrutura ou índices.
Aplicável: Dimensão e Partição

Como gerar o XLMA para processar Cubos e Dimensões do Analysis Services pelo Management Studio (SSMS)

Uma forma muito simples de se conseguir gerar scripts XLMA para automatizar o processamento de dimensões, cubos, databases e particições, é através do SQL Server Management Studio, como vou mostrar abaixo:

1) Clique em “Connect” > “Analysis Services…” e digite os dados de conexão do Analysis Services

2) Expanda a pasta “Dimensions” (como na figura abaixo) e aperte a tecla F7 (Object Explorer Details) para expandir os detalhes dos objetos.

3) Selecione as dimensões que você deseja processar, clique com o botão direito e selecione a opção “Process”.

4) Selecione o modo de processamento que você deseja para cada uma de suas partições. No exemplo, escolhi o modo Process Full para as 3 dimensões.

5) Após configurar o modo de processamento de cada dimensão, clique no botão “Script” para gerar o XLMA para essa ação.

6) Pronto. Seu XLMA foi gerado. Você também pode repetir isso para processamento de cubos, partições, databases, etc.

7) Agora você pode criar Jobs utilizando o seu XLMA para automatizar o processamento do Analysis Services.

8) Após o job ter sido criado, basta executá-lo para processar os dados.

Processando Cubos e Dimensões do Analysis Services via linha de comando utilizando T-SQL (XLMA)

Por muitas vezes na vida de um analista de BI, ele vai precisar automatizar o processamento de informações de cubos e dimensões, de modo que as informações fiquem sempre atualizadas. Para isso, você pode utilizar Tasks do Integration Services (SSIS) ou o XLMA, que permite criar e executar scripts pelo SQL Server, permitindo que você crie jobs com esses comandos facilmente e com um poder de personalização da rotina muito maior que o SSIS.

Caso você não saiba como executar scripts XLMA pelo SQL Server, dê uma lida no post Analysis Services (SSAS) – Como consultar informações e processar comandos via linha de comando (XLMA) pelo SQL Server.

Como processar uma dimensão de um cubo por linha de comando

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

Como processar várias dimensões de um cubo por linha de comando

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

Como processar um cubo por linha de comando

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

Como processar cubos do Analysis Services utilizando PowerShell

Caso você seja fã do criar scripts shell, vou apresentar também uma solução que encontrei neste link para processar cubos e dimensões usando PowerShell.

Como processar todas as dimensões de um cubo utilizando PowerShell

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")

Como processar um cubo utilizando Powershell

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")

Automatizando o processamento dos cubos do Analysis Services com Powershell

Para conseguir automatizar o processamento dos Cubos utilizando PowerShell, basta criar um Step num job do SQL Server Agent conforme demonstro abaixo e colar o script Powershell no campo “Command”:

É isso aí, pessoal!
Espero que tenham gostado do post e até mais!