¡Bienvenidos, muy buenos días tardes o noches , según donde me leas !
En el mundo actual, donde los datos se han convertido en un activo crucial para las organizaciones, la capacidad de manejar, analizar y procesar estos datos se ha vuelto esencial. El texto proporcionado subraya la importancia de poseer un conjunto diverso de habilidades técnicas para enfrentar los desafíos contemporáneos en el ámbito de la analítica de datos, especialmente en el contexto de Fp20Analytics.
El dominio de herramientas como Power BI es solo el comienzo; un analista integral debe estar equipado con conocimientos en la obtención de datos de diversas fuentes, incluyendo servicios web, y la capacidad de automatizar tareas repetitivas. Las herramientas ETL (Extract, Transform, Load) juegan un papel crucial en este proceso, facilitando la transformación y el transporte de datos desde su origen hasta sistemas como bases de datos o data warehouses, donde pueden ser analizados y visualizados efectivamente.
El enfoque en la creación de modelados de datos y el manejo avanzado de bases de datos subraya la necesidad de una comprensión profunda de cómo los datos pueden ser estructurados y consultados para obtener información valiosa. La habilidad para modelar datos en entornos BI y DW (Business Intelligence y Data Warehousing) es especialmente relevante, ya que permite a las organizaciones transformar grandes volúmenes de datos brutos en información estructurada y útil.
La propuesta de realizar una serie de pasos en cadena, utilizando herramientas como Power Automate y SQL Server, ilustra la aplicación práctica de estas habilidades en un proyecto real. Esto no solo ayuda a fortalecer el portafolio personal de un analista, sino que también brinda una experiencia invaluable en el manejo de herramientas multifacéticas en un solo proyecto. Desde la automatización de la recopilación de datos hasta la creación de complejos modelos de datos y la ejecución de procesos ETL, estas habilidades son fundamentales para navegar en el vasto y en constante evolución mundo de la analítica de datos.
Herramientas que utilizaremos:
- Power Automate
- SQL Server
- SSIS
Lenguajes utilizados:
- T-SQL
- VBA
AHORA EMPECEMOOOOOS!!!!
Cómo crear una ruta completa sobre cómo hacer el back-end y sus fases, con nuestros retos de datos en Fp20Analytics.
Primero, para ser un analista integral debes manejar diferentes habilidades, no solo es manejar Power BI, debes manejar procesos para realizar descargas de información, de servicios web, automatizar tareas, usar herramientas ETL, crear modelados de datos BI DW, manejar muy bien bases de datos a nivel de código.
Así que con este reto de datos te propongo realizar estos pasos sencillos en cadena, para practicar tu portafolio, habilidades, y ver qué puedes colocar en práctica múltiples herramientas en un solo proyecto.
Pasemos a la fase de obtener la data, en este caso es a través de un entorno web. Primero lo realizaremos con la herramienta llamada Power Automate, pero utilizaremos la versión de desktop. Primero, como obtenemos específicamente Power Automate, accederemos a https://www.office.com/apps/, imagen 1.
Luego de acceder a nuestra web y dar clic sobre la web, para obtener específicamente Power Automate Desktop, lo realizaremos dando clic. Imagen 2
Luego de ello en la siguiente ventana de diálogo damos clic donde dice launch app. Imagen 3
Damos clic en la opción get the latest version en la cual manejaremos el proceso de desarrollo completo, para que nos descargue la última versión. Imagen 4
Posterior a descargar el fichero damos clic y en la ventana de diálogo damos siguiente y siguiente para direccionar la ruta final de instalación. Imagen 5
Al iniciar Power Apps, ahora desarrollaremos específicamente nos solicitará iniciar sesión, En la ventana de diálogo damos clic donde dice específicamente nuevo flujo, creamos este proceso y lo podemos crear de manera eficiente y óptima. Imagen 6 y 7
Debemos gestionar este proceso en el cual manejaremos cada uno de estos gestionables, usaremos la propiedad Descargar desde la web y en este lienzo de flujo de trabajo, se puede usar el arrastrar y soltar, que hacemos arrastramos y soltamos. Imagen 8 y 9
Luego sale una ventana de diálogo en el cual utilizaremos la dirección URL específica en la cual manejaremos la URL exactamente donde está el fichero. Ahora bien tenemos dos métodos get y post que diferencias nos hacemos acá que el método get solamente es con un simple clic, pero si necesitamos loguearnos por cualquier motivo de que sea otro caso de negocio debes usar el post.
Luego nos indica si deseamos ver si guardamos el fichero o guardamos el archivo en una memoria en variable, y en disco sería en local en nuestro caso usaremos en local, y manteniendo el nombre específico, y la carpeta de destino evidentemente donde se almacenará nuestro fichero. En variables producidas es donde nos indicará si se realizó el proceso.
Guardamos y damos en el botón de ejecutar si todo fue bien nos indicará un exitoso y el fichero estará en la ruta indicada, si no fue bien nos indicará una alerta. Imagen 10
Segunda fase: Luego de que tenemos aterrizada nuestra base de datos en la cual manejamos el proceso completo en el cual tenemos el desarrollo, la base de datos de destino final que usaremos será SQL Server. Ante este caso debemos realizar específicamente estos pasos. Primero debemos crear nuestra base de datos la cual llamaremos en mi caso IMAGEN 1.
Posterior a ello debemos crear nuestras tablas de aterrizaje o stage para aterrizar nuestros datos y trabajarlos con T-SQL. Para identificar específicamente el contenido completo con el cual trabajaremos, es decir los nombres de las columnas, el tamaño de cada cadena de datos y los tipos de datos.
Existen muchas maneras para identificar desde un fichero xlsx el tamaño de los datos y las columnas, más sus tipos de datos. Imagen 2
Una de las maneras que lo hago es con código VBA que tengo para este tipo de casos cuando tengo que crear la tabla de aterrizaje el cual es muy sencillo en VBA, el cual el código devuelve según la hoja evaluada el nombre de la
columna con un guion bajo si existe espacio entre las palabras, sumado a ello identifica el tipo de dato y sobre específicamente si son enteros o de tipo float. Imagen 3
Con este código y la información dada en la ventana inmediato podremos ver específicamente lo que necesitamos para crear el código T-SQL. Imagen 4
y ejecutamos específicamente en los cuales podemos realizar los procesos en los que manejamos, ahora pasamos a editarlo manualmente. Creamos las tablas manualmente, recordemos primero las de aterrizaje. Imagen 5
Ahora procedemos a crear las conexiones SSIS Integration Services. Ahora para la creación del ETL utilizaremos Integration Services para trasladar nuestra información de cada hoja del Excel hacia SQL Server. Imagen 1
La lógica que aplicaremos en estos procesos, es crear una variable con la ruta del fichero, y luego de ello una variable donde se almacena el nombre de la hoja a almacenar los datos, en este caso la idea es llevar estos datos hacia las tablas de aterrizaje luego las transformaciones necesarias las realizaremos con T-SQL.
Ahora bien teniendo claro la lógica de este sencillo ETL, lo primero será usar la propiedad contenedor de bucles foreach, porque esta propiedad por qué la idea es que se gestione en bucle recorriendo cada hoja del Excel y así poder tener específicamente aterrizada cada hoja de nuestro Excel en las tablas que creamos anteriormente. Imagen 2
Luego para que esto funcione, creamos dos variables una que almacena la ruta donde se encuentra nuestro fichero local, y otro que almacenará en cada recorrido el nombre de nuestra hoja evaluada. Imagen 3
Para el siguiente caso la idea es, ahora seleccionaremos la opción FOREACH ADO. IMAGEN 4
Con este paso, ahora realizamos la conexión a nuestro fichero, es decir le damos nueva conexión y en la ventana de diálogo que nos de damos clic para dar clic en nueva. Imagen 5
Pasamos a seleccionar el proveedor llamado Microsoft Office 12.0. Imagen 6
Donde indica el servidor o el nombre de la ruta, alojaremos la ruta completa de nuestro fichero. Imagen 7
Posterior a ello seleccionamos el botón que dice all o todas, y buscamos el apartado Extended Properties y escribimos Excel 12.0. Imagen 8
Luego de nuestra conexión, ahora pasamos a la asignación de la variable donde se almacenará el nombre de tu hoja, y el índice será el número dos, dado que necesitamos el nombre de la hoja a recorrer. Imagen 9
En el apartado colección, dentro de este seleccionamos el desplegable esquema, eligiendo la opción llamada tablas, acá es donde cobra sentido colocar el número dos, dado que si presionamos en el botón de Establecer restricciones podemos ver en la ventana emergente, 4 ítems comenzando de 0 a 3 el conteo, elegimos el 2 como índice porque necesitamos el table_schema. Imagen 10
Ahora tenemos listo el bucle que recorrerá el fichero en Excel y cada hoja. Procedemos a añadir dentro del bucle la propiedad llamada Tarea flujo de datos, pero esta solo nos servirá para redireccionar por medio de la cadena de flujo cada acción a la siguiente Tarea flujo de datos, en modo cascada, insertamos tres Tarea flujo de datos más una por cada hoja del Excel, como loveremos en la imagen. Imagen 11
Ahora bien le asignamos a cada Tarea flujo de datos, en cada Tarea flujo de datos vamos a conectar la hoja del Excel hacia nuestra base de datos SQL. Imágenes 12, 13, 14 
Con este proceso podremos alimentar nuestras tablas de aterrizaje, llevar el xlsx hacia SQL Server ejecutamos nuestro Little ETL y de maravilla.
Creación del modelado de datos Ahora pasamos a crear específicamente las tablas dimensiones y la tabla de hechos, el modelado de datos los crearemos con T-SQL. Luego de tener nuestros datos ya en las tablas de aterrizaje, pasamos directamente a validar los datos.
Primero de nuestro modelo de datos crearemos la tabla calendario desde cero. Para hacerlo más genial, creamos, primero el proceso en el cual identificar, el mínimo y el máximo de la columna fecha, la cual está en la tabla de aterrizaje llamada XmasDataset. –Primero encontrar la fecha máxima de la tabla XmasDataset
select max(fecha) from XmasDataset
select min(fecha) from XmasDataset
Con esto realizado declaramos dos variables y les seteamos justo estos procesos. — Obtener el rango de fechas de XmasDataset SELECT @FechaInicio = MIN(fecha) FROM XmasDataset; SELECT @FechaFin = MAX(fecha) FROM XmasDataset;
Luego de este proceso gestionamos la creación de la tabla para almacenar. CREATE TABLE Calendario ( Fecha DATE PRIMARY KEY );
Ahora gestionaremos — Inicializar la variable para el bucle. DECLARE @FechaActual DATE = @FechaInicio;
Ahora procedemos a crear el código para que el bucle se realice.
— Bucle para insertar fechas en la tabla Calendario WHILE @FechaActual <= @FechaFin BEGIN INSERT INTO Calendario (Fecha) VALUES (@FechaActual);
SET @FechaActual = DATEADD(DAY, 1, @FechaActual);
END
Luego finalizamos con un select para cerrar este proceso. Ahora pasamos a realizar específicamente las columnas, mes, día, año, semana, trimestre, nombre mes, nombre día. Imagen 6
Ahora procedemos a crear específicamente, para crear la tabla de dimensiones específica en la cual usaremos. La lógica de esta columna es crear las combinaciones completas de todos los minutos y horas para tener nuestra dimensión y poderla relacionar con nuestra tabla de hechos. Cuando estamos creando las tablasç
— Crear la tabla de dimensión de tiempo CREATE TABLE DimTiempo ( HoraMinuto CHAR(5) PRIMARY KEY, Hora INT, Minuto INT ); Creamos la tabla calendario con estos pasos para así realizar las modificaciones necesarias y con estos pasos poder modificar y trabajar nuestros cálculos.
— Poblar la tabla con todas las combinaciones de horas y minutos DECLARE @Hora INT = 0; DECLARE @Minuto INT; WHILE @Hora < 24 BEGIN SET @Minuto = 0; WHILE @Minuto < 60 BEGIN INSERT INTO DimTiempo (HoraMinuto, Hora, Minuto) VALUES ( RIGHT(‘0’ + CAST(@Hora AS VARCHAR(2)), 2) + ‘:’ + RIGHT(‘0’ + CAST(@Minuto AS VARCHAR(2)), 2), @Hora, @Minuto ); SET @Minuto = @Minuto + 1; END; SET @Hora = @Hora + 1; END;
— Consultar la tabla para verificar SELECT * FROM DimTiempo;
Con estos sencillos pasos tenemos nuestra dimensión tiempo, cumpliendo con las reglas de creación.
Ahora pasamos a la creación de la dimensión, —Crear dimensión Customer_Age_Range
select distinct (customer_age_range) into Dim_customer_age_range from XmasDataset
select * from Dim_customer_age_range
Luego de ello pasamos a crear la dimensión, pero en esta dimensión agruparemos tres columnas Product_Name, Product_Category, Product_Type.
Se realiza la consulta. SELECT DISTINCT Product_Name, Product_Category, Product_Type FROM XmasDataset;
Para poder validar las diferentes combinaciones con la tabla de hechos, y teniendo un nivel de normalización con las tres columnas.
Ahora pasamos a la creación de la dimensión Dim_Purchase. –Creamos la dimensión Dim_Purchase select * from XmasDataset
select distinct (Purchase_Type) into Dim_Purchase from XmasDataset
select * from Dim_Purchase imagen 8
Posterior a ello la dimensión Dim_Country, esta dimensión la con la siguiente consulta SQL select distinct city, Country into Dim_Country from XmasDataset
select * from Dim_Country
—Dimensión de Gender
select distinct gender into Dim_gender from XmasDataset
–Dimensión Dim_Xmas_Budget
select distinct Xmas_Budget into Dim_Xmas_Budget from XmasDataset
–Dimensión Dim_Payment_method
select distinct Payment_method into Dim_Payment_method from XmasDataset
imagen 9
Con todas estas consultas tenemos creadas las dimensiones y la tabla de hechos que utilizaremos en nuestro informe.
Para cerrar esta explicación, es evidente que la gestión y análisis de datos en el entorno de Fp20Analytics representa un desafío intrigante y complejo, pero a la vez sumamente gratificante. La ruta descrita, que abarca desde la recopilación de datos hasta la creación de modelos de datos y la implementación de procesos ETL, destaca la importancia de un enfoque multifacético y bien integrado en la analítica de datos.
El uso de herramientas como Power BI, Power Automate, SQL Server, y el empleo de lenguajes como T-SQL y VBA, ilustra una combinación poderosa de tecnologías y prácticas que pueden transformar radicalmente la forma en que las organizaciones acceden, procesan y visualizan sus datos. Estas habilidades no solo son esenciales para los analistas de datos y los desarrolladores de back-end, sino que también son cruciales para cualquier profesional que busque tomar decisiones informadas y basadas en datos en el mundo empresarial actual.
Al final, lo que se destaca es la versatilidad y la capacidad de adaptación necesarias en el campo de la analítica de datos. A medida que las tecnologías y las necesidades del mercado evolucionan, también lo hacen las herramientas y estrategias para manejar los datos. Aprender a navegar y dominar estas herramientas no solo es beneficioso para el desarrollo profesional, sino que también es indispensable para impulsar el crecimiento y el éxito en un entorno empresarial cada vez más impulsado por datos.
En resumen, este viaje a través del manejo de datos en Fp20Analytics no solo es una demostración de habilidades técnicas, sino también una invitación a explorar las infinitas posibilidades que los datos ofrecen en el mundo moderno. ¡Vamos adelante con este emocionante desafío!
Comparto el código VBA: https://github.com/vicente2121/VBA-COLUMNAS.git
Comparto el código T-SQL: https://github.com/vicente2121/SQLReto12.git
Business Intelligence Technical
Vicente Antonio Juan Magallanes.
