1
Download José Mayorga Vindas, Mag.
Document related concepts
no text concepts found
Transcript
José Mayorga Vindas, Mag. Muldimenional Data Base Design by means of BISM Tabular Models SQLMelody@gmail.com http://sqlmelody.blogspot.ca/ @SQLMelody ca.linkedin.com/in/melodyzacharias José Mayorga Vindas, Mag. • Master Computación ITCR / Economista UCR • Microsoft Certified Professional • Consultor con más de 15 años de experiencia implementación de sistemas de Data Warehouse / Business Intelligence en Latinoamérica (Microsoft, Softtek, McKinsey and Company, …) • Jefe de desarrollo y Gerente de Sistemas en Instituciones Financieras • Profesor de Postgrado en Data Warehouse / Business Intelligence Universidad Cenfotec • Actualmente es Data Specialist de Periscope - McKinsey and Company José Mayorga V., Mag., 2015 Sponsors Diamond Silver Bronze Raffle José Mayorga V., Mag., 2015 Modelo de Base de Datos Multidimensional Esquemas de Bases de Datos de Estrellas José Mayorga V., Mag., 2015 Tablas en Estrellas Tablas de Hechos - mediciones Cantidad unidades vendidas Tablas de Dimensiones - categorías Monto de las ventas El Tiempo Costo de las unidades Catálogos de Productos Saldos de Cuentas bancarias Catálogo de Clientes Promedios ponderados Listas Empleados Datos Geográficos José Mayorga V., Mag., 2015 Modelo de Base de Datos Multidimensional Tablas de Dimensión José Mayorga V., Mag., 2015 Tablas de Dimensiones 3 tipos de campos: • llave primaria • niveles de jerarquías • campos de atributos Recomendación: • Las llaves primarias deben ser enteros, no se recomienda utilizar llaves del OLTP • 1 nivel de 1 jerarquía equivale a 1 campo de la tabla • Datos almacenados en los campos son miembros Una tabla puede tener mas de una dimensión Los atributos son datos adicionales José Mayorga V., Mag., 2015 Tipos de Dimensiones Fechas • Año, Trimestre, Mes, Día Des-normalizadas vs. Snowflakes • Varias tablas relacionadas entre si • Semi-normalizadas Recursivas (parent-child) José Mayorga V., Mag., 2015 Dimensiones de Tiempo – 2 Jeraquías 2 Jerarquías Fecha de Entrega … José Mayorga V., Mag., 2015 Año • Semestre • Trimestre • Mes • Día • Semana • Día Fecha Corte 2 Fecha de Compra/Venta Fecha Corte 1 Fecha Corte (fecha de la foto) Año Modelo de Base de Datos Multidimensional Tablas de Hechos José Mayorga V., Mag., 2015 Tabla de hechos con tres dimensiones de tiempo TD_Cliente PK TH_Ventas PK_Cliente NumeroIdentificacion Nombre Apellido1 Apellido2 TipoCliente TD_Fechas PK PK_Fecha Fecha Año Mes José Mayorga V., Mag., 2015 PK,FK2 PK,FK3 PK,FK1 PK,FK4 PK,FK5 PK PK PK PK_Cliente PK_FechaCorte PK_FechaVenta PK_FechaEntrega PK_Distrito PK_TipoProducto PK_Producto PK_FormaPago MontoVenta CantidadUnidades CostoVenta ImpuestoVenta Descuento TD_Geografica PK PK_Distrito PK_Canton PK_Provincia Distrito Canton Provincia Snow Flake vs Esquema Desnormalizado 33 + Provincia Cantón Distrito José Mayorga V., Mag., 2015 125 Provincia Cantón Tabla de Hechos de Ventas Distrito Snow Flake no preserva historia La Dimensión desnormalidaza preserva historia + 75 Snow-Flake = 233 regs x 33 regs x 125 regs x 75 regs = 309.375 regs Desnormalizado Estrella con Dimensión Snow-Flake TD_Cliente PK TD_Distrito PK_Cliente TH_Ventas NumeroIdentificacion Nombre Apellido1 Apellido2 TipoCliente TD_Fechas PK PK_Fecha Fecha Año Mes PK,FK1 PK,FK2 PK,FK3 PK PK PK PK_Fecha PK_Cliente PK_Distrito PK_Producto PK_TipoProducto PK_FormaPago MontoVenta CantidadUnidades CostoVenta ImpuestoVenta Descuento PK PK_Distrito FK1 Distrito PK_Canton TD_Canton PK PK_Canton FK1 Canton PK_Provincia TD_Provincia PK PK_Provincia Provincia José Mayorga V., Mag., 2015 Tabla de hechos con tres dimensiones de tiempo TD_Cliente PK TH_Ventas PK_Cliente NumeroIdentificacion Nombre Apellido1 Apellido2 TipoCliente TD_Fechas PK PK_Fecha Fecha Año Mes José Mayorga V., Mag., 2015 PK,FK2 PK,FK3 PK,FK1 PK,FK4 PK,FK5 PK PK PK PK_Cliente PK_FechaCorte PK_FechaVenta PK_FechaEntrega PK_Distrito PK_TipoProducto PK_Producto PK_FormaPago MontoVenta CantidadUnidades CostoVenta ImpuestoVenta Descuento TD_Geografica PK PK_Distrito PK_Canton PK_Provincia Distrito Canton Provincia Modelo de Base de Datos Multidimensional Aditividad y granularidad José Mayorga V., Mag., 2015 José Mayorga V., Mag., 2015 Tablas de Hechos - Aditividad Capacidad para resumir mediciones por medio de agregaciones (Sum, Count, Min, Max, …) Aditivas: permiten cualquier agregación Semi-aditivas: son limitadas No Aditivas: no se pueden agregar Cantidad de unidades Saldos de Cuentas Bancarias Porcentajes Monto de retiros de cuentas Acumulación de cuentas contables Razones Cantidad de retiros José Mayorga V., Mag., 2015 Tasas de crecimiento Tablas de Hechos - Granularidad • Nivel de Granularidad Nivel de detalle mínimo de los hechos Todas las mediciones deben estar al mismo nivel de granularidad Definido por el nivel mínimo de detalles de las dimensiones en su conjunto, o sea, los mínimos niveles de las jerarquías José Mayorga V., Mag., 2015 • Ej.: Granularidad de la Tabla de Hechos de Ventas: Ventas por: • • • • Día Vendedor Producto Cliente No existen las ventas por hora No es posible desagregar los datos más allá de la granularidad ´”mínima” The BISM Tabular Model Modelos de Bases de Datos Tabulares, Analysis Services 2014 José Mayorga V., Mag., 2015 El Modelo BISM y el motor VertiPacq BISM: Business Intelligence Semantic Model Paradigma de Bases de Datos Analíticas Tabulares X-Velocity In-Memory Analytic engine = VertiPaq Engine Este es un caso de una Base de Datos Columnar • Columnas en vez de tablas • Altos niveles de indexación • Algoritmos de compresión avanzados José Mayorga V., Mag., 2015 Arquitectura del Modelo BISM • Para Tabulares, SSAS reutiliza casi toda la infraestructura requerida para los modelos Multidimensionales. Los motores de formulas y queries se comparten, solo se require otro motor de base de datos. • Para el cliente es transparente si las conexiones son a un Multidimencional o a un Tabular • Los comandos desde las interfases (XMLA Listener, HTTP pump, llamadas API con ADOMD.NET, etc.) funcionan en términos de un modelo tradicional Multidimensional (UDM), el servidor ejecutará esos commandos vs. el motor de base de dats utilizado. Compresión Columnas José Mayorga V., Mag., 2015 Modelo Columnar • Dictionaries: definición de los distintos valores de las columnas, utiliza internamente un entero para cada valor distinto • Column segments: incluye un índice que identifica los valores por línea • Segments en las particiones. Cada partición tiene 1+ segmentos de columna. Por defecto cada segmento contiene 8 millones de registros • Columnas calculadas se materializan al procesar, se almacenan físicamente. • Jeraquías para cada columna y para cada Jerarquía multi-nivel. • Relationships representan llaves externas Tabular vs Multidimensional Un catalogo (database) = un modelo. Multidimensionales tienen múltiples cubos. Tabulares tiene un solo modelo, pero puede tener varias perspectivas. Tecnología In-memory : El motor de almacenamiento del Tabular utliliza la data en memoria, ésta persiste en disco, y se carga en memoria al reiniciar el servicio. MOLAP almacena grandes volumenes de datos en disco, está diseñado para pre-agregar y extraer esta data de manera eficiente. No hay agregaciones, el Tabular se basa en almacenamiento columnar, el Multidimensional pre-agrega mediciones Ambos modelos son descritos por medio de metadata en XML para Anaysis Sercives (XMLA), el lenguaje de metadata es compartido por ambos modelos Performance MOLAP vs Tabular Server Scenario Approximate Query Performance Multidimensional If query hits an aggregation ~ seconds Multidimensional If query misses aggregations but the fact data is cached in memory in the file system cache ~ minute Multidimensional If query misses aggregations and no fact data is cached in ~ minutes memory in the file system cache Tabular ~ milliseconds Commodity laptop hardware can service VertiPaq scans at 5 billion rows per second or more and can store in memory billions of rows. Commodity server hardware tests have shown VertiPaq scan rates of 20 billion rows per second or more, with the ability to store tens of billions of rows in memory. Procesamiento de Consultas en Tabulares Conexiones del Tabular Conexión a la base de datos de final Conexión a la fuente de datos Conexión al Workspace José Mayorga V., Mag., 2015 Agregando nuevas tablas al modelo tabular José Mayorga V., Mag., 2015 Relaciones principales y secundarias José Mayorga V., Mag., 2015 Campos calculados por medio de DAX José Mayorga V., Mag., 2015 Medición programada por medio de DAX Deployment del modelo tabular José Mayorga V., Mag., 2015 Aspectos a considerar en los Modelos Tabulares Tecnología relativamente nueva (p. Microsoft). Por lo tanto Best Practices apenas están creándose Relativamente simples y rápidas de construir e implementar (hacer deployment) En general tienen ventajas de rendimiento sobre BD Multidimensionales y Relacionales. Hay que tener cuidado con volúmenes de datos crecientes, abusos metodológicos e infraestrctura no óptima Se pueden exportar a Power BI (Power Pivot) DAX no es “nativo en SSRS”, pero se puede utilizar MDX José Mayorga V., Mag., 2015 Ventajas y desventajas Ventajas • No require mayor manipulación para tener buen rendimiento. Buen rendimiento sin tunning avanzado • Sistema optimizado para buen rendimiento de lectura, no de escritura • Experiencia de modelado rápida e iterativa. Se pueden hacer cambios en “caliente” si re-procesar Desventajas • Menos opciones de personalización que MOLAP • Cargar nuevos datos en el modelo puede ser lento, sobre todo si la base de datos es grande • DAX no es “connatural” para SSRS • Hay pocas herramientas para edición y pruebas con DAX José Mayorga V., Mag., 2015 Referencias • SQL Server 2012 Analysis Services – The BISM Tabular Model. Marco Russo, Alberto Ferrari, Chris Webb. Microsoft Press, 2012 • DAX Paterns. Marco Russo, Alberto Ferrari. SQLBI, 2015 • SQL Server Technical Article: Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services. John Sirmon, Greg Galloway, Cindy Gross, Karan Gulati. 2013 http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26FEF9550EFD44/Performance%20Tuning%20of%20Tabular%20Models%20in%20S QL%20Server%202012%20Analysis%20Services.docx • SQL Server Technical Article: Hardware Sizing a Tabular Solution (SQL Server Analysis Services). John Sirmon, Heidi Steen. 2013 http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26FEF9550EFD44/SSAS_HardwareSizingTabularSolutions.docx Muchas Gracias SQLMelody@gmail.com http://sqlmelody.blogspot.ca/ @SQLMelody José Mayorga V., Mag., 2015 ca.linkedin.com/in/melodyzacharias
