Implementation Example - Data Mart version of a Dimension for Account¶
This page provides an example of how to implement the data mart version of a dimension based on account related data. The dimension in the example will get the identity DIM_ACCOUNT.
Note: A dimension should as far as possible always support both Online and Data Mart access. For more details about Online implementation please refer to the Online development page.
Note: Development is done by using IFS Developer Studio. The current page can be used as a reference for general details when it comes to data mart development of a dimension.
Involved Tables¶
Account related information can be found in many tables. However if we look at how Accounting Attributes and Accounting Structures related information is defined, we can see that an account can occur many times; one time for each Accounting Attribute and one time per Accounting Structure. This means that these two sources do not really fit to the model that says that each row in the dimension should be unique. There are however other ways to make use of this type of information, where dimension related data is defined in LUs (tables), where the natural dimension identity can occur many times. Please refer to the Special Dimension Handling section.
The following tables will be involved in creating a MV for accounts:
Table Name | PK Column Name | Table alias |
---|---|---|
ACCOUNTING_CODE_PART_VALUE_TAB | COMPANY | TAB1 |
CODE_PART | ||
CODE_PART_VALUE | ||
ACCOUNT_GROUP_TAB | COMPANY | TAB2 |
ACCNT_GROUP | ||
ACCOUNT_TYPE_TAB | COMPANY | TAB3 |
USER_DEFINED_ACCOUNT_TYPE |
We want to get as much accurate information as possible from these tables. One observation is that the table ACCOUNTING_CODE_PART_VALUE_TAB
contains all code part values and accounts are represented by the condition CODE_PART='A'
.
Model File¶
The model file developed in IFS Developer Studio used to create the account dimension can be downloaded here >>
Creating a MV for Account Related Info¶
The generated Materialized View definition looks as follows:
PROMPT Create Materialized View &MV
DECLARE
stmt_ VARCHAR2(32000);
columns_ Database_SYS.ColumnTabType;
table_name_ VARCHAR2(30) := '&MV';
index_name_ VARCHAR2(30);
constraint_name_ VARCHAR2(30);
BEGIN
dbms_output.put_line('Primary key cascade drop ON &MV');
Database_SYS.Remove_All_Cons_And_Idx('&MV', TRUE);
dbms_output.put_line('Drop Materialized View &MV');
Database_SYS.Remove_Materialized_View('&MV', TRUE);
dbms_output.put_line('Create Materialized View &MV');
stmt_ :=
'CREATE MATERIALIZED VIEW &MV
TABLESPACE &IFSAPP_DATA
BUILD DEFERRED
USING NO INDEX
REFRESH COMPLETE ON DEMAND
AS
SELECT a.company||''^''||a.code_part_value id,<br/> a.company company,<br/> a.code_part_value code,<br/> ''Dummy'' description,<br/> a.accnt_group account_group,<br/> b.description account_group_desc,<br/> a.accnt_type account_type,<br/> c.description account_type_desc,<br/> a.valid_from valid_from,<br/> a.valid_until valid_until,<br/> a.logical_account_type logical_account_type,<br/> a.ledg_flag ledger_account,<br/> a.curr_balance currency_balance,<br/> a.bud_account budget_account_only,<br/> a.tax_flag tax_account,<br/> a.tax_handling_value tax_handling,<br/> a.exclude_from_curr_trans exclude_from_curr_trans,<br/> a.keep_rep_currency keep_rep_currency,<br/> a.keep_reporting_entity keep_reporting_entity<br/> FROM ACCOUNTING_CODE_PART_VALUE_TAB a,<br/> ACCOUNT_GROUP_TAB b,<br/> ACCOUNT_TYPE_TAB c<br/> WHERE a.code_part = ''A''<br/> AND a.company = b.company<br/> AND a.accnt_group = b.accnt_group<br/> AND a.company = c.company<br/> AND a.accnt_type = c.user_defined_account_type<br/> UNION ALL<br/> SELECT company||''^''||''#'' id,<br/> company company,<br/> NULL code,<br/> NULL description,<br/> NULL account_group,<br/> NULL account_group_desc,<br/> NULL account_type,<br/> NULL account_type_desc,<br/> NULL valid_from,<br/> NULL valid_until,<br/> NULL logical_account_type,<br/> NULL ledger_account,<br/> NULL currency_balance,<br/> NULL budget_account_only,<br/> NULL tax_account,<br/> NULL tax_handling,<br/> NULL exclude_from_curr_trans,<br/> NULL keep_rep_currency,<br/> NULL keep_reporting_entity<br/> FROM &TAB2<br/> UNION ALL<br/> SELECT ''#''||''^''||''#'' id,<br/> NULL company,<br/> NULL code,<br/> NULL description,<br/> NULL account_group,<br/> NULL account_group_desc,<br/> NULL account_type,<br/> NULL account_type_desc,<br/> NULL valid_from,<br/> NULL valid_until,<br/> NULL logical_account_type,<br/> NULL ledger_account,<br/> NULL currency_balance,<br/> NULL budget_account_only,<br/> NULL tax_account,<br/> NULL tax_handling,<br/> NULL exclude_from_curr_trans,<br/> NULL keep_rep_currency,<br/> NULL keep_reporting_entity<br/> FROM BI_DUAL_TAB<br/> ';<br/> EXECUTE IMMEDIATE stmt_;<br/>END;<br/>/<br/><br/>
Some observations:
- The unique row identifier is named
ID
and is defined as the concatenation of parent columnCOMPANY
and the key columnCODE_PART_VALUE
CODE
refers to the columnCODE_PART_VALUE
, i.e. the account- The parent key column
COMPANY
, referred to in theID
column, is also defined as a separate column. The main purpose is to support List of values. - Enumeration values are defined with their db value. The reason is that IFS Business Reporting & Analysis services does not have a built in support for Enumerations.
NULL
identities have been added inUNION ALL
sections, defining one uniqueNULL
value row for each parent company as well as one row representing the case with both company and account areNULL
. For the firstUNION ALL
section the source table is theCOMPANY_FINANCE_TAB
and the definition means that for each company, one line with the identity'<company>^#'
is defined.NULL
values are defined for all columns except for theID
and the parent columns. Also note that theCODE
is defined as aNULL
string. For the secondUNION ALL
section the source table isBI_DUAL_TAB
. In this sub select, only theID
column has a nonNULL
value.- The Materialized View is defined with
BUILD DEFERRED
option. This means that the Materialized View is created empty. The Materialized View has to be activated to initialize a first snapshot. - It is important to use the
NO INDEX
clause, since it will prevent Oracle from automatically creating primary key constraints.
Account MV Indexes¶
To enhance performance some indexes are created.
In this case the ID
column will be unique. IFS Developer Studio will automatically generate a primary key constraint on this column.
In the current case the ID
is derived from the COMPANY
and the CODE
, so if an index is created on these two columns it should be a unique index.
The following columns will be indexed
- ID -> primary key constraint; automatically added by the tool
- COMPANY, CODE -> unique index; defined in the model
- ACCOUNT_GROUP, COMPANY -> standad index; defined in the model
Some examples of index definitions:
PROMPT Create constraints and index(s) on Materialized View &MV
DECLARE
columns_ Database_SYS.ColumnTabType;
table_name_ VARCHAR2(30) := '&MV';
index_name_ VARCHAR2(30);
constraint_name_ VARCHAR2(30);
BEGIN
constraint_name_ := table_name_ || '_PK';<br/> Database_SYS.Set_Table_Column (columns_ , 'ID');<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_, 'ACCOUNT_GROUP');<br/> Database_SYS.Set_Table_Column (columns_, 'COMPANY');<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/> index_name_ := table_name_ || '_UX1';<br/> Database_SYS.Set_Table_Column (columns_, 'COMPANY');<br/> Database_SYS.Set_Table_Column (columns_, 'CODE');<br/> Database_Sys.Create_Index(table_name_,<br/> index_name_,<br/> columns_,<br/> 'U',<br/> '&IFSAPP_INDEX',<br/> NULL,<br/> TRUE,<br/> TRUE);<br/> Installation_SYS.Reset_Column_Table(columns_);<br/><br/>END;<br/>/<br/>
Translation Handling¶
IFS Cloud supplies company specific translation support for Accounts.
The translation can either be retrieved via a Get function in the Dimension view or via stored translation info. To speed up queries a bit, the choice in this case is to store Account related translations in a specific MV.
The Materialized View is given the name MV_TRANS=ACCOUNT_TRANSLATION_MV and it will be defined to retrieve translations for both accounts, account group and account types.
CREATE MATERIALIZED VIEW &MV_TRANS
TABLESPACE &IFSAPP_DATA
BUILD DEFERRED
USING NO INDEX
REFRESH COMPLETE ON DEMAND
AS
SELECT key_value company,
attribute_key translation_key,
language_code language_code,
SUBSTR(current_translation,1,100) description,
lu lu,
module module,
key_name dummy
FROM KEY_LU_TRANSLATION_TAB
WHERE lu IN ('Account','AccountGroup','AccountType')
AND module = 'ACCRUL'
AND key_name = 'CompanyKeyLu'
AND rowtype = 'CompanyKeyLuTranslation';
...
-- translation index definition
index_name_ := table_name_||'_IX1';<br/><br/>Database_SYS.Set_Table_Column (columns_ , 'COMPANY');<br/>Database_SYS.Set_Table_Column (columns_ , 'TRANSLATION_KEY');<br/>Database_SYS.Set_Table_Column (columns_ , 'LU');<br/>Database_SYS.Set_Table_Column (columns_ , 'LANGUAGE_CODE');<br/>Database_SYS.Create_Index ( table_name_,<br/> index_name_,<br/> columns_,<br/> 'N',<br/> '&ACCRUL_MVIEW_INDEX',<br/> NULL,<br/> TRUE,<br/> TRUE);<br/><br/>
Some observations
- Company specific translations related to the LUs Account, AccountGroup and AccountType are retrieved.
- The source is the large translation table KEY_LU_TRANSLATION_TAB
- An index has been created to hopefully speed up the queries in runtime.
Note: All MV related definitions that are not supported by IFS Developer Studio should be placed in a component specific file named MV_<component>.CRE
MV Files¶
The complete Materialized View file that creates the Materialized view ACCOUNT_MV, Mv_AccrulDimAccount.cre, can be downloaded.
The component specific MV that in this case only contains translation related MV definitions for Account, MV_Accrul.cre, can be downloaded.
Creating a Dimension View¶
The dimension specific information is published by the dimension view and it will be the source view referenced by IFS Business Reporting & Analysis services.
The goals is to create a Data Mart specific view. Note the following:
- The
ID
column is defined as the first column - Parent columns, related to parent attributes in the model comes next
- Next the
CODE
(key) column - After this all other attributes are defined.
-
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 namedDIM_ACCOUNT_DM
- Translations are retrieved from the special Materialized View for Account related translations. Below is an example where the account description is retrieved from a Materialized View that only contains translations related to a few account dimension attributes such as account code, group and type.
SUBSTR(NVL( (SELECT description
FROM ACCOUNT_TRANSLATION_MV b
WHERE b.company = a.company
AND b.translation_key = a.code
AND b.lu = 'Account'
AND b.language_code =
(SELECT language
FROM fnd_session
)
), a.description),1,100) description,
The view should have view comments. Especially for Info Services support it is important to define the view comments in correct order:
- First parent columns in correct order (according to the view)
- Next the key column (
CODE
) - Then all other columns that should be available as List of Values columns if the view is used in e.g. Info Services.
CREATE OR REPLACE VIEW &VIEW AS
SELECT a.id id,
a.company company,
a.code code,
SUBSTR(NVL( (SELECT description
FROM ACCOUNT_TRANSLATION_MV b
WHERE b.company = a.company
AND b.translation_key = a.code
AND b.lu = 'Account'
AND b.language_code =
(SELECT language
FROM fnd_session
)
), a.description),1,100) description,
a.account_group account_group,
SUBSTR(NVL( (SELECT description
FROM ACCOUNT_TRANSLATION_MV b
WHERE b.company = a.company
AND b.translation_key = a.account_group
AND b.lu = 'AccountGroup'
AND b.language_code =
(SELECT language
FROM fnd_session
)
), a.account_group_desc),1,100) account_group_desc,
a.account_type account_type,
SUBSTR(NVL( (SELECT description
FROM ACCOUNT_TRANSLATION_MV b
WHERE b.company = a.company
AND b.translation_key = a.account_type
AND b.lu = 'AccountType'
AND b.language_code =
(SELECT language
FROM fnd_session
)
),a.account_type_desc),1,100) account_type_desc,
a.valid_from valid_from,
a.valid_until valid_until,
a.logical_account_type logical_account_type,
SUBSTR(Account_Type_Value_API.Decode(a.logical_account_type),1,100) logical_account_type_desc,
a.ledger_account ledger_account,
SUBSTR(Accounting_Ledg_Flag_API.Decode(a.ledger_account),1,100) ledger_account_desc,
a.currency_balance currency_balance,
SUBSTR(Acc_Currency_Balance_Flag_API.Decode(a.currency_balance),1,100) currency_balance_desc,
a.budget_account_only budget_account_only,
SUBSTR(Budget_Account_Flag_API.Decode(a.budget_account_only),1,100) budget_account_only_desc,
a.tax_account tax_account,
SUBSTR(Tax_Account_Flag_API.Decode(a.tax_account),1,100) tax_account_desc,
a.tax_handling tax_handling,
SUBSTR(Tax_Handling_Value_API.Decode(a.tax_handling),1,100) tax_handling_desc,
a.exclude_from_curr_trans exclude_from_curr_trans,
SUBSTR(Fnd_Boolean_API.Decode(a.exclude_from_curr_trans),1,100) exclude_from_curr_trans_desc,
a.keep_rep_currency keep_rep_currency,
SUBSTR(Fnd_Boolean_API.Decode(a.keep_rep_currency),1,100) keep_rep_currency_desc,
a.keep_reporting_entity keep_reporting_entity,
SUBSTR(Fnd_Boolean_API.Decode(a.keep_reporting_entity),1,100) keep_reporting_entity_desc
FROM &MV a
WITH READ ONLY;
COMMENT ON TABLE &VIEW
IS 'LU=&LU^PROMPT=Account^MODULE=&MODULE^';
COMMENT ON COLUMN &VIEW..COMPANY
IS 'FLAGS=PMI--^DATATYPE=STRING(20)/UPPERCASE^PROMPT=Company^';
COMMENT ON COLUMN &VIEW..CODE
IS 'FLAGS=KMI-L^DATATYPE=STRING(20)/UPPERCASE^PROMPT=Account^';
COMMENT ON COLUMN &VIEW..DESCRIPTION
IS 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Account Description^';
COMMENT ON COLUMN &VIEW..ACCOUNT_GROUP
IS 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Account Group^';
COMMENT ON COLUMN &VIEW..ACCOUNT_GROUP_DESC
IS 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Account Group Description^';
COMMENT ON COLUMN &VIEW..ACCOUNT_TYPE
IS 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Account Type^';
COMMENT ON COLUMN &VIEW..ACCOUNT_TYPE_DESC
IS 'FLAGS=AMIUL^DATATYPE=STRING(100)^PROMPT=Account Type Description^';
COMMENT ON COLUMN &VIEW..VALID_FROM
IS 'FLAGS=AMIUL^DATATYPE=DATE^PROMPT=Valid From^';
COMMENT ON COLUMN &VIEW..VALID_UNTIL
IS 'FLAGS=AMIUL^DATATYPE=DATE^PROMPT=Valid Until^';
COMMENT ON COLUMN &VIEW..LOGICAL_ACCOUNT_TYPE
IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Logical Account Type^';
COMMENT ON COLUMN &VIEW..LOGICAL_ACCOUNT_TYPE_DESC
IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Logical Account Type Description^';
COMMENT ON COLUMN &VIEW..LEDGER_ACCOUNT
IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Ledger Xxx Db Value^';
COMMENT ON COLUMN &VIEW..LEDGER_ACCOUNT_DESC
IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Ledger Xxx^';
COMMENT ON COLUMN &VIEW..CURRENCY_BALANCE
IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Currency Balance^';
COMMENT ON COLUMN &VIEW..CURRENCY_BALANCE_DESC
IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Currency Balance Description^';
COMMENT ON COLUMN &VIEW..BUDGET_ACCOUNT_ONLY
IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Budget Account Only^';
COMMENT ON COLUMN &VIEW..BUDGET_ACCOUNT_ONLY_DESC
IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Budget Account Only Description^';
COMMENT ON COLUMN &VIEW..TAX_ACCOUNT
IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Tax Account^';
COMMENT ON COLUMN &VIEW..TAX_ACCOUNT_DESC
IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Tax Account Description^';
COMMENT ON COLUMN &VIEW..TAX_HANDLING
IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Tax Handling^';
COMMENT ON COLUMN &VIEW..TAX_HANDLING_DESC
IS 'FLAGS=AMIUL^DATATYPE=ENUMERATION^PROMPT=Tax Handling Description^';
COMMENT ON COLUMN &VIEW..EXCLUDE_FROM_CURR_TRANS
IS 'FLAGS=A-IUL^DATATYPE=ENUMERATION^PROMPT=Exclude From Curr Translation^';
COMMENT ON COLUMN &VIEW..EXCLUDE_FROM_CURR_TRANS_DESC
IS 'FLAGS=A-IUL^DATATYPE=ENUMERATION^PROMPT=Exclude From Curr Translation Description^';
COMMENT ON COLUMN &VIEW..KEEP_REP_CURRENCY
IS 'FLAGS=A-IUL^DATATYPE=ENUMERATION^PROMPT=Keep Reported Currency as Currency Balance^';
COMMENT ON COLUMN &VIEW..KEEP_REP_CURRENCY_DESC
IS 'FLAGS=A-IUL^DAT