Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
BW_RFA
Helper I
Helper I

Cosmos DB Mirroring Power BI

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

1 ACCEPTED SOLUTION
AndyDDC
Most Valuable Professional
Most Valuable Professional

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

View solution in original post

7 REPLIES 7
BW_RFA
Helper I
Helper I

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
Most Valuable Professional
Most Valuable Professional

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

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
Most Valuable Professional
Most Valuable Professional

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

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

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
Most Valuable Professional
Most Valuable Professional

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

November Update

Fabric Monthly Update - November 2024

Check out the November 2024 Fabric update to learn about new features.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.