ODBC Error - "no matching column found" with Redshift - started 10/15

[ScottBeal]

We just started receiving an error in Desktop when attempting to load a table from Redshift.

Everything was fine last week.  Went to do some updates today, 10/15, and starting receiving the error.  It seems to be happening on about 25% of our DW tables.

The error first happened when trying to refresh data in 'Power Query Editor' on existing datasets.

Clearing cache and an uninstall and reinstall of desktop did not help.  Neither did creating a dataset from scratch and choosing a table in the Navigator box.

The error is: (I replaced our url with @ in this message)

DataSource.Error: ODBC: ERROR [07009] [Microsoft][ODBC] (10550) Invalid result set column number: no matching column found.
Details:
DataSourceKind=AmazonRedshift
DataSourcePath=@@@@@@@@@@@@@@@.redshift.amazonaws.com:5439;rwdw
OdbcErrors=[Table]

Any help is appreciated as this is impacting our ability to update production reports/datasets.

Thanks in advance!

[frostystoo]

So it seems that due to an earlier than usual December release and also upcoming holidays, it is unlikely that this fix won't be introduced until February 2020. I'm going to ask our Account Manager to see if there is anything that can be done to bring this forward, I'd recommend anyone else in the same position to do the same.

[Anonymous]

Seems to be resolved in December release. 

[frostystoo]

I have some better news to report on this. I had time with MicroSoft support over the past couple of weeks and on Friday I successfully tested a Power BI Desktop build they supplied where this issue seems to have been fixed. Now waiting on confirmation when this fix might be bundled into the main branch, I am hoping for the mid December release.

[Anonymous]

Hi guys,

Same problem here. We have been having a lot of problems with direct query and Redshift, I've had to stick with September update of Desktop and rebuild the dataset many times and it solved the problem until now. 

I'm having the exact same problem than you so we will try a new model without FK and see what happens.

Anyway like @Anonymous said, it seems like the only solution here is we either switch the DWH or the BI tool...

Waiting for any update.

Thanks!

[Anonymous]

Just tested this with the November release, and Redshift is still not working. 

[frostystoo]

Had an update from MicroSoft who are still looking into this. I helped them confirm that the issue seems to relate to all Redshift tables which contain foreign keys. So I guess as a short term fix, you could remove foreign keys from your databases.

[ScottBeal]

--Quick Update--

After working with MS support, the first impression is that there is an issue in the October Power BI release with the native connection to Redshift.

Previous Power BI versions do not exhibit the issue.

Connection through local ODBC driver works without any issues.

[frostystoo]

We are experiencing this issue too, since October Power BI update. Is there an issue thread we can follow?

[ScottBeal]

@frostystoo I created a support (service) request through MS support.  I do not have any thread that is "followable".
I was on with them this morning.  The product team is still looking at the issue.

They provided a link to the page with previous version links: https://docs.microsoft.com/en-us/power-bi/desktop-latest-update-archive#september-2019-update-273558...

However the September versions are not available (404 error).

You can download July and/or August which didn't exhibit the Redshift issue.

If they provide a way to get the September version I will reply here with the link.

[frostystoo]

Thank you @ScottBeal  update much appreciated. This is a significant issue for us as most of our certified datasets use an AWS Redshift data mart and this paralyses our development activities. I am part of a large organisation and a big user of Power BI so I'll try and apply pressure from our contacts in MicroSoft.

[ScottBeal]

@frostystoo 

Much appreciated.

I happen to be on with them now.  They are requesting some more logs.

If it helps someone on their side at all, the support case # is 119101624002337.

(I am not sure if this is a microsoft number or something at a third party handling the support)

We are in the same boat with this issue (and the fact that the September release is not available on the website) completely stopping any development - new and maintenance - from being done.  It has been a week and is growing in terms of impact and visibility here.

Thanks again.

[Anonymous]

Same issue here. Luckily I have a snapshot of PowerBI before the upgrade (thx to Time Machine). 

It is immediatly clear, that this is an issue with the lastest PowerBI version. I've had already many issues with PowerBI and Redshift, even in production, end of last year it failed for more than a week (see here: https://community.powerbi.com/t5/Service/Direct-Query-connectivity-issues-with-Redshift-in-the-last-...). 

I've kind of given up. I had an exchange with PowerBI support, and the support team presumably with the engineering team, and they don't consider DAX formulas designed for DirectQuery, which are not working with Redshift, a bug, but a 'missing feature'. In case you are interested, try to use this formula with Redshift: https://docs.microsoft.com/de-de/dax/combinevalues-function-dax. Doesn't work at all...

Thanks for taking the time to report this to PBI support. I know how time consuming that is....

It looks to me like Redshift support got implemented two years ago and is not maintained anymore. The 'solution' probably is to switch the DWH or the BI tool. 

[frostystoo]

I hope Redshift support is still maintained as I am designing a whole IT managed self-service delivery with certified datasets to 10s of 1000s of users around it 😮 They added Redshift to the Power BI on premise data Gateway only a few months ago, so fingers crossed this is just a temporary glitch. I've pinged my contact in MS support again to try and find an update.

We use Import rather than Direct Query at the moment and so far no issues with the DAX or anything else until this one.

[Anonymous]

Well, I don't think that support will be officially dropped, and if you are using import mode, you can always use 3rd party drivers. But clearly from my experience MS doesn't make any effort to ensure features added in the past 2 years are working with Redshift, quite to the contrary. Does import mode scale in your organisation? We were having tables with a little more than 10 mio rows and only integer/decimal data fail to load into Redshift using the built-in ODBC driver. 

[ScottBeal]

We found a link to the August version of Power BI and installed it.  Accessing the database works without error.

Based on this and the timing of the error starting after Power BI October update, I am believing that something in the October release is the culprit.

I have opened a support ticket with MS and will work through that channel, and update here with what we find out.

[Anonymous]

When you will change the data source related to the PBIX file.You need to select the Tables again in the edit query.

as below.

and check if any query/step has an error or any step is added before remove other columns step.

Error is due to (column name may be changed and while removing unwanted columns that column name is not found.)

[ScottBeal]

Thanks @Anonymous 

It didn't help.  that was one of the first things we looked at.

Since we couldn't figure it out we decided to reinstall Power BI and try to create a new dataset (pbix).

When we select a table from the redshift table list it throws the error at that point.

[Anonymous]

Check below 2 solutions.

1)ref  below Url 

https://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#07009

Check the contacts table you are fetching.

2)After reinstalling, Please create new PBIX file from scrach, Do NOT use the existing PBIX file for avoiding version issue

Hope it will help you...

[venal]

@ScottBeal 

Hoping it is related to ODBC.

Please refer the below links.

https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/appendix-a-odbc-error-codes?view=sql-...

https://www.easysoft.com/developer/interfaces/odbc/sqlstate_status_return_codes.html#07009

[ScottBeal]

@venal 
Thanks.

I looked at the ODBC error codes earlier.

Since Redshift is cloud based and connection is made "in PowerBI", I don't think we have much control over the actual ODBC connection to address any of the issues mentioned in the articles. 
(short of reinstalling ODBC in windows if that is what it is using and if that is possible)