Motivation
There have been situations where end users need to review a sample, check relevant metrics and trigger a specific Action - for example "for a given sample, IF RapidQC has completed AND LOD>0 THEN submit "FinalAggregation" request in Pipeline's JMS queue". All of these actions to be done/initiated from within Tableau. Beyond visualization, Tableau can't do much else in terms of maintaining a "workflow" in DB, talking to external web-services like JMS and so on. That's why these job is being outsourced to our "SampleAnalyticsWorkflow" web-service - it's written in Scala so there is virtually no limit what tasks it can do for you. Tableau utilize it's UrlAction gadget to make a URL call to SAW service when appropriate.
...
| Column name | Data type | Description |
|---|---|---|
| PDO | VARCHAR2 | PDO |
| PDO_SAMPLE | VARCHAR2 | PDO SAMPLE |
| DATA_TYPE | VARCHAR2 | Exome, WGS |
| REQUEST_TYPE | NUMBER | "RequestFinalAggregation", "RequestTopOff", "RequestTopOffGroup", "RequestPoolCreated", "RequestSendToRework", "RequestHoldForTopOff", "RequestWaitingForNewRwData", "RequestReset", "RequestPCHoldForTopOff","RequestVolumeFinal" |
| USER_NAME | VARCHAR2 | who triggered this action/transition |
| PARAMS | VARCHAR2 | This is a free text where Tableau developers can store additional info re this transition which later can be used. Usually it's semicolon-separated-list-of-key=value-pairs |
| TIMESTAMP | DATE | when this action was triggered |
| IS_LATEST | NUMBER | Boolean (1 or 0), Is this the latest transition a given (PDO, PDO_SAMPLE, DATA_TYPE) has gone through. Every time new transition is recorded it comes with IS_LATEST=1 whereas previous latest transition is reset IS_LATEST=0 |
| IP_ADDRESS | VARCHAR2 | where (which IP) this request came from |
...
- requestType is the text-representation of this request (RequestFinalAggregation, RequestTopOff, etc)
- each individual item is a quintuple representing a single sample like this pdo:pdo_sample:data_type:user_name:parameters (greens are optional)
...
RequestFinalAggregation is the only request which also sends message to Pipeline JMS queue. This is done by SAW in 2 steps
...
- JOIN cognos.slxre_readgroup_metadata ON (pdo, pdo_sample, data_type) and lookup (research_project_id,collaborator_sample_id) for a given sample
...
- send JSON message to appropriate JMS queue
Environments
2 separate environments are provided
...
| Production | Development/Test | |
|---|---|---|
| table | SAMPLE_ANALYTICS_WORKFLOW | SAMPLE_ANALYTICS_WORKFLOW_TEST |
| web-service | http://analytics:8090/api/sampleAnalyticsWorkflow | http://analytics:8090/api/sampleAnalyticsWorkflowTest |
| JMS queue | https://picard-jms.broadinstitute.org:8161/admin/browse.jsp?JMSDestination=broad.pushtocloud.enqueue Pipeline is picking up messages from this queue. | No listener is registered on this queue. |
| JMS browse | https://picard-jms.broadinstitute.org:8161/admin/browse.jsp | https://picard-jms-dev.broadinstitute.org:8161/admin/browse.jsp |
...
- take user_name out of items (see authentication above)
- take out PDO-, SM- prefixes resp from PDO, PDO_SAMPLE. SAW will automatically add them back as needed.
- parameters which are pertinent to all samples (for example source=RQC) should be moved to standalone extra_info parameter. SAW will disperse it to PARAMS column of all samples involved.
- regarding parameters which are sample-specific (for example xShort=123;yLong=567)
- move all keys to standalone params_fields url-parameter as colon-separated-list-of-keys
- in the items/samples parameter leave only the values as semicolon-separated-list-of-values. SAW will pair back keys/values accordingly and insert it into PARAMS column.
- In item's data_type, encode Exome with E (resp.WGS with W)
...