# A New Big Data Benchmark for OLAP Cube Design Using Data Pre-Aggregation Techniques
## 5 Discussion  
After the execution of our proposed benchmarking, we analyzed the results in order to extract useful insights to improve the OLAP cube design processes for Apache Kylin. Based on these findings, together with the analyzed principles for cube design, we have proposed a guideline to find the optimal cube design for Apache Kylin. In Table 5, we can see that D1 cube could not be built successfully. The high number of \"Normal\" dimensions and the non-application of any of the Aggregation Group optimizations makes the number of combinations too high (\(2^{12}\)) for computing this cube using a Hadoop cluster. This process requires a lot of memory, thus Out of Memory (OOM) errors can take place in this scenario. However, the remaining designs were built successfully, since they only used design D1 as a basis. In order to generate remaining designs over D1, we increased the number of \"Derived\" dimensions in designs D3, D4, D7, D8, and we applied Aggregation Group optimizations in D4, D5 and D6 cubes. If we compare designs D3 and D2, we observe the high impact of defining all D3 dimensions columns as \"Derived\" has on benchmarking variables. The OLAP cube size is just 1.48 times bigger than the DW used as source (described on Section 3.2) while D2 cube size (where only some dimensions are defined as \"Derived\") is 40.43 times the DW source size. Moreover, building time is dramatically reduced in D3 compared with D2 time. Surprisingly, although the computation for aggregation needed in query execution by D3 cube is much bigger than for D2 cube, the Query Latency is a little smaller in  
Figure 4: Percentage of queries resolved in less than 3 s.  
Figure 3: Query latency results grouped by OLAP cube design and query type. D3, about 1 s on average. This could be explained due to the huge Size of cube D3, query execution could need a lot of time to scan all the HBase table where the OLAP cube is stored. Designs D4, D5 and D6 are based on D1 design but applying Aggregation Groups optimizations over \"Normal\" dimensions. If we compare this three design, we observe D5 has a smaller expansion rate (14.99\(\times\)) due to the definition of 3 hierarchies over 9 \"Normal\" dimensions. Moreover this design has the second smallest query latency of all benchmarked designs, and a reasonable building time of 4.26 h. For design D6, we removed 3 dimensions of the hierarchies used in D5, in order to add one mandatory dimension. The query latency is 15.07 s, the highest result of the benchmark, while the rest of variables are a little bigger than D5. Due to the fact that only a little subset of the queries use the mandatory dimension defined in D6 (ID_NUMBER), query latency will be better for these queries and worse for the remaining. Finally, D4 design define two groups of two joint dimensions instead of the three hierarchies defined on D5 and D6 because Kylin currently do not allow to apply Joint and Hierarchy optimizations over the same dimensions. D4 expansion rate and building time are over 50% higher than D5 and D6 cubes, while Query Latency is good and close to the value of D5. If we perform a further analysis of the query latency, we realize that the design D6 has the worst results in % of queries resolved in less than 3 s. Moreover, D6 obtain the worst results in Drill Down with UHC queries. As we define as a Mandatory Subject dimension, the queries that involves Student UHC dimension and do not include Subject dimensions, require a high computation to resolve the aggregation. This demonstrates the importance of analyze query patterns before to apply Aggregation Groups optimizations and how the execution of the benchmark can help us to realize of this fact. Designs D7 and D8 are based on the D2 design with more derived dimensions (MAX SK REL = 1/10) than D1 (MAX SK REL = 1/1), but defining three hierarchies for nine \"Normal\" dimensions like D5 design. The difference between this design is definition of ID_NUMBER as \"Normal\" dimension in D7 and as \"Derived\" on D8.