Composite Model and calculated column in DirectQuery tables

[PshemekFLK]

Hello,

I created my first composite model consisting of two sources - Excel sharepoint files and Azure Analysis Services database (DirectQuery). When I tried to publish the dashboard to service I got this warning:

I was surprised as I didn't have any calculated columns or tables refering to DirectQuery tables (I assume that's what remote tables are?). I just clicked yes and published and it worked perfectly. No refresh errors.

Now I want to start expanding my model and start building calculated columns on the DirectQuery tables. For now, as a test I built one calculated column and republished it. Still got the warning but still no refresh errors. This is great but I'm a bit worried that I don't understand the implications of what I'm doing and will invest a lot of time into expanding the model just to see the refresh failing suddenly.

Can someone clairfy what scenario in my case could cause refresh failures in the future? Can I keep adding calculated columns to the DirectQuery tables and build measures using those columns?

[Covenant-bi]

Yes, you can keep adding calculated columns to the DirectQuery tables and build measures using those columns. However, there are some scenarios that could cause refresh failures in the future:

• If you make a change to the underlying data source that affects the calculated column, the calculated column will not be updated automatically. This could cause the refresh to fail if the calculated column is used in a measure or filter.
• If you add a new column to the underlying data source, the calculated column will not be aware of the new column. This could cause the refresh to fail if the new column is used in a measure or filter.
• If you remove a column from the underlying data source, the calculated column will still refer to the removed column. This could cause the refresh to fail.

To avoid refresh failures, you should make sure that you understand the implications of adding calculated columns to DirectQuery tables. You should also periodically test the refresh to make sure that it is working correctly.

Here are some additional things to keep in mind:

• Calculated columns in DirectQuery mode are only calculated when the data is refreshed. This means that if you change the values in the underlying data source, the calculated columns will not be updated automatically.
• Calculated columns in DirectQuery mode can be slow to calculate, especially if the underlying data set is large.
• Calculated columns in DirectQuery mode can be difficult to troubleshoot if there are errors.

[PshemekFLK]

Many thanks for the detailed explanation! This is very helpful! Just to make sure I understood correctly:

1) If there is change in value in one of the columns of the underlying data source (let's call it "Net USD") and the calculated column (let's call it "Net USD Calculated") is referring to "Net USD," the "Net USD Calculated" will only get updated if I run the refresh of the report. This scenario will not cause refresh failures?

2) If someone would change the name of the "Net USD" column to "Net USD 2023" this would cause a refresh failure since calculated column "Net USD Calculated" is referring to the original name of the column which is "Net USD"?