Plegando consultas con consulta nativa

Plegando consultas con consulta nativa

En diversas ocasiones he hablado del plegado de consulta y su importancia en Power Query, dedicando un buen número de páginas a ello en mi libro “Power BI Dataflows”, sobre todo por la facilidad en el control de este que obtenemos  a través del elemento visual en los flujos.

Aprendiendo qué es el plegado de consulta con la nueva funcionalidad de visualización del plegado en dataflows. – (powerbisp.com)

Incluso he realizado diversos webinars sobre el tema:

(70) Controlando el plegado en los flujos de datos – YouTube

(70) Desmitificando el plegado de consulta – Francisco Mullor – YouTube

Por otro lado he comentado muchas veces que la utilización del cuadro de diálogo de una consulta nativa, en el conector de sql, rompe el plegado directamente a partir de su uso y escribí un post sobre cómo mantener el plegado de consultas nativas, creando la consulta con la función Value.NativeQuery() que admite el parámetro [EnableFolding=true()] desde febrero de 2021.

Mantener el plegado de consulta con consultas nativas – (powerbisp.com)

Esperaba que con el tiempo se introdujera en la interfaz del conector de SQL una pestañita que permitiera activar el EnableFolding directamente, pero es algo que no se ha implementado (y ni siquiera se si está en el pensamiento de los desarrolladores del producto y tengo un problema con plantear mis ideas en ideas.powerbi.com ya que no soy muy amigo de escribir en inglés 😊.

Es por ello que os voy a contar mi secreto, el workarround que utilizo para no tener que recordar cada vez la función Value.NativeQuery con el EnableFolding en true, aunque a base de utilizarla os aseguro que se aprende.

Vamos a ello…

Por qué usar consultas nativas

            Pues básicamente lo hacemos por comodidad y ahorro de transformaciones, a poco que tengamos unos mínimos conocimientos de SQL pues ya realizamos una primera criba en la consulta nativa, traernos solo las columnas necesarias, con los nombres que queramos de columna, con filtros a través de where e incluso si sabemos, hacemos joins en la propia consulta nativa con otras tablas.

¿Esto es eficiente?

Pues si no tenemos opción de hacer la consulta como una vista en la base de datos, pues si es eficiente, pero absolutamente lo mismo de eficiente, ni más, ni menos, que traernos las tablas completas e ir realizando las transformaciones en Power Query mientras mantengamos el plegado, ya que Power Query lo que va a mandar en ese caso sigue siendo una consulta nativa, no va a traer todos los datos y a partir de ahí va a realizar las transformaciones como alguno puede suponer.

Estamos por tanto en el punto de que realizamos nuestra consulta nativa y, por cualquier cosa, necesitamos realizar otra transformación. Si podemos hacerla en la consulta nativa, perfecto, pero si no podemos o si necesitamos combinar esta consulta creada con otra consulta de la base de datos lo primero que observamos es que cualquier transformación posterior a la consulta nativa mediante el cuadro de diálogo, rompe el plegado.

Veamos un ejemplo:

*** La primera vez que vi el check de “Habilitar plegado de bases de datos cruzadas” me dio un vuelco el corazón pensando que era el EnableFolding=true pero no, no lo es.

Pues lo que comentaba, al hacer la consulta nativa en el cuadro de diálogo un simple paso de columnas quitadas que siempre mantiene el plegado, lo rompe.

Y romper el plegado ahí supone también que se rompa si combinamos esa tabla con la Dimensión subcategoría, por ejemplo:

Aunque sabemos que la combinación de consultas no rompe el plegado (si ambas consultas pliegan)

¿Como solucionábamos esto?

Value.Nativequery en acción

Esta es la Query original:

let
  Origen = Sql.Database("powerbisp.database.windows.net", "Contoso", [Query = "select * from dbo.DimProductCategory"]),
  #"Columnas quitadas" = Table.RemoveColumns(Origen, {"ETLLoadID", "LoadDate", "UpdateDate"})
in
  #"Columnas quitadas"

Manualmente(no se puede con la interfaz) la transformamos en esto:

let
  Origen = Sql.Database("powerbisp.database.windows.net", "Contoso"),
  Consulta= Value.NativeQuery(Origen,  "select * from dbo.DimProductCategory",null,[EnableFolding=true]),
  #"Columnas quitadas" = Table.RemoveColumns(Consulta, {"ETLLoadID", "LoadDate", "UpdateDate"})
in
  #"Columnas quitadas"

En el Origen simplemente conectamos a la base de datos y es en el Value.NativeQuery donde introducimos la consulta nativa con el EnableFolding en true.

Como vemos esto ya mantiene el plegado y nuestra tabla de subcategorías con el join también:

Hasta aquí, nada nuevo con respecto a lo que hubiera contado anteriormente, pero hoy os voy a enseñar cómo hago para no tener que modificar absolutamente todas las consultas nativas manualmente en mis ETLs

En principio debería ser tan sencillo como convertir el valor de mi Query en un parámetro que invoque con una función:

(Consulta_Nativa as text)=> //parametrizo la consulta y la integro en el código M

let

  Origen = Sql.Database("powerbisp.database.windows.net", "Contoso"),

  Consulta= Value.NativeQuery(Origen,  Consulta_Nativa,null,[EnableFolding=true]),

  #"Columnas quitadas" = Table.RemoveColumns(Consulta, {"ETLLoadID", "LoadDate", "UpdateDate"})

in

  #"Columnas quitadas"

Esto me devuelve una función que, simplemente invocándola con el contenido de la consulta nativa, me funcionaría perfectamente:

Invoco y ya tengo mi tabla

Sencillo, ¿verdad? Pero vamos a darle una vuelta de tuerca más

Nuestra función es de nuestra base de datos, si la compartimos, vosotros tendréis que cambiar esa base de datos, ¿podríamos hacerla un poco más universal y parametrizarla? Pues de primeras os digo que no y os explico por qué:

Podríamos establecer parámetros en el servidor y en la base de datos como nuestro y nuestra función debería devolvernos los datos perfectamente, y lo hace:

Ven, ¡!todo perfecto!! Pero le damos a guardar el flujo y sorpresa!!!

Si esto lo llevamos al Desktop igual, funciona de primeras:

Podríamos incluso cargar los datos y publicarlos en el servicio pero no nos dejaría actualizarlos por el mismo motivo, es un origen de datos dinámico.

Lo podemos ver aquí:

Y el horror es cuando publicamos y queremos actualizar:

https://learn.microsoft.com/es-es/power-bi/connect-data/refresh-data#refresh-and-dynamic-data-sources

Aquí la explicación en la documentación.

¿Como solucionamos esto? Pues he de confesar que me costó pensar como resolverlo, pero el hecho de que me funcionara poniendo el servidor y la base de datos me dio una idea:

En la función decidí dejar solamente una referencia a una tabla que se llamase Origen1 y el parámetro de la consulta nativa.

La función obviamente da un error porque no encuentra ese Origen1

Pero basta simplemente con crear esa tabla Origen1 con únicamente la conexión cada uno a su base de datos, tabla que no debe cargarse el en modelo, y ya nuestra función va a devolvernos los datos sin problema.

Rizando el rizo…integración como función personalizada.

¿Qué os parecería tener una función en vuestro Power BI que permitiera hacer esto sin tener que recordar constantemente la sintaxis del Value.NativeQuery?

Esto es posible con la creación de funciones personalizadas.

Para recordar cómo se crea una función personalizada os remito a una publicación anterior en la que os compartía el extracto de mi libro “Power BI Dataflows” donde hablaba de ello:

Creación y utilización de funciones personalizadas en Flujos de Datos – (powerbisp.com)

El texto de la función es el siguiente:

shared ConsultaNativa.Folding =

// Desarrollado por: Francisco Mullor: www.powerbisp.com
let func =  

    ( MyTable as table, MyQuery as text ) =>
    
   let
        Source = 
        MyTable,

        Query = 
        Value.NativeQuery(Source, MyQuery, null, [EnableFolding=true])

    in
    
    Query ,
    documentation = 
    [
        Documentation.Name =  
        " ConsultaNativa.Folding ",

        Documentation.Description = 
        " Llamada de una consulta nativa manteniendo el plegado ",

        Documentation.LongDescription = 
        " Llamada de una consulta nativa manteniendo el plegado ",

        Documentation.Category = " Table ",

        Documentation.Source = 
        " www.powerbisp.com ",

        Documentation.Version = " 1.0 ",

        Documentation.Author = 
        " Francisco Mullor ",

        Documentation.Examples = 
        {
            [
                Description =  "  ",
                Code = "Creado por: Francisco Mullor ",
                Result = "  "
            ]
        }
    ]
  
 in  
    Value.ReplaceType(
        func, 
        Value.ReplaceMetadata(
            Value.Type( func ), 
            documentation)
    )
    ;

Y el resultado es que podemos crear una consulta nativa manteniendo el plegado simplemente así:

1º Creamos la conexión a la base de datos:

2º Podemos simplemente agregar un paso desde pasos aplicados y usar en él nuestra función:

Y los pasos siguientes que hagamos, obviamente mantendrán el plegado:

Que era nuestro objetivo inicial.

Así tenemos una consulta nativa totalmente plegable sin necesidad de recordar todo el código:

Francisco Mullor Cabrera

Deja una respuesta