Skip to content

Implementation Example - Data Mart version of a Fact for GL Balance

This page provides an example of how to implement the data mart version of a fact based on General Ledger balances. The entity will in IFS Developer Studio get the name GlBalance, leading to generation of a fact with the identity FACT_GL_BALANCE

Note: It is recommended to always develop the Online and Data Mart access versions of a Fact. For more details about Online implementation please refer to the Online development page.

Involved Tables

The following tables are involved when creating a Materialized View (MV) for accounting balances,

Table Name PK Column Name Table alias
ACCOUNTING_BALANCE_TAB COMPANY TAB1
  ACCOUNTING_YEAR  
  ACCOUNTING_PERIOD  
  POSTING_COMBINATION_ID  
  SIMULATION_VOUCHER  

The PK columns will in the model file be defines as key attributes. These items will be defined as non-visible items. Key attributes can be handy for e.g. Drill Down functionality.

There will also be a reference to the table  ACCOUNTING_CODE_PART_TAB but only as a sub SELECT.

Model File

The model file for the fact entity GL Balance, GlBalance.fact, can be downloaded if needed.

Note: This file is generated/used by IFS Developer Studio and and it is recommended to only use that tool for development

Creating a MV for Accounting Balances

The generated MV definition looks as follows:

...  
stmt_ := 'CREATE MATERIALIZED VIEW &MV  
    TABLESPACE &IFSAPP_DATA  
    BUILD DEFERRED  
    USING INDEX TABLESPACE &IFSAPP_INDEX  
    REFRESH COMPLETE ON DEMAND  
    AS  
    SELECT a.company||''^''||TO_CHAR(a.accounting_year)||''^''||TO_CHAR(a.accounting_period)||''^''||TO_CHAR(a.posting_combination_id)||''^''||a.simulation_voucher id,<br/>           a.company                                                                                                                                                company_key,<br/>           CAST (a.accounting_year AS NUMBER)                                                                                                                       accounting_year_key,<br/>           CAST (a.accounting_period AS NUMBER)                                                                                                                     accounting_period_key,<br/>           CAST (a.posting_combination_id AS NUMBER)                                                                                                                posting_combination_id_key,<br/>           a.simulation_voucher                                                                                                                                     simulation_voucher_key,<br/>           CAST (a.amount_balance AS NUMBER)                                                                                                                        balance,<br/>           CAST (a.debet_balance AS NUMBER)                                                                                                                         debit_balance,<br/>           CAST (a.credit_balance AS NUMBER)                                                                                                                        credit_balance,<br/>           CAST (a.curr_amount_balance AS NUMBER)                                                                                                                   curr_amount_balance,<br/>           CAST (a.curr_debet_balance AS NUMBER)                                                                                                                    curr_debet_balance,<br/>           CAST (a.curr_credit_balance AS NUMBER)                                                                                                                   curr_credit_balance,<br/>           CAST (a.third_currency_amount_balance AS NUMBER)                                                                                                         third_currency_amount_balance,<br/>           CAST (a.third_currency_debit_balance AS NUMBER)                                                                                                          third_currency_debit_balance,<br/>           CAST (a.third_currency_credit_balance AS NUMBER)                                                                                                         third_currency_credit_balance,<br/>           CAST (a.qty_balance AS NUMBER)                                                                                                                           qty_balance,<br/>           CAST (a.accounting_year AS NUMBER)                                                                                                                       year,<br/>           CAST (a.accounting_period AS NUMBER)                                                                                                                     period_in_year,<br/>           a.simulation_voucher                                                                                                                                     simulation_voucher,<br/>           a.company                                                                                                                                                dim_company_id,<br/>           a.company || ''^'' || TO_CHAR(a.accounting_year) || ''^'' || TO_CHAR(a.accounting_period)                                                                dim_accounting_period_id,<br/>           a.company || ''^'' || a.account                                                                                                                          dim_account_id,<br/>           a.company || ''^'' ||  NVL(a.code_b, ''#'')                                                                                                              dim_code_b_id,<br/>           a.company || ''^'' ||  NVL(a.code_c, ''#'')                                                                                                              dim_code_c_id,<br/>           a.company || ''^'' ||  NVL(a.code_d, ''#'')                                                                                                              dim_code_d_id,<br/>           a.company || ''^'' ||  NVL(a.code_e, ''#'')                                                                                                              dim_code_e_id,<br/>           a.company || ''^'' ||  NVL(a.code_f, ''#'')                                                                                                              dim_code_f_id,<br/>           a.company || ''^'' ||  NVL(a.code_g, ''#'')                                                                                                              dim_code_g_id,<br/>           a.company || ''^'' ||  NVL(a.code_h, ''#'')                                                                                                              dim_code_h_id,<br/>           a.company || ''^'' ||  NVL(a.code_i, ''#'')                                                                                                              dim_code_i_id,<br/>           a.company || ''^'' ||  NVL(a.code_j, ''#'')                                                                                                              dim_code_j_id,<br/>           a.company || ''^'' ||  NVL(a.project_id, ''#'')                                                                                                          dim_accounting_project_id,<br/>           NVL(a.project_id, ''#'')                                                                                                                                 dim_project_id,<br/>           a.company ||''^''|| TO_CHAR(b.date_from, ''YYYY-MM-DD'')                                                                                                 dim_rpd_company_period_id,<br/>           a.company || ''^'' ||  NVL(a.currency_code, ''#'')                                                                                                       dim_currency_code_id,<br/>           b.date_from                                                                                                                                              acc_period_from,<br/>           a.account                                                                                                                                                account,<br/>           a.code_b                                                                                                                                                 code_b,<br/>           a.code_c                                                                                                                                                 code_c,<br/>           a.code_d                                                                                                                                                 code_d,<br/>           a.code_e                                                                                                                                                 code_e,<br/>           a.code_f                                                                                                                                                 code_f,<br/>           a.code_g                                                                                                                                                 code_g,<br/>           a.code_h                                                                                                                                                 code_h,<br/>           a.code_i                                                                                                                                                 code_i,<br/>           a.code_j                                                                                                                                                 code_j,<br/>           a.project_id                                                                                                                                             project_id,<br/>           a.object_id                                                                                                                                              fa_object_id,<br/>           a.currency_code                                                                                                                                          currency_code<br/>    FROM ACCOUNTING_BALANCE_TAB a, ACCOUNTING_PERIOD_TAB b<br/>    WHERE a.company           = b.company<br/>   AND   a.accounting_year   = b.accounting_year<br/>   AND   a.accounting_period = b.accounting_period';<br/>   ...<br/>

Note: The above definition only shows the Materialized View definition and not the complete MV file definition. The definition is always defined as a string that is executed via a EXECUTE IMMEDIATE statement in the file.

Some observations:

  • A unique identity named ID is created based on the key attributes in the model.
  • The original key columns are in the model defined as key attributes with a column named as the original column name concatenated with the _key suffix.
  • Next comes measure items and then light items.
  • Next dimension identities and last all key columns of all dimensions that are not defined elsewhere as single columns. These columns are also named joiners.
  • The dimension identities are defined in the same way as for the ID column in each dimension. E.g. the column value representing the dimension DIM_ACCOUNT identity is defined as a.company || '^' || a.account while the identity of dimension DIM_CODE_B is defined as a.company || '^' ||  NVL(a.code_b, '#') We know that there must be an account on each row in Accounting_Balance_Tab so there is no need to check for NULL values. However the Code B can be NULL which means that we have to create an identity even if code_b=NULL; we use the NVL **`** function and build the identity either as^or as^#whenCODE_B IS NULL`.
  • Dimension identities must always be synchronized with the definition of each dimension..
  • All NUMBERattributes are casted to NUMBER. The reason is to make sure the declarations as NUMBER, NUMBER(x), NUMBER(x,y) are represented as data type NUMBERin the Data mart view.

Adding a Refresh Filter Parameter

It is possible to define filter parameters that can be used at refresh time as filter criteria in the Materialized View definition with the objective to reduce number of transactions that are fetched from the source table(s) to the Materialized View.

Note: This is typically a customization

The only way to define a filter parameter is via a call to a PL/SQL API named XLR_MV_CRITERIA_API and preferably the call is added to an INS file.

BEGIN  
  XLR_MV_CRITERIA_API.Define_Criteria_Parameter(  
                   mv_name_ =>        'ACCOUNTING_BALANCE_MV',  
                   param_name_ =>     'YEAR_CRITERIA',  
                   param_desc_ =>     'Defines from year criteria for accounting balances',  
                   param_datatype_ => 'NUMBER',  
                   param_value_ =>    '2007' );                
END;  
/  

This example defines the parameter YEAR_CRITERIA for the Materialized View ACCOUNTING_BALANCE_MV.

The criteria can be added to the Materialized View definition in the following way:

FROM &TAB1 a  
WHERE a.accounting_year_key >=   
      (SELECT XLR_MV_CRITERIA_API.Get_Mv_Number_Parameter('ACCOUNTING_BALANCE_MV',  
                                                          'YEAR_CRITERIA') FROM BI_DUAL_TAB)  

Note1: It is important to make sure that the columns used in the criteria are properly indexed.

Note2: Use BI_DUAL_TAB instead of DUAL. If DUALis used then the MV will never get staleness STALE upon source modifications. Staleness will be either FRESH or UNKNOWN depending on Oracle version and the MV must always have to be refreshed if staleness is UNKNOWN and will never be refreshed if staleness is FRESH.

Note3: A sub select can be beneficial for performance when the criteria is based on functions.

The value of Materialized View parameters can be modified via a page in IFS Cloud Web which means that it is possible to affect the Materialized View refresh without recreating the definition.

It is also possible to filter on more than one value. This can be very useful, e.g. to define a number of years that should apply as filters when refreshing a Materialized View.

Let us redefine the previous parameter. First we remove it and then we add the modified definition:

BEGIN  
       XLR_MV_CRITERIA_API.Remove_Parameter('ACCOUNTING_BALANCE_MV','YEAR_CRITERIA');  
          
       XLR_MV_CRITERIA_API.Define_Criteria_Parameter(  
                   mv_name_ =>        'ACCOUNTING_BALANCE_MV',  
                   param_name_ =>     'YEAR_CRITERIA',  
                   param_desc_ =>     'Defines years to be used as multi value criteria for accounting balances',  
                   param_datatype_ => 'NUMBER',  
                   param_value_ =>    '2008;2009' );                
END;  
/  

Note: Multiple values are supplied separated by a semicolon, e.g. '<value1>;<value2>;...<valueN>'

To handle multi value parameter values the following criteria is added to the Materialized View definition,

FROM &TAB1 a  
WHERE 'TRUE' =    
      (SELECT XLR_MV_CRITERIA_API.Meet_Multiple_Value_Cond('ACCOUNTING_BALANCE_MV',  
                                                           'YEAR_CRITERIA',  
                                                           a.accounting_year) FROM BI_DUAL_TAB)  

The added condition takes the actual value of accounting_year for each row and compares it with the current parameter value of the parameter YEAR_CRITERIA for Materialized View ACCOUNTING_BALANCE_MV.

The value of Materialized View parameter **YEAR_CRITERIA ** can now be modified via an IFS Cloud Web page e.g., changing the value to '2010;2012'when applicable.

Accounting Balance MV Indexes

To enhance performance some indexes are created.

Since the Materialized View is created with the USING NO INDEX option, ORACLE will not automatically create a primary key constraint, based on the key columns in the source table (Accounting_Balance_Tab). This is and advantage since the idea is to let IFS Develop Studio generate a primary key constraint based on the key attributes in the model.

About indexing:

  • The columns representing the key attributes in the model will by IFS Developer Studio be used to create a primary key constraint.
  • DIM_COMPANY_ID
  • DIM_COMPANY_ID, DIM_ACCOUNT_ID, DIM_ACCOUNTING_PERIOD_ID, DIM_CODE_B_ID
  • POSTING_COMBINATION_ID_KEY, DIM_COMPANY_ID

The model for the indexes looks like this:

indexinfo {  
   indexes {  
      index on columns DimCompanyId;  
      index on columns DimCompanyId,DimAccountId,DimAccountingPeriodId,DimCodeBId;  
      index on columns PostingCombinationIdKey,DimCompanyId;  
   }  
}

Index names will be automatically generated.

The generated definitions looks similar to the following examples:

dbms_output.put_line('Create constraints and indexes on Materialized View &MV');  

   constraint_name_ := table_name_ || '_PK';<br/>   Database_SYS.Set_Table_Column (columns_ , 'COMPANY_KEY');<br/>   Database_SYS.Set_Table_Column (columns_ , 'ACCOUNTING_YEAR_KEY');<br/>   Database_SYS.Set_Table_Column (columns_ , 'ACCOUNTING_PERIOD_KEY');<br/>   Database_SYS.Set_Table_Column (columns_ , 'POSTING_COMBINATION_ID_KEY');<br/>   Database_SYS.Set_Table_Column (columns_ , 'SIMULATION_VOUCHER_KEY');<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_, 'DIM_COMPANY_ID');<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/>...

The MV File

The complete Materialized View file that creates the MV ACCOUNTING_BALANCE_MV is named Mv_GenledFactGlBalance.cre

Creating the Fact View

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

IFS Developer Studio will create this Data Mart access view based on the model definition. The view is defined according to the following pattern:

  • The identity column (ID) is defined first
  • Next comes the key columns
  • Next the measures and the light items.
  • Next the dimension identities.
  • And last extra columns, typically for true/natural join, synchronized with the Online definition.
  • 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 FACT_GL_BALANCE_DM

  • We also want to define Row Level Security filtering.

    • This can be done in many ways; function calls, sub selects, joins etc.
    • it is important to implement a solution that has good enough performance. The security filters, i.e. the WHEREclause, is defined in the fact model.
    • In the current example a function call has been used. The function in combination with the SELECT... FROM DUAL has proved to affect the Oracle query optimizer very little and that has been important in order to get the best possible performance for main part of execution cases.

The generated standard fact view looks like this:

CREATE OR REPLACE VIEW &VIEW&nbsp;AS  
SELECT id                                                       id,  
       company_key                                              company_key,  
       accounting_year_key                                      accounting_year_key,  
       accounting_period_key                                    accounting_period_key,  
       posting_combination_id_key                               posting_combination_id_key,  
       simulation_voucher_key                                   simulation_voucher_key,  
       balance