Transcript Data Warehouse

Construyendo La Arquitectura de
una eficiente Bodega de Datos
Jose Redondo
Microsoft SQL Server MVP
E-Mail: [email protected]
Twitter: @redondoj
Blog: redondoj.wordpress.com | LinkedIn: https://www.linkedin.com/in/redondoj
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
Construyendo La Arquitectura de
una Eficiente Bodega de Datos
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
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?
Conceptos
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
Fuente: Wayne Eckerson
Madurez del Modelo de un Almacén de
Datos
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)
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)
Porque usar una Bodega de Datos?
Aplicaciones Heredadas + Bases de Datos = Caos Total
Control de
Producción
Ingeniería
CRM | MPR
Finanzas
Inventarios
Inventarios
Administración
de Partes
Logística
Ventas
•
•
•
•
•
Continuidad
Consolidación
Control
Estandarización
Colaboración
Administración
de Reportes
Logística
Contabilidad
Materia Prima
Mercadeo
Pedidos |
Facturación
Recursos
Humanos
Control de
Envíos
Bodega de Datos Empresarial = Sinónimo de Orden y Éxito
Ventas
UNA SOLA VERSIÓN DEL DATO
Bodega de Datos
Empresarial
Cada consulta = Una decisión
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
Soluciones de Hardware
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
Hardware
Ahorrador de
Energía
Software
• Almacenes de Datos (Data Warehouse)
Pre-Configurado
Pre-Optimizado
Alto Desempeño
Puesta en
Producción al
instante
• Inteligencia de Negocio (Business Intelligence)
Servicios
Listo para
ejecutarlo
• Soluciones de Consolidación de Bases de Datos
Aplicaciones en la vida diaria
• Algunas aplicaciones del mercado:
•
•
•
•
•
•
•
Dell Quickstart Data 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)
Data Warehouse vs. Data Mart.
Diferencias!!!
Data Warehouse
Data Mart
(Almacén de Datos | Bodega de Datos)
(Versión “Especial” de un Almacén de Datos)
• Único repositorio organizacional de datos
permitiendo enlazar muchas o todas las
áreas temáticas de una empresa
• Subconjunto de una Bodega de Datos que
usualmente es orientado en temas
empresariales específicos (Por ej.: Finanzas,
Márquetin, Ventas, etc)
•
•
•
•
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)
• La combinación lógica de todos los Data Mart
es lo que conforma un Data Warehouse
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
Que metodología es la mejor,
Kimball o Inmon?
Dos enfoques para construir y desarrollar Bodegas de Datos (Data
Warehouse)
Kimball & Inmon - Mitos
• Mito: Kimball es un “Criterio de
Ejecución”
Descendente–
Ascendente sin un enfoque
empresarial
• Realmente
es
AscendenteDescendente: 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)
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)
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?
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
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
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
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
Modelo Kimball
Arquitectura DW
Data Warehouse
Áreas Empresariales
Esquema Estrella
Staging
OLTP Data Sources
Staging
Area 1
Multi
Dimensional
SSIS
Data Mart 1
Data Atómica
SSIS
Staging
Area 2
SSIS
Data Mart 2
SSIS
Staging
Area 3
Vistas Dimensionadas
Multi
Dimensional
Porque Staging: Limita la contención de origen de datos (ETLs), Recuperabilidad de datos, Copias de Seguridad, Auditoria entre otros
Capa de Visualización
SSIS
Staging
Staging
Area 1
SSIS
Data Mart 1
(Normalizado)
SSIS
Staging
Area 2
SSIS
Data Warehouse
(Normalizado)
Datos Atómicos
SSIS
Staging
Area 3
Multi
Dimensional
SSIS
SSIS
SSIS
Tabular
Data Mart 2
(Normalizado)
Procesamiento de Cubos
OLTP Data Sources
SSIS
Corporate Information
Factory (CIF)
Capa de Visualización
Vistas Dimensionalizadas
Modelo Inmon
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
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)
Áreas Empresariales
Esquema Estrella
Mirror OLTP
SSIS
Staging
Area 1
SSIS
Corporate Information
Factory (CIF)
SSIS
Data Mart 1
Data Atómica
SSIS
Staging
Area 2
SSIS
EDW
Data Warehouse
(Normalizado)
Datos Atómicos
SSIS
Multi
Dimensional
Staging
Area 3
SSIS
Data Mart 2
Capa de Visualización
OLTP Data Sources
Staging
SSIS
Procesamiento de
Cubos
Tabular
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
Advertencia: Utilizar las Vistas del Sistema SQL Server (SQL Server Views) como interfaces en cada
nivel en el modelo
Data Warehouse
Procesamiento de
Cubos
Modelo Hibrido
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
Origen: Kimball’s The Microsoft Data Warehouse Toolkit
Metodología Kimball
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)
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’
•
•
•
•
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
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)
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)
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)
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
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
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)
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
CRM
Tablas
Staging
SSAS
ODS
Ventas
Sistemas
Operacionales
Data Mart
Tabular
MDM
SSIS Dimensional
SQL Server Reporting Services
PerformancePoint Services
SSIS
ERP
Datos Externos
SharePoint
SSIS
Finanzas
Esquema
Estrella
DQS
OLAP
Excel
Excel Services
Power View
EXCEL
Power
Pivot
For
SP
Mercadeo
SSIS Transaccional
Publicar
PowerPivot for Excel
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
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
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
(Cubos OLAP)
• Power View (Cubos OLAP)
• Data Mining (Cubos OLAP)
(PPS)
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
Q&A
Contact Me…
• E-Mail: [email protected]
• 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
#SQLFamily
Gracias por participar