Versions Compared

Version Old Version 26 New Version Current
Changes made by

Amy Biasella (Deactivated)

Rafael Sanchez (Deactivated)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


 

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

Contents:

Table of Contents

Schemas & Usernames (in seqbldr DB)

 labopsjira7
SCHEMA& USERNAMEDESCRIPTIONWHEN TO USE ITNOTES
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
JIRADWHHome 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.)


Note

To verify the current production db, query the following in REPORTING@SEQBLDR:

Code Block
languagesql
SELECT jiradwh.etl.labopsjira_schema 
FROM dual



Home of the production labopsjira data used by DWH and ETL.

Rarely


 prodinfojira7GPINFOJIRA7:
(See more frequently updated list found here under Database column.)		Home of the production gpinfojira data used by DWH and ETL.Rarely 
GPDEVJIRAThis 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 StashGitHub

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

Please use consistent formatting (schema DOT view UNDERSCORE ddl DOT sql)

 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

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

Tip
titleGet the SQL for a project's custom fields:

Generate SQL service is documented here: Jira DWH. 

Here is a template example (edit as needed):
SRS 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.

  
QueryingExample ETLNotes
Standard fieldsSELECT a.* FROM TABLE (jiradwh.etl.get_issues('SchemaName', 'Project')) a
Custom fieldsSELECT * 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 ancestrySELECT * FROM TABLE(jiradwh.etl.get_issue_ancestry('LABOPSJIRA_5', 0, 'LCSET','Parentage')) 
labelsSELECT * 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