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:
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.
- 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.
- Aggregation by department – Number of users, number of logins, total credit and average user’s credit per department.
- 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.
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:
|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|