Dimension Entity Concept
A Dimension entity normally represents a basic data entity that acts as a planet in a Star Schema model. A more uncommon implementation is to create a dimension that is based on a transactional source. One reason for doing so could be to open up the possibility to use the dimension for List of Values purposes, i.e. as a lookup entity. It is however a risk involved with this type of implementation, since the dimension might represent a huge number of records, something that will not be obvious for a user.
A Dimension entity is in a standard case built to represent as much as possible of available/related information. In the LU model, one LU can reference one or more LUs, while the dimension does not refer to LUs or other dimensions (with some exceptions in the IFS implementation).
The above figure shows the LU Account that has references to LU AccountGroup and AccountType. Each LU handles its own information and logic. But in the dimension case it is very likely that the account can be found in many financial sources while the account group and type cannot be found. So there is no real benefit of building dimensions for these separate LUs. Instead the basic approach would be to create one dimension that includes data from all these LUs.
The figure shows how data is fetched from three sources to represent one Dimension entity. Another difference is that a dimension only has one key and may have one or more parent keys, while a LU may have more than one key attribute.
Each dimension entity contains the following type of attributes:
Represents the natural parent attributes in the dimension. Each parent attribute should be connected to a visible key attribute in a parent dimension, normally named as CODE and having the CodeKey property attached.
Represents the key attribute. A dimension should not have more than one key. The key is normally named CODE. The key almost always has the property CodeKey attached.
Any other attribute except the parent attributes and the key attribute.
There are following data types for dimension attributes
Following are the code generation properties available for dimension attributes. They can be used to alter default properties.
- DbRefPackageName (only for enumerations)
Each dimension has a unique identity. It will be named ID and is generated by the tool. In a standard case it is not necessary to define the identity since it can be built from the parent attributes and the key attribute. There is however a codegenproperty that can be used to define the dimension identity in cases where the automatic generation is not good enough. Note that the identity of a dimension must match the dimension identity in Facts that have a reference to the dimension.
A dimension can have references to parent dimensions. This referential information is normally only used for List of Values functionality. The basic idea is that a dimension only has one key and all other keys are parent keys. Each parent key attribute should refer to a visible key attribute, normally the CODE, in a parent dimension.
In the above figure there is a dimension named DIM_XYZ with the Code attribute as key and the attributes Parent1 and Parent2 as parent keys. Now each parent attribute in DIM_XYZ should refer to (be connected to) a visible key attribute and in the example we get:
- Parent2 attribute (parent) is connected to Code in DIM_PARENT2
- Parent1 attribute (parent) is connected to Code in DIM_PARENT1
- DIM_PARENT2 also has a parent reference to DIM_PARENT1
There are three types of dimensions:
This is the most common dimension type.
An inherited dimension inherits all attributes from a super (parent) dimension. The inherited dimension has its own unique name, display name, description and module relation. It is however not possible to remove or add new attributes. The main purpose is to provide a way to reuse/copy an existing dimension but give it a different name. The time dimension is the best example, where there is a common time dimension that all other time dimensions are inherited from. There are some limitations:
a.Only one level of inheritance is allowed, i.e. it is not allowed to inherit from an inherited dimension.
b.It is not allowed to inherit from a dimension that references add-on attributes. This is due to a technical limitation.
A supportive dimension has the main purpose to support some special scenarios as e.g. a List of Values dimension to be used by the Info Services framework. A supportive dimension cannot be connected to a Fact.
Dimension attributes do not refer to other dimension or fact entities as in the LU model. Still there is some special functionality where other dimensions are referenced:
- Attributes of Enumeration type
- Parent Dimensions
- Add-on Dimensions
- Info Services support
There is a big difference when comparing a Dimension entity with a LU entity. Attributes in a LU entity might have references to another LU. These references are used to check referential consistency, to support List of Values etc. Dimension attributes do not have references to other entities as in the LU case. There are some exceptions where one is attributes of Enumeration type.
The basic idea is to handle enumerations in the following way:
- The attribute representing the db value is defined in the model file as an Enumeration type.
- The Materialized View definition (Data Mart access) only contains the db value column
- The Data Mart access view contains both the db and the client values.
- The Online access view contains both the db and the client values
- The metadata file contains both the db and the client attribute
The dimension attribute named YearStatus is typed as an Enumeration. The enumeration refers to the Enumeration LU AccYearStatus using the syntax Enumeration<AccYearStatus>.The Materialized View will only contain the column Year_Status.
The Data Mart view contains the db value column Year_Status and also the column Year_Status_Desc representing the client value.
The Online view contains the db value column Year_Status and also the column Year_Status_Desc representing the client value.
Finally the metadata (INS) file will contain two attributes, YEAR_STATUS and YEAR_STATUS_DESC.
The implementation means that each enumeration value in the dimension source is available along with the corresponding description in the current user language. The description can typically be used when creating reports in order to display the client value instead of the db value. The db value can be used in a filter criterion, making it language independent. It is also recommended to always make sure that a dimension attribute of type Enumeration has List of Values defined to display the client value when displaying the db value.
List of Values
The List of Values handling for a Dimension differs from the handling in a typical basic data LU. In the LU case, an attribute may have a reference to another LU that can act as the source when performing List of Values. This means that information will be retrieved from another LU than the basic data LU. In the Dimension case, all List of Values requests are performed against the Dimension source itself, since this is not a referential model. It is by default possible to perform List of Values on any visible Dimension attribute in IFS Business Analytics. If no special List of Values instructions have been added to the model then the LoV request will display the current attribute along with its parent attributes. For each Dimension attribute it is possible to specify what attributes to be part of the List of Values. Some recommendations:
- For the key attribute (normally named Code), add other attributes that makes the Dimension easier to understand, e.g. the Description, associated groups, types etc. NOTE that is it not necessary to specify the parent attributes.
- All attributes of Enumeration type should also list the associated description (client) attribute, <attribute_name>Desc.
- Add List Of Values attributes for other main attributes as e.g. type, group etc
Assume that we are using the Account dimensions. If a List of Values request is made to list the available values for attribute Code (column CODE), this means that a select will be made in the Dimension source for that specific column.
This means that only the current dimension source will be used to get the values. For that reason the default behavior is to:
- Always perform a DISTINCT SELECT
- Select all rows, i.e. there is no upper max limit as in the Fact case.
The default behavior can be overridden by codegenproperties.
It is however not recommended to change the default behavior.
In the above example the Code (key) attribute has quite a few other attributes defined as extra List of Values attributes, with the purpose to better describe the key (and the dimension). Secondly the Enumeration type attribute LogicalAccountType also has the client value attribute LogicalAccountTypeDesc as List of Values attributes.
Add On Dimensions
With Add On dimensions means the possibility to add attributes from one dimension to another. In the standard case each row in a dimension view is a unique row, identified by one column preferably called ID, and all information is available in one component. There are however some important exceptions:
- Information related to a dimension might exist in many components, where each component has its share of the dimension related data.
- Dimension related information might exist in another dimension where the dimension identity appears many times.
For both these cases a concept called Add-On dimensions can be used. Since dimensions are owned by different components it might be so that the total dimension content differs depending on currently installed components.
In the above example the DIM_ACCOUNT dimensions is owned by component ACCRUL. The dimension DIM_ANALYTIC_STRUCT_ACCOUNT is owned by component GENLED and defines account related structure data. When GENLED is installed, the dimension DIM_ANALYTIC_STRUCT_ACCOUNT is created but the metadata will also contain instructions to add some of its attributes to the dimension DIM_ACCOUNT.