Clearout4

Referring to the _Jobs table once more, Clearout4 has a fact load type of 2. This is a partial load. A partial load restricts the load further than the fact load parameters allow. With the fact load parameters you can choose a whole dimension or a single element from it. But there is the facility to choose any elements from any dimension as well. For this, use the _JobsParameters table.

This table describes _JobParameters:

JobParameter	ParameterSet	DimIdx	ParameterValue
100	1	1	*
100	1	2	USA
100	1	4	May,June,July

The JobParameter of 100 comes from the _Jobs table in a column of the same name.

The ParameterSet column groups clear-outs. You can choose any number for ParameterSet but it must be the same for each area to clear out.

This table shows how the rows of the _ JobsParameters table are used together with the _FactLoadParameters table:

Id	DimIdx	ColumnName	TargetElement
11	0	NULL	2003
11	1	ACTVSBUD	NULL
11	2	REGIONS	NULL
11	3	PRODUCTS	NULL
11	4	MONTHS	NULL
11	5	MEASURES	NULL

The first row of the _JobsParameters table refers to dimension 1 (ACTVSBUD) and has a ParameterValue of an asterisk (*). This means use the whole ACTVSBUD dimension. An asterisk (*) is a wildcard.

The second row instructs the process to restrict to element USA of dimension 2 (REGIONS).

The third row tells the process to use only elements May, June and July of dimension 3 (MONTHS).

A row for dimension 0 (YEARS) must not be included because it already has a target element set in the _FactLoadParameters table.

As an alternative to using a wildcard (*) for ParameterValue, the row can be omitted.

This table describes how these _JobsParameters rows would achieve the same goal:

JobParameter	ParameterSet	DimIdx	ParameterValue
100	1	2	USA
100	1	4	May,June,July

There is the option to define many regions in a single clear-out. This table describes how it can be achieved by expanding the use of ParameterSet:

JobParameter	ParameterSet	DimIdx	ParameterValue
100	1	2	USA
100	1	4	May,June,July
100	2	1	Budget
100	2	2	Canada,Mexico
100	2	4	January,February

This clears away two regions in the same clear-out, each defined by its ParameterSet value. The two regions cleared out are:

For ParameterSet 1:

• The element 2003 of dimension 0 or YEARS (defined in _FactLoadParameters).
• All elements of dimension 1 or ACTVSBUD.
• The element USA of dimension 2 or REGION.
• The elements May, June, and July of dimension 4 or MONTHS
• .

For ParameterSet 2:

• Element 2003 of dimension 0 or YEARS (defined in _FactLoadParameters).
• Element Budget of dimension 1 or ACTVSBUD.
• Elements Canada and Mexico of dimension 2 or REGIONS.
• Elements January and February of dimension 4 or MONTHS.

The two rectangular regions together are cleared out with the same job.