Calculate the cache size

If you calculate the cache size requirements correctly and sufficiently, all cached data will remain in memory and not create files on the server.

Files will be created on the server when

1. The cache space requirements exceed the memory allocated for any of the transformations that create caches: Agg, Rnk, Lkp, Jnr, Srt.
2. Incremental aggragation is used with an aggragator transformation to store the finished aggragated values until the next run
3. Persistant lookup cache is selected to store the lookup caches until they are reused by the next lookup transformation.

Aggregate Cache Calculation
Cache	Calculation	Columns in Cache
Index	# groups [( column size) + 17]	Group-by columns.
Data	# groups[( column size) + 7]	Non group-by input ports used in non-aggregate output expression. Non group-by input/output ports. Local variable ports. Column containing aggregate function (multiply by three).*
* Each aggregate function has different cache space requirements. As a general rule, you can multiply the column containing the aggregate function by three.

Rank Cache Calculation
Cache	Calculation	Columns in Cache
Index	# groups [( column size) + 17]	Group-by columns.
Data	# groups [(# ranks *( column size + 10)) + 20]	Non group-by input ports used in non-aggregate output expression. Non group-by input/output ports. Local variable ports. Rank ports.

Joiner Cache Calculation
Cache	Calculation	Columns in Cache
Index	# master rows [( column size) + 16]	Master column in join conditions.
Data	# master rows [( column size) + 8]	Master column not in join condition and used for output.

Table 19-6. Lookup Cache Calculation
Cache	Calculation	Columns in Cache
Index	# rows in lookup table [( column size) + 16]	Columns in lookup condition.
Data	# rows in lookup table [( column size) + 8]	Connected output ports not in the lookup condition. Return port (for unconnected Lookup transformations).

For more information about each cache, see the separate sections in this chapter.

Cache Column Sizes

When you calculate the column size for each cache, include the size of the data and additional processing requirements.

Table gives the columns sizes for index and data cache calculations:

Column Sizes for Cache Calculations
Datatype	Aggregator, Rank	Joiner, Lookup
Binary	precision + 2	precision + 8 Round to nearest multiple of 8
Date/Time	18	24
Decimal, high precision off (all precision)	10	16
Decimal, high precision on (precision <=18)	18	24
Decimal, high precision on (precision >18, <=28)	22	32
Decimal, high precision on (precision >28)	10	16
Decimal, high precision on (negative scale)	10	16
Double	10	16
Real	10	16
Integer	6	16
Small integer	6	16
NString, NText, String, Text	Unicode mode: 2*(precision + 2) ASCII mode: precision + 3	Unicode mode: 2*(precision + 5) ASCII mode: precision + 9