We recommend the following steps for a successful implementation:

Data Extraction with ETL Tools

Use an ETL tool (Extract, Transform, Load) like Talend to extract data from your Jira instance. You can access all relevant data via the REST API of Jira. Talend offers the possibility to perform REST API queries using Java routines and then start subsequent processes in parallel. It’s important to minimize the load on the Jira instance to avoid affecting its performance.

Loading the results into the target database should also be performed as a bulk operation to ensure high performance.

Initial Data Loading and Regular Updates

• Initial Data Loading: Begin with a complete data extraction of your Jira data to create a comprehensive basis for your analyses.
• Regular Updates: Set up regular delta extractions (e.g., every 5 minutes) to continuously update and keep your data current. The execution of the Talend jobs takes place in the Talend Cloud. Ensure the assignment of a well-scaled engine to guarantee optimal performance and resource utilization.

Updating the issue data should be performed significantly more frequently (e.g., every 5 minutes) than updating the administrative data (e.g., every 60 minutes). This ensures that project-related data like changes to issues, statuses, or comments are captured almost in real-time, while less dynamic administrative data like user information or configurations can be updated at longer intervals.

Processing the Changelog

• Read the Changelog: Extract the changelog of the issues to obtain a complete historical view of all changes.
• Utilize Native Versioning: Use Jira’s native versioning to track changes over time.

Use of Surrogate Keys and Business Keys

Jira already provides primary keys like the Issue Key or Project Key, which should be used to establish business relationships between entities.

However, to clearly separate the versions per data entity technically, it’s advisable to introduce surrogate keys. These surrogate keys allow you to uniquely identify different versions of the same entity (e.g., changes to an issue over time), ensuring consistent and traceable historization (see ERD).

Support of Slowly Changing Dimensions

Implement mechanisms to track changes in data over time, which is particularly advantageous for historical analyses. This includes:

• Snapshots: Capture the current state of the data at each update. Snapshots allow you to represent the state of the data at a specific point in time and serve as a basis for temporal comparisons.
• Changelog: The changelog of the issues enables native versioning and documents all changes to an issue, such as status changes, field updates, or assignments. This allows detailed tracking of the development and history of an issue without requiring a complete snapshot.

Use of Cloud Databases

• Scalable Storage: Store your data in a scalable cloud database like Google BigQuery.
• Individual Scaling: Adjust the resources of your cloud database flexibly to your needs.
• Security Features: Utilize functions such as masking PII data (Personally Identifiable Information) and security policies to comply with data protection regulations.

Configuration of Processing Chains

• Create Processing Chains: Set up processing chains to further process your data after the ETL process.
• Data Marts and Dimension Tables: Develop data marts for specific analysis purposes and use tools or scripts to create dimension tables.
• Automatic User Referencing: Extract user data and create automatic links to the user table to facilitate user-related analyses.

Integration of Multiple Jira Instances

• Consolidate Data: Combine data from multiple Jira instances (e.g., a mix of Data Center and Cloud) into a central data source.
• Central Data Source: Obtain a unified view of all projects and processes by merging data from different instances.
• Comparative Analyses: Identify differences and similarities between different teams or departments for improved collaboration and transparency.