Using aggregations

From a data analytics viewpoint, the concept of aggregation tables has been around for a long time. The concept is widely used in SQL Server Analysis Services Multi-Dimensional. Aggregation tables summarize the data at a particular grain and make it available in the data model. While analyzing aggregated data usually performs better at runtime, aggregation typically happens at a higher level of granularity by introducing a new table (or set of tables) containing summarized data.

To enable the users to drill down to a lower grain, we must keep the data in its lowest grain in the data model. We also need to implement a control mechanism to detect the granularity of data the user interacts with in the reporting layer. The calculation happens in the aggregated table if the aggregated data is available in the data model. When the user drills down to the lower grain, the control mechanism runs the calculations in the detail table, which contains more granular data...