Source Relations for Facts

Use this page to learn more how to define source relations for Facts. These relations are essential to make it possible to add custom attributes to a Fact.

Contents

Source Type and Sources

Each custom attribute is based on a source of a specific source type.

Available source types are presented in the below table.

Source Type Source LoV Supported Description
Dimension <dimension id> Yes The source is the identity of an existing dimension
Fact <fact id> Yes The source is the identity of an existing fact
View <view name> Yes The source is the name of an existing view
IAL view <IAL view name> Yes The source is the name of an existing IAL view
Table <table name> Yes The source is the name of an existing table
Select Statement <user defined> No The source is a select statement identified by a user defined identity
Function Call <user defined> No The source is a PL/SQL function identified by a user defined identity
Expression <user defined> No The source is a SQL expression identified by a user defined identity

 

Workflow

The following workflow is suggested:

 

Examples

This section provides examples on most of the supported source types.

Source Type - Dimension

In this example the idea is to add custom attributes from an existing Dimension to a Fact. To be able to do that, it is necessary to first create a relation between the Fact and the Dimension. In the example we will use fact FACT_GL_TRANSACTION and dimension DIM_ACCOUNT.

First the header of the relation is created and then the RMB Suggest Join Info is used to get the relation details suggested.

 LoV (<F8>) is used to identity the Fact attributes that are related to the dimension attribute keys. The Fact attributes are selected, save is performed and the last step is to Approve the relation, using an RMB option.

 

Source Type - Fact

To add custom attributes from one Fact to another Fact is possible but will not be too usable. One case could be when Quick Information Source has been created, and the quick Fact is used as the source. Please note that performance might be affected by joining two transactional sources.

A drawback with Facts is that there is no information about keys, leading to that no relation details will be suggested. The relations thus has to be known and it will be important to specify the correctly.

In the example a relation is created between the fact FACT_GL_TRANSACTION and FACT_GL_BALANCE.

Source Type - View

In this example the idea is to add custom attributes to a Fact from a source view. It is needed to create a relation between the Fact and the view. The fact FACT_GL_TRANSACTION and source view COMPANY will be used.

First the header of the relation is created and then the RMB Suggest Join Info is used to get the relation details suggested.

 LoV (<F8>) is used to identity the Fact attributes that are related to the keys of the view. The Fact attributes are selected, save is performed and the last step is to Approve the relation, using an RMB option.

 

Source Type - IAL View

In this example the idea is to add custom attributes to a Fact from an IAL view. It is needed to create a relation between the Fact and the IAL view. The fact FACT_GL_TRANSACTION and IAL view DIM_COMPANY_BI will be used.

The header is first created. The RMB Suggest Join Info normally does not work since necessary properties are normally not defined for IAL view columns. If the RMB does not provide any details, the relations have to be added manually. When done the relation is approved via a RMB option.

Source Type - Table

In this example the idea is to add custom attributes to a Fact from a table. It is needed to create a relation between the Fact and the table. The fact FACT_GL_TRANSACTION and table ACCOUNTING_YEAR_TAB will be used.

The header is first created and then the RMB Suggest Join Info is used to get the relation details suggested. 

 LoV (<F8>) is used to identity the Fact attributes that are related to the table keys. The Fact attributes are selected, save is performed and the last step is to Approve the relation, using an RMB option.

Source Type - Select Statement

In this example the idea is to add one custom attribute to a Fact by defining a SELECT statement that should fetch the name of a company. A custom attribute always depends on a source relation which means that the SELECT statement definition has to be defined as a source relation.

The header is first created. In the Source ID field a user defined identity should be provided. In the example the identity SQL_COMP_NAME is used. In the field SQL Expression the statement is defined. Some remarks:

 The RMB Suggest Join Info can be used to scan the expression for bind variables that will thus be added as relation details.

 LoV (<F8>) is used to identity the Fact attributes that are related to the bind variables in the statement. The Fact attributes are selected, save is performed and the last step is to Approve the relation, using an RMB option.

Source Type - Function Call

In this example the idea is to add one custom attribute to a Fact by using a function call to fetch the name of a company. A custom attribute always depends on a source relation which means that the function call definition has to be defined as a source relation.

The header is first created. In the Source ID field a user defined identity should be provided. In the example the identity GET_COMP_NAME is used. In the field SQL Expression the function is defined. Some remarks:

 The RMB Suggest Join Info can be used to scan the expression for bind variables that will thus be added as relation details.

 LoV (<F8>) is used to identity the Fact attributes that are related to the bind variables representing the function arguments. The Fact attributes are selected, save is performed and the last step is to Approve the relation, using an RMB option.

 

Source Type - Expression

In this example the idea is to add a row counter to a Fact. This can be achieved by using an expression. A custom attribute always depends on a source relation which means that the constant has to be defined as a source relation. An expression can of course be used for other purposes than to define constants.

The header is first created. In the Source ID field a user defined identity should be provided. In the example the identity ROW_COUNT is used. In the field SQL Expression the constant is defined.

The definition is saved and the last step is to Approve the relation, using an RMB option.

The SQL Expression might contain column references as e.g. in the following examples:

These references might lead to problems when publishing custom attributes related to the fact. Depending on which sources that are referenced, the column references in the SQL Expression might lead to ambiguous column references. To avoid that problem, it is recommended to always prefix columns in SQL expressions with an alias. For a fact relation the alias=f which means that the definition should be done as follows:

 

 

Self Reference

A special case is when there is a need to add an existing attribute from the Fact as a custom attribute. On example could be if it is required to make a Fact attribute visible without modifying the metadata of the Fact. Assume that we want to make the Account attribute visible in tools like Business Reporter and IFS Lobby. A way to do this is to create a custom attribute from the Fact itself, therefore naming this case self-reference.

The definition is done by using the source type Fact and the source is then the fact identity itself. No need to define any details. Save and then approve the relation by using the Approve option.

 

Related Subjects

Overview - Custom Attributes for Information Sources

Custom Attributes for Facts