Difference between Keys Scale and Number of Keys properties in SSIS Aggregate Transformation

Friends,

You might have seen the properties “Keys Scale” and “Number of Keys” in SSIS Aggregate transformation. What is the difference and in which scenario we use these properties ? Like to know, then continue ..

The above two properties are given to improve the performance of the transformation. For the columns with GROUP BY operation in the transformation, we set these TWO properties. Both deals about the number of groups that are expected to result from a Group by operation. For example, if I used Color column from Dimproduct table in AdventureWorks database with GROUP BY operation then by setting these properties we will give a HINT to the server that how many KEYS(Values) are expected. When you specify an appropriate value for Keys or KeyScale, you improve performance because the tranformation is able to allocate adequate memory for the data that the transformation caches. now, what is the difference between these two when both serve the same purpose ?

KeyScale – This will have the following Predefined values. this specifies the approximate number of keys that the aggregation expects. This will take the default of UNSPECIFIED.

• Unspecified – Keys Scale value is not specified.

• Low – 5 Lakh values appx.

• Medium - 50 lakh values appx.

• High – 2.5 cr values appx.

Hope this helps guys !!

Number of Keys – This is an enterable field which accepts the numeric value. If you think the Keys (values) will not be near to either of the above given Key Scales then you can set the value manually using this property.

You need to set any one of the two properties. If both Keys scale and Number of keys are specified, Number of keys takes precedence.