Pasos para crear cubos OLTP en SQL Server - Inteligencia de Negocios


En esta ocasión muestro un pequeño tutorial para hacer la transformación de una Base de datos Transaccional a un Cubo para análisis OLAP. Un cubo es una unidad de consulta multimensional, el problema que resuelvo consiste en construir el cubo a partir de la base de datos transaccional de ejemplo usando SQL Server.

El proceso consiste en tres pasos: Ubicar la tabla Fact o tabla que incluya todos los requerimientos, a continuación se debe modificar las relaciones de la base de datos y finalmente, cargar los datos en la nueva relación o Cubo OLTP.

El archivo de inicio y la solución se pueden descargar a continuación. Para revisar el ejemplo necesita SQL Server 2008 estándar o mayor.

Base de datos inicial: http://www.4shared.com/file/I65ZM8Fd/OLTP_Ventas2008.html [Revisado]

Base de datos configurada en cubo (Solución): http://www.4shared.com/file/IMqlk2vC/OLTP_Ventas2008_Solucion.html [Revisado]

Empecemos:

Paso 1:

Observe el esquema de la base de datos transaccional. Aprecie las relaciones de la base de datos de nombre OLTP_Ventas.

Note que la tabla candidato a FACT es Matrícula porque relaciona las dimensiones Cliente, Producto, Empleado, etc.



Paso 2:

Empezaremos haciendo la estructura del cubo. Borre las relaciones de las tabla.



Paso 3:

Seleccionar las tablas que harán la composición de las dimensiones del cubo. En este ejemplo se debe seleccionar Pedido, Cliente, Producto, Empleado y Proveedor. Luego renombre la tabla Pedido como Fact_Pedido y para el resto de tablas usar el prefijo Dim (Dimensión) por ejemplo: Dim_Cliente, Dim_Producto, Dim_Categoría, así en lo sucesivo.




Paso 4:

Crear la tabla Dim_Tiempo. La tabla dimensión tiempo es fundamental en todo cubo y organiza el resto de dimensiones en función del tiempo.

CREATE TABLE [dbo].[Dim_tiempo](
    [idTiempo] [smalldatetime] NOT NULL,
    [dia] [int] NULL,
    [mes] [int] NULL,
    [anio] [int] NULL,
 CONSTRAINT [PK_Dim_tiempo] PRIMARY KEY CLUSTERED
(
    [idTiempo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Paso 5:

Agregue "Claves Primarias" en la tabla Fact_Pedido (que es la tabla central del cubo) con el objetivo de unir las entidades Cliente, Producto, Empleado y Proveedor en la tabla Fact_Pedido.
 

En este ejemplo se agrega los siguientes campos:
  • IdProducto
  • IdProveedor
También debe agregar "Campos de Métrica" o que guardan cálculos (Totales, subtotals). Para este ejemplo agregaremos:
  • Cantidad (int),
  • Descuento (int),
  • Subtotal (Money)
Cambie el nombre del campo Fact_Pedido.Fecha_pedido por Fact_Pedido.IdTiempo y asocie el campo a Dim_Tiempo.

Finalmente, Elimine campo Fact_Pedido.Fecha_entrega porque las fechas son innecesarias en esta tabla.


Ya casi tenemos el cubo.

Paso 6:

Para que el cubo se complete es necesario cargar datos a las tablas o dimensiones.

Es importante notar que los nuevos campos recién creados: IdProducto, IdProveedor, Cantidad y Subtotal recién agregados a la tabla Fact_Pedido no tienen valores o son NULL. Note que Subtotal es un caso especial, porque es producto del cálculo de Cantidad * Precio. Ud. debe imaginar una manera práctica para cargar datos.

En este ejemplo vamos usar una consulta SQL para completar datos que faltan en la tabla Fact_Pedido y Dim_detalle_pedido calculando
Cantidad * Precio y el resto de claves que falta asignar.

SELECT     dbo.Fact_Pedido.NroPed, dbo.Dim_Producto.IdProducto, dbo.Dim_Producto.IdProveedor, dbo.Fact_Pedido.idTiempo, dbo.Fact_Pedido.Id_Cliente,
                      dbo.Fact_Pedido.IdEmpleado, dbo.Dim_Detalle_pedido.Cantidad, dbo.Dim_Detalle_pedido.Descuento,
                      dbo.Dim_Detalle_pedido.Cantidad * dbo.Dim_Producto.PrecioUnit AS Subtotal
FROM         dbo.Fact_Pedido INNER JOIN
                      dbo.Dim_Detalle_pedido ON dbo.Fact_Pedido.NroPed = dbo.Dim_Detalle_pedido.NroPedido INNER JOIN
                      dbo.Dim_Producto ON dbo.Dim_Detalle_pedido.IdProducto = dbo.Dim_Producto.IdProducto


Guarde esta salida de la consulta en un archivo de texto. Servirá a posterior para llenar la tabla Fact_Pedido:

1    3425    C002    2007-01-25 00:00:00.000    D004    D06    100    15    20,0000
2    4564    C001    2007-05-13 00:00:00.000    F006    C05    15    11    225,0000
3    2345    C001    2007-08-24 00:00:00.000    C003    A02    45    19    202,5000
3    7845    C003    2007-08-24 00:00:00.000    C003    A02    60    15    180,0000

Paso 7:

Borre los registros (filas) de la tabla Fact_Pedido. Edite Fact_Pedido quitando "Clave Primaria" de NroPed. Registre estas nuevas columnas como Clave Primaria:

  • IdProducto
  • IdProveedor
  • idTiempo
  • Id_Cliente
  • IdEmpleado
La tabla debería quedar así:





Importante: Solo se debe conservar Llaves Primarias y Campos creados con objetivo de guardar resultado de cálculos en la tabla Fact_Pedido.

Paso 8:
Ahora toca cargar los datos desde el archivo de texto descrito en Paso 6 sobre la tabla Fact_Pedido. La tabla se llenará, ya no da lugar a campos nulos.




Paso 9:

Cargar los datos para la tabla Dim_Tiempo. Los datos de Dim_Tiempo son el resultado de Fact_Pedido.IdTiempo, por tanto, usaremos esta consulta para extraer los datos:

SELECT DISTINCT idTiempo, DAY(idTiempo) AS dia, MONTH(idTiempo) AS mes, YEAR(idTiempo) AS anio
FROM         dbo.Fact_Pedido



Paso 10:

Finamente (ahora si...) relacionar Fact_Pedido con el resto de tablas o dimensiones usando las relaciones. Debería quedar así:





El 'cubo' está listo :D

Es hora de hacer consultas al cubo. Puedes continuar con el post Pasos para extraer información del cubo OLAP en Analysis Services y SQL Server Business Intelligence Development Studio


Buen trabajo.


COMENTARIOS 12 Comentarios

12 comentarios :

  1. Buenos Dias amigo me gusto tu post, pero te informo que no puedo descargar las base de datos de ejemplos ya que no se encuentran, si puedes subelas te lo Agradecería.

    ResponderEliminar
  2. Gracias por tu aporte compadre, me ha servido mucho...el Bakup de la base se descarga bien y la pude restaurar sin ningún problema

    Saludos!!

    ResponderEliminar
  3. la verdad esta bueno el articulo,pero si pudieras dar un ejemlo de explotacion del cubo te lo agradeceria.gracias

    ResponderEliminar
  4. El sitio donde estan hospedados los archivos es una KK con la peticion de un registro para las desgargas. NO a los sitios que exigen registro para una simple descarga. Elijamos un mejor lugar donde hospedar documentos sin tanto complique.

    ResponderEliminar
  5. El artículo es muy bueno, pero el lugar donde se hospedan los archivos 4shared.com es un fiasco; ya que para una simple descarga no es necesario procesos de registro y de mas.

    ResponderEliminar
  6. Que buen post, me sirvio de mucho, agradeciendo de antemano....

    ResponderEliminar
  7. realmente me sirvió todo el post.
    Los archivos los pude descargar sin problemas.
    El trabajo lo necesito par ala Universidad y lo seguí al pie de la letra.

    Muchas gracias!

    ResponderEliminar
  8. estimado, este archivo backup solo es para sql server 2008?, ya que tengo instalado el r2 y me sale un ventana de error apuntando a la carpeta de msql10.server que es para sql server 2008 y no msql10.50 de r2, despejame la duda

    ResponderEliminar
  9. al poner solo primary key a una atributo de la tabla ya estas indicando que es unico, ya que tenia entendido que se puede poner unique y para no permitir valores vacios not null

    ResponderEliminar
  10. muy buen trabajo.

    gracias por la ayuda.

    ResponderEliminar