SQL Server Profiler output

arvid-d3m · ‎12-02-2020

Hi,

The COMBINEVALUES() function produces a folding error when used to join two tables on two (non-text) columns in Direct Query mode for Snowflake. Using this technique with text columns (i.e. VARCHAR) does work as expected however.

It appears to be an issue related to casting numbers to text (see tracing detail below), same issue arises when using CONCACTENATE() with non-text values. The generated SQL does make sense on its own.

This is a real issue as joining on two columns is critical for performance and/or functionality and I don’t believe there to be a sustainable workaround for the use case. Steps to reproduce this issue below.

Regards,

Arvid

**

The calculated columns are defined as PROMO_ITEM_KEY = COMBINEVALUES(",", STORE_SALES[SS_ITEM_SK], STORE_SALES[SS_PROMO_SK]) and PROMO_ITEM_KEY = COMBINEVALUES(",", PROMOTION[P_ITEM_SK], PROMOTION[P_PROMO_SK]) respectively.

This example is using the STORE_SALES and PROMOTION tables from the Snowflake sample schema TPCDS_SF10TCL. The diagnostics output below is generated after attempting to summarize the NET_PROFIT column from STORE_SALES over PROMO_NAME from the PROMOTION table.

I’m using the Nov 2020 version of Power BI desktop, 2.87.762.0 (20.11), and I’m connecting through the Snowflake connector rather than ODBC.

Not sure whether Power BI desktop is leveraging my local ODBC driver rather than a bundled version, but if it is, my system is using 2.22.02.00 of the Snowflake ODBC driver.

Power BI desktop traces (edited)

OdbcQuery/FoldingWarning {"Start":"2020-12-02T10:57:12.3110885Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"32420","PartitionKey":"Expression/Expression","Function Name":"VisitBinary","ProductVersion":"2.87.762.0 (20.11)","ActivityId":"f69cc219-a74d-43a4-afe4-bcb562533a87","Process":"Microsoft.Mashup.Container.NetFX45","Pid":33864,"Tid":1,"Duration":"00:00:00.0000272"}

OdbcQuery/FoldingWarning {"Start":"2020-12-02T10:57:12.3112017Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"32420","PartitionKey":"Expression/Expression","Function Name":"VisitBinary","ProductVersion":"2.87.762.0 (20.11)","ActivityId":"f69cc219-a74d-43a4-afe4-bcb562533a87","Process":"Microsoft.Mashup.Container.NetFX45","Pid":33864,"Tid":1,"Duration":"00:00:00.0000062"}

OdbcQuery/FoldingWarning {"Start":"2020-12-02T10:57:12.3112157Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"32420","PartitionKey":"Expression/Expression","Function Name":"VisitConcat","ProductVersion":"2.87.762.0 (20.11)","ActivityId":"f69cc219-a74d-43a4-afe4-bcb562533a87","Process":"Microsoft.Mashup.Container.NetFX45","Pid":33864,"Tid":1,"Duration":"00:00:00.0000082"}

OdbcQuery/FoldingWarning {"Start":"2020-12-02T10:57:12.3112294Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"32420","PartitionKey":"Expression/Expression","Function Name":"ConvertForSize","ProductVersion":"2.87.762.0 (20.11)","ActivityId":"f69cc219-a74d-43a4-afe4-bcb562533a87","Process":"Microsoft.Mashup.Container.NetFX45","Pid":33864,"Tid":1,"Duration":"00:00:00.0000038"}

OdbcQuery/FoldingWarning {"Start":"2020-12-02T10:57:12.3112451Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"32420","PartitionKey":"Expression/Expression","ErrorMessage":"ODBC SQL Type LONGVARCHAR was not found in the SQLGetTypeInfo data from the ODBC driver. You can override the type information using SQLGetTypeInfo.","ProductVersion":"2.87.762.0 (20.11)","ActivityId":"f69cc219-a74d-43a4-afe4-bcb562533a87","Process":"Microsoft.Mashup.Container.NetFX45","Pid":33864,"Tid":1,"Duration":"00:00:00.0000736"}

OdbcQuery/FoldingWarning {"Start":"2020-12-02T10:57:12.3113298Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"32420","PartitionKey":"Expression/Expression","ErrorMessage":"Unable to find data type with size 16777217 from data types SQL_VARCHAR,SQL_LONGVARCHAR.","ProductVersion":"2.87.762.0 (20.11)","ActivityId":"f69cc219-a74d-43a4-afe4-bcb562533a87","Process":"Microsoft.Mashup.Container.NetFX45","Pid":33864,"Tid":1,"Duration":"00:00:00.0000106"}

OdbcQueryDomain/ReportFoldingFailure {

"Start":"2020-12-02T10:57:12.3114109Z","Action":"OdbcQueryDomain/ReportFoldingFailure","HostProcessId":"32420","PartitionKey":"Expression/Expression","Exception":"Exception:\r\n

ExceptionType: Microsoft.Mashup.Engine1.Runtime.FoldingFailureException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\n

Message: Exception of type 'Microsoft.Mashup.Engine1.Runtime.FoldingFailureException' was thrown.\r\n

StackTrace:\n

at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.ConvertForSize(OdbcScalarExpression left, OdbcScalarExpression right, Int32 size, Boolean fallbackToLargestType)\r\n

at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitConcat(OdbcScalarExpression left, OdbcScalarExpression right)\r\n

at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitBinary(BinaryQueryExpression binaryExpression)\r\n

at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.AddColumns(IList`1 queryExpressions, Keys keys, IValueReference[] typeValues, List`1 newColumns, OdbcQuerySpecification newQuerySpecification, Boolean allowAggregates, Int32[] groupKey, IList`1 tableKeys)\r\n

at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.AddColumns(ColumnsConstructor columnGenerator)\r\n

at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)\r\n\r\n\r\n

","ProductVersion":"2.87.762.0 (20.11)","ActivityId":"f69cc219-a74d-43a4-afe4-bcb562533a87","Process":"Microsoft.Mashup.Container.NetFX45","Pid":33864,"Tid":1,"Duration":"00:00:00.0001295"

}

SQL Server Profiler output

DAX Query Plan

'PROMOTION'[PROMO_ITEM_KEY]: CombineValues: ScaLogOp DependOnCols(1, 5)('PROMOTION'[P_PROMO_SK], 'PROMOTION'[P_ITEM_SK]) String DominantValue=NONE

Constant: ScaLogOp DependOnCols()() String DominantValue=,

Double->String: ScaLogOp DependOnCols(5)('PROMOTION'[P_ITEM_SK]) String DominantValue=NONE

'PROMOTION'[P_ITEM_SK]: ScaLogOp DependOnCols(5)('PROMOTION'[P_ITEM_SK]) Double DominantValue=NONE

Double->String: ScaLogOp DependOnCols(1)('PROMOTION'[P_PROMO_SK]) String DominantValue=NONE

'PROMOTION'[P_PROMO_SK]: ScaLogOp DependOnCols(1)('PROMOTION'[P_PROMO_SK]) Double DominantValue=NONE

'STORE_SALES'[PROMO_ITEM_KEY]: CombineValues: ScaLogOp DependOnCols(3, 9)('STORE_SALES'[SS_ITEM_SK], 'STORE_SALES'[SS_PROMO_SK]) String DominantValue=NONE

Constant: ScaLogOp DependOnCols()() String DominantValue=,

Double->String: ScaLogOp DependOnCols(3)('STORE_SALES'[SS_ITEM_SK]) String DominantValue=NONE

'STORE_SALES'[SS_ITEM_SK]: ScaLogOp DependOnCols(3)('STORE_SALES'[SS_ITEM_SK]) Double DominantValue=NONE

Double->String: ScaLogOp DependOnCols(9)('STORE_SALES'[SS_PROMO_SK]) String DominantValue=NONE

'STORE_SALES'[SS_PROMO_SK]: ScaLogOp DependOnCols(9)('STORE_SALES'[SS_PROMO_SK]) Double DominantValue=NONE

__DS0Core: Union: RelLogOp VarName=__DS0Core DependOnCols()() 0-3 RequiredCols(0, 1, 2, 3)('PROMOTION'[P_PROMO_NAME], ''[IsGrandTotalRowTotal], ''[SumSS_NET_PROFIT], ''[])

GroupSemiJoin: RelLogOp DependOnCols()() 0-2 RequiredCols(0, 1, 2)('PROMOTION'[P_PROMO_NAME], ''[IsGrandTotalRowTotal], ''[SumSS_NET_PROFIT])

Scan_Vertipaq: RelLogOp DependOnCols()() 0-0 RequiredCols(0)('PROMOTION'[P_PROMO_NAME])

Constant: ScaLogOp DependOnCols()() Boolean DominantValue=false

Sum_Vertipaq: ScaLogOp DependOnCols(0)('PROMOTION'[P_PROMO_NAME]) Double DominantValue=BLANK

Scan_Vertipaq: RelLogOp DependOnCols(0)('PROMOTION'[P_PROMO_NAME]) 1-46 RequiredCols(0, 24)('PROMOTION'[P_PROMO_NAME], 'STORE_SALES'[SS_NET_PROFIT])

'STORE_SALES'[SS_NET_PROFIT]: ScaLogOp DependOnCols(24)('STORE_SALES'[SS_NET_PROFIT]) Double DominantValue=NONE

GroupSemiJoin: RelLogOp DependOnCols()() 0-2 RequiredCols(0, 1, 2)('PROMOTION'[P_PROMO_NAME], ''[IsGrandTotalRowTotal], ''[SumSS_NET_PROFIT])

Constant: ScaLogOp DependOnCols()() Boolean DominantValue=true

Sum_Vertipaq: ScaLogOp DependOnCols()() Double DominantValue=BLANK

Scan_Vertipaq: RelLogOp DependOnCols()() 0-45 RequiredCols(23)('STORE_SALES'[SS_NET_PROFIT])

'STORE_SALES'[SS_NET_PROFIT]: ScaLogOp DependOnCols(23)('STORE_SALES'[SS_NET_PROFIT]) Double DominantValue=NONE

__DS0PrimaryWindowed: TopN: RelLogOp VarName=__DS0PrimaryWindowed DependOnCols()() 0-3 RequiredCols(0, 1, 2, 3)('PROMOTION'[P_PROMO_NAME], ''[IsGrandTotalRowTotal], ''[SumSS_NET_PROFIT], ''[])

TableVarProxy: RelLogOp DependOnCols()() 0-3 RequiredCols(0, 1, 2, 3)('PROMOTION'[P_PROMO_NAME], ''[IsGrandTotalRowTotal], ''[SumSS_NET_PROFIT], ''[]) RefVarName=__DS0Core

Constant: ScaLogOp DependOnCols()() Integer DominantValue=502

''[IsGrandTotalRowTotal]: ScaLogOp DependOnCols(1)(''[IsGrandTotalRowTotal]) Boolean DominantValue=NONE

'PROMOTION'[P_PROMO_NAME]: ScaLogOp DependOnCols(0)('PROMOTION'[P_PROMO_NAME]) String DominantValue=NONE

Order: RelLogOp DependOnCols()() 0-3 RequiredCols(0, 1, 2, 3)('PROMOTION'[P_PROMO_NAME], ''[IsGrandTotalRowTotal], ''[SumSS_NET_PROFIT], ''[])

TableVarProxy: RelLogOp DependOnCols()() 0-3 RequiredCols(0, 1, 2, 3)('PROMOTION'[P_PROMO_NAME], ''[IsGrandTotalRowTotal], ''[SumSS_NET_PROFIT], ''[]) RefVarName=__DS0PrimaryWindowed

''[IsGrandTotalRowTotal]: ScaLogOp DependOnCols(1)(''[IsGrandTotalRowTotal]) Boolean DominantValue=NONE

'PROMOTION'[P_PROMO_NAME]: ScaLogOp DependOnCols(0)('PROMOTION'[P_PROMO_NAME]) String DominantValue=NONE

Direct Query Begin

SELECT

TOP (1000001) [t1].[P_PROMO_NAME],SUM([t0].[SS_NET_PROFIT])

AS [a0]

FROM

(

SELECT [t0].[SS_ITEM_SK] AS [SS_ITEM_SK],[t0].[SS_PROMO_SK] AS [SS_PROMO_SK],[t0].[SS_NET_PROFIT] AS [SS_NET_PROFIT],(

COALESCE(

CAST([t0].[SS_ITEM_SK] AS VARCHAR(4000))

, '')

+ (N',' +

COALESCE(

CAST([t0].[SS_PROMO_SK] AS VARCHAR(4000))

, '')

)) AS [PROMO_ITEM_KEY]

FROM [STORE_SALES] AS [t0]

)

AS [t0]

LEFT OUTER JOIN

(

SELECT [t1].[P_PROMO_SK] AS [P_PROMO_SK],[t1].[P_ITEM_SK] AS [P_ITEM_SK],[t1].[P_PROMO_NAME] AS [P_PROMO_NAME],(

COALESCE(

CAST([t1].[P_ITEM_SK] AS VARCHAR(4000))

, '')

+ (N',' +

COALESCE(

CAST([t1].[P_PROMO_SK] AS VARCHAR(4000))

, '')

)) AS [PROMO_ITEM_KEY]

FROM [PROMOTION] AS [t1]

)

AS [t1] on

(

([t0].[SS_ITEM_SK] = [t1].[P_ITEM_SK])

AND

([t0].[SS_PROMO_SK] = [t1].[P_PROMO_SK])

)

GROUP BY [t1].[P_PROMO_NAME]

Error

OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..

Query Text:

<pii>DEFINE

VAR __DS0Core =

SUMMARIZECOLUMNS(

ROLLUPADDISSUBTOTAL('PROMOTION'[P_PROMO_NAME], "IsGrandTotalRowTotal"),

"SumSS_NET_PROFIT", CALCULATE(SUM('STORE_SALES'[SS_NET_PROFIT]))

)

VAR __DS0PrimaryWindowed =

TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'PROMOTION'[P_PROMO_NAME], 1)

EVALUATE

__DS0PrimaryWindowed

ORDER BY

[IsGrandTotalRowTotal] DESC, 'PROMOTION'[P_PROMO_NAME]</pii>

v-lili6-msft · ‎12-02-2020

hi @arvid-d3m

This is a similar case had beem submitted to PG which datasource is Amazon RedShift Using Direct Query, ICM:141105343

User Sees 'Can't Display Visual' When Using COMBINEVALUES DAX Function - Amazon RedShift Using Direct Query

and this is feedback:

Conveyed the message to the customer that it is not supported as of now.

The customer mistook my message for ODBC in general, not in relation to the DAX function COMBINEVALUES.

I talked with Santosh about the ticket. The current plan as I understand it to transfer the ticket back to CSS who will try and restate to the customer that this isn't a bug, but instead the absence of a feature. A work item will be created in our systems about the lack of documentation on supported DAX functions.

and if you want to get faster and better technical support for this issue, you may directly create a support ticket.

Regards,

Lin

COMBINEVALUES() does not display intended behaviour with Snowflake Direct Query

Power BI desktop traces (edited)

SQL Server Profiler output

DAX Query Plan

Direct Query Begin

Error

When `select all` is used, then slicer doesn't res...

published report with PERSONALIZE enabled - the sc...

Power BI desktop version

Bug in New Slicer Visual (Rounded Corners)

Power bi embedded keeps crashing for no apparent r...