¿Qué es un lenguaje de consulta?
Un lenguaje de consulta es un lenguaje de programación que le permite enviar una solicitud a una base de datos y, a continuación, recibir datos en forma de tabla. Si solo tiene unos pocos cientos de filas de datos, esto no es demasiado útil. Si por otro lado tiene millones de filas de datos, entonces se vuelve muy útil.
La diferencia más importante entre DAX cuando se usa en Medidas y Columnas calculadas y DAX como lenguaje de consulta es que el lenguaje de consulta siempre devuelve una tabla. La tabla resultante constará de una o más columnas, ninguna o más filas y datos dentro de ninguna o más de las celdas de la tabla.
¿Por qué necesito un lenguaje de consulta?
Al crear un modelo de datos en Power BI o Power PivotPower Pivot para Excel, esencialmente está cargando datos tabulares en una base de datos de informes dedicada con el propósito expreso de análisis o informes. Una vez cargado, normalmente usted (el autor del informe) suele dedicar horas de esfuerzo a crear el modelo de datos para que se pueda usar para agregar valor a su empresa o trabajo. Por último, una vez hecho todo esto, la forma más común de usar el nuevo modelo de datos es crear informes y tablas dinámicas que presenten datos a un usuario de una manera que sea fácil de entender.
A continuación se muestra un ejemplo de un informe interactivo de Power BI que se ejecuta fuera de un modelo de datos de Power BI.
Y aquí hay una tabla dinámica que se ejecuta fuera de un modelo de datos de Excel Power PivotPower Pivot .
Ambas visualizaciones anteriores son resúmenes de los datos subyacentes y en ambos casos no se puede ver realmente ninguno de los datos subyacentes en sí, sólo el resumen. Las herramientas de visualización anteriores son excelentes y tienen una utilidad enorme, sin embargo, generalmente no son las mejores herramientas si desea ver los datos subyacentes en sí o si desea extraer conjuntos muy grandes de datos por cualquier razón.
Una vez que haya invertido todo este esfuerzo en la creación de su modelo de datos, va a querer usarlo en la mayor medida. Power PivotPower Pivot y Power BI pueden controlar muchos millones de filas de datos (a diferencia de Excel tradicional), por lo que la «vieja manera» de simplemente mirar la tabla subyacente no es realmente práctica en este nuevo y valiente mundo.
¡Muéstrame los datos subyacentes!
Si toma la tabla dinámica que mostré anteriormente, la mayoría de los usuarios de Excel serían conscientes de que pueden hacer doble clic en cualquier celda de valor (digamos 31.6% representando las ventas de Mountain Bike a personas de entre 30 y 40 años de edad). Al hacer doble clic en una tabla dinámica tradicional de esta manera, sucede algo mágico: una copia de los datos subyacentes se extrae de la fuente y aparece mágicamente en una nueva hoja en Excel. Una vez que haya terminado de mirar la copia de los datos, puede eliminar la nueva hoja y está de vuelta donde comenzó – la vida es buena.
Al hacer doble clic en una tabla dinámica tradicional de esta manera, se envía efectivamente una consulta a la base de datos subyacente y la respuesta de consulta es una tabla de datos.
Pero, ¿funciona con Power PivotPower Pivot?
En resumen, sí y no dependiendo de lo que esté buscando. La experiencia es diferente si tiene un modelo de datos de Excel Power PivotPower Pivot o si está conectado a un origen de datos SSAS externo. En mi experiencia los resultados son inconsistentes en el mejor de los casos y en el peor de los casos puede no darle lo que espera. Pero la buena noticia es que con DAX como lenguaje de consulta puede escribir fácilmente cualquier consulta que desee y extraer exactamente el subconjunto de datos que necesita. Ya no tiene que confiar en hacer doble clic en la tabla dinámica para interrogar sus datos.
Tiempo para aprender DAX como lenguaje de consulta
Ok, suficiente charla, tiempo para tomar en serio DAX como lenguaje de consulta. Ha invertido tiempo y esfuerzo en la creación del modelo de datos en Power BI o Power PivotPower Pivot y ahora desea extraer con razón un subconjunto de los datos para que pueda echar un vistazo a él. Empecemos.
Primero seleccione la herramienta correcta
Hay 3 enfoques generales que puede tomar para extraer una tabla de datos de su fabuloso modelo de datos.
- Use una herramienta dedicada como DAX Studio o SQL Server Management Studio. Escribí un artículo que describe cómo instalar y usar DAX Studio aquí.
- Escriba una «Nueva tabla» en Power BI Desktop. Escribí un artículo sobre cómo utilizar esta característica aquí.
- Utilice un Excel «hackde tabla» que aprendí de Marco Russo para extraer una tabla directamente en Excel. Voy a cubrir este truco más adelante en esta serie de artículos.
Voy a usar DAX Studio en este artículo de blog, pero puede usar «Nueva tabla» de Power BI si desea seguir el proceso y no tiene (o desea) DAX Studio. De hecho, si quieres aprender estas cosas, entonces realmente deberías seguir usando cualquier enfoque. En mi experiencia no se pueden aprender nuevos conceptos simplemente leyendo. Sin embargo, tenga en cuenta que el uso de Power BI y Excel como herramienta de consulta debe considerarse como una extracción temporal de los datos. Una vez que haya examinado los datos que desea, normalmente eliminaría la tabla para no terminar almacenando datos redundantes (siempre puede extraerlos de nuevo si los necesita).
Comencemos con la instrucción Evaluate
Todas las consultas DAX deben comenzar con la instrucción EVALUATE. Sin embargo, hay una excepción. Cuando se usa el botón «Nueva tabla» de Power BI para escribir una consulta, simplemente se omite esta instrucción EVALUATE. Este es sin duda otro ejemplo de «Syntax Sugar» donde los desarrolladores protegen al usuario de algunas de las sintaxis más confusas. Si está siguiendo y probando mis consultas a continuación en Power BI, simplemente omita la instrucción EVALUATE al escribir las consultas. Además, si alguna vez desea mover la consulta de DAX Studio a una tabla en Power BI, puede quitar la instrucción EVALUATE en ese momento.
Conexión de DAX Studio a la base de datos
Cuando inicio DAX Studio, se me pide que se conecte a mi base de datos. En este caso, tengo una instancia de Power BI Desktop con un modelo de datos Adventure Works que se ejecuta en mi PC y DAX Studio me da la opción de conectarme a eso. También puede conectarse a modelos de datos de Excel Power PivotPower Pivot y servidores SSAS tabulares.
Si desea obtener una visión general más completa de la interfaz de usuario de DAX Studio, lea mi otra publicación que mencioné anteriormente. De lo contrario, vamos a entrar en ello.
Devolver una tabla existente
La consulta DAX más fácil que puede escribir es simplemente devolver una copia completa de una tabla existente.
Cuando ejecuto esta consulta en DAX Studio (que se muestra como 1 a continuación) y, a continuación, presiono F5 para ejecutar, se devuelve toda la tabla (se muestra como 2). Un total de 18.484 filas de datos (mostradas como 3).
Esto en sí mismo no es demasiado útil, ya que es probable que ya tenga esta tabla en algún sistema de origen.
Lista de valores únicos
En DAX hay muchas funciones que devuelven tablas en lugar de valores escalares. He tratado este tema en profundidad la semana pasada para que pueda leer sobre eso aquí si lo desea.
Permítanme ahora mirar la función TODO.
Cuando ejecuto esta función de tabla como una consulta, puedo realmente «ver» los resultados de la consulta. En este caso, la consulta devuelve una tabla de una sola columna que consta de 4 filas: todas las categorías de productos únicas.
A continuación, echemos un vistazo a VALUES.
En este caso, la función de tabla VALUES devuelve exactamente el mismo resultado.
Ahora echemos un vistazo a DISTINCT.
Y lo mismo otra vez aquí.
Las tres funciones de tabla devuelven la misma tabla en esta instancia. En realidad, todos hacen cosas diferentes en diferentes escenarios de filtrado. Esta es una de las grandes cosas de DAX Queries es que puede «inspeccionar» lo que está sucediendo con estas funciones de tabla en diferentes escenarios. Más sobre eso más tarde.
Una tabla de dos columnas
La función ALL puede tomar 1 o más columnas como entrada (o una tabla). En el ejemplo siguiente he utilizado ALL para materializar una tabla de todas las combinaciones de Categoría de producto y Subcategoría de producto. Hay 37 combinaciones únicas en total.
Una tabla filtrada
La mayoría de las personas que conocen DAX sabrán de la función FILTER. La función FILTER devuelve una copia filtrada de una tabla. por ejemplo, vea el ejemplo siguiente:
Esta es una consulta mucho más interesante porque devuelve una tabla que contiene todos los productos que han vendido más de $200,000 de ventas en todo momento. Hay 49 productos de este tipo.
Alterar el contexto del filtro
Al escribir una medida DAX o una columna calculada, solo hay 1 forma de cambiar el contexto del filtro y que está utilizando la función CALCULATE. CALCULATE devuelve un valor escalar después de aplicar filtros. CALCULATETABLE es el hermano de CALCULATE. Como probablemente puede adivinar, CALCULATETABLE devuelve una tabla después de aplicar filtros.
Esta siguiente consulta devuelve exactamente el mismo resultado que la última consulta.
La principal diferencia con el uso de CALCULATETABLE en comparación con el uso de FILTER es que CALCULATETABLE puede devolver una tabla diferente a la propia función FILTER.
La consulta anterior utiliza la misma función FILTER de antes, pero en lugar de devolver una tabla que contiene los productos que vendieron más de $200,000, devuelve todas las transacciones de ventas reales que contribuyeron a la calificación de esos productos (consulte los resultados a continuación).
Combinación de funciones de tabla
Ahora que ve cómo funcionan algunas de las funciones de tabla estándar en una consulta, echemos un vistazo a combinarlas en una sola consulta.
Esta consulta aplica primero un filtro en todos los productos que han vendido más de $50,000 (línea 4 a continuación) y, a continuación, devuelve una lista de subcategorías de productos únicas que contienen estos productos.
Una cosa interesante en los resultados de la consulta anterior es que los «cascos» están en la lista (la única subcategoría que no es de bicicleta). Para ver cuáles son los productos exactos donde se vendió, es simplemente cuestión de escribir otra consulta de la siguiente manera.
Observe cómo pude reutilizar la estructura de consulta original para devolver la lista de nombres de productos con solo un cambio menor en la consulta.
