Mejores Prácticas de DataWarehouse con SQL Server

(1)

Mejores Prácticas de

DataWarehouse con SQL Server

Casos de referencia

Ing. Eduardo Castro, PhD

(2)

Speaker Bio

2

PASS Board of Directors – LATAM Advisor PASS Regional Mentor for LATAM

Microsoft SQL Server MVP

Picture Here

edocastro ecastrom

eduardocastrom

(3)

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)

4

Datos Almacén

Extraer Transformar Cargar Refrescar

OLAP Engine

Análisis Pregunta Informes La minería de datos

Controlar Y Integrador Metadatos

Fuentes de datos Herramientas de

aplicaciones para usuario

Servir

Data Marts Operacional

DBs Otras fuentes

Almacenamiento de Datos

OLAP Server

Data Warehouse: Una arquitectura de varios niveles

(5)

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)

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)

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)

8

Variedad de datos

Archivos de Hadoop (almacenamiento no relacional)



(9)

9

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

(10)

10

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 Los datos no relacionales

(11)

11



Las fuentes de datos Los datos no relacionales

El almacén de datos moderna

(12)

12

Big Data + BI tradicional = Nuevo Enfoque de Análisis

grandes cantidades

de datos

Hadoop NoSQL

Tabular OLAP

SQL

010101010101010101 1010101010101010

01010101010101 101010101010

Visualización

Polibase

(13)

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)

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)

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)

16

Diseñar el bus del Datawarehouse

Determinar qué dimensiones serán compartidos a través de múltiples data marts

Conformar las dimensiones compartidas

 Producir una suite principal de dimensiones compartidas

Determinar qué hechos serán compartidos a través de mercados de datos

Conformar 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)

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)

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)

19

Best Practice # 6

Crear un plan de capacidad para su aplicación BI y monitorear cuidadosamente

Considere 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)

20

El aumento Requisitos de hardware

El uso de SSD

Tamaño de bloque ROLAP

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)

21

Uso de particionamiento para DW

Facts Database 1 Partition per Day

31 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 9 20 8 10 16

...

WHERE [date] = CAST(CONVERT(varchar, GETDATE(), 112) AS int) AND [hour] IN (0, 11, 17)

8 Evenly Distributed Partitions per Day 3120 Partitions, 13 Months of Data

8 Parallel Partition Processing Jobs

Current Day Partition Set Current Day Partition

Cube

(22)

22

Concurrencia mejorar desempeño multiusuario

Escalar Analysis Services: Sólo Lectura

Diseñar los cubos servicio de análisis escalables y complejas, Denny Lee, Thomas Kejser

(23)

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)

24

Cubo adCenter

PRÁCTICAS REALES: prestaciones de escalado de Microsoft SQL Server 2008 Analysis Services de AT MICROSOFT ADCENTER

(25)

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)

(26)

26

Carga de datos diaria

Cada trimestre una operación de ProcessUpdate se utiliza para actualizar dimensión datos

(27)

27

Consulta de datos

(28)

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

(29)

29

Actualización diaria de datos

(30)

30

Montaje diario por medio de clonar cubos

(31)

31

Centro de Producción adCenter

Storage Area Network OLAP Processing Server

Windows Server 2003 x64 SP2 SQL Server Enterprise Edition 32 GB RAM, 8 Xeon procs (16 cores)

Staging Data Warehouse

Windows Server 2003 x64 SP2 SQL Server Integration Services

Network Load Balancing

Data Feeds

HBA B HBA A

Windows Server 2003 x64 SP2 SQL Server Analysis Services 64GB RAM, 8 Xeon procs (16 cores)

OLAP Standby Server

SAN Fabric A

SAN Fabric B HBA B

HBA A HBA A HBA B

Host Bus Adapters: 400 MB/sec each HBA B

HBA A HBA A HBA B HBA A HBA B

adCenter Production Environment

Windows Server 2003 x64 SP2 SQL Server Analysis Services 64GB RAM, 8 Xeon procs (16 cores)

OLAP LUN Standby OLAP LUN

19200 Max Reads 9600 Max Writes

DW LUN

180 300GB 10K Drives RAID 1

(32)

ESTUDIO DE CASO: E & D

Xbox Live

(33)

33

Estrategia de Particiones

Uniformemente distribuida, continuo y no se solapan

(34)

Xbox Live - SSD Performance

Day Week Month Quarter 7 months

Dev SSD 14 29 101 203 506

Dev HDD 14 29 104 610 1191

UAT SAN 9 73 445 1025 2800

V2 Cube, SSD 5 10 15 31 72

V2 Cube, HDD 5 7 30 244 540

0 500 1000 1500 2000 2500 3000

Run Tme (seconds)

Amount of Data

DBI407 Mejores prácticas para la construcción 1 empresas soluciones de inteligencia empresarial de nivel con Microsoft SQL Server Analysis Services, Adam Jorgensen Diseñar los cubos servicio de análisis escalables y complejas, Denny Lee, Thomas Kejser

(35)

35

Concurrencia de consultas

Utilizar SSD para que cada servidor para manejar más consultas simultáneas

(36)

ESTUDIO DE CASO: YAHOO!

Cubo de 12 TB

(37)

Yahoo - Datos Masivos a gran escala

Oracle 10g

CDF 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/day

Archivo1 Archivo2

Filen

50 GB /hr

12 TB cubo

(38)

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

(39)

39

Particiones del cubo

Particiones tanto por el tiempo y región

Procesamiento completo se puede hacer en Particiones "activas"

(40)

40

Cube Flipping

Recall: Bloqueo nivel de servidor necesaria para realizar el proceso Solución alternativa:

 Dos copias del cubo, por turnos

 “Intercambiar"entre ellos

Dos maneras de mover

 Utilice ASLB de CodePlex

 Excel Plug-in

(41)

Diseñar los cubos servicio de análisis escalables y complejas, Denny Lee, Thomas Kejser 41

(42)

(43)

(44)

44

Resumiendo

Usted tiene que conseguir el diseño correcto si desea escalar El 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

(45)

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

46

Fuente: http://www.emc.com/collateral/technical-documentation/h13566-data-warehouse-fast-track-ms-sql-2014.pdf

(47)

SQL Server Fast Track Reference Architecture

47

Fuente: http://www.emc.com/collateral/technical-documentation/h13566-data-warehouse-fast-track-ms-sql-2014.pdf

(48)

SQL Server Fast Track Reference Architecture

48

Fuente: http://www.emc.com/collateral/technical- documentation/h13566-data-warehouse-fast-track- ms-sql-2014.pdf

(49)

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

(50)

Introducción al sistema Microsoft Analytics Platform

Un moderno dispositivo de almacenamiento de datos llave en mano

• 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

(51)

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 Server

Parallel Data warehouse

Microsoft HDInsight

Polibase

APS listo para la empresa Hadoop con HDInsight

Manejable, asegurado, y de alta disponibilidad Hadoop integrado dentro del aparato

(52)

Carga en paralelo de depósito de datos

HDInsight carga de trabajo

Fabric

Hardware

Aparato

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

(53)

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 Server Microsoft Azure PDW

HDInsight

Polybase

Microsoft HDInsight

Hortonworks para Windows y Linux Cloudera

Conexión de islas de datos con polybase

Trayendo soluciones de punto de Hadoop y el almacén de datos junto a los usuarios y TI

Conjunto de resultados Seleccionar

...

(54)

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 Services

APS

MapReduce T-SQL

Analytics / Ad-hoc / Visualización

Microsoft HDInsight SQL Server Parallel Data

Warehouse

Polibase

(55)

Herramientas de BI

Presentación de informes y cubos SMP SQL Server

Concurrencia de datos

Gran rendimiento con cargas de trabajo mixtas

Analytics Platform System

ETL / 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

(56)

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 Microsoft

PDW

HDInsight Polybase

(57)

PDW region

Hardware architecture

InfiniBand 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

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

(58)

SQL Data Warehouse

Data warehouse como servicio

Posee una arquitectura elástica con soporte a grandes cantidades de datos

(59)

Capacidad elástica

Soporte para grandes cargas de trabajo, ajustado para ciclo de procesamiento Se compra tiempo de procesamiento según las necesidades

(60)

Portal de administración

(61)

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 DB Service Tiers

(62)

Datos no estructurados a través de Polybase/T-SQL

Consulta T- SQL

servidor

SQL Hadoop

Cita:

************************

**********************

*********************

**********************

***********************

$ 658.39

Jim Gray

Nombre

11.13.58

Fecha de Nacimient

o Wash

ingto n

Estad o

Ann Smith 04.29.76 YO

(63)

Unidad de almacenamiento de datos (TCU)

Basta con comprar el rendimiento de las consultas que necesita, no sólo el hardware

Cuantificado 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 seg

Velocidad de lectura

xx Fila M / seg Cargando Tasa xx K fila / seg Tabla Copiar

Rate

xx Fila K / seg 100 DWU

(64)

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

(65)

Preguntas?

(66)

Thank You for Attending

Follow @pass24hop

Share your thoughts with hashtags

#pass24hop & #sqlpass