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.
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|
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
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
IDis defined as the concatenation of the parent column
COMPANYand the key column
CODEis defined as the key column and refers to the source column
- The parent key column
COMPANY, referred to in the
IDcolumn, 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 Aurena 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
IDcolumn and placed first.
- Columns related to parent keys will follow next.
- Next comes the column representing the key column, normally named
- 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
_OLand is named
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/> 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 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 (
- 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 IS 'LU=&LU^PROMPT=Account^MODULE=&MODULE^'; COMMENT ON COLUMN &VIEW..COMPANY IS 'FLAGS=PMI--^DATATYPE=STRING(20)/UPPERCASE^PROMPT=Company^'; COMMENT ON COLUMN &VIEW..CODE 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