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.
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 |
The following workflow is suggested:
This section provides examples on most of the supported source types.
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.
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.
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.
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.
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.
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:
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.
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:
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.
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:
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.