AA4-EV6- DISEÑO DE UNA BODEGA DE DATOS Y CONSTRUCCIÓN DE UN CUBO
ING. EDWIN ALEXANDER SUAREZ PALACIO ING. CESAR YAIR CASTRO MURILLO
SERVICIO NACIONAL DE APRENDIZAJE, SENA GESTIÓN Y SEGURIDAD DE BASE DE DATOS BARRANQUILLA 2017
Contenido INTRODUCCIÓN ................................................................................................................................... 3 Modelos y metodologías para el diseño y construcción de Bodegas de Datos (Data Warehouses) .. 3 Metodologías para minería de datos .............................................................................................. 3 FASES PARA EL DISEÑO E IMPLEMENTACIÓN DE CUBOS DE DATOS .................................................. 4 FASE DE COMPRENSIÓN DEL MODELO DE NEGOCIO ......................................................................... 4 Comprensión del modelo de negocio ............................................................................................. 4 FASE DE LEVANTAMIENTO DE REQUERIMIENTOS .............................................................................. 4 FASE DE RECOLECCIÓN DE LOS DATOS: .......................................................................................... 5 Identificación de las fuentes de los datos ................................................................................... 5 Descripción de los datos.............................................................................................................. 5 Exploración de los datos.............................................................................................................. 5 Verificación de la calidad y consistencia de los datos ................................................................. 6 FASE DE PREPARACIÓN DE DATOS ...................................................................................................... 6 FASE DE MODELADO DEL CUBO DE DATOS ........................................................................................ 7 FASE DE IMPLEMENTACIÓN DEL CUBO DE DATOS ............................................................................. 9 FASE DE CONSTRUCCIÓN CONSTRUCCIÓN DEL VISUALIZADOR DEL CUBO DE D E DATOS.................. ........................... ................... ................... ........... .. 16
AA4-EV6-BLOG AA4-EV6-BLOG DE GRUPOS GRUPOS DE TRABAJO PARA PARA EL DISEÑO DE UNA BODEGA BODEGA DE DATOS Y CONSTRUCCIÓN DE UN CUBO
INTRODUCCIÓN El diseño y construcción de cubos de datos permite a las organizaciones escalar progresivamente hacia una arquitectura de almacenamiento con Bodegas de Datos (Data Warehouse), sobre los que se puede pu ede aplicar técnicas de minería de datos con el fin de extraer conocimiento conoc imiento que permita satisfacer las expectativas de los clientes y alcanzar los objetivos estratégicos de la organización.
Modelos y metodologías para el diseño y construcción de Bodegas de Datos (Data Warehouses) Modelo de Barry Devlin y Paul Morphy: Modelo de Ralph Kimball Modelo de William Bill Inmon Modelo de Golfarelli Matteo, Maio Dario y Rizzi Stefano: Metodología HEFESTO
Con base a los requerimientos requeridos “AdventureWorks se utilizara la metodología o Modelo de Golfarelli Matteo, Maio Dario y Rizzi Rizzi Stefano: es un esquema que parte de los modelos entidad relación (MER) de los sistemas transaccionales de la organización, para luego derivar el MER de la estructura para el diseño de la Bodega de Datos
Metodologías para minería de datos
Las metodologías para realizar minería de datos abarcan los modelos de construcción de una Bodega de Datos como un ítem dentro del proceso de extracción de conocimiento de los datos, por esta razón a continuación mencionamos algunas de las más relevantes:
Metodología CRIPS – DM (Cross Industry Standard Process for Data Mining) • Metodología SEMMA (Sample, Explore, Modify, Model, Assess) • Metodología DMAMC (Definir, Medir, Analizar, Mejorar, Controlar) • KDD process - (Knowledge Discovery in Databases) •
FASES PARA EL DISEÑO E IMPLEMENTACIÓN DE CUBOS DE DATOS A continuación se realiza la descripción de cada una de las fases que tiene el proceso de diseño e implementación de un cubo de datos; adicionalmente, para realizar la práctica se describirán las actividades a realizar como “Caso Práctico” utilizando para ello la base de datos “AdventureWorks.sql”
FASE DE COMPRENSIÓN DEL MODELO DE NEGOCIO En esta fase se recolecta la información corporativa e institucional que permita comprender el modelo de negocios e identificar los objetivos estratégicos de la organización. Se consultan fuentes como el organigrama empresarial, el diagrama de procesos, el manual de procedimientos, el portafolio de servicios y todas aquellas que suministren información que permita comprender la estructura y el funcionamiento de los procesos que tiene la organización. Comprensión del modelo de negocio
Adventure Works Cycles, es una empresa multinacional que fabrica y vende bicicletas en los mercados de Norteamérica, Europa y Asia. Su sede central de operaciones se encuentra en Bothell, Washington, con 290 empleados, su mercado está compuesto por varios equipos regionales de ventas. • En el año 2000, la empresa compró una pequeña planta de fabricación, “Importadores Neptuno”,
situada en México la cual fabrica varios subcomponentes para la línea de productos de Adventure Works Cycles. Estos subcomponentes subcompone ntes se envían a la sede de d e Bothell para el ensamblado final del producto. • En el año 2001, Importadores Neptuno pasó a ser el único fabricante y distribuidor del grupo de productos de bicicletas de paseo.
FASE DE LEVANTAMIENTO DE REQUERIMIENTOS
Para este levantamiento de requerimientos es necesario aplicar diferentes técnicas de recolección de información las cuales nos dará una información más detallada de lo que el cliente quiere y con lo que el cliente cuenta, para ello se puede aplicar la entrevista, observación y cuestionarios. Después de esto se procede a tomar los datos y modelarlos. Para este caso utilizaremos la técnica de entrevista combinada con las lluvias de ideas.
FASE DE RECOLECCIÓN DE LOS DATOS: Se debe realizar los siguientes pasos Identificación de las fuentes de los datos
Se identifican los sistemas fuentes que contienen los datos y se extraen para posteriormente adecuarlos, es importante tener en cuenta que los datos pueden residir en diversos tipos de sistemas, a continuación mencionamos algunos de los más típicos dentro de las organizaciones: Hojas de cálculo, Bases de datos, Archivos estadísticos, Sistemas de información empresarial (ERP, ERP, FRM, HRM, MRP, SCM) Archivos documentales físicos y digitales. Descripción de los datos
Se realiza la descripción de los datos extraídos desde los sistemas fuentes con el fin de establecer sus características y métricas de la siguiente forma: Descripción cualitativa, Descripción cuantitativa, La información recolectada de los datos se consigna en un inform e denominado “reporte de la descripción de datos
Exploración de los datos
Se procede a explorar los datos que han sido extraídos desde los sistemas fuentes, con el fin de encontrar una estructura general para los datos “homogeneidad” y de identificar problemas “datos paralizantes” que puedan ocurrir durante las fases
siguientes.
Verificación de la calidad y consistencia de los datos
Se efectúan verificaciones sobre los datos, que permitan asegurar la consistencia de los valores individuales de los campos, la cantidad y distribución de los valores nulos y la corrección de valores fuera de rango que puedan constituirse en elementos que alteren el resultado del proceso.
CASO PRÁCTICO:
Para realizar la fase de recolección de datos con cada una de sus etapas deberá tener en cuenta que los datos de la empresa “Adventure Works Cycles” están dentro del archivo “BaseDatosEjemplo.zip”, allí se encuentra una base de datos relacional
que está construida sobre el sistema manejador de bases de datos SQL Server 2008. Para la descripción de los datos debe consultar el diccionario de datos de las siguientes tablas con el fin de identificar los metadatos que contiene cada una de ellas
FASE DE PREPARACIÓN DE DATOS Finalizada la fase de recolección de los datos, se procede al alistamiento de los datos para la posterior construcción del cubo de datos. La fase de preparación de datos consta de las siguientes etapas:
FASE DE MODELADO DEL CUBO DE DATOS En esta fase se identifican las dimensiones, métricas y tablas de hecho que constituirán el cubo de datos. Algunos de los aspectos para identificar los atributos de estos elementos son: Atributos de métrica: son aquellos atributos que permiten establecer un valor cuantitativo sobre los datos. Atributos de dimensión: son todos los atributos que aportan cualidades a los datos. Datos multidimensionales: son los datos que no pueden modelarse como atributos de dimensión o de medida. Atributos de la(s) tabla(s) de Hecho(s): Para identificar estos atributos hay que prestar especial atención a las tabulaciones cruzadas puesto que estas son sumatorias que no están guardas directamente en las tablas del modelo relacional,
sino que son el resultado de operaciones aritméticas que se obtienen de disponer de distintas formas los atributos de métrica y de dimensión. CASO PRÁCTICO
En esta fase se deben identificar los campos, metadatos y datos que se requieren para construir las dimensiones del cubo de datos. Para esto se toman como referencia las cinco (5) tablas creadas en el SMBD, de la siguiente forma:
Identificados los campos que se requieren para la construcción de cada una de las dimensiones del cubo, para el caso que se está desarrollando se se recomienda seleccionar el modelo estrella para el diseño del cubo. En el gráfico puede ver el modelo estrella del cubo de datos construido a partir de la base de datos “AdventureWorks”.
FASE DE IMPLEMENTACIÓN DEL CUBO DE DATOS En esta la fase se construye el modelo físico del cubo de datos el cual se realiza de la siguiente forma: 1. Crear las tablas de cada una de las dimensiones del cubo con sus respectivos atributos y llaves primarias. 2. Después se construye(n) la(s) tabla(s) de hecho(s) con sus campos, llaves primarias y relaciones que se han identificado en el modelo seleccionado.
CASO PRÁCTICO:
Se procede a la construcción física del cubo de datos a partir del modelo estrella desarrollado para la base de datos “AdventureWorks”, para esto s e realizan los siguientes pasos: 1. Creación de las dimensiones: Se crean las tablas que corresponden a cada una de las dimensiones del cubo de datos. Consulte el script SQL “CrearTablasDimensiones.sql ” y ejecútelo en el SMBD.
Creamos la dimension d-customer
Creamos la dimension d-product
Creamos la dimension d-salesterritory
Creamos la dimension d-shipdate
2. Llenado de las dimensiones desde las tablas fuentes: Se recuperan los datos desde los sistemas fuentes del nivel (OLTP), se transforman y se cargan en cada una de las tablas que conforman las dimensiones del cubo de datos. Consulte el script SQL “LlenarDimensiones. sql ” y ejecútelo en el SMBD.
3. Construcción de la(s) tabla(s) de hechos del cubo de datos: Se crea la tabla de hechos del cubo de datos. Consulte el script scrip t “CrearTablaHechos.sql ” y ejecútelo ejecút elo en
el SMBD.
4. Llenado de la(s) tabla(s) de hechos del cubo de datos: Se consolidan las métricas realizando los cálculos sobre los datos multidimensionales y se almacena el resultado dentro de la tabla de hechos del cubo de datos. Consulte el script SQL “LlenarTablaHecho.sql ” y ejecútelo en el SMBD.
FASE DE CONSTRUCCIÓN DEL VISUALIZADOR DEL CUBO DE DATOS La fase de construcción del visualizador del cubo de datos puede ser abordada de las siguientes formas: •Construcción una vista en la base de datos •Desarrollo de una aplicación en un lenguaje de programación específico •Utilización de una herramienta de uso específico existente •Creación del visualizador en una herramienta de uso general como una hoja de
cálculo. CASO PRÁCTICO: En esta fase final se debe crear una consulta sobre las dimensiones del cubo de datos de manera que proporcione algunos elementos claves que permitan interpretar más fácilmente los datos arrojados por la tabla de hechos. Siga los siguientes pasos: 1. Construir la tabla visualizador en donde se almacena el resultado de la consulta. Revise el script “visualizador.sql ” y ejecútelo en el SMBD.
Creamos la la tabla del vistalizador
2. Desde la base de datos “cubo”, exportar a un archivo de texto plano los datos que contiene la tabla de nombre “visualizador”. Para este caso específico puede consultar el archivo “dataVisualizador.txt “dataVisualizador. txt ” que que contiene 121.288 registros, que son
el resultado que debe dar la exportación de datos si se han realizado los pasos correctamente.
3. Para finalizar debe cargar el archivo exportado, exportado , “dataVisualizador. txt”, a una hoja
de cálculo con el fin de generar una tabla dinámica que permita consultar el cubo de datos. El resultado de este procedimiento puede consultarse en el archivo “visualizadorCubo.xlsx ”.
Ya podemos modelar la información mediante las tablas dinámicas
