Publicado el Deja un comentario

Análisis ABC Pareto en DAX y SQL Server

El análisis ABC se deriva del principio de Pareto del 80/20 la teoría dice que el 20% de tu esfuerzo generan el 80% de los resultados. Según este principio el 80% de tus ganancias puede estar concentrado tan solo en el 20% de tus productos (aplica en inventarios, ventas, costo, entre otros).

En este post veremos cómo realizar el análisis ABC  usando DAX  y SQL Server, no se trata de comparar que es lo más óptimo o deducir que es mejor. Como debes saber todo depende del contexto en que realices el análisis; las herramientas que tengas disponibles y tu experticia al momento de encontrar soluciones.

Los datos que estamos usando provienen de la BDD de pruebas AdventureWorksDW2017. Mira el post sobre bases de datos para SQL Server, MySql, con las que puedes entrenar en el Post:

ABC en SQL SERVER:

En la primera versión de esta consulta usaré un WITH Comon Table Expresion (CTE), para generar una tabla temporal donde se agrupa la cantidad de venta por modelo de producto. La tabla de nombra como VentaProductos  

USE AdventureWorksDW2017
GO

WITH ProductSales AS
(
--Cantidad de ventas por modelo

SELECT
pto.[ModelName],
SUM(va.[SalesAmount]) Ventas
FROM [dbo].[FactInternetSales] va
INNER JOIN [dbo].[dimProduct] pto
ON va.[ProductKey] = pto.[ProductKey]
GROUP BY
pto.[ModelName]
)
SELECT
ps.[ModelName],
ps.[Ventas],
SUM(ps.[Ventas]) OVER (ORDER BY ps.[Ventas] DESC) AS VentaAcumulada,
--En una tabla ordenada DESC la suma del acumulado
SUM(ps.[Ventas]) OVER () AS TotalVentas,
--el valor total de venta para el que se divide el acumulado
SUM(ps.[Ventas]) OVER (ORDER BY ps.[Ventas] DESC) / SUM(ps.[Ventas]) OVER () AS PorcetajeAcumulado,
--el valor porcentual de ventas acumulada para total ventas
CASE
WHEN SUM(ps.[Ventas]) OVER (ORDER BY ps.[Ventas] DESC) / SUM(ps.[Ventas]) OVER () <= 0.7
THEN 'A'
WHEN SUM(ps.[Ventas]) OVER (ORDER BY ps.[Ventas] DESC) / SUM(ps.[Ventas]) OVER () <= 0.9
THEN 'B'
ELSE 'C'
-- la condicion para clasificar A, el 20% B y el restante se clasifica como C
END AS Clasif
FROM ProductSales ps
GROUP BY
ps.[ModelName],
ps.[Ventas];

--Los modelos clasificados como A, son los que mayor ganancias generan. 

ABC con DAX en Power Pivot

Para realizar el mismo ejercicio usando Power Pivot, se va requerir usar columnas calculadas, esto va ser posible gracias al contexto de fila que se puede aplicar en una tabla con CALCULATE. De la misma forma se va utilizar la BDD AdventureWorksDW2017 y trae al modelo de datos la tabla Ventas, y Productos, el modelo se ve de la siguiente manera

Se debe  crear las siguientes columnas calculadas. SumaCantidadVenta, VentaModelo, VentaAcumModel, %deTotal y ClasifABC, todo esto lo realizamos en la tabla de DimProducts.

SumaCantidadVenta:=
SUMX(
	RELATEDTABLE(FactInternetSales),
		FactInternetSales[SalesAmount])
VentaModelo:=
SUMX(
	FILTER(DimProduct,DimProduct[ModelName]=
			EARLIER(DimProduct[ModelName])),
				DimProduct[SumaCantidadVenta]
		)
VentaAcumModel:=
      SUMX(
	FILTER(DimProduct,
		DimProduct[VentaModelo]>=
			EARLIER(DimProduct[VentaModelo])
			),
		         DimProduct[SumaCantidadVenta]
	)
%deTotal:=
     DIVIDE(
	[VentaAcumModel],
			SUM(DimProduct[SumaCantidadVenta]
			))
ClasifABC:=
      IF([%deTotal]<=0.7,"A",
	   IF([%deTotal]<=0.9, "B",
			"C")
                    )

Luego de generar las columnas calculadas la tabla DimProducts se vería así:

Haciendo una comparación entre la tabla que resulta de la consulta SQL Server y generando una tabla dinámica de las medidas en DAX el resultado es el mismo.

Conclusión

En ambos casos llegaremos al mismo resultado, es un ejercicio muy interesante de realizar si estás iniciando con SQL o DAX. Como te decía en el inicio de este post no pretendo decirte qué o cuál es mejor. Depende del contexto de tus datos, y de las tecnologías que tengas al alcance. En lo personal y como usuario más habitual de Excel prefiero DAX. Pero si te enfrentas a un contexto de de datos masivos, puedes generar la consulta de SQL e importarla a Power Pivot o Power Query directamente.

Si este artículo te gusto, te invito a que te suscribas al portal y recibirás información interesante sobre técnicas de análisis de datos.

Las matemáticas no mienten, lo que hay son muchos matemáticos mentirosos.

H.D. thoreau

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *