1/3 Desarrollo de un Cubo OLAP para EXCEL con Business Intelligence de Visual Studio Microsoft

Hola estimados(as)

El siguiente tema ya es conocido, pero en algunas organizaciones aún no lo incorporan y muchos de mis cercanos me han consultado sobre este tema. Entonces, veamos un ejemplo concreto para disponer de un CUBO OLAP en tu empresa.

Espero que al finalizar este conjunto de artículos asociado a este tema, puedas:

• Determinar requerimientos de preguntas de gestión.
• Clasificar los elementos de las preguntas en Dimensiones y Medidas.
• Resumir los elementos y diseñar un modelo multidimensional.
• Crear procesos para carga de datos. (ETL)
• Diseñar los ETL con SSIS de microsoft y crear las dimensiones y cubo.
• Con Excel puedas visualizar los datos de gestión.

El término  OLAP  (OnLine Analytical Processing) procede de un artículo escrito en 1993 (Arbor Software Corp). Es un concepto antiguo definido como "el proceso interactivo de crear, mantener, analizar y elaborar informes sobre datos" [2].

En todas las empresa se tiene la  necesidad de obtener datos en línea para tomar mejores decisiones. Hay necesidad de conocimiento, información y datos.  En cada empresa existe un gran conjunto de bases de datos que resultan minas de información [1].

Factores críticos: Tiempo de acceso, integración y calidad de Información, consultas complejas que no pueden extraerse ni siquiera empleando SQL. (lenguaje de consulta estructura, la definición y manipulación de datos.)

Figura 1: Modelo de Sistema de Información [1]

Inicio

El siguiente comentario es de un Gerente de Finanzas:  "Cualquier asunto que estemos tratando en la empresa, de alguna manera siempre está relacionado con lo que nuestras bases de datos pueden o no pueden entregar”. 

Para este Gerente un ideal de base de datos es una especie de gran planilla electrónica, con todos los datos que necesita para reducir la incertidumbre en su toma de decisiones. No entiende por qué no puede ser así, por qué tiene que estar hurgando en sus datos operacionales, en vez de tener una copia de ellos o de parte de ellos, y así poder jugar con posibles escenarios futuros. 

Cómo le explicamos esto al Gerente:

Tenemos base de datos operacionales OLTP y analíticas OLAP.

         OLTP: Procesos orientados a las transacciones en línea (pagos web, venta, facturación, pos,..)
         OLAP: Procesos orientados analíticos en línea.

Los datos operacionales OLTP  no son adecuados para aplicaciones del tipo gestión gerencial basados en Inteligencia de Negocios o Cubos de Gestión. Entonces hay que disponer de una bodega de datos.

Bodega de Datos (Data warehouses). "Un Data warehouse es un almacén de datos orientado a un tema, integrado no volátil y variante en el tiempo, que soporta decisiones de administración". El término no volátil significa que una vez que los datos han sido insertados, no pueden ser cambiados, aunque sí pueden ser borrados. [2]

Veamos un recurso de los sistemas de información, la calidad de la Información, ver figura 2.

Figura 2: Resumen de los atributos de la calidad de la información [1]

El cubo es una estructura de datos multidimensional organizada en dimensiones y medidas. Posee los datos analíticos con los que queremos trabajar.

Medidas : son los datos numéricos que queremos analizar. (cantidad de clientes mososos)

Dimensión: es cada uno de los ejes que forman el cubo. Las dimensiones son jerárquicas y, en la mayoría de los casos, sus miembros están organizado en una configuración de tipo piramidal. (dimensiones; clientes, fecha o periodo y ciudades)

El ingreso de datos en el data warehouse viene desde el ambiente operacional en casi todos los casos.

El data warehouse es siempre un almacén de datos transformados y separados físicamente de la aplicación donde se encontraron los datos fuentes del ambiente operacional.

Figura 2.1: Cubo de tres dimensiones

Ejemplo, un empresa de servicios, necesita la siguiente información:

1.- Obtener cantidad total de envíos que se han realizado a las distintas ciudades.

2.- Obtener las cantidades totales de envíos por proveedor.

3.- Obtener las cantidad totales de envíos a ciudades.

4.- Obtener las cantidades totales de envíos por proveedor y ciudad.

En la figura 3, podemos decir que esta es una forma más compacta y legible de representar el resultado de las consultas 1,2,3 y 4.

Figura 3: Procesamiento Analítico en Línea OLAP

De la figura 3, el usuario final o el famoso término actual "Colaboradores" :), puede obtener esta información a través de Microsoft Excel. Entonces asociando estos temas al concepto “modelo multidimensional” tenemos el “cubo de gestión”, que es de dos dimensiones, en este caso. (Ciudad y Proveedores).

En la figura 5, se tiene las distintas consultas 1,2,3 y 4  **OLTP**. (así los informáticos tenemos a veces que obtener la información, cuando la solicitan... )

Figura 5: Consultas 1,2,3 y 4 a través de comandos SQL (***OLTP***)

Empecemos a diseñar nuestro Cubo

Que se debe hacer inicialmente [3]:

• Determinar los requerimientos, empleando preguntas de gestión.
• Clasificar los elementos de las preguntas en Dimensiones y Medidas.
• Resumir los elementos del Modelo dimensional en la Matriz de Dimensiones, Medidas versus Preguntas de gestión.
• Diseñar a partir de la matriz, preguntas de gestión a las tablas de dimensiones (Incluir las columnas determinantes de cada Dimensión) y la tabla de Hechos posibles.
• Aplicar el concepto de Granularidad a las Tablas de Hechos.
• Integrar el Modelo Dimensional por cada Fact.

Características de las preguntas:

-De las entrevistas se debe obtener preguntas de Gestión basadas en el tiempo.

-Las preguntas de gestión se componen de Dimensiones y Medidas o Indicadores de gestión.

-Las dimensiones permiten describir al indicador de gestión, es decir el elemento de la dimensión interpreta al valor del indicador de gestión.

-En toda pregunta no debe faltar el tiempo (Dimensión).

-Toda pregunta de gestión aporta al Modelo Dimensional.

Modelo Actual de la base de datos Operacional (fuente de datos)

El siguiente modelo de datos es sencillo, no tenemos las claves foráneas ni el diccionario de datos, pero podemos analizarlas según lo que vemos en el modelo. Además no cumple las formas normales de un buen modelo, pero supongamos que esta es la fuente de datos y debemos resolver el tema de lograr desarrollar el Cubo.

Tablas del modelo:
-Cli: Registra el maestro de clientes y la residencia de estos.
-Producto: Registra maestro de productos.
-Tipo_cliente:  Define el tipo de cliente.
-Enviodespacho:  Se registra los movimiento de despacho emitidos a los clientes.
-Envioproduco Se registra los movimiento de despacho de los producto enviado a los clientes.

Figura 6: Modelo de datos (base datos Operacional)

Preguntas de Gestión

La idea es que ustedes al iniciar las preguntas a los interesados, deben cumplir con las siguientes características de Requerimiento:
-Especificado por escrito
-Posible de probar y verificar
-Conciso
-Completo
-Consistente
-No ambiguo

Para más detalle de Ingeniería de Requisitos, lee Especificación de Requisitos según el estándar de IEEE 830.

Importante conocer las fuentes de requerimientos, tales como:

-Entrevistar a los Analistas de Negocio.
-Entrevistar a los Gerentes de Áreas de Negocio.
-Reportes y gráficos de Consolidación de Información de Negocios.
-Modelos de Bases de Datos Relacionales (dba)
-Datos de Fuentes de Datos Internas y Externas.


Veamos el caso de una empresa, donde su modelo de negocio está centrado en el despacho y venta de productos "x" a clientes nacionales. La fuente de sus datos están en la figura 6.

Al finalizar las entrevistas y análisis, las siguientes preguntas de gestión serán la base para construir el modelo multidimensional para el cubo de gestión.

Pregunta 1: Se necesita consultar el valor promedio cobrado de los envíos acotado por comuna y tipo de cliente durante el periodo del presente año y año anterior a fin de estimar las diferencias de precios de venta por año.

Pregunta 2: Se desea consultar los envíos del presente año y de los últimos seis meses, de acuerdo a la suma total del valor cobrado por producto, a fin de estimar qué meses hubo más utilidad por producto vendido despachado.

Pregunta 3:  Cantidad total de clientes agrupados por comuna, calle y tipo de clientes. Esto persigue potenciar las comunas con menos clientes y así aumentar o justificar la contratación de  nuevos ejecutivos de ventas.

Pregunta 4:  Todos los meses se desea conocer, cuáles han sido los productos más despachados en los años 2015, 2014 y 2013, acotado por comuna y tipo cliente.

Pregunta 5: Cuál es la cantidad total de despachos y valor cobrado a los clientes por comuna y tipo cliente durante el presente año?


Siguiente paso:

Clasificar los elementos de las preguntas en Dimensiones y Medidas

Debes ahora separar los objetos de medidas y dimensiones, basándonos en las preguntas anteriores obtenidas. Por ejemplo, en la pregunta 1.

Las Comunas y Tipo Clientes son objetos del problema, entonces, en este caso funcionan bien como una dimensión. El tiempo es típicamente un criterio de análisis y el valor promedio cobrado es una medida.


Las siguientes tablas te permiten a partir de la preguntas de gestión más las fuentes de datos, en este caso de la figura 6, clasificar y especificar las dimensiones y medidas. Veamos como queda esto:

Pregunta 1: Se necesita consultar el valor promedio cobrado de los envíos acotado por comuna y tipo de cliente durante el periodo del presente año y año anterior a fin de estimar las diferencias de precios de venta por año.
Análisis de Pregunta:
	Medidas
	Valor promedio cobrado
	Dimensiones y elemento de Jerarquía
	comuna, tipo cliente, periodo (tiempo)

Entonces en la tabla de la pregunta 1, debes incorporar la pregunta y ubicar, cuál o cuáles son las medidas. Después busca cuáles son las dimensiones. Esto te permite analizar en conjunto con el usuario final el resultado final del cubo a diseñar.

Pregunta 2: Se desea consultar los envíos del presente año y de los últimos seis meses, de acuerdo a la suma total del valor cobrado por producto, a fin de estimar qué meses hubo más utilidad por producto vendido despachado.
Análisis de Pregunta:
	Medidas
	cantidad
	valor cobrado
	Dimensiones y elemento de Jerarquía
	Tiempo, producto

Pregunta 3: Cantidad total de clientes agrupados por comuna, calle y tipo de clientes en el presente año. Esto persigue potenciar las comunas con menos clientes y así aumentar o justificar la contratación de  nuevos ejecutivos de ventas.
Análisis de Pregunta:
	Medidas
	cantidad
	Dimensiones y elemento de Jerarquía
	Tiempo, comuna, calle, tipo cliente

Pregunta 4: Todos los meses se desea conocer, cuáles han sido los productos más despachados en los años 2015, 2014 y 2013, acotado por comuna y tipo cliente.
Análisis de Pregunta:
	Medidas
	cantidad de despachos
	Dimensiones y elemento de Jerarquía
	Período (tiempo), producto, comuna y  tipo cliente

Pregunta 5: Cuál es la cantidad total de despachos y valor cobrado a los clientes por comuna y tipo cliente durante el presente año?
Análisis de Pregunta:
	Medidas
	cantidad
	valor cobrado
	Dimensiones y elemento de Jerarquía
	Tiempo, comuna y tipo cliente

Proceso de Obtención de Dimensiones y Medidas

La siguiente tabla te permite resumir en una matriz las dimensiones y medidas de cada pregunta. 

Tabla 1: Preguntas / Dimensiones y Medidas 

DIMENSIONES	P1	P2	P3	P4	P5
Tiempo	X	X	X	X	X
Comuna	X		X	X	X
Calle			X
Tipo Cliente	X		X	X	X
Producto		X		X
MEDIDAS
Cantidad		X	X	X	X
Valor cobrado	X	X			X

Ahora diseñemos nuestro modelo multidimensional para soportar los datos de nuestro cubo OLAP.
Un modelo multidimensional se caracteriza por los conocidos esquemas estrella y copo de nieve.

Del modelo de datos en la figura 6, que es la fuente de datos principal, debemos hacer lo siguiente para crear nuestro modelo.

-Primero vamos a desnormalizar las relaciones que pertenecen a las dimensiones, incorporando una relación para cada dimensión.
-Generamos la dimensión periodo (siempre)
-Crear la Tabla de Hecho (siempre) esta tabla es la que posee las medidas y la relación entre las dimensiones.
-Tenemos que si eliminar los atributos innecesarios en las dimensiones.

Miren lo siguiente:

La tabla producto quedará como una dimensión. Entidad componente.

 

La tabla Tipo cliente quedará como una dimensión. Entidad componente.

La tabla cli la vamos a descomponer en dos dimensiones. Dimensión  calle y comuna. La tabla enviodespacho ayudará a cargar los datos cuando tengamos las dimensiones.

De las tablas envioproduco y enviodespacho obtenemos las medidas y la dimensión período.

Tabla de Hecho (Fact)

A partir de las dos tablas envioproduco y enviodespacho obtenemos las medidas, que ya fueron analizadas en la matriz de la tabla 1.

Cuando diseñes la tabla de Hecho, ups se me olvido definir, "tabla central que permite obtener y tomar decisiones de acuerdo a los eventos que suceden en los distintos procesos de la empresa", considera lo siguiente:

Busca siempre en la fuente de datos atributos numéricos que midan un hecho en particular. Ejemplo:

-Venta de productos

-Llamadas de reclamos de un Call Center

-Despachos a clientes

-Venta de gas 

-Clientes con morosidad

Los que tengan propiedades estáticas, por ejemplo, cliente, producto, ciudad, calles no son candidatos para Hechos, solo son .......DIMENSIONES.

Diseño de Tabla de Hechos por Área de Negocio 

En este caso tenemos una sola área de negocio. En la figura 7 se tiene el modelo Multidimensional que pudimos realizar según lo visto anteriormente, el cual permite generar el cubo de gestión OLAP.

Figura 7 :Integración del Modelo Dimensional 

De la fuente de datos de la figura 6 se tiene el modelo de la figura 7. Las tablas de  dimensiones y Hechos quedaron:

-D_Producto: almacena los productos.

-D_Calle: almacena las calles.

-D_Comuna: almacena las comunas.

-D_TPCliente: almacena los tipos de clientes.

-D_Tiempo: almacena los periodos. (año, mes y día)

-F_Despacho: almacena las medidas. (tabla de hecho)

Tabla 2: Resumen del modelo multidimensional asociado al análisis.

FACT		Despacho
Dimensiones	Tiempo, Producto, Calle, Cliente, Comuna, Tipo Cliente
Despacho	Grano	Medidas	Descripción y Pregunta origen
	id_pro		Clave foránea de la tabla  D_PRODUCTO,   p2, p4 y p5.
	Id_tp_cliente		Clave foránea de la tabla  D_TPCLIENTE,  p1, p3, p4 y p5.
	id_tiempo		Clave foránea de la tabla  D_TIEMPO,  p1, p2, p3, p4 y p5.
	id_comuna		Clave foránea de la tabla  D_COMUNA,  p1, p3, p4, p5.
	Id_calle		Clave foránea de la tabla  D_CALLE,  p3.
	Valor_cobrado	Valor cobrado	Precio cobrado por el despacho del producto, p1, p2, p5.
	Cantidad	cantidad	Cantidad de productos despachados, p2, p3, p4 y p5.

Con la tabla 2 se pudo concretar el modelo multidimensional con esquema tipo estrella. Tipo estrella su nombre se deriva ya que su diagrama forma una estrella, con puntos radiales desde el centro. Las tablas dimensiones tienen conexión a la tabla central y a ninguna más.

Entonces en el segundo artículo continuamos con la segunda parte. Comprende crear los procesos necesarios para cargar los datos fuentes a nuestro modelo multidimensional a través de Microsoft SQL Server Integration Services (SSIS).

Espero que les sirva y realicen un ejemplo de esto en tu organización y veamos como te queda el modelo. Dudas, comentarios a mi correo lpizarrofx@gmmail.com


Buen fin de semana...  y faltó la figura 4. :) no quiso verse hoy.

Bibliografía

[1]  Sistema de Información Gerencial. Jamess A. O' Brien

[2]  Introducción a los Sistema de Bases de Datos. C. J. Date

[3]  Universidad Andrés Bello. Alfonso Rubén Pereda