Fact tables

The fact tables are classic fact tables, with element names instead of foreign keys to the dimensions. Dimensions where only one element is used can be left out. These are the target elements in the fact load parameters.

The name is stored in _FactLoad.FactTable.

This table describes the fact table:


Column	Type	Description
Names of Columns matching Cube’s Dimensions	String(71)	Element names of the columns’ matching dimensions. There can be any number of these columns, including none.
Name of Column describing Cube’s Facts	Real or String(MAX)	Fact at the coordinates in the cube defined by the elements in the columns and any possible target elements defined in _FactLoadParameters. The name of this column is defined in the RelationColumnName column of _FactLoads. Note: Multiple rows of numeric facts with identical element coordinates are aggregated. Last row only accepted if there are multiple rows of string facts with identical element coordinates. A NULL value is taken as 0 for numeric and "" for character strings. If _FactLoad.FactType is set to 1 then the data type must be Real.
DataVersion	BigInteger	Version number of the data. For incremental load only _FactLoad.FactLoadType = 3). For other load types this must be NULL if the column DataVersion exists.

General information

A fact load needs two objects to define it:

A target cube
A source table

These are identified by a single job object. The name of the job object is found in JobObject of _FactLoad, the name the cube in CubeId and the name of the table in FactTable.

The elements of the dimensions defining the coordinate positions at which to load a fact appear in two possible places:

The columns of the fact table matching the elements’ dimensions.
As target elements in _FactLoadParameters, where their dimensions are each expressed as a zero based index in _FactLoadParameters.DimIdx and each target element itself in _FactLoadParameters.TargetElement for its dimension.

Elements could be qualified with the appropriate hierarchy name, with the two separated with a Tab character. A hierarchy-qualified element would be written in the fact table as:

hierarchyName<tab>elementName

All elements but target elements can appear in the fact table. A 2-dimensional cube with 2 target elements needs just one column whose name is _FactLoads.RelationalColumnName, and a single row to load it.

The whole table might correspond to a single element of one or more dimensions. These would be target elements in _FactLoadParameters. The columns in the fact table would not exist for such elements.

A loading of facts from a fact table takes place with _Jobs.JobType = 3. _Jobs.JobsObject is the job object identifying the load from table to cube.

For an incremental load the row is only selected if _FactLoad.FactChangesTable is NULL (meaning that there is no fact changes table) and DataVersion falls in the range _FactLoad.LastLoadedVersion < DataVersion <= _FactLoad.NewestVersionToLoad. The row is then linked to all other rows with the same element data and the total computed. This total is written to the cube to the cells whose coordinated match the element combination of the linked rows.

Here is a tip for loading cubes containing both numeric and string facts: For loading cubes containing both numeric and string data, separate load processes will be needed for the numerics and strings. This is because only one fact column is allowed in a fact table, which can only be either numeric or string. Then, separate fact load data in _FactLoads must be set up as partial loads. And the job parameters of the measure dimension corresponding to the numeric and string elements appropriately in separate parameter sets.

Fact table creation with SQL Server

This example is for numeric data:

CREATE TABLE [dbo].[my_fact_table /* Generic name */](
   [my_column_1] [nvarchar](71) NOT NULL,  /* Any number of these */
   [my_column_2] [nvarchar](71) NOT NULL,
   [my_column_n] [nvarchar](71) NOT NULL,
   [my_fact_column] [real] NULL, /* Must be present */
   [DataVersion] [bigint] NULL /* Optional */
)

This example is for string data:

CREATE TABLE [dbo].[my_fact_table /* Generic name */](
   [my_column_1] [nvarchar](71) NOT NULL,  /* Any number of these */
   [my_column_2] [nvarchar](71) NOT NULL,
   [my_column_n] [nvarchar](71) NOT NULL,
   [my_fact_column] [nvarchar](MAX) NULL, /* Must be present */ 
   [DataVersion] [bigint] NULL /* Optional */
)