2 Replies Latest reply on May 9, 2013 5:45 AM by Siraj Samsudeen

    How to create multiple custom queries from a single DB connection?

    Siraj Samsudeen

      Generally, in any BI tool that I have used so far, one creates a connection to database (DB) and then one can define/run multiple queries against that connection - these queries can be direct tables/view or custom queries. But in Tableau, when I created a connection, I am allowed to either bring a number of tables together or I can type only one custom query. Does that mean that I have to create a number of connections with the same information just to bring different custom queries into Tableau? 


      I am bringing in data from Oracle ERP for reporting, each of the custom queries is nothing but one logical table and there are relationships to be defined between these tables. How do I define the relationships between these different tables which are brought through different queries?


      Someone in the Tableau sales team told me that I have to create a view for each of these queries. But that is just going to make the process very difficult as I need to contact the IT team to create these views. Our idea is to quickly explore the data by bringing in the data that we want. If we go through the route of views, then for each new piece of data that I want to bring in, I have to modify the view and wait for IT to schedule the update of the view. Sounds horrible. Are there any alternatives?

        • 1. Re: How to create multiple custom queries from a single DB connection?
          Russell Christopher

          Hi Siraj -


          As you've discovered, Tableau's metadata layer is different (purposefully so)  than other tools you may have worked with. Most (non-technical) users of our tool don't frankly even understand what a metadata layer is, and don't want to write custom queries like you do.


          So, we give them a way to get at their data easily and quickly without needing your type of skills.


          The upshot of that is that you can't "relate" multiple queries like you might in a BOBJ Universe - the BOBJ sort of approach is IT-only.  Instead, you need to encapsulate each query into a view and then knit the views together in Tableau as if they were tables.


          ...or write a single query in Oracle that JOINs a bunch of sub-selects (each sub-select being one of your queries) and use that in Tablea


          ...or, you might simply create a materialized view in Oracle which does all the work FOR you ahead of time and simply bring that one "big" view into Tableau


          ...or Tableau's "blending" feature might allow you do join multiple data sources together:





          Good luck!

          • 2. Re: How to create multiple custom queries from a single DB connection?
            Siraj Samsudeen

            Thanks a lot Russel for the answer and the detailed explanation of the possibilities to resolve my issue in Tableau. Unfortunately, none of them really resolves my problem as I am taking data from the monster oracle ERP with its spiderweb of tables. 


            Given that the speed and independence from IT are given so much importance in Tableau, I feel that there is an inconsistency here - creating views or materializing them lead to long cycle times and going back to IT, thus essentially killing the advantages of quick development cycle with tools like Tableau. I respect the decisions taken by Tableau to hide the complexity of metadata layer, but I do want to point out a few inconsistencies in how this complexity is hidden.


            When I open a data import, I am presented with options to join and I am given option to select whether it is a left/right/full outer join - Do you really think that an average business users understand this? To me, a simple UI to create relationships like PowerPivot gives, no matter whether a given set of tables came from the same data source or not, will simplify the overall concept and will also make it much more powerful.


            I am not really comparing Tableau's metadata layer philosophy with Business Objects. But even tools like PowerPivot which does not expose any complexity of metadata layer to the user allow you to have multiple queries. To me, it seems logical that we create a connection and then we can use the connection to import one more tables or queries. All the BI tools that I have worked with so far have this philosophy. Creating a connection and doing table import in a single step and not being able to reuse the connection is not a very good design and can be improved upon.


            Also, given that Tableau gives lot of power in terms of Table calculations (using terms like addressing and partitioning) which an average user does not understand and only power users use it, I expect that a similar hidden power is available in the data import arena as well.


            I have just started looking at Tableau very recently and I am in love with the user-friendliness of the tool in a number of ways. What would take me a few minutes to set up in PowerPivot or Qlikview takes me a few seconds to do it in Tableau - I really think that this is the best business-user-friendly BI tool that I have seen so far. But there are a number of areas where it falls short when it comes to data import/massaging and looking at the feature requests and forum questions, it is clear that joins/blending is an area where lot of people want to have more flexibility. I request that Tableau team considers giving more power to the users in the data import/integration area.