Uso de la función RANKX (DAX)

OPTIMIZACIÓN DE MODELOS SSAS TABULAR MODEL

1         Objetivos

Los Modelos Tabulares son bases de datos de Analysis Services que se ejecutan en memoria (In-Memory) o en el modo DirectQuery, de tal forma que se accede a los datos directamente desde los orígenes de datos relacionales de back-end.

Gracias a los algoritmos de compresión avanzados y al procesador de consultas multiproceso, el motor analítico In-Memory ofrece un acceso rápido a los objetos y los datos de los modelos tabulares para aplicaciones cliente de informes como Microsoft Excel, Microsoft Power View o Microsoft Power BI.

DirectQuery es un modo de consulta alternativo para los modelos que son demasiado grandes para caber en la memoria o cuando la volatilidad de los datos impide una estrategia de procesamiento razonable. En esta versión, DirectQuery logra una paridad mayor con modelos en memoria mediante la compatibilidad con orígenes de datos adicionales, la capacidad para manejar tablas calculadas y columnas de un modelo DirectQuery, la seguridad de nivel de fila a través de expresiones de DAX que llegan a la base de datos back-end y las optimizaciones de consultas que producen un rendimiento óptimo del modelo.

Los modelos tabulares se crean en SQL Server Data Tools (SSDT) mediante la plantilla de proyecto de modelo tabular que proporciona una superficie de diseño para crear un modelo, tablas, relaciones y expresiones DAX. Puede importar datos de varios orígenes y luego enriquecer el modelo agregando relaciones, columnas y tablas calculadas, medidas, KPI, jerarquías y traducciones.

Los modelos se implementan en una instancia de Analysis Services configurada para el modo de servidor tabular, donde las aplicaciones cliente de informes pueden conectarse con ellos. Los modelos implementados se pueden administrar en SQL Server Management Studio del mismo modo que los modelos multidimensionales. También se pueden crear particiones de los mismos para optimizar el procesamiento y protegerlos en el nivel de fila usando la seguridad basada en roles.

El objetivo principal del presente documento es recoger una serie de recomendaciones o pautas que permitan a los modelos tabulares un rendimiento lo más óptimo posible sin perder la funcionalidad para la cual fueron diseñados.

1         Reglas Básicas de Optimización

Se enumeran, a continuación, las normas básicas que se deben tener en cuenta para optimizar el uso de memoria en los modelos tabulares en relación al uso de memoria RAM en los servidores de Analysis Services en modo Tabular:

1.1      Columnas Útiles

En los Modelos Tabulares los datos son almacenados por columna y cada una de estas va a tener un coste en memoria. Cuanto más valores diferentes tengan una columna, mayor coste va a tener por lo que es importante que al incluir una tabla en el modelo intentemos evitar el uso de aquellas columnas que no vamos utilizar en filtros, relaciones o cálculos.

Columnas a excluir:

§  Claves Primarias (PKs) en Tablas de Hechos.

§  Columnas de auditoria (fecha alta, fecha actualización,…).

§  Claves internas del operacional que no verá el usuario final.

1.2      Normalizar Columnas

Es muy importante el evitar columnas que son cálculos de otras intentado crear medidas en el modelo que sustituyan a estas. A modo de ejemplo supongamos que tenemos una columna con la cantidad de ventas Ventas[Cantidad] y en otra el precio Ventas[Precio] y que queremos crear una medida o campo calculado que nos devuelva el Importe Total la forma correcta de realizar esto es:

 Importe Total:=SUMX(Ventas, Ventas[Cantidad] * Ventas[Precio])

El Importe Total se calcula en este caso en el momento de la consulta.

1.3      Dividir Columnas de Alta Cardinalidad

Si una columna posee demasiados valores diferentes, como podría ser fecha y hora de un evento, si dividimos en la carga en varias columnas, por ejemplo fecha y hora en dos columnas, estas ocupan mucho menos memoria que el campo original conteniendo ambos valores.

1.4      Reducir la precisión

Reduciendo la precisión de una columna podríamos quizás reducir la cantidad de valores diferentes, y por tanto el uso de memoria.

2         Optimización del Modelo de Datos

§  Intentar utilizar Esquema en Estrella: Equilibrio entre normalización y desnormalización.

§  Evitar relaciones entre tablas de hechos: En general evitar relaciones entre tablas de gran volumetría y en especial en entre columnas de gran cardinalidad.

§  Evitar dimensiones con más de 2 millones de filas.

§  Evitar relaciones N:M.

§  Utilizar las funciones de inteligencia de tiempo proporcionado por DAX: Mejora el rendimiento y no es necesario una dimensión tiempo al uso como en los modelos de Analysis Services tradicional.

Existen ciertas consideraciones a tener en cuenta a la hora de optimizar el rendimiento del modelo a la hora de realizar consultas o análisis por parte de los consumidores de dicho modelo:

2.1      Evitar filtros de fila o seguridad en los roles demasiado complejos.

El impacto de los filtros de fila sobre el rendimiento de las consultas puede llegar a ser muy alto. Estas expresiones son evaluadas la primera vez que un usuario realiza una consulta sobre la tabla o alguna relacionada cacheando los resultados (siempre y cuando no se haga uso de la función USERNAME).

2.2      Evitar las funciones de control de errores

Siempre que sea posible, es recomendable evitar las funciones de control de error ISERROR e IFERROR. En DAX muchas de las funciones ya disponen de un parámetro  que especifica el valor a devolver en caso de error (SEARCH, FIND…).

Las funciones de control de errores provocan que la consulta tenga que ir analizando celda por celda si existe o no un error.

2.3      Detectar el uso de filtros con las funciones indicadas

Existen funciones DAX como HASONEVALUE o HASONEFILTER para detectar si se están aplicando filtros sobre los datos. Estas funciones son más eficientes que por ejemplo la función COUNTROWS.

2.4      Usar la función CALCULATE en lugar de FILTER

Las funciones CALCULATE y CALCULATETABLE son funciones más eficientes que FILTER debido a que todos los cálculos se realizan dentro del contexto de la función.

2.5      Decidir cuándo usar la función ISEMPTY en lugar de ISBLANK

Aunque aparentemente el objetivo de estas dos funciones es similar, internamente la función ISEMPTY es más rápido que ISBLANK ya que la función ISBLANK necesita evaluar la métrica mientras que ISEMPTY solo se preocupa por la presencia de la fila.

3         Optimización del Tamaño

Dado que el principal recurso que consume un Modelo Tabular es la memoria del servidor (RAM) la reducción de la cantidad de memoria necesaria a porta un beneficio en forma de dos factores:

§  Coexistencia de Modelos Tabulares con una menor necesidad de recursos.

§  Optimización del rendimiento del Modelo.

Dado que el tamaño del modelo influye decisivamente en el rendimiento se realizan una serie de recomendaciones con el fin de optimizar el tamaño, si bien siempre están supeditadas a un estudio de los requerimientos de cada modelo.

3.1      Optimización de Columnas

Uno de los factores que más decisivamente influyen en el tamaño de un modelo tabular (concretamente del tamaño de las tablas que lo componen) es la cardinalidad de las columnas que componen las tablas.

Si bien el objetivo no es profundizar en la arquitectura interna de SSAS, los Modelos Tabulares usan un sistema de almacenamiento columnar, en el que cada tabla se divide en columnas y cada una de estas columnas se almacena de forma separada a las demás tras unas operaciones de procesado interno.

Cuanto mayor sea la cardinalidad (número distinto de valores existentes) de una columna mayor será el tamaño que ocupe en memoria, mientras que el número de valores de una columna no influye de una manera decisiva.

A continuación se listan algunas opciones para la optimización bajo un escenario de alta cardinalidad según el tipo de datos de las columnas

DateTime

Dividir una columna de tipo DateTime en dos de tipo Date (una con la fecha y otra con la hora) consigue reducir de forma muy significativa la cardinalidad de las columnas y, por tanto, el tamaño de las columnas resultantes.

(Fuente: Microsoft Services)

En el siguiente ejemplo se ha creado una tabla con tres columnas que contienen las fechas de los dos primeros meses del año de 2016 (Enero y Febrero de 2016) con granularidad de minuto. En total son 86.399 filas, dividas en tres columnas:

§  DateTime: columna con los valores de Fecha y Hora

§  Date: columna con datos de Fecha

§  Time: columna con los datos de Hora

Se puede comprobar que la columna de tipo DateTime ocupa un 96.15% del espacio de la tabla (6MB en total y 5,8MB dicha columna)

A pesar de tener las mismas 86.399 filas, la columna Date únicamente ocupa 3,6KB y la columna Time menos de 230KB con la misma volumetría. Se verifica por tanto el comportamiento relativo a la cardinalidad frente a la volumetría.

Int - Bigint

Con las columnas del tipo Int – Bigint sucede que en el motor de SSAS se almacenan usando el mismo tipo de dato de 64bit. 

Para las ocasiones en la que ciertos ID’s utilizados en los modelos no forman parte de una relación entre tablas, pero tienen una cardinalidad muy alta, es posible implementar una división en dos columnas o resta (por ejemplo) reduciendo de este modo la cardinalidad de las columnas y por tanto el tamaño en memoria.

Además, es posible crear una medida DAX para recuperar como un único valor la cifra original, por lo que la posibilidad de uso de esta modificación es elevada.

String

En el caso de columnas de tipo string el procedimiento es similar al caso anterior, y se debe de buscar la división que minimice la cardinalidad de cada una de las columnas resultantes.

Es importante tener en cuenta que en este caso no es posible el uso de medidas calculadas.

3.2      Columnas Calculadas vs Medidas

Las columnas calculadas en los Modelos Tabulares son creadas en tiempo de procesado y se añaden a las columnas ya existentes mediante una expresión que define los valores que contendrá en cada una de las filas.

Al ser materializadas en tiempo de procesado, las columnas calculadas ocupan espacio en memoria, aunque no se utilicen en ninguna de las consultas que se ejecuten contra el modelo.

Es importante saber que las columnas calculadas no son comprimidas por SSAS, por lo tanto, el espacio necesario para almacenar una columna calculada es mayor que en el caso de otra columna con los mismos datos pero que han sido importados desde el origen directamente.

Una de las opciones de optimización del tamaño de la solución es sustituir columnas calculadas con medidas calculadas evitando la materialización de los valores de la columna con el consiguiente ahorro en la cantidad de memoria necesaria. Esta posibilidad es factible siempre y cuando la operación a realizar sea una operación aritmética simple.

3.3      Tipos de Datos

El modo en que xVelocity (o Vertipag que el Motor analítico en memoria) almacena internamente la información influye en el grado de compresión que puede conseguir, es decir, no con todos los tipos de datos se consigue el mismo grado de compresión.

Antes de realizar la compresión de la información xVelocity realiza una operación de codificación de la información en función de varios factores y uno de ellos el tipo de dato que se va a almacenar. Dos ejemplos significativos:

Tipo de datos “string”

Para este tipo de datos xVelocity siempre usa el mismo tipo de codificación denominada “Hash” lo que habitualmente repercute en un grado de compresión menor.

Siempre que sea posible el almacenamiento en modo numérico de los tipos de datos string se aconseja su conversión. Durante la creación de un Modelo Tabular existen dos propiedades relacionadas con el tipo de dato que se pueden establecer para una columna:

§  Data Format: formato con el que se va a representar el valor almacenado. No tiene por qué coincidir con el tipo de dato

§  Data Type: modo en el que se almacenan los valores de la columna internamente. Esta propiedad es la que realmente importa a la hora de optimizar el almacenamiento.

þ Almacenar un string en origen como un formato numérico en xVelocity optimizará el tamaño de la solución y por tanto el rendimiento.

Tipo de datos “decimal”

Siguiendo la misma metodología que en el apartado anterior, siempre que la precisión del valor lo permita (<=4 cifras decimales) se aconseja el uso del tipo de dato “Currency” estableciendo la propiedad “Data Format” en “Decimal Number”.

De este modo xVelocity puede optimizar el almacenamiento de los valores de una columna de este tipo, repercutiendo en un menos tamaño del modelo y un mejor rendimiento del mismo.

4         Procesado y Particionado

A diferencia de en la implementación Multidimensional las operaciones de particionado en los Modelos Tabulares no afectan al rendimiento final de la solución en cuanto al tiempo de respuesta a consultas.

El particionado se utiliza únicamente como una técnica que nos facilita:

§  La posibilidad de realizar cargas incrementales de información

§  Optimización del tiempo necesario para el refresco de la información.

Tanto en SQL Server Analysis Services 2012 como SQL Server Analysis Services 2014 solo una partición de cada tabla puede ser procesada al mismo tiempo, sin embargo, en la próxima versión SSAS 2016 esta limitación ha sido suprimida por lo que es posible mejorar los tiempos de procesado aumentando el número de particiones en paralelo.

4.1      Paralelismo

A la hora de implementar una estrategia de procesado de los Modelos Tabulares es importante tener en cuenta el número de objetos que se quieren “actualizar” y establecer cuáles y cuántos de ellos se quieren procesar al mismo tiempo.

Si se pretende optimizar el tiempo de procesado de una solución, siempre que los recursos disponibles en el servidor sean suficientes, se debe procesar el mayor número de objetos posibles en paralelo, y para ello es necesario incluir una propiedad en el archivo XMLA denominada <Parallel>.

Por defecto el procesado de los distintos objetos se realiza en serie (unos a continuación de otros) aumentando muy probablemente el tiempo de procesado total de la solución.

4.2      Procesado

Cuando el tiempo de procesado no es un factor limitante y se pretende realizar una actualización de todos los objetos existentes en el modelo se recomienda el uso de procesado de tipo ProcessFull.

Si por el contrario existe algún factor limitante a tener en cuenta en procesado (ventana temporal de procesado limitada, recursos limitados, carga incremental,…) se pude recurrir a cualquiera de las siguientes técnicas:

Carga Incremental con Modificación de Datos

La estrategia recomendada en este caso sería el procesado en paralelo, siempre que sea posible, de las tablas/particiones que se quieren actualizar usando ProcessData para posteriormente ejecutar una única vez al termino de todos los comandos ProcessData un ProcessRecalc.

Carga Incremental solo Inserción de Datos

En el caso de que los datos ya existentes en el Modelo Tabular no varíen con el tiempo y solo se produzcan inserciones de nuevos registros podemos utilizar un nuevo tipo de procesado denominado ProcessAdd. 

Con este método de procesado es necesario definir una consulta que únicamente nos devuelva nuevos registros a insertar en cada uno de los objetos que queremos refrescar, pero el beneficio obtenido es que el número de registros a procesar es menor por lo que necesitará un menor volumen de recursos.

Es importante tener en cuenta que este método es viable cuando únicamente existen inserciones de datos, no es posible utilizarlo si se producen borrados o actualizaciones.

4.3      Técnicas de Procesado

Aunque se puede realizar el procesado mediante la ejecución del fichero XMLA resultante, se recomienda, para un mayor control utilizar PowerShell para un mayor control de los procesos de los distintos modelos SSAS Tabular Model.

Los beneficios en este caso vienen supeditados a las técnicas que empleemos:

§  Creación de particiones en tiempo de ejecución.

§  Posibilidad de habilitar Logs de carga.

5         Vertipaq

Para el análisis de los modelos, una vez implementados, podemos utilizar la herramienta VertiPaq Analyzer.xlsx que mediante la utilización de las vistas DMV permite obtener información relevante del modelo analizado. Las vistas DMV o vistas de administración dinámica de Analysis Services son estructuras de consulta que exponen información sobre las operaciones del servidor local y el estado del servidor. La estructura de consulta es una interfaz para los conjuntos de filas de esquema que devuelven metadatos y la información de supervisión acerca de una instancia de Analysis Services. La información que podemos obtener con el analizador es:

·         Modelo: Tablas, campos, medidas, etc…

·         Uso de memoria: consumo de memoria de cada objeto que compone el modelo.

·         Estructura: Diccionario de datos, relaciones, codificación, etc..

6         Problemas de rendimiento en Excel

Existen una serie de medidas a tener en cuenta para mejorar el rendimiento cuando se trabaja desde Microsoft Excel y se pretende analizar la información de un Modelo Tabular:

6.1      Evitar cruzar dimensiones sin una métrica

A diferencia de los cubos multidimensionales, en los modelos tabulares no se puede asignar una métrica por defecto al modelo. En los modelos tabulares esta métrica se asigna automáticamente y se le denomina “__No measures defined” devolviendo siempre un valor no nulo.

Por esta razón, si no elegimos otra métrica, en el momento en el que se crucen datos de dos dimensiones se realizará siempre un crossjoin pudiendo provocar problemas de rendimiento.

Una buena práctica es modifcar es modificar la métrica “__No measures defined” para que no devuelva información a la hora de realizar un análisis hasta que se incluya una métrica.

Replace:

[__No measures defined] AS 1;
Por:

[__No measures defined] AS NULL, VISIBLE = 0;

o Por:

[__No measures defined] AS 1, VISIBLE = 0; 


La diferencia es que para la primera opción los valores de las dimensiones no se verán hasta que se añada una métrica. En el segundo caso al arrastrar un atributo de una dimensión, este será visible sin necesidad de añadir una medida.

6.2      Evitar convertir a fórmulas las PivotTables

Las tablas de Excel permiten representar los valores extraídos de los modelos tabulares como fórmulas del tipo “CUBEVALUE”. Internamente esto provoca que se ejecuten cientos de fórmulas en una única consulta MDX por lo que el rendimiento suele ser muy pobre.

6.3      Evitar “slicers” conectados a muchas PivotTables

Los slicers en Excel son filtros que pueden ser utilizados para filtrar en varias tablas del mismo documento. Sin embargo, cuando se utiliza un Excel demasiado grande, la tarea de filtrar con un slicer en múltiples tablas puede provocar una degradación importante en el rendimiento del modelo.