_FactLoad

The _FactLoad table contains information about jobs that load cell data ("facts") into the cubes. There are three kinds of fact load: partial, incremental, and full.

This table is used by job types 3 (Load Facts) and 9 (Clear Cube Region).

This table describes _FactLoad:

Column Type Description
JobObject String(50) The job object of the fact load. This identifies the load of a cube from a specific table.
CubeId String(50) The name of the cube to load (with facts).
FactTable Text The name of the table to load the cube from. The cube's name is in CubeId. A sub-query can be used in place of a table name. Must not be NULL for a fact load. Must be NULL for clearing a cube region.
FactChangesTable Text For incremental load only (FactLoadType 3). The name of the table containing version numbers and their related coordinate/key data for processing. The records of this table selected are in the range defined by the values in the LastLoadedVersion and NewestVersionToLoad fields of this table. The corresponding key data link to the fact table for the selection of the fact data with which to update (overwrite) the matching cells in the cube.

The use of a fact changes table is optional. It will only be used if version numbers will not be present in the fact table. If a fact changes table is not used, the value of this field must be NULL. A sub-query can be used in place of a table name.

FactLoadType Integer The fact load type. Enumerator. The values are:
  • 1 = Full Load
  • 2 = Partial Load
  • 3 = Incremental Load

Fact Load Type 3 (Incremental Load) is not allowed for Job Type 9 (Clear Cube Region).

ParameterId Integer The parameter ID. This is the key to dimension-level loading criteria described in _FactLoadParameters.
LastLoadedVersion Integer Version number of last incremental load. Next incremental load beginning from version number is one more than this. Must be NULL for loads other than incremental. Must not be NULL for incremental.
NewestVersionToLoad Integer Load all versions up to and including this version number. Must be NULL for loads other than incremental. Must not be NULL for incremental.
SourceId Integer Data source of cube. This links to the key SourceId of the table _Sources which describes the details of the source.
FactType Integer The data type of the facts described by the current fact load (found in the facts column of the fact table). Enumerator. The values are:
  • 1 = Numeric
  • 2 = Character String
  • 3 = Numeric without summation/aggregation

Further notes:

  • Must not be NULL for Fact Load Type 3 (Load Facts).
  • Must be NULL for Fact Load Type 9 (Clear Cube Region).
RelationalColumnName String(50) The name of the relational column in the fact table describing the facts.

Further notes:

  • Can be NULL for Fact Load Type 3 (Load Facts).
  • Must be NULL for Fact Load Type 9 (Clear Cube Region).
LoadFlags BigInteger Flags controlling the fact load. The following powers of 2 flag the action indicated:
  • 1 = Do not break the import on the first unresolved error that occurs.
  • 2 = Document each error that occurs.
  • 4 = Commit the imported values even if unresolved errors have occurred.
  • 32= Import zero values.
  • 8 = Document replaced missing and unknown elements even if these resulted in successful writing of data to the cube.
  • Combinations of these can be made by adding their values together.
  • Committing data and breaking on error are incompatible.
  • A commit will occur regardless if there are no errors in the load.
  • Default value is 0. A NULL value is interpreted as 0.
Comment Text User defined comment.

General information

This table is only used by job types 3 (Load Facts) and 9 (Clear Cube Region).

A fact load is defined by a source-table-target-cube pair. This metadata table defines the fact loads.

JobObject identifies a fact load. The JobObject column is linked from the _Jobs table column of the same name.

Fact data of type 1 (numeric) is aggregated over all dimension columns with 'group by' and 'sum' to easily handle fact tables with more detail than is loaded into the cube. This is not done for fact data of type 2 (string) as there is no viable default aggregation available there. It is also omitted for fact type 3 (numeric without aggregation), mainly for performance reasons when querying large data-sets that are already pre-aggregated.

The complete picture of a fact load is described by this table together with the _FactLoadParameters table.

For incremental fact loads, the LastLoadedVersion field is updated at the end of the run with the NewestVersionToLoad unless this is -1. If NewestVersionToLoad is -1 the field LastLoadedVersion is updated at the end of the run with the highest version number actually loaded. The NewestVersionToLoad field has to be updated externally.

For incremental loads there are two scenarios, one where the versions are stored in the rows of the fact table, and the other in the rows of a separate fact changes table:

  • Without the fact changes table: additional rows coming in are considered to be deltas, changes to the already existing values. These rows are entered into the cube additively by selected version. The values of loaded data are added to values of existing data with matching key coordinates.
  • With the fact changes table: rows in the fact changes table are considered to mark cells with changed values. Matching existing data is overwritten by the new loaded values. Rows in the fact changes table are selected by version number, and these are linked by key to the corresponding rows in the fact table. All rows of the fact table whose keys match will be selected.

If RelationalColumnName is NULL, then ColumnName and TargetElement of _FactLoadParameters must both be set in at least one row. If it is not NULL, then these cannot both be set in any row.

To commit valid data even if errors have occurred, the 4-bit and the 1-bit must be set. Any value of LoadFlags that entails the 4-bit being set but not the 1-bit will result in an error.