Skip to content

Source Relations for Dimensions

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

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 Dimensions window
  • Create new
  • Use LoV  to list available facts and select the Dimension 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 is the Source ID is user defined or not
    • For non-user defined source types, use 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 is beneficial for performance compared to sub SELECTs and function calls. 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 Dimension 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 with this case is however the 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.
  • 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
  • Perform Suggest Join Info to get suggested relation details created
  • In the detail part
    • Add Fact Item Id for each relation detail, starting from bottom and moving upwards
    • Use LoV  to list available Dimension items
    • The Source Reference is either a column name or an identifier of an attribute
  • Save
  • Perform 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 target Dimension. To be able to do that, it is necessary to first create a relation between the dimensions. In the example we will use the target dimension DIM_ACCOUNT and source dimension DIM_COMPANY

First the header of the relation is created and then the command Suggest Join Info is used to get the relation details suggested, handled by an assistant.

LoV is used to identity the Dimension attributes that are related to the source dimension attribute keys. The Dimension attributes are selected, save is performed and the last step is to approve the relation using the Approve command.

Source Type - Fact

To add custom attributes from one Fact to a Dimension is possible but will not be too usable. There is little point in referencing a transactional source from a dimension. A possible case could be when there is a fact, core specific or created as a Quick Information Source, that more or less represents a dimension converted to a Fact for practical reasons.
Please make sure to use the correct Join Type in this case. To make sure that the result set is limited it is suggested to use Sub Select type, thus making sure that only one row is returned for each dimension row. The query performance when, and if, the custom attribute is used as a filter in a condition, might however lead to bad performance.

In the above case, no source Fact has been selected. The Join Type is set to Sub Select, since that is the suggested type for this case.

Source Type - View

In this example the idea is to add custom attributes to a Dimension from a source view. For that we need to create a relation between the Dimension and the view. The dimension DIM_ACCOUNT and source view COMPANY will be used.

First the header of the relation is created and then use the command Suggest Join Info get the relation details suggested.

LoV is used to identity the Dimension attributes that are related to the source view keys. The Dimension attributes are selected and saved 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 Dimension from an IAL view. For that we need to create a relation between the Dimension and the IAL view. The dimension DIM_ACCOUNT 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 the idea is to add custom attributes to a Dimension from a table. For that we need to create a relation between the Dimension and the table. The dimension DIM_ACCOUNT and table COMPANY_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 identify the Dimension attributes that are related to the table keys. The Dimension attributes are selected and saved save and 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 Dimension 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:

  • 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 Dimension. This is handled by defining join conditions using bind variables, e.g. :company

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 identify the Dimension attributes that are related to the bind variables in the statement. The Dimension 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 Dimension 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 definition.
  • In most cases the function has arguments that has to be connected to the the Dimension. 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 be added as relation details. LoV is used to identify the Dimension attributes that are related to the bind variables in the statement. The Dimension 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 constant to a Dimension. A custom attribute always depends on a source relation which means that a relation has to be created.
An expression can of course be used for other purposes, where one example can be to create a new attribute that is a combination of e.g. Code and Description. The expression can in this case be defined as e.g. "CODE || ' - ' || DESCRIPTION". The tricky think here is to know the column names. The best way to find out is probably to use the Information Sources feature and look up the metadata for the dimension. Also note that referencing the column names directly, means a risk since a column is just a property of an attribute and in rare cases it might be changed.

The header is first created. In the Source ID field a user defined identity should be provided. In the example the identity CODE_NAME 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 action.

However, the above example might lead to problems when publishing custom attributes related to DIM_ACCOUNT. 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 dimension relation the alias=d 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 Dimension as a custom attribute. On example could be if it is required to make a Dimension attribute visible without modifying the existing metadata of the Dimension. In this case the Source Type is Dimension, the Source ID is the dimension identity. No details are needed for this case.

Overview - Custom Attributes for Information Sources

Custom Attributes for Dimensions