Use EI permission data with Atlassian Analytics

Enterprise Insights (EI) provides 4 tables in the schema to help determine what data a user can access in Jira Align, based on their team memberships. In Jira Align, permissions are primarily tied to teams, not directly to individual users. Each table maps out the portfolio, program, and solution team levels.

Permission tables overview

Each permission table contains:

• A column with the Team ID
• A column with the ID of the level of the team: Portfolio, solution, or program

View names

Review the view names in the current_dw schema for the following tables:

Table descriptions

• MAP Team Access Team
  • FK Team ID: Foreign key to Team.[Team ID]
  • FK Agile Team ID: Foreign key to Team.[Team ID] that the team can access
• MAP Team Access Program
  • FK Team ID: Foreign key to Team.[Team ID]
  • FK Program ID: Foreign key to Program.[Program ID] that the team can access
• MAP Team Access Solution
  • FK Team ID: Foreign key to Team.[Team ID]
  • FK Solution ID: Foreign key to Solution.[Solution ID] that the team can access
• MAP Team Access Portfolio
  • FK Team ID: Foreign key to Team.[Team ID]
  • FK Portfolio ID: Foreign key to Portfolio.[Portfolio ID] that the team can access

Use permission tables in AA queries

To filter data based on the current user's permissions, follow these steps:

1. Identify the current user.
2. Find the teams the user belongs to.
3. Determine the level of each team: portfolio, solution, or program.

The join path is:
Current User → Team Member → Team → MAP Team Access {Level} → {Level}

Note: Atlassian Analytics provides a {CURRENT_USER} variable containing the Atlassian account ID. To connect this to a Jira Align user, join on the email address, as Atlassian account data and Jira Align data are in separate sources.

Sample query

The following SQL query returns the total effort points for all open stories in programs the current user can access:

SELECT TOP 1000 [User].[Email], [Program ID], SUM([Story].[Level of Effort]) AS [Total LOE]
FROM [current_dw].[Story] AS [Story]
INNER JOIN [current_dw].[Program] AS [Program] ON [Story].[FK Program ID] = [Program].[Program ID]
INNER JOIN [current_dw].[Team Access Program] AS [teamAccessProgram] ON [teamAccessProgram].[FK Program ID] = [Program].[Program ID]
INNER JOIN [current_dw].[Team] AS [Team] ON [Team].[Team ID] = [teamAccessProgram].[FK Team ID]
INNER JOIN [current_dw].[Team Member] AS [Team Member] ON [Team Member].[FK Team ID] = [Team].[Team ID]
INNER JOIN [current_dw].[User] AS [User] ON [User].[User ID] = [Team Member].[FK User ID]
WHERE [Story].[State] NOT IN ('', '5 - Accepted')
GROUP BY Program.[Program ID], [User].[Email]