Best Practices for Real-Time Power BI Dashboard

[mjoseph111]

Hello everyone,
So im working for government and one of our department is looking to develop Power BI dashboard with real time data. Their operational data currently resides in Cartegraph, which is a cloud-based platform, and the dataset is expected to be relatively large.

We are operating under a Power BI Premium P1 license, and I would like some guidance and best practices regarding the following points:

1. For extracting real-time data from a cloud-based platform like Cartegraph, what is the recommended approach under our current license?

   • Do we need to use APIs or any other better options?

2. Is our Premium P1 capacity sufficient for DirectQuery connections to a cloud data source, or are there any limitations or upgrade requirements we should be aware of?

3. How is the performance expected to behave when querying large volumes of real-time data from a cloud source through DirectQuery?

4. Are there any known CPU or capacity constraints we should plan for under the P1 tier?

5. Since DirectQuery does not allow extensive data transformations or DAX modeling, what are the recommended practices for handling data transformations in such scenarios?

Additionally, if there are any other important considerations, performance tuning tips, or architectural recommendations we should keep in mind while implementing this setup,
I would appreciate any insights. 
Thank you so much!

[Murtaza_Ghafoor]

Recommended Approach for Real-Time Power BI Dashboards (Cartegraph + Premium P1)

1. Data Extraction Strategy

   • Use Cartegraph’s REST API to extract data — this is typically the most flexible and supported method for near real-time access.

   • If available, leverage Cartegraph’s OData or Azure integration endpoints for smoother Power BI connectivity.

   • Consider staging the data in Azure SQL Database, Dataverse, or Fabric Lakehouse if direct API calls are too heavy.

2. Connection Mode

   • For real-time visualization, use DirectQuery or Streaming Dataset (depending on latency needs).

   • If “near real-time” (every 5–15 minutes) is acceptable, use scheduled refresh or Fabric Dataflows Gen2 to simplify transformation and caching.

3. Power BI Premium P1 Capacity

   • P1 is suitable for DirectQuery and large model performance, but ensure:

     • Sufficient vCores are available (8 vCores for P1).

     • Refreshes and queries are load-balanced across time to avoid throttling.

     • Use Premium Metrics App to monitor CPU and memory usage.

4. Performance Expectations

   • DirectQuery performance depends heavily on Cartegraph’s API response time and network latency.

   • Avoid complex visuals or measures that trigger multiple queries.

   • Use aggregations, pre-calculated views, and parameterized queries to reduce query load.

5. Transformation Best Practices

   • Perform all heavy data transformations upstream:

     • Use SQL views, Fabric Data Pipelines, or Azure Data Factory for ETL.

     • Keep Power BI’s data model lean—minimal calculated columns and DAX measures.

6. Performance Tuning Tips

   • Enable query caching in Premium capacity.

   • Limit visuals per page (ideally < 8).

   • Use Aggregations + DirectQuery hybrid mode if performance issues arise.

   • Index key columns in the source system.

7. Other Key Considerations

   • Validate Cartegraph API rate limits before scaling.

   • Ensure data security (AAD/OAuth authentication).

   • Monitor performance using Power BI Premium Capacity Metrics App and adjust workloads accordingly.

---

Summary

• Use Cartegraph API + DirectQuery or staging in Fabric/Azure SQL.

• P1 capacity is sufficient with proper optimization and monitoring.

• Offload heavy transformations outside Power BI.

• Focus on caching, aggregation, and capacity tuning for scalability.