8 Replies Latest reply on Oct 19, 2017 5:46 PM by Tom W

    Importing multiple (unknown number) unrelated tables programmatically

    Ben Hagan

      Hi,

       

      I am developing a web data connector that connects to a REST based API. The api returns a complex JSON object that contains multiple values that I wish to import to Tableau as multiple tables.

       

      After doing some testing it appears I can create multiple tables within a single connection, however Tableau then forces you to create a join strategy between the tables. For my data, these tables are unrelated and hence a join strategy is impossible.

       

      So it appears the only strategy is to create multiple connections, 1 for each table. This would be very inefficient and clunky for the user as they would request the same data n times and select which table to import.

       

      Im assuming the solution is then to programmatically create multiple connections where I can then build a single table for each.

       

      Can anyone advise if this is possible or point me to an example?

       

      Regards, Ben.

        • 1. Re: Creating multiple connections programmatically
          Tom W

          If they are completely unrelated then you are correct, they need to be separate connections within Tableau. That means calling the web data connector once for each connection.

           

          Before I suggest "solutions": what do you hope to achieve by programatically creating multiple connections? And how do you anticipate that workflow would work?

          • 2. Re: Creating multiple connections programmatically
            Ben Hagan

            Hi Tom,

             

            My requirement is to simply take a JSON object, create multiple tables from it and import in to Tableau. As noted the tables are unrelated and hence no join strategy should be imposed.

             

            Multiple connections etc is simply a suggestion from me on an approach. In an ideal world I would have a single connection with multiple tables. It appears I can do this but am then forced to define joins.

             

            Appreciate any thoughts on strategy.

             

            All the best, Ben.

            • 3. Re: Creating multiple connections programmatically
              Tom W

              You definitely need multiple connections, as per my previous post. The reason is, Tableau needs the relationship in order to flatten out the data into one big flat table. Without a relationship you cannot use the data in a related manner and thus it doesn't make sense for it to be self contained within the same connection in Tableau (even though it may seem that way because you only want to hit the server once).

               

              Each of those connections need to be defined individually, one by one in the workbook.

               

              So knowing this, I'm curious what the reasoning is behind creating multiple connections programatically? This is the piece I'm trying to understand. I.e. would the requirement for one unrelated table per connection force you to create lots of individual connections and you'd hope to automate that?

              If it were me I'd create my connections, then export those as a TDS file so end users can easily add a pre-defined connection into their Tableau Desktop session.

              • 4. Re: Creating multiple connections programmatically
                Ben Hagan

                Many thanks Tom.

                 

                would the requirement for one unrelated table per connection force you to create lots of individual connections and you'd hope to automate that?

                Correct. My goal is to automate the import process so that the user has zero interaction during this process. The web UI will have controls to select the appropriate data from the remote API. Once selected, I would simply like to import the result set in to Tableau without the user having to do anything.

                 

                Perhaps I have titled this thread incorrectly; im looking for a solution to programmatically do the import. Being new to Tableau development I have no idea if creating multiple connections is correct or possible programatically. Im just looking for a best practice solution to import multiple separate tables with no user interactivity.

                 

                Regards, Ben.

                • 5. Re: Creating multiple connections programmatically
                  Tom W

                  You have the ability to save data sources - Save Data Sources

                  So you could setup the multiple connections yourself in Tableau, save the datasources then share those sources with end users and have them use them in their workbook. If that's not complete enough, you could create a workbook which already has the connections made within the workbook and all they have to do is refresh (If both / all your WDC's require user input though, you're going to have to prompt the user for that each time).

                   

                  More advanced - you could look at the Document API to define and change the datasources - Developer Portal . This would still require a process external to Tableau though for the workbook creation, then the refresh of the WDC's within Tableau itself (as above on the multiple WDC's prompting for user input).

                   

                  If you want something 100%, end to end, prompt the user once only, you're going to have to do something like this;

                  • Create a desktop app / script or web app which prompts the user for their inputs
                  • Run your queries in your app, save the resulting JSON datasets. Use the Tableau SDK / Document API to create a workbook with the static JSON sources, then start streaming the file as a download to the user. If the user wants to refresh the data / change their parameter selections - they would need to use your 'tool' to start from scratch.

                  This solution is complicated and it's also not flexible - surely your users will want to refresh data using different parameters right? I guess what I'm trying to demonstrate with this is there's no single approach which does everything you want in one click.

                   

                  My final suggestion... Take a look at what would happen if you use a whole bunch of nulls. I.e. lets say you have two tables - products and customers, they aren't related.

                  Product has two columns; Name and Price

                  Customers has two columns: Name and Revenue

                   

                  What if you unioned your data on top of itself in the return so it was effectively;

                      

                  ProductNameProductPriceCustomerNameCustomerRevenue
                  IPHONE1.23nullnull
                  IPOD145nullnull
                  nullnullACME1402
                  nullnullWALMART

                  140141

                   

                  The nulls mean calculations / aggregations wouldn't be impacted. Then within Tableau you could manually define dimension folders to group your objects a bit better, save that data source as per the top of the post and you'd only hit the WDC once;

                  I have no idea if this approach is viable, but I thought I'd put it out there.

                  • 6. Re: Creating multiple connections programmatically
                    Ben Hagan

                    Many thanks for the detailed response Tom - its really helpful for me to explore these options.

                     

                    The issue I face is that I dont know up front what data the user will return. Thats the beauty of JSON. The external api is a complex analytics product and can return a huge variety of different data combinations, so pre-mapping connections is not feasible.

                     

                    I thought about the null-join strategy previously. I could easily add an id to each table for a foreign key. All feels a little messy.

                     

                    Perhaps im alone in my thinking but if the web connector JS api supported creating multiple connections, this would be trivial. Create the required connections dynamically from the json result set, create the tables, add rows, done. That way there is no user interaction and all data is available. Supporting multiple connections seems like a really useful enhancement to me. Happy to log it if there is an appropriate channel.

                     

                    Right now im opting for an external RDBMS option. Just import the logic in to MySQL, I can create the tables as needed and then the user can connect to that.

                     

                    Thanks again for your time and support, its been very helpful indeed. I shall rename this thread to help others.

                    • 7. Re: Creating multiple connections programmatically
                      Tom W

                      You could recommend this idea here Ideas  or on the Github Issues section for the Web Data Connector.

                       

                      I have to say that I don't really think your idea can work for the following reasons: An individual connection within Tableau defines the source, type, filters, metadata etc associated with that connection. Having one connection be able to impact or populate the other seems really dangerous to me and it would require a pretty big shift in how Tableau structures it's connections.

                       

                      It's sounds more to me like your problem is dealing with unknown JSON structures and how you surface that back to the user. Whether you put it in an RDMS or return it as JSON, you're going to have differing structures either way and you probably need some type of consolidation.

                       

                      If you do do this in a database, you're still going to need multiple connections within Tableau for the unrelated data pieces. So I'm not really sure there's a huge advantage to that approach.

                      • 8. Re: Creating multiple connections programmatically
                        Ben Hagan

                        Thanks for the comments Tom.

                         

                        I have to say that I don't really think your idea can work for the following reasons: An individual connection within Tableau defines the source, type, filters, metadata etc associated with that connection. Having one connection be able to impact or populate the other seems really dangerous to me and it would require a pretty big shift in how Tableau structures it's connections.

                        Again, this is likely my lack of knowledge but from a JS api perspective, its seems trivial to create a connection, not too sure why one would populate or impact another? So to create a new connection:

                         

                        let myConnector = tableau.makeConnector();

                        myConnector.getSchema...

                        myConnector.getData...

                        tableau.registerConnector(myConnector);

                        tableau.connectionName = "My Connection";

                        tableau.submit();

                         

                        If the tableau object supported multiple connections, could more not be added repeating the process before tableau.submit() is called?

                        It's sounds more to me like your problem is dealing with unknown JSON structures and how you surface that back to the user. Whether you put it in an RDMS or return it as JSON, you're going to have differing structures either way and you probably need some type of consolidation.

                        Receiving the JSON data and translating that to a tabular schema is taken care of by the web connector. I then have an n number of tables I simply want to import in to Tableau, unrelated. For each table I can define the schema using getSchema and getData. I just cant import more than one.

                         

                         

                        If you do do this in a database, you're still going to need multiple connections within Tableau for the unrelated data pieces. So I'm not really sure there's a huge advantage to that approach.

                         

                        Agreed. There does not appear to be a simpler approach other than to ask the user to import each connection.

                         

                        Regards, Ben.

                        • 9. Re: Creating multiple connections programmatically
                          Tom W

                          You're confusing the connection within the Javascript / WDC with a connection within Tableau.

                           

                          Within Tableau a connection is made to a single datasource i.e. SQL Server, Excel, CSV or in your case, Web Data Connector. A connection cannot connect to multiple datasources and all tables within the connection must be related.

                          Your web data connector code is going to return your data to a single connection within the Workbook. So within the context of executing your WDC, it's not possible to do something like do this, return this to connection 1 in the workbook. now do this, return this to connect 2 in the workbook. I hope this context helps.