Operator Advisor, Best practices to make Joined calls

[jasperlg]

Hi all, 

We are implementing Operator Advisor (in TW 8.4). It's clear that every table in the database has a copy in ThingWorx as a DataShape, also junction tables. 

I'm wondering, what is the best way to show data from multiple tables in the database? The way I see it there are two options: 

1. Use the Operator Advisor database services and make multiple calls and do intersects on the infotables you'll get as an answer. 

2. Write your own JOIN query's in the database thing and display the answer in a datatable.

Another question leaning on the previous question: What datashape do you use when you display the joined data? Or do you make a new DataShape for every new call? 

[mthakker · Accepted solution]

Hi Jasper,

For the time being I would not recommend approach 1: ThingWorx only gets the last 500 records, so your intersects will only occur on the last 500 records.  That being said, I would also not recommend approach 2 as anything other than a temporary measure - we will be releasing the ability to make basic SQL joins out of the box in the next Operator Advisor release.  @fmok can provide more details about exactly what we plan.

As for what datashape to use - use the datashape that you ultimately wish to return - for example, if you're looking for "Work Definitions" to come back after being joined and filtered, use that, and it will bring back additional properties that you have added as well.

Thanks,

Milan

[jasperlg]

Hi Milan, 

Thanks for the answer. I'm looking forward to this feature, we won't start writing our own joins then. 

Jasper

[fmok]

Hi Jasper,

We are targeting our March release to handle joins. 

This will be done through the existing filters. Here is an example of what your filter may look like with the new join tags.

 

[jasperlg]

Hi @fmok , 

It looks like something went wrong, I can't see your screenshot. 

Jasper

[fmok]

Hi Jasper

Here is the same info in text. Hope it formats ok...

{

                "select": [

                                {

                                                "dataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement_AP",

                                                "fieldName": "Name"

                                },

                                {

                                                "dataShapeName": "PTC.SCA.SCO.JobOrder_AP",

                                                "fieldName": "Name"

                                },

                                {

                                                "dataShapeName": "PTC.SCA.SCO.JobOrder",

                                                "fieldName": "ID"

                                },

                                {

                                                "dataShapeName": "PTC.SCA.SCO.ProcessingResource",

                                                "fieldName": "Name"

                                }

                ],

                "filters": {

        "filters": [

            {

                "dataShapeName": "PTC.SCA.SCO.ProcessingResource",

                "fieldName": "ID",

                "type": "EQ",

                "value": "WC1"

            }

        ],

        "type": "AND"

    },

                "joins": [

                                {

                                                "type": "INNER",

                                                "sourceDataShapeName": "PTC.SCA.SCO.JobOrder",

                                                "sourceFieldName": "UID",

                                                "targetDataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",

                                                "targetFieldName": "JobOrderUID"

                                },

                                {

                                                "type": "INNER",

                                                "sourceDataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",

                                                "sourceFieldName": "ProcessingResourceUID",

                                                "targetDataShapeName": "PTC.SCA.SCO.ProcessingResource",

                                                "targetFieldName": "UID"

                                }

                ],

                "sorts":[

                                {

                                                "dataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement_AP",

                                                "fieldName": "Name",

                                                "isAscending": true,

                                                "isCaseSensitive": false

                                },

                                {

                                                "dataShapeName": "PTC.SCA.SCO.JobOrder_AP",

                                                "fieldName": "Name",

                                                "isAscending": false,

                                                "isCaseSensitive": false

                                }

                ]             

}

[jasperlg]

@fmok, 

Looks great, thanks for the example! 

Kind regards, 

Jasper

[jasperlg]

@fmok for the record, does the join filter already exists in the current JSON syntax?  Or is it the syntax that you will develop for OA? 

[fmok]

Hi Jasper, The join filter is a new tag that we will add to the filter in OA