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
IDand is defined as the concatenation of parent columnCOMPANYand the key columnCODE_PART_VALUE CODErefers to the columnCODE_PART_VALUE, i.e. the account- The parent key column
COMPANY, referred to in theIDcolumn, 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.
NULLidentities have been added inUNION ALLsections, defining one uniqueNULLvalue row for each parent company as well as one row representing the case with both company and account areNULL. For the firstUNION ALLsection the source table is theCOMPANY_FINANCE_TABand the definition means that for each company, one line with the identity'<company>^#'is defined.NULLvalues are defined for all columns except for theIDand the parent columns. Also note that theCODEis defined as aNULLstring. For the secondUNION ALLsection the source table isBI_DUAL_TAB. In this sub select, only theIDcolumn has a nonNULLvalue.- The Materialized View is defined with
BUILD DEFERREDoption. 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 INDEXclause, 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
IDcolumn 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
_DMand 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^DATATYPE=ENUMERATION^PROMPT=Keep Reported Currency as Currency Balance Description^';
COMMENT ON COLUMN &VIEW..KEEP_REPORTING_ENTITY
IS 'FLAGS=A-IUL^DATATYPE=ENUMERATION^PROMPT=Keep Balances Seperated by Reporting Entity^';
COMMENT ON COLUMN &VIEW..KEEP_REPORTING_ENTITY_DESC
IS 'FLAGS=A-IUL^DATATYPE=ENUMERATION^PROMPT=Keep Balances Seperated by Reporting Entity Description^';
Note: If a column comment contains the REF option/flag, it is important to specify the
NOCHECKoption, to avoid errors when deleting records in the referenced LU.
Look at the following example:
COMMENT ON COLUMN DIM_CUSTOMER_INVOICE_TYPE_OL.SERIES_ID
IS 'FLAGS=A-IUL^DATATYPE=STRING(10)^PROMPT=Number Series Id^REF=InvoiceSeries(company)/NOCHECK^'';
By adding /NOCHECK at the end of REF=InvoiceSeries(company) we make sure to avoid error if an invoice series is removed. Since there is no package associated with the dimension, there is no remove method that can be called, so we want simply to avoid referential errors.
The complete dimension file for Data Mart support is named DimAccountDm.apv
Meta Data File¶
The Meta Data file is the last part of the development process for a dimension and all details about what can be defined is described in the Meta Data Model section.
IFS Developer Studio will generate the metadata file as well as all other needed files.
Some things to consider:
- Is this a Standard dimension that is supposed to be visible as part an Information Source or is it a dimension that has another purpose, like e.g. supportive or add-on dimension?
- Make sure that parent information is correct since this will affect both List of Values functionality but also runtime performance.
- What data access types does the dimension support?
- What should be the default data access type?
- Which items should be visible? In most cases the parent items are not visible and the same goes for the
IDitem. - Make sure that the Data Mart view supports the same items as the Online view.
- Which items needs to have extra List of Values information?
- Always make sure to add performance related indicator information. But not the following:
- Initially is it not necessary to define indicator information in the Meta Data since the source views will be scanned during deployment with the aim of automatically finding out columns that are indexed or that are derived.
- After the deployment, either look into the Information Source page for hint item details or use the same feature to export the current Meta Data contents to file and then compare the current work file with the exported one. In both cases it is possible to find out the hint items that have been automatically defined. Check these items and only add items in the Meta Data file that the automatic scanning has not been able to detect.
One example of a Meta Data file for the DIM_ACCOUNT dimension is provided here. In this case both access type Data Mart and On Line are represented.
The file name is MetaData_AccrulBIDimAccount.ins