Incremental Refresh & Real Time con Direct Query en Power BI

Incremental refresh and real time con Direct Query en Power BI

Bienvenidos/as al mejor blog de Data & Cloud. En el post de hoy vamos a hablar sobre Hybrid tables: Incremental refresh & Real time with DirectQuery. ¡Empezamos!

Las actualizaciones de Power Bi del mes de Mayo, han venido cargadas de nuevas características. Pero sobre todo, y como novedad a destacar nos encontramos con Microsoft Fabric, de la que hablaremos en posteriores entradas del blog. Pero hoy, quiero hacer especial énfasis en una nueva funcionalidad muy interesante, las Hybrid tables.

Hybrid Tables

Las Hybrid tables son una función de partición la cual nos deja utilizar el modo Import y modo DirectQuery en una misma tabla. Gracias a esta nueva característica podemos mejorar el rendimiento de nuestras soluciones ya que, tendremos almacenadas en particiones en modo Import nuestros datos históricos y dispondremos de actualización en tiempo real para nuestros datos más recientes consultándolos en modo DirectQuery.

En la siguiente imagen, observamos un ejemplo de partición donde destacan en verde, los datos archivados que solo se actualizarán la primera vez que carguemos el dataset. En amarillo, los datos que se cargarán en cada refresco que ejecutemos desde una programación o a mano. Y en rojo, los datos actuales que se van cargando progresivamente.

¡IMPORTANTE!

El refresco de datos en tiempo real con DirectQuery solo está disponible en Power BI Premium, Premium por usuario y Power BI Embedded.

Buenos y sin mas dilación vamos a empezar por el ¡ASÍ SE HACE!

Incremental Refresh & Real Time

Imaginemos que tenemos un sensor que va insertando de forma recurrente, cada 2 segundos la siguiente informacion: fecha_insercion y value en SQL Server.

Para recrear el sensor he creado el siguiente script en SQL Sever donde primero cargareis el histórico y después ejecutareis cada vez que queráis emular el sensor el procedimiento almacenado.

/****** PASO 1: CREAR TABLA ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_incremental_refresh__realtime]') AND type in (N'U'))
DROP TABLE [dbo].[table_incremental_refresh__realtime]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[table_incremental_refresh__realtime](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[date_insert] [datetime] NOT NULL,
	[value] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[table_incremental_refresh__realtime] ADD  DEFAULT (getdate()) FOR [date_insert]
GO

/****** PASO 2: RELLENAR HISTORICO ******/
--NECESITARIS LA CREACION DE LA TABLA DIMDATE, PODEIS ENCONTRAR EL SCRIPT EN OTRAS ENTRADAS DE ESTE BLOG
INSERT INTO [dbo].[table_incremental_refresh__realtime]
select date,cast(RAND()*1000 as int) 
from [dbo].[DimDate] where date<getdate()

/****** PASO 3: CREACION Y POSTERIOR EJECUCION DEL PROCEDIMIENTO ALMACENADO ******/
CREATE procedure [dbo].[sp_insert_table_realtime]
as

declare @a as  datetime
set @a=dateadd(MINUTE, 100,GETDATE())

while GETDATE()<@a
begin

INSERT INTO [dbo].[table_incremental_refresh__realtime]
           ([value])
     VALUES
           ( cast(RAND()*1000 as int))

WAITFOR DELAY '00:00:02';

end

GO


EXEC [dbo].[sp_insert_table_realtime]

Una vez tengamos creado nuestro origen de datos que emula ser un sensor, podemos seguir con la creación de nuestro Power Bi. Para ello abrimos Power Bi Desktop, elegimos como origen de datos SQL Server y añadimos los datos necesario para establecer la conexión.

Después de cargar los datos en Power Bi Desktop como podemos observar en la imagen anterior, vamos a proceder a crear los parámetros necesario para el refresco incremental.

Incremental Refresh

Estos parámetros se utilizarán inicialmente para filtrar los datos por un campo fecha o campo fecha formateado a int. Para ello nos vamos a Administrar parámetros y creamos los parámetros RangeStar y RangeEnd.

A continuación, nos dirigimos a la tabla donde queremos implementar el refresco incremental y seleccionamos la columna de tipo fecha donde debemos aplicar los filtros con los valores de los parámetros como podemos ver en las siguientes imágenes:

Aplicamos los cambios y nos refrescará el dataset con los filtros que le hemos puesto.

Ahora abrimos la vista de modelo y procedemos a configurar el Incremental refresh.

Habilitamos Incremental refresh en la tabla pertinente y le indicamos que vamos a dejar archivados los últimos 5 años y que se haga el refresco incremental de los últimos 4 días. Esto significa que la primera vez que publiquemos el Power Bi se procesará todo el dataset, quedando archivados los últimos 5 años entendiendo que estos no van a ser modificados a posteriori.

También, deberemos indicar en la parte de Incrementally refresh data starting, 4 días después de la fecha de refresco de los datos. Esto significa que en el refresco programado que pongamos con el horario que nosotros creamos, 1 al día o cada 8 horas, cuando creamos conveniente por negocio, solo actualizará los datos de los últimos días.

Activamos la opción de real time with DirectQuery y nos aparecerá una imagen con el resumen donde nos indica la información sobre la parte Archived, el incremental refresh y la parte real time y le damos a aplicar.

Visualización en tu Workspace

Hacemos deploy del proyecto y nos aparecerán activos para seleccionar solo los Workspace con Power BI Premium, Premium por usuario y Power BI Embedded. Subimos el proyecto y ya podremos visualizarlo en Power Bi online.

Ya con la solución subida en el Workspace, en nuestro caso una capacidad Premium por usuario podemos coger la url del workspace y abrir el analisis services desde nuestro Management Studio.

Abrimos las particiones y podremos observar todo el particionamiento que nos ha creado de forma automática.

En esta primera imagen detectamos, en verde, las particiones Archived de los últimos 5 años.

Mientras tanto, en el recuadro de color naranja ya cambia de fecha al día siguiente ya que seria las de refresco incremental.

Y en esta imagen podemos ver en naranja las particiones de 4 días del refresco incremental y en rojo los datos del día 31 de abril, hoy mismo que se refrescan por DirectQuery en tiempo real.

Previamente, tendremos que instalar una gateway en el servidor donde esté nuestro SQL Server para que haya conectividad con el servicio de Power Bi, os dejo una foto del Lineaje View.

Real Time

Como podéis ver en este video se ven los datos actualizados cada 2 segundos. Esto se tiene que configurar editando el informe desde el servicio de Power Bi. Le damos a Edit y hacemos clic sobre ningún objeto y nos aparecerá el siguiente panel donde podremos ver Page refresh.

En nuestro caso, configuramos que se actualicen las visualizaciones cada 2 segundos.

Por defecto, aparece configurado por el Administrador de la capacidad con un mínimo de 5 minutos y aunque pongamos 2 segundos se haría cada 5 minutos ya que cada 2 segundos puede suponer un consumo muy elevado de recursos.

Si queremos modificar este parámetro, nos dirigimos a la rosca de arriba a la derecha y seleccionamos Amin Portal y modificamos lo que creamos conveniente.