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
|
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 |
Como processar várias dimensões de um cubo por linha de comando
|
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 |
Como processar um cubo por linha de comando
|
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 |
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
|
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") |
Como processar um cubo utilizando 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") |
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”:
And that's it, folks!
Espero que tenham gostado do post e até mais!
Portuguese 
English