Mejores prácticas en Analysis Services (SSAS Best Practices)

Hola de nuevo.

Hace poco tiempo estuve desarrollando una consultoría sobre una solución basada en Analysis Services para analizar cantidades bárbaras de información (del orden de miles de millones de filas en cada tabla). Como resultado de la consultoría hice un recopilatorio de buenas prácticas que toda solución completa de Analysis Services debería seguir. Os lo adjunto a continuación y espero que lo encontréis útil.

Diseño del Data Source View

• En el diseñador de cubos de Visual Studio, se recomienda en los DSVs separar en varios diagramas las tablas para una mayor claridad a la hora de diseñar. Una recomendación general es separar cada tabla de hechos con sus respectivas dimensiones relacionadas en un solo diagrama. Esto ayuda también a diseñar siempre el cubo con un esquema OLAP en mente.

Diseño de las Dimensiones

• Establecer la propiedad HierarchyVisible a false para esconder los atributos que participan en las diferentes jerarquías de las dimensiones. De esta forma se obliga a los usuarios a navegar con las jerarquías y se evitan posibles errores en los resultados de los informes.
• Asignar la propiedad AttributeHierarchyEnabled a false en aquellos atributos que no sean útiles para el análisis de información y permitir a los usuarios adquirir esta información mediante las propiedades del miembro o una acción de detalle, optimizando así el tiempo de procesado y el tamaño de los cubos.
• Los atributos con una cardinalidad cercana a la clave de la dimensión para asignar la propiedad AttributeHierarchyOptimizedState a false, y así evitar la creación de índices innecesarios. En estos casos es recomendable además, si no existe ninguna jerarquía de negocio con ese atributo, habilitar una jerarquía adicional utilizando una agrupación ad-hoc (por ejemplo para el atributo nombre, crear una jerarquía Inicial-Nombre con la inicial del nombre) para facilitar la navegación del usuario.
• Se recomienda además en las dimensiones con atributos con la misma cardinalidad que la clave primaria establecer la propiedad ProcessingGroup a ByTable para evitar consultas distinct en tiempo de procesado.

Relaciones con las tablas de hechos

• Repasar el diseño para encontrar posibles relaciones many-to-many existentes que puedan ser convertidas a relación indirecta, para optimizar el tiempo de procesado y tiempos de respuesta de consultas.
• Se recomienda también en el caso de tener claves foráneas nullables en el relacional utilizar una vista o una named query del DSV para enmascarar los Nulls y redirigirlos a un miembro desconocido “de negocio”, de forma que podamos diferenciar los hechos que se pierden por no tener informado este campo, y los errores de clave foránea que detecte la partición al procesar (2 miembros desconocidos, el nuestro y el de sistema).

Esquema de particionamiento de los grupos de medida:

• Se recomienda siempre particionar los grupos de medida. Las recomendaciones de Analysis Services sugieren particiones de entre 200Mb-3Gb con un máximo de 10-15 millones de filas, hasta un máximo de 2000 particiones.
• Se recomienda particionar las tablas relacionales de la misma forma que se particionan cada uno de los grupos de medida del cubo, para optimizar los accesos a disco en el procesado en paralelo de particiones.
• Se recomienda automatizar la creación y procesado de las particiones de las medidas, y asignar dinámicamente diseños distintos de agregaciones para particiones que varíen de uso con el tiempo (por ejemplo sobre la dimensión tiempo, asignar un esquema con más agregaciones a los últimos X meses mediante programación AMO).

Agregaciones

• Como ya se ha apuntado en el apartado anterior, se recomienda implementar más de un diseño de agregaciones para el conjunto de particiones de un grupo de medida que varíe con el uso a través del tiempo.
• Una vez desplegado habilitar el log durante un tiempo suficiente con un trabajo intensivo de los usuarios de negocio y revisar los diseños de las agregaciones para optimizar aquellas agregaciones más críticas.

Error en SSAS: The attribute key cannot be found when processing...

Este error es el más común que te encontrarás si te dedicas a crear cubos OLAP con SQL Server Analysis Services. Para mí el modo en que este error se corrige dará credibilidad o no a los datos presentados por el cubo.

Pero empezamos por el principio. Comienzas creando las dimensiones y grupos de medida del cubo, repasas las relaciones entre estas dimensiones y cada uno de estos grupos de medida, defines las jerarquías y relaciones entre atributos, incluso defines agregaciones y particiones para optimizar rendimiento y espacio en disco. En el momento en el que vas a procesar el cubo para ver los resultados el siguiente error se muestra:

The attribute key cannot be found when processing: Table: 'TableName', Column: 'ColumnName', Value: 'ErrorValue'. The attribute is 'AttributeName'.

El error en el fondo es muy sencillo. En la tabla de hechos TableName tienes una clave foranea ColumnName que apunta a un registro de la dimensión cuyo atributo es AttributeName, y este registro, seguramente una clave subrogada, no se ha encontrado en la dimensión. Hablando en términos relacionales, se ha roto la integridad referencial pues la clave foranea apunta hacia un valor de clave primaria que no existe.

En Analysis Services tenemos básicamente 4 modos de solventar esta situación:

Opción 1) Modificamos el valor no encontrado con un valor correcto correspondiente a un miembro de la dimensión. Reprocesamos la dimensión y posteriormente volvemos a procesar el cubo.
Opción 2) Modificamos la propiedad ErrorConfiguration del cubo o bien del grupo de medida, indicamos custom, y cambiamos la propiedad KeyErrorLimitAction a StopLogging y la propiedad KeyErrorAction a DiscardRecord. Finalmente procesamos el cubo.
Opción 3) Modificamos la propiedad ErrorConfiguration del cubo o bien del grupo de medida, indicamos custom, y cambiamos la propiedad KeyErrorLimitAction a StopLogging, la propiedad KeyErrorAction a ConvertToUnknown y la propiedad UnknownMember de la dimensión a Visible. Reprocesamos la dimensión y posteriormente volvemos a procesar el cubo.
Opción 4) Añadimos un miembro más a la dimensión llamado Unknown con una clave conocida (0 por ejemplo) y se la asignamos a este registro, de forma que se cumpla la integridad referencial. Reprocesamos la dimensión y posteriormente volvemos a procesar el cubo.

Y ahora las implicaciones. La opción 1 nos puede servir cuando siempre exista un valor correcto y conocido para la dimensión, aunque esto no siempre es válido. En cualquier caso será la opción predilecta si podemos aplicarla. La segunda opción es un suicidio profesional en toda regla. El registro será ignorado por el cubo lo cual quiere decir que no se incluirá en el cálculo de las medidas. En definitiva, el resultado que lanzará el cubo no coincidirá con el resultado calculado en el relacional y el proyecto (y el autor del mismo) perderá toda la credibilidad. La opción 3 es una buena opción a la vez que rápida, el registro se incluirá en un miembro especial de la dimensión llamado miembro desconocido y los resultados calculados por el cubo serán correctos. El único problema puede ser que los usuarios vean raro un miembro fantasma que englobe todos los valores perdidos en el procesado, aunque es cuestión de acostumbrarlos. Finalmente una de mis opciones preferidas, crear un miembro desconocido propio que nos permita tener controlados los resultados que asignamos a este fantasma. De esta manera podemos tener claro qué valores se agregan en el miembro desconocido y solventar los valores que realmente son errores.

En conclusión y por orden de preferencia utilizar la opción 1 siempre que se pueda, la opción 4 si tenemos valores desconocidos y queremos mantener el control total sobre los cálculos, la opción 3 si se quiere ir rápido (en fases de desarrollo y testing por ejemplo), y jamás la opción 2 a no ser que busquemos confundir al usuario o acabar nuestra carrera como ingenieros.