Bienvenidos a mi blog, en esta publicación vamos entender, Cómo crear dimensiones y tablas de hechos en POSTGRESQL para análisis de datos eficiente en empresas
Este código es una serie de consultas realizadas en POSTGRESQL, que crean una base de datos de análisis para una empresa.
En la primera línea, se selecciona todo el contenido de la tabla «stage_rrhh».
Luego, se crean varias dimensiones utilizando «select distinct» para obtener valores únicos de varias columnas de «stage_rrhh». Cada dimensión se crea como una nueva tabla en la base de datos y se insertan los valores únicos en ella. Las dimensiones creadas son «Gender», «EthnicGroup», «Work_Type», «Cost_Centre», «HireDate», «Remains_Employed», «PayType» y «SeperationReason».
Después, se crea una tabla de hechos que contiene todas las columnas de «stage_rrhh», pero con las claves de las dimensiones en lugar de los valores de texto. La tabla de hechos se une con cada dimensión correspondiente utilizando «left join».
Finalmente, se crea una nueva tabla llamada «hechos_empresa» que contiene los mismos datos que la tabla de hechos, pero almacenados permanentemente en la base de datos.
Por ejemplo tomamos Gender y explicamos a continuación la lógica de como se creo y luego es cuestión de replicarlo con las demás dimensiones , en la primera línea de código se selecciona todo el contenido de la tabla «stage_rrhh». Esto significa que se está extrayendo toda la información que se encuentra en esa tabla.
A continuación, se crean varias dimensiones utilizando «select distinct». Esto significa que se seleccionan todos los valores únicos de una columna determinada y se crea una tabla con ellos. Por ejemplo, la siguiente consulta crea la dimensión «Gender»:
select distinct(gender) from stage_rrhh Esta consulta devuelve todos los valores únicos de la columna «gender» en la tabla «stage_rrhh». Luego, se crea una nueva tabla llamada «Dim_gender» con la siguiente consulta:
create table Dim_gender
(
id_gender serial,
Gender varchar(10)
)
Esta nueva tabla tendrá una columna llamada «id_gender» que actúa como clave primaria, y otra columna llamada «Gender» que almacena los valores únicos de la columna «gender» de la tabla «stage_rrhh».
A continuación, se insertan los valores únicos de «gender» en la nueva tabla de dimensión con la siguiente consulta:
insert into dim_gender (Gender)
select distinct (gender) from stage_rrhh
Este proceso se repite para cada dimensión que se va a crear en la base de datos.
Luego, se crea una tabla de hechos que contiene todas las columnas de la tabla «stage_rrhh», pero con las claves de las dimensiones en lugar de los valores de texto. Por ejemplo, la siguiente consulta une la tabla de hechos con la dimensión «Gender»:
left join Dim_gender as b
on a.gender=b.gender
Esto significa que la columna «gender» de la tabla de hechos se sustituirá por la clave correspondiente de la tabla de dimensión «Dim_gender».
Finalmente, se crea una nueva tabla llamada «hechos_empresa» que contiene los mismos datos que la tabla de hechos, pero almacenados permanentemente en la base de datos. Esto se logra con la siguiente consulta:
select a.EmployeeID,a.Age,a.AgeGroup,a.TenureDays,a.TernureMonths,a.Leave_Balance,
a.Sick_Days_Last_12_Mths,a.Current_Salary,a.Current_Employee_Rating,
b.id_gender,c.id_EthnicGroup,d.id_Work_Type,e.id_Cost_Centre,g.id_Remains_Employed,
h.id_PayType,i.id_SeperationReason into hechos_empresa
from stage_rrhh as a
left join Dim_gender as b
on a.gender=b.gender
left join Dim_EthnicGroup as c
on a.EthnicGroup=c.EthnicGroup
left join Dim_Work_Type as d
on a.Work_Type=d.Work_Type
left join Dim_Cost_Centre as e
on a.Cost_Centre=e.Cost_Centre
left join Dim_Remains_Employed as g
on a.Remains_Employed=g.Remains_Employed
left join Dim_PayType as h
on a.PayType=h.PayType
left join dim_SeperationReason as i
on a.SeperationReason=i.SeperationReason
Con esta consulta, se crea una nueva tabla llamada «hechos_empresa» que almacena los mismos datos que la tabla de hechos, pero con las claves de las dimensiones en lugar de los valores de texto. Esta nueva tabla es útil para realizar análisis más avanzados sobre los datos de la empresa.
Para cerrar, este código es muy útil para empresas que necesitan analizar datos de manera eficiente y efectiva. Al crear dimensiones y una tabla de hechos con claves de dimensiones, se puede realizar un análisis más avanzado sobre los datos de la empresa. Además, al almacenar la tabla de hechos en una tabla permanente en la base de datos, se garantiza que los datos estén disponibles para futuros análisis y consultas. En general, este código es una herramienta poderosa para el análisis de datos y puede ayudar a las empresas a tomar decisiones informadas basadas en los datos.
Acá te dejo en link de Github donde esta todo el código para que lo puedas usar de ejemplo y también encontrás el archivo utilizado.
https://github.com/vicente2121/C-mo-crear-dimensiones-y-tablas-de-hechos-en-SQL-y-POSTGRESQL-para-an-lisis-de-datos-eficiente-en-em/blob/main/Creando_modelo.sql
Vicente Antonio Juan Magallanes
Business Intelligence Technical.
Perfil linkedin.
