En algunas ocasiones necesitamos relacionar una tabla con sigo misma, construyendo una relación de autoreferencia o lo que se conoce como relación padre-hijo en el mundo de Inteligencia de Negocio. PowerPivot no tiene soporte explícito para este tipo de relaciones pero con la nueva versión de PowerPivot en SQL Server 2012 disponemos de funciones que nos permiten simularla perfectamente. Ejemplos de estas relaciones son: empleados o las cuentas del libro de cuentas de la organización. Nuestro objetivo es construir algo como lo siguiente:
En esta imagen podemos ver que empleado es manager de cada uno de los empleados. En la versión anterior de PowerPivot no disponíamos de jerarquías y los distintos niveles los teníamos que calcular en la consulta contra el origen sobrecargando así los orígenes debido a la complejidad de estas consultas. Ahora con la nueva versión de PowerPivot podemos resolverlo en 3 pasos:
- Crear una columna calculada en la tabla que nos permita obtener la ruta desde el primer nivel hasta el nivel de la fila concreta. Esto lo hacemos con una función nueva en PowerPivot para SQL 2012 que se llama PATH y que la podemos utilizar de la siguiente manera:
=PATH(DimEmployee[EmployeeKey],DimEmployee[ParentEmployeeKey])
Esta función recibe como primer parámetro la columna de la tabla que tiene que utilizar para buscar el valor del padre y el segundo nos dice que valor tiene que buscar. La función construirá una ruta con el siguiente aspecto:”112|23|18|1”, lo que nos indica que estamos en el empleado con EmployeeKey es el 1 y que su manager es el 18, que a su vez tiene como manager el 23, y así sucesivamente. Este es el resultado de la columna para el ejemplo de AdventureWorks:
- Crear columnas calculadas para cada una de las columnas y para cada nivel que queremos utilizar. Para este ejemplo de empleados con Adventure Works, vamos a obtener el nombre de los tres primeros niveles. Esto lo hacemos también con 2 funciones nuevas que son: LOOKUPVALUE y PATHITEM, y que las podemos utilizar en las columnas calculadas como vemos en el siguiente ejemplo:
=LOOUPVALUE(DimEmployee[FistName],DimEmployee[EmployeeKey],PATHITEM(DimEmployee[RutaEmpleado],1))
En esta columna tenemos que explicar el funcionamiento de dos funciones:
- PATHITEM –> Recibe como primer parámetro la lista generada con la función path y como segundo el elemento que queremos obtener. En este caso estamos diciéndole que queremos obtener el primer elemento de la lista (el empleado que es manager de todos los demás).
- LOOKUPVALUE –> Recibe como primer parámetro la columna que queremos obtener (en nuestro caso el nombre del empleado), como segundo parámetro espera la columna con la que localizaremos al empleado (normalmente la columna que representa a cada elemento de la tabla) y por último, en el tercer parámetro enviaremos el elemento que estamos buscando (el EmployeeKey del manager)
Este es el resultado que obtenemos al crear las tres columnas:
- Por último, podemos construir la jerarquía en PowerPivot utilizando las columnas calculadas creadas.
De esta manera ya podemos construir el informe que necesitábamos. Espero que os haya gustado y nos vemos en futuros post.
También recordaros que este Jueves 24 de Noviembre de 2011 a las 16:00 horas (horario España) impartiré un webcast sobre novedades de DAX, en que hablaremos de este tema y otros relacionados con el mundo de las expresiones para el Análisis de Datos. Espero veros por allí, aquí tenéis el link de registro (Webcast de novedades en DAX).
Un saludo
Ilde
