Carga de Datos de Fuentes de Datos en
Excel a un Data Warehouse en Access
Illustration by Freepik Storyset
ESTAMOS POR EMPEZAR
INF303
Bases de Datos para la Gestión
INF303
Bases de Datos
para la Gestión
Hoy en Bases de Datos para la Gestión
Procedimiento para Crear un Cubo en Access a partir de archivos
Excel
Limpieza y Transformación de datos
Creación de un almacenamiento intermedio para Dimensiones y
Tabla de Hechos
Carga de datos en un Data Warehouse en Access
Conectar un Data Warehouse en Access a Power BI
3
INF303 Bases de Datos para la Gestión
Procedimiento para Crear un Cubo en
Access a partir de archivos Excel
INF303 Bases de Datos para la Gestión
Procedimiento para Crear un Cubo en Access
a partir de archivos Excel
5
INF303 Bases de Datos para la Gestión
Procedimiento para Crear un Cubo en Access
a partir de archivos Excel
6
INF303 Bases de Datos para la Gestión
Procedimiento para Crear un Cubo en Access
a partir de archivos Excel
7
INF303 Bases de Datos para la Gestión
Procedimiento para Crear un Cubo en Access
a partir de archivos Excel
Modelo
Multidimensional
ROLAP
8
INF303 Bases de Datos para la Gestión
Procedimiento para Crear un Cubo en Access
a partir de archivos Excel
Modelo
Multidimensional
ROLAP
ETL
9
INF303 Bases de Datos para la Gestión
Cargar fuentes de datos en
Excel al cubo en Access
Una empresa que vende sus productos por internet ha realizado varias ofertas, pero sus clientes no han respondido
adecuadamente, esto causa que sus ventas no sean exitosas.
La empresa posee información descriptiva, demográfica y crediticia de sus clientes y las compras que han realizado en el
último tiempo gracias a su sistema de e-commerce, pero desea realizar estrategias para poder mejorar las ventas en
distintos segmentos de clientes.
Descripción de la organización Empresa que vende productos por internet
Descripción del problema que se quiere Tienen pocas ventas así que quieren hacer
resolver ofertas dirigidas a cierto tipo de clientes para
aumentarlas
Beneficios para la organización Aumentar ingresos por ventas
Usuarios a los que apoyará con el Data ▪ Dueños de la empresa
Warehouse ▪ Trabajadores que realizan compras a
proveedores de los productos
10
INF303 Bases de Datos para la Gestión
Cargar fuentes de datos en
Excel al cubo en Access
Una empresa que vende sus productos por internet ha realizado varias ofertas, pero sus clientes no han respondido
adecuadamente, esto causa que sus ventas no sean exitosas.
La empresa posee información descriptiva, demográfica y crediticia de sus clientes y las compras que han realizado en el
último tiempo gracias a su sistema de e-commerce, pero desea realizar estrategias para poder mejorar las ventas en
distintos segmentos de clientes.
Requerimiento Segmentar los clientes que aceptan y los que no
aceptan ofertas
Categoría Descriptivo
Descripción Para dirigir de mejor manera las ofertas enviadas a
los clientes, se debe segmentar la actual cartera de
clientes. Para enviar ofertas adecuadas a cada
segmento.
11
INF303 Bases de Datos para la Gestión
Cargar fuentes de datos en
Excel al cubo en Access
Una empresa que vende sus productos por internet ha realizado varias ofertas, pero sus clientes no han respondido
adecuadamente, esto causa que sus ventas no sean exitosas.
La empresa posee información descriptiva, demográfica y crediticia de sus clientes y las compras que han realizado en el
último tiempo gracias a su sistema de e-commerce, pero desea realizar estrategias para poder mejorar las ventas en
distintos segmentos de clientes.
Requerimiento Encontrar clientes que generan mayor cantidad de
ingresos
Categoría Descriptivo
Descripción Al saber que tipo de clientes generan mayor cantidad
de ingresos, se podrían diseñar planes para poder
ofrecer nuevos productos y ofertas
12
INF303 Bases de Datos para la Gestión
Cargar fuentes de datos en
Excel al cubo en Access
13
INF303 Bases de Datos para la Gestión
Cargar fuentes de datos en
Excel al cubo en Access
Para realizar este análisis, se dispone de una planilla Excel con las ventas de los últimos 4 meses,
donde se describen además algunas características de los clientes que realizaron esas compras
Antes de poder analizar, se deben procesar estos datos en Excel para cargarlos en una Base de datos Access
según el Cubo del Modelo Multidimensional ROLAP presentado. Así después se podrá conectar ese cubo a
cualquier herramienta OLAP (por ejemplo Power BI)
14
INF303 Bases de Datos para la Gestión
PREGUNTAS
INF303 Bases de Datos para la Gestión
Limpieza y Transformación
de datos
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
17
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
18
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Falta columna que
represente id_respuesta
19
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
20
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Hay que realizar una
transformación para
almacenar mes y año
por separado
21
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
22
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Perfilado de datos con Excel Cantidad total de filas
▪ Ver filas del archivo (solo archivos pequeños)
Para Atributos de las dimensiones calcular Contar valores nulos
▪ Cantidad total de filas =CONTAR.BLANCO(RANGO)
▪ Cantidad de valores nulos (o vacíos)
▪ Valores distintos Sumar valores
=SUMA(RANGO)
Para Hechos o Medidas calcular
▪ Suma de todos los valores Valores distintos
23
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Dimensiones: Cantidad total de filas
24
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Dimensiones: Cantidad de Valores nulos
25
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Dimensiones: Valores distintos para Género
Seleccionar ambas columnas de la Copiar solo registros únicos a
dimensión género, y todas las filas otra celda, en este caso a la W5
26
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Dimensiones: Valores distintos para Actividad
Seleccionar ambas columnas de la Copiar solo registros únicos a
dimensión actividad, y todas las filas otra celda, en este caso a la Z5
27
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Dimensiones: Valores distintos para Tiempo
Seleccionar la columna mes que contiene Copiar solo registros únicos a
información para la dimensión tiempo, y otra celda, en este caso a la AC5
todas las filas
28
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Dimensiones: Valores distintos para RespuestaOferta
Seleccionar la columna compra ofertas mes Copiar solo registros únicos a
que contiene información para la otra celda, en este caso a la AC5
dimensión RespuestaOferta, y todas las filas
29
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Hechos: Suma de todos los valores
Vamos a usar la suma para verificar
posteriormente que no hayamos
perdido datos
30
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Perfilado de datos
Acciones necesarias:
▪ Solucionar valores nulos en Actividad
▪ Renombrar valores de COMPRA OFERTA MES a
"Positiva" (SI) y "Negativa" (NO)
31
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Solucionar valores nulos en Actividad
Seleccionar todas las columnas con datos y agregar filtro
32
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Solucionar valores nulos en Actividad
Filtrar solo las filas que tengan actividad vacía
Como son 760 filas no podemos perder estos datos, entonces vamos a crear
una nueva actividad llamada SIN INFORMACIÓN con actividad_id = 4
33
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Renombrar valores de COMPRA OFERTA MES a "Positiva" (SI) y "Negativa" (NO)
34
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Revisemos la equivalencia entre columnas del archivo Excel y las columnas de las tablas del ROLAP
35
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Revisemos la equivalencia entre columnas del archivo Excel y las columnas de las tablas del ROLAP
Las siguientes columnas del ROLAP no existen en la planilla, así
que hay que crearlas
▪ RespuestaOferta
¡¡Muy importante porque es una
o id_respuesta Clave Primaria y Foránea!!
▪ Tiempo
o mes Estas columnas las podemos crear
o año después, porque son atributos de la
dimensión Tiempo
36
INF303 Bases de Datos para la Gestión
Limpieza y Transformación de datos
Nueva columna RespuestaOferta id_respuesta
Duplicamos la columna tipo_respuesta y Usando Buscar – Reemplazar, cambiamos
la renombramos a id_respuesta Positiva por 1 y Negativa por 2 teniendo la
columna id_respuesta seleccionada
37
INF303 Bases de Datos para la Gestión
PREGUNTAS
INF303 Bases de Datos para la Gestión
Creación de un almacenamiento
intermedio para Dimensiones y
Tabla de Hechos
INF303 Bases de Datos para la Gestión
Almacenamiento Intermedio
Ya están listos los datos base que irán en el Data Warehouse en Access, ahora falta crear una estructura
previa a la carga donde se pueda realizar el cálculo de las medidas de la tabla de hechos
40
INF303 Bases de Datos para la Gestión
Almacenamiento Intermedio
Vamos a crear nuevas hojas en el archivo Excel, una por cada tabla del Modelo Multidimensional ROLAP
41
INF303 Bases de Datos para la Gestión
Almacenamiento Intermedio
Dimensión Genero
En la Hoja Género vamos a copiar los valores distintos
que habíamos encontrado para cada columna
La primera fila deberá ser el nombre de los atributos
de la tabla Genero del Modelo ROLAP
42
INF303 Bases de Datos para la Gestión
Almacenamiento Intermedio
Dimensión RespuestaOferta
En la Hoja RespuestaOferta vamos a copiar los valores
distintos que habíamos encontrado para cada columna
La primera fila deberá ser el nombre de los atributos
de la tabla RespuestaOferta del Modelo ROLAP
43
INF303 Bases de Datos para la Gestión
Almacenamiento Intermedio
Dimensión Actividad
En la Hoja Actividad vamos a copiar los valores
distintos que habíamos encontrado para cada columna
La primera fila deberá ser el nombre de los atributos
de la tabla Actividad del Modelo ROLAP
44
INF303 Bases de Datos para la Gestión
Almacenamiento Intermedio
Dimensión Tiempo
En la Hoja Tiempo vamos a copiar los valores distintos
que habíamos encontrado para cada columna
La primera fila deberá ser el nombre de los atributos
de la tabla Tiempo del Modelo ROLAP
45
INF303 Bases de Datos para la Gestión
Almacenamiento Intermedio
Dimensión Tiempo
Completamos valores de los atributos que faltan
46
INF303 Bases de Datos para la Gestión
Almacenamiento Intermedio
Tabla de Hechos Ventas
La tabla de Hechos debe tener calculados los valores
numero de compras e ingreso compras para todas las
combinaciones posibles de los valores distintos de las
dimensiones
Dimensión Cantidad de Valores distintos
Genero 2
RespuestaOferta 2
Actividad 4
Tiempo 4
Filas Tabla de Hechos Ventas = 2 x 2 x 4 x 4 = 64
47
INF303 Bases de Datos para la Gestión
Almacenamiento Intermedio
Tabla de Hechos Ventas
Suma de todos los valores numero_compras donde
▪ id_actividad sea 4
▪ id_genero sea 1
▪ id_tiempo sea 202004
▪ id_respuesta sea 1
48
INF303 Bases de Datos para la Gestión
Almacenamiento Intermedio
Tabla de Hechos Ventas
=SUMAR.SI.CONJUNTO(
DatosOriginal!$N$2:$N$16001; numero_compras
DatosOriginal!$H$2:$H$16001;Ventas!A21; id_actividad
DatosOriginal!$E$2:$E$16001;Ventas!B21; id_genero
DatosOriginal!$L$2:$L$16001;Ventas!C21; id_tiempo
DatosOriginal!$Q$2:$Q$16001;Ventas!D21 id_respuesta
) 49
INF303 Bases de Datos para la Gestión
Almacenamiento Intermedio
Tabla de Hechos Ventas
Si la suma que hicimos en perfilado coincide con la de la tabla
de hechos, entonces no hemos perdido datos
50
INF303 Bases de Datos para la Gestión
PREGUNTAS
INF303 Bases de Datos para la Gestión
Carga de datos en un Data Warehouse
en Access
INF303 Bases de Datos para la Gestión
Carga de datos en Access
53
INF303 Bases de Datos para la Gestión
Carga de datos en Access
54
INF303 Bases de Datos para la Gestión
Carga de datos en Access
Importar Dimensiones
55
INF303 Bases de Datos para la Gestión
Carga de datos en Access
Importar Dimensiones
56
INF303 Bases de Datos para la Gestión
Carga de datos en Access
Importar Dimensiones
57
INF303 Bases de Datos para la Gestión
Carga de datos en Access
Importar Dimensiones
58
INF303 Bases de Datos para la Gestión
Carga de datos en Access
Importar Dimensiones
Repetir hasta importar todas las dimensiones
59
INF303 Bases de Datos para la Gestión
Carga de datos en Access
Importar Tabla de Hechos
60
INF303 Bases de Datos para la Gestión
Carga de datos en Access
Importar Tabla de Hechos
61
INF303 Bases de Datos para la Gestión
Carga de datos en Access
Importar Tabla de Hechos
Seleccionar todos los id
62
INF303 Bases de Datos para la Gestión
Carga de datos en Access
Conectar Claves Primarias con Claves Foráneas
63
INF303 Bases de Datos para la Gestión
Carga de datos en Access
Conectar Claves Primarias con Claves Foráneas
64
INF303 Bases de Datos para la Gestión
Carga de datos en Access
Conectar Claves Primarias con Claves Foráneas
Conectar todas las Claves Primarias de las dimensiones con las Foráneas de la Tabla de Hechos
65
INF303 Bases de Datos para la Gestión
Carga de datos en Access
Conectar Claves Primarias con Claves Foráneas
Guardar cambios en Base de Datos de Access
66
INF303 Bases de Datos para la Gestión
PREGUNTAS
INF303 Bases de Datos para la Gestión
Conectar un Data Warehouse en
Access a Power BI
INF303 Bases de Datos para la Gestión
Conectar DW a Power BI
69
INF303 Bases de Datos para la Gestión
Conectar DW a Power BI
Marcar todas
70
INF303 Bases de Datos para la Gestión
Conectar DW a Power BI
71
INF303 Bases de Datos para la Gestión
Conectar DW a Power BI
72
INF303 Bases de Datos para la Gestión
PREGUNTAS
INF303 Bases de Datos para la Gestión
INF303
Bases de Datos
para la Gestión
