# A New Big Data Benchmark for OLAP Cube Design Using Data Pre-Aggregation Techniques
## 3 Method
### Proposed Data Model and Workload  
In this section, we describe the data model and workload, two essential components of our proposed benchmark. This data model source is an Apache Hive data warehouse that stores and supports analytics over academic data generated last 15 years in a large university. The fact table described in Table 2, has more than 1,500,000,000 rows with one measure of the academic performance: Academic Credits Approved. Credits can be aggregated by the operations SUM, AVG, MAX and MIN in combination with context of analysis, i.e., the dimensions tables described in Table 3: Academic Year, Student, Gender, Degree and Subject. Moreover, Student is an example of Ultra High Cardinality (UHC) Dimension since it has 500,000 different instances. Furthermore, there are 21,028 different Subjects, thus also Subject dimension has a High Cardinality (HC). The remaining dimensions are not HC dimensions. Such cardinality numbers make this analytic model hardly implementable with a traditional OLAP approach using an RDBMS, thus we consider this scenario more suited to be treated with the reviewed Big Data OLAP approaches [2; 3; 4; 5; 6; 7; 8]. Moreover, the logical data model is based on an Star Schema, one of the most used data schemas for data warehousing [1] due to its demonstrated benefits in reducing query latency. In complex dimensions tables data are de-normalized, i.e., allowing duplicates also as another technique to reduce the number of joins that would involve distributing the information of the dimension in several tables as other schemas such as Snowflake do. Therefore, we assume this data source schema design is a good design, since we are focusing on benchmarking OLAP cubes, and this dataset is suitable to generate a variety of them. Moreover, in order to implement query latency measures, we must define a set of analytical queries over the above proposed data model. These queries should reflect the nature of any OLAP model.To address it, we propose to define a set of queries composed by three groups that implement the following three common patterns we found in OLAP analytics:  
1. **High level Aggregation:** Queries that involve dimensions with few instances, thus the data at this level is very aggregated. 2. **Drill Down:** Queries that involve dimensions with a lot of instances. These queries shows more detailed data than High Level Aggregation queries, thus computation needed during query execution time will be higher. We exclude UHC dimensions (dimensions over 300,000 instances). 3. **Drill Down UHC:** They are as Drill Down queries but including UHC dimensions. Due to UHC dimensions are less frequently used in analytics models, we propose to isolate this scenario. Applying the above criteria, we have generated a set of 30 SQL queries for the proposed data model. As an example the proposed queries, in Figure 1 we show a query that includes aggregation and filtering operations on an UHC dimension column (Student). \begin{table}
\begin{tabular}{c c c c c} \hline \hline
**Fact.** & **Name** & **Rows** & **Measures (Facts)** & **Aggregation Operators** \\ \hline F1 & Academic Performance & 1,500,000,000 & Credits Approved & SUM, AVG, MAX, MIN \\ \hline \hline \end{tabular}
\end{table} 
Table 2: Data Warehouse fact table used for benchmark execution.