Aggregated tables enable an organization to create an aggregated version of almost any table in the project. Sometimes it may not be necessary to see the transaction-level detail in financial or sales reports, but only data grouped by business unit, department, or sales team. This makes the aggregated tables feature very useful for reporting directly from the data warehouse as opposed to using OLAP cubes.
Watch the Video!
To add an aggregated table, follow the steps below.
1. In the project tree, under Tables in a data warehouse, right click on the table, you wish to add an aggregated version of, click on Advanced and click on Add Aggregate Table. The Add Aggregrate Data Table window opens.
2. Under GroupBy, the user can choose what columns on the table should be used for grouping the aggregated data. Click on the column to use in the empty list under Table: [table name]. Type a name for the field in Field name. If the column that has been chosen contains date values, click on the list under GroupBy Type to adjust the granularity of the grouping. This can be set to group by second, minute, hour etc. all the way up to year. Note that the same date column can be used multiple times with different GroupBy types. For other data types, the GroupBy type will always be Value.
3. Under Aggregate, the user can choose what columns from the table you wish to have aggregated. Click on the column you wish to use in the empty list under Table: [table name]. Type a name for the field in Field name. Click on the list under Aggregation Type and click on the method you wish to use for calculating the aggregation. The following options are available:
• Min: The lowest value of the field to be aggregated.
• Max: The highest value of the field to be aggregated
• Count: The number of rows.
• Count_Big: Same as count, but is able to count higher than 2^31, because it uses the bigint data type instead of the int data type.
• DistinctCount: The number of unique values in the field.
• Sum: The sum of all row values.
• Average: The average of all row values.
4. Click OK. The aggregated table is added under Tables and can be recognized by its yellow icon.