Cosmos DB Mirroring Power BI

[BW_RFA]

Hi,

With Cosmos DB Mirroring in Fabric, if I build a Power BI report using a semantic model from the mirrored DB, does it query the delta files in the lakehouse, or query the Cosmos DB directly e.g. when filtering data using slicers? 

I want to know if mirroring will cost a lot of RU when users use the PBI report, or if ingesting directly daily is the best method.

Thanks,

BW

[BW_RFA]

Hi Andy,

Thanks for clarifying. I have built a view in the Cosmos SQL Analytics Endpoint that expands the nested JSON in the Cosmos DB and got the fields I need. I have then created a PBI report on top of this and the performance is very slow. Ideally, I would be able to use this data in a semantic model with data in my lakehouse as well.

Is this creating the view each time the slicer is changed? How can I improve the way I'm doing this? 

Should I instead ingest the Cosmos DB data each data using the timestamp and dedupe based on a unique ID? However this feels expensive and unnecessary.

Thanks in advance

[AndyDDC]

AFAIK the mirror will simply mirror the cosmos db entities into the lakehouse but not any views you've built in cosmos db.

At the moment it seems like you're connecting from Power BI directly to Cosmos DB. 

Have you tried setting up Cosmos db mirroring in Fabric?  It would mirror the entities so you'd then need to do any transformation on the lakehouse side to shape the data into what you need

[BW_RFA]

I am mirroring the Cosmos DB in Fabric, but then I need to use a script to extract the columns I want for my PBI report. I've saved this as a view in the SQL Analytics Endpoint but I suspect this is querying the DB every time. How can I use the Mirrored Delta files to create the dataset I need for my semantic model? 

[AndyDDC · Accepted solution]

If you've created a view in the lakehouse SQL analytics endpoint this will be querying the lakehouse delta tables, not the source cosmos db. You won't have access to the source cosmos db through the mirroring process

[v-nikhilan-msft]

Hi @BW_RFA 
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. Otherwise, will respond back with the more details and we will try to help.
Thanks

[v-nikhilan-msft]

Hi @BW_RFA 

Thanks for using Fabric Community.

The semantic model built over the mirror will use the mirrored database as a source. The data for the mirror is stored in delta format in OneLake. The PBI report will benefit from using DirectLake where possible (instead of Direct Query or import mode) which will improve performance and reduce cost.

Hope this helps.

[AndyDDC]

Hi @BW_RFA mirroring will create delta managed parquet files in OneLake (hence mirroring is near-realtime), the semantic model will then query the data in onelake, not the source Cosmos DB.