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

Hola a todos

La segunda parte de este artículo es continuar con el desarrollo del CUBO OLAP en tu empresa. Teniendo el modelo multidmensional, vamos a  crear los procesos necesarios para cargar los datos fuentes a  través de Microsoft SQL Server Integration Services (SSIS).



Figura 1: Modelo Multidimensional 

De la figura 1, debes crear este modelo en SQL Server Management Studio. Te dejo aquí el enlace para crear las tablas.  Nombre de la base de datos de la figura 1, es DMABC

Figura 2: Base de datos

En la figura 2, se tiene la base datos BDABC que es la fuente de datos y la base de datos DMABC que creamos recién, la cual nos sirve para depositar los datos fuentes BDABC ---> DMABC que posterior se servirá nuestro cubo OLAP. A diferencia del modelo relacional, el modelo multidimensional te permite analizar la información mediante cubos OLAP, otra de las razones, que resulta adecuado para el estudio de los datos de la base de datos BDABC pero transformado en un DATAMART. (DMABC)

Veamos lo que hemos realizado:

-Preguntas de gestión.
-Conocer las fuentes de datos que estén alineadas con las preguntas de gestión.
-Disponer de las fuentes, en este caso base de datos relacional BDABC.
-Crear base de datos multi dimensional DMABC. (esquema estrella)

Ahora lo principal está sobre los ETL’s como primer componente de las fuentes desde donde se extrae la información necesaria para poblar la base de datos DMABC, en la figura 1 del modelo destino. 

La extracción, transformación y carga se basa en:

Transforma los datos de acuerdo al modelo de datos. (figura 1)
Limpiar los datos para corregir errores que pueden contener las fuentes.
Integrar los datos para formar la base de datos donde va a residir la información.
Lectura de datos transaccionales, se trata de obtener, mediante consultas SQL la información que se necesita de la base de datos transaccional. La primera carga de datos, suele ser la más "difícil" ya  que los datos pueden encontrarse en distintos formatos.
Integración de datos, consiste en la unión de datos de distintas fuentes, detectar cuándo  representan  los  mismos  objetos  y  generar  las  referencias  y  restricciones adecuadas para conectar la información y proporcionar la integridad de referencias.

A veces necesitarás crear tablas de paso o intermedias para cubrir los datos fuentes o destino, veamos la figura 3 y 4:


Figura 3: Vista del área del STAGE en el lado de la fuente u origen.



Figura 4: Vista del área del STAGE en el lado destino.

En la figura 3, puedes a veces necesitar mantener una tabla intermedia que te permita solucionar algunos temas en los datos origen para posterior cargar al destino. Por ejemplo, tabla intermedia para cargar fechas.

En la figura 4, al contar con la tabla intermedia se aprovecha en las cargas iniciales, otro STAGE que permitirá carga los datos intermedio  origen al destino en otra tabla intermedia en modelo dimensional (DM). Luego transformas los datos de la tabla intermedia del STAGE DM (destino) cargando los datos a la dimensión correspondiente, por ejemplo, D_TIEMPO, con sus transformaciones correspondiente.


Integration Services en Business Intelligence Development Studio

Business Intelligence Development Studio es el entorno que utilizaré para desarrollar los paquetes para la extracción, transformación y carga (ETL) de datos sobre el proyecto Integration Services.

Primer paso crear el proyecto, File - New - Project (figura 5)

Figura 5: Crear el proyecto

Después, en tipo proyecto, selecciona Business Intelligence Projects y se lista los Templates, selecciona Integration Services Project, crear un nombre del proyecto. En mi caso "ETL_ABC".


Figura 6: Vista de la solución

En SSIS Packages  se crean los paquetes, en este caso, la carga inicial del ETL. Donde dice Package.dtsx, renombrar por ETLInicial.dtsx


Figura 7: Vista de diseño

En la vista de diseño, en la parte inferior de tu ventana, se tiene el administrador de conexiones a las fuentes de datos. Hace un clic con el botón derecho de tu mouse y selecciona New OLE DB Connection... luego elije "Data connections", selecciona tu fuente de datos, en este caso "localHost.BDABC". Repite lo mismo para base destino DMABC. En la figura 8, debe quedar lo explicado.

Figura 8: Conexiones creadas


En la figura 9, la vista de diseño inicia con Control Flow:

Figura 9: Vista de diseño


En la lista de flujo de control (Toolbox), se listan varios Ítems, selecciona "Data Flow Task" y arrastrar a la vista de diseño. (figura 10)

Figura 10: Vista creada del flujo de control, fuente de datos.


En Data Flow Task, doble clic y cambia el nombre, en mi caso "Carga Comuna". Luego seleccionado el control de flujo, botón derecho y "Edit", se cambia a Data Flow, Ver figura 11.

Figura 11: Data Flow Task - Transformación de flujo de datos

Estando en Data Flow y la tarea de flujo de datos seleccionada "Carga Comuna", debes seleccionar en Data Flow Sources, OLE DB Source y arrastrar a la vista de diseño en Data flow, explico en la figura 12.

Figura 12: Definiendo fuente origen tabla comuna.

-Se selecciona "OLE DB source" en ToolBox y se arrastra a la vista de diseño en Data Flow.
-Clic botón derecho en "OLE DB Source" en Data Flow y en la ventana nombrada como "OLE DB Souce Editor", debes seleccionar en "OLE DB connection manager" tu fuente de datos, en este caso es BDABC, en caso que no se liste ninguna, hace clic en NEW... para crear tu origen de datos. Pero recuerda que se hizo, ver figura 8.

En "Data access mode" selecciona "SQL Command". En este caso es necesario ya que en la fuente de datos no tenemos la tabla comuna, solo los datos están contenidos en la tabla CLI y ENVIODESPACHO. Explico:

El siguiente SQL permite buscar las comunas y su código asociado registradas en la tabla ENVIODESPACHO y CLI. Ver figura 6 del artículo anterior. (1/3)

SELECT DISTINCT enviodespacho.id_comuna, cli.des_comuna
FROM  CLI, ENVIODESPACHO
WHERE CLI.ID_CLIENTE = ENVIODESPACHO.ID_CLIENTE

Clic en Preview para validar los datos fuentes. Figura 11.

Figura 11: Lista previa de los datos fuentes


Realizado esto, debes ir a ToolBox y busca la tarea nombrada "Data Conversion", este permite convertir o transformar los datos fuentes a los datos destino. Mira  la figura 12, 13 y 14.


Figura 12: Crear en Data Flow Transformation..

En la figura 12, seleccionado "Data Conversion" y arrástralo a la vista de diseño.
Figura 13, realiza las conexiones entre ambos objetos, debes seleccionar la flecha verde y arrastrarla hasta llegar a "Data Conversion". El resultado debe quedar como se visualiza en la figura 12.

Figura 13: Crear las relaciones entre ambos objetos.

Figura 14: Data Conversion se configura.

Selecciona entonces DATA CONVERSION y clic botón derecho, luego clic en EDIT, se abre la ventana de la figura 14, se seleccionan las columnas deseadas, en este caso todas (id_columna y des_comuna). Esta estructura nace de la salida generada en "OLE DB Source" de SQL Commands. Seleccionadas, automáticamente se incorporan en el detalle de la ventaba "Data Conversion Transformation Editor". Los tipos de datos deben ser del mismo tipo que la tabla de destino que recibirá estos datos de la fuente. Debes seleccionar el tipo correspondiente en la columna "Data Type" y valida en la columna Length el largo del atributo fuente con el atributo del destino. Es decir des_comuna destino el largo debe ser mayor o igual al largo del atributo des_comuna origen.

El siguiente control recibirá los datos fuentes mapeados en Data Conversion. Selecciona desde ToolBox, en Data Flow Destination y OLE DB Destination y arrastra a la vista de diseño, colocar debajo de Data Conversion. En OLE DB Destination del objeto recién ubicado, clic botón derecho y clic en EDIT. Ver figura 15


Figura 15: Creando flujo dato destino.

Selecciona en "OLE DB Connection manager" la fuente destino, en este caso DMABC. Luego selecciona en modo de acceso de la tabla, Table or view - fat load. Se listarán todas las tablas asociadas DMABC. En este caso selecciono D_COMUNA. Esto permite que los datos de origen se almacenarán en el destino D_COMUNA. Luego clic en "MAPPINGS", ver figura 16.

Figura 16: Creando flujo dato destino.

Debe seleccionar de las columnas de entradas disponibles las nombradas que están registrada en DATA CONVERSION, en este caso, Copy of id_comuna y Copy_of des_comuna. Arrástrala en  cada una a las columnas destino donde corresponda. Esto proviene de la figura 14.

Terminado esto, ejecutemos la primera depuración a la  carga origen al destino, para ello hace clic en "Star Debugging". Al iniciar el proceso ETL, los controles deben cambiar de blanco a amarillo y quedar en color verde y si hay uno en rojo, hubo una incidencia. Ver figura 17.

Figura 17: Estado de la ejecución del flujo trabajo "Carga Comuna"


Realiza el mismo paso para todos los demás flujos de control. Como tienes la Carga Comuna creada, debes crear Carga CALLE y luego crear la relación, solo hace clic en la flecha verde y arrástrala hasta el control Carga CALLE, entre Carga COMUNA, y Carga CALLE y así sucesivamente para el resto.Ver figura 18


Figura 18: Se crean los flujos de control para cada tabla fuente.

Finalizado todo y validado que esté todo ok, ejecuta  todo el ETL Inicial desde "Star Debugging".
Cada control cambia de color pasando de blanco a amarillo (ejecutándose)  a verde (listo) y si uno queda en rojo es porque hubo una incidencia. Todos deben quedar en color verde.
Debes revisar tus datos destino versus los datos fuentes. Esto lo puedes hacer contando los registros fuentes y destino o en la depuración de cada control, te muestra la cantidad de registros procesados en el origen y luego los registros cargados en el destino. Te deben quedar con la misma cantidad.


Te dejo, los comandos SQL que me quedaron en el EtlInicial.dtsx origen correspondientes:

Origen de las calles: carga destino D_CALLE
SELECT   DISTINCT  enviodespacho.id_calle, cli.des_calle
FROM  CLI, ENVIODESPACHO
WHERE cli.id_cliente = enviodespacho.id_cliente

Origen Tipo Cliente: carga destino D_TPCLIENTE
SELECT  id_tp_cliente, des_tp_cliente
FROM tipo_cliente

Origen Producto: carga destino D_PRODUCTO
SELECT DISTINCT    id_pro, des_prod
FROM producto

Origen Fecha o Periodo o Tiempo: carga destino D_TIEMPO
SELECT DISTINCT rank() over (order by fecha_despacho desc) as Secuencia  
, fecha_despacho AS Fecha_Despacho, YEAR(fecha_despacho) AS ANO,
MONTH(fecha_despacho) as MES, DAY(fecha_despacho) as DIA
FROM         enviodespacho
GROUP BY fecha_despacho

En la siguiente figura en Data Conversion, como me ha quedado la transformación hacia la tabla destino D_TIEMPO.


Mira como me quedó en el OLE DB Destino en el mapeo.




Origen Despacho:  carga destino H_DESPACHO
SELECT p.id_pro,c.id_tp_cliente,f.id_tpo,e.id_comuna,e.id_calle,e.id_cliente,e.valor_cobrado,1 as cantidad
FROM CLI C, ENVIODESPACHO E, producto p, tipo_cliente t, envioproduco ep, 
     dmabc.dbo.d_tiempo f
where c.id_cliente = e.id_cliente and
      ep.codigo_cliente = e.id_cliente and
      e.fecha_despacho = ep.fecha_despacho and
      ep.id_pro = p.id_pro and
      c.id_tp_cliente = t.id_tp_cliente and
      e.fecha_despacho = f.fecha_despacho

Mira como me quedó en el OLE DB Destino en el mapeo.




Revisando los datos destino en la base de datos DMABC, han quedado registrado en forma correcta. En la siguiente figura, se observa desde SQL Server Management Studio el listado de datos de cada tabla.

-select * from d_calle
-select * from d_comuna
-select * from d_producto
-select * from d_tiempo
-select * from d_tpcliente
-select * from h_despacho


Lo que hice fue crear el ETLInicial, falta el ETLIncrementar, eso lo veremos en otro artículo de investigación. Acá tenemos el Inicial.

Resumen de lo visto en la Carga Inicial.

La preparación se realiza para cada fuente y consiste en:

-la extracción de datos
-limpieza de datos
-conversión de datos
-el almacenamiento de datos

Integración de datos consiste en:
-la reconciliación de datos provenientes de fuentes heterogéneas
-la generación de las relaciones

Las cargas Incrementales no deben afectar la integridad de los datos presentes.

Que nos queda para finalizar, crear nuestro CUBO OLAP.  Como hoy es sábado y el día está espectacular debo salir :)

Pronto subo el último artículo de este trabajo. Nos vemos... y gracias por sus comentarios y sugerencias.

Dudas a lpizarrofx@gmail.com


































































































































Entradas populares de este blog

Interpretación de Mediciones de Resistividad de Terreno por medio de Curva Patrón

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