Set up PowerBI-Synapse

Power BI lets you build custom dashboards/reports to visualize data. In this section we explain how you set up Power BI with Azure Synapse as the data source.

Prerequisites: Azure Parquet data lake + Synapse

  1. Set up Azure Parquet data lake [~10 min]

Note

The above steps are required before proceeding


Example 1: Create a basic Power BI Desktop chart

Power BI Synapse basic plot
  1. Make sure you have completed the prerequisites above
  2. Open Power BI Desktop and create a new report
  3. Go to ‘Get data/More/Azure Synapse Analytics SQL’
  4. In ‘Server’, enter your ‘Serverless SQL endpoint’ from the Synapse overview
  5. As ‘Database’ use your Synapse database name (e.g. database1)[1] and select ‘DirectQuery’
  6. In ‘Advanced’ insert below query (after editing the bracketed fields)
  7. To login, use the ‘Database’ option with sqladminuser and your SQL admin password[2]
SELECT
   DATEADD(SECOND, DATEDIFF(SECOND, '2000', t) / 5 * 5, '2000') AS time,
   AVG(<signal>) AS avg_signal
FROM
   OPENROWSET(
         BULK '/<deviceid>/<message>/*/*/*/*',
         DATA_SOURCE = 'ParquetDataLake',
         FORMAT = 'PARQUET'
   ) AS r
WHERE
   CONCAT(r.filepath(1), '-', r.filepath(2), '-', r.filepath(3)) BETWEEN '<yyyy-MM-dd>' AND '<yyyy-MM-dd>'
   AND
   t BETWEEN '<yyyy-MM-dd HH:mm:ss>' AND '<yyyy-MM-dd HH:mm:ss>'
GROUP BY
   DATEDIFF(SECOND, '2000', t) / 5
ORDER BY
   time
OFFSET 0 ROWS

The above query achieves a number of things:

  • It extracts a single signal and time axis
  • It groups the data into 5-second resampled interval averages
  • It fetches data directly from the Parquet data lake via OPENROWSET
  • It leverages date partitioning to only touch relevant files via the 1st filter
  • It then filters the resulting data by time via the 2nd filter

Example 2: Create a dynamic Power BI dashboard

For practical use we recommend to leverage Parameters and Slicers in Power BI. As inspiration, you can download the below pbix file to see how this can be set up. The Power BI file is intended as inspiration, not a plug & play template.

powerbi-synapse-dynamic-example

[1]This was shown as part of the Synapse Terraform deployment output. If you do not have it, open your synapse-map-tables-<id> Container App Job via the console and go to Settings/Containers and click ‘synapse-map-tables’. Under ‘Environment variables/SYNAPSE_DATABASE’ you can find the database name
[2]This was shown as part of the Synapse Terraform deployment output. If you do not have it, open your synapse-map-tables-<id> Container App Job via the console and go to Settings/Secrets/synapse-password to find it