Mejores Prácticas de DataWarehouse con SQL ServerCasos de referencia
Ing. Eduardo Castro, PhD
2
Speaker Bio
PASS Board of Directors – LATAM AdvisorPASS Regional Mentor for LATAMMicrosoft SQL Server MVP
Picture Here
edocastro
ecastrom
eduardocastrom
3
Derechos de autor
Este presentación contiene información parcial de las siguientes fuentes
• Prácticas reales: la escala del rendimiento MICROSOFT SQL Server 2008 Analysis SERVICIOS EN MICROSOFT ADCENTER
• DBI407 Mejor Prácticas para la construcción 1 empresas soluciones de inteligencia empresarial de nivel con el análisis de Microsoft SQL Server Servicios, Adán Jorgensen
• El diseño escalable y complejo Cubos servicio de análisis, Denny Lee, Thomas Kejser
• http://msdn.microsoft.com/en-us/library/dd758814 (v = SQL.100).aspx
• http://technet.microsoft.com/en-us/library/cc966414.aspx
• Almacenamiento de datos moderno, Minería y Visualización: Core Conceptos por George M. Marakas
• Data Warehousing Diez Común Los errores de Jon C. Choe
4
DatosAlmacén
ExtraerTransformarCargarRefrescar
OLAP Engine
AnálisisPreguntaInformesLa minería de datos
ControlarY
IntegradorMetadatos
Fuentes de datos Herramientas de aplicaciones para usuario
Servir
Data Marts
Operacional DBs
Otrasfuentes
Almacenamiento de Datos
OLAP Server
Data Warehouse: Una arquitectura de varios niveles
5
Arquitecturas OLAP Server
OLAP relacional (ROLAP) Utilice relacional o relacional ampliada DBMS para almacenar y gestionar datos
de almacenes y OLAP media de consumo Incluya optimización de DBMS backend, la implementación de la lógica de
navegación agregación y herramientas y servicios adicionales Mayor escalabilidad
OLAP multidimensional (MOLAP) Escaso motor de almacenamiento multidimensional basada en arreglos Indexación rápida a los datos resumidos previamente calculados
OLAP híbrido (HOLAP) (Por ejemplo, Microsoft SQL Server) La flexibilidad, por ejemplo, el bajo nivel: relacional de alto nivel: array
6
Uso de almacenamiento de datos
Tres tipos de aplicaciones de almacenamiento de datos Tratamiento de la información
apoya la consulta, el análisis estadístico básico, y la presentación de informes con tablas de referencias cruzadas, tablas, cuadros y gráficos
Procesamiento analítico análisis multidimensional de datos de almacenamiento de datos apoya las operaciones básicas de OLAP, rebanada-dados, perforación, pivotantes
La minería de datos descubrimiento de conocimiento a partir de patrones ocultos apoya las asociaciones, la construcción de modelos analíticos, realizar la clasificación y
predicción, y la presentación de los resultados de minería de datos utilizando herramientas de visualización
7
DW Arquitectura Áreas Componente Clave
Arquitectura de datos - cada área en un negocio se basa en diferentes dimensiones. Donde se cruzan es necesario definir el mismo (el cliente que compra es el mismo proyecto).
Arquitectura Infraestructura - cuestiones de tamaño, la escalabilidad y la capacidad deben ser diseñados y dimensionados.
Arquitectura técnica - Este es impulsado por el catálogo de metadatos. Los servicios deben elaborar los parámetros de las tablas.
http://courseware.finntrack.eu/it/data/marakas_dw_ch6.ppt
8
Variedad de datosArchivos de Hadoop (almacenamiento no relacional)
Los archivos podrían tener una variedad de datos semi-estructurado o no estructurado Anteriormente, estos archivos no se han visto como proporcionar valor o ideas Hoy en día, las nuevas preguntas de negocios y puntos de vista se está descubriendo a través de la ciencia de
datos
SentimentEntender cómo sus clientessentir acerca de su marca y productos-ahora
ClicsCapturar y analizarvisitantes del sitio web ' uso de datos para optimizar su sitio web
SensoresDescubra patrones en transmisión de datos de forma automática desde los sensores y máquinas remotas
GeográficoAnalizar los datos basados en la localización para gestionar las operaciones en que se producen
Los registros del servidorRegistros de investigación para diagnosticar fallas en el proceso y prevenir violaciones de la seguridad
No EstructuradosComprender los patrones de archivos a través de millones de páginas web, correos electrónicos, mensajes y documentos
9
ETL
Almacenamiento de datos
BI y análisis
Volumenes de datos creciente
1
Datos en tiempo real
2
Nuevo datos fuentes y tipos
3
El almacén de datos tradicional
Las fuentes de datos
OLTP ERP CRM LOB
10
ETL
Almacenamiento de datos
BI y análisis
Volumenes de datos creciente
1
Datos en tiempo real
2
Nuevo datos fuentes y tipos
3
Inclusión de datos no tradicionales
Las fuentes de datos
OLTP ERP CRM LOB
Los datos no relacionales
Dispositivos Web Sensores Social
11
INFRAESTRUCTURA
GESTIÓN DE DATOS Y PROCESAMIENTO
ENRIQUECIMIENTO DE DATOS Y CONSULTA FEDERADOS
BI y análisis
Autoservicio ColaboraciónCorporativo ProféticoMóvil
Extraer, transformar, cargar
Modelo de consulta individual
Calidad de la informaciónGestión de datos maestros
No relacionalRelacional Analítico Streaming Interna y externa
OLTP ERP
Las fuentes de datos
CRM LOB
Los datos no relacionales
Dispositivos Web Sensores Social
El almacén de datos moderna
12
Big Data + BI tradicional = Nuevo Enfoque de Análisis
grandes cantidades de datos
HadoopNoSQL
TabularOLAPSQL
0101010101010101011010101010101010
01010101010101101010101010
Visualización
Polibase
13
Best Practice # 1
Usar un modelo de datos que se ha optimizado para la recuperación de la información
Modelo tridimensional Sin normalizar Enfoque híbrido
14
Best Practice # 2
Diseñar cuidadosamente la adquisición de datos y procesos de limpieza para su DW
Asegurar que los datos se procesan de manera eficiente y precisa Considere la adquisición de ETL y herramientas de limpieza de datos Úsalos bien!
15
Best Practice # 3
Diseñar una arquitectura de metadatos que permite el intercambio de metadatos entre los componentes de su DW
Considerar los estándares de metadatos como Metamodelo Cómun de Datos de OMG (CWM)
16
Diseñar el bus del Datawarehouse
Determinar qué dimensiones serán compartidos a través de múltiples data martsConformar las dimensiones compartidas
Producir una suite principal de dimensiones compartidasDeterminar qué hechos serán compartidos a través de mercados de datosConformar los hechos
Estandarizar las definiciones de los hechos
Más información en http://www.slideshare.net/ecastrom/arquitecura-de-bodega-de-datos del 2013
17
Best Practice # 4
Adoptar un enfoque que consolida los datos en "una sola versión de la verdad"
Data Warehouse Bus de Kimball Dimensiones y Hechos
Más información en http://www.slideshare.net/ecastrom/arquitecura-de-bodega-de-datos del 2013
18
Best Practice # 5
Considere la posibilidad de la aplicación de un ODS sólo cuando los requisitos de recuperación de información están cerca de la parte inferior de la pirámide de la abstracción de datos y / o cuando hay múltiples fuentes operativas que necesitan ser consideradas
Debe asegurarse que el modelo de datos está integrado, no sólo consolidada
Se puede considerar modelo de datos 3NF Evite a toda costa un “volcado de datos”
19
Best Practice # 6
Crear un plan de capacidad para su aplicación BI y monitorear cuidadosamenteConsidere la posibilidad de futuras demandas adicionales de rendimiento
Establecer consultas de referencia de rendimiento estándar y ejecutar regularmente tareas de comparación de rendimiento
Implementar herramientas de control de capacidad Construir escalabilidad en su arquitectura Puede ser necesario para permitir escalar hacia arriba y hacia
fuera!
20
El aumento Requisitos de hardware
El uso de SSDTamaño de bloqueROLAP
DBI407 Mejores prácticas para la construcción 1 empresas soluciones de inteligencia empresarial de nivel con Microsoft SQL Server Analysis Services, Adam Jorgensen
21
Uso de particionamiento para DW
Facts Database
1 Partition per Day31 Partitions, 1 Month of Data
ALTER PARTITION FUNCTION PerDay () SPLIT RANGE(CAST(CONVERT(varchar, GETDATE()+1, 112) AS int))
1 2 3 4 5 6 7 8 9 10 11
12 13 14 15 16 17 18 19 20 21 22 23
0
0 11 17 1 14 18 2 5 22 3 12 21
4 13 19 6 15 23 7 209 8 10 16
...WHERE [date] = CAST(CONVERT(varchar, GETDATE(), 112) AS int) AND [hour] IN (0, 11, 17)
8 Evenly Distributed Partitions per Day3120 Partitions, 13 Months of Data
8 Parallel Partition Processing Jobs
Current Day Partition Set
Current Day Partition
Cube
DBI407 Mejores prácticas para la construcción 1 empresas soluciones de inteligencia empresarial de nivel con Microsoft SQL Server Analysis Services, Adam Jorgensen
22
Concurrencia mejorar desempeño multiusuarioEscalar Analysis Services: Sólo Lectura
Diseñar los cubos servicio de análisis escalables y complejas, Denny Lee, Thomas Kejser
23
Estudio de caso - AdCenter
EMC DMX V-Max para manejar la E / S V-Max son dedicados a la aplicación Cientos de discos y ejes dedicados a este proceso Discos para asegurarse rápido de E / S Trabaja en estrecha colaboración con EMC directamente (presente en el EMC
World regularmente) Pruebas con EMC EFDs (Enterprise Flash Drives)
Equipo de Ingeniería de Sistemas dedicado al proceso de DW Trabajar en estrecha colaboración con varios proveedores (EMC, HDS, etc)
Referencias Acelerar Microsoft adCenter con Microsoft SQL Server 2008 Analysis Services. PRÁCTICAS REALES: prestaciones de escalado de Microsoft adCenter con Microsoft SQL Server 2008
Analysis Services de EMC VMAX
24
Cubo adCenter
PRÁCTICAS REALES: prestaciones de escalado de Microsoft SQL Server 2008 Analysis Services de AT MICROSOFT ADCENTER
25
EMC Symmetrix VMAX
Cada servidor está conectado a una EMC Symmetrix VMAX a través de bus con doble adaptadores
El servidor utiliza un volumen de 3 TB organizado en 80 400 GB 10000 rpm Fibra Discos de canal en una configuración duplicada y rayas (RAID 1 + 0).
Cada 24 horas el volumen replica los cambios en un volumen de informes 3 TB hecho por nueve EFDs 400 GB configurado en una configuración de paridad distribuida (RAID 5)
PRÁCTICAS REALES: prestaciones de escalado de Microsoft SQL Server 2008 Analysis Services de AT MICROSOFT ADCENTER
26
Carga de datos diaria
Cada trimestre una operación de ProcessUpdate se utiliza para actualizar dimensión datos
PRÁCTICAS REALES: prestaciones de escalado de Microsoft SQL Server 2008 Analysis Services de AT MICROSOFT ADCENTER
27
Consulta de datos
PRÁCTICAS REALES: prestaciones de escalado de Microsoft SQL Server 2008 Analysis Services de AT MICROSOFT ADCENTER
28
Administración
La actualización de datos del cubo multidimensional del servidor de procesamiento soporta las tareas de carga de datos (carga de datos de los datos relacionales) y el procesamiento del cubo
PRÁCTICAS REALES: prestaciones de escalado de Microsoft SQL Server 2008 Analysis Services de AT MICROSOFT ADCENTER
29
Actualización diaria de datos
PRÁCTICAS REALES: prestaciones de escalado de Microsoft SQL Server 2008 Analysis Services de AT MICROSOFT ADCENTER
30
Montaje diario por medio de clonar cubos
PRÁCTICAS REALES: prestaciones de escalado de Microsoft SQL Server 2008 Analysis Services de AT MICROSOFT ADCENTER
31
Centro de Producción adCenter
Storage Area Network
OLAP Processing Server
Windows Server 2003 x64 SP2SQL Server Enterprise Edition
32 GB RAM, 8 Xeon procs (16 cores)
Staging Data Warehouse
Windows Server 2003 x64 SP2SQL Server Integration Services
Network Load Balancing
Data Feeds
HBA BHBA A
Windows Server 2003 x64 SP2SQL Server Analysis Services
64GB RAM, 8 Xeon procs (16 cores)
OLAP Standby Server
SAN Fabric A
SAN Fabric B
HBA BHBA A HBA BHBA A
Host Bus Adapters: 400 MB/sec each
HBA BHBA A HBA BHBA A HBA BHBA A
adCenter Production Environment
Windows Server 2003 x64 SP2SQL Server Analysis Services
64GB RAM, 8 Xeon procs (16 cores)
OLAP LUNStandby OLAP LUN
19200 Max Reads9600 Max Writes
DW LUN
180 300GB 10K Drives
RAID 119200 Max Reads9600 Max Writes
180 300GB 10K Drives
RAID 12560 Max Reads2560 Max Writes
32 300GB 10K Drives
RAID 1
DBI407 Mejores prácticas para la construcción 1 empresas soluciones de inteligencia empresarial de nivel con Microsoft SQL Server Analysis Services, Adam Jorgensen
ESTUDIO DE CASO: E & DXbox Live
33
Estrategia de ParticionesUniformemente distribuida, continuo y no se solapan
Diseñar los cubos servicio de análisis escalables y complejas, Denny Lee, Thomas Kejser
Xbox Live - SSD Performance
Day Week Month Quarter 7 months0
500
1000
1500
2000
2500
3000
Amount of Data
Run
Tme
(sec
onds
)
DBI407 Mejores prácticas para la construcción 1 empresas soluciones de inteligencia empresarial de nivel con Microsoft SQL Server Analysis Services, Adam JorgensenDiseñar los cubos servicio de análisis escalables y complejas, Denny Lee, Thomas Kejser
35
Concurrencia de consultas
Utilizar SSD para que cada servidor para manejar más consultas simultáneas
Diseñar los cubos servicio de análisis escalables y complejas, Denny Lee, Thomas Kejser
ESTUDIO DE CASO: YAHOO!Cubo de 12 TB
Yahoo - Datos Masivos a gran escalaOracle 10gCDF SSAS Cube Constructor
NAS
Servidores de consultas SSAS
HW NLB
Partición 1
Partición 2
Partición N
Partición 1
Partición 2
Partición N
1.2TB/dayArchivo1
Archivo2
Filen
50 GB /hr
12 TB cubo
DBI407 Mejores prácticas para la construcción 1 empresas soluciones de inteligencia empresarial de nivel con Microsoft SQL Server Analysis Services, Adam Jorgensen
38
MOLAP conmutación En Acción
Idea básica: Utilizar MOLAP para los datos históricos Procesar últimas particiones MOLAP más a menudo Latencias típicas en minutos
Preocupaciones: Tiempo de procesamiento de las particiones actualizadas Manejo el bloqueo del proceso cuando necesite actualizar los datos
Diseñar los cubos servicio de análisis escalables y complejas, Denny Lee, Thomas Kejser
39
Particiones del cubo
Particiones tanto por el tiempo y regiónProcesamiento completo se puede hacer en Particiones "activas"
Diseñar los cubos servicio de análisis escalables y complejas, Denny Lee, Thomas Kejser
40
Cube Flipping
Recall: Bloqueo nivel de servidor necesaria para realizar el procesoSolución alternativa:
Dos copias del cubo, por turnos “Intercambiar"entre ellos
Dos maneras de mover Utilice ASLB de CodePlex Excel Plug-in
41Diseñar los cubos servicio de análisis escalables y complejas, Denny Lee, Thomas Kejser
42Diseñar los cubos servicio de análisis escalables y complejas, Denny Lee, Thomas Kejser
43Diseñar los cubos servicio de análisis escalables y complejas, Denny Lee, Thomas Kejser
44
Resumiendo
Usted tiene que conseguir el diseño correcto si desea escalarEl particionamiento es absolutamente fundamental
• Partición de velocidad de procesamiento• Partición de latencia de los datos (en tiempo real frente a
históricos)• Partición de archivos de datos antiguos
Hardware realmente importa para grandes cubos• Dispositivos SSD. • Las pruebas muestran dos CPU core con frecuencia puede
soportar cientos de usuarios• Con cuidado equilibrio IOPS frente memoria, considere parte más
utilizada del cubo
DBI407 Mejores prácticas para la construcción 1 empresas soluciones de inteligencia empresarial de nivel con Microsoft SQL Server Analysis Services, Adam Jorgensen
45
DW Appliance
Aparatos DW, que consisten en paquetes de soluciones que proporcionan todo el software y hardware necesario, están empezando a ofrecer el precio / rendimiento muy prometedor
46
SQL Server Fast Track Reference Architecture
Fuente: http://www.emc.com/collateral/technical-documentation/h13566-data-warehouse-fast-track-ms-sql-2014.pdf
47
SQL Server Fast Track Reference Architecture
Fuente: http://www.emc.com/collateral/technical-documentation/h13566-data-warehouse-fast-track-ms-sql-2014.pdf
48
SQL Server Fast Track Reference Architecture
Fuente: http://www.emc.com/collateral/technical-documentation/h13566-data-warehouse-fast-track-ms-sql-2014.pdf
49
Mantener la inversión legado
Comprar nuevo nivel uno dispositivo de hardware
Adquirir Big Data solución
Adquirir la inteligencia de negocios
Escalabilidad limitada y
capacidad de gestionar nuevos
tipos de datos
Entrenamiento Alta adquisición y
costos de migración
Complejidad y adopción
Obstáculos para un almacén de datos moderna
Introducción al sistema Microsoft Analytics PlatformUn moderno dispositivo de almacenamiento de datos llave en mano
La próxima generación rendimiento
Big Data lista para la empresa
Diseñado paravalor óptimo
• De datos relacionales y no relacionales en un único dispositivo
• Hadoop lista para la empresa
• Consultas integradas a través de Hadoop y PDW utilizando T-SQL
• La integración directa con las herramientas de BI de Microsoft, como Microsoft Excel
• Cerca de rendimiento en tiempo real con In-Memory Columnstore
• Capacidad de escalabilidad para incluir cada vez mayor de datos
• La eliminación del almacén de datos cuellos de botella con MPP SQL Server
• Concurrencia que ayuda rápida adopción
• Precio de appliance de datos más bajo por terabyte
• Valor a través de una única solución
• Valor con opciones de hardware flexibles utilizando hardware comercial
Alto rendimiento y sintonizado en el hardware
Autenticación del usuario final con Active Directory
Accesible ideas para todo el mundo con las herramientas de Microsoft BI
Administrado y monitoreado utilizando System Center
100-por ciento de Apache Hadoop
SQL ServerParallel Datawarehouse
Microsoft HDInsight
Polibase
APS listo para la empresa Hadoop con HDInsightManejable, asegurado, y de alta disponibilidad Hadoop integrado dentro del aparato
Carga en paralelo de depósito de datos
HDInsight carga de trabajo
Fabric
Hardware
Apar
ato
Una región es un contenedor lógico dentro de un appliance
Cada carga de trabajo contiene los siguientes límites:
• Seguridad • Medida • Prestación de servicios
Resumen hardware APS
Proporciona un único Modelo de consulta T-SQL para PDW y Hadoop con ricas características de T-SQL, incluyendo joins sin ETL
Utiliza el poder del MPP para mejorar el rendimiento de ejecución de consultas
Compatible con Windows Azure HDInsight para permitir escenarios híbrido de la nube
Ofrece la posibilidad de consultar las distribuciones no Microsoft Hadoop, como Hortonworks y Cloudera
SQL ServerPDWMicrosoft Azure
HDInsight
Polybase
Microsoft HDInsight
Hortonworks para Windows y Linux
Cloudera
Conexión de islas de datos con polybaseTrayendo soluciones de punto de Hadoop y el almacén de datos junto a los usuarios y TI Conjunto
de resultados
Seleccionar ...
Automatic MapReduce pushdown
Hadoop / Data Lake(Cloudera, Hortonworks,
HDInsight)
Fuente sistemas
Actualizar Día / Hora / Minuto
SQL Server Data Marts
SQL Server Reporting Services
SQL Server Analysis ServicesAPS
MapReduce T-SQL
Analytics / Ad-hoc / Visualización
MicrosoftHDInsight
SQL Server Parallel Data Warehouse
Polibase
Herramientas de BI
Presentación de informes y cubos
SMP SQL Server
Concurrencia de datos Gran rendimiento con cargas de trabajo mixtas
Analytics Platform SystemETL / ELT con SSIS, DQS, MDS
ERP CRM LOB APPS
ETL / ELT con DWLoader
Hadoop / Big Data
PDW
HDInsight
Polibase
Consultas ad hoc
Intra-Day
Casi en tiempo real
Fast ad hoc
Almacén de columnas
Polibase
CRTAS
Linked Table
Real-Time
ROLAP / MOLAP DirectQuery
SNAC
Hardware y software de ingeniería junto
Co-dirigido con HP, Dell, Quanta y mejores prácticas
Liderando el rendimiento con hardware comercial
Pre-configurado, construido, y ajustado software y hardware
Integrado apoyo con un solo contacto MicrosoftPDW
HDInsight
Polybase
PDW region
Hardware architectureInfiniBand
InfiniBand
Ethernet
Ethernet
Control node
Failover node
Master node
Failover node
Economical disk storage
Compute nodes
Economical disk storage
Compute nodes
Economical disk storage
Compute nodes
Networking
PDW region
HDInsight region
Rack #1
InfiniBand
InfiniBand
Ethernet
Ethernet
Failover node
Economical disk storage
Compute nodes
Economical disk storage
Compute nodes
Economical disk storage
Compute nodes
HDI extension base unit
HDI active scale unit
HDI extension base unit
HDI active scale unit
Rack #2
HST-02
HST-01
HSA-01
HST-02
Economical disk storage
IB and Ethernet
Active Unit Dos nodos adicionales
Passive Unit HDInsight
Failover Node Alta Disponibilidad
SQL Data WarehouseData warehouse como servicio
Posee una arquitectura elástica con soporte a grandes cantidades de datos
Elasticidad y desempeño
Procesamiento en la nube
Ajuste de desempeño
Capacidad elásticaSoporte para grandes cargas de trabajo, ajustado para ciclo de
procesamientoSe compra tiempo de procesamiento según las necesidades
Elasticidad y desempeño
Procesamiento en la nube
Ajuste de desempeño
Portal de administración
SQL DW: Basado en SQL DB
Elastic, Petabyte Scale DW Optimized
99.99% uptime SLA, Geo-restore
Azure Compliance (ISO, HIPAA, EU, etc.)
True SQL Server Experience;Se utilizan las herramientas existentes
SQL DW
SQL DBService Tiers
Datos no estructurados a través de Polybase/T-SQL
Consulta T-SQL
CapacidadT-SQL para realizar consultas relacionales y los datos no relacionales a través SQL Server y Hadoop
Beneficios Soporte al negocio a través
el lago de datos Aproveche de habilidades existentes
y herramientas de BI Procesos ETL simplificada
servidor SQL
Hadoop
Cita:****************************************************************************************************************
$ 658.39
Jim Gray
Nombre11.13.58
Fecha de Nacimiento Wash
ington
Estado
Ann Smith 04.29.76 YO
Unidad de almacenamiento de datos (TCU)Basta con comprar el rendimiento de las consultas que necesita, no sólo el hardwareCuantificado por objetivos de carga de trabajo: cómo se escanean filas rápidas, cargado, copian
Medida de Potencia
Transparencia
Servicio de primera DW para ofrecer potencia de computación bajo demanda, independiente de almacenamiento
Bajo demanda
Scan 1B filas
100 DWU = 297 seg
400 DWU = 74 seg
800 DWU = 37 seg
1600 DWU = 19 segVelocidad de lectura
xx Fila M / seg
Cargando Tasa xx K fila / seg
Tabla Copiar Rate
xx Fila K / seg
100 DWU
Almacén de datos SQL Azure
Integrado con Power BI, Azure Machine Learning, y Azure Data Factory
Almacenamiento por separado y de cómputo
Capacidad elástica
Escala de salida relacional almacén de datos
MPP masivo scale-out
Preguntas?
Thank You for AttendingFollow @pass24hop
Share your thoughts with hashtags #pass24hop & #sqlpass