Cuando nos enfrentamos a un proyecto de BI necesitamos planificar distintas fases y tareas, algunas de ellas son interesantes y retadoras, otras por el contrario monótonas y repetitivas. Durante la charla veremos que piezas son necesarias en una solución ágil y como podemos acortar el ciclo de vida de desarrollo de forma drástica.

Como continuación veremos en una sesión eminentemente práctica, cómo construir con esa metodología ágil una solución de BI desde el principio hasta el final. Para ello en la parte de ETL nos apoyaremos en el lenguaje BIML, que nos permite hacer más rápidos nuestros desarrollos.

 



  1. 1. #SQSummit 26, 27 y 28 de Mayo Arquitectura general de una solución Agile BI Alfonso Carrera acarrera@SolidQ.com DPS Miguel Egea megea@SolidQ.com Mentor Carmen Martínez cmartinezprado@SolidQ.com DPE
  2. 2. #SQSummit Agenda • Introducción a BIML • Patrones de diseño • Framework ¿Qué, cómo y por qué? • Fases de una solución BI • Desarrollo completo de una solución BI con Framework Son objetivos de esta sesión… Madrid · 27, 28 y 29 de mayo de 2015
  3. 3. #SQSummit ¿Qué es BIML? – Lenguaje de Marcado para Business Intelligence – Inspirado en el “Proyecto Vulcan” (Microsoft) – Desarrollado por Varigence en 2008 – Sintaxis XML con código .NET embebido (opcional) – Tres opciones de implementación (desarrollo): – Mist (IDE de pago de Varigence) – BIDS Helper (Open Source) – BimlScript.com (Editor de código online) 4
  4. 4. #SQSummit Requisitos – BIDS Helper • https://bidshelper.codeplex.com/ – SSIS Multiple Hash No es necesario para el desarrollo con BIML pero es muy útil para mejorar y acelerar la carga de datos • https://ssismhash.codeplex.com/ 5
  5. 5. #SQSummit Desventajas – Necesidad de conocer C# o VB (para usar scripts) – La mayoria de las veces, Intellisense deja de funcionar cuando detecta sintaxis no XML. Por lo tanto, no tenemos Intellisense para C# o VB – La complejidad de los paquetes generados depende de tu conocimiento de BIML (*) 6
  6. 6. #SQSummit Ventajas – Facilidad para crear y cambiar paquetes – No más tareas aburridas y repetitivas – Reusabilidad: de 2005 a 2014 – Flexibilidad: la complejidad de un paquete depende de tu conocimiento de BIML (*) – Disminución del tiempo de desarrollo: haz en un día lo que antes hacías en 3 o 4 7
  7. 7. #SQSummit DEMO 8 Nuestro primer BIML
  8. 8. #SQSummit Comparación de Sintaxis • BIML SSIS 9
  9. 9. #SQSummit Conclusiones Vale, la tecnología funciona pero … • … puedo crear los mismos paquetes, y más rápido, de la forma tradicional Entonces, vamos a introducir el “BIML Dinámico” 10
  10. 10. #SQSummit BIML Dinámico (I) – Extiende las capacidades de BIML con C# o VB – Nuevas posibilidades como: iterar por tablas, añadir expresiones, parametrizaciones … – Crear paquetes SSIS de forma iterativa – En conclusion: “Usar BIML como parte de un FrameWork” 11
  11. 11. #SQSummit BIML Dinámico (II) – Si necesitamos crear varios paquetes con la misma estructura (ejemplo: carga de Staging) – Creamos una plantilla con BIML Dinámico, con la que podremos: – Iterar por tablas – Crear tablas destino – Cambiar nombres de paquetes – Cambiar cadenas de conexión (si es necesario) – … 12
  12. 12. #SQSummit BIML Dinámico – Como funciona – Introducimos bloques de codigo: – Directivas: – <#@ … #> – Bloques de Control: – <# … #> – Expresiones de Bloques de Control – <#= … #> 13
  13. 13. #SQSummit DEMO 14 Nuestro primer BIML Dinámico
  14. 14. #SQSummit Conclusiones – Reusabilidad: – Menos esfuerzo – Menos tiempo de desarrollo – Escalabilidad (estático a dinámico) – Puede que la curva de aprendizaje sea compleja, pero los resultados son totalmente satisfactorios – En definitiva: Excelente relación Coste – Beneficio 15
  15. 15. #SQSummit Patrones de Diseño – Necesitamos crear paquete de SSIS de una forma rápida y eficiente – Para alcanzar este objetivo, podemos aprovechar los “Patrones de Diseño” – Cada problema puede tener varias soluciones, pero muchas veces solo una es la adecuada – Podemos confiar en estos Patrones para alcanzar esta solución. Entonces ¿ por qué no creamos un FrameWork basado en estos “Patrones de Diseño”? 16
  16. 16. #SQSummit Problemas Típicos – ¿Los orígenes de datos y el Data Warehouse están en la misma red? – ¿Carga incremental o total? – Orígenes de datos: ¿On Premise, Azure …? – ¿Discos duros rápidos? – ¿Suficiente memoria? – ¿Puedo habilitar CDC en los orígenes de datos? – ¿Necesitamos manejar SCD? – … 17
  17. 17. #SQSummit Patrones de Diseño 18 “Una solución general y reutilizable para un problema común que ocurre dentro de un contexto dado”
  18. 18. #SQSummit Carga total de Staging 19
  19. 19. #SQSummit Carga incremental de Staging 20
  20. 20. #SQSummit Carga diferencial de Staging (I) 21
  21. 21. #SQSummit Carga diferencial de Staging (II) 22
  22. 22. #SQSummit SCD Tipo 1 y SCD Tipo 2 23 Start Lookup Dimension Id and MD5 Checksum From Business Key Calculate MD5 Checksum of Non- SCD-Key Colums Dimension Id is Null? Yes Insert new members into DWH No Checksum are different? Yes Store into temp table Merge data from temp table to DWH End
  23. 23. #SQSummit Carga de una table de hechos 24
  24. 24. #SQSummit 25 Árbol de decisión STG
  25. 25. #SQSummit 26 Árbol de decisión DWH
  26. 26. #SQSummit Necesitamos un FrameWork porque – DW es un trabajo de equipo – Necesitamos definir reglas de implementación estándar – Cada persona tiene su propia forma de interpretar la solución arquitectónica adoptada – La calidad de la implementación es directamente proporcional a la experiencia de quien la implementa – Pierdes una gran cantidad de tiempo implementando “soluciones técnicas”, Este tiempo podría ser empleado para identificar la resolución óptima de los “problemas de negocio” 27
  27. 27. #SQSummit Necesitamos un FrameWork porque – Debemos “garantizar” un mínimo de calidad independientemente de quien haga el trabajo – Debemos facilitar la integración de nueva gente en el equipo de trabajo, así como la sustitución de personas – Es vital que dejemos que la gente haga lo que mejor saben hacer: esto dará un valor añadido a la solución. El “trabajo de monos” deber ser lo más pequeño posible – Básicamente: “Necesitamos definir formalmente una serie de reglas de trabajo” 28
  28. 28. #SQSummit Regla de oro “ Necesitamos pasar más tiempo en la búsqueda de la mejor solución para los problemas de negocio y menos en implementar la solución “ 29
  29. 29. #SQSummit Bases de Datos 30 1. Núcleo • Staging • Data Warehouse 2. Opcional (recomendado) • Helper 3. Soporte • Log • Metadata OLTP SYS 1 OLTP SYS 2 Helper 1 Helper 2 Staging Data Warehouse MetadataLog
  30. 30. #SQSummit Bases de Datos • Helper Contiene objetos que permiten acceder a los datos de las bases de datos OLTP 31 OLTP SYS 1 OLTP SYS 2 Helper 1 Helper 2 Staging Data Warehouse MetadataLog
  31. 31. #SQSummit Bases de Datos • Staging • Contiene datos Intermedios “volátiles” • Contiene procedimientos ETL 32 OLTP SYS 1 OLTP SYS 2 Helper 1 Helper 2 Staging Data Warehouse MetadataLog
  32. 32. #SQSummit Bases de Datos • Data Warehouse La base de datos final 33 OLTP SYS 1 OLTP SYS 2 Helper 1 Helper 2 Staging Data Warehouse MetadataLog
  33. 33. #SQSummit Bases de Datos • Metadata Contiene toda la información necesaria para automatizar la creación y la carga de Staging y Data Warehouse • Log 34 OLTP SYS 1 OLTP SYS 2 Helper 1 Helper 2 Staging Data Warehouse MetadataLog
  34. 34. #SQSummit Bases de Datos 35 1. Convención de nombres: • NombreProyecto_* • * = LOG, STG, DWH, MD, HLP 2. Ficheros de las bases de datos • STG & DWH “DEBEN” crearse con 2 filegroups (por lo menos) • PRIMARY (catalogos del sistema), • SECONDARY (todas las otras tablas). Será el filegroup por defecto • Altamente recomendado también para otras bases de datos
  35. 35. #SQSummit Esquemas 36 Los esquemas ayudan a • crear relaciones lógicas • distinguir distintos ámbitos Usar varios esquemas para distinguir los distintos ámbitos • stg, etl, cfg, dwh, tmp, bi, err, olap, rpt • opcional el esquema “util” para almacenar objetos de utilidad • ejemplo: fn_Nums, una función que genera números Un esquema (generalmente) no puede ser usado en más de una base de datos • Previene errores por descuidos
  36. 36. #SQSummit Esquemas 37 bi Helper stg etl tmp err util Staging dwh olap rpt DWH bi OLTP md MetaData log Log
  37. 37. #SQSummit Vistas 38 Reglas generales • Preparativos básicos a los datos para simplificar el desarrollo de los paquetes SSIS • Casteos • Renombar columnas • Filtrado básico de datos • Limpieza y normalización de datos • Join de tablas
  38. 38. #SQSummit Procedimientos almacenados 39 Su uso debería ser muy limitado • La mayoría de la lógica ETL en el paquete SSIS Uso • Carga incremental/Gestión • Cargas SCD (MERGE) • Gestión de miembros Dummy • Abstracción adicional que ayuda a evitar tener que cambiar los paquetes SSIS • Para debug (importer una fila especifica de una table) • Para optimizaciones (tunning de consultas) • Para ordenar información
  39. 39. #SQSummit Cargando el Data Warehouse 40 Cargar el DWH significa hacer ETL • Extraer datos de distintos orígenes • Bases de datos, Ficheros, Servicios Web, etc. • Transformar los datos extraidos para que • Puedan ser limpiados y verificados • Puedan ser enriquecidos con otros datos • Puedan ser puestos en un esquema en estrella • Cargar los datos en el Data Warehouse
  40. 40. #SQSummit Fundamentos para cargar el DW – Intentar entender lo que hace el ETL simplemente “leyendo” los paquetes SSIS – “Lógica simple”: usar vistas – “Lógica compleja”: ETL con paquetes SSIS – SSIS NUNCA debe cargar datos directamente desde la tabla; usaremos SIEMPRE vistas como origen (para reducir así la complejidad de los paquetes), pero … – … algunas veces, al cargar dimensiones y tablas de hechos, las vistas no reducen la complejidad por lo que en estos casos es completamente aceptable cargar datos directamente desde las tablas 41
  41. 41. #SQSummit Cargando el Data Warehouse 42 STG ETLETL OLTP DWH Proceso Técnico Proceso Técnico ETL Proceso de Negocio
  42. 42. #SQSummit Procesos de Negocio • Manipulación de datos, transformación, enriquecimiento y lógica de limpieza • Específica para cada cliente. Poco o nada automatizable
  43. 43. #SQSummit Procesos Técnicos • Aplicar técnicas de extracción y carga de datos • Recurrente (patrón) en cualquier solución • Altamente automatizable
  44. 44. #SQSummit Carga de un Data Warehouse 45 ETL es usualmente la fase más compleja y larga • Alrededor de un 80% del trabajo total se hace aquí Integration Services es el motor que usamos para hacer ETL • Muy muy rápido • Completamente en memoria • Disponibilidad para 64 bits • Muy escalable
  45. 45. #SQSummit Carga de un Data Warehouse 46 SSIS NO sustituye a T-SQL Se complementan perfectamente • T-SQL: ideal para manipulaciones simples de conjuntos de datos • SSIS: ideal para manipulaciones complejas de conjuntos de datos • Scripts avanzados a través de Expresiones SSIS o .NET
  46. 46. #SQSummit Carga de un Data Warehouse – Fase E 47 OLTP STGExtracción y Carga Vistas HLP Otros Orígenes
  47. 47. #SQSummit Carga de un Data Warehouse – Fase E 48 El primer paso es cargar datos en el área de Staging • De distintos orígenes de datos • Sin “transformaciones”, simplemente copiar el dato tal cual • En otras palabras, crear una copia de los datos OLTP que usaremos en la solución de BI • Total o Parcial (en función de si usamos carga incremental) • Esto nos permitirá hacer consultas ETL complejas sin interferir con los sistemas de producción • Solo filtraremos datos que por definición no manejaremos en la solución BI • Datos de ejemplo o de Test
  48. 48. #SQSummit Base de datos “Helper” 49 Crea las vistas que sean necesarias para crear el DWH • Las vistas son simples “SELECT columna FROM…” • No realizar transformación • No casteos, ni renombrado de columnas, ni limpieza de datos • Solo filtrar los datos que nunca se usaran en el DWH
  49. 49. #SQSummit Base de datos “Metadata” 50 Crear y rellenar las tablas con toda la información necesaria para automatizar la creación y la carga de las tablas de STG • Tabla origen, tabla destino, columna incremental, patrón … Estas tablas se crearán en el esquema md
  50. 50. #SQSummit Carga de un Data Warehouse – Fase T 51 El Segundo paso es transformar los datos para cargarlos en el DWH • “Transformar” puede ser complejo • Transformar = Limpiar, Chequear, Corrección… • Los datos pueden tener que pasar por varias transformaciones • Aquí es donde se parará la mayor parte del tiempo
  51. 51. #SQSummit Carga de un Data Warehouse – Fase T 52 STG ETLVistas Procedimientos
  52. 52. #SQSummit Base de datos “Staging” 53 Contiene una copia del origen • Solo los datos necesarios, por supuesto • Copiar los datos es rápido. Esto nos permite evitar el uso de las bases de datos Origen durante demasiado tiempo • Evita problemas de concurrencia • El resto de trabajo se realiza en el servidor de BI, no afectando a los orígenes. Los datos de las tablas de orígenes tienen que ser copiados en las tablas de staging • Las tablas deben tener el mismo esquema que el origen • Las tablas de staging deben ser creadas en el esquema de staging
  53. 53. #SQSummit Base de datos “Staging” 54 1. Contienen tablas intermedias que usaremos en la transformación de los datos • Las tablas intermedias favorecen en el sentido de no tener todo en memoria SSIS • Esto hará que la depuración/solución de problemas sea ¡más fácil! • El equilibrio correcto para decidir cuántos tablas intermedias se necesitan depende de cada proyecto
  54. 54. #SQSummit Base de datos “Staging” 55 Tablas utilizadas para mantener los datos procedentes de archivos • Ej.: Excel, archivos planos • Utilice el esquema etl Tablas intermedias • Usar esquema tmp Objetos utilizados en la fase de ETL • Vistas, procedimientos almacenados, funciones definidas por el usuario,… • Todos estos objetos deben estar en el esquema etl
  55. 55. #SQSummit Carga de un Data Warehouse – Fase L 56 STG DWH SSIS Vistas Procedimientos
  56. 56. #SQSummit Carga de un Data Warehouse – Fase L 57 Este tercer paso es la carga del DWH • Muy sencillo: basta con tomar los datos transformados del en la base de datos de staging y cargarlos en los Hechos y Dimensiones • Cargar todas las dimensiones • Generar identificadores dimensión • Cargar todos los hechos • «Sólo» convertir claves de negocio para dimensionar los ID No es tan fácil  • Debe manejar carga incremental • Obligatoria para las dimensiones (de lo contrario puede tener problemas si los datos cargados tienen diferente ID dimensión) • Sería bueno también para los hechos • Más compleja cuando haya que implementar «llega temprano un hecho»
  57. 57. #SQSummit Base de datos “Data Warehouse” 58 La base de datos de DWH debe contener solamente • tablas relacionadas con el hecho de dwh y dimensiones • todas las tablas deben estar en el esquema dwh Vistas para permitir el acceso a las tablas físicas • Utilizar esquemas específicos para exponer datos a otras herramientas • Utilizar el esquema OLAP para las vistas utilizadas por SSAS • El uso de esquemas rpt para vistas utilizado por SSRS • Agregue su propio esquema en función de la tecnología que utilice • O incluso crear un Data Mart fuera del DWH
  58. 58. #SQSummit • «E» y «L» deben ser • Simples • Completamente automático • Completamente reusable • Tienen CERO valor en una solución BI • Deberían hacerse de la manera más económica • «T» debe ser • Compleja • No automática • Donde se debe emplear el mayor tiempo (entra a jugar la lógica de negocio) Fases ETL
  59. 59. #SQSummit Framework BI – Staging 60 Pasos a seguir a la hora de implantar la solución del Framework BI para la carga de un DWH 1. Carga área Staging 1. Crear las bases de datos • MD • HLP • STG • DWH • LOG 2. Crear las vistas en la base de datos Helper, en el esquema bi (Ideal) STG ETL OLTP
  60. 60. #SQSummit Framework BI – Staging 61 3. Rellena la tabla de metadatos [md] [extract_phase_info], con la información necesaria para la creación y carga de las tablas de Staging • Tabla origen • Tabla destino • Patrón de carga: tipo de BIML que se usa para crear el paquete (DLH,Full,CDC…) • Activo para crear • Activo para cargar • Tipo de origen (sight, Excel, query,VIEW …) • Clave primaria • Columna incremental
  61. 61. #SQSummit Framework BI – Staging 62 ¿Como rellenar la tabla de metadatos? • Excel que se rellena a mano • De forma automática con un paquete que se conecta a Helper y extrae del information schemaç • Otras
  62. 62. #SQSummit Framework BI – Staging 63 3. Crear las tablas de Staging. El patron BIML Script Generator recorre la tabla de metadatos y genera el script de creación para cada una de ellas 4. Carga tabla de Staging. Selecciona el patrón que se usará para la carga y genera los paquetes de carga con dicho patrón • Load-Staging-CDC-TS-Lookup • Load-Staging-Differential-Hash-Merge • Load-Staging-Full • Load-Staging-Differential-TS-Lookup • Load-Staging-Differential-Hash-Lookup 5. Crea un orquestador que llame a cada un de los paquetes creados en el paso anterior • Orquestador STG
  63. 63. #SQSummit Framework BI – DWH 64 2. Carga área DWH 1. Crea las vistas en la base de datos STG, en el esquema etl. Realiza en este paso las transformaciones necesarias para la creación de las dimensiones y hechos (Ideal) 2. Convención de nombres de las vistas • etl.vw_dim_DimensionName • etl.vw_fact_FactName STG ETL
  64. 64. #SQSummit Framework BI – DWH Dimensiones 65 3. Rellena las tablas de metadatos de las dimensiones con el paquete Script_Load_MD_Dimension.dtsx • [md].[dimensions_load_phase_info] • Tabla origen • Tabla destino • Clave primaria • Columna incremental • Activo para crear • Activo para cargar
  65. 65. #SQSummit Framework BI – DWH Dimensiones 66 • [md].[dimensions_load_phase_info_columns] • Tabla origen • Tabla destino • Tipo cambio Tres tipos de SCD
  66. 66. #SQSummit Framework BI – DWH SCD Tipo 0 67 Actualiza todas las versiones de ese dato Implementación: UPDATE
  67. 67. #SQSummit Framework BI – DWH SCD Tipo 1 68 Actualiza la última versión de ese dato Implementación: UPDATE
  68. 68. #SQSummit Framework BI – DWH SCD Tipo 2 69 Mantener los todos los valores del pasado y las actuales Implementación: Fila válida en el tiempo + UPDATE + INSERT
  69. 69. #SQSummit Framework BI – DWH Hechos 70 4. Convención de nombres para las columnas de los hechos. Las columnas de los hechos que hacen Lookup contra las dimensiones deberán nombrarse la siguiente modo: • rp_RoleName_Dimension_Atributo 5. Rellena las tablas de metadatos de los hechos con el paquete Script_Load_MD_Fact.dtsx
  70. 70. #SQSummit Framework BI – DWH Hechos 71 • [md].[load_phase_info] • Tabla origen • Tabla destino • Clave primaria • Activo para crear • Activo para cargar • [md].[load_phase_info_columns] • Tabla origen • Columna origen • Tabla Dimension • Columna Dimension • Columna salida
  71. 71. #SQSummit Framework BI – DWH 72 6. Crear las tablas de DWH. Existen los patrones BIML Script Generator DWH Fact y Generator DWH Dimensions que recorre la tabla de metadatos y generan los scripts de creación para cada una de ellas. 7. Carga tablas DWH. Selecciona el patrón que se usará para la carga y genera los paquetes de carga con dicho patron • Load-Dim-Differential-Hash-Lookup.biml • Load-Fact-Differential-Hash-Lookup.biml 8. Crea un orquestador que llame a cada uno de las paquetes generados anteriormente para el DWH • OrquestadorDWH
  72. 72. #SQSummit No más trabajo de Monos! Deja a las personas pensar y a las máquinas que hagan el «Trabajo de Monos»
  73. 73. #SQSummit ¿Preguntas? 74
  74. 74. #SQSummit ¡Gracias! ¿Te has quedado con alguna duda? Consúltanos. Adaptive BI Framework es un método exclusivo desarrollado por SolidQ a nivel global, para el mantenimiento y desarrollo de soluciones de Business Intelligence. SolidQ ofrece servicios de Asesoramiento, Orientación (un mentor acompañando a los empleados internos de la empresa) y Formación a sus clientes. El método del Adaptive BI Framework se aplicará al desarrollo de las soluciones de Inteligencia Empresarial para cada área. www.adaptivebi.it
  75. 75. #SQSummit • Máster en BI sobre tecnologías Microsoft Más información Estudia un Máster con SolidQ • Máster en Big Data Más información ¿Quieres dar un giro a tu Carrera profesional y no encuentras la forma? ¿Tu empresa te pide más conocimientos y no sabes cómo adquirirlos? ¿No abordas proyectos nuevos o hay proyectos que no avanzan por falta de conocimiento? Si has contestado a alguna de estas preguntas que “sí”, entonces deberías estudiar un máster certificado por SolidQ. Fechas Octubre 2015 – Octubre 2016