Hola a todos, por fin la última parte de este trabajo realizado para ustedes.
Que nos queda, crear el cubo de gestión OLAP que permite servirse de la base de datos DMABC a través del ETL.
-Recordemos que lo realizado fue crear una base de datos multidimensional a partir de los datos fuentes . Luego se programó el ETL iniciales por medio de SSIS. Ahora que viene:
-Crear las dimensiones, cubo y presentar la información por medio de Microsoft EXCEL.
Crear proyecto Cubo
Abre SQL Server Business Intelligence Development Studio y vamos a File - New - Project.
En tipo proyecto selecciona Business Intelligence Projects. En templates, selecciona Analysis Services Project. En Name registra el nombre del proyecto, en mi caso es "DM_Despacho". En la figura 1 queda la solución.
Figura 1: Explorador de solucciones
Antes de seguir, recordemos:
Modelo Multidimensional
El modelo multidimensional se utiliza para modelar bases de datos, la información se representa como matrices multidimensionales, en donde cada una de estas matrices se denomina cubo. La idea de almacenar la información en estas matrices, es que la base de datos es visualizada como un cubo de tres o más dimensiones, y así es más fácil para el usuario visualizar la relación que existe entre los componentes e imaginarse la forma de obtener la información deseada. En el modelo multidimensional los ejes corresponden a las dimensiones y a los datos que se representan en la matriz se les llama medida.
Componentes del Modelo Multidimensional
Los componentes del modelo multidimensional son dos: los esquemas y las tablas. Las tablas que se representan en el modelo multidimensional se dividen en dos tipos:
• Tablas de hechos: Son el objeto a analizar, es la tabla central en un esquema dimensional, contienen datos sobre las actividades principales de la organización. Cada tupla de la tabla se compone de datos observables de cada actividad y de las referencias a las dimensiones que los caracterizan[1] .
• Tablas de dimensiones: Son las tablas que alimentan a la tabla de hechos, estas almacenan valores relacionados a una dimensión particular. Son los elementos que determinan la estructura de las dimensiones. Representan cada uno de los ejes en un espacio multidimensional, poseen atributos llamados dimensión o clasificación. Las dimensiones poseen jerarquías, que son varios atributos unidos mediante una relación de tipo jerárquico[1] .
En la figura 1, lo primero es definir la fuente de datos, botón derecho en Data Sources, luego New Data source, luego next y en la ventana nombrada Data Source Wizard selecciona la base de datos DMABC, esta es la que posee los datos transformados y cargados por el ETL. Next y selecciona "use the service account", Next y quedamos con el nombre fuente DMABC. Lo puedes cambiar si lo deseas.
Guiándonos por la figura 1, debemos seleccionar ahora Data Source View, esto permite seleccionar un conjunto de tablas que provienen de tu Data Source. Repite el mismo paso anterior y selecciona en "Relational data sources", DMABC y luego Next, Next, en la ventana nombrada Data Source View Wizard, donde se lista las tablas de tu base datos DMABC, debes seleccionar las tablas que te permitirán alimentar tu CUBO. En mi caso la selecciono todas. Mira la figura 2.
Figura 2: Selección de tablas o vistas
En mi caso selecciono todas las tablas, y las dejo en Included objects. Luego NEXT y me queda lo siguiente en la figura 3:
Figura 3: Tablas completadas para el CUBO a definir.
Como va quedando esto en Solution Explorer
Figura 4: Diagrama del Data Source View
Entonces en Data Source View te permite definir los DataMart que necesitas. Hecho esto, falta solamente definir las dimesiones y el cubo. Yesssss :)
En la figura 4, el esquema nos queda Estrella.
• Esquema estrella (star join): Este esquema esta formado por una tabla central y un conjunto de tablas de dimensiones unidas a ella. Su nombre se deriva ya que su diagrama forma una estrella, con puntos radiales desde el centro. Las tablas de dimensiones tienen conexión a la tabla central y a ninguna más.
• Esquema copo de nieve (snowflake): Este modelo se basa en el modelo estrella, pero aquí las tablas están normalizadas. Cada tabla de dimensiones contiene solo el nivel que es clave primaria en la tabla y la foreign key de su parentesco del nivel más cercano del diagrama.
Dimensiones
Las dimensiones son los criterios de análisis de los datos de tu empresa, son el objeto del problema, representan variables independientes y se alojan en los ejes del cubo. Una característica de las dimensiones es que si tienen más de un nivel, entonces los miembros de la dimensión, pueden ser representadas mediante una o más jerarquías. La ventaja de almacenar la información en un modelo jerárquico es que nos permite organizarla de forma detallada como sea necesario, y así poder analizarla en distintos niveles[1] .
En la figura 1, debes seleccionar Dimensions, clic botón derecho, luego selecciona New Dimensión... mira la figura 5.
Figura 5: Método de selección.
No voy a profundizar en detalle de cada opción que te presenta esta ventana, solo selecciona la opción "Use an existing table", luego NEXT. En resumen para crear las dimensiones las seleccionaré de las tablas definidas en "Data Source Wizard".
Figura 6: Especificar Dimensión D_CALLE
En la figura 6, se especifica el nacimiento de la dimensión D_CALLE, mira en Name column: seleccioné "id_calle", ya que será la columna que me permite caracterizar la jerarquía y es la relación que hay entre D_CALLE y la tabla de Hecho.
Figura 7: Especifica los atributos a listar.
Luego de la figura 7, selecciona DES_CALLE y clic en Next.
Figura 8: Dimensión y su jerarquía
En figura 8, finaliza y la dimensión D_CALLE queda grabada.
Figura 9: Procesar Dimensión.
La dimensión creada se procesa, esto permite obtener los datos de la fuente en Data Source View y almacenar los datos en la base de datos Dimensional del tipo OLAP. No es una base de datos relacional.
Figura 10: Carga de datos
Los datos quedan registrados en la dimensión D_CALLE. Al salir de esta ventana puedes listar los datos, así quedan en la siguiente figura:
Figura 11: Lista dimensión D_CALLE
Ahora repite los mismo para D_COMUNA, D_PRODUCTO, D_TPCLIENTE Y D_TIEMPO.
En la dimensión D_TIEMPO puedes crear jerarquías de varios niveles. Mira la figura 12
Figura 12: Jerarquía en D_TIEMPO
Al crear la Dimensión, puedes crear jerarquías, solo debes pasar los atributos de menor a mayor en el lado donde dice "hierarchy"
Figura 13: Dimensiones
Vamos a crear el CUBO, en la figura 13, clic en Cubes y clic botón derecho, luego New Cube... selecciona Use Existing tables y Next.
Recuerda que el cubo siempre es la tabla de HECHO, en este caso H_DESPACHO. La tabla de hecho posee las medidas que son los valores de análisis de los datos, además los campos claves están asociados a las dimensiones. Son variables dependientes y variables en la intersección de las dimensiones. Luego Next y mira la figura 14.
Figura 14: Medidas incluidas en el cubo.
De la tabla de Hecho en la figura 14, las medidas disponibles; valor cobrado, Cantidad y H DESPACHO Count. Este último es presentado en caso que no tengas la medida de cantidad de registros.
Figura 15: Dimensiones asociadas al cubo.
En la figura 15, las dimensiones están todas seleccionadas, fueron creadas anteriormente. Luego NEXT y finaliza. Luego procesa el cubo, figura 16, carga las dimensiones y la tabla de HECHO. Este proceso se alimenta de la base de datos relacional DMABC, Al final el cubo queda con los datos basado en un modelo multidimensional, en el cual la información se encuentra organizada por temas dándoles alguna orientación, para ayudar a los usuarios a realizar análisis dinámicos de los datos.
Figura 16: Procesa Cubo OLAP
Mira la figura 17, como queda el cubo con sus dimensiones relacionadas.
Figura 17: Estructura del cubo.
Enla figura 17, selecciona la opción "Browse" para navegar por los datos, mira la figura 18.
Figura 18: Navegación de los datos
Esto es igual que trabajar en una tabla dinámica en EXCEL. Entonces al usuario final debe usar EXCEL así el está familiarizado con EXCEL, lo usará, si le pasas otro programa para navegar en un cubo OLAP te aseguro que no lo usará.
Herramientas OLAP
Las herramientas OLAP tienen sus operadores de refinamiento o manipulación de consultas[1] :
• Drill (Disgregación): Permite introducir un nuevo criterio de agrupación en el análisis, agregando los grupos actuales.
• Roll (Agregación): Permite eliminar un criterio de análisis, agregando los grupos actuales.
• Slice & Dice: Permite seleccionar y proyectar datos en el informe.
• Pívot: Permite la reorientación de las dimensiones.
Vista del cubo usando EXCEL
Abre tu EXCEL y ve a DATOS (en el menú principal), selecciona Desde Analysis Services, luego te pide el nombre del servidor donde reside el cubo anterior, en mi caso locahost. Mira la figura 19.
Figura 19: Conexión al cubo OLAP
Selecciona tu cubo "DM_DESPACHO" en mi caso y Siguiente y Finalizar.
Figura 20: Conexión a EXCEL
De la figura 20, esto debes tener. En la lista de campos debes realizar lo siguiente:
-Etiqueta de fila: Siempre incorpora las Dimensiones con su atributo, por ejemplo "des_comuna". (D_COMUNA)
-Etiqueta de columna: Siempre incorpora las Dimensiones con su atributo, por ejemplos"tipo cliente". (D_TPCLIENTE)
-En Valores, siempre utilizar las medidas, por ejemplo "cantidad" (H_DESPACHO)
-Filtro de informe: Siempre incorpora las Dimensiones con su atributo, por ejemplo "agno" (D_TIEMPO).
Mira las figuras 21,22, 23,24 y 25, como el usuario final puede ver los datos y realizar análisis cuando el lo desee. Te recomiendo que debes tener un procedimiento explícito con los acuerdos y responsabilidades de las actualizaciones de tus ETLs hacia el o los usuarios. .:)
Con esto el o los usuarios finales pueden explorar los datos a través de EXCEL fácil y útil.
Figura 21: Datos en EXCEL
Figura 22: Manipulación de consulta
Figura 23: Generar gráfico
Figura 24: Listar Detalle
Ventaja en uso de la herramienta OLAP con EXCEL.
-El usuario no puede eliminar los datos que están en tu cubo y en el EXCEL. Solo puede cambiar los filtros o seleccionar otras dimensiones.
-Puede guardar esta consulta y la puede llamar cuántas veces lo desea.
-Generar gráficos.
-Puede rotar la información utilizando los conceptos DRILL, ROLL, SLICE & DICE y PIVOT.
-En la figura 24 se lista el detalle de los datos que está en la figura 22, en la medida 3 (fila 6, columna C). Te posicionas y clic botón derecho y clic en Mostrar detalle (figura 25). Se lista la figura 24 en otra Hoja dentro de tu archivo Excel. Es espectacular el uso de OLAP con EXCEL. Te lo recomiendo.
Figura 25
Espero que lo realicen y dudas o consultas a lpizarrofx@gmail.com. Se van hacer famososssssssss en la empresa.....
Que nos faltó: uffff toy cansado.....
• Saber dónde apoyar tu empresa con proyectos de Inteligencia de Negocios.
• Saber cómo apoyar tu empresa con Inteligencia de Negocios.
• Desarrollar Jerarquías, MDX y KPI, [2]
• Procesar Cubos OLAP, MOLAP, ROLAP
Bibliografía
[1] Proyecto título Cubo de Gestión. Gislaine Valenzuela Zañartu. UCSC
[2] Tecnología Microsoft. https://msdn.microsoft.com/es-es/library/hh230953(v=sql.120).aspx
