DISEÑO DIMENSIONAL: Como crear una tabla de dimensión

Recientemente un amigo colombiano, me pidió una guía/ejemplo de cómo debería ser una tabla de dimensiones, este posteo reviso los elementos que normalmente uso en las tablas de dimensión. Puedes bajar el codigo de ejemplo de DimensionSample.sql desde GitHub.

Cuando se diseña usando un modelo dimensional, que usa el estilo Kimball para construir un modelo analítico vale la pena tomar en cuenta las siguientes consideraciones:

1. Usar un estándar de nombres fuerte y orientado al usuario. Los estándares pueden variar y hay algunos más usables que otros. En general, para objetos lógicos (Tablas, Vistas, columnas, etc.), prefiero usar un estándar basado en el estándar ISO 11179-5:
   • Pascal case: sin tildes ni símbolos especiales.
   • Nombres conceptuales: para dominios, entidades y atributos. En modelos simples con esquema estrellas, uso un único esquema de dimensiones para todas las dimensiones, separándoles de las tablas de hechos. En modelos complejos, con esquemas copo de nieve, uso un esquema con el nombre de la dimensión y agrupo las tablas que participan en dicha dimensión en dicho esquema.
   • Plurales para las tablas y singular para columnas. Por ejemplo: Tabla de Customers (Clientes), columna de CustomerCode (CodigoCliente).
   • Sin prefijos o postfijos.
   • No usar siglas excepto en casos limitados y llevar registro para que se usen de forma universal en todos los objetos.

     --- ISO 1176-5 Naming Standard: Pascal case, conceptual names. 
     --- Tables: Plural, columns: singular.
     CREATE TABLE Dimensions.Customers(

2. Definir una llave primaria subrogada de tamaño entero. Las llaves subrogadas son internas del sistema y no deben exponerse a los usuarios finales.
   • En ocasiones cuando la tabla tiene muchas filas puede pensarse en usar un BIGINT, pero rara vez es necesario.
   • Casi siempre es un IDENTITY, excepto cuando la llave es inteligente. Las llaves inteligentes son las que “significan algo”, normalmente solo se usan en las dimensiones relacionadas con tiempo (fecha y hora).
   • Reservar los números negativos para valores erróneos o desconocidos.

     --- SubrogateKey (Identity)
          CustomerID INT NOT NULL IDENTITY(1,1)
     CONSTRAINT PK_Customers PRIMARY KEY,

3. Identificar cuál es la llave de negocio, que puede ser un simple (un único atributo: CustomerCode) o que puede ser compuesta por múltiples atributos (CountryISOCode+CustomerCode).

   --- Business Key 
          CustomerCode VARCHAR(10) NOT NULL,

4. Las tablas de dimensiones relevantes suelen ser ANCHAS. Esto es que suelen tener decenas o centenas de atributos y con frecuencia muchos de estos atributos son de tipo carácter, sí que esto signifique que sea un problema que la tabla de dimensión tenga atributos con valores numéricos o fecha. Normalmente tienen pocas filas, aunque hay casos excepcionales que manejas muchas filas.
5. Puede estar des-normalizada, por ejemplo: StateProvinceISOCode y StateProvinceName, pueden ser atributos de la tabla de dimensión/tabla clientes (rompiendo la tercera forma normal).
6. Con frecuencia columnas calculadas (StateProvinceFullName y CountryFullName). En el ejemplo uso el patrón de FullName(Nombre Completo) que consiste en concatenar el código y el nombre de la entidad. Este patrón mejora la usabilidad de los cubos en varias formas:
   1. Permite usar un lenguaje común entre el personal operativo que suele usar los códigos y el personal ejecutivo o gerencial que esta usualmente usa nombres.
   2. De forma automática ordena los datos por código.
   3. Es visualmente atractivo cuando se usa el atributo como fila en una tabla o tabla pivote.
7. Deben agregarse las restricciones (constraints) apropiadas para garantizar la calidad de los datos, incluyendo NOT NULL, CHECK y FOREIGN KEY. El tener datos limpios es prioritario en un Datamart y la forma más efectiva de lograrlo es de forma declarativa.

   StateProvinceISOCode VARCHAR(5) NOT NULL
   --- Use CONSTRAINTs  
        CONSTRAINT CH_Customers_StateProvinceISOCode 
         CHECK(StateProvinceISOCode LIKE '[A-Z][A-Z]-[A-Z][A-Z]'), 
   --- ISO 3166-2 
   --- Denormalized column, no need to have another table. 
   StateProvinceName VARCHAR(120) NOT NULL,
   --- Calculated Column:  
    StateProvinceFullName AS CAST((StateProvinceISOCode+' - '+StateProvinceName) AS VARCHAR(125)),

8. Columnas de Metadata. Normalmente suelo usar:
   1. DimStart y DimEnd: Para llevar control de la fecha de vigencia del registro. Esto es que en dimensiones con atributos tipo 2, existirán múltiples filas para el mismo cliente de acuerdo a como se detecten cambios en los atributos. La fila con la última versión del cliente es la que tiene DimEnd en NULL o alternativamente fijar un valor como 2999-12-31 para marcar los registros actuales.
   2. LoadGUID: Columna para identificar el proceso de ETL responsable de la carga de la fila. En este diseño las filas se insertarán con SQL Server Integration Services (SSIS) y esta columna se mapeará con el ExecutionID del paquete de SSIS.

Estos patrones de construccion son bastante simples de implmentar y ayudan bastante a construir modelos multidimensionales o tabulares de forma mas efectiva. Baja el código de ejemplo de DimensionSample.sql y tambien puedes leer de como crear tablas de hechos.