nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

what tables will give me the hierarchy......

Raj_d6wsb7

Hi, Thank you for your time. I started looking at the tables in Jira Align database

and kind of lost in identifying the tables that will give me the hierarchy data say for example

Enterprise--->Portfolio--->Program--->Team of Teams---> Teams

I want to see the list of Teams that belong to the team of teams and list of team of teams that belong to the program and so on...

Could someone please help --- what are the tables that will help me to get that hierarchy

Accepted answers

All comments

Sam Tsubota

@Raj Do you have on-prem Jira Align? If so, are you trying to query the Jira Align database directly? We don't recommend doing that for a few reasons. For one, the Jira Align database schema is confusing and it is not normalized to be easily queried. Another reason is that querying the Jira Align database directly will impact the performance of your Jira Align app.

I suggest that you get Jira Align Enterprise Insights if you don't have it already. It is the Jira Align data mart that is optimized for querying and creating custom reports. Here is a link for more info: https://www.atlassian.com/software/jira/align/enterprise-insights.

The Enterprise Insights schema document on the Jira Align Knowledge Base is provides a list of columns and tables to help you get up to speed on the tables and columns in Enterprise Insights. Here is a link to the page: https://help.jiraalign.com/hc/en-us/sections/360007351774-Enterprise-Insights-schema. You will need to create a login for the Knowledge Base by clicking on the Sign In button on the upper right to view the document.

Raj_d6wsb7

Hi Sam, Thank you so much for your time. Yes, we do have the Enterprise Insights/EI

where i see the tons of tables in it

When I look at the views that are available in EI - I see the following 2 schemas followed by some time related tables

current_dw, export_dw

When I look at the tables that are available in EI - I see the following schemas (providing just the starting name of it)

dbo.ETL_
dbo.upd_
dbo.upd_map_
dw.dim_
dw.fact_
dw.map_

I was able to find that excel document - but kind of lost in identifying the tables that will give me the hierarchy related information only...iam not looking at the data i just want to see the hierarchy info only,,,

Enterprise--->Portfolio--->Program--->Team of Teams---> Teams

Thank you again for your time and help

Sam Tsubota

@Raj You need to use either the current_dw or export_dw tables. The current_dw tables are for direct query usage and building custom reports. The export_dw tables are used for exporting data to your own data lake or data warehouse. Here is a link to a YouTube video that explains the schema: https://youtu.be/qfYOS5xmE0I.

To get the hierarchy information you are looking for your will need to join the following tables.

current_dw.[Organization] - Enterprise organization levels

current_dw.[Portfolio] - Portfolio level

current_dw{Solution] - I am guessing you have Solution level turned in your Jira Align instance and renamed it Program.

current_dw.[Program] - I am guessing Program has been renamed Team of Teams in your Jira Align instance

current_dw.[Team]

You will need to join the primary keys and foreign keys in with the parent table. The FK Mapping tab in the Enterprise Insights schema document provide those mappings.

Depending on how many Organization levels are configured in your Jira Align instance you may need to join the Organization table several times.

Raj_d6wsb7

Hi Sam, Thank you again. It is really helpful.. Are we sure we have to use the current_dw schema tables as I also see the dw.dim_organization

do you know the difference between the dw.dim_organization and current_dw.[Organization] - This will clear my doubt and get starting...

just wondering why we have 2 for the same table - export lets not worry about this for now

Sam Tsubota

You should only use the current_dw schema tables. Not sure where you are seeing the dw.dim_organization table because I don't see it in my instance of Enterprise Insights.

Raj_d6wsb7

Hi Sam, Thank you so much for your time. I see the you tube is very helpful

going back to your first reply..

Depending on how many Organization levels are configured in your Jira Align instance you may need to join the Organization table several times.

This is little bit confusing .. I have Organization ID in the main org table and the organization ID is available in some tables as FK Organization ID..

What is the reason to use this table so many times or several times..

For example: for analysis purpose i am only looking at the organization ID = 25 so i filtered it on org table to get the 25 record only...

could you please explain on why i have to use the org table several times - 1 join for 1 level? thanks a lot