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.
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.
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
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, a.company company_key, CAST (a.accounting_year AS NUMBER) accounting_year_key, CAST (a.accounting_period AS NUMBER) accounting_period_key, CAST (a.posting_combination_id AS NUMBER) posting_combination_id_key, a.simulation_voucher simulation_voucher_key, CAST (a.amount_balance AS NUMBER) balance, CAST (a.debet_balance AS NUMBER) debit_balance, CAST (a.credit_balance AS NUMBER) credit_balance, CAST (a.curr_amount_balance AS NUMBER) curr_amount_balance, CAST (a.curr_debet_balance AS NUMBER) curr_debet_balance, CAST (a.curr_credit_balance AS NUMBER) curr_credit_balance, CAST (a.third_currency_amount_balance AS NUMBER) third_currency_amount_balance, CAST (a.third_currency_debit_balance AS NUMBER) third_currency_debit_balance, CAST (a.third_currency_credit_balance AS NUMBER) third_currency_credit_balance, CAST (a.qty_balance AS NUMBER) qty_balance, CAST (a.accounting_year AS NUMBER) year, CAST (a.accounting_period AS NUMBER) period_in_year, a.simulation_voucher simulation_voucher, a.company dim_company_id, a.company || ''^'' || TO_CHAR(a.accounting_year) || ''^'' || TO_CHAR(a.accounting_period) dim_accounting_period_id, a.company || ''^'' || a.account dim_account_id, a.company || ''^'' || NVL(a.code_b, ''#'') dim_code_b_id, a.company || ''^'' || NVL(a.code_c, ''#'') dim_code_c_id, a.company || ''^'' || NVL(a.code_d, ''#'') dim_code_d_id, a.company || ''^'' || NVL(a.code_e, ''#'') dim_code_e_id, a.company || ''^'' || NVL(a.code_f, ''#'') dim_code_f_id, a.company || ''^'' || NVL(a.code_g, ''#'') dim_code_g_id, a.company || ''^'' || NVL(a.code_h, ''#'') dim_code_h_id, a.company || ''^'' || NVL(a.code_i, ''#'') dim_code_i_id, a.company || ''^'' || NVL(a.code_j, ''#'') dim_code_j_id, a.company || ''^'' || NVL(a.project_id, ''#'') dim_accounting_project_id, NVL(a.project_id, ''#'') dim_project_id, a.company ||''^''|| TO_CHAR(b.date_from, ''YYYY-MM-DD'') dim_rpd_company_period_id, a.company || ''^'' || NVL(a.currency_code, ''#'') dim_currency_code_id, b.date_from acc_period_from, a.account account, a.code_b code_b, a.code_c code_c, a.code_d code_d, a.code_e code_e, a.code_f code_f, a.code_g code_g, a.code_h code_h, a.code_i code_i, a.code_j code_j, a.project_id project_id, a.object_id fa_object_id, a.currency_code currency_code FROM ACCOUNTING_BALANCE_TAB a, ACCOUNTING_PERIOD_TAB b WHERE a.company = b.company AND a.accounting_year = b.accounting_year AND a.accounting_period = b.accounting_period'; ...
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:
ID
column in each dimension.
E.g. the column value representing the dimension
DIM_ACCOUNT
identity is
defined as a.company
|| '^' || a.account
while the identity of dimension
DIM_CODE_B
is defined
as a.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 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
<company>^<code_b>
or as
<company>^#
when
CODE_B IS NULL
.
NUMBER
attributes are casted to NUMBER
.
The reason is to make sure the declarations as NUMBER
,
NUMBER(x)
, NUMBER(x,y)
are represented as data type
NUMBER
in the Data mart view.
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 feature in Solution Manager 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
Solution Manager,
e.g. changing the value to '2010;2012'
when applicable.
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'; Database_SYS.Set_Table_Column (columns_ , 'COMPANY_KEY'); Database_SYS.Set_Table_Column (columns_ , 'ACCOUNTING_YEAR_KEY'); Database_SYS.Set_Table_Column (columns_ , 'ACCOUNTING_PERIOD_KEY'); Database_SYS.Set_Table_Column (columns_ , 'POSTING_COMBINATION_ID_KEY'); Database_SYS.Set_Table_Column (columns_ , 'SIMULATION_VOUCHER_KEY'); Database_Sys.Create_Constraint(table_name_, constraint_name_, columns_, 'P', '&IFSAPP_INDEX', NULL, TRUE, TRUE); Installation_SYS.Reset_Column_Table(columns_); index_name_ := table_name_ || '_IX1'; Database_SYS.Set_Table_Column (columns_, 'DIM_COMPANY_ID'); Database_Sys.Create_Index(table_name_, index_name_, columns_, 'N', '&IFSAPP_INDEX', NULL, TRUE, TRUE); Installation_SYS.Reset_Column_Table(columns_); ...
The complete Materialized View file that creates the MV ACCOUNTING_BALANCE_MV is named Mv_GenledFactGlBalance.cre
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 db value
- The translated db values (client value)
_DM
and is named
FACT_GL_BALANCE_DM
- 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 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
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:
If yes some special write back related attributes have to be specified.
It is important to define the model properties correct when it comes to join. Some tips:
If yes, is are all necessary values available as items?
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:
DIM_COMPANY
DIM_COMPANY_ID
is built using COMPANY
.COMPANY_KEY
.DIM_ACCOUNTING_PERIOD
DIM_ACCOUNTING_PERIOD_ID
is built using COMPANY
, ACCOUNTING_YEAR
and
ACCOUNTING_PERIOD
COMPANY_KEY
, ACCOUNTING_YEAR_KEY
and ACCOUNTING_PERIOD_KEY
.DIM_ACCOUNT
DIM_ACCOUNT_ID
is built using COMPANY
and ACCOUNT
.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 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.