Customizing the Jira Align Enterprise Insights data source schema in Atlassian Analytics

[#1]

Once you’ve connected the Jira Align Enterprise Insights data source in Atlassian Analytics, the data source is set up with a default schema configuration. You can customize the schema configuration to align with how your Jira Align instance is configured. Here are a few tips to customize your Enterprise Insights schema configurations to support some common Jira Align configurations.

Rename tables and columns to match your Jira Align platform terminology

The table and column names in the Enterprise Insights schema reference the out-of-the-box terminology, which may confuse your users when they're creating queries using Visual SQL in Atlassian Analytics.

If you have permission to manage the Enterprise Insights data source in Atlassian Analytics, you can edit the default table and column display names in the Enterprise Insights schema to match the platform terminology configured in your Jira Align instance. This will make it easier for users who aren't familiar with the out-of-the-box Jira Align terminology.

Note: Changing the display names for tables and columns won’t affect existing charts because the SQL names for tables or columns won’t change in the Enterprise Insights schema.

How to edit table display names to reflect your platform terminology

1. Select Data from the global navigation to see your data sources.

2. Select your Enterprise Insights data source.

3. Select the Schema tab.

4. Enter the table name in the search bar at the top. Results will display automatically.

5. Select the Pencil icon by the table’s name.

6. Enter the new name.

7. Select Save.

You’ll see the new table name when browsing the schema in your visual mode queries.

How to edit column display names to reflect your platform terminology

1. Select Data from the global navigation to see your data sources.

2. Select your Enterprise Insights data source.

3. Select the Schema tab.

4. Enter the table or column name in the search bar at the top. Results will display automatically.

5. Select the expand button next to the table name to view the table’s columns.

6. 

7. Select the column name to edit.

8. Update the display name.

9. Select Save.

10. 

You’ll see the new column name when browsing the schema in your visual mode queries.

When you look at the generated SQL query, you’ll see the new display names are used as the aliases in the visual mode query.

Modifying foreign key mappings for capabilities and solutions

We configured the default foreign key mapping between tables in the Enterprise Insights schema for Atlassian Analytics to try to address the most commonly configured work item and teaming hierarchy among our customer base. The default mapping supports the work item hierarchy of theme → epic → feature → story and the teaming hierarchy of portfolio → program → team.

If you’ve configured your Jira Align instance for capabilities (theme → epic → capability → feature → story) and solutions (portfolio → solution → program → team) and want to enable your users to query capability and solution data in their visual mode queries, you’ll need to edit the foreign key mappings for columns in the following tables in the schema configuration of your data source:

• Capability

• Feature

• MAP Capability to Program

• MAP Capability to Program Increment

• Program

• Solution

Modify the Capability table

1. Select Data from the global navigation to see your data sources.

2. Select your Enterprise Insights data source.

3. Select the Schema tab.

4. Navigate to the Capability table.

5. Select the Visible checkbox to make the table always visible in visual mode.

6. Select the expand button next to the table name to view the table’s columns.

7. Find and select the FK Epic ID column to expand its column properties.

8. For its Foreign keys fields, select the Epic table, Epic ID column, and Left outer join.

9. Select Save.

Modify the Feature table

1. Navigate to the Feature table and select the expand button next to the table name to view the table’s columns.

2. Find and select the FK Epic ID column to expand its column properties.

3. Remove the join between the “Feature” and “Epic” tables by selecting ---- in both the table and column dropdowns in the Foreign keys fields.

4. Select Save.

5. Find and select the FK Capability ID column to expand its column properties.

6. For its Foreign keys fields, select the Capability table, Capability column, and Left outer join.

7. Select Save

Modify the MAP Capability to Program table

1. Navigate to the MAP Capability to Program table and select the expand button next to the table name to view the table’s columns.

2. Select the Visible checkbox to make the table always visible in visual mode.

3. Find and select the FK Capability ID column to expand its column properties.

4. For its Foreign keys fields, select the Capability table, Capability ID column, and Left outer join.

5. Select Save.

6. Find and select the FK Program ID column to expand its column properties.

7. For its Foreign keys fields, select the Program table, Program ID column, and Left outer join.

8. Select Save.

Modify the MAP Capability to Program Increment table

1. Navigate to the MAP Capability to Program Increment table and select the expand button next to the table name to view the table’s columns.

2. Select the Visible checkbox to make the table always visible in visual mode.

3. Find and select the FK Capability ID column to expand its column properties.

4. For its Foreign keys fields, select the Capability table, Capability ID column, and Left outer join

5. Select Save.

6. Find and select the FK Program Increment ID column to expand its column properties.

7. For its Foreign keys fields, select the Program Increment table, Program Increment ID column, and Left outer join.

8. Select Save.

Modify the Program table

1. Navigate to the Program table and select the expand button next to the table name to view the table’s columns.

2. Find and select the FK Portfolio ID column to expand its column properties.

3. Remove the join between the “Program” and “Portfolio” tables by selecting ---- in both the table and column dropdowns in the Foreign keys fields.

4. Select Save.

5. Find and select the FK Solution ID column to expand its column properties.

6. For its Foreign keys fields, select the Solution table, Solution ID column, and Left outer join.

7. Select Save.

Modify the Solution table

1. Navigate to the Solution table and select the expand button next to the table name to view the table’s columns.

2. Select the Visible checkbox to make the table always visible in visual mode.

3. Find and select the FK Portfolio ID column to expand its column properties.

4. For its Foreign keys fields, select the Portfolio table, Portfolio ID column, and Left outer join.

5. Select Save.

Note: If you're using objectives, dependencies, risks, etc. with capabilities, you’ll need to map the foreign keys between the required tables.

Tips

• If your Jira Align instance has portfolios with capabilities and solutions enabled and portfolios without them enabled, it's recommended that you set up two Enterprise Insights data source connections and edit one of the data sources schema configurations for capabilities and solutions.
• Be aware that when syncing Enterprise Insights schema updates any default foreign key mappings that you manually removed will revert back to the default configuration (added back). Any manually added foreign key mappings will not be affected. Learn more about syncing schema in Atlassian Analytics.

Learn more about editing your schema in Atlassian Analytics.