Clarity

Expand all | Collapse all

Is there a way to write a custom SQL query with the OData service so that we can derive a table in Tableau with all of our required data ?

  • 1.  Is there a way to write a custom SQL query with the OData service so that we can derive a table in Tableau with all of our required data ?

    Posted 04-24-2018 03:10 PM

    Hi,

     

    I am interested in a more effective way to get my data from PPM into Tableau. We are using an on-demand solution and we have connected to PPM via the OData Service. However, this only allows us to connect to one table at a time. Tableau does not allow joining on these different data sources and only allows data blending - which is not meeting our join requirements because we have ~5 tables we want to pull data from. 

     

    Is there a way to write a custom SQL query with the OData service so that we can derive a table in Tableau with all of our required data ?

     

    Thanks!



  • 2.  Re: Is there a way to write a custom SQL query with the OData service so that we can derive a table in Tableau with all of our required data ?

    Posted 04-25-2018 05:03 AM

    Another approach to your integration (so not odata access) would be calling custom PPM (NSQL) queries over the web-service ; this is supported on SAAS since its core PPM functionality.

     

    You create (whatever) query you need in NSQL, then call that (via SOAP, will get a response in XML) - some simple examples on how that works can be found here ; Obtaining NSQL Query Output via XOG 



  • 3.  Re: Is there a way to write a custom SQL query with the OData service so that we can derive a table in Tableau with all of our required data ?

    Posted 04-25-2018 12:43 PM

    Thanks David for your response!

     

    At this time Tableau does not have the capability to read .xml files. We do have all the data we need in a JasperSoft report through the creation of a custom domain. We have been able to export this file (.xls or .csv) and upload into Tableau. We were just looking for a way to automate this process vs. having to manually replace the file each month.

     

    Do you know if there is a way to create a .csv output file from a gel or XOG script to the local machine?



  • 4.  Re: Is there a way to write a custom SQL query with the OData service so that we can derive a table in Tableau with all of our required data ?

    Posted 04-25-2018 07:27 PM

    You could use the Tableau Web Data Connector SDK to call the NSQL Dave talked about.  Inside the web connector, it would pretty easy to create a JSON object from the XML return via the PPM Query WSDL.

     

    There are quite a few XML<-> JSON converters:

    GitHub - abdmob/x2js: x2js - XML to JSON and back for JavaScript 

     

    V/r,

    Gene



  • 5.  Re: Is there a way to write a custom SQL query with the OData service so that we can derive a table in Tableau with all of our required data ?

    Posted 04-26-2018 04:09 AM

    You could absolutely build something that ran "locally" to call the webservice, get the query response back (in XML) and convert it to CSV - I don't have any helpful examples I'm afraid but I know you could build all that in a little windows batch file or powershell sort of thing for example.



  • 6.  Re: Is there a way to write a custom SQL query with the OData service so that we can derive a table in Tableau with all of our required data ?

    Posted 04-26-2018 10:17 AM

    If Tableau can use Excel, you could batch up this:

     

    NSQL to Excel Utility 

     

    V/r,

    Gene