Skip to content

Implementation Example - Online version of a Dimension for Account

This page provides an example of how to implement the online version of a dimension based on account related data. The dimension in the example will get the identity DIM_ACCOUNT.

Note: A dimension should as far as possible always support both Online and Data Mart access. For more details about Data Mart implementation please refer to the Data Mart development page.

Involved Tables

Account related information can be found in many tables. However if we look at how Accounting Attributes and Accounting Structures related information is defined, we can see that an account can occur many times; one time for each Accounting Attribute and one time per Accounting Structure. This means that these two sources do not really fit to the model that says that each row in the dimension should be unique. There are however other ways to make use of this type of information, where dimension related data is defined in LUs, tables where the natural dimension identity can occur many times. Please refer to the Special Dimension Handling section.

The following tables will be involved in creating an Online view for accounts,

Table Name PK Column Name Table alias
ACCOUNTING_CODE_PART_VALUE_TAB COMPANY TAB1                   
  CODE_PART  
  CODE_PART_VALUE  

We want to get as much accurate information as possible from these tables. One observation is that the table ACCOUNTING_CODE_PART_VALUE_TAB contains all code part values and accounts are represented by the condition CODE_PART='A'.

Model File

The model file developed in IFS Developer Studio used to create the account dimension can be downloaded here >>

Creating a Dimension View

The dimension specific information is published by the dimension view and it will be the source view referenced by IFS Business Reporting & Analysis services.

Some general notes,

  • The unique row identifier is named ID is defined as the concatenation of the parent column COMPANY and the key column CODE_PART_VALUE (alias is CODE).
  • CODE is defined as the key column and refers to the source column CODE_PART_VALUE
  • The parent key column COMPANY, referred to in the ID column, is defined as a separate column. The main purpose is to support List of values.
  • Enumeration values are defined with their db value and the translated value. The reason is that IFS Business Reporting & Analysis services does not support a handling similar to what is done in the IFS Cloud Web client.
  • Normally no extra NULL `` identities should be defined, i.e. it is a plain view.

The view is defines as follows:

  • The unique identify is represented by the ID column and placed first.
  • Columns related to parent keys will follow next.
  • Next comes the column representing the key column, normally named CODE.
  • After this all other attributes are defined.

  • Enumerations are always defined via two view columns

    • The db value
    • The translated db values (client value)

  • The view has the suffix _OL and is named DIM_ACCOUNT_OL


CREATE OR REPLACE VIEW &VIEW AS  
SELECT a.company||'^'||a.code_part_value                                  id,<br/>      a.company                                                           company,<br/>      a.code_part_value                                                   code,<br/>       SUBSTR(NVL( (SELECT SUBSTR(d.current_translation,1,100)                        <br/>      FROM KEY_LU_TRANSLATION_TAB d<br/>             WHERE d.key_value       = a.company       <br/>             AND   d.attribute_key   = a.code_part_value          <br/>             AND   d.lu              = 'Account'     <br/>             AND d.module            = '&MODULE'<br/>&nbsp;            AND d.key_name          = 'CompanyKeyLu'<br/>             AND d.rowtype           = 'CompanyKeyLuTranslation'<br/>             AND   d.language_code   =                 <br/>             (SELECT language                          <br/>              FROM fnd_session                         <br/>             )                                         <br/>      ), a.description),1,100)                                            description,<br/>      a.accnt_group                                                       account_group,<br/>       SUBSTR(Account_Group_API.Get_Description(<br/>      a.company, a.accnt_group ),1,100)                                   account_group_desc,<br/>      a.accnt_type                                                        account_type,<br/>       SUBSTR(Account_Type_API.Get_Description(<br/>      a.company,a.accnt_type ),1,100)                                     account_type_desc,<br/>      a.valid_from                                                        valid_from,<br/>      a.valid_until                                                       valid_until,<br/>       a.logical_account_type                                             logical_account_type,<br/>       SUBSTR(Account_Type_Value_API.Decode(logical_account_type),1,100)  logical_account_type_desc,<br/>       a.ledg_flag                                                        ledger_account,<br/>       SUBSTR(Accounting_Ledg_Flag_API.Decode(a.ledg_flag),1,100)         ledger_account_desc,<br/>       a.curr_balance                                                     currency_balance,<br/>       SUBSTR(Acc_Currency_Balance_Flag_API.Decode(a.curr_balance),1,100) currency_balance_desc,<br/>       a.bud_account                                                      budget_account_only,<br/>       SUBSTR(Budget_Account_Flag_API.Decode(a.bud_account),1,100)        budget_account_only_desc,<br/>       a.tax_flag                                                         tax_account,<br/>       SUBSTR(Tax_Account_Flag_API.Decode(a.tax_flag),1,100)              tax_account_desc,<br/>       a.tax_handling_value                                               tax_handling,<br/>       SUBSTR(Tax_Handling_Value_API.Decode(a.tax_handling_value),1,100)  tax_handling_desc,<br/>       a.exclude_from_curr_trans                                          exclude_from_curr_trans,<br/>       SUBSTR(Fnd_Boolean_API.Decode(a.exclude_from_curr_trans),1,100)    exclude_from_curr_trans_desc,<br/>       a.keep_rep_currency                                                keep_rep_currency,<br/>       SUBSTR(Fnd_Boolean_API.Decode(a.keep_rep_currency),1,100)          keep_rep_currency_desc,<br/>       a.keep_reporting_entity                                            keep_reporting_entity,<br/>       SUBSTR(Fnd_Boolean_API.Decode(a.keep_reporting_entity),1,100)      keep_reporting_entity_desc<br/>FROM &TAB&nbsp;a<br/>WHERE a.code_part   = 'A'<br/>WITH READ ONLY;<br/>

The view should have view comments. Especially for Info Services support it is important to define the view comments in correct order:

  • First parent columns in correct order (according to the view)
  • Next the key column (CODE)
  • Then all other columns that should be available as List of values columns if the view is used in e.g. IFS Reporting.

Note: Comments for all view columns are automatically created by IFS Developer Studio based in the model.

The following shows the view comment plus the comments for the first three columns related to the online view for accounts


COMMENT ON TABLE &VIEW  
&nbsp;  IS 'LU=&LU^PROMPT=Account^MODULE=&MODULE^';  

COMMENT ON COLUMN &VIEW..COMPANY  
&nbsp;  IS 'FLAGS=PMI--^DATATYPE=STRING(20)/UPPERCASE^PROMPT=Company^';  
COMMENT ON COLUMN &VIEW..CODE  
&nbsp;  IS 'FLAGS=KMI-L^DATATYPE=STRING(20)/UPPERCASE^PROMPT=Account^';  
COMMENT ON COLUMN &VIEW..DESCRIPTION  
...

The complete dimension file for Data Mart support is named DimAccountOl.apv

Meta Data File

The Meta Data file is the last part of the development process for a dimension and all details about what can be defined is described in the metadata model section.

Some things to consider:

  • Is this a Standard dimension that is supposed to be visible as part an Information Source or is it a dimension that has another purpose, like e.g. supportive or add-on dimension?
  • Make sure that parent information is correct since this will affect both List of values functionality but also runtime performance.
  • What data access types does the dimension support?
  • What should be the default data access type? Recommended to define Online access as default.
  • Make sure that the Online view supports the same items as the Data Mart view.
  • Which items needs to have extra List of values information?
  • Always make sure to add performance related indicator information.

One example of a Meta Data file for the DIM_ACCOUNT dimension is provided here, with information defined both for data access types Online and Data Mart.

The file name is MetaData_AccrulBIDimAccount.ins