Tableau connector for WRS ODATA REST API

[rleir]

Team, Would it make sense to use Tableau to create a dashboard that displays Windchill stats? Tableau would possibly access the WC ODATA REST API using https://www.tableau.com/developer/tools/web-data-connector. Has anyone got a start on developing a connection like this? Otherwise I would start from scratch. What would be the biggest challenges in this project? cheers -- Rick

[rleir]

Windchill Help says:

---

Support for OData

The framework is designed to support OData Protocol V4. All aspects of OData standard are not currently supported by Windchill REST Services. PTC intends to support minimum OData V4 compliance in subsequent releases of Windchill REST Services.

https://support.ptc.com/help/windchill_rest_services/r2.5/en/index.html#page/windchill_rest_services/WCCG_RESTAPIsSupportOData.html#

I tried to use Tableau connector to connect to our Windchill 12.0.2 https://domain.com/Windchill/servlet/odata/

and got this error:

Error code 7C19CF64   Unsupported format. OData JSON support requires OData version 4.

Tableau connector does not think Windchill supports OData version 4. How can I fix this?

Thanks -- Rick

[rleir]

Aha, that URL should be:

https://domain.com/Windchill/servlet/odata/v6/ChangeMgmt

Now Tableau and Windchill are talking! 

[aaronjlarson]

Biggest challenge I see with using the REST for reporting is that not everything is available.  I've been using PowerBI to report Windchill stats, but based on Query Builder reports rather than REST.  QueryBuilder is much more tailorable to the specific reporting need and ensures you don't have any gaps in data availability like with the REST framework.  As the "rest" of the REST framework is fleshed out I would consider re-evaluating, but for our needs it typically isn't enough.

[aacciano3]

@aaronjlarson 

We are trying to use PowerBI as well.

Are you using SQL queries and connect to the production Windchill DB or did you setup table replication and point PowerBI to the replicated DB?

[aaronjlarson]

No - we do it by creating a QueryBuilder that is set to return a CSV. We use the source call in PowerBI of

source = Csv.Document(Web.Contents("[your windchill url for the report].....

There are some other nuances related to authentication depending on what you're ultimately looking to do.

Using this method we are able to pass parameters, if necessary, to the QB report call (must be accounted for in the QB design), and also use scheduled refreshes in the PBI service. We use this method to feed datasets directly as well as dataflows.

This communication (including any attachments) is for the use of the intended recipient(s) only and may contain information that is confidential, privileged or otherwise legally protected. Any unauthorized use or dissemination of this communication is prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. -

[aacciano3]

@aaronjlarson 

Thank you for sharing!

I may to rethink my strategy for some of the things I am doing.

[rleir]

We do something similar, running a QB report from a python program. The Windchill URL is domain/Windchill/servlet/WindchillAuthGW/wt.enterprise.URLProcessor/URLTemplateAction?parameters

You can learn how to use the parameters with the help of chrome:right-mouse->inspect in ReportBuilder etc. But there are problems: if the description field has newlines that messes up the CSV. If you choose to get the data in XML then any non-ascii character in your description field messes up the XML file. So we use QB just to run a simple join, and get the description fields via ODATA. That makes for a complicated python program.

[aaronjlarson]

Agree on the XML.  I started with that output and ran into the same issue with the characters.  Then I figured out how to use the CSV and life is great.  I haven't experienced any issue with the new line characters yet.  Regarding the parameters, the way I do it is to first set up the QB output as HTML and run it once on a small constrained sample.  This gets you the URL that contains the parameter(s) strings you'd need. I copy this URL and then change the output to CSV and use the source call in PBI that I mentioned.  Since I now have a URL that contains parameters I can replace my test values for them with actual PBI parameters.  This allows me to make a dynamic reporting source, i.e. one QB design that can be used for multiple PBI use-cases within the parameter definition.  Like a project name, or user name, etc can be feed into the parameter on the PBI side.

[rleir]

Agreed, the OData WRS does not make everything available. You cannot do a join quite as easily as QB does it. In certain cases it is easy with $expand Context/Name or $expand AffectedObjects (sp).  Other joins seem to be impossible in OData.

Oh, and the current sore point is that we don't see how we can get the Rich Text RTF fields in OData. That is with OData called from python.

Worse still, Tableau's Odata connector does not seem to support the $expand= flavour of join. Comments please! Maybe I need to use TabPy and some custom python to make a bridge between WC Odata and Tableau.

[rleir]

Team

Sorry for bumping this question up again. Maybe we need to use the Tabpy project, because the Odata connector does not seem to let us do the '$expand=' that we need? And we would need to build a WC specific layer on top of TabPy, or is there a simpler way?

cheers -- Rick

https://github.com/tableau/TabPy