cancel
Showing results for 
Search instead for 
Did you mean: 

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

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.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>

Status: New
Comments
Community Support

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