cancel
Showing results for 
Search instead for 
Did you mean: 
0

Error during internal query folding

Hi,

 

I am attempting to create a visualization in Power of 'users per time and day' by using data from a Redshift database but am getting an error message which reads: 

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

 

I am using one date table (called d_date) which contains one date per row and associated information about the date. A row could look like this:

 

date, month, day, year, day of week, day name, month name, quarter, week

2019-02-07, 2, 7, 2019, 4, Thursday, February, 1, 6

 

The 'day name' column has been sorted by 'day of week' in Power BI.

 

The other table (called ga_seo_general) contains a number of columns and a row could look like this:

 

date, hour, users, [...]
2019-02-07, 5, 13, [...]

 

 

I have created a relationship between the 'date' columns in each table. Next step is to create a matrix visualization in Power BI using ga_seo_general[users] as value, ga_seo_general[hour] as column and d_date[day of week] as rows. This gives my a two dimensional table of how many users are visiting a certain site per day of week and hour of day. However, when trying to switch from d_date[day of week] to d_date[day name] as row entry, I get the error message as mentioned in the beginning of my post.

 

I have managed to trace the internal queries for these two examples and can verify that the SQL queries to Redshift are working just fin (I can run the same queries in Redshift and get the correct response). But I am not so sure about these DAX measures:

 

This DAX measure works fine (using d_date[day of week]):

 

DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('ga_seo_general'[hour], "IsGrandTotalRowTotal"),
      ROLLUPADDISSUBTOTAL('d_date'[day of week], "IsGrandTotalColumnTotal"),
      "Sumusers", CALCULATE(SUM('ga_seo_general'[users]))
    )

  VAR __DS0Primary = 
    TOPN(
      102,
      SUMMARIZE(__DS0Core, 'ga_seo_general'[hour], [IsGrandTotalRowTotal]),
      [IsGrandTotalRowTotal],
      0,
      'ga_seo_general'[hour],
      1
    )

  VAR __DS0Secondary = 
    TOPN(
      102,
      SUMMARIZE(__DS0Core, 'd_date'[day of week], [IsGrandTotalColumnTotal]),
      [IsGrandTotalColumnTotal],
      1,
      'd_date'[day of week],
      1
    )

  VAR __DS0CoreNoInstanceFiltersNoTotals = 
    FILTER(
      KEEPFILTERS(__DS0Core),
      AND([IsGrandTotalRowTotal] = FALSE, [IsGrandTotalColumnTotal] = FALSE)
    )

EVALUATE
  GROUPBY(
    __DS0CoreNoInstanceFiltersNoTotals,
    "MinSumusers", MINX(CURRENTGROUP(), [Sumusers]),
    "MaxSumusers", MAXX(CURRENTGROUP(), [Sumusers])
  )

EVALUATE
  __DS0Secondary

ORDER BY
  [IsGrandTotalColumnTotal], 'd_date'[day of week]

EVALUATE
  NATURALLEFTOUTERJOIN(
    __DS0Primary,
    SUBSTITUTEWITHINDEX(
      __DS0Core,
      "ColumnIndex",
      __DS0Secondary,
      [IsGrandTotalColumnTotal],
      ASC,
      'd_date'[day of week],
      ASC
    )
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'ga_seo_general'[hour], [ColumnIndex]

 

 

But this DAX measure (using d_date[day name]) yields an error:

 

DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('ga_seo_general'[hour], "IsGrandTotalRowTotal"),
      ROLLUPADDISSUBTOTAL('d_date'[day name], "IsGrandTotalColumnTotal"),
      "Sumusers", CALCULATE(SUM('ga_seo_general'[users]))
    )

  VAR __DS0Primary = 
    TOPN(
      102,
      SUMMARIZE(__DS0Core, 'ga_seo_general'[hour], [IsGrandTotalRowTotal]),
      [IsGrandTotalRowTotal],
      0,
      'ga_seo_general'[hour],
      1
    )

  VAR __DS0Secondary = 
    TOPN(
      102,
      SUMMARIZE(__DS0Core, 'd_date'[day name], [IsGrandTotalColumnTotal]),
      [IsGrandTotalColumnTotal],
      1,
      'd_date'[day name],
      1
    )

  VAR __DS0CoreNoInstanceFiltersNoTotals = 
    FILTER(
      KEEPFILTERS(__DS0Core),
      AND([IsGrandTotalRowTotal] = FALSE, [IsGrandTotalColumnTotal] = FALSE)
    )

EVALUATE
  GROUPBY(
    __DS0CoreNoInstanceFiltersNoTotals,
    "MinSumusers", MINX(CURRENTGROUP(), [Sumusers]),
    "MaxSumusers", MAXX(CURRENTGROUP(), [Sumusers])
  )

EVALUATE
  __DS0Secondary

ORDER BY
  [IsGrandTotalColumnTotal], 'd_date'[day name]

EVALUATE
  NATURALLEFTOUTERJOIN(
    __DS0Primary,
    SUBSTITUTEWITHINDEX(
      __DS0Core,
      "ColumnIndex",
      __DS0Secondary,
      [IsGrandTotalColumnTotal],
      ASC,
      'd_date'[day name],
      ASC
    )
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'ga_seo_general'[hour], [ColumnIndex]

I would appreciate some insight for you and am looking forward to a response. Let me know if you need further information.

 

 

Kind regards,

John

 

Feedback Type:
Frown (Error)

Timestamp:
2019-02-12T14:05:45.0862585Z

Local Time:
2019-02-12T15:05:45.0862585+01:00

Session ID:
751cb272-e77f-4017-9ac0-771fe397d88c

Release:
December 2018

Product Version:
2.65.5313.1381 (18.12) (x64)

Error Message:
OLE DB- eller ODBC-fel: [Expression.Error] Vi kunde inte vika uttrycket för datakällan. Försök med ett enklare uttryck..

OS Version:
Microsoft Windows NT 10.0.17134.0 (x64 sv-SE)

CLR Version:
4.7 or later [Release Number = 461808]

Peak Virtual Memory:
38.9 GB

Private Memory:
960 MB

Peak Working Set:
1.01 GB

IE Version:
11.523.17134.0

User ID:
34b8ad87-88c4-4574-90b9-a89ab92c8636

Workbook Package Info:
1* - sv-SE, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Model Default Mode:
DirectQuery

Snapshot Trace Logs:
C:\Users\john.isaksson\Microsoft\Power BI Desktop Store App\FrownSnapShot197881854.zip

Performance Trace Logs:
C:\Users\john.isaksson\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_variationUIChange

Disabled Preview Features:
MIntellisense
PBI_SpanishLinguisticsEnabled
PBI_PdfImport
PBI_ColumnProfiling
PBI_PythonSupportEnabled
PBI_showIncrementalRefreshPolicy
PBI_showManageAggregations
PBI_FuzzyMatching
PBI_EnableWebDiagramView
PBI_improvedFilterExperience
PBI_qnaLiveConnect

Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
125%

Supported Services:
Power BI

Formulas:


section Section1;

shared ga_seo_general = let
    Källa = AmazonRedshift.Database("skf-web-analytics.c6okg9pna554.eu-west-1.redshift.amazonaws.com:5439", "prod", null),
    us = Källa{[Name="us"]}[Data],
    ga_seo_general1 = us{[Name="ga_seo_general"]}[Data]
in
    ga_seo_general1;

shared d_date = let
    Källa = AmazonRedshift.Database("skf-web-analytics.c6okg9pna554.eu-west-1.redshift.amazonaws.com:5439", "prod", null),
    utilities = Källa{[Name="utilities"]}[Data],
    d_date1 = utilities{[Name="d_date"]}[Data],
    #"Sorterade rader" = Table.Sort(d_date1,{{"date", Order.Descending}})
in
    #"Sorterade rader";
Status: Delivered
Comments
Visitor

From the mashup log:

DataMashup.Trace Information: 24579 : {"Start":"2019-02-12T14:10:27.6909071Z","Action":"SqlTranslator/SqlParser/Parse","HostProcessId":"53944","SQL":"\nSELECT \nTOP (1000001) [t0].[users],[t1].[day name],SUM([t0].[hour])\n AS [a0]\nFROM \n((SELECT * FROM [ga_seo_general]) AS [t0]\n\n left outer join \n\n(SELECT * FROM [d_date]) AS [t1] on \n(\n[t0].[date] = [t1].[date]\n)\n)\n\nGROUP BY [t0].[users],[t1].[day name] ","ProductVersion":"2.65.5313.1381 (18.12)","ActivityId":"19a0cc82-2cca-4687-a12a-7bfc8b741970","Process":"Microsoft.Mashup.Container.NetFX45","Pid":55080,"Tid":1,"Duration":"00:00:00.0001939"}
DataMashup.Trace Information: 24579 : {"Start":"2019-02-12T14:10:27.6911659Z","Action":"DependencyCompiler/RegisterDataSources","HostProcessId":"53944","Kind":"AmazonRedshift","WasLoaded":"True","ProductVersion":"2.65.5313.1381 (18.12)","ActivityId":"19a0cc82-2cca-4687-a12a-7bfc8b741970","Process":"Microsoft.Mashup.Container.NetFX45","Pid":55080,"Tid":1,"Duration":"00:00:00.0000064"}

[...]

DataMashup.Trace Information: 24579 : {"Start":"2019-02-12T14:10:27.6911141Z","Action":"SqlExpressionTranslator/Translate","HostProcessId":"53944","IsRecognized":"True","Result":"(environment) => Table.RenameColumns(let\r\n t1617 = let\r\n t1614 = Table.RenameColumns(Table.PrefixColumns(environment[ga_seo_general], \"ga_seo_general\"), {{\"ga_seo_general.date\", \"t0.date\"}, {\"ga_seo_general.hour\", \"t0.hour\"}, {\"ga_seo_general.page path\", \"t0.page path\"}, {\"ga_seo_general.users\", \"t0.users\"}, {\"ga_seo_general.sessions\", \"t0.sessions\"}, {\"ga_seo_general.source\", \"t0.source\"}, {\"ga_seo_general.device category\", \"t0.device category\"}, {\"ga_seo_general.operating system\", \"t0.operating system\"}, {\"ga_seo_general.bounces\", \"t0.bounces\"}, {\"ga_seo_general.keyword\", \"t0.keyword\"}, {\"ga_seo_general.new users\", \"t0.new users\"}, {\"ga_seo_general.avg session duration\", \"t0.avg session duration\"}, {\"ga_seo_general.organic searches\", \"t0.organic searches\"}, {\"ga_seo_general.city\", \"t0.city\"}, {\"ga_seo_general.state\", \"t0.state\"}, {\"ga_seo_general.country\", \"t0.country\"}}),\r\n t1615 = Table.RenameColumns(Table.PrefixColumns(environment[d_date], \"d_date\"), {{\"d_date.date\", \"t1.date\"}, {\"d_date.month\", \"t1.month\"}, {\"d_date.day\", \"t1.day\"}, {\"d_date.year\", \"t1.year\"}, {\"d_date.day of week\", \"t1.day of week\"}, {\"d_date.day name\", \"t1.day name\"}, {\"d_date.month name\", \"t1.month name\"}, {\"d_date.quarter\", \"t1.quarter\"}, {\"d_date.week\", \"t1.week\"}}),\r\n t1616 = Table.Join(t1614, {\"t0.date\"}, t1615, {\"t1.date\"}, JoinKind.LeftOuter, null, {Value.NullableEquals})\r\n in\r\n t1616,\r\n t1623 = Table.Group(t1617, {\"t0.users\", \"t1.day name\"}, {{\"a0\", (t1618) => List.Sum(t1618[t0.hour])}}),\r\n t1624 = Table.SelectColumns(t1623, {\"t0.users\", \"t1.day name\", \"a0\"}),\r\n t1626 = Table.FirstN(t1624, 1000001)\r\nin\r\n t1626, {{\"t0.users\", \"users\"}, {\"t1.day name\", \"day name\"}, {\"a0\", \"a0\"}})","ProductVersion":"2.65.5313.1381 (18.12)","ActivityId":"19a0cc82-2cca-4687-a12a-7bfc8b741970","Process":"Microsoft.Mashup.Container.NetFX45","Pid":55080,"Tid":1,"Duration":"00:00:00.0133151"}

[...]

DataMashup.Trace Warning: 24579 : {"Start":"2019-02-12T14:10:27.7050015Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"53944","ErrorMessage":"Data Type of column t1.day name with searchable property UNSEARCHABLE should be SEARCHABLE or ALL_EXCEPT_LIKE. You can override the supported data types from ODBC driver using SQLGetTypeInfo.","ProductVersion":"2.65.5313.1381 (18.12)","ActivityId":"19a0cc82-2cca-4687-a12a-7bfc8b741970","Process":"Microsoft.Mashup.Container.NetFX45","Pid":55080,"Tid":1,"Duration":"00:00:00.0000106"}
Moderator

Hi @johnisaksson ,

 

I would suggest you create a support ticket to let engineers look into the pbix on your side. 

 

Support Ticket.gif

 

Best Regards,
Qiuyun Yu 

Moderator
Status changed to: Delivered