I haven't, but if the issue is that your account doesn't have access to the metadata for the materialized views, there are probably two options right off the top of my head:
1. Up the permissions for your user in Postgres such that it can see the metadata for materialized views (not sure what that requires...hopefully not full owner rights..)
2. Use Custom SQL to grab the columns from that one view, and join it to the rest of your tables like you would any other table. If your account does have rights to read from the view, that should also work...but it'll be less efficient than the option in (1).
thanks Matt. It doesn't seem to be a permissions issue as we can connect and see the MV's via other tools such as dbviz. Just not Tableau Desktop. Do you have an opinion as to whether custom sql or db views are better? We've taught our analysts to avoid custom sql, so for right now we've created views that point at the MV's.
Also, the mentioned KB article (in the original post) references this in 2014, I was hoping there might be an update to this or at least a better workaround.
1 of 1 people found this helpful
I looked into it a bit on the postgreSQL side, and it seems that materialized views aren't stored in the INFORMATION_SCHEMA, which all other objects are. They're more a "custom" object that Postgres uses. I think the metadata could be obtained in other ways, and perhaps our connection could be modified to look at the custom PostgreSQL object metadata to pull those in...my guess is that it wasn't done because it's not standard, and therefore harder to maintain. But for sure put an Idea in and I'll upvote it with you!
As to which is best? I'd probably just go with Custom SQL. I'm not sure the first approach will help anyway, and you can somewhat minimize the Custom SQL cons if you're careful about how you use it--namely, select only the columns you'll need, ONLY use Custom SQL for that one view, and keep the rest of your Tableau connection standard.
Even if the information is not available in the information_schema, this is not an unreasonable request. Listing the objects which you have access to query makes for a better user experience anyway. Moreover Tableau could get this information directly from the data dictionary (and only list those objects that the logged on user has 'select' privileges to) with the following query:
# Retrieve list of tables, views and materialized views
# to which your account has select privilege specifically.
# relname = the name of the table
# nspname = the name of the schema
# r = relation (ie: table)
# v = view
# m = materialized view
# The “has_table_privilege” call filters out those
# objects to which the user has “select”
# privilege granted
SELECT n.nspname, c.relname
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
WHERE c.relkind IN ('r', 'v', 'm')
AND n.nspname NOT IN('pg_catalog', 'information_schema', 'pg_toast', 'pg_temp_1')
AND has_table_privilege(c.oid, 'select')
AND n.oid = c.relnamespace
ORDER BY n.nspname, c.relname