Soporte para la Toma Toma de Desiciones Desicione s
UNSCH 2016
Labo La bora ra torio or io 2 y 3: “Modelamiento Dimensional – ETL ET L”
OBJETIVOS ESPECÍFICOS
Identificar el modelo de datos Northwind Mostrar la base de datos analítica a nalítica de Northwind y la base de datos de Trabajo Crear un Proyecto de ETL utilizando SL Ser!er Inte"ration Ser!ices
CONTENIDOS
#e!isi$n de Tablas Tablas de la base de datos dat os Northwind Creaci$n de la %ase de datos Northwind&Mart Creaci$n de la %ase de datos Northwind&Sta"e Creaci$n de un Proyecto de ETL utilizando SL Ser!er Inte"ration Ser!ices In"reso al entorno de desarrollo del SL Ser!er %usiness Intelli"ence o 'e!elo(ment Studio o 'efinici$n de )ri"en de datos* Cone+iones* flujos de control y flujo de datos o
ACTIVIDADES
Construye un modelo dimensional en base a la base de datos (ubs,
1. IDENTIFICAR EL MODELO DE DATOS DE NORTHWIND
La base de datos Northwind almacena el registro de las órdenes solicitadas por diversos clientes situados en varios países. Dichas órdenes están conformadas por los productos ofrecidos los cuales están agrupados por categorías.
1.1. Revisión de Ta!as Ta!as N"#$%&ind N"#$%&ind La base de datos Northwind est- conformada (or las si"uientes tablas .Solo mencionaremos a/uellas necesarias (ara el curso0, Customers .Clientes0
Employees .Em(leados0
Categorías .Cate"orías0 Products .Productos0
Orders .)rdenes0
Order Details (Detalle de
Ing. Richard Zapata Casaerde
La tabla re"istra la informaci$n relacionada con la cartera de clientes con la /ue trabaja Northwind, 'icha tabla contem(la* el nombre de la com(a1ía* el nombre del contacto* la direcci$n* la re"i$n* ciudad* (aís* entre otros cam(os, La tabla re"istra la informaci$n relacionada con los em(leados /ue atienden las $rdenes en Northwind, 'icha tabla contem(la los a(ellidos* nombre* fecha de contrataci$n* fecha de nacimiento* así como el su(erior inmediato del em(leado* entre otros cam(os, La tabla re"istra la informaci$n relacionada con las cate"orías /ue a"ru(an !arios (roductos, La tabla re"istra la informaci$n de los (roductos /ue son solicitados en las $rdenes* como nombre del (roducto* cate"oría asociada* (recio unitario* (ro!eedor* unidad de medida* estado del (roducto* entre otros cam(os, La tabla re"istra los mo!imientos de cada orden "enerada* en la cual se consi"na el cliente solicitante* el em(leado /ue la atiende* la fecha* entre otros cam(os, La tabla re"istra el detalle de cada línea de
Soporte para la Toma Toma de Desiciones Desicione s Ordenes)
UNSCH 2016 (roducto de cada orden "enerada* es decir* el (roducto* la orden* la cantidad solicitada* el (recio unitario y el descuento,
1.'. ESTR(CT(RA ESTR(CT(RA DE TABLAS TABLAS DE NORTHWIND 2 continuaci$n* continuaci$n* se (resenta (resenta la estructura de las tablas tablas de la base de de datos Northwind* se se1ala /u3 almacena y cu-les son sus cam(os mas im(ortantes,
Ta!a Ta!a ) C*s$"+e#s Lla!e Princi(al4 CustomerId, Ti(o de dato nchar .alfanum3rico0
#e!isemos al"unos cam(os4 CustomerId 5 Ti(o de datos alfanum3rico, #e"ion 5 Contiene los !alores de las re"iones (ara los clientes* al"unos re"istros tienen el !alor de N6LL* ya /ue de acuerdo con el (aís al"unos cuentan re"iones y otros no,
Ta!a Ta!a ) E+,!"-ees Lla!e (rinci(al4 Em(loyeeId* Ti(o de dato entero El cam(o 7#e(ortsTo8* 7#e(ortsTo8* se refiere al em(leado inmediato su(erior .9efe0,
#e!isemos al"unos cam(os4 Em(loyeeId 5 Ti(o de datos num3rico,
Ing. Richard Zapata Casaerde
Soporte para la Toma Toma de Desiciones Desicione s
UNSCH 2016
#e"ion 5 Contiene los !alores de las re"iones (ara los em(leados* al"unos re"istros tienen el
!alor de N6LL* ya /ue de acuerdo al (aís al"unos cuentan re"iones y otros no, #e(ortsTo 5 :ace referencia al c$di"o del em(leado a /uien debe re(ortar el em(leado descrito, E+iste una relaci$n en la misma tabla entre el cam(o em(loyeeId y #e(ortsTo, #e(ortsTo,
Ta!a Ta!a ) Ca$e"#ies Lla!e Princi(al4 Cate"oryId, Ti(o de dato entero
#e!isemos al"unos cam(os4 Cate"oryId 5 Ti(o de datos num3rico Picture 5 2lmacena en formato binario la foto de la cate"oría,
Ta!a Ta!a ) P#"d*/$s Lla!e (rinci(al4 ProductId, Ti(o de 'ato Entero
#e!isemos al"unos cam(os4
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
ProductId 5 Ti(o de datos num3rico ProductName 5 2lmacena en el nombre del (roducto Su((lierId 5 C$di"o del (ro!eedor del (roducto Cate"oryId 5 C$di"o de la cate"oría a la cual (ertenece el (roducto 6nitPrice 5 Precio 6nitario del (roducto 'iscontinued 5 Estado del (roducto .;5acti!o * < 5 descontinuado0 6nitInStoc= 5 Stoc= de (roductos
Ta!a ) O#de#s Lla!e (rinci(al4 )rderId, Ti(o de 'ato entero La tabla )rders tiene la informaci$n del cliente solicitante y el em(leado /ue atiene la orden, Cada re"istro de la tabla )rders almacena los datos relacionados a una orden,
#e!isemos al"unos cam(os4 )rderId 5 Ti(o de datos num3rico )rder'ate 5 >echa de solicitud de la )rden #e/uired'ate 5 >echa /ue debería lle"ar la orden al cliente Shi((ed'ate 5 >echa de en!ío de la )rden Em(loyeeId 5 Em(leado /ue atendi$ la orden CustomerId 5 Cliente /ue solicit$ la orden
Ta!a ) O#de#s De$ai!s Lla!e (rinci(al4 )rderId8* Ti(o de dato entero La tabla ?)rders 'etails@ tiene la informaci$n en detalle de la orden solicitada* es decir* re"istra cada línea de (roducto (edida asociada al (recio unitario y la cantidad,
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
#e!isemos al"unos cam(os4 )rderId 5 Ti(o de datos num3rico ProductId 5 C$di"o del (roducto 6nitPrice 5 Precio unitario del (roducto solicitado uantity 5 Cantidad (edida de un (roducto solicitado 'iscount 5 'escuento em(leado a cada (roducto de la orden Note /ue e+isten A re"istros (ara la )rden 5 <;BD* es decir* /ue se solicitaron A TIP)S 'E P#)'6CT)S 'ISTINT)S .<<* B y B0, 2dem-s obser!e /ue (ara el (roducto <<* se solicit$
1.0.
CONS(LTA LA BASE DE DATOS NORTHWIND En esta secci$n* se realizar-n al"unas consultas sobre las tablas descritas anteriormente* con la finalidad de ir definiendo4
• •
2n-lisis a realizar (osteriormente El "rado de com(licaci$n referente a la im(lementaci$n de consultas SL cada !ez m-s elaboradas
P#e*n$a. 7Liste el total facturado (or cate"orías8 Select c,cate"oryName as Cate"oria* Sum .d,/uantityFd,unit(rice0 as Monto >rom Cate"ories c 9)IN Products ( on c,cate"oryId 5 (,cate"oryId 9)IN Gorder detailsG d on (,(roductid 5 d,(roductid Hrou( by c,cate"oryName
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
P#/$i/a. Se desea hacer com(araciones entre los a1os facturados (or cada cate"oría Select year.o,orderdate0 as 21o* c,cate"oryName as Cate"oria* sum.d,/uantityFd,unit(rice0 as Monto >rom Cate"ories c 9)IN Products ( on c,cate"oryId 5 (,cate"oryId 9)IN Gorder detailsG d on (,(roductid 5 d,(roductid 9)IN orders o on o,orderid5d,orderid "rou( by year.o,orderdate0*c,cate"oryName order by B*<
P#/$i/a, Sería m-s con!eniente tener un listado de la si"uiente manera4 :-"alo usted, La consulta es m-s com(licadaJ
P#/$i/a, 2hora le solicitan /ue a"re"ue el total facturado (or cada cate"oría junto con el detalle com(arati!o (or a1o, Im(lemente usted un listado de la si"uiente manera4 La consulta se com(lica aKn m-sJ
P#e*n$a, 7'e la consulta anterior* se desea analizar la facturaci$n de cada (roducto /ue se solicit$ de la cate"oría %e!era"es detallado (or a1o8, Select c,cate"oryName as Cate"oria*
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
year.o,orderdate0 as 21o* (,(roductname as Producto* Sum .d,/uantityFd,unit(rice0 as Monto >rom Cate"ories c 9)IN Products ( on c,cate"oryId 5 (,cate"oryId 9)IN Gorder detailsG d on (,(roductid 5 d,(roductid 9)IN orders o on o,orderid5d,orderid here c,cate"oryid5< Hrou( by c,cate"oryName*year.o,orderdate0*(,(roductname order by <*A*B
Nue!amente* debe !ariar la consulta ocasionando in!ersi$n de tiem(o, 2dem-s* el resultado no es muy (r-ctico (ara (ro($sitos de an-lisis, Con los ejercicios anteriores* (odemos concluir /ue4 2l"unas !eces* el resultado del SL no es (r-ctico (ara hacer re!isiones o an-lisis sobre los datos, Se in!ierte tiem(o en la im(lementaci$n de las consultas, 6n usuario final (romedio no (odría im(lementar las consultas a medida /ue re/uiere ahondar m-s en la informaci$n, Las consultas SL son rí"idas cuando se desea cambiar la a(ariencia de los resultados de!ueltos, •
• •
•
'. CREACI2N DE LA BASE DE DATOS NORTHWIND3MART 'e acuerdo con lo e+(uesto en la secci$n anterior* referente a las tablas de la base de datos Northwind y al "iro del ne"ocio de dicha em(resa* es (osible /ue usted se (lantee al"unos re/uerimientos de an-lisis, La facilidad /ue brindan las soluciones )L2P /ueda de manifiesto en (oder definir a/uello /ue se desea analizar de manera sencilla y fle+ible,
'.1. INDICADORES DE AN4LISIS 'ebe formularse de manera (recisa a/uello /ue se desea analizar, Lo im(ortante de los indicadores es /ue muestran .como un tablero de control de un auto0 informaci$n rele!ante (ara (oder tomar una decisi$n sobre la base de lo mostrado (or 3l, La toma de decisiones (uede conlle!ar a la (romoci$n de un nue!o (roducto* la li/uidaci$n de contrato con un cliente* ser m-s a"resi!o en las cam(a1as (ublicitarias* fusi$n entre em(resas o hasta el cierre del ne"ocio, amos a listar al"unos criterios de an -lisis b-sicos sobre la base de datos Northwind, 2l"unos de ellos ser-n im(lementados a lo lar"o del curso, • • • •
2n-lisis com(arati!os de monto facturado entre cate"orías (or (eriodos de tiem(o 2n-lisis "eo"r-fico (or (aíses de clientes /ue solicitan ordenes (or (eriodos de tiem(o 2n-lisis de (roductos con (roblemas de ace(taci$n (or ran"os de tiem(o Com(arati!os entre meses relacionados a monto de facturaci$n
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones •
UNSCH 2016
Encontrar los (eri$dos de tiem(o con menos in"resos .facturaci$n0 (ara la em(resa con res(ecto a las $rdenes
6sted defina al"unos criterios m-s4 <,&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& B,&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& A,&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& ,&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
'.'. MODELO DIMENSIONAL El modelo dimensional contiene la representación de las tablas que conforman la solución OL!. Dicho modelo tiene una estructura mu" diferente al modelo E#$ conocido. En el modelo dimensional* se deben contem(lar dos ti(os de estructuras4 • Tabla de hecho .fact table0, Tabla dimensi$n •
'.'.1 Ta!a de %e/%" 5 fact table6 Esta tabla contiene la re(resentaci$n de un hecho en el sistema, El hecho se ori"ina al momento de re"istrarse las transacciones en el sistema )LTP y debe ser identificado (or el analista con la finalidad de re(resentarlo en el modelo dimensional,
La tabla de hechos debe contener la informaci$n de las medidas /ue se (retende analizar* las cuales ser-n cam(os con ti(os de datos num3ricos* como monto facturado* cantidad de items* "astos* notas, Por ejem(lo4 6n hecho es el re"istro de asistencia de cada trabajador de manera diaria al lle"ar a su centro de labores* con lo cual (odemos analizar las tardanzas mensuales* anuales* entre otros, La tabla de hechos deber- contener Medidas Informaci$n relacionada al hecho .Surro"ate ey0 • •
'.'.' Ta!a di+ensión Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
Esta tabla contiene la informaci$n relacionada con el hecho* el cual es descrito en la tabla de hechos, Las tablas dimensi$n tambi3n re(resentan las distintas o(ciones /ue el usuario tiene (ara (oder analizar su informaci$n* (or ejem(lo* las !entas se (ueden analizar (or clientes* (or (roductos* (or em(leados* etc, 2/uí* clientes* (roductos y em(leados son* (ote ncialmente* tablas dimensi$n, )tra característica de las tablas dimensi$n es /ue contienen un cam(o llamado Surro"ate ey .S0* el cual identifica* Knicamente* un re"istro en la tabla* tal 7S8 debe ser num3rico y correlati!o, Tambi3n* es im(ortante estructurar las tablas dimensi$n con cam(os /ue (uedan ser (otencialmente a"ru(ables, Por ejem(lo4 Productos* analizar las $rdenes desde el (unto de !ista de los (roductos Clientes* analizar las $rdenes desde el (unto de !ista de los clientes • •
'.0. 7RAN(LARIDAD DE DATOS DEL MODELO DIMENSIONAL Esto si"nifica /ue debemos definir el ni!el de detalle de nuestro datamart, En nuestro caso* el objeti!o es analizar los !olKmenes de !entas (or cada (roducto de una orden,
'.8. ESTR(CT(RA DEL MODELO DIMENSIONAL
El modelo dimensional mostrado res(onde a las (re"untas (lanteadas en la secci$n de los indicadores, El modelo dimensional consta de las si"uientes tablas, Ejem(lo4 7Com(arati!os entre meses relacionados a monto de facturaci$n8 Cada re"istro en la tabla fact&!entas contiene el monto facturado (or un (roducto o determinado en una orden, Con este* (odemos obtener la facturaci$n total de manera mensual* con lo cual es factible realizar la com(araci$n, •
FACT TABLE ) FACT3VENTAS Note /ue la tabla de hechos esta com(uesta (or Surro"ate ey y las medidas definidas,
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
Note /ue e+isten A re"istros (ara la orden <;BD, Cada re"istro hace referencia a un ti(o de (roducto solicitado de dicha orden, Note /ue todos los S* son num3ricos y hacen referencia a la informaci$n com(lementaria del hecho .Em(leado* Cliente* Producto etc,0, Note las medidas en la tabla de hechos .enta&Monto* entas&6nidades* etc,0,
TABLA DIMENSION) DIM3CLIEN
Note /ue 4 • •
•
E+isten un Surro"ate ey (or cada re"istro .Cliente0, E+iste (osibilidad de jerar/uizar los cam(os .a"ru(ar la informaci$n (or04 País O #e"i$n O Ciudad o País O Com(a1ía o Com(a1ía o No e+isten !alores nulos en la tabla4 (ara a/uellos clientes /ue el !alor (ara el cam(o re"i$n sea N6L)* a sido reem(lazado (or la cadena de te+to formado4 P2IS Q#EHI)NR
TABLA DIMENSION) DIM3PROD(CTO
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
Note /ue4 E+isten un Surro"ate ey (or cada re"istro .Producto0, • E+iste un cam(o /ue define la re"la de relaci$n entre la cate"oría .Producto&Cate"oria&Sey0 y el •
Producto .Producto&Sey0,
TABLA DIMENSION) DIM3CATE7ORIA
Note /ue4 E+isten un Surro"ate ey (or cada re"istro .cate"oria0, E+iste un cam(o /ue define la re"la de relaci$n con la tabla 'IM&P#)'6CT) .Cate"oria&Sey0, • •
TABLA DIMENSION) DIM3EMPLEADO
Note /ue4 E+isten un Surro"ate ey (or cada re"istro .Em(leado0, E+iste (osibilidad de a"ru(amientos entre los cam(os, • •
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones • •
UNSCH 2016
E+iste un cam(o /ue define la re"la de su(erioridad .Em(leado&Su(erior Em(leado0, E+isten re"istros cuyo cam(o Em(leado&Su(eriorey es nulo, Esto se debe a /ue no todos los em(leados tiene un 9efe,
TABLA DIMENSION) DIM3TIEMPO
Note /ue 4 • •
•
E+iste un Surro"ate ey (or cada re"istro .>echa0, E+iste (osibilidad de jerar/uizar los cam(os, Esta tabla (ermite identificar cada fecha de los a1os /ue se estime con!eniente (ara referenciarlos con las fechas de las $rdenes,
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
'el modelo dimensional .M'0 se (uede concluir4 •
• •
El M,'* est- en es/uema estrella* (ues las tablas dimensi$n se conectan con la tabla de hechos directamente, Las estructuras de las tablas del M,', (resentan diferencias con las del modelo E#, E+iste una recodificaci$n total (ara los c$di"os ori"inales,
'.9. REVISI2N DEL NORTHWIND3STA7E Las or"anizaciones de hoy en día cuentan con innumerables dis(ositi!os de almacenamiento donde a los lar"o de los a1os han ido "uardando sus datos4 archi!os de te+to* hojas de c-lculo* archi!os dbf* di!ersas bases de datos .informi+* oracle* SL* etc0* etc, La elaboraci$n de un modelo dimensional debe entonces* considerar toda estos orí"enes de informaci$n (ara (oder res(onder a (re"untas de an-lisis /ue (udiera re/uerirse sobre estos datos, Es (or ello /ue* antes de trasladar la informaci$n de nuestros orí"enes de datos a nuestro modelo dimensional* es necesario /ue estos sean inte"rados* lim(iados y transformados* de tal manera /ue ase"uren la calidad de los datos /ue se est-n almacenando en el modelo dimensional creado,
!ara ello% es necesario contar con un modelo intermedio en el cual se pueda reali&ar esta tarea de limpie&a " transformación. Esta área es conocida como 'taning rea o (rea de traba)o. La si"uiente "r-fica muestra la ar/uitectura /ue se debe construir (ara el desarrollo del ejem(lo,
En las secciones anteriores* hemos descrito la base de datos Northwind* así como la base de datos Northwind&Mart, Para (oblar el modelo dimensional es necesario /ue los datos hayan sido (re(arados y de(urados, 'icha labor se realiza en el Sta"e 2rea
'.9.1. ESTR(CT(RA DEL MODELO STA7E AREA Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
Note /ue el modelo anterior es muy similar al modelo dimensional, La base de datos del sta"e* es em(leada (ara realizar todas las tareas de lim(ieza y transformaci$n de los datos, )tro (unto a notar es le estructura de las tablas* (or ejem(lo* solo mostraremos dos tablas* las dem-s ser-n e+(uestas en clase,
Tabla : Stg_Producto
La tabla consta de4 Producto&s=ey, El cam(o Surro"ate =ey .identity0 Producto&Codi"o y Cate"oriaProducto&Codi"o, Corres(onden a los c$di"os ori"inales del (roducto y cate"oría, En la misma tabla* est-n los c$di"os ori"inales y surro"ate =ey con la finalidad de encontrar las e/ui!alencias, • •
Tabla : Stg_entas
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
La tabla consta de4 o Los cam(os con los c$di"os ori"inales o Los cam(o con los res(ecti!os Surro"ate ey o Las medidas identificadas,
0. CREACI2N DE (N PRO:ECTO DE ETL (TILI;ANDO S* base de datos )racle o Sybase* archi!os,ULS* archi!os,TUT* etc,0* (ero la data en el ser!idor de an-lisis debe ser uniforme, Para ello* utilizamos SL Ser!er Inte"ration Ser!ices* /ue es la herramienta de ETL (ro(orcionada (or Microsoft y /ue !iene incluida en SL Ser!er B;
•
•
• •
Henerar* dentro de un Proyecto* uno o m-s (a/uetes de ETL /ue nos (ermita trasladar los datos de diferentes orí"enes a un destino, Permitir- administrar mejor los (rocesos de ETL /ue (uedan desarrollarse (ara (oblar el datawarehouse* E+traer la informaci$n de diferentes fuentes de datos a tra!3s de diferentes conectores .(ro!eedores ,Net y (ro!eedores )LE '% nati!os0* Transformar datos mediante tareas de transformaci$n* bKs/ueda* inte"raci$n* combinaci$n* etc,V Controlar la secuencia /ue los datos deben se"uir mediante el control de e!entos de 3+ito y falloV Car"ar la informaci$n a diferentes fuentes de datos, •
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
0.1. ELEMENTOS DE SSIS SSIS est- constituido (or !arios elementos /ue (ermiten construir (a/uetes de ETL com(lejos, Sus com(onentes son4
A. Da$a S"*#/es.= Identifica las cone+iones hacia los di!ersos orí"enes de datos, B. Da$a S"*#/e Vie&.= 'efinen !istas (arciales o totales de los objetos de un 'ata Source, C. Tas>s.= 2sociada a una unidad de transformaci$n* (rocesamiento de datos o transferencia de re"istros de un ori"en a un destino,
D. C"n$#"! F!"&.= 'efine la secuencia l$"ica de transferencia de informaci$n, Por ejem(lo* a tra!3s de los flujos de control se (ermiten controlar la secuencia de transformaci$n* lim(ieza* bKs/ueda* etc, del dato,
E. C"n$aine#.= Permite a"ru(ar diferentes tareas de transformaci$n (ara facilitar el entendimiento o la secuencia de las tareas F. Pa/>ae.= Todas las tareas contenidas dentro de un (a/uete de SSIS,
0.'. CREACI?N DE PA<(ETES DTS La creaci$n de un (a/uete de SL Ser!er Inte"ration Ser!ices se realiza utilizando el entorno de desarrollo 7SL Ser!er %usiness Intelli"ence 'e!elo(ment Studio8, 2hí se crear-n los (a/uetes /ue nos (ermitir- trasladar la informaci$n de la base de datos Norhwind a la base de datos Northwin&Sta"e, Es necesario /ue definamos y aclaremos al"unos (untos antes de iniciar nuestra labor4 'efinici$n del (roblema a solucionar Es un (roblema de mi"raci$n de datosJ Estoy en un (roceso de 'ata arehousin"J Mi (roceso se deber- ejecutar (eri$dicamente o (or Knica !ezJ Ser- un (roceso de ejecuci$n manual* autom-tica o a demandaJ 'efinici$n de las fuentes de datos /ue inter!endrían en dicha soluci$n El ETL in!olucra un solo ser!idor o m-s de unoJ El ETL in!olucra solo ser!idores SL SE#E#J Si e+istiera la necesidad de in!olucrar a otras fuentes de datos* en /u3 (lataformas est-n .)racle* Ms,E+cel* Correo* MyS/l* archi!os (lanos* etc,0J Para el (aso anterior* tenemos los 'ri!ers a(ro(iados (ara (odernos conectar a las fuente de datos en cuesti$nJ 'efinir si la fuente de datos contiene datos /ue son factibles transformarlos (ara (oder obtener datos de calidad 'efinir es(ecíficamente d$nde est-n los datos dentro de la fuente de datos (ara (re(arar las sentencias de acceso a datos •
•
•
•
0.'.1. In#es" a! en$"#n" de! SSIS En el entorno de desarrollo de Microsoft isual Studio, Para "enerar un nue!o (royecto de SSIS ele"imos la o(ci$n 2rchi!o O Nue!o OProyecto, 2(arecer- la (antalla de (royectos en el cual seleccionamos Proyecto de !ntegration Ser"ices y le asi"namos como nombre 7Poblar&Nortwind&)L2P8,
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
2 continuaci$n* se muestra el entorno de desarrollo de los (a/uetes de ETL
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
0.'.'. I+,!e+en$a/ión de ,a@*e$es SSIS El ejem(lo /ue se est- desarrollando re/uiere la im(lementaci$n de dos (a/uetes, El (rimero lle!ar- la data de la base de datos Northwind a Northwind&Sta"e y el se"undo* de Northwind&Sta"e a Northwind&Mart, Creaci$n de la %ase de datos Northwind&Sta"e y Tablas con el si"uiente Scri(t4
create table STG_Cliente ( Cliente_Sey !"T #rimary $ey identity(%&%'& Cliente_Codio "C)*+(,' & Cliente_"ombre n-arc.ar(/0' & Cliente_Com#ania n-arc.ar(/0' & Cliente_Direccion n-arc.ar(10' & Cliente_Cidad n-arc.ar(%,' & Cliente_+eion n-arc.ar(%,' & Cliente_ostal n-arc.ar(%0' & Cliente_a4s n-arc.ar(%,' ' create table STG_rodcto (
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
rodcto_Sey !"T #rimary $ey identity(%&%'& rodcto_Codio int & rodcto_"ombre -arc.ar(3,' & rodcto_5nitario money & Cateoriarodcto_Codio int ' create table STG_Em#leado ( Em#leado_Sey !"T #rimary $ey identity(%&%'& Em#leado_Codio int & Em#leado_*#ellido n-arc.ar(20' & Em#leado_"ombre n-arc.ar(%0' & Em#leado_Direccion n-arc.ar(10' & Em#leado_Cidad n-arc.ar(%,' & Em#leado_+eion n-arc.ar(%,' & Em#leado_ostal n-arc.ar(%0' & Em#leado_ais n-arc.ar(%,' & Em#leado_+e#ort!D !"T & Em#leado_"ombreCom#leto n-arc.ar(60' '
A. O#enes de da$"s Lo (rimero /ue tenemos /ue hacer es conectarnos a la base de datos Norhwind /ue es la fuente de la cual !amos a e+traer los datos, Para ello debemos "enerar una cone+i$n hacia ese ori"en, Para ello realizaremos los si"uientes (asos4 Crear Las cone+iones Nortwind y Nordwind&Sta"e,
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
C#ee e! ,a@*e$e ETL_Poblar_Stage en SSIS Para crear un nue!o (a/uete realizaremos los si"uientes (asos4 En el e+(lorador de soluciones* seleccionar la car(eta de Pa/uetes SSIS .SSIS Pac=a"es0, Clic derecho O Nue!o Pa/uete de SSIS .New SSIS Pac=a"e0, Se ha creado un nue!o (a/uete denominado Pac=a"e<,dts+, Para renombrarlo4 Clic derecho O Cambiar Nombre, #enombre el (a/uete como ETL&Poblar&Sta"e,dts+, Se mostrar- la si"uiente (antalla, '3 clic en Sí •
•
•
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
C"ns$#*i# ,a@*e$e ETL_Poblar_Stage 2hora !amos a construir el (rimer (a/uete de ETL /ue !a a trasladar la informaci$n de la base de datos Norhwind a la base de datos Northwind&Sta"e
Las acti!idades desarrolladas en el ETL son4 o Eliminar los re"istros de las tablas de Sta "e o Im(lementar A >lujos de 'ato .Cliente* Producto* Em(leados0
A/$ividad e!i+ina# #eis$#"s 'el Cuadro de :erramientas seleccionamos de Elementos de flujo la tarea 7 Tarea E#ecutar S$% y la arastramos a 'ise1ador,
Clic derecho sobre la tarea* Editar, Modificamos las si"uientes (ro(iedades
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
Para (robar la tarea tenemos /ue ejecutarla4 Para ello se"uimos los si"uientes (asos, o Seleccionamos la tarea O Clic 'erecho O Ejecutar tarea o La tarea comienza su ejecuci$n, 'urante este (roceso la tarea cambia de icono, o 2l finalizar la tarea (aramos la ejecuci$n .MenK 'e(urar O'etener 'e(uraci$n0
A/$ividad Ca#a# ST73/!ien$e 'el cuadro de herramientas seleccionamos de Elementos de flujo la tarea Tarea &lu#o de datos y la arrastramos a 'ise1ador,
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
Cambiar el nombre de la tarea a Car"ar STH&CLIENTE Clic derecho sobre la tarea y seleccionamos la o(ci$n Editar 2utom-ticamente la (antalla del 'ise1ador cambia a la (esta1a F!*" de da$"s y el Cuadro de herramientas cambia de tareas dis(onibles, • • •
La (antalla de >lujo de datos nos (ermitir- realizar la tarea de ETL, Para ello necesitaremos conectarnos a un ori"en de datos* dise1ar las tareas de transformaci$n (ara* finalmente* trasladarla hacia el destino final, SSIS dentro del flujo de control nos (ermite conectarnos a los si"uientes orí"enes4
Nos (ermite trasladar la informaci$n a los si"uientes destinos
W nos (ermite realizar las si"uientes transformaciones,
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
Para desarrollar nuestra Tarea de flujo de datos se"uiremos los si"uientes (asos4 • • •
'e orí"enes de flujo de datos seleccionamos Origen de O%E D', Cambiamos el nombre de la tarea a Leer Customers, 'amos doble clic sobre la tarea Le er Customers o Clic derecho editar,
2(arece la (antalla con las (ro(iedades de la tarea seleccionada* con las si"uientes o(ciones4
2dministrador de Cone+i$n 2dministrador de Cone+i$n )LE '%, 'efine el ori"en del cual se leer- los datos, Modo de 2cceso a 'atos, 'efine la forma en como se leer-n los datos, Esta (uede ser4 Tabla o !ista o o ariable de Nombre de tabla o Nombre de !ista Comando SL o o Comando SL con !ariable, Para el ejem(lo* debemos considerar los si"uientes !alores4
Lue"o seleccionamos las columnas /ue utilizaremos en el (roceso de car"a, Seleccionamos todas las columnas e+ce(to ContactTitle* Phone y >a+ y damos Clic en 2ce(tar,
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
'el Cuadro de herramientas* en Transformaci$n de flujo de datos seleccionamos la tarea Copiar Columna* y lo asociamos con la tarea %eer Customers, 'ebe mostrarse de la si"uiente manera4
Cambiamos el nombre de la tarea a Co(iar Customers, Si le damos Clic derecho Editar o doble clic sobre la tarea Co(iar Customers* estas muestran la lista de columnas seleccionadas en la tarea Leer Customers, • •
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
>inalmente* debemos indicar hacia d$nde se co(iar-n los datos, 'el Cuadro de herramientas* en 'estinos de flujo de datos seleccionamos la tarea Destino de O%E D'* y lo asociamos con la tarea Copiar Customers, 'ebe mostrarse de la si"uiente manera 4
• •
Cambiamos el nombre de la tarea a Hrabar STH&Cliente, Si le damos Clic derecho Editar o doble clic sobre la tarea Hrabar Cliente,
2(arece el Editor de destino )LE '% con las si"uientes o(ciones4 2dministrador de cone+i$n 2dministrador de cone+i$n )LE '%, 'efine el destino en el cual se "rabaran los datos Modo de acceso a datos, 'efine la forma en como se "rabaran los datos, Esta (uede ser4 Tabla o !ista, o Car"a r-(ida de Tabla o !ista, o ariable de nombre de tabla o Nombre de !ista, o Car"a r-(ida de !ariable de nombre de tabla o Nombre de !ista o Comando SL, o
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
Note /ue no e+iste nin"una columna del ori"en asociada a la columna Cliente&Sey, Esto debido a /ue este cam(o es de ti(o Identity* (or lo /ue el !alor /ue se asi"nar- es administrado (or las base de datos SL Ser!er B;lujo de control* seleccione la tarea Car"ar STH&Cliente y ejecKtelo, 'ebe mostrar la si"uiente (antalla4
2l finalizar la tarea* (are la ejecuci$n .MenK 'e(urar O 'etener 'e(uraci$n0, Hrabe el (royecto .MenK 2rchi!o O Huardar Todo0,
• •
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
A/$ividad Ca#a# ST73P#"d*/$" Para car"ar la informaci$n de (roductos se necesitar- realizar los si"uientes (asos4 'el Cuadro de herramientas* seleccionamos de Elementos de >lujo* la tarea Tarea &lu#o de Datos y la arrastramos a 'ise1ador, Cambiar el nombre de la tarea a Car"ar STH&P#)'6CT) Clic derecho sobre la tarea y seleccionamos la o(ci$n Editar 2utom-ticamente* la (antalla del 'ise1ador cambia a la (esta1a F!*" de Da$"s y el cuadro de herramientas cambia de tareas dis(onibles, •
• • •
En la (antalla de >lujo de datos desarrollaremos las tareas de ETL, Para ello !erificamos /ue ten"amos "eneradas las cone+iones /ue necesitaremos, Esta tarea debe tener el si"uiente c$di"o y utilizar la cone+i$n Northwind creada, 'e orí"enes de flujo de datos seleccionamos Origen de O%E D', Cambiamos el nombre de la tarea a Leer Products, 'amos doble clic sobre la tarea Leer Products o Clic derecho editar, • • •
2(arece la (antalla con las (ro(iedades de la tarea seleccionada* confi"ure las si"uientes (ro(iedades4
'el Cuadro de herramientas* en Transformaci$n de flujo de datos seleccionamos la tarea Con"ersin de Datos* y lo asociamos con la tarea Leer Products, 'ebe mostrarse de la si"uiente manera,
La tarea de Con"ersin de Datos nos (ermitir- modificar los distintos ti(os de datos /ue (odemos encontrar en las distintas fuentes de datos al ti(o de dato del destino, Para el formato de >echa* (ara el tama1o de los cam(os ti(o car-cter* (ara los !alores num3ricos* etc, En el ejem(lo* si !emos el tama1o del cam(o ProductName de la base de datos Northwind y lo com(aramos con el ti(o de dato de base de datos Norhwind&Sta"e* !eremos /ue estas no son i"uales, Para estandarizarlas realizaremos un una con!ersi$n de datos,
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
Las (ro(iedades a incluir son4 Columna de entrada, Seleccionamos la columna /ue /ueremos modificar, 2lias de salida, Indicamos el nue!o nombre /ue se asi"nar- a la columna, Ti(o de datos, Indicamos el nue!o ti(o de dato /ue se le asi"nar- a la columna, Lon"itud, Si el cam(o es car-cter* se debe es(ecificar el tama1o /ue tendr- en nue!o cam(o, Precisi$n O Escala, Si el cam(o es num3rico* se debe es(ecificar el nKmero de enteros y decimales /ue so(ortar-, • • • • •
Note /ue al i"ual /ue en la car"a de la tabla STH&Cliente* (ara la car"a de STH&Producto tam(oco e+iste nin"una columna del ori"en asociada a la columna Producto&Se y, 2l i"ual /ue en el caso anterior* el !alor lo asi"nar- la base de datos SL Ser!er B;lujo de control* seleccione la tarea Car"ar STH&Producto y ejecKtelo, 'ebe mostrar la si"uiente (antalla4
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
X 2l finalizar la tarea (are la ejecuci$n .MenK 'e(urar O 'etener 'e(uraci$n0 X Hrabe el (royecto .MenK 2rchi!o O Huardar Todo0,
A/$ividad Ca#a# ST73E+,!ead"s Para car"ar la informaci$n de (roductos se necesitar- realizar los si"uientes (asos4 'el Cuadro de herramientas* seleccionamos* de Elementos de flujo* la tarea Tarea &lu#o de datos y la arrastramos a 'ise1ador, Cambiar el nombre de la tarea a Car"ar STH&EMPLE2') Clic derecho sobre la tarea y seleccionamos la o(ci$n Editar 2utom-ticamente* la (antalla del 'ise1ador cambia a la (esta1a F!*" de Da$"s y el Cuadro de herramientas cambia de tareas dis(onibles, •
• • •
En la (antalla de >lujo de datos* desarrollaremos las tareas de ETL, Para ello* !erificamos /ue ten"amos "eneradas las cone+iones /ue necesitaremos, Esta tarea debe tener el si"uiente c$di"o y utilizar la cone+i$n Northwind creada, • • •
'e orí"enes de flujo de datos* seleccionamos Origen de O%E D', Cambiamos el nombre de la tarea a Leer Em(loyees, 'amos doble clic sobre la tarea Le er Em(loyees o Clic derecho editar,
2(arece la (antalla con las (ro(iedades de la tarea seleccionada, Confi"ure las si"uientes (ro(iedades4 SELECT Em(loyeeI'* LastName* >irstName* 2ddress* City* #e"ion* PostalCode* Country* #e(ortsTo >#)M Em(loyees
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
La otra es utilizar una tarea SSIS, 'el Cuadro de herramientas* en Transformaci$n de flujo de datos* seleccionamos la tarea Columna Deri"ada y lo asociamos con la tarea Leer Em(loyees, 'ebe mostrarse de la si"uiente manera,
La tarea de Columna Deri"ada nos (ermitir- crear nue!os cam(os a (artir de los cam(os ya e+istentes, Para ello* nos facilitar- un conjunto de funciones de cadena* matem-ticas* de fecha* nulos* con!ersi$n de ti(o* etc, En el ejem(lo* !amos a crear la columna >ullName a (artir de las columnas >irstName y LastName, •
Si le damos Clic derecho Editar o doble clic sobre la tarea Columna Deri"ada* nos a(arecer- la si"uiente (antalla4
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
Las (ro(iedades a incluir son4 Nombre de la columna, Indicamos el nombre d e la columna /ue necesitamos crear, Columna deri!ada, Indicamos si la columna deri!ada ser- una nue!a columna o reem(lazar- a una columna ya e+istente, E+(resi$n, Indicamos el C$mo se !a a crear la nue!a columna deri!adaJ, Ti(o de datos, Indicamos el nue!o ti(o de dato /ue se le asi"nar- a la columna, Lon"itud, Si el cam(o es car-cter* se debe es(ecificar el tama1o /ue tendr- en nue!o cam(o, Precisi$n O Escala, Si el cam(o es nu m3rico* se debe es(ecificar el nKmero de enteros y decimales /ue so(ortar-, • •
• • • •
>inalmente* debemos indicar hacia d$nde se co(iar-n los datos, 'el Cuadro de herramientas* en 'estinos de flujo de datos* seleccionamos la tarea Destino de O%E D' y lo asociamos con la tarea Columna Deri"ada, 'ebe mostrarse de la si"uiente manera 4 • •
Cambiamos el nombre de la tarea a Hrabar STH&Em(leado, 'amos doble clic sobre la tarea Hrabar STH& Em(leado o Clic derecho Editar,
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
UNSCH 2016
Las columnas Em(leado Su(eriorS=ey y Em(leado&Su(erior ser-n com(letados m-s adelante, Para (oder (robar el (a/uete* ubí/uese en >lujo de control* seleccione la tarea Car"ar STH&Em(leado y ejecKtelo, 'ebe mostrar la si"uiente (antalla4
Ing. Richard Zapata Casaerde
Soporte para la Toma de Desiciones
>inalizar la tarea y "rabe el (royecto,
Ing. Richard Zapata Casaerde
UNSCH 2016
