Publicado el Deja un comentario

BASE DE DATOS SQL SERVER Y MySQL PARA ENTRENAR

Al inicio todo es dudas y mucha investigación. Aprender las consultas básicas en SQL es pan comido. Pero lo cierto es que se necesita una práctica constante para dominar e incrementar tus habilidades.

Ya sea que tú camino es ser un administrador de bases de datos o un analista de datos o data science.

En este post puedes encontrar el .bak de la base de datos de entrenamiento de SQL SERVER ContosoRetailDW_2019.bak y el query de MySQL employees.sql.

MySQL Employees.sql

Debes descargar el query.sql y ejecutarlo desde Workbench una vez ejecutado encontraras las siguientes entidades con sus relaciones. Si ejecutas ingeniería  inversa obtendrás el diagrama de la base

Esquema bdd Mysql employees

Podrás revisar la estructura de las relaciones y a su vez reforzar tus conocimientos sobre consultas. Si bien el lenguaje SQL es un estándar para la mayoría de bases de datos relacionales existen diferencias a la hora de realizar las consultas.

Por ello es bueno que tu entrenamiento se base en mas de una tecnología, aunque consideres que al final de día es lo mismo. Cada herramienta tiene particularidades que la hacen diferente y eficiente en el contexto que se la usa.

SQL Server ContosoRetailDW_2019.bak

Referencial de Contos

Para usar esta base de datos, debes restaurarla desde el Management Studio te sugiero una instalación 2017 o superior para que evites cualquier inconveniente.

Te será muy útil para conectarla desde servicios directamente desde Excel en caso este puliendo tus habilidades en dax o lenguaje M.

SQL Server es un servicio de pago pero puedes usarla de manera gratuita con bases de hasta 10gb. Usa una instalación Express.

Si deseas más información para desarrollar tus habilidades en análisis de datos, puedes suscribirte de manera gratuita a nuestro portal.

Cuán vano es sentarse a escribir cuando aún no te has levantado para vivir

H.D. THOREAU

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