Refresh schedule for composite model

[BBConsultancy]

Hi All,

One of my customers is on Amazon RedShift and building blocks (datamarts) are supplied to the Power BI service. Each building block is a semantic model in the Power BI service. I've build a semantic models with a directquery connection to several of those building blocks. In my semantic model I need a date table, so I made it in Power Query.

I've published the semantic model to the Power BI service, but I am not able to set a scheduled refresh to make sure the date table is updated every night. I am able to refresh the semantic model manually in the Power BI service.

Does anyone know how to solve the scheduled refresh issue?

[v-sgandrathi]

Hi @BBConsultancy,

In your case, because your semantic model uses DirectQuery on top of other published semantic models, the scheduled refresh option isn't available in the service. This is normal, as DirectQuery models always pull data in real time and don't require scheduled refreshes.
The date table you created in Power Query is an imported table, which allows you to manually refresh it, but not schedule it. In composite models with both DirectQuery and imported tables, only the imported tables need refreshing.
To set up scheduled refresh, ensure your dataset includes an import connection for the date table. You can store the date table in a datamart, SQL table, or as an Excel/CSV file in OneDrive or SharePoint, then connect it to your model to enable scheduled refresh. Alternatively, you could create the date table entirely in DAX (using CALENDAR() or CALENDARAUTO()), which removes the need for refresh altogether.

Thank  you.

[BBConsultancy]

Thank you for the clarification. The DAX solution won't work unfortunately because my date table would use a TODAY() function. I don't want (and need) future dates in my model to make SPLY measures simpler.

It's still weird though that the service does not let you set a refresh schedule when one or more tables in the model are defined as 'Import'. 

Maybe adding a DAX calculated table to one of the building blocks will work, because they are on import mode with a refresh schedule.

[v-sgandrathi · Accepted solution]

Hi @BBConsultancy,

When using a composite model with a DirectQuery connection on top of semantic models, the service currently does not provide an option for scheduled refresh, even if there are imported tables. Manual refresh works because it processes the imported data, but automated scheduling isn't available in this configuration.

If you need a rolling date table that updates daily using TODAY(), there are two main options:

Upstream refresh: Host the date table in one of your building blocks, such as an import table in a datamart or dataset. These models support scheduled refresh, so the date table remains current and the composite model accesses it through DirectQuery.

External storage: Store the date table in a source like SQL, OneDrive, or SharePoint (using import mode), which allows you to enable scheduled refresh in your semantic model.

Currently, scheduling refresh on the composite model itself is not supported, so managing refresh upstream is the most dependable solution.

Thank you.

[v-sgandrathi]

Hi @BBConsultancy,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

Thank you.

[v-sgandrathi]

Hi @BBConsultancy,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @BBConsultancy,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.