📊 Tutorial 8: Power BI Integration¶

Create real-time dashboards with Power BI streaming datasets. Visualize live sensor data, anomalies, and KPIs with automatic refresh and interactive reports.

🎯 Learning Objectives¶

✅ Configure Power BI output in Stream Analytics
✅ Create streaming datasets for real-time data
✅ Build live dashboards with tiles and visuals
✅ Implement auto-refresh capabilities
✅ Design KPI monitors and alert visuals

⏱️ Time Estimate: 30 minutes¶

📋 Prerequisites¶

Completed Tutorial 07: Anomaly Detection
Power BI account (free or Pro)
Power BI workspace access

🔌 Step 1: Configure Power BI Output¶

1.1 Authorize Power BI Connection¶

# Authorize Stream Analytics to Power BI (opens browser)
Write-Host "Opening browser to authorize Power BI connection..."
$authUrl = "https://login.microsoftonline.com/common/oauth2/authorize?client_id=1950a258-227b-4e31-a9cf-717495945fc2&response_type=code&redirect_uri=https://portal.azure.com&response_mode=query&resource=https://analysis.windows.net/powerbi/api"
Start-Process $authUrl

1.2 Create Power BI Output¶

# Create Power BI output configuration
$powerbiConfig = @"
{
  "properties": {
    "datasource": {
      "type": "PowerBI",
      "properties": {
        "dataset": "StreamAnalyticsSensorData",
        "table": "RealTimeTelemetry",
        "groupId": "",
        "groupName": "My Workspace",
        "refreshToken": "YOUR_REFRESH_TOKEN"
      }
    }
  }
}
"@

# Note: Easier to configure via Azure Portal
# Portal > Stream Analytics Job > Outputs > Add Power BI
Write-Host "Configure Power BI output through Azure Portal for easier authentication"

📈 Step 2: Create Streaming Query for Power BI¶

2.1 Real-Time Metrics Query¶

-- Real-time sensor metrics for Power BI
SELECT
    deviceId,
    location,
    SUBSTRING(location, 1, CHARINDEX('/', location) - 1) AS building,
    System.Timestamp() AS timestamp,
    AVG(temperature) AS avgTemperature,
    MIN(temperature) AS minTemperature,
    MAX(temperature) AS maxTemperature,
    AVG(humidity) AS avgHumidity,
    AVG(vibration) AS avgVibration,
    COUNT(*) AS eventCount,
    SUM(CASE WHEN status = 'critical' THEN 1 ELSE 0 END) AS criticalCount,
    SUM(CASE WHEN status = 'warning' THEN 1 ELSE 0 END) AS warningCount,
    SUM(CASE WHEN status = 'normal' THEN 1 ELSE 0 END) AS normalCount
INTO
    PowerBIOutput
FROM
    EventHubInput TIMESTAMP BY timestamp
GROUP BY
    deviceId,
    location,
    SUBSTRING(location, 1, CHARINDEX('/', location) - 1),
    TumblingWindow(second, 10);

2.2 Anomaly Detection for Visualization¶

-- Anomaly detection results for Power BI alerts
WITH AnomalyDetection AS (
    SELECT
        deviceId,
        location,
        timestamp,
        temperature,
        vibration,
        status,
        CAST(AnomalyDetection_SpikeAndDip(temperature, 95, 120, 'spikesanddips')
            OVER(PARTITION BY deviceId LIMIT DURATION(minute, 15)) AS RECORD) AS tempAnomaly
    FROM
        EventHubInput TIMESTAMP BY timestamp
)
SELECT
    deviceId,
    location,
    timestamp,
    temperature,
    vibration,
    status,
    tempAnomaly.IsAnomaly AS isAnomaly,
    tempAnomaly.Score AS anomalyScore,
    CASE
        WHEN tempAnomaly.IsAnomaly = 1 AND tempAnomaly.Score > 0.8 THEN 'Critical'
        WHEN tempAnomaly.IsAnomaly = 1 THEN 'Warning'
        ELSE 'Normal'
    END AS alertLevel
INTO
    PowerBIOutput
FROM
    AnomalyDetection
WHERE
    tempAnomaly.IsAnomaly = 1;

2.3 KPI Summary Query¶

-- High-level KPIs for executive dashboard
SELECT
    System.Timestamp() AS timestamp,
    COUNT(DISTINCT deviceId) AS activeDevices,
    COUNT(*) AS totalEvents,
    AVG(temperature) AS avgTemperature,
    MAX(temperature) AS maxTemperature,
    SUM(CASE WHEN status = 'critical' THEN 1 ELSE 0 END) AS criticalDevices,
    SUM(CASE WHEN status = 'warning' THEN 1 ELSE 0 END) AS warningDevices,
    CAST(SUM(CASE WHEN status = 'normal' THEN 1 ELSE 0 END) AS FLOAT) /
        CAST(COUNT(*) AS FLOAT) * 100 AS healthPercentage
INTO
    PowerBIOutput
FROM
    EventHubInput TIMESTAMP BY timestamp
GROUP BY
    TumblingWindow(minute, 1);

🎨 Step 3: Build Power BI Dashboard¶

3.1 Create Streaming Dataset¶

After Stream Analytics starts sending data:

Navigate to Power BI
Select workspace (My Workspace or team workspace)
Dataset should auto-create from Stream Analytics output
Verify data is flowing: Datasets → StreamAnalyticsSensorData → View data

3.2 Create Real-Time Dashboard¶

Create a new dashboard with these tiles:

Tile 1: Active Devices Count ```textVisualization: Card Field: activeDevices (Max) Title: "Active Sensors"

__Tile 2: Average Temperature__
```textVisualization: Gauge
Field: avgTemperature
Minimum: 60
Maximum: 100
Title: "Current Avg Temperature"

Tile 3: Temperature Trend ```textVisualization: Line Chart Axis: timestamp Values: avgTemperature Title: "Temperature Over Time (Last Hour)"

__Tile 4: Devices by Status__
```textVisualization: Donut Chart
Legend: status
Values: Count of deviceId
Title: "Device Health Status"

Tile 5: Critical Alerts ```textVisualization: Card Field: criticalCount (Sum) Format: Conditional (Red if > 0) Title: "Critical Alerts"

### __3.3 Create Detail Report__

Build a detailed report page:

```textPage Layout:
┌─────────────────────────────────────────┐
│  KPI Cards Row                          │
│  [Active] [Avg Temp] [Alerts] [Health%]│
├─────────────────────────────────────────┤
│  Temperature Trend (Line Chart)         │
│                                         │
├──────────────────┬──────────────────────┤
│ Device Status    │  Anomaly Alerts      │
│ (Donut Chart)    │  (Table)             │
├──────────────────┴──────────────────────┤
│ Device Details (Table with drill-down) │
└─────────────────────────────────────────┘

📊 Step 4: Advanced Visualizations¶

4.1 Heat Map Query¶

-- Building heat map data
SELECT
    SUBSTRING(location, 1, CHARINDEX('/', location) - 1) AS building,
    DATEPART(hour, System.Timestamp()) AS hour,
    AVG(temperature) AS avgTemp,
    System.Timestamp() AS timestamp
INTO
    PowerBIOutput
FROM
    EventHubInput TIMESTAMP BY timestamp
GROUP BY
    SUBSTRING(location, 1, CHARINDEX('/', location) - 1),
    DATEPART(hour, System.Timestamp()),
    TumblingWindow(hour, 1);

Power BI Visual: Matrix with conditional formatting

4.2 Anomaly Timeline¶

-- Anomaly events timeline
SELECT
    deviceId,
    location,
    timestamp,
    temperature,
    'Temperature Spike' AS anomalyType,
    CAST(AnomalyDetection_SpikeAndDip(temperature, 95, 120, 'spikes')
        OVER(PARTITION BY deviceId LIMIT DURATION(minute, 15)) AS RECORD).Score AS severity
INTO
    PowerBIOutput
FROM
    EventHubInput TIMESTAMP BY timestamp
WHERE
    CAST(AnomalyDetection_SpikeAndDip(temperature, 95, 120, 'spikes')
        OVER(PARTITION BY deviceId LIMIT DURATION(minute, 15)) AS RECORD).IsAnomaly = 1;

Power BI Visual: Scatter chart (timestamp x temperature, sized by severity)

4.3 Device Health Score¶

-- Device health score calculation
WITH HealthMetrics AS (
    SELECT
        deviceId,
        location,
        AVG(temperature) AS avgTemp,
        STDEV(temperature) AS tempVariability,
        AVG(vibration) AS avgVibration,
        COUNT(*) AS eventCount,
        System.Timestamp() AS timestamp
    FROM
        EventHubInput TIMESTAMP BY timestamp
    GROUP BY
        deviceId,
        location,
        TumblingWindow(minute, 5)
)
SELECT
    deviceId,
    location,
    timestamp,
    avgTemp,
    tempVariability,
    avgVibration,
    -- Health score (0-100)
    CASE
        WHEN avgVibration < 0.5 AND avgTemp BETWEEN 68 AND 75 AND tempVariability < 2 THEN 100
        WHEN avgVibration < 1.0 AND avgTemp BETWEEN 65 AND 80 AND tempVariability < 5 THEN 75
        WHEN avgVibration < 1.5 AND avgTemp BETWEEN 60 AND 85 THEN 50
        ELSE 25
    END AS healthScore,
    CASE
        WHEN avgVibration < 0.5 AND avgTemp BETWEEN 68 AND 75 AND tempVariability < 2 THEN 'Excellent'
        WHEN avgVibration < 1.0 AND avgTemp BETWEEN 65 AND 80 AND tempVariability < 5 THEN 'Good'
        WHEN avgVibration < 1.5 AND avgTemp BETWEEN 60 AND 85 THEN 'Fair'
        ELSE 'Poor'
    END AS healthGrade
INTO
    PowerBIOutput
FROM
    HealthMetrics;

Power BI Visual: KPI indicator with trends

⚡ Step 5: Real-Time Features¶

5.1 Auto-Refresh Configuration¶

Power BI streaming datasets auto-refresh, but for reports:

Open report in edit mode
View ribbon → Page view → Select page
Page settings → Page refresh → Set to 1 second
Publish report

5.2 Mobile Dashboard Layout¶

Create mobile-optimized view:

Dashboard → Edit → Mobile layout
Rearrange tiles for vertical layout
Prioritize: KPIs → Alerts → Charts
Save mobile view

5.3 Email Alerts from Power BI¶

Configure data alerts:

Dashboard tile → More options (...)
Manage alerts
Add alert rule:
Threshold: criticalCount > 0
Check frequency: Every hour
Email: your-email@domain.com

📱 Step 6: Advanced Dashboard Features¶

6.1 Q&A Natural Language¶

Enable Q&A on dashboard:

```textExample questions: - "Show average temperature by building" - "Which devices have critical status?" - "Temperature trend for last hour" - "Count of anomalies by device"

### __6.2 Drill-Through Pages__

Create drill-through from device tile to details:

1. Create detail page
2. Add drill-through field: deviceId
3. Add visuals: temperature history, anomaly list, status timeline
4. Users can right-click device → Drill through

### __6.3 Bookmarks and Buttons__

Create navigation:

```textBookmarks:
- Overview (default view)
- Anomalies (filtered to anomalies only)
- Critical Devices (status = critical)
- Building A Focus
- Building B Focus

✅ Validation and Testing¶

Test Dashboard Responsiveness¶

# Generate burst of data to test real-time updates
python multi_device_simulator.py --duration 300 --rate 5

Watch Power BI dashboard update in real-time.

Performance Optimization¶

Dataset Optimization:
Limit to last 24 hours of data
Pre-aggregate in Stream Analytics
Use appropriate data types
Visual Optimization:
Limit visuals per page (< 10)
Use appropriate chart types
Enable visual interactions selectively

🎓 Key Concepts Learned¶

Streaming vs. Push Datasets¶

Feature	Streaming Dataset	Push Dataset
Data Retention	1 hour	Configurable
Refresh	Real-time	On-demand
Query Capability	Limited	Full
Best For	Live monitoring	Recent history

Best Practices¶

Aggregate data before sending to Power BI
Use tumbling windows (10-60 seconds) for updates
Limit columns to needed fields only
Pre-calculate metrics in Stream Analytics
Use Power BI Pro for sharing dashboards

🚀 Next Steps¶

You've created real-time dashboards! Continue to:

Tutorial 09: Azure Functions Integration →

📚 Additional Resources¶

Tutorial Progress: 8 of 11 complete | Next: Azure Functions Integration

Last Updated: January 2025