5 Replies Latest reply on Nov 22, 2012 5:59 AM by Tamas Foldi

    Connecting to a Database with Many (25000) tables/views in a schema

    David Edwards

      I am trying to connect Tableau to our Oracle eBusiness database but I am running into some problems because there are so many objects available to my user.

       

      Specifically, there are approximately 25000 views and 16000 tables in the APPS schema, and tableau is simply not willing to render them all in the table selection window.

       

      Is there an upper limit on what Tableau is willing (or able) to put into this scrolling window?

      Am I forced to write my own Custom SQL in order to hit the tables I want which are not available in this list?

       

       

      How have other users that connect Tableau to Oracle eBusiness work around this problem, or even more generally, what methods do you use to connect them together?

       

      Understandably our IT department is quite reluctant to open the flood gates since this application is extremely complex, but they are also too busy working on other things to offer much assistance as far as building views, extracts or data warehouse objects.

       

      Thanks!

        • 1. Re: Connecting to a Database with Many (25000) tables/views in a schema
          Tamas Foldi

          I have very similar problem with Tableau on Greenplum. First of all, you should be able to select the schema in greenplum database just like in case of oracle - but Tableau simply does not have the schema selector for greenplum. I really hate it since I have also thousands of table...

           

           

          ...because of an another Tableau problem. Tableau recognizes Greenplum table partitions as regular tables. As I have many fact tables with hundreds of partitions practically I cannot use a table selector.

           

          Tableau guys, here are some things to improve.

          • 2. Re: Connecting to a Database with Many (25000) tables/views in a schema
            Robert Morton

            Hi David,

             

            Thanks for this feedback. Would an interface such as the one present in the Teradata connection dialog help, where you can filter the table list by typing in a portion of the table name?

             

            -Robert

            1 of 1 people found this helpful
            • 3. Re: Connecting to a Database with Many (25000) tables/views in a schema
              Robert Morton

              Tamas, this is interesting feedback about Greenplum partitions, and it's the first I've heard of this problem (though others at Tableau may be aware). Does Greenplum also expose a given partitioned table in a form that represents the single, logical table of data? If so, do you always work with a single logical table, or do you sometimes connect directly to a specific partition?

               

              Thanks,

              Robert

              • 4. Re: Connecting to a Database with Many (25000) tables/views in a schema
                David Edwards

                Robert,

                 

                  Thanks for your timely response.

                 

                  I don't have personal experience with the Terradata dialog window, but what you describe definitely sounds like it would solve the problem. For the most part I know the tables that I'm trying to connect to whenever I'm going into a database.  Being able to type them in instead of selecting from the window would be a good improvement.

                 

                  As is, there is no way for me to see the tables that do not show up in the list.

                  I also can't quite make out what it is that decides what is displayed.  For example, it is not merely alphabetical.

                • 5. Re: Connecting to a Database with Many (25000) tables/views in a schema
                  Tamas Foldi

                  Tamas, this is interesting feedback about Greenplum partitions, and it's the first I've heard of this problem (though others at Tableau may be aware).

                   

                  Let me just suggest one thing. Tableau currently executes the following SQL the obtain tables and schema:

                   

                  select relname, nspname, relkind from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relkind in ('r', 'v') and nspname not in ('pg_catalog', 'information_schema', 'pg_toast', 'pg_temp_1') and n.oid = relnamespace order by nspname, relname

                   

                   

                  However, it should be this:

                   

                  select relname, nspname, relkind from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relkind in ('r', 'v') and nspname not in ('pg_catalog', 'information_schema', 'pg_toast', 'pg_temp_1') and n.oid = relnamespace AND c.oid NOT IN (select parchildrelid from pg_partition_rule)  order by nspname, relname

                   

                   

                  Does Greenplum also expose a given partitioned table in a form that represents the single, logical table of data? If so, do you always work with a single logical table, or do you sometimes connect directly to a specific partition?

                   

                  It is quite unlikely, since you can achieve the same result by filtering the table on the desired conditions. Partition elimination should be done by database engine, and not by me, according my criteria (defined as filter condition). In case of Oracle Tableau also hides the partitions, thus I guess the same behaviour should apply here.

                   

                  If there is a schema selector for a database, then I think it would be useful for the other databases as well. But sometimes I have a feeling that the guys who are developing the connection windows have never tried / see the other connection windows. Also, for Oracle, Greenplum and MS SQL the same on connection executed statement will be useful just in case of teradata. The firstly executed statement can set the audit parameters for the session (like the tableau user behind the database user), and it could be used almost everywhere. What is the point to limit this functionality to teradata?

                   

                  The best would be is to try to standardize the features and connection options for database connections

                   

                  Thanks