Introducción a DirectQuery – R Marketing

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Nivel: intermedio a avanzado

R Marketing aquí. Recientemente conocí a Parker Stevens en línea a través de LinkedIn; él también es un fanático de Power BI, al igual que yo. Parker me ha compartido algunas técnicas que me parecieron muy interesantes. Estos temas no se han tratado antes en mi blog y, por lo tanto, pensé que era una gran oportunidad para que Parker compartiera algunos de sus conocimientos con ustedes en algunos artículos de blog en las próximas semanas. El blog de hoy es una introducción al modo DirectQuery en Power BI. Este tema profundiza mucho más en el tema de SQL Server de lo que normalmente cubro en mi sitio (ya que no es mi conjunto de habilidades principales). Pero todavía hay valor aquí, incluso si usted es un analista de negocios o un profesional de Excel / Visualización. Es importante comprender “qué” se puede hacer incluso si a veces puede necesitar la ayuda de otros para hacerlo: el conocimiento es poder.

Acerca de Parker Stevens

Parker Stevens es un desarrollador de inteligencia empresarial y el creador de la Canal de Youtube de BI Elite. A través de este medio, Parker comparte sus conocimientos sobre todo lo relacionado con Power BI y DAX. Tiene pasión por la innovación dentro del espacio Power BI y ha desarrollado algunas técnicas interesantes para superar los límites del software.

A Parker.

Introducción a DirectQuery

A partir de hoy, Power BI le permite conectarse a una fuente de datos relacionales de diferentes formas. Esto incluye Import, DirectQuery y a través de Live Connection cuando se utiliza SSAS. En esta publicación, discutiré brevemente las ventajas y desventajas de usar DirectQuery y luego profundizaré en su ejecución para brindarle una comprensión sólida de lo que está sucediendo detrás de escena.

DirectQuery es un método para conectarse a una fuente de datos relacionales sin la necesidad de copiar los datos en Power BI. Este modo de funcionamiento ha estado disponible en Power BI durante bastante tiempo. Durante mucho tiempo ha sido aclamado como una bendición y una maldición entre la creación de informes, ya que se sabe que su solución al problema de la necesidad de datos en vivo viene con sus trampas de restricciones y golpes de rendimiento. Afortunadamente, el equipo de desarrollo de Power BI parece haber estado invirtiendo una gran cantidad de recursos para hacer de DirectQuery un tema de conversación definido. Sin más preámbulos, estas son las razones por las que debería o no debería considerar el uso de DirectQuery en su informe.

El bueno

El atractivo principal de DirectQuery es que permite que su informe contenga siempre los datos más actualizados de su fuente de datos relacionales; es una conexión en vivo a la fuente de datos relacionales. A diferencia de su contraparte, «Importar», que requiere que cargue una instantánea de los datos necesarios en el modelo durante la actualización, DirectQuery consulta la fuente de datos en busca de datos cada vez que hay un cambio en los filtros. Esto asegura que su informe esté constantemente «activo». Esta es una gran ventaja si trabaja con una base de datos que se actualiza constantemente, similar a un sistema OLTP (procesamiento de transacciones en línea) que encontrará en una empresa que se ocupa de cualquier forma de entrada de pedidos.

Otra ventaja de DirectQuery son sus límites de tamaño de datos sin restricciones. Dado que Import carga todos los datos necesarios en la memoria, Power BI ha impuesto un límite de 1 GB en el tamaño de cualquier modelo de datos individual. Con DirectQuery, el sistema que almacena datos en la memoria (conocido como motor VertiPaq) no está habilitado, lo que alivia las limitaciones de tamaño. En cambio, puede consultar petabytes de datos almacenados en su base de datos relacional mientras se preocupa solo por la velocidad a la que se devuelven los datos. Por ejemplo, aquí se muestra el tamaño de dos archivos PBIX después de cargar los mismos datos tanto por Importación como por DirectQuery.

img_5aff715254527-4789645

Nota: En realidad, estoy trabajando con la base de datos AdventureWorksDW2012, que es minúscula en comparación con las bases de datos empresariales.

El malo

El área donde DirectQuery no cumple con su oposición son sus limitaciones a las funciones DAX y MDX. No se permite ninguna transformación de Power Query que altere la fuente de datos original (que son prácticamente todas). Las funciones de DAX, por otro lado, se dividen en una de dos categorías: Optimizadas para DirectQuery y No optimizadas para DirectQuery. Aquí hay un enlace a la página que especifica qué funciones están optimizadas y cuáles no. Puede encontrar un enlace a la página que especifica qué funciones están optimizadas para DirectQuery aquí https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016?view=sql-analysis-services-2017. Esto se desglosa aún más para especificar si las funciones están optimizadas para su uso en todos los cálculos o solo en las medidas. La regla general aquí es que puede usar todas las funciones de DAX en medidas con rendimiento variable, pero solo las funciones dictadas como «Admitidas en todas las fórmulas de DAX» están permitidas para su uso en columnas calculadas.

En mi opinión, la segunda limitación más importante es que está confinado a una sola fuente de datos. Es muy importante saberlo, aunque admito que no suelo tener informes que contengan más de una fuente de datos. Por un RDBMS, esto normalmente significa solo una base de datos. La solución alternativa para esto es conectar DirectQuery a «Vistas» (por ejemplo, Vistas de SQL Server) que extraen de diferentes bases de datos. Con este enfoque, incluso puede extraer datos de diferentes servidores si ha configurado servidores vinculados, aunque esto podría afectar el rendimiento.

Finalmente, una desventaja grande y probablemente obvia de DirectQuery es que produce una cantidad considerable de consultas que se ejecutan en la base de datos. En este método, cada visualización de la página envía su propia consulta a la base de datos. A veces, una visualización ejecutará varias consultas. Si comparte este informe con otras personas, eso podría significar una gran cantidad de tráfico en sus servidores. Para aliviar este problema, Power BI ha implementado una función opcional llamada Reducción de consultas que agrega un botón «Aplicar» a todas sus segmentaciones. Luego debe hacer clic en el botón Aplicar cuando decida que desea consultar la base de datos usando sus selecciones. Esto se puede encontrar en las Opciones en «Reducción de consultas».

Una mirada bajo el capó

Para comprender lo que realmente está sucediendo en un informe configurado con DirectQuery, simplemente debemos preguntar a nuestra fuente de datos. En el siguiente ejemplo, estoy configurando dos consultas a través de DirectQuery. La primera consulta toma un subconjunto de columnas de la tabla FactInternetSales en la base de datos AdventureWorksDW2012 proporcionada por Microsoft.

img_5aff7228aff6f-7286492

Y la siguiente consulta es una lógica complicada para tomar la última consulta que llegó a la base de datos (y también ejecutar lo suficientemente lento como para asegurarse de que sea la última consulta ejecutada). Si bien la consulta anterior es bastante fácil de entender (incluso si no conoce TSQL), no es necesario que comprenda el código a continuación en absoluto. Solo entienda que he escrito esto para demostrar cómo Power BI puede acceder directamente a los datos de una base de datos relacional como SQL Server.

img_5aff7257cf3e5-5504037

Con la configuración de estas dos consultas, podemos arrastrar un par de elementos visuales al lienzo. Comencemos con un gráfico de líneas que contiene OrderDate como Axis y SalesAmount como su valor. También agreguemos una slicera que contenga OrderDate. Finalmente, agregaremos una tarjeta que contiene el campo «Consulta» de la segunda consulta. Debería tener un informe que se parezca a esto:

img_5aff727615556-5484184

Dado que podemos ver la última consulta que se ejecutó en la base de datos, podemos comenzar a saber qué está enviando Power BI al backend. Y si movemos el control deslizante para mostrar un rango de OrderDate de 7/10/2011 – 20/11/2012, vemos que nuestra consulta que se envió a nuestra base de datos contiene una cláusula WHERE que solicita solo datos dentro de este período de tiempo.

img_5aff729852c96-5321268

Otra cosa a tener en cuenta es que nuestra consulta está realizando una SUMA mientras agrupa por OrderDate. Esto tiene sentido ya que estamos visualizando la suma de SalesAmount por día. Por último, tome nota del «TOP (1000001)» que está al comienzo de SELECT. DirectQuery pone un límite al número de filas que puede devolver cada consulta. Esto no afectará esta visualización porque, dado que estamos tomando la suma, solo esperamos un máximo de un registro por día. Pero tenga en cuenta que no podrá mostrar una visualización si su consulta devuelve más de un millón de registros.

Pero, ¿qué pasa cuando tenemos varios filtros? Agreguemos una slicera para UnitPrice y averigüémoslo.

img_5aff72c827993-2846412

Power BI maneja este filtro adicional agregando un par de parámetros que se adjuntan a la cláusula WHERE. En este caso, @ Param1 es la parte superior del rango de UnitPrice y @ Param2 es la parte inferior. Terminemos nuestra revisión investigando cómo DirectQuery maneja una medida DAX más complicada para calcular un promedio móvil de 1 mes usando la fórmula a continuación:

1MonthRollingAverage =
    CALCULATE(
          SUM(Query1[SalesAmount]),
          DATESINPERIOD(Query1[OrderDate], FIRSTDATE(Query1[OrderDate]), -1, MONTH)
    )
      / CALCULATE(
             DISTINCTCOUNT(Query1[OrderDate]),
             DATESINPERIOD(Query1[OrderDate], FIRSTDATE(Query1[OrderDate]), -1, MONTH)
        )

Esta no es la medida mejor escrita para lograr esto, pero quería mostrar lo que sucede cuando introduce un cálculo que especifica un contexto de fila. Como puede ver a continuación, termina con una cantidad ridícula de consultas …

img_5aff730a818b2-6103095

Power BI básicamente tiene dos opciones sobre cómo puede generar el resultado del cálculo. Puede enviar todo el trabajo a la base de datos como lo está haciendo en este ejemplo, o puede consultar la base de datos solo para los datos sin procesar y luego realizar los cálculos usando DAX. Se prefiere permitir que el backend (base de datos de origen) haga todo el trabajo pesado, sin embargo, en este caso, eso tiene el costo de una gran cantidad de tráfico a su fuente de datos. Curiosamente, gracias a la actualización de Power BI de febrero de 2018, DirectQuery ahora es lo suficientemente inteligente como para combinar agregaciones aditivas como SUM con agregaciones no aditivas como DISTINCTCOUNT, todo en una consulta SQL. Entonces, antes de esta actualización, ¡podríamos haber esperado que se devolvieran 390 consultas!

Conclusión

Espero que esta publicación haya ayudado a desmitificar cómo funciona DirectQuery entre bastidores. Es un tipo de conexión extremadamente útil que puede garantizar que sus datos estén actualizados con cada selección realizada en su informe de Power BI. Solo tenga en cuenta que esta capacidad requiere los sacrificios de las limitaciones de DAX y Power Query y el tráfico de la fuente de datos. Sin mencionar que la velocidad general de procesamiento de informes también puede verse afectada en determinadas circunstancias. Con todo esto a la vista, es de esperar que tenga un conocimiento lo suficientemente sólido de DirectQuery para decidir si es correcto o incorrecto para su informe.

Tengo un video complementario que puedes ver. aquí.

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.