analytics-visualization

Analytics pipeline visualization: PowerBI dashboard from parquet files in Azure Data Lake sample

This sample will focus on how to visualize FHIR data that has already been converted to parquet files in Azure Data Lake. Converting to parquet files makes it easier to unlock a variety of analytics use cases. This sample creates a PowerBI dashboard from parquet files in an Azure Data Lake storage account via a Synapse workspace.

End-to-end pipeline

The end-to-end pipeline is shown in the diagram below, starting with FHIR data in a FHIR server.

• In Stage 1: Convert FHIR data to Parquet, FHIR data in a FHIR server is converted to Parquet files (to help facilitate easier data analysis) and stored in Data Lake. This is done with the OSS FHIR to Synapse Sync Agent tool.
• In Stage 2: Create external tables, external tables and views of that Parquet files are made in Synapse.
• In Stage 3: Query and Visualize, Stored Procedures query the data to visualize in a PowerBI dashboard. This sample mainly focuses on Stage 3, but pointers will be provided for Stage 1 and 2.

Stage 1: Convert FHIR data to Parquet

First, convert your FHIR data into Parquet files and store them in Azure Data Lake. Converting FHIR data into Parquet files makes it easier to facilitate data analysis later on.

To use our provided sample data parquet files, follow steps in the "Stage 1" section in the Appendix to create a Data Lake and copy our sample Parquet files inside. Please note that this only copies over sample Parquet files into Data Lake and is only used to quickly deploy this sample.

Stage 2: Create external tables and views

Next, create external tables and views from the Parquet files.

To use provided Parquet sample files to run this sample, please follow steps in "Stage 2" section in the Appendix

Stage 3: Query and Visualize

At this point, you should have external tables of data ready to be queried and visualized. In this stage, we will create SQL stored procedures to query the external tables, and visualize that data in PowerBI. This example PowerBI visualizes the percentage of women 50-70 years of age who had a mammogram to screen for breast cancer in the 48 months prior to the end of the measurement period.

Note: This is a simple, basic example to demonstrate capabilities of the FHIR analytics pipeline and data visualization with Power BI, and does not meet requirements of any standard quality measures. This sample uses Synthea data.

Prerequisites needed

1. Microsoft work or school account
2. Azure Synapse Workspace with Serverless SQL Endpoint.
   • The Serverless SQL Endpoint will be used to connect to Database from Power BI Desktop Application to create Power BI Dashboard/reports.
3. Power BI Desktop application
4. Power BI service account and workspace
5. Microsoft SQL Server Management Studio

Query: Setting up Database from SQL Server Management Studio

To use provided Parquet sample files to run this sample, the stored procedure was already created from the Bicep template and is available in the database.

If you would like to explore (view/edit) the stored procedure in Microsoft SQL Server Management Studio, detailed instructions can be found in the Appendix to connect to the database using the “Serverless SQL endpoint”.

Visualize: Checking and editing the dashboard in Power BI desktop application

1. Open the “BCS_Compliance_Dashboard.pbix” file at (../azure-health-data-services-samples/samples/analytics-visualization/powerbiReport)in Power BI Desktop application.

2. The charts get data from the "ComplianceData" table. See the Appendix for more details on navigating the ComplianceData table.

Change the serverless SQL endpoint to your endpoint

This example PowerBI is currently connected to an example endpoint. In this step, you will change the endpoint to your serverless SQL endpoint that you created above.

1. In the “Model” section, click “More Options” button (Three dots in top right corner of the table ), then click on “Edit Query”.

2. A new “Power Query Editor” window will open, click on the "expand" arrow to see the full query. Change the serverless SQL pool URL (blacked out part below) to the URL of your Serverless SQL endpoint. You may be asked to edit your credentials to connect to the Serverless SQL endpoint.

Editing the query to change measurement period date range in parameters

In this query editor, you can also change the measurement period date range. If you'd like detailed instructions on how to do this, follow the Appendix here.

Publish the dashboard in Power BI Service

Finally, publish the dashboard to PowerBI service and view. For detailed instructions, follow the Appendix here.