Aggregate Tables are special tables in your model containing Measures that have been aggregated by a selected Attribute or Attributes. Pre-aggregating Measures in this way can speed up certain queries at run time and make analytic queries and dashboards more responsive and easier to use.
For example, an Aggregate Table may contain the Measures, Sales and Costs, aggregated by Country. I.e. there will be a record in the table for each Country in the database with its associated total Sales and Costs. If the query only references Sales, Costs as Measures and Country as the Attribute, then the Pyramid query engine will only need to access the Aggregate Table and read the aggregated values directly, rather than having to read all the individual records and aggregate them at run time. Where there is a large table of individual records to aggregate (perhaps many millions), this can significantly accelerate the query and reduce the time to produce results.
Note: Aggregate Tables are not visible to users of the model in Discover. By default, they are also hidden in the diagram view in the tables section of the data model. Use Show All option in the Data Model ribbon to display them.
Using Aggregate Tables
How to Set an Aggregate Table
To set a table of type aggregate, click on the table concerned in the diagram view then in the properties panel on the right, check the box marked Aggregate Table.
Once a table is marked as type aggregate, it will not, by default, be shown in the diagram view. To see the table, click on the "Show All" button on the ribbon.
Once a table is marked as type aggregate, a new section will appear in the left hand Elements panel, Aggregate Tables, where details of the aggregation table may be configured:
Configuring Aggregate Tables
In order for the Pyramid query engine to understand how to use the aggregate table, it needs to be configured such that each column present in the table is mapped to the appropriate column in the target tables. For example, in an aggregate table where Sales are aggregated by Country, then the Country column needs to mapped to the table and column from where Country originated, in this case, Customer. Similarly, Sales needs to be mapped to the table and column that contains the Sales Measure. Note that for the items being aggregated, the Measure item (indicated by the capital Sigma, Σ) needs to be selected.
- Select the Aggregate Tables option in the Elements panel.
- Then select the table required in the Aggregations panel.
- Map each column to its Target Table and Target Column using the drop downs.
Note: when selecting the item to be aggregated from the Target column, make sure that the Measure is selected, rather than the base column. Measures are indicated with capital Sigma, Σ for sum or other character indicating the measure aggregation method.
The Aggregate Table can also be deleted from this view by clicking on the "X" next to the Aggregate Table name, indicated by the red circle above.
Aggregating from Multiple Tables
In certain models, the Measures may come from more than one table. In this case, a secondary mapping will need to be applied. Consider this Pyramid model:
Here there are two tables containing the numeric data to be analyzed. The revenue metrics are in the Income table and the costs are in the Costs table, indicated above by the red arrows. There is an aggregate table (red rectangle and displayed using Show All Tables) that contains aggregates of sales, quantity, cost, overhead and expenses. These metrics are aggregated by customer using the customerKey (indicated by the green rectangle). Because the transaction level values for revenue and costs are stored in different tables, it is necessary to map the customerKey to both, while the revenue and cost metrics are mapped to their respective tables, shown by the red, green and yellow boxes below. To add the additional mapping, click on the blue plus sign in the blue circle below.
With this mapping, a query that references any of the attributes in the Customer table and measures in the Aggregate table will not need to read the large transaction fact tables, but construct the example query, total sales by country, by combining the Customer table and the Aggregate table to derive the result. This provides a large degree of flexibility of queries without requiring reading of the large transaction tables.
As well as evaluating the query context to determine which tables to use for the required results, Pyramid's Pyrana query engine will also consider metrics on the absolute and relative sizes of the tables concerned to heuristically resolve the most efficient query plan.