MDULO DE LABORATORIO DE INTELIGENCIA DE NEGOCIOS
FACULTAD DE INGENIERA ESCUELA DE INGENIERA DE SISTEMAS
1
Laboratorio 1: Diseando un Sistema de Data
Warehousing
A. Diseando el Esquema Estrella
Objetivos
Disear un Modelo Multidimensional usando en Esquema Estrella
Identificar medidas de una Fact Table
Identificar niveles de jerarqua en una dimensin
Escenario
En el Laboratorio anterior se explic que la Base de Datos NorthWind soportar el sistema de ordenes de
pedido que contiene las sgts. Tablas
Orders
Order details
Products
Categories
Suppliers
Shippers
Employees
Customers El Administrador de NorthWind desea incrementar su habilidad para analizar datos enfocando las
tendencias que le permitan tomar decisiones sobre las ventas del negocio
Objetivos del Negocio
Determinar cmo sus productos son vendidos en las distintas regiones geogrficas que conforman el mbito de la empresa. Evaluar los lugares con las ventas ms altas y aquellas en la que las ventas son
menores.
Proceso de Negocios
El detalle del proceso de negocios fue obtenido despus de realizar una serie de
entrevistas con varios empleados, analiar los documentos y seguir el flujo de un pedido desde la solicitud hasta la entega de pedidos al cliente.
2
Construccin de un Data WareHouse
Requerimientos del Data Mart
Informacin acerca de las ventas Informacin de las compras de los clientes
Informacin acerca de los productos que ha sido vendidos
Informacin acerca de los empleados que atendieron los pedidos Datos acerca de la entrega de productos
Medidas
Cantidad de productos vendidos Descuentos otorgados
Total de la producto vendido (lnea total del item)
Costo de flete por enviar el producto al cliente Alguna de las medidas son datos precalculados y que se realizarn al momento de hacer la transformacin de los datos. Por ejemplo
Medidas Mtodo de clculo
Lnea total del item PrecioUnitario * Cantidad
Costo de Flete (((Costo Embarque/ sum(cantidad))* cantidad
La informacin puede ser observada construyendo el Diagrama de la Base de Datos NorthWind en el SQL Server.
Identificando el Grain, Dimensiones, Facts y Jerarquas
1. Qu tablas son apropiadas para tablas dimensin?
________________________________________________________________
2. Qu tabla o tablas sern dimensiones compartidas? _________________________________________________________________
3. Con qu tipo de ndice identificara a cada dimensin?
_________________________________________________________________
4. Qu tabla de la Base de Datos NorthWind es la ms representiva a la Fact Table?
_________________________________________________________________
5. Qu medidas y calves forneas son necesarias a incluir en la Fact Table _________________________________________________________________
6. Qu tabla y columnas contienen informacin de la fecha requerida en la Orden? _________________________________________________________________ 7. Cmo puede ser til la fecha de embarque para analizar datos en el Data Mart? Cmo se denomina a este tipo de informacin? Note que esta informacin no es una dimensin ni es una medida.
3
Construccin de un Data WareHouse
B. Implementando el Esquema Estrella Objetivos
Crear la Fact Table y las tablas dimensin
Establecer relaciones
Ejercicio 01. Creando la Base de Datos Multidimensional: NorthWind_Mart Iniciando el Microsoft SQL Server Management Studio.
Cargar desde Programas-Microsoft SQL Server 2008 y luego haga click en Microsoft
SQL Server Management Studio.
Clic en : Connect
Con lo que se activa la siguiente interfaz
4
Construccin de un Data WareHouse
Creando la Base de Datos.
Cargar desde Inicio - Programas-Microsoft SQL Server 2008 y luego haga click en Microsoft SQL Server Management Studio..
Con lo que aparece la sgte interfaz:
Ubicarse sobre Databases, clic derecho Nueva BaseDatos: NorthWind_Mart, clic
Aceptar.
Expandir la BD NorhWind_Mart y ubicarse sobre Diagrams, clic derecho New Diagram y preparar el sgte modelo
5
Construccin de un Data WareHouse
Ejercicio 03. Identificando Dimensiones, hechos y Jerarquas
Durante una entrevista con Alejandro, el Analista de Ventas se nos proporcion el siguiente cuadro resumen que usan mensualmente para conocer las cantidades vendidas de una determinada sucursal (Krispy Foods).
6
Construccin de un Data WareHouse
Identificar dimensiones 1. Visualice la clasificacin del cuadro e identifiqu las cabeceras de las columnas con menor
nivel.
2. Visualice la clasificacin del cuadro e identifiqu las cabeceras de las filas con menor nivel. 3. Visualice el ttulo del reporte e identifique el nivel ms bajo
Identificar medidas
4. Cul es la interseccin entre las columnas y filas de menor nivel.
Identificar Jerarquas
5. Identifique los acumulados a nivel de Columna
6. Identifique los acumulados a nivel de Fila
7. Identifique alguna clasificacin en el ttulo
7
Construccin de un Data WareHouse
Laboratorio 2: Poblando un Data Warehouse Objetivos
Familiarizarse con una Base de Datos OnLine Transaction Process (OLTP)
Ejecutando Simples bsquedas a los datos en un Online Analytical Processing (OLAP)
Ejercicio 01.
Definiendo Flujos de Trabajo
Con el DTS se puede definir un flujo de trabajo que controla la ejecucin secuancial
paso a paso. Controlar el flujo lgico y determinando las precedencias respectivas, priozando tareas a desarrollar.
Definiendo Pasos de Transformacin
Un paso define una unidad de trabajo que es ejecutada como parte de un proceso de transformacin. Un paso puede:
Ejecutar una orden SQL
Mover y transformar datos heterogneos Ejecutar Jscript, VBScript. Estos scripts pueden ejecutar cualquier operacin con su lengiaje nativo.
Recuperar y ejecutar otros DTS packages. Por ejemplo Ud. Puede preparar un DTS package para sumarizar las ventas al contado y al crdito resumidas por mes, vendedor, etc. De diferentes tablas y ponerlas un una sola tabla.
Pasos, Tareas y Restricciones de precedencia:
Un paquete se forma a partir de conexiones de datos (orgenes y destinos) y tareas usadas para ejecutar estas conexiones. La accin de ejecutar una tarea es controlada por un paso:
Notemos los sgts pasos y restricciones de precedencia en el DTS Designer
8
Construccin de un Data WareHouse
Las restricciones de precedencia son representadas por una lnea azl (Cuando se ha completado) Verde (ejecutada) o Rojo (Error)
Veamos el sgte. Diagrama:
Tipos de Restriccin de Precedencia
Los tipos de restricciones de precedencia que seleccione determinar los requerimientos para ejecutar cada paso: SUCCESS indica que el paso origen debe completarse satisfactoriamente antes de
que el paso de destino empiece a ejecutarse.
9
Construccin de un Data WareHouse
FAILURE indica que el Paso origen debe completarse con una indicacin de error antes de ejecutar el paso destino. COMPLETION: indica que el paso origen debe smplemente ejecutarse (con FAILURE
O SUCCESS) antes de que el paso destino se ejecute.
Ejemplo de formas de flujo de trabajo:
Un paquete con pasos que se ejecutan en paralelo y secuencialmente
Poblamiento Utilizando el Transact-SQL
Ejemplo 1. Copiando informacin y transformando datos. Mismo Orgen
Orgen : MS SQL Server : NorthWind.dbo.Employees
Destino: MS SQL Server: NorthWind_Mart.dbo.Employee_Dim
Sin verificar datos del destino.
Suponga que desea copiar informacin desde NorthWind hacia NorthWind_Mart de la tabla Employees
USE NorthWind INSERT employee_dim
(EmployeeID, EmployeeName, HireDate) SELECT EmployeeID, LastName + ','+ FirstName AS EmployeeName, HireDate
FROM NorthWind.dbo.Employees Ejecute nuevamente esta ltima instruccin y luego consulte la informacin de la tabla dimensin: Employee_Dim
Qu observa en los datos ?
10
Construccin de un Data WareHouse
Eliminando datos de la Tabla Dimensin: Employee_Dim
DELETE FROM Employee_Dim
Copiando informacin y transformando datos. Verificando datos del destino
Utilizando el mtodo de limpieza total DELETE FROM Employee_Dim
INSERT employee_dim
(EmployeeID, EmployeeName, HireDate)
SELECT EmployeeID, LastName + ','+ FirstName AS EmployeeName, HireDate FROM NorthWind.dbo.Employees WHERE EmployeeID
Utilizando el mtodo incremental INSERT employee_dim
(EmployeeID, EmployeeName, HireDate)
SELECT EmployeeID, LastName + ','+ FirstName AS EmployeeName, HireDate FROM NorthWind.dbo.Employees WHERE EmployeeID
NOT IN (SELECT EmployeeID FROM employee_dim)
Ejemplo de Ejecucin de un Package
Creacin de una DTS Package Creando un Paquete y Adicionando Conexiones
Activada d
Ingresand o al SSIS
Pasos a Seguir
1. Iniciar el SQL Server Business Intelligence Development Studio y desde el Menu la opcion : File New Project con lo que aparece la interfaz siguiente
11
Construccin de un Data WareHouse
Activada d
Pasos a Seguir
2. Elija en a. Tipo Proyecto: Business Intelligent Projects b. Templates: Integration Services Project
c. Name: Doblamiento General NorthWind_Mart d. Browse: ubique la direccion deseada
e. Ok
Ver la sgte interfaz
12
Construccin de un Data WareHouse
Activada d
Pasos a Seguir
3. Renombrar el Paquete como: PoblamientoGeneral Recuerde que un proyecto es una coleccin de Paquetes, DataSources, DataSource Views y otros elementos que podrn ser usados mientras desarrolle la construccin del paquete
Creando la conexin de datos ORIGEN
1. Clic derecho sobre DataSource : New con lo que se activa:
13
Construccin de un Data WareHouse
Activada d
Pasos a Seguir
2. Haga clic en New y digite los sgts datos
14
Construccin de un Data WareHouse
Activada d
Pasos a Seguir
3. Ok
4. Next 5. Finish
Creando la conexin destino de datos
1. Proceda como en paso anterior, pero esta vez establesca la conexin con la BD NorthWind_Mart
15
Construccin de un Data WareHouse
Activada d
Agregand o
una tarea de ejecucion
Pasos a Seguir
1. Asegurarse de estar ubicado en Control Flow 2. Desde ToolBox arrastre Execute SQL Task luego clic derecho Edit ubique la propiedad Connection y seleccione: localhost.NorthWind_Mart
3. Ubique la propiedad SQL Statment clic en () y digite lo sgte:
DELETE Sales_Fact DELETE Employee_Dim DBCC CHECKIDENT('Employee_Dim', reseed,0) DELETE Time_Dim DBCC CHECKIDENT('Time_Dim', reseed,0) DELETE Customer_Dim
DBCC CHECKIDENT('Customer_Dim', reseed,0) DELETE Shipper_Dim DBCC CHECKIDENT('Shipper_Dim', reseed,0) DELETE Product_Dim
DBCC CHECKIDENT('Product_Dim', reseed,0)
16
Construccin de un Data WareHouse
Activada d
Pasos a Seguir
4. Ok
5. ok
Poblando Dimension Transportistas
Actividad Pasos a Seguir
Creando el
Flujo de Datos
1. Desde ToolBox arrastre DataFlowTask
2. Clic derecho: Rename y digite: Poblando Transportistas 3. Clic en Execute SQL Tasks, clic en la flecha verde y arrastre hasta Poblando Transportistas
Aspecto inicial
17
Construccin de un Data WareHouse
Actividad Pasos a Seguir
Estableciendo el
origen
1. Doble clic sobre Poblando Transportistas 2. En ToolBox en DataFlowSources arrastre OLEDB DataSource y haga doble clic
3. Desde ConnectionManager a. OLE DB Connection Manager : localhost.NorthWind b. Data Access: Table o View
c. Name: Shippers Estableciendo el Destino
Grabando y Ejecutando el Paquete
Verificando
Poblamiento
4. s
1. En ToolBox en DataFlowDestinations , arrastre OLEDB
DataDestionation 2. Clic en OLE DB DataSource, clic en la flecha verde y arrastre hacia OLEDB Data Destination
3. Doble Clic en OLEDB DataDestination. 4. Desde ConnectionManager> a. OLE DB Connection Manager: localhost.NorthWind_Mart b. Data Access; Table
c. Name: Shipper_Dim
Clic en Mappings: y trate de llegar al sgte esquema 1. Grabar
2. Presione F5 para ejecutar
1. Cargue el SQL Server Management Studio
2. Clic en New Quero
3. Seleccione la BD: NorthWind_Mart 4. Digite: SELECT * FROM Shipper_Dim
Poblando Dimension Clientes
Actividad Pasos a Seguir
Creando el
Flujo de Datos
1. Desde ToolBox arrastre DataFlowTask
2. Clic derecho: Rename y digite: Poblando Clientes
3. Clic en Execute SQL Tasks, clic en la flecha verde y arrastre hasta
Poblando Clientes Estableciendo el origen
1. Doble clic sobre Poblando Cliente 2. En ToolBox en DataFlowSources arrastre OLEDB DataSource y haga doble clic
3. Desde ConnectionManager a. OLE DB Connection Manager : localhost.NorthWind b. Data Access: SQL Command
18
Construccin de un Data WareHouse
Actividad Pasos a Seguir
c. SQL CommandText: SELECT *, Regioncita = ISNULL(Region, 'Otros') FROM customers
Estableciendo el Destino
1. En ToolBox en DataFlowDestinations , arrastre OLEDB DataDestionation
2. Clic en OLE DB DataSource, clic en la flecha verde y arrastre hacia OLEDB Data Destination
3. Doble Clic en OLEDB DataDestination.
4. Desde ConnectionManager> a. OLE DB Connection Manager: localhost.NorthWind_Mart b. Data Access; Table
c. Name: Customer_Dim Clic en Mappings: y trate de llegar al sgte esquema
Asegurarse de que en Destino Region sea a partir de Regioncita
Grabando y Ejecutando el Paquete
Verificando
Poblamiento
5. Grabar
2. Presione F5 para ejecutar
5. Cargue el SQL Server Management Studio
6. Clic en New Query
7. Seleccione la BD: NorthWind_Mart 8. Digite: SELECT * FROM Customer_Dim
Poblando Dimension Tiempo
Actividad Pasos a Seguir
Creando el
Flujo de Datos
1. Desde ToolBox arrastre DataFlowTask
4. Clic derecho: Rename y digite: Poblando Tiempo 5. Clic en Execute SQL Tasks, clic en la flecha verde y arrastre hasta Poblando Tiempo
Estableciendo el origen
4. Doble clic sobre Poblando Tiempo 5. En ToolBox en DataFlowSources arrastre OLEDB DataSource y haga doble clic
19
Construccin de un Data WareHouse
Actividad Pasos a Seguir
6. Desde ConnectionManager a. OLE DB Connection Manager : localhost.NorthWind b. Data Access: SQL Command
c. SQL CommandText:
SELECT DISTINCT S.ShippedDate AS TheDate, DateName(dw, S.ShippedDate) AS DayOfWeek,
DatePart(mm, S.ShippedDate) AS [Month], DatePart(yy, S.ShippedDate) AS [Year], DatePart(qq, S.ShippedDate) AS [Quarter],DatePart(dy, S.ShippedDate) AS DayOfYear,
DateName(month, S.ShippedDate) + '_' + DateName(year,S.ShippedDate) AS YearMonth, DatePart(wk, S.ShippedDate) AS WeekOfYear
FROM Orders S WHERE S.ShippedDate IS NOT NULL
Estableciendo el Destino
Grabando y Ejecutando el Paquete
Verificando
Poblamiento
6. En ToolBox en DataFlowDestinations , arrastre OLEDB DataDestionation
7. Clic en OLE DB DataSource, clic en la flecha verde y arrastre hacia OLEDB Data Destination
8. Doble Clic en OLEDB DataDestination.
9. Desde ConnectionManager> a. OLE DB Connection Manager: localhost.NorthWind_Mart b. Data Access; Table
c. Name: time_Dim Clic en Mappings: y trate de llegar al sgte esquema
1. Grabar
2. Presione F5 para ejecutar
1. Cargue el SQL Server Management Studio
2. Clic en New Query
3. Seleccione la BD: NorthWind_Mart 4. Digite: SELECT * FROM Time_Dim
20
Construccin de un Data WareHouse
Poblando Dimension Producto
Actividad Pasos a Seguir
Creando el Flujo de Datos
1. Desde ToolBox arrastre DataFlowTask 2 Clic derecho: Rename y digite: Poblando Producto
3. Clic en Execute SQL Tasks, clic en la flecha verde y arrastre hasta Poblando Producto
Estableciendo el origen
1. Doble clic sobre Poblando Tiempo 2. En ToolBox en DataFlowSources arrastre OLEDB DataSource y haga doble clic
3. Desde ConnectionManager a. OLE DB Connection Manager : localhost.NorthWind b. Data Access: SQL Command
c. Clic en el boton: Build Query
d. Clic derecho: Add Table. Seleccione: Products, Suppliers y
Categories
e. Clic : Add
f. Clic:Close g. Seleccione las columnas de acuerdo a lo mostrado a continuacin.
h. Clic ok i. Clic ok
21
Construccin de un Data WareHouse
Actividad Pasos a Seguir
Estableciendo el Destino
Grabando y Ejecutando el Paquete
Verificando
Poblamiento
1. En ToolBox en DataFlowDestinations , arrastre OLEDB DataDestionation
2. Clic en OLE DB DataSource, clic en la flecha verde y arrastre hacia
OLEDB Data Destination 3. Doble Clic en OLEDB DataDestination.
4. Desde ConnectionManager> a. OLE DB Connection Manager: localhost.NorthWind_Mart b. Data Access; Table
c. Name: Product_Dim Clic en Mappings: y trate de llegar al sgte esquema
1. Grabar
2. Presione F5 para ejecutar
1. Cargue el SQL Server Management Studio
2. Clic en New Query
3. Seleccione la BD: NorthWind_Mart
4. Digite: SELECT * FROM Product_Dim Poblando Dimensin Empleados
Actividad Pasos a Seguir Creando el Flujo de Datos
1. Desde ToolBox arrastre DataFlowTask
2. Clic derecho: Rename y digite: Poblando Empleados 3. Clic en Execute SQL Tasks, clic en la flecha verde y arrastre hasta Poblando Empleados
Estableciendo el origen
1. Doble clic sobre Poblando Tiempo 2. En ToolBox en DataFlowSources arrastre OLEDB DataSource y haga doble clic
3. Desde ConnectionManager a. OLE DB Connection Manager : localhost.NorthWind b. Data Access: SQL Command
c. SQL CommandText:
select * , FirstName +',' + LastName as EmployeeName
22
Construccin de un Data WareHouse
Actividad Pasos a Seguir
from employees
Estableciendo el Destino
Grabando y Ejecutando el Paquete
Verificando
Poblamiento
1. En ToolBox en DataFlowDestinations , arrastre OLEDB DataDestionation
2. Clic en OLE DB DataSource, clic en la flecha verde y arrastre hacia OLEDB Data Destination
3. Doble Clic en OLEDB DataDestination. 4. Desde ConnectionManager> a. OLE DB Connection Manager: localhost.NorthWind_Mart b. Data Access; Table
c. Name: Employee_Dim
Clic en Mappings: y trate de llegar al sgte esquema
1. Grabar
2. Presione F5 para ejecutar
1. Cargue el SQL Server Management Studio
2. Clic en New Query
3. Seleccione la BD: NorthWind_Mart 4. Digite: SELECT * FROM Employee_Dim
5. Execute Poblando Tabla Hecho
Actividad Pasos a Seguir
Creando el
Flujo de Datos
1. Ubicarse en Control Flow
2. Desde ToolBox arrastre DataFlowTask 3. Clic derecho: Rename y digite: Poblando Tabla Hecho 4. Clic en Poblando Transportistas, clic en la flecha verde y arrastre hasta Poblando Tabla Hecho. 5. Clic en Poblando Clientes, clic en la flecha verde y arrastre hasta Poblando Tabla Hecho.
6. Clic en Poblando Tiempo, clic en la flecha verde y arrastre hasta Poblando Tabla Hecho.
7. Clic en Poblando Productos, clic en la flecha verde y arrastre
23
Construccin de un Data WareHouse
Actividad Pasos a Seguir
hasta Poblando Tabla Hecho. 8. Clic en Poblando Empleados, clic en la flecha verde y arrastre hasta Poblando Tabla Hecho.
Estableciendo el origen
1. Doble clic sobre Poblando Tabla Hecho 2. En ToolBox en DataFlowSources arrastre OLEDB DataSource y haga doble clic
3. Desde ConnectionManager a. OLE DB Connection Manager : localhost.NorthWind b. Data Access: SQL Command
c. SQL CommandText: SELECT Northwind_Mart.dbo.Time_Dim.TimeKey,
Northwind_Mart.dbo.Customer_Dim.CustomerKey, Northwind_Mart.dbo.Shipper_Dim.ShipperKey, Northwind_Mart.dbo.Product_Dim.ProductKey, Northwind_Mart.dbo.Employee_Dim.EmployeeKey,
Northwind.dbo.Orders.RequiredDate, Orders.Freight * [Order Details].Quantity /
(SELECT SUM(Quantity) FROM [Order Details] od
WHERE od.OrderID = Orders.OrderID) AS LineItemFreight, [Order Details].UnitPrice * [Order Details].Quantity AS LineItemTotal,
[Order Details].Quantity AS LineItemQuantity, [Order Details].Discount * [Order Details].UnitPrice *
[Order Details].Quantity AS LineItemDiscount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID INNER JOIN Northwind_Mart.dbo.Product_Dim ON [Order Details].ProductID = Northwind_Mart.dbo.Product_Dim.ProductID INNER JOIN Northwind_Mart.dbo.Customer_Dim
ON Orders.CustomerID = Northwind_Mart.dbo.Customer_Dim.CustomerID INNER JOIN Northwind_Mart.dbo.Time_Dim
ON Orders.ShippedDate = Northwind_Mart.dbo.Time_Dim.TheDate INNER JOIN Northwind_Mart.dbo.Shipper_Dim
ON Orders.ShipVia = Northwind_Mart.dbo.Shipper_Dim.ShipperID INNER JOIN Northwind_Mart.dbo.Employee_Dim
ON Orders.EmployeeID = Northwind_Mart.dbo.Employee_Dim.EmployeeID WHERE (Orders.ShippedDate IS NOT NULL)
Estableciendo el Destino
10. En ToolBox en DataFlowDestinations , arrastre OLEDB DataDestionation
11. Clic en OLE DB DataSource, clic en la flecha verde y arrastre
24
Construccin de un Data WareHouse
Actividad Pasos a Seguir
hacia OLEDB Data Destination
12. Doble Clic en OLEDB DataDestination. 13. Desde ConnectionManager> a. OLE DB Connection Manager: localhost.NorthWind_Mart b. Data Access; Table
c. Name: Sales_Fact
Clic en Mappings: y trate de llegar al sgte esquema Grabando y Ejecutando el Paquete
Verificando
Poblamiento
1. Grabar
2. Presione F5 para ejecutar
1. Cargue el SQL Server Management Studio
2. Clic en New Query
3. Seleccione la BD: NorthWind_Mart
4. Digite: SELECT * FROM Sales_Fact 5. Ejecutar
Ejercicio 04. Grabando y Ejecutando el Paquete
1. Hace click en el cono de grabar.
2. Grabe el paqute con: Poblando el Data Mart para NorthWind 3. Seleccione SQL Server de la lista Location
4. Click OK
Ejecutar el Paquete:
1. Presione el botn grabar 2. Para ejecutar el paquete haga click en el botn execute
3. A continuacin observar la ejecucin de cada dimensin programada. 4. Si observa ninguna advertencia de error , corrija.
5. Haga click en Done
6. Repita los pasos 2,3, 4 y 5 ms veces. Esto le permitir ver informacin ms tarde.
25
Construccin de un Data WareHouse
Ejercicio 05. Consultando informacin de NorthWind_mart
1. Ejecutar la siguiente consulta:
SELECT Product_Dim.ProductName, Product_Dim.CategoryName, Product_Dim.SupplierName, SUM(Sales_Fact.LineItemQuantity) AS [Total Units Sold] FROM Sales_Fact
INNER JOIN Product_Dim ON Sales_Fact.ProductKey = Product_Dim.ProductKey GROUP BY Product_Dim.ProductName, Product_Dim.CategoryName, Product_Dim.SupplierName, Sales_Fact.RequiredDate
HAVING (Sales_Fact.RequiredDate < getdate()) 2. Realice las sgts consultas de informacin, que determinan la Jerarqua de los Datos en un Data WareHouse en la dimensin productos a. Consultando Productos select ProducName,
SUM(LineItemQuantity) as cantidad, SUM(LineItemTotal) AS total
from sales_fact sf inner join product_Dim p on sf.productKey = p.ProductKey GROUP BY ProductName
b. Consultando Categoras - Productos select CategoryName, ProductName,
SUM(LineItemQuantity) as cantidad, SUM(LineItemTotal) AS total
from sales_fact sf inner join product_Dim p on sf.productKey = p.ProductKey GROUP BY CategoryName, ProductName
ORDER BY CategoryName, ProductName
c. Consultando Proveedores Categorias Productos select SupplierName, CategoryName, ProductName, SUM(LineItemQuantity) as cantidad, SUM(LineItemTotal) AS total
from sales_fact sf inner join product_Dim p on sf.productKey = p.ProductKey GROUP BY SupplierName, CategoryName, ProductName
ORDER BY SupplierName, CategoryName, ProductName
3. Realice las consultas que determinen la jerarqua de la dimensin Clientes: a. Para el cliente
b. Ciudad - Cliente c. Region Ciudad Cliente d. Pas Regin Ciudad Cliente 4. Se quiere un realizar un anlisis de las ventas de las productos por aos. Ejecute las sgts. Consulta:
select t.year, ProductName, SUM(LineItemQuantity) as cantidad, SUM(LineItemTotal) AS total from sales_fact sf inner join product_Dim p on sf.productKey = p.ProductKey inner join time_Dim t on sf.TimeKey = t.TimeKey
GROUP BY t.year, ProductName Suponga que slo quiere analizar el producto: Queso Manchego La Pastora , en qu ao se vendi ms. Se puede determinar en qu trimestre las ventas fueron mayores y en que mes? 5. Haga un anlisis de las ventas por categoras en el tiempo. Determine el mejor mes de venta de una categora determinada. 6. Haga un anlisis de las ventas por cliente en el tiempo. Determine la mejor semana de venta de un regin determinada.
26
Construccin de un Data WareHouse
Laboratorio 3: Creando Cubos
Objetivos
Crear dimensiones privadas y compartidas
Crear un Cubo
Disear agregaciones y campos calculados A. Creando el Proyecto de Analysis Services y definiendo el Origen de Datos
Actividad Pasos a seguir
Ingresando al SQL Server Business Intelligent
Development
Studio
1. Iniciar el SQL Server Business Intelligent Development Studio. 2. Desde el menu elegir: File New Proyect, con lo que se activa la ste interfaz
3. Tipo de Proyecto: Business Intelligent
4. Templates: Anlisis Services Project 5. Name: PyNorthWind
6. Clic Ok, con lo que se activa la interfaz siguiente:
27
Construccin de un Data WareHouse
Actividad Pasos a seguir
Creando el Data Source
1. Ubicarse en el Explorador de Soluciones, clic derecho sobre Data Source: New Data Source.
2. Aparece la Pantalla de Presentacion del Asistente: clic Next
3. Clic en New: con lo que se activa:
28
Construccin de un Data WareHouse
Actividad Pasos a seguir
4. Clic: Next: seleccione: Service Account
Creando el Data Source View
5. Clic: Finish
1. Ubicarse en el Explorador de Soluciones, clic derecho sobre Data
Source: New Data Source View.
2. Aparece la Pantalla de Presentacion del Asistente: clic Next 3. Clic en New:
29
Construccin de un Data WareHouse
Actividad Pasos a seguir
4. Clic Next Debe aparecer NorthWind
5. Click en : >>
6. Clic Next 7. Clic: Finish
Al finalizar obtendr la sgte interfaz:
30
Construccin de un Data WareHouse
Actividad Pasos a seguir
Definiendo un Cubo
Actividad Pasos a seguir
Creando el
Cubo y sus Propiedades
1. En el explorador de soluciones, clic derecho sobre Cubes, y
clic en New Cube.
2. En la pagina de bienvenida Clic Next 3. En Select Build Method verifique que se encuentre seleccionado: Build the cube using DataSource y este seleccionada el check AutoBuild. Clic Next
4. En Select Data Source View, asegurarse de estar seleccionado : NorthWind_Mart, clic Next
5. En Detecting Fact and Dimension Tables clic Next 6. En Identifying Fact and Dimension Tables, asegurarse de seleccionar de acuerdo a las sgte interfaz
31
Construccin de un Data WareHouse
Actividad Pasos a seguir
Asegurarse de haber seleccionado: Time_dim en Time Dimension table
y haga clic en Next 7. En Select Time Periods, haga la seleccin de acuerdo a la interfaz mostrada.
32
Construccin de un Data WareHouse
Actividad Pasos a seguir
Clic Next
8. En Select Measures, clic Next
9. En Detecting Hierarchies, clic Next 10. En Review New Dimensions, verifique que solo ha
autodetectado Jerarquias en Time_Dim, clic Next
33
Construccin de un Data WareHouse
Actividad Pasos a seguir
11. Clic Finish
12. Este es el aspecto final del Cubo
13. Grabar
34
Construccin de un Data WareHouse Visualizando el Cubo Creado
Actividad Pasos a seguir
Visualizando
Dimensiones
Desplegando el proyecto
1. En dimensiones ubiquese en Time_Dim y expandalo 2. Clic en Edit , y visualice la dimension con las jeraquias creadas. Observe que puede tener mas de una jerarquia por cada dimension, esto es mas trasparente ahora que en versiones anteriores.
1. Presione : F5
35
Construccin de un Data WareHouse
Visualizando Datos de la dimensin tiempo
1. Clic sobre la ficha: Browser.
2. Navegue sobre la jerarquias y atributos
36
Construccin de un Data WareHouse
Visualizando Datos del Cubo
1. Visualice el sgte esquema
2. Haga Clic en la Ficha Design
3. Luego clic en Browser 4. Arrastre a Filas: time_dim - Year Quarter Month
5. Arrastre a Campos: Product_dim -- CategoryName 6. Arrastre a Filtro: Employee_Dim EmployeeName 7. Measures: LineItem Total
8. Apariencia de la Visualizacion
Navegue de acuerdo a su gusto, recuerda que aun no hay jerarquas establecidas salvo la dimensin tiempo, en el ejercicio siguiente crearemos las jerarquas respectivas.
37
Construccin de un Data WareHouse Creando Jerarquas en las Dimensiones
Actividad Pasos a seguir
Creando
jerarquias en la dimension Customer_dim
1. Clic en la ficha Cube Structure
2. Expanda la Dimension Customer_dim y clic en Edit Customer 3. Visualice en sgte esquema: Attributes y Hierarquies and Levels
4. Arrastre : Country - desde Attributes a Hierarquies 5. Arrastre debajo de Country : Region
6. Arrastre debajo de Region: City
7. Arrastre debajo de City: CompanyName 8. Ubique en Propiedades Name y digite: Pais-Region-Ciudad-Cia
9. Creando Otra Jerarquia: 10.Arrastre : Country - desde Attributes a Hierarquies
11.Arrastre debajo de Region: City
12.Arrastre debajo de City: CompanyName 13.Ubique en Propiedades Name y digite: Country-Ciudad-Cia
14.Visualice el Esquema Final
38
Construccin de un Data WareHouse
Visualizando datos de la dimension: Customer
Creando las Jerarquias de la Dimension Product_Dim
1. Procesando Dimension
2. Clic en el Menu: Dimension Process 3. Clic en Run
4. Clic Close
5. Clic en Browser y navegue por las jerarquias respectivas 1. Crear la jerarquias con los atributos: CategoryName, SupplierName, ProductName y nombrela: Categora-Proveedor- Producto 2. Crear la jerarquias con los atributos: CategoryName, ProductName y nombrela: Categora-Producto
3. Crear la jerarquia con los atributos: SupplierName, ProductName y nombrela: Proveedor-Producto
4. Luego visualice los datos.
39
Construccin de un Data WareHouse Agregar un campo calculado a su cubo
Actividad Pasos a seguir
Creando Campo Calculado
1. Asegurarse de estar ubicado Cube Structure
2. Clic en Calculations y visualice el sgte esquema.
3. Clic en la barra de herramientas: New Calculated Member
4. Nombre de Campo: ValorNeto
5. Expresin: [Measures].[Line Item Total] - [Measures].[Line Item Discount] -- puede arrastrar
desde medidas
6. Format String: Currency
7. Non-Empty behaviour: LineItemTotal , LineItemDiscount
Desplegar 1. En el men elegir: Build Deploy PyNothWind
40
Construccin de un Data WareHouse
Visualizando el Cubo
1. Clic en browser
2. Clic Reconnect -- desde la barra de herramientas. 3. Ubique en Measures y verifique el campo calculado creado
41
42
Construccin de un Data WareHouse
Laboratorio 4: Analizando Datos Usando
Clientes
Objetivos
Analizar datos con drill up y drill down
Generando una Pivot Table Report usando Excel 2010
Visualizar los datos por medio de un grfico en Excel 2010
Desarrollar un proyecto Windows en Visual Studio .NET 2008 Desarrollo del Laboratorio
Ejemplo 1. Conectarse al Cubo Ventas
Cargar Excel 2010
Seleccionar la opcin Datos - Informe de tablas y grficos dinmicos
Al aparecer el asistente elegir:
Dnde estn los datos Fuente de datos externa
Click en: Obtener Datos, seleccione la ficha: Cubos OLAP
Doble click: Nuevo Orgen de Datos e ingrese la sgte. informacin
Opcin Valor
Nombre orgen de datos NorthWind_Cubo_Ventas
Proveedor OLE DB Microsoft OLE DB Provider for Analysis Services 9.0
Conectar a Proveedor OLE DB Click Conectar
Localizacin OLAP Server Multidimensional Connection 9.0 . Dejar en blanco: usuario y password
Database PyNorthWind
Seleccione un Cubo North Wind Mart
Click Aceptar
Clic Aceptar
Click Siguiente Click Finalizar
Este es la interfaz para la creacin de informacin.
43
Construccin de un Data WareHouse
Ejemplo 2. Creando el Reporte de Ventas desde los datos del OLAP Ubicar dimensiones y medidas en la Tabla dinmica
Haga un drag and drop de los sgts elementos desde la barra de herramientas
Elemento Ubicacin
Pais-Region-Ciudad-Cia Coloque pginas de campo
Categora Producto Campos de fila
Year Quarter YearMonth Coloque campos de columna
LineItemQuantity Coloque datos Ejemplo 3. Analizando datos Usar la Tabla dinmica
En la tabla dinmica: en Year click en la lista drop-down, limpie el check 1996 y
1998. Click 1997 , aparecer una marca doble
Expanda 1997 y click cada quarter , aparecer una marca doble
click OK Para reducir el nivel de detalle, doble click cualquier celda que tenga el valor 1997.
44
Construccin de un Data WareHouse
Para expandir haga doble click
Intente presentar la informacin mensualmente y luego presente los datos del LineItemQuantity a nivel de CategoryName, Year.
Consultando datos: realice las sgts consultas y luego grabe los datos.
Cules son las ventas de Grain/Cereals para USA, en November 1997
Cmo se compara este valor con las ventas de United Kingdom (U.K.)para el mismo perodo.
Cmo son las cantidades vendidas en Confections el primer quarter de 1997 comparadas con el primer quarter de 1998 Qu productos tienen las mayores ventas en London para el 2do trimestre de 1997 de la categora Confecciones. En qu trimestre del ao 1997 en el estado WA de USA se vendi ms Dairy
Products Cul fu la categora de productos ms vendida y que ao en USA
En qu mes del 2do trimestre del 1997 se vendieron ms items de la categora
Beverages Ejemplo 4. Creando un grfico usando data existente Creando un grfico
Mostrar London y todas las categoras de los productos del 1er trimestre de 1997 y 1er trimestre de 1998
Haga click en Asistente de grficos
Creando un grfico tridimensional
Click botn derecho y elija: Tipo de grfico
En Tipos seleccione: Grficos de Columna 3D, el resultado debe ser el siguiente
45
Construccin de un Data WareHouse
Click Year y marque todos los trimestres disponibles de 1997 y 1998.
Cambie el tipo de grafico a: grfico de columnas. Cul fue la venta ms alta para el cliente: Around Horn- especifique ao- trimestre y
categora.
Vaya a la hoja de clculo y grabe el archivo Ejemplo 5. Creando un archivo de Cubo Local Creando un Cubo Local
Ubicarse en la Hoja de clculo En la barra de Tabla dinmica seleccione: OLAP Sin Conexin
Click: Crear archivo datos sin Conexin
46
Construccin de un Data WareHouse
Clic: Siguiente
Opcin Valor Dimensiones seleccione todos los niveles para cada dimensin que aparece con Chech
Nivel superior objetos Country: Seleccin USA Year: 1998.
Ubicacin archivo C:\
Nombre archivo Ventas.Cub
Clic Aceptar. Cierre el archivo, sin grabar Ejemplo 6. Conectndose a un Cubo Local
Conectando un Cubo Local
Crear una nueva Hoja de Clculo Seleccionar la opcin Datos - Informe de tablas y grficos dinmicos
Al aparecer el asistente elegir:
Dnde estn los datos Fuente de datos externa Clase de Reporte Tabla dinmica
Click en: Obtener Datos, seleccione la ficha: Cubos OLAP
Doble click: Nuevo Orgen de Datos e ingrese la sgte. informacin
Opcin Valor
Nombre orgen de datos NorthWind_Cubo_Ventas_Local
Proveedor OLE DB Microsoft OLE DB Provider for SQL Server
Conectar a Proveedor OLE DB Click Connect
Localizacin Archivo Cubo
Nombre archivo C:\Ventas.Cub
Click Aceptar
Click Next
Click Finalizar
Ubicando dimensiones
Crear una nueva Hoja de Clculo Seleccionar la opcin Datos - Informe de tablas y grficos dinmicos
Elemento Ubicacin
Customer Coloque pginas de campo
Time Campos de fila
Product Coloque campos de columna
LineItemTotal Coloque datos
Top Related