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
IDcolumn in each dimension. E.g. the column value representing the dimensionDIM_ACCOUNTidentity is defined asa.company || '^' || a.accountwhile the identity of dimensionDIM_CODE_Bis 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 beNULLwhich means that we have to create an identity even ifcode_b=NULL; we use theNVL**`** 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 toNUMBER. The reason is to make sure the declarations asNUMBER,NUMBER(x),NUMBER(x,y)are represented as data typeNUMBERin 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_IDDIM_COMPANY_ID, DIM_ACCOUNT_ID, DIM_ACCOUNTING_PERIOD_ID, DIM_CODE_B_IDPOSTING_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
_DMand 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
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 DUALhas 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 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
WHERE 'TRUE' = (SELECT Authorize_Comb_Finance_API.Is_Allowed(company_key, posting_combination_id_key) FROM DUAL)
WITH READ ONLY
;
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:
- Dimension
DIM_COMPANY- The identity
DIM_COMPANY_IDis builtusing COMPANY**. 2. Closest joiner column isCOMPANY_KEY.
- The identity
- Dimension
DIM_ACCOUNTING_PERIOD- The identiy
DIM_ACCOUNTING_PERIOD_ID``` is built usingCOMPANY,ACCOUNTING_YEARandACCOUNTING_PERIOD` - Joiner columns used are
COMPANY_KEY,ACCOUNTING_YEAR_KEYandACCOUNTING_PERIOD_KEY.
- The identiy
- Dimension
DIM_ACCOUNT- The identiy
DIM_ACCOUNT_ID**`** is built usingCOMPANYandACCOUNT`. - Joiner columns used are
COMPANY_KEY`` and ACCOUNT.
- The identiy
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
DECLARE
rec_ XLR_META_UTIL_API.Fact_Dim_Id_Def_Public_Rec;
BEGIN
--
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;
XLR_META_UTIL_API.Install_Fact_Dim_Id_Def(rec_);
--
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;
XLR_META_UTIL_API.Install_Fact_Dim_Id_Def(rec_);
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>)';
XLR_META_UTIL_API.Install_Fact_Dim_Id_Def(rec_);
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>)';
XLR_META_UTIL_API.Install_Fact_Dim_Id_Def(rec_);
--
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;
XLR_META_UTIL_API.Install_Fact_Dim_Id_Def(rec_);
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;
XLR_META_UTIL_API.Install_Fact_Dim_Id_Def(rec_);
--
...
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.