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

1. Select Add Aggregate Table under Advanced on a table
2. Provide a name for the aggregate table
3. Select your GroupBy parameters
4. Select your Aggregate parameters
5. 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.

Aggregation Types

• 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).