lunes, 22 de agosto de 2011

Relaciones Parent – Child en T-SQL


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 Sonrisa)  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:


CTE Parent and Child Result



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 Sonrisa

Curso Business Intelligence en Castellano

 

Tras la publicación del libro ‘Vea el cubo medio lleno’ de nuestro compañero Salvador Ramos (blog | twitter) del que hablamos en una entrada anterior, en los blogs de SolidQ han puesto a nuestra disposición una serie de post que componen un mini curso de Business Intelligence en el que podremos refrescar o adquirir conceptos para entender y desarrollar este tipo de soluciones.

Les dejo los enlaces a las entradas que componen la serie:

 

  1. Curso MS Business Intelligence – Definición del término BI (1)
  2. Curso MS Business Intelligence – Conceptos importantes sobre BI – parte I (2)
  3. Curso MS Business Intelligence – Conceptos importantes sobre BI – parte II (3)
  4. Curso MS Business Intelligence – Componentes de una solución de BI (4)
  5. Curso MS Business Intelligence – Visión y estrategia (5)
  6. Curso MS Business Intelligence – Componentes de la plataforma Microsoft BI (6)
  7. Curso MS Business Intelligence – Herramientas cliente SQL Server (7)
  8. Curso MS Business Intelligence – Introducción completa (8)
  9. Curso MS Business Intelligence - Modelado Dimensional (9)
  10. Curso MS Business Intelligence - Introducción a SSIS (10)
  11. Curso MS Business Intelligence - SSIS ControlFlow (11)
  12. Curso MS Business Intelligence - SSIS Data Flow (12)
  13. Curso MS Business Intelligence SSIS: Buenas prácticas (parte I) (13)
  14. Curso MS Business Intelligence SSIS: Buenas prácticas (parte II) (14)
  15. Curso MS Business Intelligence SSIS: Buenas prácticas (parte III) (15)
  16. Curso MS Business Intelligence SSIS: Logging (parte I) (16)
  17. Curso MS Business Intelligence SSIS: Logging (parte II) (17)
  18. Curso MS Business Intelligence SSIS: Logging (parte III) (18)
  19. Curso MS Business Intelligence SSIS: Configuración, Despliegue y Administración (parte I) (19)
  20. Curso MS Business Intelligence SSIS: Configuración, Despliegue y Administración (parte II) (20)
  21. Curso MS Business Intelligence SSIS: Configuración, Despliegue y Administracion (parte III) (21)
  22. Curso MS Business Intelligence SSIS: SSIS, Despliegue de paquetes (22)
  23. SSIS, Ejecución de paquetes en el servidor (23)
  24. SSIS, Programar la ejecución de paquetes (24)
  25. Fin del módulo de SSIS y recapitulación (25)
  26. SSAS, Qué es Analysis Services (26)
  27. SSAS, Cubos OLAP, Conceptos básicos (parte I) (27)
  28. SSAS, Cubos OLAP, Conceptos básicos (parte II) (28)
  29. SSAS, Cubos OLAP, Conceptos básicos (parte III) (29)
  30. SSAS, Objetos de una BBDD de Analysis Services (parte I – DataSources) (30)
  31. SSAS, Objetos de una BBDD de Analysis Services (parte II – DataSource Views) (31)
  32. SSAS, Objetos de una BBDD de Analysis Services (parte III – Cubos) (32)
  33. SSAS, Objetos de una BBDD de Analysis Services parte IV – Dimensiones) (33)
  34. SSAS, Construyendo y desplegando el modelo (34)
  35. SSAS, Procesamiento y modos de almacenamiento (35)
  36. SSAS, Agregaciones (36)
  37. SSAS, Dimensiones (37)
  38. SSAS, Personalizando el modelo: Atributos (38)
  39. SSAS, Atributos, Jerarquías y Niveles (39)
  40. SSAS, Relaciones entre atributos (40)

/* Actualizado 2013-10-29 */ 

Espero que les sea de interés Guiño

Entradas populares