Download - Bussines Inteligence
3-12-2015
INFORME FINAL BI.
Julio Montero Diego Soto Claudio Ponce Rodrigo Villalobos
Tabla de contenidoIntroducción:..........................................................................................................................1
Introducción a Adventure Works Cycles...............................................................................2
Indicadores y perspectivas:...................................................................................................4
Análisis de los OLTP:............................................................................................................4
Unidades producidas.........................................................................................................4
Costo de producción..........................................................................................................4
Establecer correspondencia..................................................................................................5
Producción:........................................................................................................................5
Nivel de granulidad Producción:...........................................................................................7
Modelo conceptual................................................................................................................8
Modelo conceptual ampliado................................................................................................9
Tabla de Hechos Producción..............................................................................................10
Modelo lógico......................................................................................................................11
Herramientas de software utilizadas en el proyecto...........................................................12
SQL Server Management Studio....................................................................................12
Sql server data tolos........................................................................................................12
Microsoft Excel................................................................................................................12
Datawarehouse-Datamart:..............................................................................................12
Modelo Relacional:..........................................................................................................14
Proceso ETL.......................................................................................................................15
Query utilizada para llenar con datos de la tabla Dim_Territorio.................................15
Query utilizada para llenar con datos de la tabla Dim_Oferta.....................................15
Query utilizada para llenar con datos de la tabla Dim_Tiempo...................................15
Query utilizada para llenar con datos de la tabla Hch_Produccion.............................15
Excel:..................................................................................................................................16
Introducción:En el desarrollo de este proyecto se describirá el rubro de la empresa que nos
facilitará sus datos, la Base de datos Transaccional y su diccionario de datos.
También se conocerán los escenarios más importantes de la empresa. Junto a lo anterior
se desarrollará una Base de datos multidimensional, la cual permite almacenar
información a través de tablas de hechos y de dimensiones. Una base de datos
multidimensional nos permite tener acceso flexible a los datos previamente definidos, para
explorar y analizar sus relaciones y resultados.
Introducción a Adventure Works Cycles
Para poder intervenir en la empresa de manera positiva, es necesario conocer la
misma e interiorizarse en sus procesos:
“Adventure Works Cycles, es una gran empresa de fabricación multinacional. La
empresa fabrica y vende bicicletas de metal y de metal compuesto en los mercados de
Norteamérica, Europa y Asia. Si bien su sede central de operaciones se encuentra en
Bothell, Washington, con 290 empleados, en toda su base de mercado tiene distribuidos
varios equipos regionales de ventas.”
Indagando un poco más en la empresa se ha podido identificar que esta no solo
vende bicicletas, sino que también fabrica componentes y vende piezas para bicicletas, en
sus procesos más importantes se han podido identificar 4 que son los pilares de la
empresa:
1. Ventas y marketing: Como empresa de fabricación de bicicletas, Adventure
Works Cycles tiene dos tipos de cliente:
Individuos: clientes que compran productos de la tienda en línea de
Adventure Works Cycles, para uso personal.
Tiendas: tiendas de venta al por menor o al por mayor que compran a
los representantes de ventas de Adventure Works Cycles productos
para la reventa.
2. Producto: En este campo Adventure Works Cycles tiene las cuatro líneas
de productos (Bicicletas, componentes de bicicletas, equipos para
bicicletas y accesorios para bicicletas) a disposición de los clientes.
3. Compra y proveedor: En Adventure Works Cycles, el departamento de
compras adquiere las materias primas y las piezas que se utilizan para
fabricar las bicicletas de Adventure Works Cycles. Adventure Works Cycles
también adquiere productos para la reventa, como equipamiento para
bicicletas y complementos, como botellas para el agua y bombas de aire.
4. Fabricación: Como se mencionó anteriormente Adventure Works Cycles
también fabrica sus propios componentes para las bicicletas.
Entrevistando a los altos directivos de Adventure Works Cycles, se llegó a la
conclusión de que el proceso más importante de la empresa es el escenario de
“producción”
Se les preguntó cuáles eran los indicadores que representan de mejor modo el
proceso de producción y qué sería exactamente lo que se desea analizar del mismo. La
respuesta fue saber con certeza las unidades producidas y los costos de producción en
un tiempo específico.
Indicadores y perspectivas:
Costo de producción
por producto, por territorio de almacenamiento, por oferta por tiempo
Unidades producidas
por producto, por territorio de almacenamiento, por oferta por tiempo
Análisis de los OLTP:
Unidades producidas
Hechos: Unidades producidas Función: SUM
Definición: el indicador “Unidades Producidas” representa la sumatoria de los productos que se han fabricados según cada perspectiva (Producto, territorio de almacenamiento, tiempo, oferta).
Costo de producción
Hechos: Costos de Producción Función: SUM
Definición: el Indicador “Costo de Producción” representa los Costos relacionados con los materiales de elaboración de los productos que se han fabricado según cada perspectiva (Producto, territorio de almacenamiento, tiempo, oferta)
Establecer correspondencia
Producción:
Nivel de granulidad Producción:
Producto: Corresponde al campo Name de la tabla Product, ya que hace mención al identificador único de cada producto que se desee conocer.
Tiempo: Hace referencia al campo StartDate de la tabla ProductCostHistory y hace referencia al inicio de los costos de los productos.
Territorio de almacenamiento: Corresponde al campo Name de la tabla Location corresponde al nombre del local, donde están almacenados los productos.
Oferta: Corresponde al campo DiscountPct de la tabla SpecialOffer hace mención al producto en específico que posee el descuento.
Unidades producidas: Corresponde al campo StockedQty de WorkOrder
Costo de producción: Corresponde al campo StandarCoast de ProductCostHistory
Modelo conceptual
Como puede apreciarse en la figura, el modelo conceptual permite de un solo
vistazo y sin poseer demasiados conocimientos previos, comprender cuáles serán los
resultados que se obtendrán, cuáles serán las variables que se utilizarán para analizarlos
y cuál es la relación que existe entre ellos.
Modelo conceptual ampliadoEn este paso, y con el fin de graficar los resultados obtenidos en los pasos
anteriores, se ampliará el modelo conceptual, colocando bajo cada perspectiva los
campos seleccionados y bajo cada indicador su respectiva fórmula de cálculo.
Gráficamente
Tabla de Hechos Producción
Modelo lógico
La creación de este modelo tiene las siguientes finalidades:
Ejemplificar la creación de cubos multidimensionales.
Propiciar la correcta distinción entre hechos de una tabla de hechos e indicadores
de un cubo.
Propiciar la correcta distinción entre campos de una tabla de dimensión y atributos
de un cubo.
Herramientas de software utilizadas en el proyecto
SQL Server Management Studio
SQL Server Management Studio (SSMS) proporciona herramientas para configurar,
gestionar y administrar las instancias de Microsoft SQL Server, que reúne a una
amplia gama de herramientas de diseño gráfico y visual que simplifican el trabajo de
con SQL Server.
En nuestro proyecto, SQL Server Management Studio fue utilizado para crear la base
de datos multidimensional DW_AW. Además nos sirvió para ir probando las consultas
SQL que se iban desarrollando en el transcurso del proyecto.
Sql server data tolos
Esta herramienta de SQL la hemos utilizado para crear el proyecto de integración ETL
“Proyecto de Integration Services1” en el cual se llevó a cabo la carga de dimensiones
y la carga de Hechos.
Por otro lado creamos el origen de datos del cubo, las vistas del origen de datos y el
cubo junto a sus dimensiones.
Microsoft Excel
En la planilla Excel tomamos los datos del cubo multidimensional para presentarlos de
una forma más ordenada que en la base de datos.
Datawarehouse-Datamart:
Nombre de Base de Datos: DW_AW
Usuario Propietario: Localhost
Diccionario de datos:
Tabla: Dim_Tiempo
Columna Tipo Nulo Enlaces a Tabla
Comentarios
Id_fecha int no Identificador de fecha
Fecha_completa Datetime si Fecha completa(año mes dia)
Anio Int año
Mes Int Mes en numero
Nombre_mes Varchar(50)
Nombre del mes
Tabla: Dim_Territorio
Columna Tipo Nulo Enlaces a Tabla
Comentarios
Id_territorio int no Identificador de fecha
Nombre_territorio varchar(50) no Nombre del territorio
Tabla: Dim_Producto
Columna Tipo Nulo Enlaces a Tabla
Comentarios
Id_producto Int no Identificador producto
Nombre_producto varchar(50)
no Nombre del producto
Tabla: Dim_Oferta
Columna Tipo Nulo Enlaces a Tabla
Comentarios
Id_Oferta int no Identificador oferta
Descripción_oferta varchar(50)
no Descripción de la oferta
Tabla: Dim_Tiempo
Columna Tipo Nulo Enlaces a Tabla
Comentarios
Id_fecha int no
Fecha_completa varchar(20) no
Modelo Relacional:
Proceso ETL
Una vez construido el modelo lógico, se deberá proceder a poblarlo con datos, utilizando
técnicas de limpieza y calidad de datos, procesos ETL, etc.; luego se definirán las reglas y
políticas para su respectiva actualización, así como también los procesos que la llevarán a
cabo. • Debemos poblar el modelo de datos que hemos construido anteriormente. Lo cual
conlleva realizar tareas básicas, tales como limpieza de datos, calidad de datos, procesos
ETL, etc. • Primero se cargarán los datos de las dimensiones y luego los de las tablas de
hechos, teniendo en cuenta siempre, la correcta correspondencia entre cada elemento. •
Concretamente, en este paso se deberá registrar en detalle las acciones llevadas a cabo.
Por ejemplo, es muy común que sistemas ETL trabajen con "pasos" y "relaciones", en
donde cada "paso" realiza una tarea en particular del proceso ETL y cada "relación" indica
hacia donde debe dirigirse el flujo de datos. En este caso lo que se debe hacer es explicar
que hace el proceso en general y luego que hace cada "paso" y/o "relación".
Query utilizada para llenar con datos de la tabla Dim_Territorioselect Production.Location.LocationID, Production.Location.Name from Production.Locationorder by LocationID
Query utilizada para llenar con datos de la tabla Dim_Ofertaselect Sales.SpecialOffer.SpecialOfferID, Sales.SpecialOffer.Description from Sales.SpecialOfferorder by SpecialOfferIDQuery utilizada para llenar con datos de la tabla Dim_Productoselect Production.Product.ProductID, Production.Product.Name from Production.Productorder by Production.Product.ProductID
Query utilizada para llenar con datos de la tabla Dim_Tiemposelect distinct replace(convert(date, ProductCostHistory.StartDate),'-','')fecha_completa, ProductCostHistory.StartDate,datepart(yyyy, ProductCostHistory.StartDate) anio, datepart(month, ProductCostHistory.StartDate)mes,datename(month, ProductCostHistory.StartDate)nombre_mes
from production.ProductCostHistory
Query utilizada para llenar con datos de la tabla Hch_Produccionselect p.ProductID id_producto, l.LocationID id_territorio, o.SpecialOfferID id_oferta, replace(convert(date, c.StartDate),'-','') id_fecha,sum(w.StockedQty) cantidad_total,sum(w.StockedQty * c.StandardCost) costo_totalfrom (((Production.Product p inner join Production.WorkOrder w on p.ProductID=w.ProductID) inner join(Production.ProductInventory i inner join Production.Location l on i.LocationID=l.LocationID)on p.ProductID =i.ProductID) inner join Production.ProductCostHistory c on p.ProductID=c.ProductID) inner join(Sales.SpecialOfferProduct s inner join Sales.SpecialOffer o on s.SpecialOfferID =o.SpecialOfferID ) on p.ProductID=s.ProductID
group by p.ProductID, l.LocationID, o.SpecialOfferID, c.StartDateorder by c.StartDate, p.ProductID, l.LocationID, o.SpecialOfferID
Excel:
Según la siguiente tabla se muestran datos obtenidos directamente desde una
planilla Excel, la cual está relacionada con el cubo multidimensional, el cual alberga todos
los datos necesarios para saber la cantidad total de productos fabricados.
En la columna “cantidad total” se muestra un monto por 885791,128 que
corresponde al producto HL Mountain Frame - Black, 38 que fue fabricado en el mes de
julio del año 2005 en la localidad de “Debur and Polish”