Connecting Redshift Spectrum to PowerBI

[MarcSitges]

Hi all,

We are looking ways to connect PowerBI to AWS S3 buckets and one of the solutions we are analyzing is to use Redshift Spectrum (rather than AWS Athena).

However, I've been searching if this connection is possible and I've only saw this message from 2018: Re: Error in accessing redshift spectrum (external... - Microsoft Fabric Community

What I understand is that connection between PowerBI and Redshift Spectrum will only work if tables are previously stored in Redshift DB.

Is this still correct?

Thanks!

[JoeBarry]

Hi @MarcSitges 

I connect using the ODBC connector. You will need to configure the ODBC connection.

You probably have already, but download and install the ODBC driver for Athena

https://downloads.athena.us-east-1.amazonaws.com/drivers/ODBC/v2.0.1.1/Windows/AmazonAthenaODBC-2.0.... 

There are some configuration articles here https://docs.aws.amazon.com/athena/latest/ug/odbc-v2-driver-getting-started.html 

Here is how I setup everything

• Install the driver
• Open ODBC data Sources in Administration mode
• Click on Add 
• Find the Athena Driver and click on Finish
• You will me by a pop up
• Give The connection a name
• Enter the AWS Region
• The rest you fill out like in the screenshot that suits your comnpany's connection configuration 
• Click on test to see if it works
• if it does go to Power BI
• Open the ODBC connector
• Choose from the DSN dropdown, the connection you just created
• You can also do an SQL query if needed.
• You will be then promted to add the credentials

If you want to refresh the source automatically, you will need a Power BI Gateway

Setting up the credential part of the gateway should look like this 

Hope this helps

Thanks

Joe

If this post helps, then please Accept it as the solution

[MarcSitges]

Hi @JoeBarry ,
I do not understand, is this a connection for Athena (which we know how to do it) or for Redshift Spectrum? I'm looking for the second.

Thanks,

Marc

[JoeBarry]

Sorry @MarcSitges 

I misread, i think it's a similar setup as i explained here is the documentaion and driver links https://docs.aws.amazon.com/redshift/latest/mgmt/configure-odbc-connection.html 

[MarcSitges]

Yes, here is the connectivity with Redshift although my suspition is that the connection with Redshift Spectrum only works when datasources from Redshift Spectrum comes from Redshift DB, not in the case of coming directly from S3.
I would need some confirmation on this last thing.