29 marzo 2012

Script XMLA para procesar nuestras dimensiones de Analysis Services (Multidimensional)

 

Una de las formas más prácticas de lograr que nuestras dimensiones, cubos y/o particiones de Analysis Services (en modo Multidimensional) se actualicen con los datos de la fuente de una manera automática, es usando un script de XMLA. Este script puede ser invocado luego por un SQL Server Agent Job.

El tema es que si queremos forzar a que todas o muchas de nuestras dimensiones se actualicen, hay que generar un script de XMLA para cada una y si estamos hablando de 10 o más dimensiones, esto se vuelve una tarea tediosa (por no decir aburrida). Eso me pasó hoy, así que me puse a escribir un pequeño script que capitalice en los Dynamics Management Views (DMV) de Analysis Services, donde similar a los que existen en el lado de SQL Server relacional, encontramos varios views interesantes que nos proporcionan entre muchas cosas, información de la metadata de nuestro cubo. Si quieren más información de los DMV, Vincent Rainardi escribió un excelente post al respecto hace ya algún tiempo.

Lo único que hay que hacer es identificar el esquema que debemos generar en el XMLA para procesar los elementos que necesitemos. En mi caso sería algo similar al siguiente para hacer un ProcessUpdate en paralelo para cada una de mis dimensiones:

<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">
<Object>
<DatabaseID>[DATABASE ID]</DatabaseID>
<DimensionID>[DIMENSION ID]</DimensionID>
</Object>
<Type>ProcessUpdate</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>

</Parallel>
</Batch>

Lo que va en el elemento (tag) <Process>…</Process> es lo que tendríamos que repetir para cada dimensión que vayamos a procesar en paralelo. Para ello, voy a usar el DMV de Analysis Services llamado TABLE_OLAP_TYPE que nos brinda la metadata de toda nuestra base de datos. De este modo generamos la siguiente consulta en un script nuevo de MDX con la siguiente sentencia:

select TABLE_NAME
from $system.dbschema_tables
WHERE TABLE_OLAP_TYPE = 'CUBE_DIMENSION'
AND TABLE_SCHEMA = TABLE_NAME;


En la parte del WHERE, usamos la columna TABLE_OLA_TYPE para indicarle el tipo de elemento que necesitamos (en este caso dimensiones), y con la parte de TABLE_SCHEMA = TABLE_NAME, me aseguro que me traiga todas las dimensiones de mi base de datos sin importar en qué cubo o measure group estén referenciadas. El resultado lo obtengo con TABLE_NAME, la cual me devuelve el nombre de la dimensión incluyendo el símbolo de dolar ($) al inicio:



image



Ya con esto tenemos todos los elementos para generar nuestro query incluyendo la parte del XMLA necesario para procesar las dimensiones requeridas:



select 
'<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"><Object><DatabaseID>OneMCS</DatabaseID><DimensionID>'
+ MID(TABLE_NAME,2)
+ '</DimensionID></Object><Type>ProcessUpdate</Type><WriteBackTableCreation>UseExisting</WriteBackTableCreation></Process>'
from $system.dbschema_tables
WHERE TABLE_OLAP_TYPE = 'CUBE_DIMENSION'
AND TABLE_SCHEMA = TABLE_NAME;




El resultado se muestra similar a:



image



Si le dan copy/paste a una de las líneas a una ventana de XMLA, se ve mejor:



<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">
<Object>
<DatabaseID>HealthInsuranceDB</DatabaseID>
<DimensionID>Adjudication Reason</DimensionID>
</Object>
<Type>ProcessUpdate</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>



Lo único que queda ahora es copiar todo los resultados dentro del encabezado y cierre del comando XMLA (<Batch…> <Parallel>…</Parallel></Batch>) y tienen su script listo para ejecutar en su herramienta favorita.



Alan