Quick Start: Aggregate Tables
Key Points: Aggregate Tables
- Aggregate tables are based on the data of a pre-existing table.
- Various aggregation types can be used, such as averages, sums, counts, and minimum and maximum values.
- Adding lookups to an aggregate table, can greatly increase the value for end users, which support the concepts of dimensional modeling.
Key Steps: Aggregate Tables
Create an Aggregate table
- Select Add Aggregate Table under Advanced on a table
- Provide a name for the aggregate table
- Select your GroupBy parameters
- Select your Aggregate parameters
- Finish the table with OK
For more detailed instructions, please see Aggregate Tables, (in the Tables artcile).
Understanding Aggregate Tables
An aggregated table is an aggregated version of another table in your
project. Often, you will not need the transactional level in financial
or sales reports, but only data grouped by business unit or sales team.
This makes the aggregated tables feature very useful if you are doing
reporting directly from you data warehouse as opposed to using, for
instance, SSAS Multidimensional cubes.
With aggregate tables and a few minutes of development time, you have
now provided your analysts with some vital customer data. For more
advanced reporting, a proper data warehouse, cube, or front-end
reporting system will be required, but for highly formatted or ad-hoc
reporting, aggregate tables can be a powerful tool.
- Min: The lowest value of the field in question.
- Max: The highest value of the field in question.
- 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.
After deploying and executing the aggregate table, you should be able to preview your new aggregations.
Add Lookups (Optional)
If the aggregate table feeds a highly standardized report that requires
no more advanced analysis, it can sometimes be a good idea to add
additional information to the aggregate table that you'd ordinarily want
to retain in a dimension table. For example, if your analysts only ever want to see
customer name and number, and no other customer-related data, it can
save time and effort to bring customer name in as a lookup.
For more detailed information, please see Aggregate Tables, (in the Tables article).