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.
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:
- Open the Relations for Facts page
- Create new
- Use LoV to list available facts and select the Fact to which custom attributes are about to be added
- Next move to the Source Type field and select the source type via the drop-down
- Next define the Source ID
- The above table specifies if the Source ID is user defined or not
- For non-user defined source types, use the LoV to find the source or type it manually if known
- For user defined source types it is necessary to define the source identity manually. It is suggested to use a string that is easy to understand. Refer to the examples for more information.
- Next give a description
- The Join Type is normally by default the correct one.
- Outer Join
- Should in most cases be used for all source types except for Select Statement, Function Call and Expression. This type leads to that the original source views will be joined with the selected source through a so called Left Outer Join when custom attributes are created.
- To perform a join, instead of a Sub Select or a Function Call, is beneficial for performance. But please note that there is also a risk involved; it will be very important to define the relation details correctly, i.e. how to connect, join, the Fact with the source. If the details are incorrect it might lead to that the result set get much bigger than it should be.
- Sub Select
- Should be used for source types Select Statement, Function Call and Expression. It is possible to use this join type for other source types as well but generally not recommended.
- This type leads to adding a sub SELECT statement when custom attributes are created.
- If this type is used for other source types it also leads to a sub SELECT statement instead of an outer join and it will be guaranteed that only one row is returned from the source. The problem for this case is however risk for bad performance, both read performance and select performance. With select performance means performance when custom attributes based on this relation are used to define a filter condition, e.g. in a IFS Business Reporter report.
- Outer Join
- Field SQL Expression
- Only to be used for source types Select Statement, Function Call and Expression
- Refer to the examples for more info
- Save the definitions
- Use command Suggest Join Info to open an assistant that suggests join details between the entity and the source
- In the detail part
- Add Fact Item ID for each relation detail, starting from bottom and moving upwards
- Use LoV to list available Fact items
- The Source Reference is either a column name or an identifier of an attribute
- Save
- Use RMB Approve to approve the relation, i.e. make it available when defining custom attributes.
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 Fact Information for the relation is created and then the command Suggest Join Info is used to get relation join details suggested.
The command starts an assistant. Use LoV to identity the Fact attributes that are related to the dimension attribute keys. The Fact attributes are selected and the assistant is finalized.
When the assistant ends, the Join Information will show the join details.
Last step is to approve the relation, using the command Approve.
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 command Suggest Join Info is used to get the relation details suggested.
LoV 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 the Approve command.
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 command Suggest Join Info can be used but normally there are no properties defined for IAL view columns, meaning that no suggestions will be provided. Thus, in most case the relations have to be added manually. The last step is to approve the relation using the Approve command.
Source Type - Table¶
In this example, we want to add custom attributes 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 command Suggest Join Info is used to get the relation details suggested.
LoV is used to identity the Fact attributes that are related to the table keys. The Fact attributes are selected and saved. The last step is to approve the relation using the Approve command.
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 account group related to company and account. 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_ACCOUNT_GROUP is used. In the field SQL Expression the statement is defined. Some remarks:
- Clicking on the note icon related to the SQL Expression field opens up a dialog. In non-edit mode it is read-only and in edit mode it can be used to write/modify the statement.
- In most cases there has to be a WHERE condition that connects the used source with the Fact. This is handled by defining join conditions using bind variables, e.g. :company, :account
The command Suggest Join Info can be used to scan the expression for bind variables that will be added as relation details. LoV is used to identity the Fact attributes that are related to the bind variables in the statement. The Fact attributes are selected and saved. The last step is to approve the relation using the Approve command.
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_COMPANY_NAME is used. In the field SQL Expression the function is defined. Some remarks:
- Clicking on the note icon related to the SQL Expression field opens up a dialog. In non-edit mode it is read-only and in edit mode it can be used to write/modify the function call.
- In most cases the function has arguments that have to be connected to the the Fact. This is handled by defining the arguments using bind variables, e.g. :company
The command Suggest Join Info can be used to scan the expression for bind variables that will thus be added as relation details. LoV is used to identity the Fact attributes that are related to the bind variables representing the function arguments. The Fact attributes are selected and saved. The last step is to approve the relation using the Approve command.
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 COUNTER 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 the Approve command.
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 command.