This page focuses on Oracle connections & organization for JIRA-based views & tables.

Contents:

Schemas & Usernames

SCHEMA& USERNAME	DESCRIPTION	WHEN TO USE IT
REPORTING	This is where to write view synonyms that point to JIRADWH views. This can also be where view code drafts get written before being transferred to JIRADWH.	For all report-related read access, or when creating a view in REPORTING schema
JIRADWH	Home of actual DWH views. Home of ETL packages & functions that work on multiple JIRA schemas' data. This is where to write DWH code.	Only when creating views or tables in JIRADWH schema
LABOPSJIRA_5	Home of the production labopsjira data used by DWH and ETL.	Rarely
PRODINFOJIRA_5	Home of the production gpinfojira data used by DWH and ETL.	Rarely
GPDEVJIRA	This is the "LABOPSJIRADEV" clone of labopsjira_5, which is refreshed on demand.	During testing
PRODINFOJIRA5_TEST	This is the "GPDEVJIRA" server test clone which can be anything, depending on current testing needs.	During testing

Further information on these and other Atlassian applications can be found here: Genomics Platform Atlassian Applications

Note: SEQPROD.REPORTING and COGNOS.COGNOS are home to lots of pipeline data, but also contain database links to SEQBLDR to allow joining of pipeline data with JIRA data.

More info for analytics users can be found here: JIRA DB Schemas

Backing up view SQL in Stash

DWH-quality views should be backed up in Stash: https://stash.broadinstitute.org:8443/projects/GPAN/repos/oracle/browse

Datamart structure

View locations

As described above, there are two main locations for views. DWH-quality views are located in JIRADWH schema, but accessed through synonyms in REPORTING schema. REPORTING schema also holds views that are in development or temporary. Tableau and other access tools reading data should use the REPORTING username and schema.

Creating a new view

New views are created using as much standardization as possible, and are generally created in REPORTING schema until peer code-review has been performed. Views in the JIRADWH schema are good templates (example at right). Use the ETL functions whenever possible, use standardized formatting, and document Primary Keys.

Querying custom fields

The ETL functions described below can be used to query custom fields from production JIRA schemas and projects.

This document (JIRA Custom Fields) facilitates view SQL creation (and is especially useful when you need to add several CFs to your SQL). If you aren't sure which ETL function to use (e.g. get_text_fields vs get_option_fields), trial and error is okay.

Querying transitions

Transition data are available in the JIRADWH.TRANSITIONS table.

Querying with ETL functions

We have built ETL functions to simplify view creation, standardize SQL structure, and provide scalability and schema flexibility. These functions are generic, allowing you to plug in whatever specifiers you need. Use ETL functions to specify schemas, projects, and custom fields. When querying JIRA data, most of the functions you'll need live in JIRADWH/Packages/ETL/Entries (see image at right).

Querying	Example ETL	Notes
Standard fields	SELECT a.* FROM TABLE (jiradwh.etl.get_issues('SchemaName', 'Project')) a
Custom fields	SELECT * FROM TABLE(jiradwh.etl.get_text_fields('LABOPSJIRA_5', 'Starting Material Type' ))
Links	SELECT a.id1 id, SUBSTR(concat_string(a.key2),5) pdos FROM TABLE (jiradwh.etl.get_issue_directed_links('LABOPSJIRA_5','GAPREQ', 'PDO')) a GROUP BY a.id1 SELECT -- PK a.id1,-- CASE WHEN count(*)=0 then 0 ELSE 1 END pdo_link FROM TABLE(jiradwh.etl.get_issue_directed_links('GPDEVJIRA','PICO','PDO')) a WHERE project_key2='PDO' AND project_key1='PICO' GROUP BY id1	Schema is required. Projects are optional. You will need to format this carefully to get the results you want. Using 'get_issue_directed_links (SCHEMA, PROJECT1, PROJECT2) without any further filtering or grouping will return two records for each linkage, one in each direction. (see example at right).
Ticket ancestry	SELECT * FROM TABLE(jiradwh.etl.get_issue_ancestry('LABOPSJIRA_5', 0, 'LCSET','Parentage'))
labels	SELECT * FROM TABLE(jiradwh.etl.get_issue_labels('LABOPSJIRA_5'))	Returns comma-separated list. For separated records, use ETL–>TDE method (/wiki/spaces/AN/pages/613974058). For CSV record of other fields, talk to Nasko.

More info and partial list of functions here: Jira DWH

get_directed_links results

Dropped & deprecated schemas

The following schemas are no longer in use:

LABOPSJIRA
LABOPSJIRA44
LABOPSJIRA44_QA
LABOPSJIRADEV42
LABOPSJIRADWH
LABOPSJIRATEST_5
LABOPSJIRAUPTEST
LOJ_REPORTER
PRODINFOJIRA44
PRODINFOJIRA5_TEST
PRODINFOTESTJIRA42

JIRA Datamarts