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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

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? 

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
Solution Sage
Solution Sage

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Expanding the Synapse Forums

New forum boards available in Synapse

Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayFBCUpdateCarousel

Fabric Monthly Update - May 2024

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