martes, 7 de octubre de 2014

Particiones en SSAS

 

1 - Introducción

1.1 - Objetivos

El objetivo de este Knowledge Package es explicar el uso de particiones y agregaciones (Aggregations) en SQL Server Analysis Services (SSAS).

1.2 - Alcance

Los ejemplos están basados en SSAS 2012 pero los conceptos aplican a otras versiones de Analysis Services como por ejemplo 2005, 2008 R1 y 2008 R2.

2 - Particiones

Las particiones son usadas a la hora de dividir un measure group (tabla de datos) en partes más chicas, estos datos son divididos de forma lógica y solo a nivel del SSAS. El código usado para crear una partición es SQL. Un select basado en todos los campos del measure group (tabla de datos), pero aplicando filtros (rangos) para dividir la tabla en las particiones.

2.1 - Ventajas de usar particiones

Una de las ventajas de usar particiones es que a la hora de procesar el cubo, no hace falta procesar el measure group completo, sino que se pueden seleccionar particiones, de esta forma, se reducen los tiempos de proceso y la carga sobre el servidor de SSAS. Por ejemplo, en una carga diaria de datos, solo haría falta procesar siempre la última partición que la que se estaría actualizando diariamente, y una vez por semana es recomendable hacer un full process del cubo.
Siempre que se agrega un measure group al cubo, se crea por default una partición basada en ese measure group (tabla completa). El motor de SSAS ejecuta queries que poseen menor cantidad de datos a lo que seria ejecutar una query contra el measure group completo. No es lo mismo para el motor ejecutar un query que agrupa datos de 10 años, a tener 10 queries que agrupan datos de un año en particular.

2.2 - Recomendaciones

Asegurarse que al aplicar los filtros no se esté repitiendo datos en 2 particiones diferentes. En otras palabras, asegurarse que los rangos de filtrado, no se superpongan ni hagan que se pierdan datos. Ya que cuando se trabaja con "<","<=",">",">=" y fechas es muy factible que se repitan 2 fechas en diferentes particiones.
No siempre es recomendable crear particiones, si las tablas son chicas, en estos casos no haría falta crear particiones. Cuando la tabla supera las 3 millones de filas, en esos casos empieza a ser recomendable el uso de particiones. El tamaño recomendado es 20 millones de tuplas por partición, o máximo 250 MB(SSAS 2012).

2.3 - Modos de almacenamiento:

Son las formas en que las particiones guardan los datos. Tener en cuenta que cada particion puede tener su propio modo de almacenamiento.

2.3.1 - MOLAP (Multi dimensional Online Analytical Processing)

Es el más usado, apunta al la performance del cubo(tiempo de respuesta bajo).Los datos y agregaciones son guardados por el motor de SSAS. Los cambios de datos en las tablas van a ser reflejados en el cubo, una vez que este sea procesado o la partición procesada.

2.3.2 - ROLAP (Relational Online Analytical Processing)

A diferencia de MOLAP, el ROLAP no hace una copia de los datos al SSAS, sino guarda los datos en vistas indexadas, pero en el motor de base de datos. El tiempo de consultas y proceso es más lento, pero la gran ventaja es que permite ver los datos de las tablas en tiempo real.

2.3.3 - HOLAP (Hybrid Online Analytical Processing)

El HOLAP toma características del MOLAP y ROLAP. Una parte de los datos es almacenado en el motor de SSAS, los más recientes, para que sean accedidos de forma mas rápida, mientras que los restantes datos trabajan igual que ROLAP. Si se producen cambios en las tablas, la partición debe ser re-procesada.

2.4 - Ejemplo de particiones

Un breve ejemplo de cómo se podrían crear particiones usando diferentes filtros de tiempos (en este caso se usan rangos de meses), mostrando que los filtros pueden ser variables.

Tabla

Rango de años

Particiones

Tabla 1
(Measure Group)

2011

Particion A: Enero 2011 - Junio 2011

Particion B: Julio 2011 - Diciembre 2011

2012

Partición A: Enero 2012 - Junio 2012

Partición B: Julio 2012 - Diciembre 2012

Tabla 2
(Measure Group)

2013

Particion A: Enero 2013 - Marzo 2013

Partición B: Abril 2013 - Junio 2013

Partición C: Julio 2013 - Septiembre 2013

Partición D: Octubre 2013 - Diciembre 2013

3 - Agregaciones (Aggregations)

Las agregaciones se generan para mejorar los tiempos de respuesta de las consultas al cubo. Son parte de las particiones, ya que cada a cada partición tiene sus propias agregaciones.
Las agregaciones son datos pre-calculados y almacenados para distintos niveles del cubo. Una forma de verlas seria como usando un Group By en una consulta de SQL. Las agregaciones son calculadas al momento de procesar el cubo, por lo tanto a mayor número de agregaciones mayor va a ser el tiempo de proceso. Tener en cuenta que las agregaciones aumentan el tamaño en disco usado por el cubo.

3.1 - Diseño de Agregaciones

Aplica a los atributos con los que está relacionado esa partición.

Full

La agregación va a incluir el atributo seleccionado de la dimensión

None

Ninguna agregación va a incluir ese atributo

Unrestricted

El motor de SSAS va a decidir si el atributo va a ser incluido o no

Default

Cada atributo es evaluado por su granularidad, determinada en la dimensión.
Los atributos usados en jerarquías creadas por el usuario son tratados como "Unrestricted"
Los atributos que forman parte de una relación Many-to-Many, son tratados como None
El resto de los atributos es tratado como None

3.2 - Aggregation Options

A la hora de setear las agregaciones, existen 4 opciones:

Estimated Storage reaches

Crear cierto número de agregaciones hasta que alcanza un tamaño (MB)
estimado por el usuario, esto es almacenado en disco

Performance Gain Reaches

Estimar el % de ganancia que se quiere obtener a al usar agregaciones,
ese % aplica a la hora de usar esa measure, debe ser estipulado por el usuario.
Tener en cuenta que este porcentaje es estimativo

I click Stop

El motor crea agregaciones hasta que el usuario le diga lo pare

Do not desing Aggregations

No se diseñan agregaciones

4 - Ejemplo

Este ejemplo está basado en la Adventure Works 2012 que proporciona Microsoft de forma gratuita.

1) Ir a la solapa de "Partitions" y clickear en "New Partition"

clip_image002

2) Seleccionar la tabla sobre la que se quieren crear particiones.

clip_image003

3) Especificar el query para dividir las particiones

clip_image004

4) Determinar el nombre de la partición

clip_image006

5) Diseño de agregaciones:

clip_image007

6) El motor cuenta el número de filas que tiene esa partición

clip_image009

7) Opciones de agregaciones.

clip_image010

4 - Enlaces

AdventureWorksDW Database: Link

AdventureWorks SSAS: Link (seleccionar "AdventureWorks Multidimensional Models SQL Server 2012")

SQL Server 2008 Analysis Services Performance Guide: Link

 

¡Gracias por tu contribución Andrés Bernardo!