15.4. Modelo Fisico. Modelo normalizado y desnormalizado (Dimensional).

Partiendo del modelo lógico que hemos construido en la entrada anterior, vamos a pasar al modelo físico, pero haciendo un pequeño ejercicio. En primer lugar, vamos a construir el modelo físico normalizado (como si hubiesemos obviado las recomendaciones de Kimball). Podría quedar algo así:

Esquema Fisico Normalizado

Podemos ver que en la parte derecha de los campos el valor PK que indica que un campo es clave primaria de una tabla, o el valor FK para las claves foráneas (valores que han existir en otras tablas relacionadas). El modelo es algo orientativo de como puede quedar un esquema físico totalmente (o casi) normalizado. No he entrando en profundidad en todas las posibilidades de diseño para la construcción de los datos geográficos (población, provincia, región, pais), ni en la codificación de datos sociográficos del club de vinos (sexo, nivel estudios, estado civil, tipo de vivienda), que también podrían haber estado codificados con su consiguiente tabla normalizada (tupla código-descripción).

A continuación, lo vamos a desnormalizar, hasta llegar a nuestro esquema físico de estrella (tal y como diseñamos en la entrada anterior del blog).  El modelo físico final sería el siguiente:

Esquema Fisico Desnormalizado con campos para SCD y Audit

Podeís ver que hemos seguido las siguientes convenciones respecto a los nombres de las tablas y de los campos que las conforman:

• Tablas: la tablas de hechos empiezan por DWH (DWH_VENTAS para las ventas), mientras que las tablas de dimensiones lo hacen por DWD (DWD_TIEMPO para la dimensión tiempo).
• La dimensiones que son dimensiones conformadas o derivadas de otras (o como la dimensión Club de Vinos que es una dimensión outrigger) se llaman igual que la dimensión de la que dependen, añadiendo al final del nombre el nombre del subconjunto (la tabla para la dimensión mes se llama DWD_TIEMPO_MES).
• Campos: la nomenclatura de los nombres de campo incluye el sufijo ID para el caso de que el campo represente un código y el sufijo DESC para lo que son una descripción, nombre o texto explicativo del atributo.

Tambien podeís observar como hemos añadido en la tabla de la dimensión Producto, en la dimensión Cliente y en la dimensión outrigger del Club de Vinos campos adicionales que no habiamos enumerado hasta ahora. Por un lado, hemos añadido campos de auditoría,para registrar información del momento en el que se crean los registros en la base de datos y del momento de la ultima modificación (INSERT_DATE y UPDATE_DATE). Ademas, nos guardamos el nombre del proceso ETL que realiza la inserción (INSERT_PROC) o la ultima modificación (UPDATE_PROC). Con estos campos podremos realizar verificaciones o nos podrán ser utiles en el caso de realizar debug o corregir errores en los procesos de ETL.

Igualmente, hemos añadido otros campos para la gestión de las dimensiones lentamente cambiantes.Vamos a realizar una gestión SCD del tipo 2. Para realizar el versionado de los registros de la dimensión, tendremos los siguientes campos:

Campos adicionales para gestión de SCD

Cuando se produzca un cambio en los campos que hayamos definido como importantes, generaremos un nuevo registro en la tabla, construyendose una nueva clave subrogada (inventada) para el nuevo registro. Ademas, se llenaran los valores de los nuevos campos con los valores apropiados:

• SCD_FEC_INI: fecha en la que entra en vigor los valores del nuevo registro.
• SCD_FEC_FIN: fecha final.
• SCD_VERSION: numerador de la versión de datos. Valor correlativo que nos indicara el número de registros que hay para un determinado cliente o producto, por ejemplo.
• SCD_ACTIVO: flag que indica si el registro es el activo (por ejemplo, podremos tener varios registros para un cliente, con diferentes claves subrogadas, pero con el mismo código original del sistema operacional). Y solo uno de los registros estará activo.

Al insertar un nuevo registro para un cliente, habrá que actualizar igualmente los registros anteriores (para indicar que ya no será el registro activo y para limitarle la fecha final de validez). Todos los campos adicionales para la gestión de las dimensiones lentamente cambiantes tienen el prefijo SCD.

Para las dimensiones derivadas (dimensión Mes, Tipo de Cliente y Canal), hemos creado una tabla con los campos que forman el subconjunto de la dimension original. Podiamos haber creado una vista para definirlas, pero hemos preferido crearlas como tablas independientes para luego hacer los correspondientes procesos ETL de llenado a partir de la dimensión original.

Particionado de la tabla de hechos de ventas por Año.

Aunque en el esquema no lo vemos, hemos realizado un particionado de la tabla de hechos de ventas, creado una partición diferente para cada año. Tenemos 16 particiones, partiendo del año 2005 hasta el 2020. Mas que suficiente seguramente para lo que será la vida útil de la aplicación. La sentencia Sql para la creación de la tabla sería la siguiente:

/*==============================================================*/
/* Table: DWH_VENTAS                                            */
/*==============================================================*/
create table DWH_VENTAS  (
     FECHA_SK             INTEGER                         not null,
     CLIENTE_SK           INTEGER                         not null, 
     MATERIAL_SK          INTEGER                         not null,
     CENTRO_ID            INTEGER                         not null,
     PROMOCION_ID         INTEGER                         not null,
     PEDIDO_ID            INTEGER                         not null,
     UNIDADES             FLOAT                           not null,
     LITROS               FLOAT                           not null,
     PRECIO               FLOAT                           not null,
     IMPORTE_DTOS         FLOAT                           not null,
     IMPORTE_PROMO        FLOAT                           not null,
     COSTE_UNIT           FLOAT                           not null,
           constraint PK_DWH_VENTAS primary key (FECHA_SK, CLIENTE_SK, MATERIAL_SK, CENTRO_ID, PEDIDO_ID, PROMOCION_ID))
partition by range(fecha_sk)
      (partition year2005 values less than (20060101),
     partition year2006 values less than (20070101),
     partition year2007 values less than (20080101),
     partition year2008 values less than (20090101),
     partition year2009 values less than (20100101),
     partition year2010 values less than (20110101),
     partition year2011 values less than (20120101),
     partition year2012 values less than (20130101),
     partition year2013 values less than (20140101),
     partition year2014 values less than (20150101),
     partition year2015 values less than (20160101),
     partition year2016 values less than (20170101),
     partition year2017 values less than (20180101),
     partition year2018 values less than (20190101),
     partition year2019 values less than (20200101),
     partition year2020 values less than (20210101) );

Os dejo el link a las sentencias Sql para la creación de todos los objetos que forman el modelo. Os recuerdo que estoy trabajando con Oracle 10g. Para el modelado he utilizado la herramienta de Sybase Power Designer, aunque podría haber sido una alternativa trabajar con Oracle Sql Developer (y la herramienta Data Modeler que ofrece también Oracle para el modelado de datos).