Data today grows dramatically, and data aggregation is a must have. Warehouse aggregate tables should be generated and maintained constantly, based on the business needs.
PartitionDB ‘Generate Aggregation’ Add-in can help you simplify this process. Running one command will yield the desired aggregate table. Not happy with it? Create a new one. Definitely, worthwhile trying…

Here is an example:

In order to run this example please download our PdbLogic.

This example creates a database ‘TestAggregationDB’ and a table ‘Users’. ‘Users’ entries include user ID, user’s role, user’s department, number of times the user logged in to the system and total credit of the user.
We assume this table can get very big within a short period of time.

Then three different aggregate tables are created. Obviously, there are many more valuable tables that can be generated. This is to the discretion of the business.

  1. Total aggregation – This table shows the total number of users, number of distinct users, total number of times the user logged in, total credit and average credit for user.
  2. Aggregation by department – Number of users, number of logins, total credit and average user’s credit per department.
  3. Aggregation by role –  Number of users, number of logins and average credit per user’s role.

Note that the data aggregation allows using filters, in which only specific departments are considered for the aggregation. This gives more flexibility and control over your aggregated data.

See below the three aggregate tables that were generated:

Start Aggregating

We showed that generating aggregate tables can be a very quick process. Using aggregate data will sure save time for your business.
Looking for data trends and courses is more available than before using PartitionDB Add-In.

Below is  a list of parameters to pass to PdbgenerateAggregations:

Parameter Description Mandatory
SourceDatabaseName Name of source database Yes
SourceSchemaName Name of source schema No
SourceObjectName Name of source object Yes
TargetDatabaseName Name of target (aggregate) database Yes
TargetTables Names of target (aggregate) tables Yes
DimensionColumns Name of column to aggregate by Yes
MeasureColumns Names of columns to aggregate Yes
SourceLinkedServerName Name of source linked server No
TargetLinkedServerName Name of target linked server No
IsRebuildIndexes Should indexes be rebuilt after aggregation No
IsBuildAggregationIndexes Should indexes be rebuilt at aggregated tables No
IsOutputScript Decide if to print the command or run it No

To use generate aggregation Add-in and other PartitionDB Add-ins, download our PdbLogic and attach to your database.
To learn more about PartitionDB capabilities check out our blog.