Consulta plegable y conexión a SQL Server

En este tutorial, aprenderemos cómo conectarnos al servidor SQL. También discutiremos cómo funciona el plegado de consultas en Power Query y cómo podemos ejecutar comandos SQL en LuckyTemplates. Dentro de Power Query, podemos extraer y manipular datos de varias fuentes. Con el plegado de consultas, podemos descargar las transformaciones de datos al origen en lugar de realizarlas en LuckyTemplates.

El plegado de consultas es muy efectivo con bases de datos grandes para garantizar mejoras en el rendimiento de sus informes.

Tabla de contenido

• Conexión a una base de datos de SQL Server para el plegamiento de consultas
• Comprender el plegamiento de consultas en Power Query
• Identificar y corregir un plegado de consulta roto
• Beneficios de conectar SQL Server y ejecutar SQL Language
• Conclusión

Conexión a una base de datos de SQL Server para el plegamiento de consultas

Haremos este tutorial en un editor de Power Query. Primero, haga clic en SQL Server en Nueva fuente .

Si ya instaló una máquina de servidor SQL y trabaja de forma independiente, el nombre del servidor será localhost . Sin embargo, si está trabajando en una organización, le proporcionarán el nombre del servidor y el acceso a su base de datos. También necesitamos proporcionar el nombre de la base de datos. En este ejemplo, usaré AdventureWorksDW2012 . Puede aprender a descargar esta base de datos de ejemplo en este tutorial .

Para el Modo de conectividad de datos , todos los datos que seleccionemos se cargarán en el modelo si seleccionamos Importar . Pero si elegimos DirectQuery , no se cargará nada en el modelo de datos pero todo estará en la base de datos. Siempre que apliquemos un filtro, la consulta se enviará de vuelta al servidor SQL.

Pero esto no es eficiente porque llevará más tiempo actualizar. Por lo tanto, elegiremos Importar como Modo de conectividad de datos .

Luego, si hacemos clic en Opciones avanzadas , nos proporcionará una sección donde podemos escribir una declaración SQL. Aprenderemos cómo hacer esto más adelante.

En este ejemplo, solo queremos una tabla a la vez, por lo que no traeremos columnas o tablas de relación. En ese caso, tenemos que desmarcar la opción para eso.

Por último, haga clic en Aceptar .

Después de eso, podremos ver las mesas que están disponibles y desde allí, podemos tener algo de información. A partir de este ejemplo, necesitamos obtener los datos de las ventas por Internet. Por lo tanto, busquemos y seleccionemos FactInternetSales y luego hagamos clic en Aceptar .

Como resultado, ahora tendremos los datos en nuestro editor de Power Query.

Comprender el plegamiento de consultas en Power Query

En el panel Fuente , haga clic con el botón derecho en Navegación y luego seleccione Ver consulta nativa .

Con eso, podremos ver el comando que fue ejecutado por esta máquina. El motor Power Query creó este comando para ejecutarlo en SQL Server. Ahora hagamos clic en Aceptar .

Como ejemplo, creé aleatoriamente un filtro en esta tabla solo para mostrarle que una vez que los creamos, podremos verlos en el panel PASOS APLICADOS .

Cuando hacemos clic con el botón derecho en uno de los filtros, veremos que la opción Ver consulta nativa todavía está disponible.

Esta consulta SQL de nuestro filtro se ha ejecutado en el servidor SQL . Cuando no hemos aplicado el filtro, obtenemos 5 millones de filas. Ahora que hemos aplicado un filtro, solo obtenemos 4 millones de filas.

Esto significa que LuckyTemplates ahora extrae 4 millones de filas en lugar de 5 millones de filas de SQL Server. Con esto, se ha reducido el número de filas y también se reduce el número de cargas de nuestra red.

Tenga en cuenta que mientras veamos Native Query , significa que el plegamiento de consultas está funcionando. Por lo tanto, todo el procesamiento se realiza dentro del sistema de origen. Esta es la forma más eficiente de procesar datos, especialmente si tiene un gran volumen de datos.

Agregamos otro paso en el que eliminamos una columna de nuestra tabla. Si hacemos clic derecho sobre él, veremos la opción Ver consulta nativa , lo que significa que todavía está funcionando.

Identificar y corregir un plegado de consulta roto

Con algunas transformaciones, como cambiar el tipo de datos de una columna, el plegado de consulta se romperá. Por ejemplo, cambiaremos el tipo de datos de la columna TaxAmt a Whole Number .

Esto agregará un paso de tipo de cambio debajo de PASOS APLICADOS . Si hacemos clic derecho sobre él, veremos que View Native Query ahora está deshabilitado, lo que significa que Query Folding está roto.

Cuando se rompe una consulta plegable , cualquier otra transformación que hagamos se realizará en LuckyTemplates Power Query, pero ya no en el sistema de origen.

Por ejemplo, si obtenemos 3 millones de filas, todas vendrán en Power Query. Todavía podemos reducir esos registros mediante el filtrado. Sin embargo, esos 3 millones de filas ahora llegarán a través de la red, lo cual no es muy eficiente.

Para otro ejemplo, supongamos que queremos filtrar OrderDate para mostrar solo las fechas posteriores al 1 de enero de 2012.

Si visualizamos ese filtro en PASOS APLICADOS , la opción Ver Consulta Nativa no estará visible.

Nuevamente, eso se debe a que Query Folding se rompió debido a la transformación anterior que creamos. Lo que podemos hacer es mover todos los pasos de filtrado que haremos por encima del paso de transformación que rompió el Query Folding .

Para este ejemplo, simplemente haremos clic con el botón derecho en el paso de filtrado reciente que hemos creado y haremos clic en Mover antes , o simplemente lo arrastraremos a la parte superior de la transformación Cambiar tipo .

Si volvemos a hacer clic con el botón derecho en ese filtro, veremos que la opción Ver consulta nativa ahora está visible, lo que significa que el plegado de consulta está funcionando nuevamente.

Beneficios de conectar SQL Server y ejecutar SQL Language

Digamos, por ejemplo, que queremos mostrar datos con un formato de ventas totales por país como se muestra en la imagen.

En nuestra tabla FactInternetSales , tenemos la columna SalesAmount pero no tenemos información del país.

Todavía podemos obtener información del país porque tenemos la columna SalesTerritoryKey .

Lo que debemos hacer es traer la tabla DimSales aquí para poder fusionarla con FactInternetSales . Luego, debemos traer la columna de país y agruparla por columna de país, lo cual es muy complejo y puede llevar mucho tiempo. Entonces, en lugar de hacer todo eso en Power Query , que no es eficiente, deberíamos hacerlo en SQL .

Para hacerlo, simplemente haga clic en Nueva fuente > Servidor SQL .

Conectémonos nuevamente a nuestro servidor llamado localhost y AdventureWorksDW2012 como nuestra base de datos.

Esta vez, queremos hacer una opción avanzada porque queremos escribir un comando en el campo de declaración SQL . Para este ejemplo, ya hemos escrito un comando que ingresaremos en la instrucción SQL. Puede obtener información sobre los comandos SQL en nuestros otros tutoriales.

No queremos incluir columnas de relación, así que desmarcaremos esta opción aquí. Luego, haga clic en el botón Aceptar para ejecutar este comando.

Después de ejecutar el comando, veremos esta ventana de vista previa que muestra Ventas totales por región de territorio de ventas .

Por lo tanto, pudimos obtener un resultado similar de la tabla SalesByCountry usando un comando SQL simple en lugar de manipular diferentes tablas y columnas en nuestro Power Query.

Otro beneficio es que podemos transformar todos nuestros datos en SQL y solo traer los datos necesarios o requeridos a nuestro modelo de datos. Con esto, podemos construir un modelo de datos muy eficiente según nuestro plan sin ninguna dificultad o problema.

Descarga e instalación de SQL Server
SQL para usuarios de LuckyTemplates: nuevo curso de LuckyTemplates
Consulta de datos de múltiples fuentes de datos

Conclusión

En este tutorial, aprendimos qué es un Query Folding y descubrimos sus beneficios. También hemos discutido los pasos sobre cómo conectar Power Query a SQL Server .

Además, hablamos sobre los beneficios de conectarse a SQL Server y crear transformaciones en SQL Server en lugar de hacerlas en Power Query .

Con suerte, pudo ver cómo hacer todas las transformaciones en SQL es más eficiente y rápido en comparación con hacerlo en Power Query .

Mis mejores deseos,

Hafiz