Construyendo La Arquitectura de una eficiente Bodega de Datos

1. Construyendo La Arquitectura de una eficiente Bodega de Datos Jose Redondo Microsoft SQL Server MVP E-Mail: redondoj@gmail.com Twitter: @redondoj Blog: redondoj.wordpress.com | LinkedIn: https://www.linkedin.com/in/redondoj

2. Speaker • Business Intelligence Architect - Consultant for +20 years • Owner of EntornoDB C.A. in Caracas, Venezuela. Company specialized in the Analysis, Design & Development of Information Systems, BI Solutions & DWH using Microsoft Data Platform (MDP), SyBase, Oracle and IBM • Worked as Desktop - Web - Database in .NET & Java; DBA Data Platform MSFT, IBM & Oracle; and BI and DWH (MDP) Arquitect and Developer • Speaker in events of Microsoft in Latam & PASS SQL Saturday in Latam - U.S.A. • SQL Server, MCP - MSTS – MTA • DPA for SolidQ • Contributing Technical Reviewer for Packt Publishing • Blog in Spanish "El Blog de Cheo Redondo" • Microsoft SQL Server MVP

3. Construyendo La Arquitectura de una Eficiente Bodega de Datos

4. Agenda • Conceptos • Soluciones de Hardware • Data Warehouse vs. Data Mart. Diferencias!!! • Que metodología es la mejor, Kimball o Inmon? • Como poblar un Almacén de Datos

5. Agenda • Migrando datos como ETL o ELT!!! • Claves Subrogadas. Nuestra salvación. • Bases de datos Multidimensionales en SSAS (Cubos OLAP) • Modelo Tabular en SQL Server • Herramientas BI clientes para usuarios finales. Cuales son y como se utilizan?

6. Conceptos

7. Bodega de Datos - Que no es? • NO ES una copia de la base de datos “Origen” con el prefijo de nombramiento de objetos ‘DWH_’ • TAMPOCO es la copia de tablas de datos (Por ej.: ‘Productos’) desde varias bases de datos y/o Plataforma de datos externas unidas todas a través de una vista • MUCHO MENOS un vertedero de datos en tablas procedente de varios orígenes sin existir un diseño congruentes entre las mismas

8. Madurez del Modelo de un Almacén de Datos Fuente: Wayne Eckerson

9. Que es una Bodega de Datos?Para que existe? Razones para la existencia de una Bodega de Datos: • Reduce la sobrecarga de procesamiento en los sistemas de producción • Esta optimizado para el acceso de solo lectura, escaneando los discos secuencialmente • Se encuentra integrado con muchos orígenes de datos • Mantiene un histórico de los registros almacenados (No es necesario guardar copias de reportes en un momento determinado) • Puede reestructurarse / renombrarse las tablas y columnas en su modelo de datos • Se encuentra protegido contra las actualizaciones de los sistemas de datos origen (En Producción)

10. Que es una Bodega de Datos?Para que existe? Razones para la existencia de una Bodega de Datos: • Utiliza la Administración de Datos Maestros (Master Data Management) incluyendo las Jerarquías de datos • No es necesario involucrar al personal IT para que los usuarios crean reportes y áreas de visualización de datos a través de herramientas clientes como Microsoft Excel • Mejora y Optimiza la Calidad de los Datos de manera transparente en los orígenes de datos • Permite mantener una versión de los datos en un punto histórico de su existencia • Es fácil para la creación de soluciones de Inteligencia de Negocio eficientes (Por ej.: Cubos para SQL Server Analysis Services)

11. Porque usar una Bodega de Datos? Aplicaciones Heredadas + Bases de Datos = Caos Total Bodega de Datos Empresarial = Sinónimo de Orden y Éxito Control de Producción Ingeniería • Continuidad • Consolidación • Control • Estandarización • Colaboración CRM | MPR Finanzas Inventarios Inventarios Administración de Partes Administración de Reportes UNA SOLA VERSIÓN DEL DATO Logística Logística Ventas Contabilidad Bodega de Datos Empresarial Materia Prima Mercadeo Pedidos | Facturación Recursos Humanos Control de Envíos Cada consulta = Una decisión Ventas 2 Propósitos de una Bodega de datos: 1.- Reducir el tiempo de creación de reportes empresariales | 2.- Dividir multidimensionalmente escenarios de cualquier índole

12. Soluciones de Hardware

13. Fast Track DWH (FTDW) • Configuración de Referencia optimizada para una Bodega de Datos • Permite reducir el compromiso de recursos en la configuración y construcción de servidores • Perfecto escenario para eliminar problemas de diseños complejos permitiendo ahorrar meses de configuración, instalación, optimización y pruebas • Requerimientos únicos: Instalación de OS (MS Windows Server 2012 R2) y SQL Server 2014

14. Almacenes de Datos (Data Warehouse) • Inteligencia de Negocio (Business Intelligence) • Soluciones de Consolidación de Bases de Datos Pre-Configurado Pre-Optimizado

15. Aplicaciones en la vida diaria • Algunas aplicaciones del mercado: • Dell QuickstartData Warehouse Appliance 1000 x (FT 4.0; 5TB x) • Dell PDW Appliance (v2 x, SQL Server 2012 – 2014, 15TB – 6PB x) • IBM FTDW • HP Enterprise DWH Appliance • HP Business DWH Appliance • HP Business Decision Appliance (BI, SQL Server, SharePoint, PP) • HP Database Consolidation Appliance (Virtual Environment, Windows Server)

16. Data Warehouse vs. Data Mart. Diferencias!!! Data Warehouse (Almacén de Datos | Bodega de Datos) Data Mart (Versión “Especial” de un Almacén de Datos) Subconjunto de una Bodega de Datos que usualmente es orientado en temas empresariales específicos (Por ej.: Finanzas, Márquetin, Ventas, etc) La combinación lógica de todos los Data Mart es lo que conforma un Data Warehouse • Único repositorio organizacional de datos permitiendo enlazar muchas o todas las áreas temáticas de una empresa • Sostiene multiples sectores de negocio • Sustenta mucha información detallada • Trabaja integrando todos los orígenes de datos • Alimenta versiones especiales de un almacén de datos (Data Mart) En resumen, un Almacén o Bodega de Datos (Data Warehouse) es el conjunto de multiples áreas de negocio de una empresa, y un Data Mart es el contenido de una de estas área empresariales de una empresa

17. Que metodología es la mejor,Kimball o Inmon? Dos enfoques para construir y desarrollar Bodegas de Datos (Data Warehouse)

18. Kimball & Inmon - Mitos • Mito: Kimball es un “Criterio de Ejecución” Descendente–Ascendente sin un enfoque empresarial • Realmente es Ascendente-Descendente: La Matrix de ejecución conceptual establece la siguiente ecuación (Procesos Empresariales / Orígenes de Datos) * (Dimensiones conformadas + MDM) • Mito: Inmon requiere una cantidad exagerada de diseño que toma mucho tiempo generarla • Inmon comenta que construir un DW iterativamente, no es un gran trabajo (pag. 91 BDW, pag. 21 Imhoff)

19. Kimball & Inmon - Mitos • Mito: Los Data Mart con esquemas estrella no estan permitidos en el modelo de Inmon • Inmon nos comenta que no son buenos para generarlos directamente en un entorno donde se accedan los datos por los usuarios finales (pag. 365 BDW). Estos esquemas son excelente para entornos Data Mart (pag. 12 TTA) • Mito: Pocas son las compañías que utilizan el método Inmon • Según Survey nos informa que en la actualidad el 39% utiliza Inmon con el 26% Kimball. Muchos de ellos tienen en operación un EDW (Enterprise Data Warehouse – Almacén de Datos Empresarial)

20. Metodología (Kimball & Inmon) Esquema Relacional (Inmon) vs. Esquema Dimensional (Kimball) Esquema Relacional Esquema Dimensional Si tu eres un usuario empresarial, realmente con cual de estos modelos trabajarías en tu escenario actual?

21. Metodología • Modelado Relacional • Modelo Entidad Relación (ER) • Reglas de Normalización • Muchas tablas de datos relacionadas por uniones (Joins) • Tablas Históricas, Claves Naturales • Buena estrategia para el acceso a los datos por parte de los usuarios finales

22. Metodología • Modelado Dimensional • Esquema Estrella. Tablas de Dimensiones y Hechos • Menos tablas que contengan datos duplicados (Desnormalizados) • Fácil para usuario que no entienden la filosofía DWH (pero muy extraño para usuarios IT acostumbrados a utilizar el esquema relacional) • Slowly Changing Dimensions. Claves Subrogadas • Buena estrategia para el acceso directos a los datos por parte de los usuarios finales

23. Metodología • Kimball • Bodega de Datos Lógico (BUS) creado para aplicarse a escenarios empresariales ‘Data Mart’ (Ej.: Ventas, Finanzas, etc) • Aplicado empresarialmente con el fin de tener una máxima participación de todos los usuarios • Data Mart descentralizado (No requiere ser separado físicamente del Almacén de Datos) • Optimizado para escenarios de Reportes Analíticos y Análisis de Datos OLAP independientemente de la dimensionalidad del Data Mart • Integrado vía ‘Dimensiones Conformadas’ (Provee consistencia de orígenes de datos cruzados) • 2 capas (Data Mart, Cubos OLAP), Menos ETLs, Duplicación de Datos nula

24. Metodología • Inmon • Modelo de Datos Empresariales (CIF) que es un Almacén (Bodega) de Datos Empresarial (EDW – Enterprise Data Warehouse) • Aplicado a usuarios IT con el fin de tener una mínima participación de dichos usuarios • Centralización Atómica de Tablas Normalizadas (Fuera del limite de uso de usuarios finales) • Posteriormente crea dependencia de los Data Marts que se encuentran separados físicamente de los subconjuntos de datos y pueden ser usados por multiples propósitos • Integrado vía ‘Modelo de Datos Empresariales’ • 3 capas (Data Warehouse, Data Mart, Cubos OLAP), Duplicación de Datos

25. Modelo Kimball Arquitectura DW Data Warehouse Áreas Empresariales Esquema Estrella Staging Procesamiento de Cubos Multi Dimensional Staging Area 1 OLTP Data Sources Capa de Visualización Data Mart 1 SSIS SSIS Data Atómica Staging Area 2 Data Mart 2 SSIS SSIS Multi Dimensional Staging Area 3 SSIS Vistas Dimensionadas Porque Staging: Limita la contención de origen de datos (ETLs), Recuperabilidad de datos, Copias de Seguridad, Auditoria entre otros

26. Modelo Inmon Staging Capa de Visualización Staging Area 1 OLTP Data Sources Corporate Information Factory (CIF) Vistas Dimensionalizadas SSIS Multi Dimensional Data Mart 1 (Normalizado) Data Warehouse (Normalizado) SSIS Staging Area 2 SSIS SSIS SSIS SSIS Tabular Data Mart 2 (Normalizado) Datos Atómicos Staging Area 3 SSIS SSIS Procesamiento de Cubos

27. Razones para establecer un EDW (Enterprise Data Warehouse) • Un sola versión de un dato • Se desarrollan dimensiones fácilmente utilizando el ecosistema de tablas desnormalizadas en el EDW en vez de ir directamente a los orígenes de datos OLTP • Un EDW normalizado da como resultado la consistencia absoluta de los datos del lado empresarial haciendo con esto, un fácil mantenimiento de los Data Mart a expensas de cohabitar con datos duplicados • Reduce ampliamente las actualizaciones vía ETLs, aun reconsiderando el tener muchos orígenes y Data Marts en multiples bases de datos • Desde un solo lugar se control y administran los datos (Esfuerzo y Duplicación de Datos complemente Nula) • Lo opuesto: Si existen pocos orígenes de datos que necesitan reportar fácilmente, no hay razón para establecerlo

28. Que modelo utilizar? • Los Modelos no son diferentes, ya que llegan a ser tan similar con el pasar de los años en un ecosistema que terminan complementándose mutuamente • Inmon reduce la creación de DW normalizados antes de crear un Data Mart dimensional, y Kimball pasa por alto la normalización de un DW • Se puede optimizar cada modelo, mostrándose cada uno similar al otro (Por supuesto, agregando un EDW normalizado bajo el modelo Kimball, estructurándolo dimensionadamente los Data Mart como Inmon) • A tener en cuenta: Comprendiendo ambos enfoques (Vital); y seleccionando partes de ambas para cubrir las necesidades que nuestro escenario se lograra exitosamente la decisión a tomar. (Pendiente: No se necesita establecer un solo enfoque) • PERO, no será una solución eficiente a menos que poseamos experticia en el manejo de estos escenarios tecnológicos (Por ej.: Liderazgo, Comunicación, Planeación, y relaciones Interpersonales)

29. Modelo Hibrido Advertencia: Utilizar las Vistas del Sistema SQL Server (SQL Server Views) como interfaces en cada nivel en el modelo Data Warehouse Áreas Empresariales Esquema Estrella Staging Procesamiento de Cubos Multi Dimensional Staging Area 1 OLTP Data Sources Capa de Visualización Mirror OLTP Corporate Information Factory (CIF) Data Mart 1 SSIS SSIS SSIS EDW Data Warehouse (Normalizado) Data Atómica Staging Area 2 Data Mart 2 SSIS SSIS SSIS Tabular Datos Atómicos Staging Area 3 SSIS SSIS Procesamiento de Cubos En la Arquitectura DW cada Data Mart seria un Esquema (Teniendo en cuenta que cada proceso empresarial sería un área a tratar), todo en una sola base de datos. Algunas empresas y compañías tienen una sola base de datos donde se encuentran cada Data Mart como modulo independiente distribuido en Data Files y FileGroups

30. Metodología Kimball Origen: Kimball’s The Microsoft Data Warehouse Toolkit Kimball define el ciclo de desarrollo de un Almacén de Datos, donde Inmon lo que solamente emite es acerca del Almacén de Datos (No “COMO” utilizarlo

31. Como poblar un Almacén de Datos • Determinar la frecuencia de Extracción de Datos (Diaria, Semanal, etc) • Full extracción – Toda la data (Usualmente aplicada a las tablas dimensiones) • Extracción Incremental – Únicamente la data que ha sido modificada o cambiada desde la última extracción (Tablas de Hechos)

32. Como poblar un Almacén de Datos • Como determinar que la data ha cambiado: • Timestamp – Ultima actualización • Change Data Capture (CDC) • Particionamiento por fecha • Desencadenadores (Triggers) en tablas de datos • Comando SQL ‘MERGE’ • Columnas de fechas con valores predeterminados ‘DEFAULT’

33. Como poblar un Almacén de Datos • Extracción en línea (On Line) – Datos desde los orígenes de datos creando la primera copia del origen: • Replicación • Snapshot de la base de datos • Availability Group

34. Migrando datos como ETL o ELT!!! • Extract, Transform, y Load (ETL en ingles) • Transformar cuando se traen datos desde los orígenes de datos • No existe área de Staging • El procesamiento es efectivo por las herramientas ETL (Por ej.: SSIS)

35. Migrando datos como ETL o ELT!!! • Extract, Load, y Transform (ELT en ingles) • Utiliza área de Staging • El procesamiento es efectivo cuando se ejecuta en el motor de base de datos (SSIS: Ejecutar comandos T-SQL en los componentes donde lo aplique desde las tareas de transformación de datos (DFT = Data Flow Transform Tasks) • Usado para grandes volúmenes de datos • Usado cuando tanto el origen así como el destino de bases de datos son el mismo • Usando en Parallel Data Warehouse (PDW)

36. Migrando datos como ETL o ELT!!! ELT vs. ETL ELT es mejor desde que se ejecute desde las bases de datos en el motor SQL Server, siendo mas eficiente en este contexto que en SSIS • Mejor utilizar el motor de base de datos para las transformaciones (Por ej.: CONVERT, CAST, etc) • Mejor utilizar SSIS para la Administración del Flujo de Información y Canalización de Flujo de Datos (Data Pipeline)

37. Claves Subrogadas. Nuestra salvación. Claves Subrogadas – Identificadores únicos no heredados desde los sistemas de origen • Embebidos en las tablas de Hechos como Claves Foráneas en tablas Dimensiones • Permite integridad de los datos procedentes de diferentes sistemas de orígenes de datos • Protege desde los cambios de las claves orígenes en los sistemas de orígenes de datos • Permite Slowly Changing Dimensions

38. Claves Subrogadas. Nuestra salvación. Claves Subrogadas – Identificadores únicos no heredados desde los sistemas de origen • Permite que se crean registros en las dimensiones que no existen en el origen (-1 en las tablas de hechos no asignados) • Mejora la ejecución (Joins) y optimiza el tamaño de las bases de datos utilizando tipos de datos numéricos en vez de caracteres • Implementa columnas de Identidad (IDENTITY) en tablas dimensiones

39. Bases de datos Multidimensionales en SSAS (Cubos OLAP) Razones para ser utilizada en un Almacén de Datos: • Agregaciones (Sumarizaciones) de datos para su ejecución • Análisis Multidimensional – Slice, Dice, Drilldown • Jerarquías • Cálculos de tiempo avanzados (Por ej.: Promedio balanceado de 12 meses de ventas) • Utilizado fácilmente desde MS Excel 2013 viendo y analizando los datos desde Pivot Tables • Slowly Changing Dimensions (SCD)

40. Arquitectura Data Warehouse 1.- Recopilación 2.- Limpieza | Estandarización 3.- Almacén de Datos 4.- Modelo | Presentación 5.- Análisis 6.- Compartir Microsoft Data Platform – Plataforma de Datos de Microsoft 3NF Data Warehouse SSAS SharePoint ODS CRM Tablas Staging Ventas Tabular SQL Server Reporting Services Sistemas Operacionales MDM Data Mart ERP PerformancePoint Services Esquema Estrella OLAP Finanzas SSIS SSIS SSIS Dimensional Excel Excel Services DQS Datos Externos Power View Power Pivot For SP EXCEL Mercadeo SSIS Transaccional Publicar PowerPivot for Excel

41. Modelo Tabular en SQL Server • In-Memory Database en SSAS • Desarrollar modelos en PowerPivot o SQL Server Data Tools (SSDT) • Utilizar modelos relacionales existentes • No esquema estrella. No generar ETL extras en SSIS

42. Modelo Tabular en SQL Server • Utilizar DAX • Es rápido y fácil de utilizar con modelos multidimensionales • En un Modelo Hibrido, una arquitectura para Bodega de Datos siempre será una vía apropiada para lograr los objetivos deseados en este contexto PERO solo sino se necesitan Claves Subrogadas, SCD, Dimensiones conformadas, y todos los reportes estarán fuera de los cubos

43. Herramientas BI clientes para usuarios finales. Cuales son y como se utilizan? Muchas son las opciones basadas con la Plataforma de Datos de Microsoft (Microsoft Data Platform) a utilizarse en un escenario DW/BI (Data Warehouse – Almacén de Datos / Inteligencia de Negocio): • Excel PivotTables (Cubos OLAP, NF) • SQL Server Reporting Services (SSRS) (Cubos OLAP, NF) • ReportBuilder (Cubos OLAP, NF) • PowerPivot (NF) • PerformancePoint Services (PPS) (Cubos OLAP) • Power View (Cubos OLAP) • Data Mining (Cubos OLAP)

44. DEMO“MS SQL Server 2014 Database Engine”Bodega de datos OLTP – OLAP Databases “MS SQL Server 2014 Integration Services”ETL Package MultiPlatform (Microsoft Windows – IBM AS-400 iSeries 5C – UNIX)“MS Excel 2013”Power BI

45. Q & A

46. Contact Me… • E-Mail: redondoj@gmail.com • Social Network: • Facebook: Cheo Redondo • Twitter: @redondoj • LinkedIn: https://www.linkedin.com/in/redondoj • Blog in Spanish: “El Blog de Cheo Redondo” - http://redondoj.wordpress.com

47. #SQLFamily Gracias por participar