EJERCICIO PRACTICO DE BI

INTRODUCCION

En este documento se busca realizar el análisis de una base de datos SAKILA  que se utilizara como prototipo para el desarrollo de este  caso práctico, al cual se le construirá o desarrollara una  solución de inteligencia de negocio.

De esta manera se deberá presentar una posible solución de acuerdo a la base de datos entregada, con el fin de poner en práctica todos los conceptos y herramientas estudiadas de inteligencia de negocio para el desarrollo del mismo.

Descarga la base de datos sakila AQUI

En este proyecto se implementaran herramientas de pentaho para poder llevar a cabo el desarrollo del mismo. Todas las herramientas las podemos encontrar en la siguiente pagina web: http://sourceforge.net

Estas herramientas son: 

• Consola de Pentaho = Creación Cuadros de mando "DashBoard" y Creación de informes
• kettle o Spoon = Para la creación de los ETL
• Mondrian de pentaho = Para la realización de los Cubos Olap

MODELO DE NEGOCIO

La empresa la cual se le desarrollara una solución de inteligencia de negocio será CLUBFIML S.A.

La empresa Sakila tiene como objetivo principal el alquiler de películas de videos, manejando una gran variedad de películas con el fin de satisfacer todas las necesidades de sus clientes.

La empresa Sakila tiene varias sucursales, donde sus clientes podrán encontrar las mejores películas del mercado distribuidas por, lenguaje, actor, título, categoría, de la mejor manera posible, para su fácil acceso dentro de sus tiendas.

El modelo de negocio de la empresa CLUBFILM S.A  se compone de los siguientes procesos según el análisis de la base de datos:

• Datos Clientes: Donde se almacenan los datos de los clientes que por lo menos una vez, han prestado los servicios ofrecidos en las tiendas de CLUBFILM S.A.
• Inventario: Donde se almacena todos los datos sobre los productos (películas) que se tienen en las sucursales, para la administración y el buen funcionamiento de las tiendas.
• Proceso del Negocio: En este proceso del negocio es donde se encuentran los datos del negocio.

DIAGRAMA ENTIDAD RELACION "SAKILA"

TABLAS TRANSACCIONALES	TABLAS NO-TRANSACCIONALES
Payment-Sales	Customer
Rental	address
Inventory	City
	country
	Staff
	store
	film
	Film_category
	Category
	Language
	Actor
	Film_actor
	Film_text

TABLAS DE HECHOS "MODELO EN ESTRELLA"

1. TABLA "HECHOS SALES"

TABLA DE HECHOS	TABLA DE DIMENSIONES
FACT_SALES	DIM_FILM
	DIM_CUSTOMER
	DATA_SALES
	DIM_STORE

2. TABLA "HECHOS INVENTORY"

TABLA DE HECHOS	TABLA DE DIMENSIONES
FACT_INVENTORY	DIM_FILM
	DIM_STORE
	DATE_INVENTORY

3. TABLA "HECHOS RENTALS"

TABLA DE HECHOS	TABLA DE DIMENSIONES
FACT_RENTALS	DIM_FILM
	DIM_STORE
	DIM_CUSTOMER
	DIM_STAFF
	DATE_RENTALS

MODELO DIMENSIONAL "DATAWAREHOUSE"

Hasta aquí tenemos muestro modelo dimensional pero se encuentra vació, este modelo tiene que diseñarse en la base de datos que mas nos guste manejar, en mi caso la diseñe en mysql al igual que la base de datos SAKILA. Ahora debemos crear los ETL´s para extraer los datos de la base de datos SAKILA y almacenarla a nuestro nuevo MODELO DIMENSIONAL.

DISEÑO Y CONSTRUCCION DE ETL EN SPOON

Para llevar a cabo el desarrollo de los ETL se contara con la herramienta de PENTAHO llamada SPOON daba para el desarrollo de este proyecto, se debe observar nuestro modelo dimension presentado anteriormente. Se debe desarrollar un ETL por cada dimensión , fact, y tabla de tiempo que tengamos. La construcción de los ETL nos permitirá extraer los datos de nuestra base de datos (Sakila) y cargarlos en muestro modelo dimensional desarrollando en el punto anterior.

En nuestra solución decidimos realizar una etl para cada tabla tiempo que posee las tabla de hechos.

ETL DIMENSIONES

A continuación explicaremos paso a paso la construcción de todos los ETL DIMENSIONES: Dim_films, Dim_customer, Dim_store, Dim_staff

Se relizaran los mismos pasos para cada uno de los ETL

Paso 1: Primero construimos una tabla de entrada en SPOON  la cual le asignamos el nombre y determinamos la conexión a la base de datos y en sql determinamos que tabla y que columnas deseamos que nos extraiga y nos muestre, damos PREVISUALIZAR para observar los datos.

Paso 2: Construimos una selecciona/renombrar valores  para determinar que valores me renombrar de la base de datos y el modelo dimensional para luego le los almacene en los respectivos campos.

Paso 3: por ultimo construimos un insertar/actualizar  para insertarle los datos extraídos de la base de datos al modelo dimensional.

Construcción de Dimensión de Tiempo:

Por solicitud de algunos de los lectores de mi Blog, me han muestre como construí las 3 dimensiones de tiempo.

Cuando construí este ejemplo por primera vez, mi experiencia en BI era poca 2 años despues me he dedicado por completo a QlikView y QlikSense pero aun tengo recuerdo de como trabajar con esta hermosa herramienta de Pentaho.

Es redundante tener 3 dimensiones de tiempo en nuestro DataWareHouse, no creería que seria mejor tener una sola dimensión de tiempo que contenga todas las posibles fechas de nuestras Fact Tables ? Esta seria una mejor que pueden implementar en sus soluciones.

Con el Query a continuacion podemos obtener todas esas fechas de nuestras Fact Tables, solo queria construir el campo incrementar para hacer único cada uno de los registros que contiene nuestra Dim_Tiempo y por el cual sera la relacion con las Fact:

Dim_Tiempo:

Select date(a.rental_date) AS Fecha, 

  year(a.rental_date) AS Año,

  quarter(a.rental_date) AS Trimestre,

  Month(a.rental_date) AS Mes,

  monthname(a.rental_date) AS MesNombre,

  Day(a.rental_date) AS Dia

  From sakila.rental a

Union All

Select 

       date(b.payment_date) AS Fecha, 

  year(b.payment_date) AS Año,

  quarter(b.payment_date) AS Trimestre,

  Month(b.payment_date) AS Mes,

  monthname(b.payment_date) AS MesNombre,

  Day(b.payment_date) AS Dia

  From sakila.payment b 

Union All

Select

  Date(c.last_update) AS Fecha,

  year(c.last_update) AS Año,

  quarter(c.last_update) AS Trimestre,

  Month(c.last_update) AS Mes,

  monthname(c.last_update) AS MesNombre,

       Day(c.last_update) AS Dia

  From sakila.inventory c;

Por motivos de tiempo les envio solo este Query, en pocos dias actualizare su implementacion con el Klette y su construccion a un dataWareHouse.

Espero les siga a todos 

ETL HECHOS

Para la construccion de los ETL de las tablas de hechos, realizaremos cuatro pasos, los mismos pasos son para cada uno de las tablas.

Tomaremos como ejemplo la tabla hechos rents

Paso 1: Primero contruimos una tabla de entrada en SPOON  la cual le asignamos el nombre y determinamos la conexión a la base de datos y en sql determinamos que tabla y que columnas desemoas que nos extraiga y nos muestre, damos PREVISUALIZAR para observar los datos.

Paso 2: Construimos una seleciona/renombra valores  para obtener el valor de la secuencia de la base de datos 

Paso 3: Construimos una seleciona/renombra valores  para determinarle que valores me renombra de la base de datos y el modelo dimensional para luego le los almance en los respectivos campos

Paso 4: por ultimo construimos un insertar/actualizar  para insertarle los datos extraidos de la base de datos al modelo dimensional. 

GERACION DE LOS INFORMES CON PENTAHO

Cargamos la plataforma de pentaho ejecutamos los archivos star-pac.bat y star-pentaho.bat y cargamos un navegador y en la direccion ingresamos http://localhost:8080/pentaho/Login, que nos abrirá la plataforma de pentaho e procedemos a ingresar el username y password que es joe y password respectivamente.

Ingresamos a la plataforma de pentaho y procedemos a crear un nuevo informe, debemos predeterminar la conexión con el modelo dimensional ya creado y al cual cargamos los datos con ayuda del Spoon.

A continuación determinamos que el tipo de datos que queremos mostrar en nuestro informe. Crearemos 3 informes.

1 Informe Film: En este informe mostraremos 4 campos que sean, id_film, name, category y actors que ya fueron importadas a nuestros modelos dimensional por medio de los etl.

Seleccionamos la tabla dim_film y selecionamos los campos y los colocamos en la tabla detalle y selecionamos el tipo de documento que deseamos el informe, en este caso sera PDF y damos en go

2. Informe Customer: En este informe mostraremos 5 campos que sean, id_customer, name, address customer, city y country customer que ya fueron importadas a nuestros modelos dimensional por medio de los etl.

Seleccionamos la tabla dim_cumstomer y seleccionamos los campos y los colocamos en la tabla detalle y seleccionamos el tipo de documento que deseamos el informe, en este caso sera PDF y damos en go

CREACIÓN DE CUBOS OLAP CON LA HERRAMIENTA MONDRIAN

DESCARGAR GUIA PARA ELABORAR CUBOS EN MONDIAN AQUI

CUBO "FACT_INVENTORY"

Paso 1: Se inicializa creando un esquema y luego un cubo “add Cube” el cual le seleccionado la fact_inventory y así será como se llamara

Paso 2: Insertar 3 dimensiones, film, store y date inventory para cada una de las dimensiones en el campo foreaignkey se seleccionada cada una de las llaves foráneas de cada dimensión.

Paso 3: Luego ingresamos los campos que vamos a medir en este caso replacement_cost y Price_film, en el campo columna de la tabla fact_invetory seleccionados la columna replacement_cost, lo mismo realizamos con el campo a medir Price_Film, en el campo columna de la tabla fact_inventory seleccionamos la columna Price_FilM

Con esto nuestro primer cubo esta terminado solo falta publicarlo en pentaho.

Para este ejemplo se realizaron 3 cubos pero solo se mostró como se construye 1. 

PUBLICACIÓN DE CUBOS EN PENTAHO

Demostraremos la publicación de un solo cubo, ya que el procedimiento es igual para todos los cubos.

Publicaremos el cubo “Fact_sales”

click en publish

En este caso la url es el link de la consola de pentaho, el publish password es el password que determinamos al comienzo de la instalación de la consola de pentaho. Para determinar este password realizamos lo siguiente.

Vamos a la carpeta biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions\system y seleccionamos el archivo con el nombre de publisher_config y abrirmo con el block de notas

Aquí escribimos la palabra que deseamos como password, en este caso escogimos la palabra password>password<. Le damos guardar, vamos a la consola de pentaho y actualizamos

Luego de haber realizado esto, y poder haber llenado todos los campos de la publicación procedemos a la consola de pentaho y vamos a configuración del sistema.

Ahora solo nos falta ir he intentar publicar, escogemos una carpeta donde queramos que se publiquen en el pentaho y le damos en publicar. Nos debe de salir esto.