Cosmos db mirroring data in a power bi

[PBILover]

---

Hi,

I’m working with Cosmos DB mirroring and want to access the data in Power BI. Before doing so, I need to flatten some JSON structures, rename columns, and perform additional transformations such as creating measures.

What would be the best approach to achieve this?

• Should I create a view in a Lakehouse and use that in Power BI and then do the incremental dataset refresh in apower BI? Actually in this approach i feel i will be missing the latest update from the database like status change etc
• Or should I create a Lakehouse table where most of the transformations are handled using Dataflows or Data Pipelines, and then connect Power BI to that table?
• Is it possible to do the INCR refresh in a lakehouse?

Is there a better approach you would recommend?

Thanks!

[nilendraFabric · Accepted solution]

Hi @PBILover 

For using Cosmos DB mirrored data in Power BI with transformations and real-time updates, create a Lakehouse table or materialized view where you first flatten JSON structures, rename columns, and apply transformations using SQL or Dataflow Gen2; then enable incremental refresh in either Dataflow Gen2 (for Lakehouse updates) or Power BI (using date filtering) to handle new data efficiently, and finally connect Power BI via Direct Lake mode to the transformed Lakehouse data – this ensures near real-time access to status changes while avoiding data duplication and maintaining transformation logic at the source.

[v-prasare · Accepted solution]

Hi @PBILover,

We would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

@nilendraFabric, thanks for your prompt response.

Thanks,

Prashanth Are

MS Fabric community support

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

[PBILover]

@v-prasare @nilendraFabric 
If I want to leverage the benefits of database mirroring, would it be a good idea to create a view on the mirrored database that handles JSON flattening and date conversion directly? Then, I could use that view in a Power BI dataset. What would be the impact on a medium-sized dataset, and what factors should I consider when implementing this approach?
Thanks

[v-echaithra]

Hi @PBILover ,

Using a view on a mirrored database to handle JSON flattening and date conversion directly, and then leveraging that view in Power BI. If you're using a read-only mirrored database (i.e., a mirrored replica), you should ensure that your transformations (like JSON flattening and date conversions) are executed efficiently. The benefit here is offloading the work to the database server, which might be more powerful and capable of handling such transformations efficiently, rather than relying on Power BI for complex data wrangling.

Ensure that your database tables especially the JSON columns and date columns are indexed properly. This helps speed up query execution. If your JSON is highly nested, use specific functions (like JSON_VALUE or OPENJSON in SQL Server) to extract relevant fields before flattening. Avoid loading unnecessary data. Use Power BI’s query folding capabilities and only retrieve necessary columns in the query to improve refresh performance. Before committing, test the performance of your view under typical usage conditions. If necessary, tweak the design (e.g., move some transformations to Power BI or use materialized views for complex aggregations).

[PBILover]

Thanks @nilendraFabric  for your input, I will try this approach.

[nilendraFabric · Accepted solution]

Hi @PBILover 

For using Cosmos DB mirrored data in Power BI with transformations and real-time updates, create a Lakehouse table or materialized view where you first flatten JSON structures, rename columns, and apply transformations using SQL or Dataflow Gen2; then enable incremental refresh in either Dataflow Gen2 (for Lakehouse updates) or Power BI (using date filtering) to handle new data efficiently, and finally connect Power BI via Direct Lake mode to the transformed Lakehouse data – this ensures near real-time access to status changes while avoiding data duplication and maintaining transformation logic at the source.