Hola, chicos,
¿Cómo estás hoy?

En esta publicación, demostraré cómo usar el lenguaje XLMA (XML para Análisis) para poder procesar cubos y dimensiones de Analysis Services a través de consultas Transact-SQL (T-SQL), que se pueden usar en un trabajo del Agente SQL para automatizar este procesamiento, que, junto con las cargas ETL para generar datos de dimensiones y hechos, le permiten automatizar todo el proceso de carga y actualización de sus cubos de Analysis Services.

Para poder realizar las rutinas descritas en este post, necesitarás haber creado un Servidor Vinculado para la integración entre SQL Server y Analysis Services (Aquí en el ejemplo, a este objeto lo llamo INTERFACE_CUBOS).

Si no has creado este Servidor Enlazado y no sabes cómo hacerlo accede al post Analysis Services (SSAS): cómo consultar información y procesar comandos a través de la línea de comandos (XLMA) a través de SQL Server y sigue el paso a paso para crearlo.

Si no estás muy familiarizado con SQL Server Analysis Services (SSAS) y te gustaría aprender a crear tu primer Cubo Multidimensional, conoce más accediendo al post Analysis Services: cómo crear su primer cubo multidimensional en el modelo en estrella (esquema en estrella).

Tipos de procesamiento

Antes de demostrar cómo procesar cubos y dimensiones a través de la línea de comando, necesito explicar qué es cada tipo de procesamiento.

Proceso predeterminado: Hace el menor esfuerzo posible (con la menor cantidad de tareas) para procesar la estructura y los datos. El servidor convierte esta opción en la que más se adapta a las necesidades de tu entorno en cada momento.
Aplicable: todos los objetos

Proceso completo: Procesa toda la estructura y los datos, eliminando y recreando objetos. Esto significa que el procesamiento descarta todo lo que existe y crea nuevamente la estructura analítica y luego procesa los datos en esta estructura. Si se agrega algún atributo nuevo a la dimensión, debe ocurrir un proceso completo.
Aplicable: todos los objetos

Actualización del proceso: Cuando ocurra algún cambio de atributo en la dimensión, ya sea agregando, eliminando o actualizando, se debe realizar este proceso. Tiene suficiente inteligencia para procesar sólo la diferencia en estructura y datos que son nuevos, pero es más lento (para aplicar “inteligencia”).
Aplicable: Dimensión

Datos de proceso: Descarta todos los datos almacenados y procesa todos los datos nuevamente, ignorando si ha habido un cambio en la estructura o los índices. El foco son sólo los datos.
Aplicable: Dimensión, Cubo, Medida y Partición

Agregar proceso (incremental): Procesa solo datos nuevos, ignorando los datos que ya existen y también cualquier cambio nuevo en la estructura o los índices.
Aplicable: Dimensión y Partición

Cómo generar XLMA para procesar cubos y dimensiones de Analysis Services a través de Management Studio (SSMS)

Una forma muy sencilla de generar scripts XLMA para automatizar el procesamiento de dimensiones, cubos, bases de datos y particiones es a través de SQL Server Management Studio, como mostraré a continuación:

1) Haga clic en “Conectar” > “Analysis Services…” e ingrese los datos de conexión de Analysis Services

2) Expanda la carpeta "Dimensiones" (como en la figura siguiente) y presione la tecla F7 (Detalles del Explorador de objetos) para expandir los detalles del objeto.

3) Seleccione las dimensiones que desea procesar, haga clic derecho y seleccione la opción “Procesar”.

4) Seleccione el modo de procesamiento que desea para cada una de sus particiones. En el ejemplo, elegí el modo Proceso completo para las 3 dimensiones.

5) Después de configurar el modo de procesamiento para cada dimensión, haga clic en el botón "Script" para generar el XLMA para esta acción.

6) Hecho. Su XLMA ha sido generado. También puedes repetir esto para procesar cubos, particiones, bases de datos, etc.

7) Ahora puede crear trabajos utilizando su XLMA para automatizar el procesamiento de Analysis Services.

8) Una vez creado el trabajo, simplemente ejecútelo para procesar los datos.

Procesamiento de cubos y dimensiones de Analysis Services a través de la línea de comandos usando T-SQL (XLMA)

Muchas veces en la vida de un analista de BI, necesitará automatizar el procesamiento de información de cubos y dimensiones, para que la información esté siempre actualizada. Para ello, puedes utilizar Integration Services Tasks (SSIS) o XLMA, que te permite crear y ejecutar scripts a través de SQL Server, permitiéndote crear trabajos con estos comandos fácilmente y con un poder de personalización de rutinas mucho mayor que SSIS.

Si no sabe cómo ejecutar scripts XLMA a través de SQL Server, lea la publicación Analysis Services (SSAS): cómo consultar información y procesar comandos a través de la línea de comandos (XLMA) a través de SQL Server.

Cómo procesar una dimensión de cubo a través de la línea 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

Cómo procesar múltiples dimensiones de un cubo mediante la línea 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

Cómo procesar un cubo a través de la línea 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

Cómo procesar cubos de Analysis Services usando PowerShell

Si eres fanático de la creación de scripts de shell, también te presentaré una solución que encontré. en este enlace para procesar cubos y dimensiones usando PowerShell.

Cómo procesar todas las dimensiones de un cubo usando 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")

Cómo procesar un cubo usando 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")

Automatización del procesamiento de cubos de Analysis Services con Powershell

Para poder automatizar el procesamiento de Cubos usando PowerShell, simplemente cree un Paso en un trabajo del Agente SQL Server como se muestra a continuación y pegue el script de Powershell en el campo "Comando":

¡Eso es todo, amigos!
Espero que hayas disfrutado del post y ¡hasta luego!