LABORATORIO N° 04 Asignatura Docente
: :
Sistema de Toma de de Decisiones Decisiones Ing. Johny Pretell C.
Turno : Noche Ciclo : VII Semestre : 2012-2
Procesos ETL con Microsoft Integration Services Poblando un Data Warehouse I.
OBJETIVOS Familiarizarse con una Base de Datos OnLine Transaction Process (OLTP) Ejecutando Simples búsquedas a los datos en un Online Analytical Processing (OLAP)
II.
EQUIPOS Y MATERIALES
Computadora personal MS Integration Services Services Guía de Laboratorio. Base de Datos Completa NorthWind
III. METODOLOGIA Y ACTIVIDADES Ejercicio 01 Definiendo Flujos de Trabajo Con el DTS se puede definir un flujo de trabajo que controla la ejecución secuencial paso a paso. Controlar el flujo lógico y determinando las precedencias respectivas, priorizando tareas a desarrollar.
Definiendo Pasos de Transformación Transformación Un paso define una unidad de trabajo que es ejecutada como parte de un proceso de transformación. Un paso puede: Ejecutar una orden SQL Mover y transformar datos heterogéneos Ejecutar Jscript, VBScript. Estos scripts pueden ejecutar cualquier operación con su lenguaje nativo. Recuperar y ejecutar otros DTS packages.
Por ejemplo Ud. Puede preparar un DTS package para sumarizar las ventas al contado y al crédito 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 (orígenes y destinos) y tareas usadas para ejecutar estas conexiones. La acción de ejecutar una tarea es controlada por un paso: Notemos los sgts pasos y restricciones de precedencia en el DTS Designer.
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 1
Las restricciones de precedencia son representadas por una línea azul (Cuando se ha completado) Verde (ejecutada) o Rojo (Error) Veamos el sgte. Diagrama:
Tipos de Restricción 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. FAILURE indica que el Paso origen debe com pletarse con una indicación de error antes de ejecutar el paso destino. COMPLETION: indica que el paso origen debe simplemente 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
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 2
Poblamiento Utilizando el Transact-SQL Ejemplo 1 Copiando información y transformando datos. Mismo Origen Origen: MS SQL Server: NorthWind.dbo.Employees Destino: MS SQL Server: NorthWind_Mart.dbo.Employee_Dim Sin verificar datos del destino. Suponga que desea copiar información 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 instrucción y luego consulte la información de la tabla dimensión:Employee_Dim Qué observa en los datos? Eliminando datos de la Tabla Dimensión: Employee_Dim DELETE FROM Employee_Dim Copiando información y transformando datos. Verificando datos del destino
Utilizando el método 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 método 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 Ejecución de un Package Creación de una DTS Package Creando un Paquete y Adicionando Conexiones Activi d ad 1: Ingresando al SSIS
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 3
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:
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
3. Renombrar el Paquete como: Poblamiento General Recuerde que un proyecto es una colección STD – CONSTRUYENDO UN DATA WAREHOUSE
de
Paquetes,
DataSources, Página 4
DataSource Views y otros elementos desarrolle la construcción del paquete.
que
podrán
ser
usados
mientras
Actividad 2: Creando la conexión de datos ORIGEN
1. Clic derecho sobre DataSource : New con lo que se activa:
2. Haga clic en New y digite los sgts datos
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 5
3.
Ok 4. Next 5. Finish Actividad 3: Creando la conexión destino de datos
1. Proceda como en paso anterior, pero esta vez establezca la conexión con la BD NorthWind_Mart. Actividad 4: Agregando una tarea de ejecución 1. Asegurarse de estar ubicado en Contr ol Flow 2. Desde ToolBox arrastre Execute SQL Task luego clic derecho Edit ubique la propiedad Conn ection y seleccione: localhost.Nor thWind _ M ar t 3.Ubique la propiedad SQL Statment clic en (…) y digite lo sgte:
DELETE Sales _ Fact DELETE Emp loyee _ Dim DBCC CHECKIDENT('Employee_Dim', r eseed,0) DELETE Tim e _ Dim DBCC CHECKIDENT('Time_Dim', r eseed,0 ) DELETE Custom er _ Dim DBCC CHECKIDENT('Customer_Dim', r eseed,0 ) DELETE Shipper _ Dim DBCC CHECKIDENT('Shipper_Dim', r eseed,0 ) DELETE Pr oduct _ Dim DBCC CHECKIDENT('Product_Dim', r eseed,0 ) 4. Ok 5. Ok STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 6
Poblando Dimensión Transportistas Actividad 1: 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
Actividad 2: 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 4. S Actividad 3: 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: Shipper_Dim Clic en Mappings: y trate de llegar al sgte esquema
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 7
Actividad 4: Grabando y Ejecutando el Paquete
1. 2.
Grabar Presione F5 para ejecutar
Actividad 5: Verificando Poblamiento
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 Dimensión Clientes Actividad 1: Creando el Flujo de Datos
1. 2. 3. 4.
Desde ToolBox arrastre DataFlowTask Clic derecho: Rename y digite: Poblando Clientes Clic en Execute SQL Tasks, clic en la flecha verde y arrastre hasta Poblando Clientes
Actividad 2: Estableciendo el origen
1. 2. 3. 4.
Doble clic sobre Poblando Cliente En ToolBox en DataFlowSources arrastre OLEDB DataSource y haga doble clic Desde ConnectionManager Desde ConnectionManager> a. OLE DB Connection Manager : localhost.NorthWind b. Data Access: SQL Command b. Data Access: SQL Command c. SQL CommandText: SELECT *, Regioncita = ISNULL(Region, 'Otros') FROM customers
Actividad 3: 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
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 8
Asegurarse de que en Destino Región sea a partir de Regioncita Actividad 4: Grabando y Ejecutando el Paquete
1. 2.
Grabar Presione F5 para ejecutar
Actividad 5: Verificando Poblamiento
1. 2. 3. 4.
Cargue el SQL Server Management Studio Clic en New Query Seleccione la BD: NorthWind_Mart Digite: SELECT * FROM Customer_Dim
Poblando Dimensión Tiempo Actividad 1: Creando el Flujo de Datos
1. 2. 3.
Desde ToolBox arrastre DataFlowTask Clic derecho: Rename y digite: Poblando Tiempo Clic en Execute SQL Tasks, clic en la flecha verde y arrastre hasta Poblando Tiempo
Actividad 2: Estableciendo el origen
4. 5. 6.
Doble clic sobre Poblando Tiempo En ToolBox en DataFlowSources arrastre OLEDB DataSource y haga doble clic 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
Actividad 3: 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: time_Dim Clic en Mappings: y trate de llegar al sgte esquema
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 9
Actividad 4: Grabando y Ejecutando el Paquete
1. Grabar 2. Presione F5 para ejecutar Actividad 5: Verificando Poblamiento
1. 2. 3. 4.
Cargue el SQL Server Management Studio Clic en New Query Seleccione la BD: NorthWind_Mart Digite: SELECT * FROM Time_Dim
Poblando Dimension Producto Actividad 1: Creando el Flujo de Datos
1. 2 3.
Desde ToolBox arrastre DataFlowTask Clic derecho: Rename y digite: Poblando Producto Clic en Execute SQL Tasks, clic en la flecha verde y arrastre hasta Poblando Producto
Actividad 2: 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
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 10
e. Clic: Add f. Clic:Close g. Seleccione las columnas de acuerdo a lo mostrado a continuación.
h. Clic ok i. Clic ok
Actividad 3: 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: Product_Dim Clic en Mappings: y trate de llegar al sgte esquema
Actividad 4: Grabando y Ejecutando el Paquete
1. Grabar 2. Presione F5 para ejecutar Actividad 5: Verificando Poblamiento
1. 2. 3. 4.
Cargue el SQL Server Management Studio Clic en New Query Seleccione la BD: NorthWind_Mart Digite: SELECT * FROM Product_Dim
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 11
Poblando Dimensión Empleados Actividad 1: 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 Actividad 2: 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 from employees Actividad 3: 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: Employee_Dim Clic en Mappings: y trate de llegar al sgte esquema
Actividad 4: Grabando y Ejecutando el Paquete
1. Grabar 2. Presione F5 para ejecutar Actividad 5: Verificando Poblamiento
1. 2. 3. 4. 5.
Cargue el SQL Server Management Studio Clic en New Query Seleccione la BD: NorthWind_Mart Digite: SELECT * FROM Employee_Dim Execute
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 12
Poblando Tabla Hecho Actividad 1: Creando el Flujo de Datos
1. 2. 3. 4. 5. 6. 7. 8.
Ubicarse en Control Flow Desde ToolBox arrastre DataFlowTask Clic derecho: Rename y digite: Poblando Tabla Hecho Clic en Poblando Transportistas, clic en la flecha verde y arrastre hasta Poblando Tabla Hecho. Clic en Poblando Clientes, clic en la flecha verde y arrastre hasta Poblando Tabla Hecho. Clic en Poblando Tiempo, clic en la flecha verde y arrastre hasta Poblando Tabla Hecho. Clic en Poblando Productos, clic en la flecha verde y arrastre hasta Poblando Tabla Hecho. Clic en Poblando Empleados, clic en la flecha verde y arrastre hasta Poblando Tabla Hecho.
Actividad 2: 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 =
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 13
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) Actividad 3: Estableciendo el destino
9. En ToolBox en DataFlowDestinations , arrastre OLEDB DataDestionation 10. Clic en OLE DB DataSource, clic en la flecha verde y arrastre hacia OLEDB Data Destination 11. Doble Clic en OLEDB DataDestination. 12. 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
Actividad 4: Grabando y Ejecutando el Paquete
1. Grabar 2. Presione F5 para ejecutar Actividad 5: Verificando Poblamiento
1. 2. 3. 4. 5.
Cargue el SQL Server Management Studio Clic en New Query Seleccione la BD: NorthWind_Mart Digite: SELECT * FROM Sales_Fact 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. 2. 3. 4. 5.
Presione el botón grabar Para ejecutar el paquete haga click en el botón execute A continuación observará la ejecución de cada dimensión programada. Si observa ninguna advertencia de error, corrija. Haga click en Done
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 14
6.
Repita los pasos 2,3, 4 y 5 más veces. Esto le permitirá ver información más tarde.
E jer cicio 05. Consultando inf or mación 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 información, que determinan la Jerarquía de los Datos en un Data WareHouse en la dimensión 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 Categorías - 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 jerarquía de la dimensión Clientes: a. c. d.
Para el cliente b. Ciudad - Cliente Region – Ciudad – Cliente País – Región – Ciudad – Cliente
4. Se quiere un realizar un análisis de las ventas de las productos por años. 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 sólo quiere analizar el producto: Queso Manchego La Pastora, en qué año se vendió más. Se puede determinar en qué tri mestre las ventas fueron mayores y en que mes?
5. Haga un análisis de las ventas por categorías en el tiempo. Determine el mejor mes de venta de una categoría determinada. 6. Haga un análisis de las ventas por cliente en el tiempo. Determine la mejor semana de venta de una región determinada.
STD – CONSTRUYENDO UN DATA WAREHOUSE
Página 15