Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
7 months ago
Solved

Power BI dual Storgae Mode question

I’m working through a design decision related to Dual storage mode and how it interacts with DirectQuery models - specifically around the requirement to Mark as Date Table.


Since a Date table cannot be marked in pure DirectQuery, I’m experimenting with two versions of my data model (DM1 and DM2):
• DM1: All tables in DirectQuery
• DM2: Calendar table set to Dual, all other tables remain DirectQuery


The Dual approach lets me mark the Calendar table as a Date table, which is great. However, I’ve noticed that as soon as I introduce Dual storage mode, the model now requires an explicit refresh schedule, even though the fact/dimension tables are still DirectQuery.

My question:

If I don’t configure a refresh schedule for DM2, does that mean:

  • The DirectQuery tables will not reflect up‑to‑date data
  • Downstream analysis using DM2's DQ tables will not show fresh results
  • Even though I don’t care whether the Calendar table itself refreshes, the presence of a Dual table forces the entire dataset to require scheduled refresh

In short: Does adding a Dual table effectively “convert” the dataset into something that must be refreshed, even if the core data is still DirectQuery?

 

 

 

Thank you in advance

AlexisOlson 

Need Help
  • smpa01's avatar
    smpa01
    6 months ago

    Thanks for taking the time to respond.

     

    I want to clarify what I was actually asking, because the question wasn’t about how to build a Date table or whether it should be DirectQuery or Import.

     

    My question is specifically about refresh behavior in a mixed (Dual + DirectQuery) storage model.

    To restate it clearly:

    • In a Dual model where one table is Import and all other tables are DirectQuery,
    • And where I do not care whether the Import table (Calendar) ever refreshes,
    • Do I still need to configure an explicit refresh schedule in order for the DirectQuery tables to continue returning up‑to‑date data and for downstream reports to reflect real‑time results?

    I’ve now tested this myself, and the behavior is:

    • Even with a Importl table present,
    • And without any scheduled refresh configured,
    • The DirectQuery tables continue to return live data, (which is simply amazing)
    • And downstream reports do update correctly based on the DQ sources.

    So the presence of an Import table does not force the dataset to require a refresh schedule for DirectQuery to keep working.

    In the future, I’d really appreciate answers that address the question directly rather than reframing it into a different problem (the classic XY problem). I wasn’t asking how to build a Date table - I was asking about the refresh implications of Dual storage mode.

    Thanks again.

     

     

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    7 months ago

    Dates are immutable. Create an external Calendar table that is slightly oversized (say, including two future years) and then import that table.  No need to use Direct Query for that, and no need to refresh it for at least a year.

    • v-menakakota's avatar
      v-menakakota
      Icon for Community Support rankCommunity Support
      6 months ago

      Hi smpa01 ,

      Thanks for reaching out to the Microsoft fabric community forum. 

       

      I would also take a moment to thank lbendlin   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

       

       

      Best Regards, 
      Community Support Team

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    6 months ago

    Hi smpa01 

     

    What I would say is that if you have a semantic model, you typically will have something that needs to be refreshed and you will have to configure that in the data source settings as you shown above.

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    6 months ago

    Thanks for taking the time to respond.

     

    I want to clarify what I was actually asking, because the question wasn’t about how to build a Date table or whether it should be DirectQuery or Import.

     

    My question is specifically about refresh behavior in a mixed (Dual + DirectQuery) storage model.

    To restate it clearly:

    • In a Dual model where one table is Import and all other tables are DirectQuery,
    • And where I do not care whether the Import table (Calendar) ever refreshes,
    • Do I still need to configure an explicit refresh schedule in order for the DirectQuery tables to continue returning up‑to‑date data and for downstream reports to reflect real‑time results?

    I’ve now tested this myself, and the behavior is:

    • Even with a Importl table present,
    • And without any scheduled refresh configured,
    • The DirectQuery tables continue to return live data, (which is simply amazing)
    • And downstream reports do update correctly based on the DQ sources.

    So the presence of an Import table does not force the dataset to require a refresh schedule for DirectQuery to keep working.

    In the future, I’d really appreciate answers that address the question directly rather than reframing it into a different problem (the classic XY problem). I wasn’t asking how to build a Date table - I was asking about the refresh implications of Dual storage mode.

    Thanks again.