OLAP DWH database

The latest eazyBI version for Data Center includes support for ClickHouse as the OLAP DWH database. Using ClickHouse can improve report performance for eazyBI accounts with large imported data size.

Overview

eazyBI stores imported data in the database that is specified in eazyBI settings. For each eazyBI account, a separate database schema is created (called account DWH schema) with a separate set of dimensions and measures tables (called DWH tables). If a large amount of data is imported (for example, hundreds of thousands or millions of Jira issues), then it will result in many millions of rows in some of these DWH tables. When the embedded Mondrian OLAP reporting engine performs report queries, it will generate SQL queries from these DWH tables to aggregate selected measures by selected dimensions and store the results in the Mondrian OLAP cache. For large DWH tables (many millions of records), these SQL queries can become slow. Therefore previously it was recommended to split large eazyBI accounts into smaller accounts to improve report query performance.

The new ClickHouse OLAP DWH database support can significantly improve SQL query performance from DWH tables. ClickHouse is a leading open-source analytical database that significantly improves analytical SQL query performance. When using ClickHouse as an OLAP DWH database, after importing source data in the account DWH tables, additional data synchronization to the ClickHouse database is performed. After that Mondrian OLAP reporting engine uses the ClickHouse OLAP DWH tables for all reporting queries.

NOTE: This solution is already used in eazyBI Cloud for larger accounts and it requires no setup for Cloud customers.

ClickHouse database can be installed

• either on a Linux server in the same data center where Jira (or Confluence) Data Center nodes are installed (to ensure that there is a fast network connection between Data Center nodes and the ClickHouse database);
• or if Jira Data Center is installed on AWS, then ClickHouse Cloud service on AWS can be used.

Administrators can specify that ClickHouse OLAP DWH is used either for just specific eazyBI accounts or it could be used automatically if imported data size exceeds a specified threshold.

ClickHouse installation

If you would like to install the ClickHouse database on your own server, then see ClickHouse installation instructions. The alternative is to use ClickHouse Cloud – use it only if you host your Jira Data Center on AWS and ClickHouse Cloud is available in the same AWS region.

Enable access_management in the users.xml configuration file to enable new user creation using SQL (this is not needed if ClickHouse Cloud is used).

Connect to the database as a database admin and create a separate user for eazyBI:

CREATE USER eazybi_jira IDENTIFIED BY '...';
GRANT SHOW, SELECT, INSERT, ALTER, CREATE, DROP, TRUNCATE, OPTIMIZE ON *.* TO eazybi_jira;

It is necessary to grant access to all schemas as eazyBI will dynamically create new schemas for each eazyBI account.

Settings

In eazyBI settings, enable Use OLAP DWH database and specify the ClickHouse database connection:

If several eazyBI instances (e.g. production and staging or testing) use the same ClickHouse database, then specify a unique schema prefix for each eazyBI instance.

If you would like to test ClickHouse OLAP DWH only for one or several eazyBI accounts, then select the account and, in the account edit page, specify the OLAP DWH connection as olap_dwh. This will start a background job for initial data synchronization. Afterward, data synchronization will be performed every time after source data import is performed.

If you would like to enable ClickHouse OLAP DWH for all large accounts, then specify in advanced settings the DWH size threshold from which OLAP DWH synchronization should be enabled:

[olap_dwh]
dwh_size_threshold = "5 GB"

You can check the DWH size in the Analyze tab of the particular account. Or you can go from the top administration menu to All accounts and see the data size of all accounts.

Troubleshooting

From the Administration / Troubleshooting page, check the background jobs log file eazybi-queues.log  to see if there are any OLAP DWH synchronization errors.

If you need help from eazyBI support, then Download all log files from the Troubleshooting page and send them when contacting support.