This page focuses on Oracle connections & organization for JIRA-based views & tables. More information is available here: Jira DWH

Contents:

Schemas & Usernames (in seqbldr DB)

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
LABOPSJIRA711: (See more frequently updated list found here under Database column.) To verify the current production db, query the following in REPORTING@SEQBLDR: SELECT jiradwh.etl.labopsjira_schema FROM dual	Home of the production labopsjira data used by DWH and ETL.	Rarely
GPINFOJIRA7: (See more frequently updated list found here under Database column.)	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.

Backing up view SQL in GitHub

DWH-quality views are automatically updated in GitHub if they're created in the following schemas:

ANALYTICS@SEQPROD
COGNOS@SEQPROD
REPORTING@SEQPROD
REPORTING@SEQBLDR
JIRADWH@SEQBLDR
ANALYTICS@GAP_PROD

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 (including custom fields)

New views should use standardized formatting, and are generally created in REPORTING schema until peer code-review has been performed and they're moved to the JIRADWH schema, with symlinks from REPORTING to JIRADWH.

Use our ETL functions (see below)
use standardized formatting
document Primary Keys

This tool lists all custom fields: CF_CATALOG

"Generate SQL" service creates these standardized views for you!

Get the SQL for a project's custom fields:

Generate SQL service is documented here: Jira DWH.

Here is a template example (edit as needed):
GAPREQ autogenerated custom SQL example

Querying transitions

Transition data are best queried from the REPORTING.LOJ_TRANSITIONS view, which in turn queries the JIRADWH.TRANSITIONS table for only those transitions in the ~~'LABOPSJIRA_5'~~ schema. The main table contains transitions for the GPDEVJIRA schema for use in testing.

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).

If you're writing SQL on your own, and aren't sure which ETL function to use (e.g. get_text_fields vs get_option_fields), trial and error is okay.

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:

LABOPSJIRA7

LABOPSJIRA
LABOPSJIRA44
LABOPSJIRA44_QA
LABOPSJIRADEV42
LABOPSJIRADWH
LABOPSJIRATEST_5
LABOPSJIRAUPTEST
LOJ_REPORTER
PRODINFOJIRA44
PRODINFOJIRA5_TEST
PRODINFOTESTJIRA42

Analytics