Nuevos orígenes compatibles con parámetros dinámicos M

Parámetros de consulta dinámica M

La característica Parámetros de consulta dinámica M se añadió en la actualización de octubre de 2020. Con esta funcionalidad los usuarios pueden actualizar dinámicamente los parámetros desde la vista de informe. Lo que permite optimizar el rendimiento de las consultas sin sacrificar la interactividad del informe. Para modificar los valores de los parámetros utilizamos los filtros y las segmentaciones de datos.

Esta característica solo se puede utilizar en orígenes de datos DirectQuery y está en versión preliminar.

En la entrada del blog Parámetros de consulta dinámica M analizamos un ejemplo de su uso con el conector Azure Data Explorer (Kusto). También comentamos algunas limitaciones de su uso. Entre estás limitaciones que aún continúan están los tipos de parámetros no soportados, las operaciones no soportadas y filtros no soportados.

En la actualización de Power BI Desktop de febrero de 2022 se han añadido otros orígenes de datos DirectQuery que soportan esta capacidad. Entre estos conectores se encuentran SQL Server, Azure SQL, Azure Synapse Analytics, Oracle, Teradata, SAP Hana Relacional y los flujos de datos.

En esta entrada queremos mostrar el uso de esta característica con el conector SQL Server. Veremos dos ejemplos, cada uno utiliza una base de datos local diferente.

El primer ejemplo mostrará un parámetro con selección de un único valor y en el segundo un parámetro con selección de múltiples valores.

Ejemplo 1

La base de datos de este ejemplo es Navision, Demo Database NAV (8-0). Esta base de datos contiene datos de dos empresas y queremos escoger dinámicamente, desde la vista de informe, de cuál empresa queremos ver sus datos usando parámetros dinámicos.

Para hacer uso de los parámetros dinámicos debemos realizar una serie de pasos que ya vimos en la entrada anterior y que repetiremos aquí para cada ejemplo. Por lo que las únicas diferencias que encontraremos son el conector y las condiciones de filtrado. Veamos el primer ejemplo.

Pasos en el Editor de Power Query

Lo primero que haremos será crear los parámetros: uno para el servidor, otro para la base de datos y un tercero para la empresa. Puedes ver la configuración del parámetro Empresa en la siguiente imagen.

A continuación seleccionaremos nuevo origen de datos SQL Server y configuraremos el cuadro de diálogo como se muestra en la imagen de debajo.

Después elegiremos las tablas Item y Item Ledger Entry de una de las dos empresas que contiene la base de datos. Estas tablas contienen los datos de los productos y sus movimientos. Como resultado se obtienen dos consultas.

Seleccionamos la consulta Item y nos vamos al paso Navegación en el panel de Configuración de Consultas. En la barra de fórmulas, en la opción Item podemos ver el nombre de la empresa seguido del nombre de la tabla.

Además reemplazaremos el nombre de la empresa por el parámetro EmpresaActual que hemos creado previamente y lo concatenamos con el nombre de la tabla como se muestra en la imagen.

Lo siguiente será crear una tabla con los distintos valores que puede tomar el parámetro. Esta tabla tendrá dos filas, una por cada empresa. El nombre de la columna que se utilizará para filtrar no debe ser igual al de ninguna columna de las tablas del modelo. Si esto ocurriera, esas tablas resultarían filtradas por los valores seleccionados en el filtro o segmentación de datos.

También es importante comprobar que el tipo de datos del parámetro y de la columna que se utilizará para filtrar sea el mismo.

Finalmente cargamos las tablas en el modelo.

Pasos en la vista de Modelo

Cuando se hayan cargado las tablas nos vamos a la vista de Modelo y seleccionamos la tabla Empresas que es la que utilizaremos para filtrar.

Escogemos la columna Empresa que es la que utilizaremos para filtrar los parámetros y en sus propiedades avanzadas la enlazaremos con el parámetro EmpresaActual.

Pasos en la vista de Informe

Finalmente iremos a la vista de informes y mostraremos los datos en alguna visualización.

En este caso hemos utilizado la visualización Tabla y hemos añadido los campos No_ y Description de la tabla Item y el campo Quantity de la tabla Item Ledge Entry.

Una vez que está lista la visualización, añadimos una segmentación con el campo Empresa de la tabla Empresas. Este es el campo que hemos vinculado al parámetro y que usaremos para filtrar los valores. Automáticamente nos mostrará la segmentación con el formato de selección de valor único, como se muestra en la siguiente imagen.

Por defecto se selecciona el primer valor de la lista y si no coincide con el valor actual del parámetro se ejecuta la consulta con el nuevo valor.

Ahora ya podemos elegir la empresa cuyos datos queremos analizar sin necesidad de crear otro modelo o modificar el código Power Query.

Ejemplo 2

Para este ejemplo hemos escogido la tabla DimGeography de la base de datos AdventureWorksDW2017. La idea es filtrar la tabla por la columna EnglishCountryRegionName que contiene los nombres de los países. Como resultado el filtro debe permitir seleccionar, todos, ninguno o varios países a la vez.

Pasos

1.- Primero crearemos un parámetro para la base de datos y otro para el país (PaisParametro). El parámetro del servidor es el mismo del ejemplo anterior.





2.- A continuación crearemos una consulta utilizando el conector SQL Server y a diferencia del ejemplo anterior en lugar de seleccionar tablas escribiremos una consulta nativa.






3.- A continuación modificaremos la consulta en el editor avanzado añadiendo dos pasos. En el primero comprobaremos si el parámetro contiene una lista de valores o un único valor. En el caso que contenga una lista de valores se concatenarán en una única expresión. Para ello utilizaremos como separador la coma y encerraremos cada valor entre comillas simples. En el caso de que solo sea un valor se encerrará entre comillas simples. En el siguiente paso, definimos la consulta que se ejecutará en SQL Server. Para crear esta consulta usaremos la consulta nativa original a la que le añadiremos la cláusula WHERE. Combinaremos la cláusula WHERE con el operador IN de SQL para indicar que valores del campo EnglishCountryRegionName se seleccionaran. En este caso el conjunto de valores es el resultado del paso anterior. Por último, sustituimos en el paso Origen la consulta nativa por el resultado del paso anterior.





4.- A partir de la columna EnglishCountryRegionName crearemos la tabla Países como se muestra en la siguiente imagen.







5.- Se eliminan los duplicados de la tabla Países, se cambia el nombre de la columna por País. Recordad que el nombre no debe ser el mismo de la columna de la consulta. A continuación se cargan los datos en el modelo.
6.- En la vista de Modelo vincularemos el campo País de la tabla Países con el parámetro PaisParametro y habilitamos la opción Selección múltiple.





7.- Cambiamos a la vista informe donde crearemos una tabla con los campos City, PostalCode, StateProvinceName y EnglishCountryRegionName. A continuación añadimos una Segmentación de datos con el campo País de la tabla Países.

Podemos observar que en este caso el control permite seleccionar varios países. Una vez hecha la selección de países se lanza la consulta. Como resultado se actualizan los datos de la tabla mostrando solo la información de los países seleccionados.

Opción Seleccionar todo

Si además, queremos habilitar la opción Seleccionar Todo de la segmentación debemos realizar una serie de pasos. El primero es volver a la vista de Modelo y seleccionar la columna País y en las opciones avanzadas habilitar la opción Seleccionar Todo. Después de habilitar esta opción debemos definir el valor que mostrará esa opción, por defecto es __SelectAll__. Si cambiamos el valor debemos garantizar que el que escribamos no esté contenido en la lista de valores de la columna.

Seguidamente debemos volver al Editor de Power Query a modificar la consulta para que tenga en cuenta este valor. También hemos tenido en cuenta la posibilidad de que el parámetro esté en null.

Después de aplicar los cambios volvemos a la vista de informe. Seleccionamos la segmentación de datos y en Formato habilitamos la opción Seleccionar todo. Ahora podremos usarla para filtrar los valores.

Consideraciones y limitaciones

Si utilizas parámetros dinámicos debes saber que:

• Los nombres de parámetro no pueden ser palabras reservadas de DAX ni contener espacios
• Un parámetro único no se puede enlazar a varios campos ni viceversa
• No admiten seguridad a nivel de filas
• Tampoco admiten agregaciones

Puedes revisar la documentación de Microsoft sobre Parámetros de consulta M dinámicos en Power BI Desktop (versión preliminar) para conocer que otras consideraciones y limitaciones debes tener en cuenta al usar los parámetros dinámicos.