Unable to validate source query

[KoenVerbeeck]

Describe the bug
A fact table has multiple partitions. The partition query uses an inline table-valued function, of which the parameters change for each partition. It takes a while to return rows (and metadata), even if the partition contains no rows. When refreshing table metadata, after a while the error "unable to validate source query is thrown". When capturing the query that TE sends to the database using profiler (you can use extended events if you want ;), we can see it actually completes successfully. Probably some timeout occurred.

To Reproduce
Steps to reproduce the behavior:

1. Have a table with partitions, and the partition query takes a long time to return anything.
2. Right-click on the table and select "refresh table metadata
3. Wait a while.
4. See error

Expected behavior
Since there's no issue with the partition query, the metadata should be able to be refreshed.

Observed behavior

Application specifics

• Tabular Editor version: 2.12.4
• Semantic Engine used: SSAS 2019
• Model Compatibility Level: 1500

Additional context
I could resolve the issue by appending WHERE 1 = 0 to the query of the first partition, effectively short-circuiting the query and returning metadata immediately.

[otykier]

Hi @KoenVerbeeck . Could you please provide the SQL text that you use in the query partition? I'm wondering why the SET FMTONLY flag does not short-circuit the logic to return the metadata immediately. Maybe it's some kind of limitation for table-valued functions.

[KoenVerbeeck]

Hi Daniel,
the SQL statement looks like this:

SELECT a.*
FROM [DWH].[TF_MYFACT](null,2012010101,2012033125,null) a
    INNER JOIN [DWH].[DIM_DATETIME] b ON a.DATETIME_ID = b.DATETIME_ID
    INNER JOIN [DWH].[ETL_CONFIGURATION_FACTS] c
        ON c.NAME = 'MYFACT'
        AND b.YEAR = c.YEAR
        AND b.MONTH = c.MONTH
    WHERE c.CUBE_ENABLED = 1

I also don't know why the SET FMTONLY doesn't work. Adding AND 1 = 0 at the end did.

[otykier]

Which version of SQL Server are you using?

[KoenVerbeeck]

SQL Server 2016

[zenzeinet]

It could simply be the command timeout property of the source connection to the server. I remember having to change it to a long value for some sources. Can't remember the syntax by heart (and is different for legacy vs modern sources)

…

On Wed, Jan 27, 2021, 12:32 KoenVerbeeck ***@***.***> wrote: SQL Server 2016 — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#736 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AGTQLM7Q3WNSTDKGAZI37CDS372U5ANCNFSM4WI6CDYQ> .

[otykier]

Fixed in 2.14.0.

Add an annotation with the name TabularEditor_AddFalseWhereClause and value 1 on the partition, table or data source where you want Tabular Editor to automatically append WHERE 1 = 0 upon metadata refresh.