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
_OLand is namedFACT_GL_BALANCE_OL -
Definition of [Row Level] Security.
- Control of access via the function
Bi_Utility_API.Bi_Access_Grantedshould 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.
- Control of access via the function
Note: The fact model in IFS Developer Studio will have to be modified such that all necessary
WHEREconditions, for standard and possible also for optional and alternative access views. are defined via code generation properties. The call toBi_Utility_API.Bi_Access_Grantedwill 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 IS 'LU=&LU^PROMPT=GL 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
NULLhandling, it is important to returnNULLif any of the involved columns areNULL. E.g. theDIM_CODE_B, representing code part B, can beNULLin 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_Grantedto 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
SELECTfromDUALis 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