Construcción del PBIX Anti-Patrones DAX

Guía completa para crear un informe de Power BI que demuestre los principales problemas de rendimiento con SQL Server en modo DirectQuery

📦 Archivos incluidos en este paquete

Archivo	Descripción
`01_Crear_BaseDatos.sql`	Crea la base de datos DAXPerformanceDemo, tablas, índices y relaciones
`02_Cargar_Datos.sql`	Scripts BULK INSERT para cargar los CSV generados
`03_Monitorizar_Consultas.sql`	Extended Events, vistas y procedimientos para monitorizar queries
`datos_csv/`	30 archivos CSV con 3M filas de FactSales + dimensiones
`04_Medidas_DAX.txt`	Todas las medidas listas para copiar y pegar en Power BI
`05_DAX_Studio_Guide.txt`	Instrucciones para capturar CallbackDataID y Query Plans

Paso 1: Preparar SQL Server

Crear la base de datos

Abre SQL Server Management Studio (SSMS), conecta a tu instancia y ejecuta 01_Crear_BaseDatos.sql. Esto creará la base de datos DAXPerformanceDemo con todas las tablas e índices.

Copiar los archivos CSV al servidor

Copia la carpeta datos_csv/ a una ruta accesible por SQL Server, por ejemplo C:\datos_csv\. El servicio SQL Server debe tener permisos de lectura sobre esa carpeta.

Paso 2: Crear el modelo en Power BI Desktop (DirectQuery)

Conectar a SQL Server en modo DirectQuery

Abre Power BI Desktop → Obtener datos → SQL Server
Servidor: localhost (o tu instancia)
Base de datos: DAXPerformanceDemo
En el navegador, selecciona las 5 tablas: DimDate, DimProduct, DimCustomer, DimRegion, FactSales
Crítico: En la ventana de carga, selecciona DirectQuery (no Import)

Configurar relaciones

En la vista de modelo, verifica que las relaciones se hayan detectado automáticamente. Si no, créalas manualmente:

FactSales[DateKey] → DimDate[DateKey] (1:N, filtro único)
FactSales[ProductID] → DimProduct[ProductID] (1:N)
FactSales[CustomerID] → DimCustomer[CustomerID] (1:N)
FactSales[RegionID] → DimRegion[RegionID] (1:N)

Paso 3: Crear las medidas DAX (Anti-Patrones vs. Optimizadas)

Crea una tabla dedicada llamada Medidas (Modelado → Nueva tabla → Medidas = {0}) y añade las siguientes medidas. Cada anti-patrón tiene su versión optimizada para comparar rendimiento.

CRÍTICO #1 SUMX sobre tabla de hechos

CRÍTICO #2 Condición dentro de SUMX (London Sales)

-- ANTI-PATRÓN: IF dentro de SUMX

London Sales SUMX (MAL) =

  SUMX(FactSales,

    IF(FactSales[RegionID] = 6,

      FactSales[Amount],

      BLANK()

    )

  )

-- OPTIMIZADO: CALCULATE + predicado

London Sales CALC (BIEN) =

  CALCULATE(

    SUM(FactSales[Amount]),

    DimRegion[RegionName] = "London"

  )

ALTO #3 FILTER sobre tabla de hechos

-- ANTI-PATRÓN: FILTER(FactSales, ...)

-- Materializa toda la tabla de hechos

Filtered Sales FILTER (MAL) =

  CALCULATE(

    SUM(FactSales[Amount]),

    FILTER(FactSales, FactSales[RegionID] = 3)

  )

-- OPTIMIZADO: predicado directo en CALCULATE

Filtered Sales Pred (BIEN) =

  CALCULATE(

    SUM(FactSales[Amount]),

    DimRegion[RegionName] = "East"

  )

ALTO #4 SUMMARIZECOLUMNS en medida

-- ANTI-PATRÓN: SUMMARIZECOLUMNS dentro de medida

-- Rompe fusión de consultas SQL

Top Products SUMMARIZE (MAL) =

  SUMX(

    SUMMARIZECOLUMNS(

      FactSales[ProductID],

      "Sales", SUM(FactSales[Amount])

    ),

    [Sales]

  )

-- OPTIMIZADO: SUMX + VALUES sobre dimensión

Top Products VALUES (BIEN) =

  SUMX(

    VALUES(DimProduct[ProductName]),

    CALCULATE(SUM(FactSales[Amount]))

  )

MEDIO #5 DISTINCTCOUNT alta cardinalidad

MEDIO #6 ALL indiscriminado

-- ANTI-PATRÓN: ALL sobre tabla grande

-- Fuerza scan completo sin WHERE

All Sales ALL (MAL) =

  CALCULATE(

    SUM(FactSales[Amount]),

    ALL(FactSales)

  )

-- OPTIMIZADO: ALLSELECTED respeta selección

All Sales Selected (BIEN) =

  CALCULATE(

    SUM(FactSales[Amount]),

    ALLSELECTED(FactSales)

  )

ALTO #7 Inteligencia temporal nativa en DirectQuery

-- ANTI-PATRÓN: SAMEPERIODLASTYEAR nativo

Sales LY Native (MAL) =

  CALCULATE(

    SUM(FactSales[Amount]),

    SAMEPERIODLASTYEAR(DimDate[FullDate])

  )

-- OPTIMIZADO: inteligencia temporal manual

Sales LY Manual (BIEN) =

  VAR _Year = SELECTEDVALUE(DimDate[Year])

  VAR _Month = SELECTEDVALUE(DimDate[Month])

  RETURN

    CALCULATE(

      SUM(FactSales[Amount]),

      ALL(DimDate),

      DimDate[Year] = _Year - 1,

      DimDate[Month] = _Month

    )

CRÍTICO #8 CallbackDataID por filtros dinámicos en iteradores

-- ANTI-PATRÓN: CALCULATE anidado dentro de SUMX

-- Genera 100+ CallbackDataID

Sales con Check (MAL) =

  SUMX(FactSales,

    VAR _Check =

      CALCULATE(

        COUNTROWS(FactSales),

        FILTER(ALL(FactSales),

          FactSales[RegionID] = 6

        )

      )

    RETURN

      IF(_Check > 0, FactSales[Amount], BLANK())

  )

-- OPTIMIZADO: predicado simple

London Sales Simple (BIEN) =

  CALCULATE(

    SUM(FactSales[Amount]),

    DimRegion[RegionName] = "London"

  )

Paso 4: Diseñar las páginas del informe

📄 Página 1: "Anti-Patrones vs. Optimizado"

Crea una tabla comparativa con dos columnas: medida MAL (izquierda) y medida BIEN (derecha). Añade un slicer de DimDate[Year] para forzar recálculos al cambiar el filtro.

Visuales recomendados:

Tabla con: Total Sales SUMX (MAL) | Total Sales SUM (BIEN)
Tabla con: London Sales SUMX (MAL) | London Sales CALC (BIEN)
Tabla con: Filtered Sales FILTER (MAL) | Filtered Sales Pred (BIEN)
Tarjeta con: Unique Emails DC (MAL) | Unique Customers COUNT (BIEN)

📄 Página 2: "Inteligencia Temporal"

Gráfico de líneas con meses en el eje X y dos valores:

Sales LY Native (MAL) — línea roja
Sales LY Manual (BIEN) — línea verde

Añade un slicer de categoría de producto para aumentar la complejidad de las consultas.

📄 Página 3: "Auditoría DAX"

Tabla con todas las medidas del modelo usando INFO.MEASURES(). Incluye:

Nombre de la medida
Expresión completa
Flags de detección (Tiene_SUMX, Tiene_FILTER, etc.)
Nivel de riesgo calculado

Usa el script M del cuadro de mando original para generar esta tabla.

Paso 5: Medir el rendimiento y detectar CallbackDataID

5.1 Performance Analyzer (Power BI Desktop)

5.2 DAX Studio: detectar CallbackDataID

Ejecutar y analizar

Pega la consulta DAX del Performance Analyzer y ejecuta. En la pestaña Server Timings, busca:

CallbackDataID — Cada instancia representa una llamada fila a fila del FE al SE. En DirectQuery, esto se traduce en subconsultas SQL.
FE Time — Si es alto (>50% del total), el FE está haciendo trabajo que debería estar en SQL.
SQL Queries — En DirectQuery, DAX Studio muestra las consultas SQL generadas. Si hay muchas para una sola medida, hay iteración.

💡 Cómo identificar CallbackDataID en el Query Plan: Busca nodos del plan con la etiqueta CallbackDataID o VertiPaq SE Query que aparecen repetidamente. En DirectQuery, estos se traducen en SQL Query con nombres como DirectQuery-1, DirectQuery-2, etc. Si ves más de 5 SQL Queries para una sola medida, tienes un problema de iteración.

5.3 SQL Server: contar consultas en tiempo real

Contar queries por interacción

Ejecuta antes de interactuar con Power BI:
EXEC sp_CountPowerBIQueries @SecondsWindow = 15;
Mientras espera, cambia un slicer o filtro en Power BI. Al finalizar, verás cuántas consultas SQL se lanzaron.

Detectar CallbackDataID desde SQL

Ejecuta:
EXEC sp_DetectCallbackDataID @MinQueriesPerSPID = 20, @MaxDurationMs = 50;
Si devuelve "POSIBLE CallbackDataID", significa que Power BI está lanzando muchas consultas cortas desde el mismo SPID — el patrón clásico de iteración fila a fila.

Paso 6: Tabla de auditoría automática (DAX)

Crea una tabla AuditoriaMedidas en Power BI con el siguiente DAX. Esto escanea automáticamente todas las medidas del modelo y clasifica su riesgo.

AuditoriaMedidas =
  ADDCOLUMNS(
    INFO.MEASURES(),
    "Tiene_SUMX", IF(CONTAINSSTRING([Expression], "SUMX"), "SÍ", "NO"),
    "Tiene_AVERAGEX", IF(CONTAINSSTRING([Expression], "AVERAGEX"), "SÍ", "NO"),
    "Tiene_FILTER", IF(CONTAINSSTRING([Expression], "FILTER("), "SÍ", "NO"),
    "Tiene_ALL", IF(CONTAINSSTRING([Expression], "ALL(") || CONTAINSSTRING([Expression], "REMOVEFILTERS"), "SÍ", "NO"),
    "Tiene_SUMMARIZECOLUMNS", IF(CONTAINSSTRING([Expression], "SUMMARIZECOLUMNS"), "SÍ", "NO"),
    "Tiene_DISTINCTCOUNT", IF(CONTAINSSTRING([Expression], "DISTINCTCOUNT"), "SÍ", "NO"),
    "Tiene_SEARCH", IF(CONTAINSSTRING([Expression], "SEARCH") || CONTAINSSTRING([Expression], "CONTAINSSTRING"), "SÍ", "NO"),
    "Nivel_Riesgo",
      SWITCH(TRUE(),
        CONTAINSSTRING([Expression], "SUMX") || CONTAINSSTRING([Expression], "AVERAGEX") || CONTAINSSTRING([Expression], "SUMMARIZECOLUMNS"), "CRÍTICO",
        CONTAINSSTRING([Expression], "FILTER("), "ALTO",
        CONTAINSSTRING([Expression], "ALL(") || CONTAINSSTRING([Expression], "REMOVEFILTERS") || CONTAINSSTRING([Expression], "DISTINCTCOUNT"), "MEDIO",
        "OK"
      )
  )

Paso 7: Medidas de diagnóstico del dashboard

Crea estas medidas en la tabla Medidas para tener KPIs de rendimiento en el propio informe:

_Total Medidas = COUNTROWS(INFO.MEASURES())

_Medidas Críticas =
  COUNTROWS(
    FILTER(INFO.MEASURES(),
      CONTAINSSTRING([Expression], "SUMX") ||
      CONTAINSSTRING([Expression], "AVERAGEX") ||
      CONTAINSSTRING([Expression], "SUMMARIZECOLUMNS")
    )
  )

_Medidas de Alto Riesgo =
  COUNTROWS(
    FILTER(INFO.MEASURES(),
      CONTAINSSTRING([Expression], "FILTER(")
    )
  )

_Riesgo Total = [_Medidas Críticas] + [_Medidas de Alto Riesgo]

_Porcentaje Riesgo = DIVIDE([_Riesgo Total], [_Total Medidas], 0)

_Color Riesgo =
  SWITCH(TRUE(),
    [_Porcentaje Riesgo] > 0.30, "🔴 CRÍTICO",
    [_Porcentaje Riesgo] > 0.15, "🟡 ALTO",
    "🟢 OK"
  )

_Estado Dashboard =
  IF([_Porcentaje Riesgo] > 0.30, "Requiere optimización urgente",
    IF([_Porcentaje Riesgo] > 0.15, "Revisar medidas lentas", "Rendimiento óptimo")   )

Resumen del flujo de trabajo

Fase	Herramienta	Qué buscar
Construcción	Power BI Desktop	Medidas MAL y BIEN en paralelo
Medición inicial	Performance Analyzer	Tiempo DAX > 3 segundos en visuales MAL
Diagnóstico DAX	DAX Studio	CallbackDataID, múltiples SQL Queries, FE Time alto
Diagnóstico SQL	SSMS + Extended Events	Ráfagas de consultas cortas del mismo SPID
Corrección	Power BI Desktop	Reescribir medidas usando patrones BIEN
Validación	Todas las anteriores	Comparar tiempos antes/después

🚨 Nota sobre DirectQuery y 3M filas: Con 3 millones de filas, los anti-patrones MAL pueden tardar 10-60 segundos en responder. Los patrones BIEN deberían responder en menos de 2 segundos. Si una medida MAL tarda menos de 5 segundos, aumenta la carga interactuando con múltiples slicers simultáneamente o añadiendo más visuales en la misma página.

🛠️ Construcción del PBIX: Anti-Patrones DAX en DirectQuery

Paso 1: Preparar SQL Server

Paso 2: Crear el modelo en Power BI Desktop (DirectQuery)

Paso 3: Crear las medidas DAX (Anti-Patrones vs. Optimizadas)

CRÍTICO #1 SUMX sobre tabla de hechos

CRÍTICO #2 Condición dentro de SUMX (London Sales)

ALTO #3 FILTER sobre tabla de hechos

ALTO #4 SUMMARIZECOLUMNS en medida

MEDIO #5 DISTINCTCOUNT alta cardinalidad

MEDIO #6 ALL indiscriminado

ALTO #7 Inteligencia temporal nativa en DirectQuery

CRÍTICO #8 CallbackDataID por filtros dinámicos en iteradores

Paso 4: Diseñar las páginas del informe

Paso 5: Medir el rendimiento y detectar CallbackDataID

5.1 Performance Analyzer (Power BI Desktop)

5.2 DAX Studio: detectar CallbackDataID

5.3 SQL Server: contar consultas en tiempo real

Paso 6: Tabla de auditoría automática (DAX)

Paso 7: Medidas de diagnóstico del dashboard

Resumen del flujo de trabajo