Después de varias semanas de ausencia (no estuve de vacaciones sino envuelto en 20 cosas) voy compartir un caso interesante para el diseño de un DataMart y su respectivo cubo en Analysis Services que me tocó trabajar en las últimas semanas. El caso se resume en crear un cubo en Analysis Services que brinde la información de ventas asignadas a los representantes de venta (vendedores) para el cálculo de sus comisiones de acuerdo al cliente y producto vendido.
Para llegar a la solución requerida fue necesario trabajar con relaciones muchos-a-muchos y referenciadas en el cubo. No voy a ahondar en qué trata cada uno de estos tipos de relaciones sino más bien me enfocaré en los detalles del problema de negocio y en el recorrido realizado para llegar a la solución. Pueden revisar la documentación de los BOL de SQL para mayor referencia: Many-to-Many Dimensions in Analysis Services 2005 y Defining a Referenced Relationship.
EL PROBLEMA
En este caso simularé el escenario real con un ejemplo simple de manera que pueda ser fácil de entender para todos. El problema de negocio consiste en calcular las comisiones de nuestros representantes de venta. Los clientes (podrían considerarse como distribuidores), están asignados a la cartera de uno o más representantes de venta.
Detalles importantes acerca del negocio/proceso:
- Los productos se organizan en diversas categorías.
- Los representantes de venta tienen una jerarquía de organización según las localizaciones que cubren.
- Los productos en cuestión son altamente especializados por lo cual el negocio asigna al representante cuota sobre una o más categorías de producto (y por tanto de los productos dentro de las mismas). Además, estas categorías son asignadas de acuerdo a la localización y cliente.
Si modelamos la asignación de clientes y categorías de producto para los representantes de ventas en un diagrama de entidad relación (ER), tenemos:
Según se muestra hay una relación de muchos-a-muchos entre Representante ([SalesPerson]), Categoría ([Category]) y Cliente ([Customer]) que se materializa físicamente usando la tabla puente [CustomersAndCategoriesBySalesPerson]. Para abreviar el ejemplo la tabla de representante de ventas ([SalesPerson]) ha sido de-normalizada con respecto a los campos que guardan los datos de localización geográfica (territorio y distrito). Un proceso automático al inicio del periodo carga los datos en la tabla puente de acuerdo a las estrategias del departamento de ventas, por tanto esta tabla mantiene lo que cada representante de venta tiene asignado de acuerdo al cliente y por tanto las comisiones respectivas se asignarán según los datos de esta tabla.
Nuestra base de datos de ejemplo contiene los siguientes datos de prueba:
Clientes:
Categorías:
Productos:
Representantes de venta:
Tabla puente con la asignación de clientes y categorías para cada representante de venta:
Con respecto a las transacciones ocurridas, vamos a simular que en último mes se registraron las siguientes ventas (nuevamente para simplificar el ejemplo se han omitido varios campos como la fecha, número de transacción, etc.):
LÓGICA PARA LASIGNACION DE VENTAS:
Primera venta:
Para el primer registro de la tabla de ventas en la figura anterior, venta del Producto 1 por el Cliente 1, deberíamos tener la siguiente asignación según representante de venta:
Representante de ventas 1 = 10 unidades
(el cliente 1 y el producto 1 - perteneciente a la categoría 1 - están asignados a su cartera)
Representante de ventas 2 = 10 unidades
(el cliente 1 y el producto 1 - perteneciente a la categoría 1 - están asignados a su cartera)
A nivel de territorio tenemos (sin importar el representante):
Territorio 1 = 10 unidades del producto 1 (a pesar de que representante de venta 1 y representante de venta 2 están en el mismo territorio, sólo debemos ver la cantidad única de unidades vendidas y no duplicarlo)
Si analizamos según distrito debemos tener:
Distrito 1 = 10 unidades del producto 1 (de acuerdo a la misma lógica anterior)
Agregando la segunda venta:
Para el segundo registro de ventas, venta del Producto 2 por el Cliente 1 deberíamos tener la siguiente asignación según representante de ventas:
Representante de ventas 2 = 5 unidades
(el cliente 1 y el producto 2 - perteneciente a la categoría 2 - están asignados a su cartera)
Recapitulando, para los dos primeros registros de venta debemos tener como resultado:
Representante de ventas 1 = 10 unidades vendidas
Representante de ventas 2 = 10 + 5 = 15 unidades vendidas
Territorio 1 = 15 (10 del producto 1 y 5 del producto 2)
Distrito 1 = 15 (10 del producto 1 y 5 del producto 2)
Agregando la tercera venta:
Para el tercer registro de ventas, venta del Producto 2 por el Cliente 2 deberíamos tener la siguiente asignación según representante de ventas:
Representante de ventas 3 = 3 unidades
(el cliente 2 y el producto 2 - perteneciente a la categoría 2 - están asignados a su cartera)
Los resultados acumulados en este punto serían:
Representante de ventas 1 = 10 unidades vendidas
Representante de ventas 2 = 10 + 5 = 15 unidades vendidas
Representante de ventas 3 = 3 unidades vendidas
Territorio 1 = 15 (10 del producto 1 y 5 del producto 2)
Territorio 3 = 3 (3 del producto 2)
Distrito 1 = 18 (10 del producto 1, 5 del producto 2, 3 del producto 2)
Por tanto, las ventas agregadas a nivel de territorio y distrito deben ser de acuerdo a los valores únicos de la combinación cliente y producto para NO duplicar las ventas. Si se requiere analizar las ventas según representante de venta entonces debemos de generar los resultados de acuerdo a la relación de muchos a muchos (en este caso si puede haber duplicados de la misma transacción si en caso el cliente y producto han sido asignados a más de un representante).
CONSTRUYENDO LA SOLUCIÓN
El cubo resultante debe permitir responder preguntas de manera consistente a nivel de representante de venta, cliente y producto.
Al crear nuestro datamart básicamente tenemos las siguientes dimensiones:
A nivel del cubo creamos también las tres dimensiones:
Representante de venta:
Cliente:
Producto:
Revisemos las posibles opciones para solucionar nuestro problema:
(INTENTO DE) SOLUCION # 1:
La idea de mostrar esta primera opción es demostrar por qué no podemos tratar este caso de modelamiento de DataMart como uno más. En esta primera opción vamos a guardar las ventas asignadas a cada representante en el fact table. Para esto creamos nuestro modelo de estrellas de la siguiente manera:
Como mencionamos anteriormente, nuestro fact table está poblado con las asignaciones de ventas por cada representante de acuerdo a lo explicado en la primera parte de este post:
Si agregamos nuestro fact v1 a nuestro cubo tendríamos una única medida con la cantidad de venta (Quantity) y el tab de Dimension Usage configurado de la siguiente manera:
Exploramos nuestro nuevo cubo:
Según se muestra tenemos una cantidad total de 28 productos vendidos lo cual es erróneo porque sólo fueron 18 productos.
Asimismo al agregar la jerarquía del representante de venta notamos que los valores en el nivel más granular (representante de venta), los valores se muestran correctamente, pero al agregar por territorio tenemos 25 unidades para el Territorio 1 lo cual es incorrecto porque sólo fueron 15 unidades en total. Lo mismo para el total del Distrito 1, el cubo nos arroja un total de 28 cuando en total fueron 18 productos vendidos.
Vemos los mismos resultados incorrectos si agregamos la jerarquía de producto. El total de ventas para el Territorio 1, Producto 1 arroja 20 cuando debe ser 10.
Ya en este punto deben haberse dado cuenta de que si el fact table tiene todos los registros de ventas (tal como los tiene ahora), no hay forma de que los totales no nos arrojen como lo está haciendo ahora (sumando cada registro) que es forma usada para cualquier caso típico.
(INTENTO DE) SOLUCION # 2 - Implementando relación muchos-a-muchos (many-to-many):
En este caso ya que existe una relación de muchos-a-muchos entre nuestras tres dimensiones (representante de venta, cliente y producto) vamos a usar la funcionalidad de SSAS para manejar este tipo de relaciones. En este caso sólo necesitamos guardar en nuestra tabla de hechos las transacciones que realmente sucedieron sin importar la asignación a uno o más representantes de venta y vamos a dejar que la asignación de la venta al representante o representantes se resuelva usando la relación muchos a muchos.
Primero a modo de demostración sólo crearemos la tabla intermedia (Fact muchos-a-muchos) para relacionar el representante de venta con el cliente y probar cómo funciona el uso de la relación muchos-a-muchos en SSAS. Nuestro diagrama de estrellas quedaría de la siguiente manera:
SSAS trata a las tablas intermedias con relaciones muchos a muchos como Fact Tables y por tanto debemos agregarla como un nuevo Measure Group de nuestro cubo. El contenido de nuestra tabla FactManyToMayPersonAndCustomer se muestra a continuación:
Como mencionamos anteriormente, los registros ingresados en nuestra tabla fact (versión 2) son idénticos a como se registraron en la tabla de ventas de nuestro sistema fuente (sólo 3 registros, tal como sucedieron los hechos en la realidad):
Al crear el nuevo cubo versión 2 con el asistente de SSAS, seleccionamos los 2 fact tables conteniendo las ventas y la relación muchos a muchos. Automáticamente el asistente detecta que existe una relación de varios a varios entre el representante de venta y nuestro fact table principal con las ventas. En ese caso sólo nos queda cambiarle la propiedad Visible a False a la falsa medida creada para el fact de muchos a muchos ya que no queremos que se muestre al usuario final. El tab de Dimension Usage queda de la siguiente manera:
Los detalles de la relación muchos-a-muchos se muestra a continuación:
Luego de procesar nuestro nuevo cubo visualizamos los datos y obtenemos los siguientes resultados:
El total de productos se muestra correctamente con 18 unidades. Al agregar la dimensión de cliente y producto los resultados también son consistentes con lo esperado:
Ahora agregamos la jerarquía de representante de venta y notamos que los resultados son consistentes de acuerdo a la solución parcial que tenemos (sólo estamos considerando la relación entre cliente y representante en nuestra tabla muchos-a-muchos, nos falta agregar el producto o categoría):
Como podemos ver, los resultados se están agregando correctamente para el escenario planteado, es decir, la relación muchos-a-mucho sólo suma los valores únicos o distinct a nivel de cada territorio y distrito.
En teoría, para completar la solución sólo faltaría agregar a la tabla de muchos a muchos el campo categoría o en este caso, los productos pertenecientes a la categoría.
(INTENTO DE) SOLUCION # 3 - Implementando relación muchos-a-muchos entre tres tablas:
Según lo analizado en el caso anterior vamos a agregar los productos a la relación muchos-a-muchos:
Nuestro fact de muchos a muchos contiene los siguientes valores:
Nuestro fact principal de asignación de ventas se mantiene idéntico al que tuvimos en el intento de solución # 2, pero sí cambiamos las relaciones en el tab de Dimension Usage para indicarle a SSAS que representante de venta y producto se derivan de relaciones muchos a muchos:
Recuerden que el segundo measure group se agrega sólo con el propósito de crear las relaciones muchos-a-muchos, por tanto no existen medidas visibles para el usuario final provenientes de ese fact table.
Explorando los resultados de nuestro nuevo cubo tenemos:
Los valores a nivel de representante se muestran correctos, pero como se muestra a continuación al agregar la jerarquía de producto nuevamente se desvirtúan los resultados:
El problema radica en que a diferencia del modelo relacional, SSAS sólo entiende correctamente las relaciones muchos-a-muchos entre 2 tablas, pero en nuestro caso tenemos 3. A pesar de que el representante de venta 1 sólo vendió 10 unidades del producto 1, el cubo nos muestra 18, incluso nos muestra también ventas del producto 3 a pesar de que no existieron ventas para dicho producto. SSAS entiende de nuestra tabla muchos-a-muchos que hay una relación indirecta entre el representante 2 y el producto 3 a pesar de que no hay ventas para el mismo.
LA SOLUCION - Implementando relación muchos-a-muchos y referenciadas:
Después de darle muchas vueltas al problema y probar muchos intentos más de solución, encontramos lo que buscábamos, pero para no nos bastó con usar las relaciones muchos-a-muchos sino tuvimos que recurrir también a las relaciones referenciadas entre dimensiones (referenced relations). En este punto debo de declarar que nunca vi este tipo de modelaje de estrellas tan particular en los libros de Kimball y otros autores y que probablemente se sale de las bases tradicional de diseño ya que tal como en los casos anteriores nos ayudamos de pseudo-dimensiones o facts que deberán ser cargadas previamente con el mecanismo del ETL para preparar los datos como las necesitamos para obtener los resultados deseados. Aquí debo hacer un paréntesis para dar crédito a mi amigo José Miranda que contribuyó significativamente al análisis para llegar a esta solución.
Nuestro datamart incluye una relación muchos-a-muchos entre la dimensión cliente y producto. Esta dimensión nos obliga a repetir nuestro catálogo de productos para cada cliente, ya que en teoría cualquier cliente puede vender cualquier producto. Por lo mismo, probablemente el tamaño de esta dimensión sea bastante significativo con respecto a otras dimensiones, pero vamos a sacrificar algo de espacio en disco para poder obtener los resultados como los necesitamos.
Nuestra pseudo-dimensión tiene una clave primaria que identifica únicamente la combinación entre el cliente y producto para permitir la relación de esta tabla con nuestro fact muchos-a-muchos:
Por tanto de acuerdo a nuestras reglas de negocio, asignamos el representante de venta al cliente y producto respectivo:
Hemos creado nuestra pseudo-dimensión CustomerAndProduct.dim para que nos permita luego crear las relaciones tipo referenced con cliente y producto. La pseudo-dimensión queda de la siguiente manera:
Ahora la parte clave de nuestra solución, el tab Dimension Usage donde tenemos nuestra pseudo-dimensión con propiedad visible = false para que no se muestre al usuario final, la relación muchos-a-muchos con la dimensión representante de venta y relaciones referenced con cliente y producto:
Ahora exploramos los resultados:
Correctamente tenemos asignadas las cantidades según representante y su jerarquía. Ahora agregamos producto y cliente para tener todas las combinaciones:
Al finnnnnnn!!! Tal como se muestra ahora tenemos la asignación de las ventas por representante tal como se requiere.
Aquí tienen las bases de datos en SQL Server 2008 y proyecto de Analysis Services 2008 usado para la creación de este post. Deben hacer attach para agregar la base de datos Source y DataMart. No olviden cambiar el data source dentro del proyecto de SSAS para que apunte a su servidor.
Alan.