Introducción

¡Hola, chicos!

En este post me gustaría compartir con ustedes una situación que me pasó mientras consultaba con un cliente, donde necesitaba tener un informe de Power BI conectado a una base de datos de SQL Server, pero la fuente de datos era un Procedimiento almacenado que realizó varias transformaciones de datos internamente y devolvió un conjunto de datos.

El problema es que este cliente quería que los datos se actualizaran en tiempo real, es decir, si abriera el informe, aplicara un filtro o interactuara con el gráfico, los datos tendrían que actualizarse ejecutando este Procedimiento Almacenado. Y resulta que Power BI NO Esto es compatible 🙂

Si desea obtener más información sobre Power BI DirectQuery, ver este contenido aquí.

Este es el código de procedimiento almacenado que me gustaría ejecutar como DirectQuery en Power BI:

CREATE OR ALTER PROCEDURE dbo.stpPBI_DirectQuery_Procedure
AS
BEGIN
    

    IF (OBJECT_ID('tempdb..#Tabelas') IS NOT NULL) DROP TABLE #Tabelas
    SELECT *
    INTO #Tabelas
    FROM sys.tables
    WHERE is_ms_shipped = 0


    IF (OBJECT_ID('tempdb..#Tabelas_Internas') IS NOT NULL) DROP TABLE #Tabelas_Internas
    CREATE TABLE #Tabelas_Internas ( 
        [object_id] INT,
        [Descricao] VARCHAR(100) 
    )

    INSERT INTO #Tabelas_Internas 
    VALUES(965578478, 'Tabela de Confirmados'), (1013578649, 'Tabela de Casos Recuperados')


    SELECT 
        A.[object_id], 
        A.[name], 
        A.create_date, 
        A.lock_escalation_desc, 
        A.temporal_type_desc, 
        B.Descricao
    FROM
        #Tabelas A
        LEFT JOIN #Tabelas_Internas B ON B.[object_id] = A.[object_id]
    WHERE  
        is_replicated = 0
    ORDER BY
        [name]


END

Resultado de ejecución esperado:

Simulando el problema

Haga clic aquí para ver este contenido
El primer paso al intentar ejecutar un procedimiento con DirectQuery es seleccionar este tipo de almacenamiento, ingresar el nombre del servidor y hacer clic en el botón Aceptar para cargar la lista de objetos:

En la lista de objetos podemos ver que solo lista tablas, vistas y funciones:

Volveré a la pantalla de conexión e intentaré ingresar el comando de ejecución manualmente en la pantalla de declaración SQL:

Pero al intentar hacer esto, aparece un mensaje de error:

Intentemos nuevamente, sin agregar la declaración EXEC:

¡Ups! ¡Parece que está cargado!

Parecía que funcionaría, pero al intentar cargar los datos...

PowerQuery incluso muestra los datos de la base de datos, pero no me deja cargarlos en el modelo, mostrando el mensaje de la impresión anterior.


Solución n.º 1: OpenRowSet

Haga clic aquí para ver este contenido
La primera “solución” que les presentaré es utilizar OpenRowSet para ejecutar el procedimiento en el servidor local y devolver los datos. Dado que esto termina siendo una instrucción SELECT, Power BI debe aceptar la ejecución: 
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'EXEC eventos.dbo.stpPBI_DirectQuery_Procedure')

El primer punto a considerar es este mensaje de error a continuación:

Mensaje 15281, Nivel 16, Estado 1, Línea 43
SQL Server bloqueó el acceso a la DECLARACIÓN 'OpenRowset/OpenDatasource' del componente 'Consultas distribuidas ad hoc' porque este componente está desactivado como parte de la configuración de seguridad para este servidor. Un administrador del sistema puede habilitar el uso de "Consultas distribuidas ad hoc" mediante sp_configure. Para obtener más información sobre cómo habilitar "Consultas distribuidas ad hoc", busque "Consultas distribuidas ad hoc" en los Libros en pantalla de SQL Server.

Este mensaje no representa más que que la función OpenRowset/OpenDatasource está deshabilitada en la instancia (de forma predeterminada, esta configuración está deshabilitada por razones de seguridad). Para usar Openrowset, deberá habilitar esta función y probablemente a su administrador de bases de datos no le guste:

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

Una vez habilitado, ahora simplemente ejecute nuestro comando SELECT. Y nos encontramos con el segundo mensaje de error:

Mensaje 11526, Nivel 16, Estado 1, Procedimiento sys.sp_describe_first_result_set, Línea 1 [Línea de inicio de lote 53] No se pudieron determinar los metadatos porque la declaración 'INSERT INTO #Tables_Internas
VALUES(965578478, 'Tabla confirmada'), (1013578649, 'Tabla' en el procedimiento 'stpPBI_DirectQuery_Procedure' utiliza una tabla temporal.

Ahora SQL Server se queja de las tablas temporales utilizadas en el procedimiento almacenado. Hagamos algunos cambios en el procedimiento y reemplacemos las tablas temporales (#table) con variables tipo tabla (@table):

ALTER PROCEDURE dbo.stpPBI_DirectQuery_Procedure
AS
BEGIN
    

    DECLARE @Tabelas TABLE ( 
        [object_id] INT, 
        [name] VARCHAR(100), 
        create_date DATETIME, 
        lock_escalation_desc VARCHAR(100), 
        temporal_type_desc VARCHAR(100),
        is_replicated BIT
    )

    INSERT INTO @Tabelas
    SELECT 
        A.[object_id], 
        A.[name], 
        A.create_date, 
        A.lock_escalation_desc, 
        A.temporal_type_desc,
        A.is_replicated
    FROM
        sys.tables A
    WHERE
        is_ms_shipped = 0


    DECLARE @Tabelas_Internas TABLE ( 
        [object_id] INT,
        [Descricao] VARCHAR(100) 
    )

    INSERT INTO @Tabelas_Internas 
    VALUES(965578478, 'Tabela de Confirmados'), (1013578649, 'Tabela de Casos Recuperados')


    SELECT 
        A.[object_id], 
        A.[name], 
        A.create_date, 
        A.lock_escalation_desc, 
        A.temporal_type_desc, 
        B.Descricao
    FROM
        @Tabelas A
        LEFT JOIN @Tabelas_Internas B ON B.[object_id] = A.[object_id]
    WHERE  
        A.is_replicated = 0
    ORDER BY
        [name]


END

Y intentémoslo de nuevo. Otro mensaje de error:

Mensaje 7357, Nivel 16, Estado 1, Línea 54
No se puede procesar el objeto “EXEC eventos.dbo.stpPBI_DirectQuery_Procedure”. El proveedor OLE DB "SQLNCLI11" para el servidor vinculado "(nulo)" indica que el objeto no tiene columnas o que el usuario actual no tiene permisos sobre ese objeto.

Para solucionar este problema incluiremos la instrucción “SET NOCOUNT ON” al inicio del Procedimiento Almacenado, para que el comando OPENROWSET pueda identificar correctamente los datos devueltos y no sufra interferencia con la devolución del número de filas devueltas:

ALTER PROCEDURE dbo.stpPBI_DirectQuery_Procedure
AS
BEGIN
    

    SET NOCOUNT ON


    DECLARE @Tabelas TABLE ( 
        [object_id] INT, 
        [name] VARCHAR(100), 
        create_date DATETIME, 
        lock_escalation_desc VARCHAR(100), 
        temporal_type_desc VARCHAR(100),
        is_replicated BIT
    )

    INSERT INTO @Tabelas
    SELECT 
        A.[object_id], 
        A.[name], 
        A.create_date, 
        A.lock_escalation_desc, 
        A.temporal_type_desc,
        A.is_replicated
    FROM
        sys.tables A
    WHERE
        is_ms_shipped = 0


    DECLARE @Tabelas_Internas TABLE ( 
        [object_id] INT,
        [Descricao] VARCHAR(100) 
    )

    INSERT INTO @Tabelas_Internas 
    VALUES(965578478, 'Tabela de Confirmados'), (1013578649, 'Tabela de Casos Recuperados')


    SELECT 
        A.[object_id], 
        A.[name], 
        A.create_date, 
        A.lock_escalation_desc, 
        A.temporal_type_desc, 
        B.Descricao
    FROM
        @Tabelas A
        LEFT JOIN @Tabelas_Internas B ON B.[object_id] = A.[object_id]
    WHERE  
        A.is_replicated = 0
    ORDER BY
        [name]


END

Con este cambio, nuestro comando SELECT se ejecutó correctamente:

Volvamos a Power BI, repetimos todo el proceso y ahora ya podremos cargar los datos normalmente en Power BI:

¡Y está ahí! ¡Estamos consumiendo un procedimiento almacenado usando DirectQuery en Power BI!


Solución n.º 2: uso de funciones con valores de tabla

Haga clic aquí para ver este contenido
La segunda forma de consultar objetos complejos usando DirectQuery en Power BI es reemplazando el Procedimiento Almacenado por una Función con Valores de Tabla, lo que termina siendo una forma mucho más elegante y correcta de realizar esta consulta que la forma anterior.

A continuación se muestra el código de función, que hace exactamente lo mismo que el procedimiento almacenado anterior:

CREATE FUNCTION dbo.fncPBI_DirectQuery_Procedure()
RETURNS @Retorno TABLE (
    [object_id] INT, 
    [name] VARCHAR(100), 
    create_date DATETIME, 
    lock_escalation_desc VARCHAR(100), 
    temporal_type_desc VARCHAR(100),
    descricao varchar(200)
)
AS
BEGIN
    

    DECLARE @Tabelas TABLE ( 
        [object_id] INT, 
        [name] VARCHAR(100), 
        create_date DATETIME, 
        lock_escalation_desc VARCHAR(100), 
        temporal_type_desc VARCHAR(100),
        is_replicated BIT
    )

    INSERT INTO @Tabelas
    SELECT 
        A.[object_id], 
        A.[name], 
        A.create_date, 
        A.lock_escalation_desc, 
        A.temporal_type_desc,
        A.is_replicated
    FROM
        sys.tables A
    WHERE
        is_ms_shipped = 0


    DECLARE @Tabelas_Internas TABLE ( 
        [object_id] INT,
        [Descricao] VARCHAR(100) 
    )

    INSERT INTO @Tabelas_Internas 
    VALUES(965578478, 'Tabela de Confirmados'), (1013578649, 'Tabela de Casos Recuperados'), (338100245, 'Tabela Interna de Log')


    INSERT INTO @Retorno
    SELECT 
        A.[object_id], 
        A.[name], 
        A.create_date, 
        A.lock_escalation_desc, 
        A.temporal_type_desc, 
        B.Descricao
    FROM
        @Tabelas A
        LEFT JOIN @Tabelas_Internas B ON B.[object_id] = A.[object_id]
    WHERE  
        A.is_replicated = 0
    ORDER BY
        [name]


    RETURN


END

Después de crear la función, seleccione la opción “Obtener datos” > “SQL Server” y escriba el comando SELECT para leer los datos de la función:

Después de hacer clic en el botón “Aceptar”, sus datos se cargarán normalmente:

Simplemente haga clic en el conocido botón "Cerrar y aplicar" y nuestra tarea estará lista.

Limitaciones

El uso de Procedimientos Almacenados o funciones para conectarse a datos con DirectQuery tiene algunas limitaciones que puedo resaltar:

  • No se pueden pasar parámetros dinámicos a un procedimiento almacenado o una función con valores de tabla de forma dinámica
  • Cuando se utiliza una segmentación de datos, por ejemplo, los datos se ejecutan en la base de datos y luego se filtran los datos devueltos utilizando las segmentaciones, por lo tanto, esto puede causar problemas de rendimiento en tablas muy grandes.
  • A diferencia de un informe paginado, que vuelve a calcular todo el conjunto de datos después de las interacciones del usuario, Power BI no actualiza los datos en el origen con cada filtro o interacción aplicado en DirectQuery.
  • Ambas soluciones presentarán problemas de rendimiento en grandes volúmenes de datos
  • Las 2 técnicas presentadas requieren ciertos conocimientos de T-SQL y sólo funcionan en SQL Server. Esto puede resultar un poco complejo para un usuario empresarial que no sea de TI.
  • DirectQuery por sí solo ya tiene algunas limitaciones con las que te puedes encontrar accediendo a este enlace aquí

¡Eso es todo, amigos!
Espero que te haya gustado este consejo, que te sea útil en alguna necesidad específica y ¡hasta la próxima!