Cuando hablamos de una dimensión Padre-Hijo (Parent-Child en inglés) debemos tener en mente que si esta crece demasiado podríamos tener problemas de rendimiento. Por este motivo muchas veces se aconseja intentar aplanar la dimensión. Esta tarea puede ser más sencilla o compleja en función de nuestra lógica de negocio. Mi intención con este post no es más que mostrar un pequeño ejemplo que nos encontramos para conocer una de las posibles formas de hacerlo. En nuestro ejemplo tenemos una dimensión Padre-Hijo que dispone de versionado tipo 2 para el atributo que indica quién es el padre. Aquí un ejemplo (para simplificar el ejemplo sólo se muestran las columnas clave):
En la imagen vemos como por ejemplo id 2 primero pertenecía al padre 1 pero desde el 1 de Enero de 2012 pasa a ser un miembro raíz. Además, vemos también como uno de sus descendientes (id=5) cambia durante el primer rango de tiempo valido para el id 2. Cuando decimos que queremos aplanar esta dimensión estamos diciendo que queremos obtener el siguiente resultado:
Una de las restricciones que hay que establecer al aplanar una dimensión Padre-Hijo es el número de niveles que queremos establecer, en este caso lo hemos limitado a 3 niveles. Lo que estamos haciendo es identificar las relaciones Padre-Hijo válidas en un rango de tiempo determinado y escribirlas en un formato columnar en lugar de por filas, de manera que cada columna LVL1, LVL2, LVL3,…. representa un nivel de lo que después será nuestra jerarquía en SSAS. Así no tendremos que utilizar un dimensión Parent-Child para nuestro análisis OLAP.
Ahora la pregunta es sencilla, ¿cómo hacemos esta transformación de una manera sencilla a la par que elegante? La respuesta es utilizando la siguiente consulta:
/******CTE recursiva que nos permite aplanar la dimensión Parent-Child************/
WITH MYCTE (ID, LVL1, LVL2, LVL3, Valid_From, Valid_To, NumberLevel) AS ( /*********Elementos raíz y casos base de la recursión*******************/ SELECT [id] ,[Id] LVL1, [Id] LVL2, [Id] LVL3, Valid_From, Valid_To, 1 AS NumberLevel FROM [dbo].[Source] WHERE Parent_Id IS NULL UNION ALL /*******Establecemos la recursión utilizando en le join la propia MYCTE**********/ SELECT Child.[id] ,Parent.LVL1, /****Columnas que deben cambiar en función del nivel, con un case when sabremos que valor hay que establecer********/ (case when (Parent.NumberLevel<2) then Child.[id] else Parent.LVL2 end) as LVL2, (case when (Parent.NumberLevel<3) then Child.[id] else Parent.LVL3 end) as LVL3, /***Buscar el rango más restrictivo****/ (case when (coalesce(Parent.Valid_From,’20200101′)>coalesce(Child.Valid_From,’20200101′)) then Parent.Valid_From else Child.Valid_From end) Valid_From, (case when (coalesce(Parent.Valid_To,’20200101′)<coalesce(Child.Valid_To,’20200101′)) then Parent.Valid_To else Child.Valid_To end) Valid_To, /********************************/ (Parent.NumberLevel + 1) as NumberLevel FROM MYCTE Parent INNER JOIN [dbo].[Source] Child ON Child.Parent_Id=Parent.[ID] and /******Eliminar las relaciones incorrectas por rango de fechas (puede ser que por reglas de negocio se deba añadir algun filtro)**************/ ( not (Child.Valid_From <coalesce(Parent.Valid_To,’20200101′) and Parent.Valid_From>coalesce(Child.Valid_To,’20200101′)) ) )
/******Una vez creada la CTE lanzamos una select sobre ella***********/ select ID, LVL1, LVL2, LVL3, Valid_From, Valid_To from MYCTE
Espero que os sea útil y cualquier duda o problema que tengáis con este tema estaré encantado de echaros una mano. Saludos
