Skip to content

Implementation Example - Data Mart version of a Dimension for Account

This page provides an example of how to implement the data mart 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 Online implementation please refer to the Online development page.

Note: Development is done by using IFS Developer Studio. The current page can be used as a reference for general details when it comes to data mart development of a dimension.

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 a MV for accounts:

Table Name PK Column Name Table alias
ACCOUNTING_CODE_PART_VALUE_TAB COMPANY TAB1                   
  CODE_PART  
  CODE_PART_VALUE  
ACCOUNT_GROUP_TAB COMPANY TAB2
  ACCNT_GROUP  
ACCOUNT_TYPE_TAB COMPANY TAB3
  USER_DEFINED_ACCOUNT_TYPE  

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

The generated Materialized View definition looks as follows:


PROMPT Create Materialized View &MV  
DECLARE  
   stmt_            VARCHAR2(32000);  
   columns_         Database_SYS.ColumnTabType;  
   table_name_      VARCHAR2(30) := '&MV';  
   index_name_      VARCHAR2(30);  
   constraint_name_ VARCHAR2(30);  
BEGIN  
   dbms_output.put_line('Primary key cascade drop ON &MV');  
   Database_SYS.Remove_All_Cons_And_Idx('&MV', TRUE);  

   dbms_output.put_line('Drop Materialized View &MV');  
   Database_SYS.Remove_Materialized_View('&MV', TRUE);  

   dbms_output.put_line('Create Materialized View &MV');  
   stmt_ :=  
      'CREATE MATERIALIZED VIEW &MV  
       TABLESPACE &IFSAPP_DATA  
       BUILD DEFERRED  
       USING NO INDEX  
       REFRESH COMPLETE ON DEMAND  
       AS  
       SELECT a.company||''^''||a.code_part_value id,<br/>              a.company                           company,<br/>              a.code_part_value                   code,<br/>              ''Dummy''                           description,<br/>              a.accnt_group                       account_group,<br/>              b.description                       account_group_desc,<br/>              a.accnt_type                        account_type,<br/>              c.description                       account_type_desc,<br/>              a.valid_from                        valid_from,<br/>              a.valid_until                       valid_until,<br/>              a.logical_account_type              logical_account_type,<br/>              a.ledg_flag                         ledger_account,<br/>              a.curr_balance                      currency_balance,<br/>              a.bud_account                       budget_account_only,<br/>              a.tax_flag                          tax_account,<br/>              a.tax_handling_value                tax_handling,<br/>              a.exclude_from_curr_trans           exclude_from_curr_trans,<br/>              a.keep_rep_currency                 keep_rep_currency,<br/>              a.keep_reporting_entity             keep_reporting_entity<br/>       FROM   ACCOUNTING_CODE_PART_VALUE_TAB a,<br/>   ACCOUNT_GROUP_TAB b,<br/>   ACCOUNT_TYPE_TAB c<br/>       WHERE a.code_part   = ''A''<br/>   AND    a.company     = b.company<br/>   AND    a.accnt_group = b.accnt_group<br/>   AND    a.company     = c.company<br/>   AND    a.accnt_type  = c.user_defined_account_type<br/>       UNION ALL<br/>       SELECT company||''^''||''#''               id,<br/>              company                             company,<br/>              NULL                                code,<br/>              NULL                                description,<br/>              NULL                                account_group,<br/>              NULL                                account_group_desc,<br/>              NULL                                account_type,<br/>              NULL                                account_type_desc,<br/>              NULL                                valid_from,<br/>              NULL                                valid_until,<br/>              NULL                                logical_account_type,<br/>              NULL                                ledger_account,<br/>              NULL                                currency_balance,<br/>              NULL                                budget_account_only,<br/>              NULL                                tax_account,<br/>              NULL                                tax_handling,<br/>              NULL                                exclude_from_curr_trans,<br/>              NULL                                keep_rep_currency,<br/>              NULL                                keep_reporting_entity<br/>       FROM   &TAB2<br/>&nbsp;      UNION ALL<br/>       SELECT ''#''||''^''||''#''                 id,<br/>              NULL                                company,<br/>              NULL                                code,<br/>              NULL                                description,<br/>              NULL                                account_group,<br/>              NULL                                account_group_desc,<br/>              NULL                                account_type,<br/>              NULL                                account_type_desc,<br/>              NULL                                valid_from,<br/>              NULL                                valid_until,<br/>              NULL                                logical_account_type,<br/>              NULL                                ledger_account,<br/>              NULL                                currency_balance,<br/>              NULL                                budget_account_only,<br/>              NULL                                tax_account,<br/>              NULL                                tax_handling,<br/>              NULL                                exclude_from_curr_trans,<br/>              NULL                                keep_rep_currency,<br/>              NULL                                keep_reporting_entity<br/>       FROM BI_DUAL_TAB<br/>    ';<br/>   EXECUTE IMMEDIATE stmt_;<br/>END;<br/>/<br/><br/>

Some observations:

  • The unique row identifier is named ID and is defined as the concatenation of parent column COMPANY and the key column CODE_PART_VALUE
  • CODE refers to the column CODE_PART_VALUE, i.e. the account
  • The parent key column COMPANY, referred to in the ID column, is also defined as a separate column. The main purpose is to support List of values.
  • Enumeration values are defined with their db value. The reason is that IFS Business Reporting & Analysis services does not have a built in support for Enumerations.
  • NULL identities have been added in UNION ALL sections, defining one unique NULL value row for each parent company as well as one row representing the case with both company and account are NULL. For the first UNION ALL section the source table is the COMPANY_FINANCE_TAB  and the definition means that for each company, one line with the identity '<company>^#' is defined. NULL values are defined for all columns except for the ID and the parent columns. Also note that the CODE is defined as a NULL string. For the second UNION ALL section the source table is BI_DUAL_TAB. In this sub select, only the ID column has a non NULL value.
  • The Materialized View is defined with BUILD DEFERRED option. This means that the Materialized View is created empty. The Materialized View has to be activated to initialize a first snapshot.
  • It is important to use the NO INDEX clause, since it will prevent Oracle from automatically creating primary key constraints.

Account MV Indexes

To enhance performance some indexes are created.

In this case the ID column will be unique. IFS Developer Studio will automatically generate a primary key constraint on this column.

In the current case the ID is derived from the COMPANY and the CODE, so if an index is created on these two columns it should be a unique index.

The following columns will be indexed

  • ID -> primary key constraint; automatically added by the tool
  • COMPANY, CODE -> unique index; defined in the model
  • ACCOUNT_GROUP, COMPANY -> standad index; defined in the model

Some examples of index definitions:


PROMPT Create constraints and index(s) on Materialized View &MV  
DECLARE  
&nbsp;  columns_         Database_SYS.ColumnTabType;  
   table_name_      VARCHAR2(30) := '&MV';  
   index_name_      VARCHAR2(30);  
   constraint_name_ VARCHAR2(30);  
BEGIN  

   constraint_name_ := table_name_ || '_PK';<br/>   Database_SYS.Set_Table_Column (columns_ , 'ID');<br/>   Database_Sys.Create_Constraint(table_name_,<br/>                                  constraint_name_,<br/>                                  columns_,<br/>                                  'P',<br/>                                  '&IFSAPP_INDEX',<br/>&nbsp;                                 NULL,<br/>                                  TRUE,<br/>                                  TRUE);<br/>   Installation_SYS.Reset_Column_Table(columns_);<br/><br/>   index_name_ := table_name_ || '_IX1';<br/>   Database_SYS.Set_Table_Column (columns_, 'ACCOUNT_GROUP');<br/>   Database_SYS.Set_Table_Column (columns_, 'COMPANY');<br/>   Database_Sys.Create_Index(table_name_,<br/>                                  index_name_,<br/>                                  columns_,<br/>                                  'N',<br/>                                  '&IFSAPP_INDEX',<br/>&nbsp;                                 NULL,<br/>                                  TRUE,<br/>                                  TRUE);<br/>   Installation_SYS.Reset_Column_Table(columns_);<br/><br/>   index_name_ := table_name_ || '_UX1';<br/>   Database_SYS.Set_Table_Column (columns_, 'COMPANY');<br/>   Database_SYS.Set_Table_Column (columns_, 'CODE');<br/>   Database_Sys.Create_Index(table_name_,<br/>                                  index_name_,<br/>                                  columns_,<br/>                                  'U',<br/>                                  '&IFSAPP_INDEX',<br/>&nbsp;                                 NULL,<br/>                                  TRUE,<br/>                                  TRUE);<br/>   Installation_SYS.Reset_Column_Table(columns_);<br/><br/>END;<br/>/<br/>

Translation Handling

IFS Cloud supplies company specific translation support for Accounts.

The translation can either be retrieved via a Get function in the Dimension view or via stored translation info. To speed up queries a bit, the choice in this case is to store Account related translations in a specific MV.

The Materialized View is given the name MV_TRANS=ACCOUNT_TRANSLATION_MV and it will be defined to retrieve translations for both accounts, account group and account types.


CREATE MATERIALIZED VIEW &MV_TRANS&nbsp;  
TABLESPACE &IFSAPP_DATA  
BUILD&nbsp;DEFERRED  
USING NO INDEX  
REFRESH COMPLETE ON DEMAND  
AS  
SELECT key_value                                 company,  
       attribute_key                             translation_key,  
       language_code                             language_code,  
       SUBSTR(current_translation,1,100)         description,  
       lu                                        lu,  
       module                                    module,  
       key_name                                  dummy  
FROM KEY_LU_TRANSLATION_TAB  
WHERE lu     IN ('Account','AccountGroup','AccountType')  
AND module   = 'ACCRUL'  
AND key_name = 'CompanyKeyLu'  
AND rowtype  = 'CompanyKeyLuTranslation';  

...  
-- translation index definition  
index_name_ := table_name_||'_IX1';<br/><br/>Database_SYS.Set_Table_Column (columns_ , 'COMPANY');<br/>Database_SYS.Set_Table_Column (columns_ , 'TRANSLATION_KEY');<br/>Database_SYS.Set_Table_Column (columns_ , 'LU');<br/>Database_SYS.Set_Table_Column (columns_ , 'LANGUAGE_CODE');<br/>Database_SYS.Create_Index ( table_name_,<br/>                            index_name_,<br/>                            columns_,<br/>                            'N',<br/>                            '&ACCRUL_MVIEW_INDEX',<br/>                            NULL,<br/>                            TRUE,<br/>                            TRUE);<br/><br/>

Some observations

  • Company specific translations related to the LUs Account, AccountGroup and AccountType are retrieved.
  • The source is the large translation table KEY_LU_TRANSLATION_TAB
  • An index has been created to hopefully speed up the queries in runtime.

Note: All MV related definitions that are not supported by IFS Developer Studio should be placed in a component specific file named MV_<component>.CRE

MV Files

The complete Materialized View file that creates the Materialized view ACCOUNT_MV, Mv_AccrulDimAccount.cre, can be downloaded.

The component specific MV that in this case only contains translation related MV definitions for Account, MV_Accrul.cre, can be downloaded.

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.

The goals is to create a Data Mart specific view. Note the following:

  • The ID column is defined as the first column
  • Parent columns, related to parent attributes in the model comes next
  • Next the CODE (key) column
  • After this all other attributes are defined.
  • Enumeration columns in the Materialized View stores the database value. Always define two view columns for an enumeration

    • The db value
    • The translated db values (client value)
  • The view has the suffix _DM and is named DIM_ACCOUNT_DM

  • Translations are retrieved from the special Materialized View for Account related translations. Below is an example where the account description is retrieved from a Materialized View that only contains translations related to a few account dimension attributes such as account code, group and type.

SUBSTR(NVL( (SELECT description                          
             FROM ACCOUNT_TRANSLATION_MV b  
             WHERE b.company         = a.company         
             AND   b.translation_key = a.code            
             AND   b.lu              = 'Account'         
             AND   b.language_code   =                   
             (SELECT language                            
              FROM fnd_session                           
             )                                           
      ), a.description),1,100)                                                description,  


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. Info Services.

CREATE OR REPLACE VIEW &VIEW&nbsp;AS  
SELECT a.id                                                                   id,  
       a.company                                                              company,  
       a.code                                                                 code,  
       SUBSTR(NVL( (SELECT description                          
                                 FROM ACCOUNT_TRANSLATION_MV b  
             WHERE b.company         = a.company         
             AND   b.translation_key = a.code            
             AND   b.lu              = 'Account'         
             AND   b.language_code   =                   
             (SELECT language                            
              FROM fnd_session                           
             )                                           
      ), a.description),1,100)                                                description,  
       a.account_group                                                        account_group,  
       SUBSTR(NVL( (SELECT description  
             FROM ACCOUNT_TRANSLATION_MV b  
             WHERE b.company         = a.company  
             AND   b.translation_key = a.account_group  
             AND   b.lu              = 'AccountGroup'  
             AND   b.language_code   =  
             (SELECT language  
              FROM fnd_session  
             )  
      ), a.account_group_desc),1,100)                                         account_group_desc,  
       a.account_type                                                         account_type,  
       SUBSTR(NVL( (SELECT description  
             FROM ACCOUNT_TRANSLATION_MV b  
             WHERE b.company         = a.company  
             AND   b.translation_key = a.account_type  
             AND   b.lu              = 'AccountType'  
             AND   b.language_code =  
             (SELECT language  
              FROM fnd_session  
             )  
      ),a.account_type_desc),1,100)                                           account_type_desc,  
       a.valid_from                                                           valid_from,  
       a.valid_until                                                          valid_until,  
       a.logical_account_type                                                 logical_account_type,  
       SUBSTR(Account_Type_Value_API.Decode(a.logical_account_type),1,100)    logical_account_type_desc,  
       a.ledger_account                                                       ledger_account,  
       SUBSTR(Accounting_Ledg_Flag_API.Decode(a.ledger_account),1,100)        ledger_account_desc,  
       a.currency_balance                                                     currency_balance,  
       SUBSTR(Acc_Currency_Balance_Flag_API.Decode(a.currency_balance),1,100) currency_balance_desc,  
       a.budget_account_only                                                  budget_account_only,  
       SUBSTR(Budget_Account_Flag_API.Decode(a.budget_account_only),1,100)    budget_account_only_desc,  
       a.tax_account                                                          tax_account,  
       SUBSTR(Tax_Account_Flag_API.Decode(a.tax_account),1,100)               tax_account_desc,  
       a.tax_handling                                                         tax_handling,  
       SUBSTR(Tax_Handling_Value_API.Decode(a.tax_handling),1,100)            tax_handling_desc,  
       a.exclude_from_curr_trans                                              exclude_from_curr_trans,  
       SUBSTR(Fnd_Boolean_API.Decode(a.exclude_from_curr_trans),1,100)        exclude_from_curr_trans_desc,  
       a.keep_rep_currency                                                    keep_rep_currency,  
       SUBSTR(Fnd_Boolean_API.Decode(a.keep_rep_currency),1,100)              keep_rep_currency_desc,  
       a.keep_reporting_entity                                                keep_reporting_entity,  
       SUBSTR(Fnd_Boolean_API.Decode(a.keep_reporting_entity),1,100)          keep_reporting_entity_desc  
FROM &MV&nbsp;a  
WITH READ ONLY;  

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  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Account Description^';  
   COMMENT ON COLUMN &VIEW..ACCOUNT_GROUP  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Account Group^';  
   COMMENT ON COLUMN &VIEW..ACCOUNT_GROUP_DESC  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Account Group Description^';  
   COMMENT ON COLUMN &VIEW..ACCOUNT_TYPE  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Account Type^';  
   COMMENT ON COLUMN &VIEW..ACCOUNT_TYPE_DESC  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Account Type Description^';  
   COMMENT ON COLUMN &VIEW..VALID_FROM  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=DATE^PROMPT=Valid From^';  
COMMENT ON COLUMN &VIEW..VALID_UNTIL  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=DATE^PROMPT=Valid Until^';  
COMMENT ON COLUMN &VIEW..LOGICAL_ACCOUNT_TYPE  
IS&nbsp;'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Logical Account Type^';  
COMMENT ON COLUMN &VIEW..LOGICAL_ACCOUNT_TYPE_DESC  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Logical Account Type Description^';  
COMMENT ON COLUMN &VIEW..LEDGER_ACCOUNT  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Ledger Xxx Db Value^';  
COMMENT ON COLUMN &VIEW..LEDGER_ACCOUNT_DESC  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Ledger Xxx^';  
COMMENT ON COLUMN &VIEW..CURRENCY_BALANCE  
IS&nbsp;'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Currency Balance^';  
COMMENT ON COLUMN &VIEW..CURRENCY_BALANCE_DESC  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Currency Balance Description^';  
COMMENT ON COLUMN &VIEW..BUDGET_ACCOUNT_ONLY  
IS&nbsp;'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Budget Account Only^';  
COMMENT ON COLUMN &VIEW..BUDGET_ACCOUNT_ONLY_DESC  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Budget Account Only Description^';  
COMMENT ON COLUMN &VIEW..TAX_ACCOUNT  
IS&nbsp;'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Tax Account^';  
COMMENT ON COLUMN &VIEW..TAX_ACCOUNT_DESC  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Tax Account Description^';  
COMMENT ON COLUMN &VIEW..TAX_HANDLING  
IS&nbsp;'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Tax Handling^';  
COMMENT ON COLUMN &VIEW..TAX_HANDLING_DESC  
&nbsp;  IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Tax Handling Description^';  
COMMENT ON COLUMN &VIEW..EXCLUDE_FROM_CURR_TRANS  
IS&nbsp;'FLAGS=A-IUL^DATATYPE=ENUMERATION^PROMPT=Exclude From Curr Translation^';  
COMMENT ON COLUMN &VIEW..EXCLUDE_FROM_CURR_TRANS_DESC  
&nbsp;  IS 'FLAGS=A-IUL^DATATYPE=ENUMERATION^PROMPT=Exclude From Curr Translation Description^';  
COMMENT ON COLUMN &VIEW..KEEP_REP_CURRENCY  
IS&nbsp;'FLAGS=A-IUL^DATATYPE=ENUMERATION^PROMPT=Keep Reported Currency as Currency Balance^';  
COMMENT ON COLUMN &VIEW..KEEP_REP_CURRENCY_DESC  
&nbsp;  IS 'FLAGS=A-IUL^DATATYPE=ENUMERATION^PROMPT=Keep Reported Currency as Currency Balance Description^';  
COMMENT ON COLUMN &VIEW..KEEP_REPORTING_ENTITY  
IS&nbsp;'FLAGS=A-IUL^DATATYPE=ENUMERATION^PROMPT=Keep Balances Seperated by Reporting Entity^';  
COMMENT ON COLUMN &VIEW..KEEP_REPORTING_ENTITY_DESC  
&nbsp;  IS 'FLAGS=A-IUL^DATATYPE=ENUMERATION^PROMPT=Keep Balances Seperated by Reporting Entity Description^';  

Note: If a column comment contains the REF option/flag, it is important to specify the NOCHECK option, to avoid errors when deleting records in the referenced LU.

Look at the following example:


COMMENT ON COLUMN DIM_CUSTOMER_INVOICE_TYPE_OL.SERIES_ID  
   IS 'FLAGS=A-IUL^DATATYPE=STRING(10)^PROMPT=Number Series Id^REF=InvoiceSeries(company)/NOCHECK^'';  

By adding /NOCHECK at the end of REF=InvoiceSeries(company) we make sure to avoid error if an invoice series is removed. Since there is no package associated with the dimension, there is no remove method that can be called, so we want simply to avoid referential errors.

The complete dimension file for Data Mart support is named DimAccountDm.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 Meta Data Model section.

IFS Developer Studio will generate the metadata file as well as all other needed files.

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?
  • Which items should be visible? In most cases the parent items are not visible and the same goes for the ID item.
  • Make sure that the Data Mart view supports the same items as the Online view.
  • Which items needs to have extra List of Values information?
  • Always make sure to add performance related indicator information. But not the following:
    • Initially is it not necessary to define indicator information in the Meta Data since the source views will be scanned during deployment with the aim of automatically finding out columns that are indexed or that are derived.
    • After the deployment, either look into the Information Source page for hint item details or use the same feature to export the current Meta Data contents to file and then compare the current work file with the exported one. In both cases it is possible to find out the hint items that have been automatically defined. Check these items and only add items in the Meta Data file that the automatic scanning has not been able to detect.

One example of a Meta Data file for the DIM_ACCOUNT dimension is provided here. In this case both access type Data Mart and On Line are represented.

The file name is MetaData_AccrulBIDimAccount.ins