How can I connect PBI to a specific schema in Redshift?

[Anonymous]

Hi,

I'm successfully connecting my PowerBi to Redshift, however I have multiple schemas in the warehouse and because of that I'm unable to use queries in the connection. 

Is there a way to specify the actual Schema I want to connect to, so that when the connection goes through, I'll have my list of tables, instead of the list of schemas?

Thanks in advance! 

[Anonymous]

Thanks for the suggestion! It's not applicable in this case, otherwise it might actually work. 

Any other thoughts? 🙂 

[lbendlin]

Have you tried creating users that are restricted to single schemas?  Might not be practical but maybe worth a try.  (You can definitely do it in the gateway)

[Anonymous · Accepted solution]

@lbendlin figured it out! 

Step 1: Connect to the Redshift DB.

Step 2: Change the Hierarchical Navigation option to false. 
= Odbc.DataSource("dsn=Redshift", [HierarchicalNavigation=false])

Step 3: open the Source step and paste your query as follows :

SELECT * FROM DBNAME.SCHEMA.TABLE

Works 🙂 

[lbendlin]

Good to hear.  Be aware of the risk of oversharing when you specify the gateway connection user.