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

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:

    SELECT||''^''||TO_CHAR(a.accounting_year)||''^''||TO_CHAR(a.accounting_period)||''^''||TO_CHAR(a.posting_combination_id)||''^''||a.simulation_voucher id,<br/>                                                                                                                                                 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/>                                                                                                                                                 dim_company_id,<br/>  || ''^'' || TO_CHAR(a.accounting_year) || ''^'' || TO_CHAR(a.accounting_period)                                                                dim_accounting_period_id,<br/>  || ''^'' || a.account                                                                                                                          dim_account_id,<br/>  || ''^'' ||  NVL(a.code_b, ''#'')                                                                                                              dim_code_b_id,<br/>  || ''^'' ||  NVL(a.code_c, ''#'')                                                                                                              dim_code_c_id,<br/>  || ''^'' ||  NVL(a.code_d, ''#'')                                                                                                              dim_code_d_id,<br/>  || ''^'' ||  NVL(a.code_e, ''#'')                                                                                                              dim_code_e_id,<br/>  || ''^'' ||  NVL(a.code_f, ''#'')                                                                                                              dim_code_f_id,<br/>  || ''^'' ||  NVL(a.code_g, ''#'')                                                                                                              dim_code_g_id,<br/>  || ''^'' ||  NVL(a.code_h, ''#'')                                                                                                              dim_code_h_id,<br/>  || ''^'' ||  NVL(a.code_i, ''#'')                                                                                                              dim_code_i_id,<br/>  || ''^'' ||  NVL(a.code_j, ''#'')                                                                                                              dim_code_j_id,<br/>  || ''^'' ||  NVL(a.project_id, ''#'')                                                                                                          dim_accounting_project_id,<br/>           NVL(a.project_id, ''#'')                                                                                                                                 dim_project_id,<br/>  ||''^''|| TO_CHAR(b.date_from, ''YYYY-MM-DD'')                                                                                                 dim_rpd_company_period_id,<br/>  || ''^'' ||  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           =<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.account while the identity of dimension DIM_CODE_B is defined as || '^' ||  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.

                   mv_name_ =>        'ACCOUNTING_BALANCE_MV',  
                   param_name_ =>     'YEAR_CRITERIA',  
                   param_desc_ =>     'Defines from year criteria for accounting balances',  
                   param_datatype_ => 'NUMBER',  
                   param_value_ =>    '2007' );                

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 >=   
                                                          '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 the Solution Manager page 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:

                   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' );                

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' =    
                                                           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 [Solution Manager](../../../../../../045_administration_aurena/255_br_and_a/010_information_sources/050_materialized_views/050_mv_data_filter_parameters/, 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.

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:

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                                                  balance,  
       debit_balance                                            debit_balance,  
       credit_balance                                           credit_balance,  
       curr_amount_balance                                      curr_amount_balance,  
       curr_debet_balance                                       curr_debet_balance,  
       curr_credit_balance                                      curr_credit_balance,  
       third_currency_amount_balance                            third_currency_amount_balance,  
       third_currency_debit_balance                             third_currency_debit_balance,  
       third_currency_credit_balance                            third_currency_credit_balance,  
       qty_balance                                              qty_balance,  
       year                                                     year,  
       period_in_year                                           period_in_year,  
       simulation_voucher                                       simulation_voucher,  
       SUBSTR(Fnd_Boolean_API.Decode(simulation_voucher),1,100) simulation_voucher_desc,  
       dim_company_id                                           dim_company_id,  
       dim_accounting_period_id                                 dim_accounting_period_id,  
       dim_account_id                                           dim_account_id,  
       dim_code_b_id                                            dim_code_b_id,  
       dim_code_c_id                                            dim_code_c_id,  
       dim_code_d_id                                            dim_code_d_id,  
       dim_code_e_id                                            dim_code_e_id,  
       dim_code_f_id                                            dim_code_f_id,  
       dim_code_g_id                                            dim_code_g_id,  
       dim_code_h_id                                            dim_code_h_id,  
       dim_code_i_id                                            dim_code_i_id,  
       dim_code_j_id                                            dim_code_j_id,  
       dim_accounting_project_id                                dim_accounting_project_id,  
       dim_project_id                                           dim_project_id,  
       dim_rpd_company_period_id                                dim_rpd_company_period_id,  
       dim_currency_code_id                                     dim_currency_code_id,  
       acc_period_from                                          acc_period_from,  
       account                                                  account,  
       code_b                                                   code_b,  
       code_c                                                   code_c,  
       code_d                                                   code_d,  
       code_e                                                   code_e,  
       code_f                                                   code_f,  
       code_g                                                   code_g,  
       code_h                                                   code_h,  
       code_i                                                   code_i,  
       code_j                                                   code_j,  
       project_id                                               project_id,  
       fa_object_id                                             fa_object_id,  
       currency_code                                            currency_code  
FROM &MV&nbsp;  
WHERE 'TRUE' = (SELECT Authorize_Comb_Finance_API.Is_Allowed(company_key, posting_combination_id_key) FROM DUAL)  

The complete Data Mart access view definitions for the fact can be found in the file named FactGLBalanceDm.apv

Meta Data File

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

Some things to consider when the model is created:

  • What data access types does the fact support?
  • What should be the default data access type?
  • Does the fact support write back or not? If yes some special write back related attributes have to be specified.
  • Make sure that parent information is correct, typically used by List of values functionality.
  • Make sure that the Data Mart view supports the same attributes as the Online view.
  • Are all supported dimensions covered?
  • Make sure that dimension identity information is correct for each connected dimension.
  • Is the join between the fact and a dimension an exact (inner join) or a non-exact (outer join)? It is important to define the model properties correct when it comes to join. Some tips:

    • If any fact row always contains all necessary attributes to define the dimension identity and the dimension record (instance) always exists, then the join is exact.
    • If any fact row always contains all necessary attributes to define the dimension identity but the dimension record (instance) might not exist, then a non-exact join must be used. E.g. dimensions Company Reporting Period and Reporting Period might not contain rows that match the data on a fact row, since the dimension data is generated while the fact data on a row is what it is.
    • If a fact row might not contain all necessary attributes to define the dimension identity then a non-exact join must be used.
    • Does the fact support drill down to a detailed fact? If yes, is are all necessary values available as items?
    • For Zoom In and Drill Down; which items should be displayed?
    • Is there any Write back specific item information to be defined?
    • Always make sure to add performance related indicator information for fact items and index information.

One example of a Meta Data file for the FACT_GL_BALANCE fact is provided.

The file name is MetaData_GenledBIFactGLBalance.ins and it provides information both for OnLine and Data Mart access.

Note: It is important to define the joiner columns that represent that identity of each connected dimension. The reason is because this information is used when creating special Access Views.

If we look at a few examples of connected dimensions:

  1. Dimension DIM_COMPANY
    1. The identity DIM_COMPANY_ID is built using COMPANY**. 2. Closest joiner column is COMPANY_KEY.
  3. Dimension DIM_ACCOUNT
    1. The identiy DIM_ACCOUNT_ID **`** is built usingCOMPANYandACCOUNT`.
    2. Joiner columns used are COMPANY_KEY `` and ACCOUNT.

With this information at hand we can define the dimension identity information in the metadata file. An extract of the metadata file shows this.

PROMPT Installing dimension id column definitions for dimensions associated with fact &FACT  
&nbsp;  rec_  XLR_META_UTIL_API.Fact_Dim_Id_Def_Public_Rec;  
   rec_.Fact_Id                  := '&FACT';  
   rec_.Dimension_Id             := 'DIM_COMPANY';  
   rec_.Dim_Id_Column_Item_Id    := '&FACT..COMPANY_KEY';  
   rec_.id_item_seq_no           := 1;  
   rec_.id_item_expression       := NULL;  

   rec_.Fact_Id                  := '&FACT';  
   rec_.Dimension_Id             := 'DIM_ACCOUNTING_PERIOD';  
   rec_.Dim_Id_Column_Item_Id    := '&FACT..COMPANY_KEY';  
   rec_.id_item_seq_no           := 1;  
   rec_.id_item_expression       := NULL;  

   rec_.Fact_Id                  := '&FACT';  
   rec_.Dimension_Id             := 'DIM_ACCOUNTING_PERIOD';  
   rec_.Dim_Id_Column_Item_Id    := '&FACT..ACCOUNTING_YEAR_KEY';  
   rec_.id_item_seq_no           := 2;  
   rec_.id_item_expression       := 'TO_CHAR(<item_column>)';  

   rec_.Fact_Id                  := '&FACT';  
   rec_.Dimension_Id             := 'DIM_ACCOUNTING_PERIOD';  
   rec_.Dim_Id_Column_Item_Id    := '&FACT..ACCOUNTING_PERIOD_KEY';  
   rec_.id_item_seq_no           := 3;  
   rec_.id_item_expression       := 'TO_CHAR(<item_column>)';  

   rec_.Fact_Id                  := '&FACT';  
   rec_.Dimension_Id             := 'DIM_ACCOUNT';  
   rec_.Dim_Id_Column_Item_Id    := '&FACT..COMPANY_KEY';  
   rec_.id_item_seq_no           := 1;  
   rec_.id_item_expression       := NULL;  

   rec_.Fact_Id                  := '&FACT';  
   rec_.Dimension_Id             := 'DIM_ACCOUNT';  
   rec_.Dim_Id_Column_Item_Id    := '&FACT..ACCOUNT';  
   rec_.id_item_seq_no           := 2;  
   rec_.id_item_expression       := NULL;  


The rec_.Id_Item_Expression can be used to clarify that a column value is type converted when begin part of a dimension identity definition. The IFS Developer Studio model does however not support this attribute since the information is regarded as obsolete.