Reporting Database Connector

Route device telemetry to a dedicated SQL reporting database for structured queries and BI dashboards. Optimized for aggregated analytics, scheduled reports, and Power BI integration with pre-built views.

What is Reporting Database

The Reporting Database connector routes IoT telemetry to a dedicated SQL database optimized for reporting and business intelligence workloads. Unlike NoSQL options, a relational database provides familiar SQL syntax for JOINs, aggregations, and structured reporting.

Key Features:

SQL Queries: Use standard SQL for complex JOINs, aggregations, window functions, and CTEs on telemetry data.

BI Integration: Direct connect from Power BI, Tableau, SSRS, or any ODBC/JDBC-compatible tool.

Indexed Performance: Pre-built indexes on device ID, timestamp, and template for fast report generation.

Scheduled Reports: Use SQL Agent or Azure Automation for automated report generation and delivery.

Views and Stored Procedures: Pre-built views for common reporting patterns — daily summaries, device comparisons, trend analysis.

How it Works

When the Reporting Database connector is enabled, /IOTCONNECT™ inserts device telemetry into structured SQL tables:

Device sends telemetry → /IOTCONNECT™ receives and validates data.

Data structured → Telemetry is flattened into a relational schema with device metadata.

Inserted to tables → Records are batch-inserted into normalized telemetry tables.

Reports generated → Query via SQL, connect BI tools, or run scheduled report jobs.

User Manual

Step 1: Navigate to Connector Settings

Go to Settings > Configuration > Live Data Streaming in the /IOTCONNECT™ portal.

Step 2: Enable Reporting Database

Find the Reporting Database connector card and toggle the switch to ON (green).

Step 3: Select Data Types

Choose which data to store: Telemetry, Faulty, Diagnostics, Command History.

Step 4: Run ARM Template

Click the ARM template link. This deploys:

Azure SQL Database or SQL Managed Instance

Pre-built schema with telemetry tables and indexes

Service Principal with db_datareader + db_datawriter roles

Firewall rules for /IOTCONNECT™ platform access

Step 5: Confirm Subscription ID

Subscription ID auto-populates after deployment.

Step 6: Save

Click Save. Telemetry begins flowing to your reporting database.

Use Cases

Executive Dashboards: Build Power BI dashboards showing KPIs, trends, and device fleet health.

Scheduled Reports: Generate daily/weekly/monthly PDF reports with automated SQL queries.

Cross-device Comparison: SQL JOINs to compare performance across devices, templates, or locations.

SLA Reporting: Track uptime, response times, and SLA compliance with SQL aggregations.

Custom Applications: Connect from .NET, Java, or Python apps via standard database drivers.

ⓘ NOTE

Size the database tier based on your expected telemetry volume and concurrent query load. Use table partitioning on timestamp for efficient data management and query performance on large datasets.