Hola muy buenos días , tardes o noches, hoy crearemos un modelo estrella con el reto numero 5 de fp20analytics.
Por acá comparto el link para los que desean acceder: https://fp20analytics.com/challenge
En este proceso se está trabajando en la creación de un modelo dimensional en estrella mediante la selección de columnas candidatas para crear la dimensión. Este modelo es recomendado en la práctica de modelado de datos eficaz y eficiente. Se utiliza una estrategia de selección de columnas numéricas o que se usarán para contabilizar y sumar debido a su alta aditividad.
A continuación, se detallan los pasos del proceso, que incluyen la creación de consultas para las dimensiones seleccionadas, la creación de tablas para cada dimensión, la inserción de datos únicos en las tablas de dimensión, la creación de una consulta para la tabla de hechos utilizando varios left joins con las tablas de dimensión, y la revisión de la tabla de fechas. En la tabla resultante, se encuentra la tabla de hechos junto con las tablas de dimensión creadas en el proceso.
Siguiendo las buenas practicas de un modelado de datos eficaz y sobre todo eficiente, apuntamos a que sea un modelo en estrella el que crearemos el día de hoy.
Así que seleccionaremos las columnas candidatas para crear el modelo dimensional las cuales serán las de texto dado que lo aconsejable es dejar solo las columnas de tipo numero o que se usaran para contabilizar y sumado a ello son de alta aditiva.
Paso 1) Creación de las consultas de las dimensión seleccionadas
–Creamos la consulta los paises únicos para obtener solo los datos únicos de la columna
select distinct(country) from stage
–Creamos la tabla dim_country con el id que nos servirá para crear las primero la llave primaria de la dimensión y que esta sea la foránea de la de hechos
create table dim_country
(
id serial,
Country varchar(14)
)
–Insertamos los datos en la nueva tabla Dim_country
insert into dim_country (Country)
select distinct(country) from stage
–Creamos la consulta los distribuidores únicos para obtener solo los datos únicos de la columna
select distinct(distribuitor) from stage
–Creamos la tabla dim_distribuitor con el id que nos servira paracrear las primero la llave primaria de la dimension y que esta sea la foranea de la de hechos
create table dim_distribuitor
(
id serial,
distribuitor varchar(14)
)
–Insertamos los datos en la nueva tabla dim_distribuitor
insert into dim_distribuitor (distribuitor)
select distinct(distribuitor) from stage
–Creamos la consulta las marcas únicas para obtener solo los datos únicos de la columna
select distinct(brand) from stage
–Creamos la tabla dim_brand con el id que nos servirá para crear las primero la llave primaria de la dimensión y que esta sea la foránea de la de hechos
create table dim_brand
(
id serial,
brand varchar(14)
)
–Insertamos los datos en la nueva tabla dim_brand
insert into dim_brand (brand)
select distinct(brand) from stage
–Creamos la consulta las operadores únicas para obtener solo los datos únicos de la columna
select distinct(operators) from stage
–Creamos la tabla dim_operators con el id que nos servirá para crear las primero la llave primaria de la dimensión y que esta sea la foránea de la de hechos
create table dim_operators
(
id serial,
operators varchar(14)
)
–Insertamos los datos en la nueva tabla dim_operators
insert into dim_operators (operators)
select distinct(operators) from stage
Paso2) Creamos las consultas para crear la tabla de hechos, Ahora crearemos la consulta para crear la tabla definitiva de hechos con varios left joins
select a.date_original,a.unit_cost,a.amount,a.unit_price,a.sales,
b.id as id_country,
c.id as id_distribuitor,
d.id as id_brand,
e.id as id_operators
into Fact_table
from public.stage as a
left join dim_country as b
on a.country=b.Country
left join dim_distribuitor as c
on a.distribuitor=c.distribuitor
left join dim_brand as d
on a.brand=d.brand
left join dim_operators as e
on a.operators=e.operators
Paso 3) Revisión de la tabla de fechas que tengas los años máximos y mínimos correctos
— Verificación de tabla de fechas
— El maximo de fecha en años es 2022 así que solo subiremos en fecha calendario los dias
select max(year),min(year) from dates
select max(date_original),min(date_original) from stage
–eliminamos los datos de año 2022
delete from dates
where year=2022
–verificamos
select max(year),min(year) from dates
Paso final tablas resultantes:
—Tabla de hechos
select * from Fact_table
–Tablas de dimensiones
select * from dim_country
select * from dim_distribuitor
select * from dim_brand
select * from dim_operators
select * from dates
En conclusión, la creación de un modelo dimensional en estrella mediante la selección de columnas candidatas para crear la dimensión, siguiendo las buenas prácticas de un modelado de datos eficaz y eficiente, es una estrategia recomendada.
En este proceso se utilizaron las columnas de tipo texto, dejando solo las columnas de tipo numérico o que se usarán para contabilizar y sumar debido a su alta aditividad.
Los pasos del proceso incluyeron la creación de consultas para las dimensiones seleccionadas, la creación de tablas para cada dimensión, la inserción de datos únicos en las tablas de dimensión, la creación de una consulta para la tabla de hechos utilizando varios left joins con las tablas de dimensión, y la revisión de la tabla de fechas. La tabla resultante incluye la tabla de hechos junto con las tablas de dimensión creadas en el proceso, lo que permite una fácil visualización y análisis de los datos. En general, este proceso es fundamental para la implementación de soluciones de análisis de datos eficaces y eficientes.
Te comparto el código completo en mi GitHub: https://github.com/vicente2121/Consultas-modelo-dimensional-reto-numero-5-de-fp20analytics/blob/main/Consultas_dimensiones_hecho.sql
