¡Bienvenidos, muy buenos días tardes o noches , según donde me leas !
El modelado de datos dimensional es un proceso esencial en el diseño y desarrollo de bases de datos para la toma de decisiones empresariales efectivas. Este enfoque, centrado en la optimización para el análisis y la consulta, se compone de tres fases fundamentales: conceptual, lógica y física. Cada una de estas fases desempeña un papel crucial en la creación de un modelo dimensional que sea robusto, eficiente y altamente funcional para satisfacer las necesidades de análisis de una organización.
La fase conceptual marca el inicio del proceso, donde se establece una comprensión profunda de los requisitos del negocio y se identifican las entidades, relaciones y atributos clave que darán forma al modelo dimensional. Esta etapa proporciona la base conceptual sobre la cual se construirá el resto del modelo, asegurando que se capturen de manera precisa las necesidades y objetivos del negocio.
A continuación, la fase lógica transforma el modelo conceptual en una representación más estructurada y detallada, donde se definen las tablas, columnas y relaciones con mayor precisión. Aquí es donde se toman decisiones críticas sobre la normalización de datos, la optimización de consultas y la integridad referencial, asegurando que el modelo sea eficiente y adaptable a las consultas analíticas.
Finalmente, la fase física convierte el modelo lógico en una implementación concreta en una plataforma de bases de datos, como SQL Server o PostgreSQL. En esta etapa, se definen los tipos de datos, índices y restricciones que permitirán almacenar y manipular los datos de manera efectiva. Además, se consideran aspectos prácticos como el rendimiento y la escalabilidad del sistema.
En conjunto, estas tres fases del modelado de datos dimensional son vitales para el éxito de cualquier proyecto de inteligencia empresarial o análisis de datos. Proporcionan una estructura sólida y coherente que permite a las organizaciones convertir datos en información valiosa y accionable, facilitando la toma de decisiones informadas y estratégicas en todos los niveles. En última instancia, un modelo dimensional bien diseñado y correctamente implementado es una herramienta poderosa que impulsa la eficiencia operativa, la innovación y el crecimiento empresarial.
Ahora el siguiente post, se trata de realizar, las tres fases de un modelado de datos, fase conceptual, fase lógica, y fase física.
Primero vamos a centrarnos, en la fase conceptual:
Esta fase es la conceptual:
Imagen para fase conceptual.
Entender los Requisitos del Negocio: Comienza identificando las necesidades del negocio o cliente y sumado a ello los objetivos comerciales que se deben o desean abordar, para así responder estas preguntas con los datos suministrados por la empresa.
Identificar Entidades y Relaciones: Basándote en los requisitos comerciales o las preguntas de negocio, debemos identifica las principales entidades (objetos de interés) y sus relaciones, pero a nivel general, en esta fase no entramos a detalle, de evaluar, de muchos a 1 o viceversa. Por ejemplo, con el ejemplo que traigo hoy, son datos de ventas, las entidades pueden incluir Clientes, Productos y Ventas, con relaciones entre ellas (por ejemplo, un cliente realiza una compra de varios productos).
Definir Atributos: Determina los atributos clave de cada entidad, es decir los nombres de las columnas que tendrán las tablas, que responderán las preguntas, Estos son los detalles específicos que se capturan sobre cada entidad. Por ejemplo, para la entidad Cliente, tenemos el ID cliente
Crear un Diagrama Conceptual: Representa visualmente las entidades, relaciones y atributos en un diagrama conceptual. Es decir, debemos representarlo visualmente, para poder aterrizar la primera idea inicial.
Ahora, teniendo esta idea aterrizada, debemos tener en nuestras manos, un ejemplo de los datos o una muestra de los datos , según la granularidad del tiempo a analizar , a la semana, mes o año, en este caso.
Para este ejemplo he creado un código en sql sever, para poder manejar este proceso, o el fichero csv o txt, con los datos, para realizar este ejemplo, comparto específicamente en mi github , el cual encontraras en los comentarios.
Nota especial , abro paréntesis imaginario, acá tambien comparto el código para exportalo a CSV desde SQL SEVER, pero debes leer ciertas consideraciones primero antes de , entrar en detalle con este paso a paso, acá te las dejo .
La velocidad de ejecución del proceso de exportación de datos a un archivo CSV dependerá de varios factores, incluyendo el rendimiento del servidor, la complejidad de la consulta SQL, la estructura de la tabla, la red, y la capacidad de escritura del disco donde se guarda el archivo txt, entre otros.
Dado que estás tratando con 60,000 registros, es posible que el proceso tome algunos segundos o incluso varios minutos(Como fue mi caso), dependiendo de las condiciones mencionadas anteriormente. En general, la exportación de 60,000 registros debería ser manejable rápida , sencilla y no debería tardar demasiado en completarse.
Es importante tener en cuenta, ojito, que la ejecución de comandos xp_cmdshell puede tener cierta sobrecarga como es claro y puede no ser la opción más eficiente o mas fiable, para exportar grandes volúmenes de datos(Recuerda son 60.000 mil registros es poco). Si encuentras que el proceso es demasiado lento, considera otras alternativas como el uso de herramientas de ETL (Extract, Transform, Load) como SSIS (SQL Server Integration Services) que es mi favorita, y en el futuro tendrás post sobre esta herramienta, o la exportación de datos directamente desde una aplicación cliente .
Además, ten presente y recuerda que mientras se ejecuta el proceso de exportación, es posible que pueda haber una degradación temporal del rendimiento del servidor SQL, es decir va ir mas lentitud con otros procesos que hagas en vuelo, especialmente si el servidor ya está bajo carga pesada(Peor la situación ). Es una buena práctica realizar pruebas en un entorno de desarrollo, como yo , lo hice en mi maquina local, o de pruebas, antes de ejecutar procesos de exportación en un entorno de producción.
Ahora teniendo en nuestras manos, los datos, que son el txt de una empresa minorista, las ventas de un año.
El cliente, para nuestro ejemplo, hemos tenido el punto de contacto, y nos ha trasladado las preguntas de negocio que dese contestar.
Ejemplifico las preguntas de negocio que tenemos en nuestro punto de contacto, es decir las preguntas que tiene el cliente la minorista
¿Cuál es el total de ventas realizadas durante un período específico?
Esta pregunta nos ayudará a entender la cantidad total de ventas en un rango de fechas determinado.
¿Cuál es el producto más vendido por categoría?
Nos permitirá identificar los productos más populares dentro de cada categoría.
¿Cuál es el total de ventas por cliente?
Nos dará una idea de quiénes son nuestros clientes más importantes y cuánto están contribuyendo a nuestras ventas totales.
¿Cuál es el ingreso total generado por ventas?
Esta pregunta nos ayudará a comprender la situación financiera general de la tienda minorista.
¿Cuál es la cantidad promedio de unidades vendidas por transacción?
Nos permitirá entender mejor el comportamiento de compra de nuestros clientes.
¿Cuál es el promedio de precios unitarios por categoría de producto?
Nos ayudará a comprender la distribución de precios dentro de cada categoría de productos.
¿Cuál es la ubicación geográfica con mayor número de ventas?
Nos dará información sobre las áreas geográficas donde nuestra tienda tiene más éxito.
¿Cuál es el producto con el precio unitario más alto y bajo?
Nos permitirá identificar los productos que tienen un precio más alto y bajo, lo que puede influir en nuestras estrategias de precios y marketing.
Todas esta recopilación e información es fundamental, para poder crear la primera fase, Recordemos la conceptual.
Mi herramienta predilecta para desarrollar, modelos conceptuales, lógicos… se llama
draw.io, es fácil de usar en la web, fácil descargar, solo es irnos manos a la obra.
En la siguiente web, llamada draw.io, es fácil de usar en la web, fácil descargar, solo es irnos manos a la obra
Ahora, teniendo esto claro, redacto la idea del modelo conceptual.
Identificar las entidades principales:
Basándonos en las preguntas de negocio, de nuestra empresa minorista de ejemplo, las principales entidades podrían ser, según las preguntas de negocio:
Producto
Venta
Cliente
Definir los atributos de cada entidad:
Para cada entidad, tablas de hechos y las dimensiones, es vital que identifiquemos los atributos relevantes que necesitaremos para responder a las preguntas de nuestro cliente, recordemos, que en esta fase, no especificamos el tipo de datos, el tamaño de las columnas, llaves primarias , llaves foráneas.
Para la entidad Producto, podríamos tener: ProductoID, ProductoNombre, Categoria.
Para la entidad Venta, podríamos tener: VentaID, ProductoID, FechaVenta, Unidades, PrecioUnitario, CostoTotalPorUnidad, ClienteID.
Para la entidad Cliente, podríamos tener: ClienteID, ClienteNombre,
Establecer relaciones entre las entidades:
Basándonos en las preguntas que implican análisis de datos a través de diferentes entidades, tablas de hechos y de dimensiones, establecemos relaciones entre ellas, recordemos que en este paso no especificamos, si es de muchos a uno, ni la cardinalidad, en esta fase no aplica.
Ahora debemos dibujar, solo una línea de conexión, entre las entidades
Una venta está relacionada con un producto y un cliente, por lo tanto, hay relaciones entre Venta y Producto y entre Venta y Cliente (Por su puesto ya te imaginas quienes serán nuestras dimensiones y quien será nuestra tabla de hechos).
Refinar el modelo y resolver ambigüedades:
Revisamos el modelo conceptual para asegurarnos de que sea completo y coherente.
Resolvemos cualquier ambigüedad que pueda surgir durante este proceso, o que identifiquemos cualquier irregularidad, ausencia de datos, carencia para responder las preguntas.
Por ejemplo, podríamos preguntarnos si necesitamos algún atributo adicional, categoría extra, o si necesitamos solicitar más datos para satisfacer las necesidades de análisis.
Posterior a este análisis pasamos a la fase Lógica
Basándonos en las preguntas planteadas, podemos desarrollar un modelo lógico que refleje las entidades (Nuestras tablas de hechos y dimensiones), atributos (Nuestras columnas) y relaciones necesarias para responder a esas preguntas. Aquí hay una propuesta para el modelo lógico, como nuestro caso de negocio, en este camino si que necesitaremos, el tipo de columnas, el tipo de relación, las claves primarias y secundarias:
Entidades (Tablas de hechos y dimensiones):
Producto: Representa los productos vendidos en la tienda.
Atributos: ProductoID (identificador único), Nombre, Descripción, Precio, Categoría.
Venta: Representa una transacción de venta de un producto.
Atributos: VentaID (identificador único), ProductoID (clave foránea para referenciar el producto vendido), ClienteID (clave foránea para referenciar al cliente), Fecha, Cantidad, PrecioUnitario.
Cliente: Representa a los clientes que realizan las compras.
Atributos: ClienteID (identificador único), Nombre, Ciudad, Estado, País.
Relaciones:
Una venta está asociada a un único producto (relación uno a uno con Producto).
Un producto puede estar asociado a múltiples ventas (relación uno a muchos con Venta).
Una venta está asociada a un único cliente (relación uno a uno con Cliente).
Un cliente puede realizar múltiples compras (relación uno a muchos con Venta).
Consideraciones adicionales:
Se pueden agregar otras tablas o atributos según sea necesario para responder a preguntas más detalladas o para adaptarse a los requisitos específicos del negocio.
Los atributos de las tablas se pueden normalizar o desnormalizar según los requisitos de consulta y rendimiento.
Este modelo lógico proporciona una base sólida para responder a las preguntas de negocio planteadas inicialmente, en el punto de contacto con el cliente, o el caso de negocio que tengas y puede servir como punto de partida para el diseño e implementación de la base de datos, es decir como bases de ideas plasmadas para su realizacion. Una vez que el modelo lógico está definido, podemos proceder a convertirlo en un modelo físico y luego implementarlo en el sistema de gestión de bases de datos (por ejemplo, SQL Server,o tambien postgresql, en el mismo power query de power bi.).
Para desarrollar el modelo físico en SQL Server basado en las preguntas de negocio y en el modelo conceptual propuesto, vamos a definir las tablas con las estructuras y relaciones necesarias para almacenar los datos y responder a las preguntas planteadas. A continuación, proporcionaré una descripción del modelo físico:
Modelo Físico este es el mas divertido (a mi gusto):
Tabla Producto:
ProductoID (INT, Primary Key): Identificador único del producto.
ProductoNombre (VARCHAR(10)): Nombre del producto.
Categoria (VARCHAR(10)): Categoría del producto.
Tabla Venta:
VentaID (INT, Primary Key): Identificador único de la venta.
ProductoID (INT, Foreign Key a Producto): Identificador del producto vendido.
FechaVenta (DATE): Fecha en que se realizó la venta.
Unidades (INT): Cantidad de unidades vendidas.
PrecioUnitario (DECIMAL(10, 2)): Precio unitario del producto.
CostoTotalPorUnidad (DECIMAL(10, 2)): Costo total por unidad.
ClienteID (INT, Foreign Key a Cliente): Identificador del cliente que realizó la compra.
Tabla Cliente:
ClienteID (INT, Primary Key): Identificador único del cliente.
ClienteNombre (VARCHAR(11)): Nombre del cliente.
Ciudad (VARCHAR(8)): Ciudad del cliente.
Estado (VARCHAR(7)): Estado del cliente.
Pais (VARCHAR(5)): País del cliente.
Explicación del Modelo Físico:
La tabla Producto almacena información sobre los productos disponibles en la tienda minorista, incluyendo su identificador único, nombre y categoría.
La tabla Cliente almacena información sobre los clientes que realizan compras, como su identificador único, nombre, ciudad, estado y país.
La tabla Venta registra cada transacción de venta, incluyendo el identificador único de la venta, el identificador del producto vendido, la fecha de la venta, la cantidad de unidades vendidas, el precio unitario del producto, el costo total por unidad y el identificador del cliente que realizó la compra.
Implementación en SQL Server:
A continuación, se muestra cómo se implementaría este modelo físico en SQL Server:
Luego de tener las tablas creadas, creamos la consulta para la inserción de datos
Insertamos los datos en cada tabla.
Por que la importancia de alimentar para un modelado dimensional:
Consistencia de datos: Al utilizar DISTINCT, se asegura de que cada registro único se inserte solo una vez en la dimensión correspondiente. Esto evita inconsistencias y errores en los datos que podrían surgir si se permiten registros duplicados.
Eficiencia en el diseño del modelo: Al eliminar duplicados en las dimensiones, se simplifica el diseño del modelo y se facilita su comprensión y mantenimiento. Esto también puede mejorar el rendimiento de las consultas al reducir la cantidad de datos redundantes que deben procesarse.
Integridad referencial: Al cargar datos en las dimensiones, es importante mantener la integridad referencial con los datos en las tablas de hechos. Utilizando DISTINCT, se garantiza que cada registro único tenga una correspondencia única en las tablas de hechos, lo que facilita la creación de relaciones entre dimensiones y hechos.
Calidad de los datos: Eliminar duplicados en las dimensiones ayuda a garantizar la calidad de los datos al reducir la posibilidad de errores y redundancias en los datos. Esto es especialmente importante en un entorno de análisis de datos donde la precisión y la consistencia son fundamentales.
Ahora explico brevemente las consultas.
NSERT INTO Producto: Esta consulta inserta datos en la tabla Producto. Selecciona registros distintos de la tabla Ventas para las columnas ProductoID, ProductoNombre y Categoria, y los inserta en la tabla Producto. La cláusula DISTINCT garantiza que no se inserten duplicados en la tabla Producto.
INSERT INTO Cliente: Similar al primer caso, esta consulta inserta datos en la tabla Cliente. Selecciona registros distintos de la tabla Ventas para las columnas ClienteID, ClienteNombre, Ciudad, Estado y Pais, y los inserta en la tabla Cliente. Al igual que antes, la cláusula DISTINCT garantiza que no se inserten duplicados en la tabla Cliente.
INSERT INTO Venta: Esta consulta inserta datos en la tabla Venta. Selecciona todas las columnas de la tabla Ventas y las inserta directamente en la tabla Venta. No se utiliza la cláusula DISTINCT en este caso, lo que significa que se insertarán todos los registros de la tabla Ventas en la tabla Venta
Respondiendo a las preguntas de negocio de nuestro cliente
- Total, de ventas realizadas durante un período específico:
Creamos una tabla Venta que almacara cada transacción individual, con atributos como VentaID, FechaVenta, Unidades, PrecioUnitario, CostoTotalPorUnidad y ClienteID.
- Producto más vendido por categoría:
Podemos utilizar la tabla Venta y la tabla Producto para obtener el recuento de ventas por producto y categoría utilizando una consulta de agregación.
- Total, de ventas por cliente:
Utilizamos la tabla Venta para calcular el total de ventas por cliente mediante una consulta de agregación agrupada por ClienteID.
- Ingreso total generado por ventas:
De nuevo, utilizamos la tabla Venta para calcular el ingreso total mediante una consulta de agregación que sume el costo total por unidad de todas las ventas.
- Cantidad promedio de unidades vendidas por transacción:
Podemos calcular la cantidad promedio de unidades vendidas por transacción utilizando una consulta de agregación en la tabla Venta.
- Promedio de precios unitarios por categoría de producto:
Utilizamos la tabla Producto y la tabla Venta para calcular el precio unitario promedio por categoría de producto.
- Ubicación geográfica con mayor número de ventas:
Utilizamos la tabla Venta y la tabla Cliente para obtener el recuento de ventas por ubicación geográfica (ciudad, estado, país) mediante una consulta de agregación.
- Producto con el precio unitario más alto y bajo:
Podemos utilizar la tabla Producto para encontrar el precio unitario máximo y mínimo.
Como podemos ver podemos responder , las preguntas de negocio , con este ejemplo ficticio, realizando las tres fases para el diseño, que para mi son fundamentales, para llevar a buen puerto, cualquier desarrollo BI.
Las tres fases del modelado dimensional – conceptual, lógica y física – son pilares fundamentales para los analistas de datos en su búsqueda por comprender y aprovechar el potencial de los conjuntos de datos. Estas fases no solo representan etapas en el proceso de construcción de un modelo dimensional, sino que también delinean un camino crítico hacia la generación de conocimientos accionables y la toma de decisiones informadas.
En la fase conceptual, los analistas de datos se sumergen en la esencia del negocio, comprendiendo sus necesidades y objetivos. Aquí, se identifican las entidades, relaciones y atributos clave que formarán la base del modelo dimensional. Esta etapa no solo establece una comprensión profunda del contexto empresarial, sino que también garantiza que el modelo resultante esté alineado con las metas y prioridades del negocio.
La fase lógica lleva esta comprensión conceptual un paso más allá, transformándola en una estructura detallada y estructurada. Aquí, los analistas definen las tablas, columnas y relaciones con mayor precisión, tomando decisiones críticas sobre la normalización de datos y la optimización de consultas. Esta fase permite a los analistas crear un modelo dimensional que sea eficiente, adaptable y optimizado para el análisis de datos.
Finalmente, la fase física convierte este modelo lógico en una implementación práctica en una plataforma de bases de datos. Aquí, los analistas definen los tipos de datos, índices y restricciones que permitirán almacenar y manipular los datos de manera efectiva. Esta etapa proporciona a los analistas acceso directo a los datos en un entorno controlado y optimizado, permitiéndoles ejecutar consultas complejas y obtener resultados rápidos y precisos.
Para concluir, para mi las tres fases del modelado ,son esenciales, diría fundamentales para los analistas de datos en su búsqueda por convertir datos en información valiosa y accionable. Al seguir este proceso, los analistas pueden desarrollar modelos dimensionales que satisfagan las necesidades específicas del negocio y proporcionen una base sólida para la toma de decisiones estratégicas y la generación de conocimientos significativos.
Business Intelligence Technical
Vicente Antonio Juan Magallanes.
