_FactLoadParameters
The _FactLoadParameters table maps the facts in the fact table to the dimension elements in the target cube.
It is used by job types 3 (Load Facts) and 9 (Clear Cube Region).
This table describes _FactLoadParameters:
| Column | Type | Description |
|---|---|---|
| Id | Integer | The ID of the fact load. This is the ParameterId on the _FactLoad table. |
| DimIdx | Integer | The index of the dimension of the target cube is zero based. |
| ColumnName | String(50) | The column names in the fact table that relate to the dimension specified by DimIdx. Possible values depend on the TargetElement column. |
| TargetElement | String(71) |
The element name of the dimension specified by |
| TargetElementUnknown | String(71) | If the element name cannot be found in the dimension, the process should use this element name instead. This works no matter if the element name was delivered as a TargetElement or in a column. |
| TargetElementMissing | String(71) | If the element name is empty, the process should use this element name instead. This works no matter if NULL was delivered as a TargetElement or in a column. |
| Comment | Text | User defined comment. |
General information
Target, unknown, and missing elements can be qualified with the name of the hierarchy each belongs to. The two names are separated with a Tab character. The hierarchy name appears first. For example, hierarchyName<tab>elementName.
ColumnName and TargetElement value combinations
| Case number | ColumnName | TargetElement | Comment |
|---|---|---|---|
| 1 | Matches the dimension name | NULL | This is used when defining a single value column fact table. |
| 2 | NULL | Element name of the dimension specified by DimIdx of the target cube. |
The whole fact table relates to a single element of the dimension specified by the DimIdx of the target cube.
This is used when defining a single value column fact table. |
| 3 | Not NULL, does not match the dimension name | Element name of the dimension specified by DimIdx of the target cube. |
Multiple rows with the same DimIdx possible. Only one dimension can have multiple rows for it.
This is used when defining a multiple value column fact table. |
| 4 | NULL | NULL | Not possible |
Example for case 1
This example shows a single value column fact table where no TargetElements are defined and there is a row for each dimension.
_FactLoadParameters
| Id | DimIdx | ColumnName | TargetElement | TargetElementUnknown | TargetElementMissing | Comment |
|---|---|---|---|---|---|---|
| 1 | 0 | Years | NULL | NULL | NULL | NULL |
| 2 | 1 | Regions | NULL | NULL | NULL | NULL |
Fact table
| Years | Regions | Value |
|---|---|---|
| 2019 | Sweden | 2000 |
| 2020 | Sweden | 2100 |
| 2019 | Norway | 1700 |
| 2020 | Norway | 1750 |
Target cube (Years dimension on the columns; Regions dimension on the rows):
| 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | |
|---|---|---|---|---|---|---|
| Denmark | ||||||
| Norway | 1700 | 1750 | ||||
| Finland | ||||||
| Sweden | 2000 | 2100 |
Example for case 2
This example shows a single value column fact table where one dimension uses a TargetElement instead of a column name and there is a row for each dimension.
_FactLoadParameters
| Id | DimIdx | ColumnName | TargetElement | TargetElementUnknown | TargetElementMissing | Comment |
|---|---|---|---|---|---|---|
| 1 | 0 | NULL | 2020 | NULL | NULL | NULL |
| 2 | 1 | Regions | NULL | NULL | NULL | NULL |
Fact table
| Years | Regions | Value |
|---|---|---|
| Sweden | 2100 | |
| Norway | 1750 |
Target cube (Years dimension on the columns; Regions dimension on the rows)
| 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | |
|---|---|---|---|---|---|---|
| Denmark | ||||||
| Norway | 1750 | |||||
| Finland | ||||||
| Sweden | 2100 |
Example for case 3
This example shows a multiple value column fact table where there are multiple rows for one dimension and each row has TargetElement set.
_FactLoadParameters
| Id | DimIdx | ColumnName | TargetElement | TargetElementUnknown | TargetElementMissing | Comment |
|---|---|---|---|---|---|---|
| 1 | 0 | Y2019 | 2019 | NULL | NULL | NULL |
| 2 | 0 | Y2020 | 2020 | NULL | NULL | NULL |
| 3 | 1 | Regions | NULL | NULL | NULL | NULL |
Fact table
| Y2019 | Y2020 | Regions |
|---|---|---|
| 2000 | 2100 | Sweden |
| 1700 | Norway |
Target cube (Years dimension on the columns; Regions dimension on the rows)
| 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | |
|---|---|---|---|---|---|---|
| Denmark | ||||||
| Norway | 1700 | |||||
| Finland | ||||||
| Sweden | 2000 | 2100 |