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

[Raj]

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

[Raj]

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]

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]

Thank you Sam, I looked at the Video - I see so many tables when i go to SQL server Mgt Studio and expand the tables (not the views) I see many tables - just as example

Are these not coming from jira align into EI ? 

Do you think our org might have request the jira align to build those tables within EI?

[Sam Tsubota]

@Raj It looks like you may have an on-prem version of Jira Align and Enterprise Insights. If you have on-prem Enterprise Insights, your administrator can create the login accounts with whatever permissions you want. For the Enterprise Insights cloud versions, users cannot see the dw.dim_ tables at all. Those dim tables are used for the Enterprise Insights ETL process and strongly advise not using them. You should only use the views in the current_dw or export_dw schema.

[Raj]

Hi Sam,

when you get few minutes..could you please help me understand if i missed anything

in my Org table i have an Org ID for each and there is another field that says org structure level with values like 0 1 2 3 - since Iam only looking at one particular org ID

When do we use the  org able multiple times ? - my portfolio table has org id as FK so i am confused on why we need the org table multiple times

thanks a lot for your time

[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]

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