I am busy working with a customer and had a challenge when using Azure Analysis Services to connect to Amazon Redshift via an ODBC connection.

The first issue that I encountered was the following error: OLE DB or ODBC error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application; AWS PROD. This lead me to a few websites and the one that got me to my solution was Tabular: Error while using ODBC data source for Importing Data

Below are the steps on how I installed, configured and got the connection and refresh working.

The details below should also work for other ODBC data sources too.

NOTE: The first thing I had to do was to make sure I had installed both the 32bit & 64bit ODBC drivers for Redshift

I downloaded it from the following location: https://docs.aws.amazon.com/redshift/latest/mgmt/install-odbc-driver-windows.html

Configuration of 32bit ODBC Connection

Below are the steps to configure the 32bit ODBC Connection.

  • I went into my ODBC Data Source Administrator (32bit)
  • I then clicked on System DSN
    • I could then see my existing ODBC drivers installed
  • I then clicked on Add
    • On the Create New Data Source window I selected Amazon Redshift (x86), then clicked Finish
  • This then opened the Amazon Redshift ODBC Driver DNS Setup
    • I then put all the required connection settings
  • I then clicked on Test to make sure it could connect as expected.
  • I now completed creating the 32bit ODBC Connection to Redshift, and I could see it in the ODBC Data Source Administrator (32bit)

Configuration of 64bit ODBC Connection

In order to configure the 64bit ODBC connection, I did the following steps below.

  • I went into my ODBC Data Source Administrator (64bit)
  • I then clicked on System DSN
    • I could then see my existing ODBC drivers installed
    • NOTE: I could see my existing 32bit connection I created in the steps above.
  • I then clicked on Add
    • On the Create New Data Source window I selected Amazon Redshift (x64), then clicked Finish
  • This then opened the Amazon Redshift ODBC Driver DNS Setup
    • I then put all the required connection settings
    • NOTE: It is essential that I named the Data Source Name with the same name as the 32bit Name
  • I then clicked on Test to make sure it could connect as expected.
  • I now completed creating the 64bit ODBC Connection to Redshift, and I could see it in the ODBC Data Source Administrator (64bit)

Conclusion

Now that I had created both the 32bit and 64bit ODBC connections, not only was I able to process and use my Visual studio instance, I was also able to successfully process tables and deploy it to my AAS instance.

Just to re-iterate that I had to make sure that both my 32bit & 64bit ODBC Names had the identical name. The reason is because when creating the data source in Visual Studio (Azure Analysis Services editor) it references the name of the ODBC System DSN.

If you have encountered this or got any suggestions, please let me know in the comments below.

Thanks for reading!