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 | NOTES |
|---|---|---|---|
| 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 | |
| LABOPSJIRA7: (See more frequently updated list found here under Database column.) | 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.
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
Please use consistent formatting (schema DOT view UNDERSCORE ddl DOT sql)
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):
SRS 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 SELECT -- PK | 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
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