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.

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 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:

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:

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.

 

Related Subjects

Overview - Custom Attributes for Information Sources

Custom Attributes for Dimensions