Cómo monitorear datos en tiempo real usando Power BI y PowerShell

¿Te gustaría saber cómo monitorear datos en tiempo real de manera sencilla? Con los conjuntos de datos en streaming puedes analizar cualquier tipo de información al instante.

En este ejemplo, aprenderás a monitorear el uso de una base de datos SQL Server utilizando PowerShell y Power BI, herramientas accesibles que te permitirán visualizar métricas críticas y detectar problemas antes de que se vuelvan graves.

Paso a Paso:

1. Crear un Dataset de Streaming en la web de Power BI

Ve a Power BI Service y navega a "Mi Área de Trabajo". Haz clic en "Nuevo" y selecciona "Conjunto de datos (modelo semántico) en streaming".

Crear modelo semántico en streaming

Elige "API" como el origen de los datos.

Elegir API

Define el esquema de tu dataset con los campos conexiones_activas, uso_de_memoria y marca_tiempo.

Configurar dataset

Copia la URL de la API proporcionada por Power BI. La necesitaremos más adelante para enviar datos.

Información de Dataset

2. Preparar PowerShell para Conectar con SQL Server

Configura una consulta SQL que nos permita obtener la cantidad de conexiones activas y el uso de memoria. La consulta será como sigue:

SELECT 
    (SELECT COUNT(session_id) FROM sys.dm_exec_sessions) AS ConexionesActivas,
    (SELECT physical_memory_in_use_kb / 1024 FROM sys.dm_os_process_memory) AS UsoMemoriaMB
  

3. Escribir el Script en PowerShell para la Consulta y el Envío de Datos

Utiliza PowerShell para realizar la consulta cada 5 segundos y enviar los datos a Power BI. Aquí te dejo el script completo:

# URL del dataset en streaming de Power BI
$endpoint = "https://api.powerbi.com/beta/tuURLstreaming"

# Configuración de conexión a SQL Server
$server = "localhost"
$database = "tu_base_de_datos"

# Consulta SQL para obtener la cantidad de conexiones y uso de memoria
$query = @"
    SELECT 
        (SELECT COUNT(session_id) FROM sys.dm_exec_sessions) AS ConexionesActivas,
        (SELECT physical_memory_in_use_kb / 1024 FROM sys.dm_os_process_memory) AS UsoMemoriaMB
"@

# Loop infinito para ejecutar cada 5 segundos
while ($true) {
    try {
        # Ejecutar consulta SQL para obtener datos
        $connectionString = "Server=$server;Database=$database;Integrated Security=True;"
        $connection = New-Object System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = $connectionString
        $connection.Open()

        $command = $connection.CreateCommand()
        $command.CommandText = $query

        $reader = $command.ExecuteReader()
        $reader.Read() | Out-Null

        $conexionesActivas = $reader["ConexionesActivas"]
        $usoMemoriaMB = $reader["UsoMemoriaMB"]
        $timestamp = (Get-Date).ToString("yyyy-MM-ddTHH:mm:ss")
        
        
        $payload = @{
            "conexiones_activas" = $conexionesActivas
            "uso_de_memoria" = $usoMemoriaMB
            "marca_tiempo" = $timestamp
        }
        
        Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
        
        Write-Output "Datos enviados a Power BI: Conexiones=$conexionesActivas, Memoria=$usoMemoriaMB MB, Timestamp=$timestamp"

        # Cerrar conexión SQL
        $connection.Close()
    } catch {
        Write-Error "Error al ejecutar la consulta o al enviar datos a Power BI: $_"
    }

    # Esperar 5 segundos antes de la próxima iteración
    Start-Sleep -Seconds 5
}
  

4. Probar y Ejecutar el Script

Guarda el script como un archivo .ps1 (por ejemplo, send_data_pbi_stream.ps1). Abre PowerShell como administrador y ejecuta el script:

Este script comenzará a enviar datos de conexiones y memoria de tu servidor SQL Server a Power BI cada '5' segundos.

Ejecución de Script de PS

5. Crear un Informe en Power BI

Ve a Power BI, selecciona el modelo semántico de streaming y seleccionar 'Crear Informe'.

Crear un informe

Puedes añadir gráficos como un gráfico de líneas para mostrar la evolución de las Conexiones Activas y Uso de Memoria en el tiempo.

Recuerda: un Informe contiene todas las visualizaciones en relación al modelo semántico y lo puedes compartir a usuarios de tu organización o con un enlace público, pero tiene la limitación que los datos se actualizarán solo cuando se actualice la página.

Diseño del informe

Para poder ver los datos en tiempo real, debemos crear un Panel o Dashboard

6. Crear un Panel en Power BI

En cada visualización del informe, utiliza el botón de 'Anclar' para incluirlo en un panel existente o crear uno nuevo.

Anclar la visualización

Creación de un panel

Puedes anclar gráficos de diferentes informes en un solo panel y esto es muy útil por si tienes diferentes modelos semánticos en streaming.

6. Visualiza y Analiza en Tiempo Real

Una vez que el panel esté configurado, podrás ver cómo se actualiza automáticamente con los datos cada pocos segundos. Utiliza estos datos para detectar picos en las conexiones o problemas de consumo de memoria y tomar decisiones más informadas.

Panel en Streaming

CONCLUSIÓN

Este método te permite monitorear cualquier tipo de datos en tiempo real y visualizarlos en Power BI de una manera práctica y eficaz. Ahora tienes una vista clara del comportamiento de tus procesos, permitiéndote actuar proactivamente ante cualquier anomalía o cambio inesperado.

Espero que este artículo te haya sido útil y te inspire a implementar soluciones de monitoreo en tiempo real para mejorar la gestión y eficiencia de tus sistemas.