postgresql - PowerBI 直接查询连接到 PostgreSQL 错误。 OLE 或 ODBC 错误 : [Expression. 错误] 我们无法将表达式折叠到数据源

Question

根据Microsoft DataConnectors的信息我想通过 this ODBC driver 创建一个从 PowerBi 到 PostgreSQL 的连接器使用直接查询。我重用了 Microsoft sample 中的代码，只是调整了ConnectionString，没有别的。构建导入到 PowerBI 的 .mez 文件后，我想连接到 PostgreSQL 服务器。 This is how the connection dialog looks like .连接成功，我可以看到数据库中的表并使用它们。现在，问题出在这里:当我选择一些列来显示表格或绘图中的数据时，我得到 OLE or ODBC error: [Expression.Error] We couldn't fold the expression to the data source .我还在诊断选项中启用了跟踪，所以这里是日志内容:

DataMashup.TraceWarning: 24579:    "Start": "2018-05-18T10:51:56.6199845Z",    "Action": "OdbcQuery/FoldingWarning",    "HostProcessId": "25020",    "Function Name": "Group",    "ProductVersion": "2.58.5103.501 (PBIDesktop)",    "ActivityId": "f06a4b5b-09ba-40ce-bd99-424710286b77",    "Process": "Microsoft.Mashup.Container.NetFX40",    "Pid": 11080,    "Tid": 1,    "Duration": "00:00:00.0000051"DataMashup.TraceInformation: 24579:     "Start": "2018-05-18T10:51:56.6199552Z",    "Action": "BackgroundThread/RollingTraceWriter/Flush",    "ProductVersion": "2.58.5103.501 (PBIDesktop)",    "ActivityId": "00000000-0000-0000-0000-000000000000",    "Process": "Microsoft.Mashup.Container.NetFX40",    "Pid": 11080,    "Tid": 8,    "Duration": "00:00:00.0000560"DataMashup.TraceWarning: 24579:     "Start": "2018-05-18T10:51:56.6199999Z",    "Action": "OdbcQuery/FoldingWarning",    "HostProcessId": "25020",    "ErrorMessage": "This ODBC driver doesn't set the GroupByCapabilities feature. You can override it by using SqlCapabilities.",    "ProductVersion": "2.58.5103.501 (PBIDesktop)",    "ActivityId": "f06a4b5b-09ba-40ce-bd99-424710286b77",    "Process": "Microsoft.Mashup.Container.NetFX40",    "Pid": 11080,    "Tid": 1,    "Duration": "00:00:00.0000159"DataMashup.TraceInformation: 24579:     "Start": "2018-05-18T10:51:56.6200385Z",    "Action": "BackgroundThread/RollingTraceWriter/Flush",    "ProductVersion": "2.58.5103.501 (PBIDesktop)",    "ActivityId": "00000000-0000-0000-0000-000000000000",    "Process": "Microsoft.Mashup.Container.NetFX40",    "Pid": 11080,    "Tid": 9,    "Duration": "00:00:00.0000215"DataMashup.TraceWarning: 24579:     "Start": "2018-05-18T10:51:56.6201305Z",    "Action": "OdbcQueryDomain/ReportFoldingFailure",    "HostProcessId": "25020",    "Exception": "Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.FoldingFailureException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Folding failed. Please take a look the information in the trace.\r\nStackTrace:\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.Group(Grouping grouping)\r\n   at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)\r\n\r\n\r\n",    "ProductVersion": "2.58.5103.501 (PBIDesktop)",    "ActivityId": "f06a4b5b-09ba-40ce-bd99-424710286b77",    "Process": "Microsoft.Mashup.Container.NetFX40",    "Pid": 11080,    "Tid": 1,    "Duration": "00:00:00.0000504"DataMashup.TraceInformation: 24579:     "Start": "2018-05-18T10:51:56.6202107Z",    "Action": "BackgroundThread/RollingTraceWriter/Flush",    "ProductVersion": "2.58.5103.501 (PBIDesktop)",    "ActivityId": "00000000-0000-0000-0000-000000000000",    "Process": "Microsoft.Mashup.Container.NetFX40",    "Pid": 11080,    "Tid": 8,    "Duration": "00:00:00.0000154"DataMashup.TraceWarning: 24579:     "Start": "2018-05-18T10:51:56.6199413Z",    "Action": "RemotePageReader/RunStub",    "HostProcessId": "25020",    "Exception": "Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.ValueException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.\r\nStackTrace:\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryDomain.ReportFoldingFailure(NotSupportedException ex)\r\n   at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)\r\n   at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query)\r\n   at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)\r\n   at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query)\r\n   at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.Optimize(Query query)\r\n   at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.get_OptimizedQuery()\r\n   at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.GetReader()\r\n   at Microsoft.Mashup.Engine.Interface.Tracing.TracingDataReaderSource.get_PageReader()\r\n   at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.c__DisplayClass11.c__DisplayClass13.b__10()\r\n   at Microsoft.Mashup.Evaluator.RemotePageReader.c__DisplayClass7.b__0()\r\n   at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)\r\n\r\n\r\n",    "ProductVersion": "2.58.5103.501 (PBIDesktop)",    "ActivityId": "f06a4b5b-09ba-40ce-bd99-424710286b77",    "Process": "Microsoft.Mashup.Container.NetFX40",    "Pid": 11080,    "Tid": 1,    "Duration": "00:00:00.0005557"

关于如何解决这个错误有什么想法吗？谢谢

Answer 1

我今天第一次使用直接查询存储模式连接到 PostgreSQL，也遇到了这个错误。与您一样，连接和其他一切似乎都运行良好，但在尝试使用在使用导入存储模式时运行良好的视觉效果时出现错误。

我的错误是:OLE DB 或 ODBC 错误:[Expression.Error] 我们无法将表达式折叠到数据源。请尝试更简单的表达式。我假设最后一句是在更高版本的 Power BI 中添加的。

Carl de Souza 解释查询折叠 here并且 Direct Querying 似乎试图将尽可能多的分析折叠到 Native Query 中。第一次提取数据时我没有指定任何查询，但一旦我这样做，错误就消失了。

对于不知道如何执行此操作的任何人，在单击“获取数据”(如果已经在查询编辑器中，则单击“新源”)并选择 PostreSQL 数据库后，确保在高级选项中添加 SQL 语句部分。

对我来说，一个简单的 SELECT * FROM my_schema."my_table_name"就足够了。

"Sample filling out of the PostgreSQL database popup