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 dimensionDIM_ACCOUNT
identity is defined asa.company || '^' || a.account
while the identity of dimensionDIM_CODE_B
is defined asa.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 beNULL
which means that we have to create an identity even ifcode_b=NULL
; we use theNVL
**`** function and build the identity either as
^ or as
^# when
CODE_B IS NULL`. - Dimension identities must always be synchronized with the definition of each dimension..
- All
NUMBER
attributes are casted toNUMBER
. The reason is to make sure the declarations asNUMBER
,NUMBER(x)
,NUMBER(x,y)
are represented as data typeNUMBER
in 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 DUAL
is 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/> 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/> 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 namedFACT_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
WHERE
clause, 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 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