Develop Dimensions

Dimensions in most cases represent basic data entities in a Star Schema model. By connecting dimensions to a fact we get an Information Source. The dimensions provide detailed basic data information and serve the following purposes:

• Enhancing the reporting possibilities/flexibility in tools like IFS Business Reporter, appearing as display, criteria and repeater items.
• Providing List of Values support in IFS Business Reporter
• Supporting detailed transaction information as part of Zoom In and Drill Down functionality in IFS Business Reporter
• Acting as Write Back related items.
• Acting as Add-On / Inherited dimensions that can provide other dimensions with data.
• Supplying performance indicator information.

This section supplies some guidelines how to develop dimensions.

Contents

• Data Access Types
• Dimension Specifics
• Data Mart Development
• Online Development
• Special Dimension Handling
• Dimension Reference Relations

Data Access Types

The IFS Business Reporting & Analysis services framework handles two data access types:

• Data Mart

  Dimension data is in this case processed and stored either in Materialized Views or in snapshot tables in the Oracle database. For this case the following storage layer objects have to be created:

• Materialized View(s) or snapshot tables
• Specific views related to incremental load of snapshot tables
• Dimension View

• Online

  In this case the dimension data is retrieved from the ordinary core tables and/or views. For this case the following storage layer objects have to be created:

• Dimension View

Metadata files:

• A dimension has an associated IFS Business Reporting & Analysis services specific metadata file that describes the Dimension. This is currently an INS file.
• If a dimension supports incremental load, there is a specific metadata file that describes how to handle incremental load for the dimension. This metadata file is currently also an INS file.

Dimension Specifics

Some specific aspects to be considered when creating a dimension:

• A dimension represents a basic data entity in an Information Source and should provide as many meaningful attributes as possible.

  In the database there might be many tables that represents data related to a specific dimension. Defining a dimension then means to de-normalize the database by gathering data from many sources to one.

• Each row in a dimension should have a unique identifier.

  This unique identifier is preferably defined by an item named ID.

  The suggested implementation is to create a value that is the concatenated value of all source key columns, 

  i.e. <key_col1>|| '^' || <key_col2> || ...|| '^' || <key_coln>.

• A dimension should have one item that represents the natural key value for the end-user. This is the dimension code and is preferably named CODE.

  This is either a value from a single source column or a concatenated value from two or more columns.

• A dimension should contain all natural parents, defined as separate items. They should be there regardless of visibility in the e.g. IFS Business Reporter client, mainly to support List of Values functionality.
• The IFS Business Reporting & Analysis services framework does not yet support Enumerations as in the ordinary core framework. To handle Enumerations it is necessary to consider the following two values:

• The database value
• The translated database value

• Handling of NULL value references

• A dimension might not always have a value in a transaction source, i.e. the dimension reference might be NULL.
• For development of Data Mart support, these so called NULL identities should be considered when creating the dimension specific definitions.
• Even if the transaction sources in the domain that owns the dimension, has the dimension identity available on all rows, there might be other domains that wants to use the dimension but where the transaction sources do not have the dimension identity available on all rows. So instead of guessing if NULL identities should be created or not, please make sure to always consider it for Data Mart execution.

• Avoid adding too many derived items.
• List of Values support.

• Find out what dimension item that needs special List of Values configuration.
• Is it necessary to develop supportive dimensions, i.e. dimensions with the main purpose to serve List of Values functionality in Info Services?

Data Mart Development

Development of Data Mart support means that the dimension specific data is collected in one of the following ways:

1. In a Materialized View (sometimes even in more than one MV). This option relates to a Data Mart based on Materialized Views in the Oracle database.
    
2. In a snapshot table, e.g. an ordinary Oracle table. This option relates to a Data Mart based on the framework for Incremental Load.

The dimension information is retrieved by a Data Mart specific dimension view.

Learn more.

Online Development

Development of Online support means that the dimension specific data is retrieved directly from the source tables via an online specific dimension view.

Learn more.

Special Dimension Handling

There some special cases when it comes to development and usage of dimensions:

• Supportive Dimensions
• List of Values support for Info Services
• Add-On dimensions
• Inherited dimensions

Learn more.

Dimension Reference Relations

Components that owns dimensions should define an INS file that specifies the relationship between the dimensions and existing LU entities. If no suitable relation can be found, then of course no file has to be created.

The dimension and LU entity relations are mainly used by the Quick Information Source tool, when trying to connect dimensions to a newly created fact (Information Source) via existing column LU references.

The reference information only supports Online access.

The keys defined for a registered LU entity should match the Online keys for the related dimension. If e.g. referenced LU is Account then this means that the keys are COMPANY and ACCOUNT. The dimension DIM_ACCOUNT represents account information and apart from the ID column that is used for Data Mart access, it also contains the columns COMPANY and CODE. These two columns/items are used when connecting the dimension to a fact for Online access. If now the reference information in a column in a (QIS) fact source view refers to LU Account, then it will be possible, in most cases, to connect the dimension DIM_ACCOUNT to the created Quick Information Source.

The following method can be used to register relations between dimensions and LU entities:

Xlr_Dim_Ref_Relations_API.Reg_Dim_Ref_Relation(ref_name_     IN VARCHAR2,
                                               dimension_id_ IN VARCHAR2)

--The ref_name_ parameter equals to LU entity name related to the dimension_id_

Example:

Xlr_Dim_Ref_Relations_API.Reg_Dim_Ref_Relation('Account', 'DIM_ACCOUNT');

This example shows that the LU Account is related to the dimension DIM_ACCOUNT. The LU Account has COMPANY as parent key and ACCOUNT as key. The dimension DIM_ACCOUNT has one parent item representing company and one key item named CODE. Both these items/columns are used when connecting the dimension to a fact for Online access. This means that is should be possible to add DIM_ACCOUNT to a fact based on a view that has a column referencing LU Account.

Please note the following guidelines:

1. Only add references to high level dimensions, i.e. dimensions that are directly connected to facts. Do not add any information for add-on dimensions.
2. Only register LUs owned by the current component.
3. Do not refer to dimensions that might not be installed at the time when the INS file is deployed. It is OK to refer to dimensions in statically dependent components, but normally this is needed.

   One exception can e.g. be when a component owns a LU that keeps component specific information related to another master/parent LU. In INVOIC component there is a LU named CompanyInvoiceInfo that represents INVOIC specific company data and it would be ok to define a relationship between this LU and the dimension DIM_COMPANY, since DIM_COMPANY is the main dimension container for company information.

Each component adds its own dimension reference relations via an INS file. A template file can be found here.