HierCube benchmarks

The research of the HierCube library performance has been made using the special application for performance testing.

The performance has been testing depending on 4 factors: amount of records in the fact table, amount of dimensions, length of dimensions, and amount of summaries.

There were used three common operations to calculate their cumulative time: drilling down, rotating the cube, and filtering. These operations were taken advisedly, because operations of rotating and filtering are most expensive.

All results include time required for drawing the resulted grid after every operation. The testing was performed on a computer Athlon 2500XP with 512MB of RAM.

Below are the results of testing:

1. Depending on the amount of records in the fact table

Following values were used on this test:

• Amount of dimensions: 8
• Amount of summaries: 1
• Amount of members in every dimension: 25
• Amount of records in the fact table was being changed from 62,500 to 2,000,000 of records.

The diagram bellow shows the result:

Conclusions: the points on every axis are set in logarithmic scale, so there is an exact linear dependence between amount of the fact table records, and the time required to perform three common operations. The cumulative time of the most expensive operations is no more than 10 seconds even on 2 millions of records, this means that every operation takes less then 5 seconds. If we take into consideration the fact that the operations requiring the maximum time are quite rare then it is obvious that the cube shows not bad performance with quite big data size.

2. Depending on the amount of dimensions

Following fixes values were used on this test:

• Amount of records in the fact table: 250,000
• Amount of summaries: 1
• Amount of members in every dimension: 25
• Amount of dimensions was being changed from 4 to 36.

The diagram bellow shows the result:

Conclusions: the performance of the cube is weakly dependent on the amount of dimensions. However increasing the amount of dimensions makes influence on the reading of the fact table and aloes increases the memory required for the cube. So in spite of the actual independent from this factor it is recommended however to make as many dimensions as you really need.

3. Depending on the length of dimensions

Following fixes values were used on this test:

• Amount of records in the fact table: 250,000
• Amount of summaries: 1
• Amount of dimensions: 8
• The length of all dimensions was being changed from 5 to 1215.

The diagram bellow shows the result (the "X" axis has a logarithmic scale):

Conclusions: here we see exact logarithmic dependence between length of dimensions and performance. So to minimize the required time it is recommended using hierarchical dimensions instead of one "flat" dimension (making composite dimensions is a good practice to increase the performance) or create several dimensions with less members in each one.

4. Depending on the amount of summaries

Following fixes values were used on this test:

• Amount of records in the fact table: 250,000
• Amount of members in every dimension: 25
• Amount of dimensions: 8
• The amount of summaries was being changed from 1 to 6.

The diagram bellow shows the result:

Conclusions: there is an exact linear dependence between amount of summaries and performance. So it's recommended to minimize the amount of used summaries. For example it is possible to add rarely used summaries only on a separate request of a user.