# A New Big Data Benchmark for OLAP Cube Design Using Data Pre-Aggregation Techniques
## 5 Discussion
The benchmark results shows that D7 design achieves the best average query latency of benchmark execution, just 4.05 s and only 21.21% of the all queries set responded over 3 s. Compared with the other designs this is one of most balanced, achieving an Expansion Rate of 14.94\(\times\) and 3.76 h of building time. D8 has slightly better results for Expansion Rate and Building Time, however query latency average is over 1 s worse. We conclude that best balanced designs are D3 and D7. D3 obtains the best values for variables related to cube building and size, especially for cube building time. This process takes less than two hours, thus an acceptable value for a BI OLAP system with a daily window load. However its 11.34 s of query latency is a little high for an OLAP system, where 42.42% of the queries are executed over 3 s. Although, in some BI scenarios the 3.76 h of building time could be high, D7 cube latency is just 4.05 s, with only a 21.21% queries executed over 3 s, thus this design is very suited for OLAP production environments. The execution of our benchmark has provided us results that demonstrate that to achieve a good OLAP cube design with Kylin we have to: (i) find a trade-off between the number of \"Normal\" and \"Derived\" dimensions, i.e., the trade-off between the amount of data pre-aggregated during cube building (thus stored on cube) and the aggregation needed on query execution time, and (ii) define the Aggregation Groups and its optimizations over \"Normal\" dimensions accordingly to the analytics data model aimed to implement and end-user query patterns. Based on these findings, together with the analyzed principles for cube design, we have proposed the following guideline for the optimal cube design for Apache Kylin:  
1. **Identify query patterns:** We must collect the query requirements from existing queries or user requirements. The aim is to identify the following elements among others: Tables, relations between these tables, most used columns, aggregations on columns, columns that are queried together, hierarchical relations or columns used as filters. 2. **Define the OLAP cube:** Using the information collected about querying patterns. **Table and column selection:** Include only those tables and columns that will be used. If the cube contains too many dimension columns, we should consider create multiples cubes to reduce its complexity. **Dimension type selection:** We recommend defining as \"Normal\" only those dimension columns that occur frequently in the identified query patterns and their performance would be low if we defined them as a \"Derived\" dimensions. Based on our insights, we can establish that this low performance is often given for cardinality ratios greater than 1/10 with respect to the SK column of the dimension table to this column belongs. **Aggregation groups definition:** We have to group the columns with dimensions defined as \"normal\" into aggregation groups that respond to query patterns: Columns queried together, with a hierarchical relationship or those used in all queries (e.g., a date column). Declaring these patterns will reduce the effects of the curse of dimensionality problem and therefore reduce the size and building time of the resulting cube. 3. **Apply the benchmark:** Apply our proposed benchmark to evaluate the cube design. **Building the OLAP cube:** In case the cube cannot be built due to OOM errors, go back to step 2 to review the cube design criteria to generate a new one. **Benchmark execution:** In case there are no existing queries, we must design a set of SQL queries based on user requirements. Then, execute this query set and measure query latency and coverage of the model. 4. **Evaluate results and refine cube design:** If the implemented cube does not meet the values required for the goals listed in Table 1, return to step 2 to refine the cube design.