5 Replies Latest reply on Apr 24, 2018 2:37 PM by Fernando Luna

    Tableau select against Postgres Materialized views

    Jeff Strauss

      Run into this problem yet?  I just did (even with version 10.5).  Any thoughts on the best workaround?

       

       

      PostgreSQL Materialized Views Do Not Appear in Table List | Tableau Software

        • 1. Re: Tableau select against Postgres Materialized views
          Matt Coles

          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).

          • 2. Re: Tableau select against Postgres Materialized views
            Jeff Strauss

            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.

            • 3. Re: Tableau select against Postgres Materialized views
              Matt Coles

              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.

              1 of 1 people found this helpful
              • 5. Re: Tableau select against Postgres Materialized views
                Fernando Luna

                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

                # relkind:

                # 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