Skip to content

Power BI

Embed and use Power BI dashboards in your web application to visualize and analyze data stored in Azure Data Explorer (ADX).

Power BI is a powerful business analytics tool that enables you to create interactive visualizations and reports from your data. When integrated with Azure Data Explorer (ADX), Power BI provides a robust solution for understanding and representing large volumes of simulation data generated by the CosmoTech platform.

Why use Power BI with ADX?

The combination of Power BI and ADX offers several advantages:

  • Real-time Analytics: query and visualize large datasets stored in ADX with minimal latency.
  • Interactive Dashboards: create dynamic, interactive reports that allow users to explore data through filtering and drill-down capabilities.
  • Scalability: handle massive amounts of time-series and telemetry data efficiently.
  • Custom Visualizations: build tailored charts, graphs, and KPIs specific to your simulation scenarios.
  • Embedded Reports: seamlessly integrate Power BI reports directly into your web application.

Common data tables in ADX

The Cosmo Tech platform typically stores the following data in ADX:

  • ProbesMeasures: time-series data from simulation probes and measurements.
  • ScenarioMetadata: information about scenarios (name, description, parameters).
  • ScenarioRun: execution details and results of scenario runs.
  • ScenarioRunMetadata: metadata associated with each scenario run.
  • SimulationTotalFacts: aggregated facts and metrics from simulations.

Creating visualizations

Basic steps

  1. Import Data: connect to ADX and load relevant tables
  2. Transform Data: use Power Query Editor to clean and shape your data
  3. Create Relationships: define relationships between tables (e.g., linking runs to scenarios)
  4. Build Visuals: add charts, tables, and graphs to your report canvas
  5. Add Interactivity: configure filters, slicers, and drill-through actions
  6. Publish: publish your report to Power BI Service for sharing and embedding

Several different visualizations are available, for example:

  • Line Charts: display time-series trends from ProbesMeasures.
  • Bar Charts: compare metrics across different scenarios.
  • KPI Cards: show key performance indicators at a glance.
  • Tables: present detailed scenario parameters and results.
  • Maps: visualize geographic data if applicable to your simulation.

Using KQL in Power BI

You can write Kusto Query Language (KQL) queries directly in Power BI to:

  • Filter and aggregate data before importing.
  • Perform complex calculations and transformations.
  • Optimize query performance for large datasets.

Example KQL query:

ProbesMeasures
| where SimulationRun == "run-123"
| summarize avg(Value) by bin(Timestamp, 1h), ProbeName
| order by Timestamp asc

Embedding reports in the web application

Power BI reports can be embedded directly into the CosmoTech web application:

  1. Publish to Power BI Service: upload your report to a Power BI workspace.
  2. Configure Workspace: set up the workspace in your CosmoTech configuration.
  3. Define Report Settings: specify display options, filters, and navigation preferences.
  4. Apply Dynamic Filters: link report filters to scenario data for context-aware visualizations.

The workspace configuration includes settings for: - Report tags and display names. - Navigation pane options. - Filter pane visibility. - Dynamic filters based on scenario context.

Best Practices

  • Optimize Queries: use aggregations and filters to reduce data volume.
  • Incremental Refresh: configure incremental refresh for large datasets.
  • Limit Visuals: keep reports focused with a reasonable number of visualizations.
  • Use Bookmarks: create bookmarks for different views and perspectives.
  • Test Performance: ensure reports load quickly even with large datasets.
  • Document Calculations: add descriptions to measures and calculated columns.

Dynamic filtering

Power BI reports in the CosmoTech web application support dynamic filtering based on the current scenario context. This allows users to:

  • Automatically view data relevant to the selected scenario.
  • Compare results across different scenario runs.
  • Filter by specific parameters or time ranges.

Dynamic filters are configured in the workspace settings and can link Power BI report filters to scenario metadata stored in ADX.

Troubleshooting

Common Issues

  • Connection Errors: verify cluster URI and database name are correct.
  • Authentication Failures: ensure proper permissions are set in ADX and Power BI.
  • Slow Performance: optimize KQL queries and consider data aggregation.
  • Empty Reports: check that data exists in ADX tables and queries are correct.

Performance Tips

  • Use DirectQuery mode for real-time data or Import mode for better performance.
  • Create aggregation tables in ADX for frequently accessed metrics.
  • Limit the date range of data being queried.
  • Use materialized views in ADX for complex calculations.

References

For more detailed information on Power BI, please refer to: