Forum Discussion

BW_RFA's avatar
BW_RFA
Icon for Helper I rankHelper I
2 years ago
Solved

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...
data engineering
Data Integration
data warehouse
one lake
power bi
  • AndyDDC's avatar
    AndyDDC
    2 years ago

    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

BW_RFA's avatar
BW_RFA
Icon for Helper I rankHelper I
2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    AndyDDC
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

    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's avatar
      BW_RFA
      Icon for Helper I rankHelper I
      2 years ago

      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's avatar
        AndyDDC
        Icon for Most Valuable Professional rankMost Valuable Professional
        2 years ago

        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