En la mayoría de proyectos de BI, por no decir en todos, es necesario generar consultas de SQL, en mi caso T-SQL para SQL Server, para completar tareas de las distintas etapas de modelado de datos para análisis: extracción, staging, etc… Scripts de creación de tablas, índices, select, updates, todo lo que sea necesario para conformar un eficiente modelo dimensional.
Podemos encontramos con datos transaccionales que guardan relación padre-hijo en la misma tabla y nos interesa extraer esta relación como jerarquía para el modelo dimensional. El ejemplo clásico de este tipo de relación suele ser la entidad empleado y la organización jerárquica de funciones.
Vamos a ver la tabla:
Create table Employee (
EmployeeID int , FullName varchar(255)
, ManagerID int )Y los datos con los que vamos a trabajar:
insert into Employee (EmployeeID,FullName,ManagerID)values (1, 'Peter Brewick', null)
,(2, 'Margaret Thompson', 1),(3, 'Michel Sams', 1),(4, 'Susan Rogers', 3),(5, 'Tom Smith', 3),(5, 'John Little', 2)La columna ManagerID hace referencia a EmployeeID de forma que para obtener el responsable de cada empleado hacemos la siguiente consulta:
1: Select e.EmployeeID, e.FullName EmployeeName, e.ManagerId, m.FullName ManagerName
2: from Employee e left join Employee m on e.ManagerID = m.EmployeeID
Y efectivamente tenemos el nombre de responsable a nivel de cada fila. Esto puede complicarse si nos plantearan conocer el nivel del personal dentro de la relación, recorrer el árbol en busca de todos los niveles. Es decir, si mi jefe tiene un jefe (suele pasar
) yo estaría situado en el tercer nivel en la jerarquía ¿cómo consultamos este dato?Las Common Table Expressions (CTE) fueron introducidas en SQL Server en su versión 9.0 (2005) y son de gran utilidad para resolver algunos escenarios, como el que tenemos por ejemplo.
Aunque existen varias alternativas para obtener los datos, voy a utilizar una consulta recursiva que escribió nuestro compañero Javier Loria (blog | twitter) en el blog de SolidQ hace ya unos años:
WITH Empleados (EmployeeID, ManagerID, Level) AS (
SELECT EmployeeID, ManagerID, 1FROM EmployeeWHERE ManagerID IS NULL
UNION ALLSELECT Employee.EmployeeID, Employee.ManagerID, Empleados.Level+1
FROM EmployeeJOIN EmpleadosON Empleados.EmployeeID=Employee.ManagerID)
SELECT * FROM Empleados
Con esta query conseguimos listar todos los empleados con su respectivo responsable además de informar del nivel en el que se encuentra dentro de la jerarquía, recorriendo todos los niveles. Básicamente se utiliza un dataset base (nivel 0) y se hace unión con la misma consulta haciendo Join por el campo que referencia al padre contra la propia CTE, de esta forma se van generando los distintos niveles:
Este tipo de consultas puede ser especialmente útil en escenarios dónde sea necesario filtrar datos agregables por su nivel en una jerarquía.
En la MSDN puedes encontrar mas información acerca de el uso de Common Table Expression y las consultas recursivas
Espero que les sea de utilidad
