Dimension Data Mart Development 

Data Mart development of a dimension means creating a (snapshot) table with calculated/derived information. There are two different Data Mart source types; one based on Materialized Views and one based on an ordinary table supported by incremental load.

This page deals with the source type Materialized Views. Data Mart development based on incremental load is found on a separate page named Incremental Load Development, valid both for dimensions and facts.

Materialized Views access means that a Materialized View in the Oracle database is created that serves as place holders for all, or at least most part of, the information associated with a dimension. To publish the data in the Materialized Views, a dimension view has to be defined and then IFS Business Reporting & Analysis services specific Metadata for the dimension must be defined and deployed.

This section provides some of the basic steps needed to create Meta Data support for a dimension.

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: All development of dimension entities in IFS Applications should be done by using the IFS Developer Studio. The development is model based. Compared to a LU entity, a dimension entity has a model file as well as generated files in the code repository. This is important to remember - never edit the dimension files manually, instead always use the model.
For more information about how to handle modeling of a dimension, please refer to IFS Develop Studio but also to the section about modeling.
The current page provides some details, things that can be good to know when developing a dimension entity.

Contents

• General MV Definition
• Remarks
• Performance Indexes
• Creating NULL Identities
• Deployment
• Materialized View File
• Dimension View File
• Metadata File
• Implementation Example

General MV Definition

A Materialized View (MV) is a snapshot table that is created from a SELECT statement. When creating a MV for a dimension, this is done by creating a PL/SQL block where the SELECT statement is executed dynamically. The block also contains creation of indexes.

The Materialized View has the following structure:

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 
     -- ID (unique identity) column
           <parent1_column>||''^''||...||<parentN_column>||''^''||<key_column>  id,
     -- parent columns
           <parent1_column>                                                     <parent1_column_alias>,
           ...                                                                  ...,
           <parentN_column>                                                     <parentN_column_alias>,         
     -- key (CODE) column                                                      
           <key_column>                                                         code,
     -- other attributes                                                       
           <attribute1_column>                                                  <attribute1_column_alias>,
           ...                                                                  ...,
           <attributeN_column>                                                  <attributeN_column_alias>
    FROM <source_table> 
    WHERE <source_condition>
    UNION ALL
    SELECT <parent1_column>||''^''||...||<parentN_column>||''^''||''#''         id,
           <parent1_column>                                                     <parent1_column_alias>,
           ...                                                                  ...,
           <parentN_column>                                                     <parentN_column_alias>, 
           NULL                                                                 code,
           NULL                                                                 <attribute1_column_alias>,
           ...                                                                  ...,
           NULL                                                                 <attributeN_column_alias>
    FROM <parentN_table>
    WHERE <parentN_condition>
    UNION ALL
     ...
    UNION ALL
    SELECT <parent1_column>||''^''||...||''#''||''^''||''#''                    id,
           <parent1_column>                                                     <parent1_column_alias>,
           ...                                                                  ...,
           NULL                                                                 <parentN_column_alias>, 
           NULL                                                                 code,
           NULL                                                                 <attribute1_column_alias>,
           ...                                                                  ...,
           NULL                                                                 <attributeN_column_alias>
    FROM <parent1_table>
    WHERE <parent1_condition>
    UNION ALL
    SELECT ''#''||''^''||...||''#''||''^''||''#''                               id,
           NULL                                                                 <parent1_column_alias>,
           ...                                                                  ...,
           NULL                                                                 <parentN_column_alias>, 
           NULL                                                                 code,
           NULL                                                                 <attribute1_column_alias>,
           ...                                                                  ...,
           NULL                                                                 <attributeN_column_alias>
    FROM BI_DUAL_TAB';

   EXECUTE IMMEDIATE stmt_;

   dbms_output.put_line('Create constraints and indexes on Materialized View &MV');
   
   constraint_name_ := table_name_ || '_PK';
   Database_SYS.Set_Table_Column (columns_ , 'ID');
   Database_Sys.Create_Constraint(table_name_,
                                  constraint_name_,
                                  columns_,
                                  'P',
                                  '&XXXXXX_MVIEW_INDEX',
                                  NULL,
                                  TRUE,
                                  TRUE);
   Installation_SYS.Reset_Column_Table(columns_);

   index_name_ := table_name_||'_UX1';
   Database_SYS.Set_Table_Column (columns_ , '<parent1_column_alias>');
   ...
   Database_SYS.Set_Table_Column (columns_ , '<parentN_column_alias>');
   Database_SYS.Set_Table_Column (columns_ , 'CODE');
   Database_SYS.Create_Index ( table_name_,
                               index_name_,
                               columns_,
                               'U',
                               '&XXXXXX_MVIEW_INDEX',
                               NULL,
                               TRUE,
                               TRUE);
   Installation_SYS.Reset_Column_Table(columns_);
   
   index_name_ := table_name_ || '_IX1';
   Database_SYS.Set_Table_Column (columns_ , '<parent1_column_alias>');
   ...
   Database_SYS.Set_Table_Column (columns_ , '<parentN_column_alias>');
   Database_SYS.Set_Table_Column (columns_ , '<attribute1_column_alias>');
   Database_Sys.Create_Index( table_name_,
                              index_name_,
                              columns_,
                              'N',
                              '&XXXXXX_MVIEW_INDEX');
   Installation_SYS.Reset_Column_Table(columns_);
   
   ...

END;
/   

Some remarks:

• Constraints and indexes related to the MV are dropped before creation. The reason is that it is not possible to replace an existing MV definition as is the case with a VIEW.
• The MV is created with the following options:
  • TABLESPACE <tablespace_name>

    Specifies the tablespace in the database that the MV should be created in. This is normally a specific tablespace that differs from the tablespace dedicated for tables.

  • BUILD DEFERRED

    Means that only the MV definition is created in the dictionary. The MV will thus be empty after creation.

  • USING NO INDEX

    Prevents ORACLE from automatically creating indexes based on the information in the MV source table. This is especially important for dimension MVs, where we want to make sure that the ID column, in almost all cases, is the PRIMARY KEY in the MV.

  • REFRESH COMPLETE ON DEMAND

    The refresh of the MV is always a complete refresh and it will be performed on demand, i.e. typically by executing a batch job. Do not ever set the REFRESH MODE to COMMIT, since it might lead to serious database performance problems.

Remarks

Some general remarks about Data Mart development for dimensions.

Note: All development is done in IFS Developer Studio where a dimension model is used

• Check out general dimension specifics first.
• A dimension is supposed to be described by a metadata model and that model is the same regardless if the execution is based on Data Mart or Online data.
• Since the dimension data is stored in a Materialized View, this means that derived column values will be stored in single columns in the Materialized View.
• Data that has to be evaluated at runtime cannot be stored in the Materialized View, i.e. user specific data. Translations and security are typical examples.
• The Data Mart version of a dimension should contain NULL identities, i.e. a set of extra rows should be generated for each parent condition.

  The purpose is to make the following possible:

• The join between a fact and a dimension can be made using one single column that keeps the unique identity of a row in the dimension.

  The join can be made as an exact join which will be an advantage. No considerations have to be done if an outer join is necessary.

• Even if a transaction row might not always have a value corresponding to the dimension, the exact join using one column (in the fact and the dimension) is possible.
• We do not know what applies to a dimension outside the domain that owns it. Is the dimension identity always available?

  To avoid these kind of questions, the rule is to always define the so called NULL identifiers.

• Each row in the dimension should be uniquely identified. There are however some exceptions, e.g. when the dimension can be regarded as a child or add-on dimension that is supposed to be added to a master/parent dimension.

Performance Indexes

To get good performance when accessing an Information Source, using the Data Mart as source, it is important to create indexes on the Materialized Views representing dimension and fact data. Some recommendations:

• Create concatenated indexes on frequently used columns rather than creating single column indexes.
• Fact related Materialized Views

• Create concatenated indexes on dimension identity columns that are commonly accessed at the same time.

• Dimension related Materialized Views

• Create concatenated indexes on columns that are commonly accessed at the same time.
• Always create a PRIMARY KEY index on the ID column when possible. This column in most cases represents a unique row identifier. But there are exceptions, e.g. sometimes for add-on dimensions. IFS Developer Studio will by default create a primary key constraint on the ID column.
• Always create UNIQUE indexes on items that have a unique value.

  This is especially important for items that are visible.

  Assume we have a Materialized View representing companies. We have also defined the columns ID, CODE and COMPANY to contain the same value, i.e. the company code. The first thing we do is to create a UNIQUE index on column ID. Since both columns CODE and COMPANY in this cases are unique, we can create on UNIQUE INDEX on each one of the columns. The CODE column is the most important one since it is visible and will be used frequently in reports based on Information Sources in IFS Financials.

Assume we have index definitions in the model:

indexinfo {
   indexes {
      index on columns AccountGroup,Company;
      index unique on columns Company,Code;
   }
}

IFS Developer Studio will create

• A primary key constraint on ID column
• An ordinary/standard index on columns ACCOUNT_GROUP, COMPANY
• A unique index on COMPANY, CODE

Index names will be automatically generated.

Download and check the MV file MV_AccrulDimAccount.cre if needed.

Creating NULL Identities

Dimension specific Materialized Views should contain NULL identities, mentioned in the remarks section.

These NULL identities are normally created by performing an extra select from a typical parent table, to get one NULL identity row per each parent.

One example is supplied here, where we create a Materialized View representing accounts.

CREATE MATERIALIZED VIEW &MV 
TABLESPACE &ACCRUL_MVIEW_DATA
BUILD DEFERRED
USING NO INDEX
REFRESH COMPLETE ON DEMAND
AS
SELECT  a.company||'^'||a.code_part_value  id,
        a.company                          company,
        a.code_part_value                  code,
        a.description                      description,
        a.accnt_group                      account_group,
        a.accnt_type                       account_type
FROM ACCOUNTING_CODE_PART_VALUE_TAB a
WHERE a.code_part  = 'A'
UNION ALL 
SELECT  company || '^' || '#'              id, 
        company                            company,
        NULL                               code,
        NULL                               description,
        NULL                               account_group,
        NULL                               account_type
FROM COMPANY_FINANCE_TAB
UNION ALL
SELECT '#' || '^' || '#'                   id,
        NULL                               company,
        NULL                               code,
        NULL                               description,
        NULL                               account_group,
        NULL                               account_type
FROM BI_DUAL_TAB;

The ID column is created from two values, the COMPANY and the CODE, i.e. the parent key and the key. The CODE represents accounts. We can look at it as ID = (<parent_key>, <key>) = (COMPANY, CODE)

The select has three parts:

1. The first part selects accounts from the natural dimension source table (ACCOUNTING_CODE_PART_VALUE_TAB).
2. The second part considers the case where the key column CODE, i.e. account, is NULL. We should create one NULL row for each valid parent, company.

   This is done by adding a select from the parent table COMPANY_FINANCE_TAB.

   The ID is created as (COMPANY, '#'), i.e. we use the hash character to represent the NULL value of account/CODE.

3. The third part considers the case where both the parent, COMPANY, and the key, CODE, are NULL. Even if we believe that there will always be a company in our Information Sources, we should make sure to cover this case anyway.

Choose the following SELECT values:

• '<parent1>'||'^'||'<parent2>'||'^'||...'<parentN>'||'^#'  for the ID (if it is a string).

  Using the hash character might be a problem if we believe that the code actually can have this value. If we have doubts, then another separator character or characters should be used.

• If the ID is built by one single number column, one key only, there is no natural value representing NULL. If we e.g. know that the key column can never have the value 0 or a negative value, then we can use such a value for the NULL record selected from BI_DUAL_TAB.

  Another option is then to create the ID as a string column where e.g. ID=TO_CHAR(<key_column>) and ID='#' for the NULL record.

• If the ID is built by one single date value, one key only, there is no natural value representing NULL. Since it in this case can be really hard to find a unique data that is not used and at the same time is a valid data in e.g. a MS Cube, it is recommended to use a string representation instead, e.g. ID=TO_CHAR(<key_column>, 'YYYY-MM-DD') and ID='#' for the NULL record.

  If a date is used then a suggestion can be to set the NULL record based on the date 1/1-1900, e.g. TO_DATE('1900-01-01', 'YYYY-MM-DD')

• If the ID is build by more than one column of type number or date or a mix of string, number and date, then it is recommended to create the ID column as a string.
• Parent values are selected as is. As seen in the above example, the second part selecting from COMPANY_FINANCE_TAB means that we, except for the ID, select the COMPANY since it is the parent value, but all other values are selected as NULL. For the last select from BI_DUAL_TAB it means that the ID is selected but all other values are NULL, since the parent is NULL in this case.
• Other values then the ID and the parents are selected as NULL when adding NULL records.

To select the single NULL record that represents the case when parents and keys are all NULL, please use the table BI_DUAL_TAB instead of the Oracle table DUAL. The reason is that if a Materialized View contains select from the DUAL table, it will not be possible for Oracle to track changes in other tables in the SELECT clause, so the Materialized Views can be marked as FRESH even if the source tables have changed. By using an ordinary table like BI_DUAL_TAB this problem is solved.

One example:

CREATE MATERIALIZED VIEW &MV 
TABLESPACE &IFSAPP_DATA
BUILD DEFERRED
USING NO INDEX
REFRESH COMPLETE ON DEMAND
AS
SELECT  a.column1                  id,
        a.column1                  code,
        a.description              description
FROM XYZ_TAB a
UNION ALL 
SELECT  '#'                        id, 
        ''                         code,
        ''                         description   
FROM BI_DUAL_TAB

The second part selects one line from BI_DUAL_TAB that will represent a NULL identity record.

Note: Do not use Oracle's DUAL table. Using it will lead to incorrect refresh status of the Materialized View.

To make things work when connecting the dimension to a fact it is important that the fact definition also handles the NULL values in the same way for the columns representing the dimension identities. But if for a specific fact, the key values of a dimension are always present on all transaction rows, then the fact definition for the dimension identity can be done without NULL value handling.

Deployment

Development of Data Mart support for a dimension means creating a set of files that have to be deployed in the database.

The files are:

1. Materialized View file definition
2. Dimension View file definition
3. Dimension Metadata file

Materialized View File

The Materialized View definition is either represented as a CRE file, a UPG file or as a part of a CDB file (bug correction).

The file is deployed in the database as any other CRE/UPG/CDB file.

When the Materialized View has been created, it is important to find out how long time it will take to perform a complete refresh and of course also to make sure that the selected data is correct.

A Materialized View is always empty after creation and the staleness is UNUSABLE. The Materialized View has to be activated first, i.e. the first snapshot has to be created. The Materialized View features in IFS Solution Manager can normally be used for activation BUT these features are supposed to be used in an environment where all BI specific files have been deployed. So the suggested way to do the activation during development is to execute the following PL/SQL block in the database:

BEGIN
   Xlr_Mv_Util_API.Activate_Unusable_Mviews(<mview_name1>[,<mview_name2>,...,<mview_name3>]);
END;

After this step the natural thing would be to SELECT from the Materialized View to make sure that:

• The dimension identities are correctly defined.
• NULL dimension identities are defined correctly.
• Parent values are represented.
• Enumeration db values are available.
• Values derived via function calls or SUB SELECT statements, are correct.

Testing refresh performance requires a large database.

The staleness (state) of a Materialized View can have many different values. Please refer to Materialized View Basics for more detailed information.

The Materialized View file can be downloaded if needed.

Dimension View File

The dimension view definition is represented as an APV file, meaning that it represents a public read interface.

The file is deployed in the database as any other CRE/UPG/CDB file.

After deployment the natural thing would be to SELECT from the view.

Make sure that:

• The dimension identities are correctly defined
• NULL dimension identities are defined correctly
• Parent values are represented
• Enumeration db and client values are available
• Values derived via function calls or SUB SELECT statements, are correct

Note: It is very important to refresh the F1 dictionary after having created a new dimension view that is supposed to act as List of Values view in IFS Reporting.

The Data mart access/view file DimAccountDm.apv can be downloaded if needed.

Metadata File

A dimension has to be described/defined in a Metadata file. The Metadata is represented as an INS file.

The file is deployed in the database as any other INS file.

There is of course also the possibility to create the dimension metadata in the Information Source feature.

Note: If the Information Source feature is used to create or add metadata, it is important to make sure to export the metadata to file as the last step. The reason is that any modification through the feature is a customization of the metadata in the current environment and if the changes are not reflected in a metadata file, it means that next time the metadata file is executed, all manual configuration will be lost.

Note: All development should preferably be made using IFS Developer Studio

Information about the deployment is output on screen (or in a log file). Errors must be investigated and corrected. Information lines should be looked up. Do one of the following:

1. Open the Information Source log feature. This feature displays all log entries registered when deploying the metadata INS file or when doing manual metadata configuration.
2. Perform the following SELECT

   SELECT * FROM XLR_IMPORT_LOG_TAB ORDER BY TIMESTAMP DESC

When the accurate IMPORT_ID has been found, the following SELECT can be performed:

SELECT * FROM XLR_IMPORT_LOG_TAB WHERE IMPORT_ID = '<import_id>' ORDER BY LINE_ID ASC

When all errors have been corrected it is important to refresh the Materialized View Cache. The cache is vital for Materialized View related functionality in the IFS Business Reporting & Analysis services framework. There are two ways to do this:

• Use an RMB option in the BI Materialized View feature
• Execute the following PL/SQL block in the database:

  BEGIN
     Xlr_Mv_Util_API.Refresh_Mv_Info;
  END;

Implementation Example

An implementation example related to a Data Mart version of a Dimension can be found here.