How to make Microstrategy to work with user schema in Redshift / psql tables?

Question

I use Amazon Redshift with MIcrostrategy. In Microstrategy i can see properly all tables from public schema. When I created my own schema, however, Microstrategy "sees" the tables, but does not see the fields inside them (i.e. when I click on a table, Microstrategy does not show the fields inside).

Any ideas what may I be doing wrong / how to configure Microstrategy to work with a schema other then "public"?

Thank you!

Answer 1

You probably need to add your schemas to the SEARCH_PATH for the user that Microstrategy is using to access the database.

If someone besides that user has created the tables then you may also need to GRANT access to the Microstrategy user.

Note: I do not use Microstrategy, this advice is based on issues I've seen with Tableau.

Answer 2

In Amazon Redshift, you have to set the table schema prefixes in the database instance and it worked for me. Below are the steps to make MicrosStrategy 9.4.1 connect to Redshift.

1. Specify Redshift schema name under Database instance > Advamced > Table Space name
2. Give Table Space Name / Schema name at Warehouse Catalog > Options > Catalog > Custom Database login > select > select your login
3. Warehouse Catalog > Options > View > Table Prefixes > Check Automatically.. + Select Set default prefix + Select default prefix from drop down
4. Warehouse Catalog > Options > View > Table Name Spaces > Check Display the name space for each table

After these steps, the data model should show columns for selected tables.