PDF de programación - SQL Server - Tutorial Data Warehouse

BI – Data Warehouse – SQL Server: ETL – Parte I

UTN FRRO - SISTEMAS DE GESTION II

TUTORIAL DATA WAREHOUSE SQL Server: ETL – Parte 1

Este documento consta de:
• Descripción general del Tutorial
• Prerrequisitos
• Pasos generales a seguir
• Pasos específicos a seguir
• Anexo I – Instalación de Microsoft SQL Server
• Anexo II - Almacenamiento y recuperación



2222



BI – Data Warehouse – SQL Server: ETL – Parte I

UTN FRRO - SISTEMAS DE GESTION II



Descripción general del Tutorial



Nuestro objetivo es la construcción de un Data Warehouse para que TDC (The Drinking

Company) pueda atender las siguientes cuestiones:

o Cantidad de litros consumidos y de productos adquiridos por cliente en el tiempo.

o Compra promedio en litros por cliente en el tiempo.

El esquema del Data Warehouse que responderá esos interrogantes es el siguiente:



El proceso de construcción del Data Warehouse será dividido en dos grandes etapas:

o Procesos de extracción, transformación y carga (ETL).

o Procesamiento analítico de datos (OLAP).

Este tutorial tiene como objetivo la creación y población de la Base de Datos del Data



Warehouse, es decir, los procesos ETL.

Esta Base de Datos poseerá las siguientes tablas:



Cliente

Cod_cliente

Cod_sistema
Nombre



Ventas

Cod_cliente

Cod_tiempo
Cantidad
Litros

No es necesario crear la tabla Tiempo debido a que se generará en la etapa OLAP.

Para llegar a construir esta Base de Datos, los analistas determinaron que serán necesarias

las siguientes Fuentes de Datos:



Products.txt (Productos)

CAMPO
PRODUCT_ID
DETAIL
PACKAGE



3333

BI – Data Warehouse – SQL Server: ETL – Parte I

UTN FRRO - SISTEMAS DE GESTION II



Customer_R.txt (Clientes Minoristas)

CAMPO
CUSTOMER_ID
FULL_NAME
BIRTH_DATE
CITY
STATE
ZIPCODE


Customer_W.txt (Clientes Mayoristas)

CAMPO
CUSTOMER_ID
FULL_NAME
BIRTH_DATE
CITY
STATE
ZIPCODE



Sales.mdb (Ventas)



La Base de Datos History_Sales no será utilizada en este Tutorial.



4444

BI – Data Warehouse – SQL Server: ETL – Parte I

UTN FRRO - SISTEMAS DE GESTION II



Prerrequisitos


o Microsoft SQL Server 2000: Enterprise Manager

o Fuentes de datos del Sistema de Gestión de Ventas de TDC:

Customer_W.txt (Clientes Mayoristas)

Customer_R.txt (Clientes Minoristas)

Sales.mdb (Ventas)

o Carpeta de trabajo definida, por ejemplo: C:\SGII2005. Usaremos este nombre

de carpeta para el presente trabajo. En esta carpeta deberán estar copiadas las

fuentes de datos.



5555

BI – Data Warehouse – SQL Server: ETL – Parte I

UTN FRRO - SISTEMAS DE GESTION II



Pasos generales a seguir



Parte I

1. Crear la Base de Datos de la Staging Area.

2. Cargar los datos desde las Fuentes de Datos a la Staging Area.



Parte II

3. Crear la Base de Datos del Data Warehouse.

4. Realizar los procesos de Extracción, Transformación y Carga (ETL).



6666

BI – Data Warehouse – SQL Server: ETL – Parte I

UTN FRRO - SISTEMAS DE GESTION II



Pasos específicos a seguir



1. Crear la Base de Datos de la Staging Area.

Un Staging Area es un área de almacenamiento intermedio, que nos permitirá

una mejor manipulación de los datos a la hora de realizar los procesos ETL

subsiguientes.

a. Ingresaremos al Enterprise Manager y al área de trabajo.

Clic en Inicio  Programas  Microsoft SQL Server  Enterprise

Manager. En el árbol de carpetas de la Consola, doble clic en Microsoft

SQL Servers. Doble clic en SQL Server Group para ver la lista de

servidores. Seleccionaremos el servidor local, que en nuestro caso es

localhost o el nombre de la PC local.

Doble clic en el servidor seleccionado para establecer una conexión con

SQL Server. Luego de que la conexión se ha establecido, aparecerá una

lista de carpetas.

b. Crearemos la Base de datos de la Staging Area y le daremos un nombre.

En Enterprise Manager, clic con el botón derecho en Databases 

New Database…. Escribir el nombre de la base de datos “TDC Staging

Area”. Clic en Aceptar.



2. Cargar los datos desde las Fuentes de Datos a la Staging Area.

a. Ingresaremos en el DTS Package Designer.



7777

BI – Data Warehouse – SQL Server: ETL – Parte I

UTN FRRO - SISTEMAS DE GESTION II

Clic con el botón derecho en Data Transformation Services  New

Package.



b. Crearemos una conexión para las fuentes de datos del Sistema de

Gestión de Ventas que sean archivos de

texto: Products.txt,

Customer_R.txt y Customer_W.txt.

Para Products.txt: En DTS Package Designer  Menú Connection 

Text File (Source). En el cuadro de texto New connection vamos a

escribir un nombre para la conexión: “Products Source”. Clic en el botón

situado a la derecha del cuadro de texto File Name ([…]) y

seleccionamos

el

archivo

fuente: C:\SGII2005\Fuentes de

datos\Stock\Products.txt. Una vez seleccionado el archivo y cerrado el

cuadro de diálogo de selección, clic en Properties…, clic en Siguiente >,

verificar que la opción de delimitador sea Other, y en el cuadro de texto

aparezca el símbolo pipe ( | ), clic en Finalizar. Clic en Aceptar.

Para Customer_R.txt: En DTS Package Designer  Menú Connection

 Text File (Source). En el cuadro de texto New connection vamos a

escribir un nombre para la conexión: “Retail Source”. Clic en el botón

situado a la derecha del cuadro de texto File Name ([…]) y

seleccionamos

el

archivo

fuente: C:\SGII2005\Fuentes de

datos\Ventas\Customer_R.txt. Una vez seleccionado el archivo y

cerrado el cuadro de diálogo de selección, clic en Properties…, tildar la

opción First row has column names, clic en Siguiente >, verificar que

la opción de delimitador sea Tab, clic en Finalizar. Clic en Aceptar.

Para Customer_W.txt: En DTS Package Designer  Menú

Connection  Text File (Source). En el cuadro de texto New



8888



BI – Data Warehouse – SQL Server: ETL – Parte I

UTN FRRO - SISTEMAS DE GESTION II

connection vamos a escribir un nombre para la conexión: “Wholesale

Source”. Clic en el botón situado a la derecha del cuadro de texto File

name ([…]) y seleccionamos el archivo fuente: C:\SGII2005\Fuentes

de datos\Ventas\Customer_W.txt. Una vez seleccionado el archivo y

cerrado el cuadro de diálogo de selección, clic en Properties…, tildar la

opción First row has column names, clic en Siguiente >, verificar que

la opción de delimitador sea Tab, clic en Finalizar. Clic en Aceptar.



c. Crearemos un archivo UDL que nos servirá de vínculo de datos.



Un archivo de vínculos de datos se utiliza para crear y administrar

mediante una interfaz OLE DB las conexiones entre el equipo cliente y los

datos almacenados en un origen de información. Se trata de un archivo

de texto cuya extensión es *.UDL (Universal Data Link) y que se puede

tener como referencia para futuras conexiones a bases de datos. El

archivo generalmente tiene la extensión UDL, aunque puede tener la

extensión que deseemos, pero en éste último caso, no se abrirá el cuadro

de diálogo Propiedades de vínculo de datos cuando se haga doble clic

sobre el archivo en el Explorador de Windows.

Si se usan conexiones a vínculos de datos (data link connections) es

posible mantener la información de la conexión para un origen de datos

OLE DB en un archivo externo UDL, y luego leer esta información dentro

de la conexión a vínculos de datos cada vez que se ejecuta el proceso.

Implementar un vínculo de datos implica:

Crear un archivo UDL

Almacenar la información de la conexión en el archivo UDL

Crear una conexión a vínculos de datos que use el archivo UDL



9999



BI – Data Warehouse – SQL Server: ETL – Parte I

UTN FRRO - SISTEMAS DE GESTION II

En el Explorador de Windows  carpeta C:\SGII2005\Fuentes de

datos\Ventas\, botón derecho  Nuevo  Documento de texto.

Renombrar “Nuevo Documento de texto.txt” a Sales Source.udl.

Importante: antes de renombrar el documento de texto y cambiar la

extensión del archivo, verificar que el checkbox Ocultar las extensiones

de archivo para tipos de archivo conocidos esté destildado. Esto lo

hacemos en el Explorador de Windows, Menú Herramientas 

Opciones de carpeta… solapa Ver.



d. Configuraremos el archivo Sales Source.udl.

Para ello deberemos definir primero un origen de datos mediante ODBC.

Con este fin, abriremos el Administrador de orígenes de datos ODBC,

accesible desde el Panel de Control, o desde la carpeta Herramientas

Administrativas dentro del Panel de Control.

Dentro del Administrador de orígenes de datos ODBC, solapa DSN

de Sistema (System DSN), botón Agregar… Microsoft Access

Driver (*.mdb), botón Finalizar. En el cuadro de texto Nombre del

origen de datos, colocar “Sales”. Botón Seleccionar, seleccionamos el

archivo C:\SGII2005\Fuentes de datos\Ventas\Sales.mdb. Clic en

Aceptar, clic en Aceptar.



Ya tenemos el origen de datos ODBC. Ahora vamos a trabajar con el

archivo UDL. En el Explorador de Windows, situados en la carpeta

C:\SGII2005\Fuentes de datos\Ventas\, doble clic en Sales

Source.udl. En la solapa Proveedor, seleccionar Microsoft OLE DB

Provider for OD