Skip to content

Power BI

Embed and use Power BI dashboards in your web application to visualize and analyze simulation data stored in your database.

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

Why use Power BI?

Power BI offers several advantages for data visualization:

  • Real-time Analytics: query and visualize large datasets with minimal latency.
  • Interactive Dashboards: create dynamic, interactive reports that allow users to explore data through filtering and drill-down capabilities.
  • Database Connectivity: connect to PostgreSQL, MySQL, SQL Server, and many other databases.
  • 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

The CosmoTech platform typically stores the following data in your database:

  • 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. Connect to Database: configure your database connection in Power BI.
  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 SQL in Power BI

You can write SQL queries directly in Power BI to:

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

Example SQL query:

SELECT 
    DATE_TRUNC('hour', timestamp) AS hour,
    probe_name,
    AVG(value) AS avg_value
FROM probes_measures
WHERE simulation_run = 'run-123'
GROUP BY DATE_TRUNC('hour', timestamp), probe_name
ORDER BY hour 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 the database.

Troubleshooting

Common Issues

  • Connection Errors: verify database host, port, and credentials are correct.
  • Authentication Failures: ensure proper database permissions are configured.
  • Slow Performance: optimize SQL queries and consider adding database indexes.
  • Empty Reports: check that data exists in the tables and queries return results.

Performance Tips

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

References

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