Skip to content

Implementation Example - Online version of a Fact for GL Balance

This page provides an example of how to implement the online 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 version of a Fact. For more details about Data Mart implementation please refer to the Data Mart development page.

Involved Tables

The following tables are involved when creating an Online view for accounting balances:

Table Name PK Column Name Table alias
ACCOUNTING_BALANCE_TAB COMPANY TAB
  ACCOUNTING_YEAR  
  ACCOUNTING_PERIOD  
  POSTING_COMBINATION_ID  
  SIMULATION_VOUCHER  
ACCOUNTING_CODE_PART_TAB   TAB2
CODESTRING_COMB POSTING_COMBINATION_ID TAB3

The table ACCOUNTING_CODE_PART_TAB is only used in a sub SELECT.

The view CODESTRING_COMB contains all posting combinations, regardless of company, and we know that the table behind the view has one index per code part. For that reason we want to make sure that the code part related columns get their value from this view instead from table ACCOUNTING_BALANCE_TAB. Hopefully this can help Oracle in the true join case where these code string columns will be used as join columns.

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 it is recommended to only use that tool for development

Creating a 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. The view will fetch necessary values from the source tables but must also handle things as:

  • Access
  • Row level security
  • Basic data translations
  • Calculations that must be made at the point of execution.

Note the following:

  • 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/attributes and then light items/attributes.
  • After that comes, normally, the dimension identities.
  • And last comes all key columns of dimensions that are not defined elsewhere as single columns. These columns are also named joiners.
  • Enumerations are always represented by two view columns

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

  • Definition of [Row Level] Security.

    • Control of access via the function Bi_Utility_API.Bi_Access_Granted should always be done in all Online fact views.
    • In the example we also want to add General Ledger specific Row Level Security. This can be done in many ways; function calls, sub selects, joins etc. In the current example a function call has been used. The function has proved to affect the Oracle query optimizer very little and that has been important in order to get the best possible performance for as many cases as possible.

Note: The fact model in IFS Developer Studio will have to be modified such that all necessary WHERE conditions, for standard and possible also for optional and alternative access views. are defined via code generation properties. The call to Bi_Utility_API.Bi_Access_Granted will be added automatically.

The standard online fact view looks like this:


CREATE OR REPLACE VIEW &VIEW 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/>       SUBSTR(Fnd_Boolean_API.Decode(a.simulation_voucher),1,100)                                                                                       simulation_voucher_desc,<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 || '^' || c.account                                                                                                                    dim_account_id,<br/>       DECODE(c.code_b,NULL, TO_CHAR(NULL), a.company || '^' || c.code_b)                                                                               dim_code_b_id,<br/>       DECODE(c.code_c,NULL, TO_CHAR(NULL), a.company || '^' || c.code_c)                                                                               dim_code_c_id,<br/>       DECODE(c.code_d,NULL, TO_CHAR(NULL), a.company || '^' || c.code_d)                                                                               dim_code_d_id,<br/>       DECODE(c.code_e,NULL, TO_CHAR(NULL), a.company || '^' || c.code_e)                                                                               dim_code_e_id,<br/>       DECODE(c.code_f,NULL, TO_CHAR(NULL), a.company || '^' || c.code_f)                                                                               dim_code_f_id,<br/>       DECODE(c.code_g,NULL, TO_CHAR(NULL), a.company || '^' || c.code_g)                                                                               dim_code_g_id,<br/>       DECODE(c.code_h,NULL, TO_CHAR(NULL), a.company || '^' || c.code_h)                                                                               dim_code_h_id,<br/>       DECODE(c.code_i,NULL, TO_CHAR(NULL), a.company || '^' || c.code_i)                                                                               dim_code_i_id,<br/>       DECODE(c.code_j,NULL, TO_CHAR(NULL), a.company || '^' || c.code_j)                                                                               dim_code_j_id,<br/>       DECODE(a.project_id,NULL, TO_CHAR(NULL), a.company || '^' || a.project_id)                                                                       dim_accounting_project_id,<br/>       DECODE(a.project_id,NULL, TO_CHAR(NULL), a.project_id)                                                                                           dim_project_id,<br/>       a.company                    ||'^'||<br/>      TO_CHAR((SELECT p.date_from<br/>      FROM   ACCOUNTING_PERIOD_TAB p<br/>      WHERE  p.company         = a.company<br/>      AND  p.accounting_year   = a.accounting_year<br/>      AND  p.accounting_period = a.accounting_period), 'YYYY-MM-DD')                                                                                    dim_rpd_company_period_id,<br/>       DECODE(a.currency_code,NULL, TO_CHAR(NULL), a.company || '^' || a.currency_code)                                                                 dim_currency_code_id,<br/>       (SELECT p.date_from<br/>      FROM   ACCOUNTING_PERIOD_TAB p<br/>      WHERE  p.company           = a.company<br/>      AND  p.accounting_year   = a.accounting_year<br/>      AND  p.accounting_period = a.accounting_period)                                                                                                   acc_period_from,<br/>       c.account                                                                                                                                        account,<br/>       c.code_b                                                                                                                                         code_b,<br/>       c.code_c                                                                                                                                         code_c,<br/>       c.code_d                                                                                                                                         code_d,<br/>       c.code_e                                                                                                                                         code_e,<br/>       c.code_f                                                                                                                                         code_f,<br/>       c.code_g                                                                                                                                         code_g,<br/>       c.code_h                                                                                                                                         code_h,<br/>       c.code_i                                                                                                                                         code_i,<br/>       c.code_j                                                                                                                                         code_j,<br/>       a.project_id                                                                                                                                     project_id,<br/>       a.object_id                                                                                                                                      fa_object_id,<br/>       a.currency_code                                                                                                                                  currency_code,<br/>       SYSDATE                                                                                                                                          mvt_created_dt<br/>FROM ACCOUNTING_BALANCE_TAB a, CODESTRING_COMB c<br/>WHERE Bi_Utility_API.Bi_Access_Granted = 'TRUE'<br/>AND a.posting_combination_id = c.posting_combination_id<br/>   AND   'TRUE' = (SELECT Authorize_Comb_Finance_API.Is_Allowed(a.company, a.posting_combination_id) FROM DUAL)<br/>WITH READ ONLY;<br/><br/>COMMENT ON TABLE &VIEW&nbsp;IS 'LU=&LU^PROMPT=GL&nbsp;Balance^MODULE=&MODULE^';<br/>

Some remarks:

  • The first column ID represents a unique identity created based on the key attributes in the model.. By default the key attributes are concatenated to create the identity. It is however possible to override this definition with a code generation property in the model. This case is very rather unusual.
  • The columns are defined in the suggested order.
  • Dimension identity columns are created a bit differently than in the Data Mart case. First of all each identity column should have the same definition as the corresponding dimension ID column. When it comes to NULL handling, it is important to return NULL if any of the involved columns are NULL. E.g. the DIM_CODE_B, representing code part B, can be NULL in the transaction table, so the identity definition has to consider this: DECODE(a.code_b, NULL, NULL, a.company ||'^'|| a.code_b). Please note the difference compared to the Data Mart version.
  • The last columns represent dimension key columns that are necessary to be able to define true/natural dimension join. These columns are also called joiners.
  • A call has been made to Bi_Utility_API.Bi_Access_Granted to make sure that the view does not return any data component BIBASE is not part of the installation.
  • The view defines Row Level Security by performing an API call. The SELECT from DUAL is done to reduce the impact on the Oracle optimizer.

The complete fact file for Online support is named FactGLBalanceOl.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:

  • 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 in Business Reporter.
  • Make sure that the Online view supports the same items as the Data Mart view.
  • Are all supported dimensions covered?
  • Does the fact support drill down to a detailed fact? If yes, 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.
  • For the Online case, make sure that true/natural join information has been added for as many as possible of the associated dimensions. Also note that it is not sure the true join will do the trick in all cases and for all dimensions.

A Meta Data file for the FACT_GL_BALANCE fact is provided and it covers both Online and the Data Mart case. The name of the file is MetaData_GenledBIFactGLBalance.ins