sgoley / directquery-for-odbc-in-powerbi

Direct Query for PostgreSQL via ODBC with SSL

Hi there 👋

• 🔭 Currently building data products around the future of 🏘️residential investment with Avenue One. Previously, I worked on virtual 💳 payment services at Finexio, Healthcare Marketing at ARS as well as various other projects, personal and professional!
• 📚 I'm currently learning terraform, BigQuery, dbt, Looker, and more!
• 👯 I'm looking to collaborate on projects related to data infrastructure and architecture abstraction!
• 🎓 I'm Salesforce certified both as an Administrator (ADM201) and as a Heroku Architecture Designer.
• ❄️ I'm Snowflake certified for SnowPro Core (COF-C02) as of 2023.
• 💬 Ask me about data stacks! 🚢SHPPT stack: (https://github.com/sgoley/SHPPT-Data-Stack)
• ☁️ My current favorite cloud platform is GCP (https://cloud.google.com/)

directquery-for-odbc-in-powerbi's Issues

MySQL DQ Counln't load the data for this visual

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

I just tried to put columns on a table visual.

Any help?

Attempt connection with open access (No login / Auth) ODBC source

• Create ODBC datasource (ex. Local Postgres with "Accept all local connections")
• Create ODBC DSN pointing to data source
• Attempt connection / Direct Query to source

error when accessing timestamp without time zone

We are getting an error when accessing timestamp without time zone columns.

Some details:

September 2021
Product Version:
2.97.801.0 (21.09) (x64)
Error Message:
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..
OS Version:
Microsoft Windows NT 10.0.19042.0 (x64 en-US)
CLR Version:
4.7 or later [Release Number = 528372]

It works nicely with other data types, including timestamp with time zone.
My guess would be there is a no defined conversion for timestamp without time zone type, while there is one for timestamp with time zone.

Fork for compatibility with major ODBC drivers

• MySQL
• SQL Server
• PostgreSQL

SQLite?

Compatibility for MySQL

@sgoley

sure, thank you for your answer!!

Database: MySql Community Edition
ODBC Driver: MySql ODBC 8.0 Unicode Driver
DataType Field: Int
Visual: Table

Decsription: when i drag the int column it is ok (but it is sum aggregated by deafult), but when i check the "dont summerize" in power bi i get that error.

i cant see anything that is not aggregated.

Originally posted by @dr0rRvali in #1 (comment)

PostgreSQL Bug - new mashup engine breaks directquery connector

Tracking a new issue where Custom Connector works correctly with old reports (regardless of PowerBI application version) but not with newly created reports.

Reproduce steps:

• Create an ODBC Data Source (64bit) as normal.
• Add Custom Connector .mez file to Documents\Power BI Desktop\Custom Connectors as normal
• Create new PowerBI Report
• Create new blank query
• Copy contents of M source language from old, working report into new query through advanced query.

let Source = ODBCPostgresDirect.Database("Retro SF PG"), account_salesforce_d8b832js2t307u = Source{[Item="account",Schema="salesforce",Catalog="d8b832js2t307u"]}[Data] in account_salesforce_d8b832js2t307u

• Observe that preview correctly loads data for preview in Power Query Editor.
  

• Close and apply query changes.

• Resulting bug:
  

PostgreSQL Date / Date Intervals

In latest version of connector, driver is not supporting conversion of SQL_TSI_DAY operator to pgsql compatible dateadd / datediff format. Operator is being interpreted as column name.

Lines 400->450 of the following log:
mylog_Microsoft_17292.log

I have raised this as an issue with the psqlODBC project mailing list - https://www.postgresql.org/list/pgsql-odbc/

Geting error during connecting to db without ssl

Error:

Details: "ODBC: ERROR [08001] server does not support SSL, but SSL was required"

Add Query Support

• Develop ability to pass direct SQL Queries to ODBC Source.

Submit for Microsoft 'Trusted Third Party connector’ program

Ensure project meets all requirements per:
https://docs.microsoft.com/en-us/power-query/connectorcertification?branch=master

And submit for verification / microsoft distribution according to upcoming 'Trusted Third Party connector' program.

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

MySQL Dev - Issue tracking associated with MySQL Fork

MySQL in dev phase - nothing currently working.