¡Bienvenidos, muy buenos días tardes o noches , según donde me leas !
¡Bienvenido a una emocionante jornada de descubrimiento y aprendizaje en el mundo del análisis de datos y la integración de servicios! En este viaje, exploraremos cómo dar tus primeros pasos en ETL con Integration Services y luego profundizaremos en el fascinante mundo de SQL. Desde la creación de consultas hasta procesos de modificación utilizando T-SQL, nos sumergiremos en un océano de posibilidades para transformar datos y responder a preguntas de negocio de manera eficiente.
A medida que avanzamos, desentrañaremos el poder de Python en Excel y exploraremos dinámicas emocionantes. Sin embargo, hoy nos centramos en SQL y SSIS, adentrándonos en el modelado de datos en SQL Server y respondiendo las preguntas más desafiantes desde la perspectiva de T-SQL.
¿Estás listo para este apasionante recorrido? ¡Entonces, comencemos!
En este artículo, te guiaré a través de la creación de un ETL básico que, aunque simple, será una valiosa experiencia tanto para los recién llegados como para aquellos que buscan expandir sus conocimientos. Primero, importaremos un archivo Excel hacia SQL Server utilizando Integration Services. Luego, procederemos a realizar diversas transformaciones de datos para garantizar su correcta integración.
Sigue conmigo mientras creamos dimensiones esenciales como Dim_Empresa, Dim_Ubicacion, Dim_Horario y Dim_Producto, además de la tabla de hechos final, Tabla_Hechos. Con cada paso, profundizaremos en el proceso, desde la identificación de datos categóricos hasta la creación de claves subrogadas y la vinculación de tablas.
Quien dice que con nuestro retos de datos, no das tus primeros pasitos en ETL con integration services, luego creas consultas , SQL , creas procesos y modificaciones, con T-SQL.
Hace dos días, vimos Python power query en Excel y hasta dinámicas.
Hoy vamos ahora con SQL y SSIS, Modelado de datos en SQL SERVER, y al final Respondiendo las preguntas de negocio desde CON T-SQL.
¡¡¡¡Empecemos!!!!
Creamos un ETL hiper básico, pero que para muchos que son nuevos y otros que no podrán colocar en práctica (que de todo se aprende y tenemos que aprender.)
Pero primero es importar nuestro fichero Excel así, con integration services, hacia SQL Server.
Creamos un flujo de datos imagen 1
Luego creamos un conector de Excel imagen 2
Debemos realizar las conversiones de los datos para que tenga correcto efecto
Imagen 3
Ahora procedemos a crear la conexión a nuestra base de datos imagen 4
Ejecutamos y cargamos los datos imagen 5
Ahora estando la información en nuestra base de datos.
En la tabla de aterrizaje (Recuerda que el código estará en mi GitHub lo que realice en T-SQL), procedemos a la fase dos
Para estos pasos deberíamos aplicar específicamente varias modificaciones, es decir entender que datos categóricos tenemos y que tipos de datos manuales manejamos, ojo, con este paso, dado que es fundamental de entender y manejar eficientemente, es decir identificar que datos categóricos tenemos que será nuestras dimensiones, y que datos aditivos serán de nuestra tabla de hechos.
- El primer dato categórico será, y creo que estarás de acuerdo que será Empresa , con empresa y empresa bandera , lo primero será obtener las combinaciones únicas de cada una.
–combinaciones unica de Empresa y empresa bandera
select distinct Empresa, Empresa_Bandera from stage
Ahora bien debemos crear la tabla dimensión para este caso, que sera ,
Dim_Empresa
Creamos la tabla y creamos un campo auto incrementable, es decir crearemos una clave subrogada o artificial auto incrementable, posterior a ello crearemos un proceso en el cual manejamos.
Insertamos los datos con un insert sencillo.
Y tendríamos cargada nuestra primera dimensión. Vamos a por la dimensión Dim_Ubicacion
–Creamos la seiguinete dimesion que es ubicacion
–combinaciones unica de Direccion,Localidad,Provincia,Region,Latitud,Longitud
select distinct Direccion,Localidad,Provincia,Region,Latitud,Longitud from stage
Posterior a ello creamos la tabla , desnormaliada, recuerda que estamos enfocando al modelo en estrella y prevalece la desnormalización(Claro esta sin perder datos)
–Creamos la tabla , especificamente para poder trabajar con estos procesos
create table Dim_Ubicacion
(
Id_Ubicacion int identity(1,1),
Direccion varchar(150),
Localidad varchar(150),
Provincia varchar(150),
Region varchar(150),
Latitud varchar(150),
Longitud varchar(150)
)
Posterior a ello creamos el insert
–Insertamos los datos en Dim_Ubicacion
insert into Dim_Ubicacion (Direccion,Localidad,Provincia,Region,Latitud,Longitud)
select distinct Direccion,Localidad,Provincia,Region,Latitud,Longitud from stage
Seguimos con la Dimension Horario
–Dimension Horario
–combinaciones unica de Empresa y empresa bandera
select distinct Horario_Tipo from stage
recordemos luego de seleccionar los únicos , procedemos a crear la tabla
–Creamos la tabla , específicamente para poder trabajar con estos procesos
create table Dim_Horario
(
Id_Horario int identity(1,1),
Horario_Tipo varchar(50)
)
Luego de crear la tabla pasamos a realizar el insert de los horarios únicos
–creamos el el insert de la dimension horario
insert into Dim_Horario (Horario_Tipo)
select distinct Horario_Tipo from stage
Ahora pasamos a la Dim_Calendario, en este caso realizamos un paso diferente, dado que tenemos dos columnas año_y_mes y fecha, para saber cuál abarcar en totalidad mis fechas, como obtenemos esto con los siguientes scripts
SELECT MIN(TRY_CONVERT(DATE, Fecha, 103))
FROM stage;
SELECT MAX(TRY_CONVERT(DATE, Fecha, 103))
FROM stage;
Te preguntaras porque específicamente, los transformar a fecha porque mi tabla stage es específicamente pueda obtener el mínimo real y el maximo real.
Ahora en estos pasos la columna que podemos tener en estos casos podemos manejar el especifico proceso de cada uno de estos, utilizaremos fechas porque es la columna que nos sirve, dado que abarca la fecha completa.
Imagen 6
Ahora en este proceso debemos manejarlo, y ya con estos rangos validados, pasamos a crear el código el cual nos creara un proceso automatizado para las fechas, con la dimensión calendario.
Imagen 7
Evidentemente, en estos casos, podemos, manejar diferentes tipos de columnas, mes , años , todos estos pasos son necesarios y solo modificamos específicamente los pasos básicos dinámicamente.
—Procedemos a crear la tabla
create table Dim_Calendario
(
Fecha date,
Mes int,
MesNombre varchar(15),
Año int,
)
—Vereficamos especificamente como queda la tabla
Select * from Dim_Calendario
Para el siguiente paso debemos gestionar.
Hasta este momento tenemos diferente Dimensiones, Dim_Calendario, Dim_Ubicacion, Dim_Horario y Dim_Empresa
Ahora pasamos específicamente , a las dimensión de producto , es decir en estos casos , debemos manejar ahora la creación de la dimensión producto.
Primero encontramos los productos únicos para este caso — Pasamos a crear la dimension Producto
select distinct Producto from stage
Ahora posterior a ello pasamos a crear específicamente , la tabla , de nuevo con la clave subrogada.
–Pasamos a crear la tabla de dimension
create table Dim_Producto
(
Id_Producto int identity(1,1),
Producto varchar(50)
)
Por ultimo creamos el insert para poderlo gestionar
–Creamos el proceso para insertar los datos
insert into Dim_Producto (Producto)
select distinct Producto from stage
Ahora luego de tener las dimensiones creadas pasamos a crear la tabla de hechos final.
En esta tabla de hechos , hemos realizado , diferentes fases en las cuales manejamos , en cada proceso, esto quiere decir , sacaremos las columnas.
Las columnas que no usaremos para responder las preguntas de negocio en T-SQL.
Es decir la columna Año_y_Mes no va , evidentemente las columnas Localidad, Provincia, Region, Latitud y Longitud, estas no seran colocadas ,dado que primero el datos de granularidad mas fina es dirección , quiere decir que usaremos este para el leef tjoin y actualizar el Id_Ubicacion, en Empresa_Bandera que tiene la granularidad mayor.
En el insert desde nuestra tabla stage hacia la tabla de hechos usaremos los datos de mayor granularidad
—Creamos la tabla de hechos final
Create table Tabla_Hechos
(
Id_Empresa int,
Identificacion_Fiscal varchar(100),
Empresa varchar(150),
Id_Ubicacion int,
Direccion nvarchar(150),
Id_Producto int,
Producto varchar(255),
Id_Horario varchar(255),
Horario_Tipo varchar(255),
Precio money,
Fecha date
)
Y creamos el insert into
–insertar datos en la tabla de hechos
insert into Tabla_Hechos(Empresa, Identificacion_Fiscal, Direccion,Producto,Horario_Tipo,Precio,Fecha )
Select Empresa, Identificacion_Fiscal, Direccion,Producto,Horario_Tipo,Precio,Fecha
from stage
Procedemos a actualizar las la columna id_empresa , según nuestra dimensión dado que esta id traerá lo que neceistamso
–procedemos a vincular los id de empresa ons los lef join , Y HACER EL UPDATE
UPDATE a
SET a.Id_Empresa = b.Id_Empresa
FROM Tabla_Hechos AS a
LEFT JOIN Dim_Empresa AS b ON a.Empresa = b.Empresa;
Replicamos las mismas actualizaciones, para la siguiente dimensiones que sera ubicación .
Pero en estos casos gestionamos , y replicamos con las demás dimensiones
–Procedemos a la dimension Dim_Horario
UPDATE a
SET a.Id_Horario = b.Id_Horario
FROM Tabla_Hechos AS a
LEFT JOIN Dim_Horario AS b ON a.Horario_Tipo = b.Horario_Tipo;
–Por ultimo procedemos a la tabla Dim_Producto
UPDATE a
SET a.Id_Producto = b.Id_Producto
FROM Tabla_Hechos AS a
LEFT JOIN Dim_Producto AS b ON a.Producto = b.Producto;
Como estamos viendo los procesos , queda la tabla Tabla_Hechos.
Con este procesos , tenemos un modelo dimensional de toda regla.
Lo ultimo que faltaría es eliminar , las columnas categóricas , y dejaríamos , las llaves foráneas , necesarias , para las dimensiones y responder las preguntas de negocio , y luego dejando tambien solo los datos aditivos y de fecha( ojo con la fecha tambien podríamos creamos un campo clave subrogado , en lugar de la propia fecha)
Desde nuestros primeros pasos en ETL con Integration Services hasta la profundización en SQL y SSIS, hemos explorado diversas técnicas y herramientas que nos permiten transformar datos en información valiosa para las decisiones empresariales.
Durante este viaje, hemos creado un ETL básico, pero fundamental, que nos ha proporcionado una comprensión más profunda de cómo importar, transformar y cargar datos desde fuentes diversas hacia SQL Server. Hemos explorado la creación de dimensiones esenciales como Dim_Empresa, Dim_Ubicacion, Dim_Horario y Dim_Producto, así como la tabla de hechos final, Tabla_Hechos, que nos brinda un modelo dimensional completo para abordar preguntas de negocio con confianza y precisión.
Al adoptar técnicas como la creación de claves subrogadas, la desnormalización de datos y la vinculación de tablas, hemos construido una sólida base para el análisis de datos en entornos empresariales. Estamos equipados con herramientas y conocimientos que nos permitirán enfrentar desafíos futuros con mayor seguridad y eficacia.
En resumen, este viaje ha sido un recordatorio de que el análisis de datos no se trata solo de números y tablas, sino de comprender el contexto empresarial y utilizar herramientas adecuadas para convertir datos en insights accionables. Espero que este artículo haya sido informativo y estimulante, y te anime a seguir explorando y aprendiendo en el emocionante mundo del análisis de datos. ¡Hasta la próxima aventura!
Vicente Antonio Juan Magallanes.
