Determinantes - La respuesta a un misterio en Framework Manager

Hola

Los determinantes juegan un papel crucial en el rendimiento general así como en la consistencia de nuestros modelos de Framework Manager, aunque como todos sabeis, son uno de los aspectos mas confusos de esta aplicación para la mayoría de los desarrolladores. Este post intenta acabar con esta confusión


Los determinantes se usan para que una tabla que tiene un nivel de detalle se comporte como si tuviese otro nivel de detalle. Se utilizan en las tablas de dimensiones cuando las tablas de hechos se relacionan a diferentes niveles en las dimensiones.

La situación
Vamos a utilizar un ejemplo con una dimensión de fecha con un detalle a nivel de día. Si todas las tablas de hechos tienen el nivel de día, el mas detallado, entonces no es necesario determinantes. Pero como todos sabemos, esto no es siempre el caso.

El problema
Así que el problema surge cuando queremos unir la tabla de dimensión a un nivel que no es el nivel mas bajo. Imagina una tabla de hechos de forecast donde el nivel es mes, es decir una fila por mes. Una unión a la month_id (por ejemplo, 2011-09) nos devolvería desde 28 a 31 registros (dependiendo del mes)  de la dimensión de fecha y nos realizaría mal los cálculos.

El SQL
Es una buena costumbre pensar sobre el código SQL que queremos que se genere. Sin determinantes, el SQL incorrecto aparecería algo así como

select
f.forecast_value,
d.month_id,
d.mont_name
from sales_forecast f inner join date_dim d on
f.month_id = d.month_id


Este código nos recuperaría hasta 31 registros por cada registro de la tabla de forecast. Si aplicamos funciones matemáticas, por ejemplo Sum o Count, nos produciría valores incorrectos. Entonces queremos que se genere algo parecido a lo siguiente, en el cual se crea una linea por mes, Y ENTONCES unimos con la tabla de hechos


select
f.forecast_value,
d1.month_id,
d1.mont_name
from sales_forecast f inner join 
( select distinct
d.month_id,
d.month_name
from date_dim ) as d1
on f.month_id=d1.month_id


Como vemos, el truco esta en entender que columnas de la tabla de dimension estan relacionadas con month_id, y por ello son unicas. Esto es lo que los determinantes nos permiten hacer.

Desvelando el misterio en Framework Manager
Siguiendo las best practices de Cognos, los determinantes se deben especificar en la capa en la que se establecen las relaciones entre tablas.

Aqui vemos una dimension de fecha con 4 niveles en la dimension, Year, Quarter, Month y Dia.




Esto significa que podemos tener hasta 4 determinantes en función de la granularidad de las tablas de hechos  que tengamos en el modelo. Los tres primeros niveles, año, trimestre y mes se deben establecer en 'group-by' ya que no definen en si una única fila en la tabla y Framework Manager tiene que ser consciente de que los valores tendrán que ser 'agrupados' para ese nivel. En otras palabras, el código SQL necesita 'agrupar por' una o varias columnas con el fin de identificar de forma única una fila para ese nivel de detalle (por ejemplo, mes o año). El nivel de Dia se debe establecer en 'Uniquely Identified' ya que se identifica de forma única una fila en la tabla de dimensiones. Aunque puede haber varios niveles de 'agrupar por' factores determinantes, por lo general solo hay un determinante que identifica de forma única, identificando por la clave única de la tabla. El determinante de 'identificación única', por definición, contiene todas las columnas que no son clave como atributos.

La sección clave identifica la columna o columnas que identifican de forma única el nivel. Idealmente, se trata de una columna, pero en algunos casos puede ser necesario incluir mas de una columna. Por ejemplo, si el año y mes (1-12) estan en columnas separadas.

Usando la tabla anterior, la configuración sería la siguiente:



La sección Attributes identifica las columnas que son distintas en el nivel. Por ejemplo, en el nivel month_id (2009-12), columnas como month name, month starting date, number of days in a month son todas distintas en este nivel. Obviamente campos de menor nivel, como date o day of week, no se incluirán en este nivel.

También es importante el orden de los determinantes, pues el producto establece un analisis 'top-down' de los campos que utiliza en el informe. Si el informe utiliza Año, Trimestre y Mes, la consulta agrupara por las columnas clave de Año, Trimestre y Mes, pero si el informe utiliza Año y Mes (no el Trimestre) entonces se omitirá la clave de Trimestre.

¿Cuantos niveles necesitamos?
¿Necesitamos los 4 niveles en los determinantes?. Recuerda que los determinantes se utilizan para unir las dimensiones de los niveles mayores a los menores de la dimensión. En este caso, estamos uniendo al nivel mes (month_id). A menos que existan uniones adicional en los niveles de año y trimes, no necesitamos estrictamente especificar esos determinantes. Recuerda que tanto año como trimestre se identifican unicamente por cada mes y por ello deben ser incluidos en los atributos relacionados por mes, como se muestra en la imagen siguiente



El resultado
Siguiendo estos pasos la siguiente SQL se genera para el informe. La sección resaltada esta generada por la configuración de determinantes. Fijate que se agrupa por Month_ID, y usa la función min para garantizar la unicidad a ese nivel. El segundo nivel de group by es la agregación normal para cada registro. Así que el resultado es que la agregación este realizada correctamente, en el cual cada registro mensual de la tabla de hechos se relaciona a 1 registro de la tabla dimensional y presentar los valores correctos en el informe.