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!

[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

[PBILover]

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

[nilendraFabric]

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.