Sunday, January 31, 2010

¿Cómo crear una pipeline de ventas? (Cross-Time Sales Pipeline Howto)

Desde que me dedico a la inteligencia de negocio y en especial a la integración de datos, me han preguntado muchas veces cómo crear un informe de pipeline a partir de un histórico de oportunidades de venta, o lo que es lo mismo, un informe dónde se cuenta cuántas filas de una tabla existen en un determinado estado en unos instantes de tiempo marcados (Cross-time), por ejemplo cada mes.

Después de buscar mucho y encontrar poco, la solución siempre ha sido crear una tabla de snapshots incrementales para cada instante del tiempo que contuviera la información básica con la que crear el informe. En ella utilizaría un procedimiento almacenado o bien un proceso SSIS para crear un bucle que me contase por ejemplo por cada mes y por cada estado, cuántas oportunidades de venta existían y guardar esta cifra en esta tabla.

Hace poco he vuelto a encontrarme con esta necesidad, y en este caso un conjunto de filas reducido y un buen motor y esquema relacional me ha permitido implementar una solución sencilla y directa sin pasar por una tabla temporal. La idea es la misma, se necesita un proceso intermedio que esta vez implemento con una sencilla función escalar en T-SQL, pero puede servir de base a cualquiera que se encuentre con este problema alguna vez.

La idea es utilizar la siguiente función en una consulta donde cruzamos la tabla de históricos con una tabla de dimensión de tiempo para recuperar los valores que buscamos para cada mes:


CREATE FUNCTION [dbo].[fnFINGetPipelineAmount](@date as datetime, @state as varchar(20))
RETURNS numeric(38,20)
AS
BEGIN
DECLARE @total numeric(38,20);
SET @total = (select sum(New_EstRevenueQ) from
(select rn=ROW_NUMBER() over (partition by ContractID, OpportunityId order by StartDate desc), *
from Pipeline
where ((month(@date) between month(StartDate) and MONTH(EndDate)) or (MONTH(StartDate) = month(@date) and EndDate is null))
and YEAR(StartDate) = year(@date)
AND (state = @state)
) as sq where sq.rn = 1)
RETURN @total;
END;



Ahora sólo necesitamos la dimension tiempo para invocar a esta función y recuperar la pipeline completa para un lapso de tiempo determinado, por ejemplo el año pasado.


SELECT month, dbo.fnFINGetPipelineAmount(Date, 'New') AS NewAmount, dbo.fnFINGetPipelineAmount(Date, 'Proof') AS ProofAmount, dbo.fnFINGetPipelineAmount(Date, 'Close') AS CloseAmount
FROM vwFIN_CubeDimTime
where year = 2009 and IsLastDayOfMonth = 1



Cuando el número de filas comienza a dispararse esta solución se vuelve demasiado costosa con facilidad. Para profundizar en este tema podemos leer una solución más compleja y efectiva en el whitepaper de Microsoft titulado "The many-to-many revolution" pág 57., de Marco Russo, el cual recomiendo a aquellos que les guste el mundo OLAP. Lo podéis encontrar aquí.