Import properties for Jira Assignees and build custom hierarchy

eazyBI allows importing additional properties for dimensions and creating custom hierarchies. In this example, I will walk you through how to define and import property Department for Assignee and build a custom hierarchy in the dimension Assignee. You can use the same steps to add more details for assignees or any other user dimension.

Create a list of users for your source data.

You can use eazyBI as a basis to create your data source. Create a new empty report in eazyBI. Use Transition Author as a dimension on Rows with measure Issues history. Any user dimension might work. We use Transition Author. It is a user dimension with the best coverage of all users that participated in issues imported in the account.
Create a new report specific measure to show user key:

[Transition Author].CurrentMember.Key

Here is an example report for this step:

Export the report to excel.

Update the excel file.

In excel, you can remove users that are not needed, for example, old users. Remove the column issues history. Add columns with some details you would like to import for your users. For example, I added a column Department. Fill in values for each user in this column. Add the heading name for each column.
If you are missing some users, you can add extra rows with more users. Please be careful adding the user key, it should match Jira username case sensitive.

Here is an example excel file prepared for this.

The column Assignee name is used to show the real assignee names. It might be easier to understand what users you are importing and what values. While it could be used for mapping, it is not needed for this example. You can remove it before uploading data to eazyBI. Key column is more reliable in mapping. Therefore we will use key column for mapping only.

Upload excel file in eazyBI and apply the mapping

Select the Issue cube (top left corner, not visible in the screenshot).
Then make mapping for the column with User key and any additional column you added and would like to import as property.
Use the same dimension name and level for all columns, in my case, dimension Assignee, level User.

Specify a mapping column. We will use Assignee key column and setup: Key column and option Skip missing. eazyBI will use this column to match users by key and import property values from other columns. Skip missing option will ignore rows if the match for assignee is not found.

Set the property name for any other column you would like to import as property. I used property name Department for the column Department.

Run an import.

Build reports with custom hierarchy.

In eazyBI, expand the dimension Assignee, and in section, All hierarchy level members (1) use the option Add custom hierarchy (2) > select the needed property to build a new hierarchy. I used the property Department. eayzBI created a new hierarchy Department (3) in the dimension Assignee.

Use the new hierarchy in reports

After this, you can use the new hierarchy for your report. For example, I used the hierarchy in the report on Pages to see assigned issues from department Development only.

Daina / support@eazybi.com

8 Likes

Hi Daina,

How would we go about to update the new hierarchy when there are new users or when users change department for example?
Would we need to delete the hierarchy to import it anew or is there a way to add the new information?

Thanks for this tip! Feels like it`s going to come very handy :slight_smile:

Yes, you would like to import a new file with the updated list of users. You can apply the same steps for creating a new file. Use the same file name and the same structure for the file. Upload a new file (with the same file name) to override the existing one and run the import. eazyBI will update the hierarchy data based on the newly imported file.

Here are additional options for automation:
eazyBI supports regular uploads for REST API, SQL, Google sheets. If you are using some application for users with those details, you can consider using REST API and SQL to get the data changes and set the regular imports.

If you would like to automate “excel” data upload, here are two options:

Daina / support@eazybi.com

1 Like

We sometime have people change roles and be in a different department. Would this be able to be used against a time element or would we have to add a member(s) for effective date(s)? We actually want to use this against the logger dimension for the various teams.

Properties are the attributes of the current state. They do not work over time. We can import only the last/current state as a property.

You can import data over time, mapping some metrics to a date (Time dimension) and any other dimension. We do not support adding new dimensions to represent user role to Jira issue cube with additional data import. You can consider importing this dimension as a custom field. However, there could be problems to supporting dimension creation based on Lgged by dimension.

You can reach support@eazybi.com with more details on your case, and we can check if there is an option to help you with it.

Daina / support@eazybi.com

hi,
in our organization we have the following hierarchy

Division > Sector > department > jira project

i tried to implement this hierarchy in PROJECT dimension as using the function aggregate

for example

Department A is an aggregation of jira project 1 and jira project 2

Aggregate(
{
[Project].[jira project1],
[Project].[jira project2],
}
)

Department B is an aggregation of jira project 3 and jira project 4

Sector C is is an aggregation of Department A and Department B

and at the division level is aggregation of Sector C and other sectors.

In this method the performance of the reports and dashboards are very poor.

Is there a more efficient method / alternative to implement this hierarchy ?

Regards

Tomer