Download documento

ETL: Extracción, transformación y carga en un
DATAWAREHOUSE
Indice
1.- Definir el ámbito del proyecto de DW/ETL
2.- Crear una base de datos para el datawarehouse.
3.- Crear tablespaces/usuarios/objetos.
4.- Invocar a los procedimientos para la primera carga del dw.
5.- Invocar a los procedimientos para las cargas de refresco del dw.
6.- Crear objetos adicionales como vistas materializadas en el dw.
1.- Definir el ámbito del proyecto de DW/ETL
Para realizar un ejemplo sencillo, voy a trabajar con las siguientes
especificaciones:
Tanto la base de datos operacional (orcl) como el datawarehouse
(dw), van a residir en la misma máquina virtual con Windows 2003
Server. La versión de rdbms que usamos es la 11.2.0.1.0 para
Windows.
Los datos que vamos a analizar, proceden de las tablas del esquema
HR de la base de datos orcl.
Se trata de simular un datawarehouse, con la evolución de los
salarios de los empleados, leyendo las tablas EMPLOYEES y
DEPARTMENTS.
Para poder leer los datos de orcl desde dw, trabajaremos con un
dblink.
En la base de datos dw, habrá dos tablespaces: uno llamado ODS
(operational data store),
para guardar tablas intermedias o
temporales
y otro llamado DW, con las tablas de
dimensiones/hechos definitivas.
En la base de datos dw, crearemos un usuario llamado dw,
propietario de las tablas del ODS y del DW.
2.- Crear una base de datos para el datawarehouse.
Creamos una base de datos llamada dw, con dbca.
3.- Crear tablespaces/usuarios/objetos.
Estos son los scripts que usaremos en este tutorial, para
crear/cargar/refrescar objetos en el DW. Residen en la carpeta
C:\DW.
Veamos lo que contiene crear_objetos_dw.sql:
Veamos el modelo desde Sql*Developer, con el DataModeler
Estos son los detalles del modelo:
4.- Invocar a los procedimientos para la primera carga del dw.
Comenzamos la primera
departments del esquema
creando tablas de stage o
tablas serán las fuentes
EMPLOYEES_DIM.
carga, leyendo las tablas employees y
hr, a través del database link lorcl, y
temporales, en el tablespace ODS. Esas
de datos de la tabla de dimensión
Veamos el contenido del script:
Lo ejecutamos desde sqlplus:
Veamos el contenido de la tabla EMPLOYEES_DIM:
La segunda carga, consiste en rellenar los datos de la tabla de
dimensión TIME_DIM, con datos procedentes del fichero
YEARS.TXT. La carga se realiza con sql*loader.
Veamos el contenido de los ficheros cargar_times_dim.ctl y
years.txt:
Invocamos al script cargar_times_dim.bat, que realiza la carga con sqlldr:
Veamos qué ha cargado y qué rechazado:
La tercera carga, consiste en crear varias tablas temporales con los datos
de EMPLOYEES, que ya están en el tablespace ODS, y simular un
crecimiento salarial para los años 1998, 1999 y 2000. En el año 1998 el
salario es el original de la tabla EMPLOYEES, en el 1999 se incrementa el
salario en 1.1 y en el 2000 se incrementa en 1.2.
Finalmente, se insertan estos salarios en la tabla SALARY_FACT
(particionada por la columna YEAR).
Veamos el contenido del script de la tercera carga:
Invocamos al script CARGAR_SALARY_FACT.sql desde Sql*plus.
Veamos los datos:
Comprobemos que se han alojado en las particiones correspondientes:
5.- Invocar a los procedimientos para las cargas de refresco del
dw.
Primero vamos a proceder al refresco de la tabla de dimensión TIME_DIM,
cargando un nuevo año con sql*loader. Luego vamos a refrescar la tabla de
dimensión EMPLOYEES_DIM con una tabla externa y una sentencia
merge. Finalmente añadiremos nuevos datos en la tabla SALARY_FACT
para los salarios de los empleados correspondientes al año 2001, añadiendo
la partición correspondiente.
Comencemos con el refresco de TIME_DIM.
Veamos el contenido de los scripts:
Invoquemos al script desde el S.O y comprobemos después los datos desde
sql*plus.
El segundo refresco es de EMPLOYEES_DIM. Crearemos una tabla
externa, con los datos de los cambios en la dimensión empleados. La
actualización de la tabla EMPLOYEES_DIM la realizaremos con una
sentencia MERGE que leerá los datos de la tabla externa y los comparará
con la tabla EMPLOYEES_DIM. Si ya existe el empleado, lo actualiza, si
no existe, lo inserta.
Invocamos al script desde SQL*PLUS, y creamos la tabla externa:
Comprobemos los datos del fichero refresco_employees_ext:
Y ahora seleccionemos datos de la tabla externa:
Ahora procedemos a refrescar la tabla de dimensión EMPLOYEES_DIM,
con una cláusula merge, que actualice a los empleados 198 y 199 e inserte
el 206.
Invoquemos al script:
Comprobemos los datos y validamos el merge:
El tercer refresco corresponde a la tabla de hechos SALARY_FACT.
Invoquemos al script para realizar este refresco:
Comprobemos los datos de la tabla y de la nueva partición:
6.- Crear objetos adicionales como vistas materializadas en el
dw.
Veamos el script de creación:
Ejecutemos el script:
Comprobemos los datos de la vista materializada: