En casos de negocio de ventas al por menor (y tantos otros) puede ser interesante controlar el tiempo en stock de los productos que trabajamos.
En la entrada de hoy vamos a mostrar una solución que implementamos para un cliente que necesitaba una serie de métricas para controlar la rotación de su stock, y que en un primer momento nos dio problemas de eficiencia en el tiempo de cálculo implementado en MDX.
En la entrada de hoy vamos a mostrar una solución que implementamos para un cliente que necesitaba una serie de métricas para controlar la rotación de su stock, y que en un primer momento nos dio problemas de eficiencia en el tiempo de cálculo implementado en MDX.
Partimos de una vista que controla los movimientos de Stock, en este caso estamos filtrando por una tienda en concreto y por 6 lotes que contienen 2 productos distintos. Por dejarlo comentado, aunque no es el objetivo de este post, aclaramos que esta vista de movimientos de stock fue creada a partir de las tablas de compras y ventas, de manera que una compra marcaba unidades positivas, y una venta restaba.
A modo ilustrativo del problema a resolver montamos un pequeño Cubo con tres Dimensiones y un grupo de medidas:
Con la vista de movimientos de stock creamos un grupo de medidas, donde lo que nos interesa es un SUM de las unidades, tanto las que entran como las que salen del stock, esta será nuestra métrica base para conocer el stock acumulado.
No debemos confundir el movimiento de las unidades con el stock.
No debemos caer en la tentación, y creer que un sumatorio de unidades cruzado por una fecha bastaría para conocer el stock al final de dicho período, esto no es así.
El stock debe contemplar el movimiento de unidades desde que entraron hasta la fecha o periodo en la que estamos haciendo la observación, si vemos el sumatorio de unidades en un periodo, solo tendremos en cuenta las unidades compradas y vendidas dentro de ese periodo.
Muy bien… ¿Cómo calcular el Stock?
Partimos de una medida calculada de Stock base, a partir de esta medida luego podremos controlar casos de interés.
Observe el código MDX, lo que estamos haciendo es agregar la medida directa ‘Unidades’ de la tabla de hechos, desde ‘el principio de los tiempos’ de nuestra dimensión Fecha, hasta el último hijo del nivel de la jerarquía que se está seleccionando en los filtros.
Observe el código MDX, lo que estamos haciendo es agregar la medida directa ‘Unidades’ de la tabla de hechos, desde ‘el principio de los tiempos’ de nuestra dimensión Fecha, hasta el último hijo del nivel de la jerarquía que se está seleccionando en los filtros.
CREATE MEMBER CURRENTCUBE.[Measures].[Stock base]
AS
case
when [Fecha].[Fecha Calendario].currentmember.level is [Fecha].[Fecha Calendario].[Anio]
then SUM({NULL:[Fecha].[Fecha Calendario].currentmember.lastchild.lastchild.lastchild.lastchild}, [Measures].[Unidades])
when [Fecha].[Fecha Calendario].currentmember.level is [Fecha].[Fecha Calendario].[Semestre]
then SUM({NULL:[Fecha].[Fecha Calendario].currentmember.lastchild.lastchild.lastchild}, [Measures].[Unidades])
when [Fecha].[Fecha Calendario].currentmember.level is [Fecha].[Fecha Calendario].[Trimestre]
then SUM({NULL:[Fecha].[Fecha Calendario].currentmember.lastchild.lastchild}, [Measures].[Unidades])
when [Fecha].[Fecha Calendario].currentmember.level is [Fecha].[Fecha Calendario].[Trimestre]
then SUM({NULL:[Fecha].[Calendario].currentmember.lastchild}, [Measures].[Unidades])
else SUM({NULL:[Fecha].[Fecha Calendario].currentmember}, [Measures].[Unidades])
end ,
FORMAT_STRING = "#,##;-#,##",
VISIBLE = 0 , ASSOCIATED_MEASURE_GROUP = 'Stock';
Por ejemplo, en caso de estar cruzando el stock por año haríamos un sumatorio de Unidades, desde el ‘principio de los tiempos’ hasta el último día del año. Ilustramos el ejemplo a continuación.
Esta idea como se puede ver en el código se aplica de forma similar al resto de niveles de la jerarquía fecha.
Esta métrica es la que da sentido al resto del post, lógicamente si no tenemos un control del stock no podremos conocer la antigüedad del lote.
Esta métrica es la que da sentido al resto del post, lógicamente si no tenemos un control del stock no podremos conocer la antigüedad del lote.
Por ultimo en caso de que el stock sea cero lo ponemos a NULL. De esta manera no se mostrarán valores para los casos de lotes sin Stock.
CREATE MEMBER CURRENTCUBE.[Measures].[Stock] AS iif([Measures].[Stock base] = 0, null, [Measures].[Stock base]), FORMAT_STRING = "#,##;-#,##", VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Stock';
Nota: Si bien este tipo de condiciones se pueden aplicar dentro del mismo calculo, por claridad se expone de esta manera.
Calcular el tiempo en Stock
Si bien tenemos la función datediff() que podemos utilizar en MDX para calcular la diferencia en días entre la fecha de aprovisionamiento y la fecha máxima del periodo de observación, nos encontramos en nuestro caso que penaliza demasiado el tiempo de cálculo de la métrica, de ahí surge la alternativa planteada en este post.
La solución alternativa que aplicamos para buscar una mejora en estos tiempos fue la siguiente.
En la vista de stock en el Datawarehouse creamos un nuevo campo que en el que en caso de tratarse de una compra o aprovisionamiento se calcula la diferencia de días respecto a una fecha fija de referencia.
iif(TipoMovimiento = 'Aprovisionamiento inicial' or TipoMovimiento = 'Compra', datediff(d, '1900-01-01', FechaMovimiento), null) as DistanciaDias
Con este campo creado en la vista del DataWarehouse actualizamos el DSV del cubo y creamos la medida física ‘Distancia Días’ como MAX(DistanciaDias). Esta la utilizaremos en pasos posteriores.
Ahora en MDX creamos la medida ‘current_day’ que consiste en obtener la diferencia en días de la misma forma que hicimos en la vista de Stock, pero entre el último hijo del currentmember y la fecha de referencia.
CREATE MEMBER CURRENTCUBE.[Measures].[current_day]
as iif([Measures].[Stock] > 0 ,datediff('d', CDate('01/01/1900'),
case
when [Fecha].[Fecha Calendario].currentmember.level is [Fecha].[Fecha Calendario].[Anio] then [Fecha].[Fecha Calendario].currentmember.lastchild.lastchild.lastchild.lastchild.member_value when [Fecha].[Fecha Calendario].currentmember.level is [Fecha].[Fecha Calendario].[Semestre] then [Fecha].[Fecha Calendario].currentmember.lastchild.lastchild.lastchild.member_value when [Fecha].[Fecha Calendario].currentmember.level is [Fecha].[Fecha Calendario].[Trimestre] then [Fecha].[Fecha Calendario].currentmember.lastchild.lastchild.member_value when [Fecha].[Fecha Calendario].currentmember.level is [Fecha].[Fecha Calendario].[Mes Anio] then [Fecha].[Fecha Calendario].currentmember.lastchild.member_value else [Fecha].[Fecha Calendario].currentmember.member_value end), null) , FORMAT_STRING = "#,##;-#,##", VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP= 'Stock';
Comprobamos primero que tenemos Stock para esa línea, luego obtenemos la diferencia en días entre el último hijo de la jerarquía del currentmember y la fecha de referencia ‘01/01/1900’.
Por otro lado obtenemos el valor de la medida [Measures].[Distancia Días] para el mismo periodo
CREATE MEMBER CURRENTCUBE.[Measures].[Max_fecha_compra]
as iif([Measures].[Stock] > 0, case
when [Fecha].[Fecha Calendario].currentmember.level is [Fecha].[Fecha Calendario].[Anio]
then max({NULL:[Fecha].[Fecha Calendario].currentmember.lastchild.lastchild.lastchild.lastchild}, [Measures].[Distancia Dias])
when [Fecha].[Fecha Calendario].currentmember.level is [Fecha].[Fecha Calendario].[Semestre]
then max({NULL:[Fecha].[Fecha Calendario].currentmember.lastchild.lastchild.lastchild}, [Measures].[Distancia Dias])
when [Fecha].[Fecha Calendario].currentmember.level is [Fecha].[Fecha Calendario].[Trimestre]
then max({NULL:[Fecha].[Fecha Calendario].currentmember.lastchild.lastchild}, [Measures].[Distancia Dias])
when [Fecha].[Fecha Calendario].currentmember.level is [Fecha].[Fecha Calendario].[Trimestre]
then max({NULL:[Fecha].[Fecha Calendario].currentmember.lastchild}, [Measures].[Distancia Dias])
else max({NULL:[Fecha].[Fecha Calendario].currentmember}, [Measures].[Distancia Dias])
end, null),
FORMAT_STRING = "#,##;-#,##",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Stock';
Y por último calculamos la diferencia:
CREATE MEMBER CURRENTCUBE.[Measures].[Antigüedad Lote] AS [Measures].[current_day] - [Measures].[Max_fecha_compra], FORMAT_STRING = "#,##;-#,##", VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Stock';
Recapitulando
Stock
– Marcamos las entradas y salidas de Stock en la vista de DW.
– Creamos una medida física en el cubo como SUM de las entradas y salidas de stock.
– Obtenemos Stock base, como un sumatorio de la medida física desde NULL hasta el último día del periodo seleccionado.
– Controlamos que si el stock es cero el resultado sea NULL.
Stock
– Marcamos las entradas y salidas de Stock en la vista de DW.
– Creamos una medida física en el cubo como SUM de las entradas y salidas de stock.
– Obtenemos Stock base, como un sumatorio de la medida física desde NULL hasta el último día del periodo seleccionado.
– Controlamos que si el stock es cero el resultado sea NULL.
Antigüedad
– En la vista de Stock de DW calculamos la distancia en días de las fechas de entrada en stock, respecto a una fecha de referencia que se sepa que esta fuera de cualquier periodo bajo análisis (en nuestro caso usamos 1900/01/01).
– Creamos una medida dinámica en el cubo que obtenga la distancia en días entre el ultimo día del periodo seleccionado y la fecha de referencia.
– Creamos una medida dinámica en el cubo, que de las fechas de entrada en Stock obtenga la última dentro del periodo observado (la mayor distancia en días).
– En la vista de Stock de DW calculamos la distancia en días de las fechas de entrada en stock, respecto a una fecha de referencia que se sepa que esta fuera de cualquier periodo bajo análisis (en nuestro caso usamos 1900/01/01).
– Creamos una medida dinámica en el cubo que obtenga la distancia en días entre el ultimo día del periodo seleccionado y la fecha de referencia.
– Creamos una medida dinámica en el cubo, que de las fechas de entrada en Stock obtenga la última dentro del periodo observado (la mayor distancia en días).
1 comment
Hola Guillermo, me parece muy interesante tu trabajo. ¿Podrías poner la fórmula en la que te has basado para hacer esa programación.? Gracias