Fact Data Mart Development

Data Mart development of a fact means creating a (snapshot) table with calculated/derived information serving as a place holder for a transaction source, i.e. the fact. 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.

By connecting dimensions to the fact we get a Star Schema or an Information Source.

To publish the data in the Materialized Views a fact view has to be defined and then IFS Business Reporting & Analysis services specific metadata for the fact is defined and deployed.

This section provides some of the basic steps needed to create metadata support for a fact.

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.

Note: All development of fact entities in IFS Applications should be done by using the IFS Developer Studio. The development is model based. Compared to a LU entity, a fact entity has a model file as well as generated files in the code repository. This is important to remember - never edit the fact files manually, instead always use the model.
For more information about how to handle modeling of a fact, 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 fact entity.

Contents

• General MV Definition
• General Remarks
• Deployment
• Materialized View File
• Fact View File
• Metadata File
• Implementation Example

General MV Definition

If a fact does not support incremental load, then the storage container will be a Materialized View.

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

The development work should be done by using IFS Developer Studio. A fact model is created/edited and when files are generated one entity specific file will be created representing the MV definition.

The following definition shows the basic template definition of a Materialized Views for a fact.

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 &XXXXXX_MVIEW_DATA
    BUILD DEFERRED
    USING NO INDEX
    REFRESH COMPLETE ON DEMAND
    AS 
    SELECT  
      -- original key columns
            <key1_column>                             <key1_column_alias>,
            ...                                             ...,
            <keyN_column>                             <keyN_column_alias>,
      -- measure items
            <fact_attr1_column>                       <fact_attr1_column_alias>,
            ...                                             ...,
            <fact_attrN_column<                       <fact_attrN_column_alias>,
      -- light items
            <light_attr1_column>                      <light_attr1_column_alias>,
            ...,                                            ...,
            <light_attrN_column>                      <light_attrN_column_alias>,
      -- dimension identifiers
            <dimension1_id_columns>                   <dimension1_name>_id,
            ...                                             ...,
            <dimensionN_id_columns>                   <dimensionN_name>_id>,
      -- joiners
            <joiner1_column>                          <joiner1_column>,
            ...                                             ...,
            <joinerN_column>                          <joinerN_column>
    FROM <fact_source_table>
    WHERE <source_condition>';

   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_ , '<key1_column_alias>');
   ..
   Database_SYS.Set_Table_Column (columns_ , '<keyN_column_alias>');
   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_ || '_IX1';
   Database_SYS.Set_Table_Column (columns_ , '<dimension1_name>_id');
   Database_SYS.Create_Index( table_name_,
                              index_name_,
                              columns_,
                              'N',
                              '&XXXXXX_MVIEW_INDEX',
                              NULL,
                              TRUE,
                              TRUE);
   Installation_SYS.Reset_Column_Table(columns_);

   index_name_ := table_name_ || '_IX2';
   Database_SYS.Set_Table_Column (columns_ , '<dimension1_name>_id');
   Database_SYS.Set_Table_Column (columns_ , '<dimension2_name>_id');
   Database_SYS.Set_Table_Column (columns_ , '<dimension3_name>_id');
   Database_Sys.Create_Index( table_name_,
                              index_name_,
                              columns_,
                              'N',
                              '&XXXXXX_MVIEW_INDEX',
                              NULL,
                              TRUE,
                              TRUE);
   Installation_SYS.Reset_Column_Table(columns_);

END;
/   

Some remarks:

• Constraints and indexes related to the old version of the MV as well as the MV itself 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.

  Note: A Primary Key constraint will be created automatically by IFS Developer Studio based on the key attributes. The model also supports definition of indexes.

   

• 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.

  • 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.

General Remarks

Some general remarks about Data Mart development for facts:

• Check out general fact specifics first.

• Note: The fact is represented by a metadata model and that model is the same regardless if the execution is based on Data Mart or Online access. This means that e.g. all attributes are common, i.e. independent of data access type.

• Since the fact data is stored in a Materialized View, this means that derived column values will be stored in single columns in the Materialized View. This is an advantage when it comes to read access.
• Data that has to be evaluated at runtime cannot be stored in the Materialized View. This might be things as i.e. end user related data, translations, security.
• The dimension identities are built in the same way as in each referenced dimension.

  If the dimension keys can be NULL in the transaction table then this must be considered when defining the referenced dimension id.

  Note: Data Mart dimensions should always be built considering NULL identities, see Dimension Data Mart Development.

  However if it is sure that a dimension is always represented on each fact row, then that dimension identity can be built without considering NULL values.

• Consider the following:

• Refresh is always COMPLETE; each time refresh is performed than all source rows will be inserted into the MV.

  Make sure that Materialized View definition has good enough performance to avoid endless refresh time, i.e. function calls, joins between many sources, sub-selects etc can be bad for the refresh performance.

  Only way to find out is to have a database with large source tables. It is of course difficult to say what acceptable refresh time means, but it is normally possible to get an indication if the refresh is possible or not.

  If there are problems with refresh for e.g. a definition where 2 or more tables are joined, one option can be to create one Materialized View for each source table and then perform the join in the fact view instead.

  Note: IFS Developer Studio only supports one MV definition per entity. If additional MVs are needed for one entity, then such MVs should be added to the component specific file named MV<component>.CRE

   

• Instead of building only one Materialized View and one fact view, consider splitting the information into several Materialized Views and to create a set of facts.

  The information in the source table(s) might be too general and sometimes it is smart to split the information into smaller logical entities.

  • Observe IFS Dev Studio limitations in previous bullet.
  • Avoid creating MVs that are dependent of each other since the refresh framework currently does not support this scenario.
• Always consider reducing number of refreshed transactions.

  One way is to define a Materialized View Filter Parameter and to include a function call in the Materialized View definition that gets the filter parameter value and uses it at refresh time.

  This means that the Data Mart source is reduced in size, which can be beneficial for performance.

  Note: For Materialized Views it is possible to add filters, to the MV definition, that can be controlled by parameters but these definitions are not supported by IFS Developer Studio and there are no standard MV definitions from IFS R&D that contains any filter definitions. Adding MV filters is a typical customization.

• Instead of creating a Materialized View that is more or less a one-to-one mapping of a source table, one possibility can be to do the following:

• Create on Materialized View that is a grouped source, i.e. the information in the source table is grouped at refresh time. Now use this Materialized View, with grouped data, as the Fact source.
• Also create a Materialized View of the original source that can serve as a detailed Fact. Make sure that it is possible to Drill Down from the grouped source to this detailed source.

• Create concatenated indexes (based on many columns) instead of single column indexes.

  Try to create the indexes according to the most common use cases, like e.g. based on the most frequently used combinations of dimensions.

Deployment

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

The files are:

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

Materialized View File

The Materialized View definition is either represented in a CRE file, an 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.

IFS Developer Studio will only support creation of the CRE file. Also note that the CRE is entity specific.

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 have values that match the identities of the referenced dimensions
• NULL dimension identities are defined correctly
• Measures and light items are correct
• Values, e.g. calculations, that are derived via function calls or SUB SELECT statements, are correct
• Columns to be used for natural/true join are available for all referenced dimensions

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.

Fact View File

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

The file is deployed in the database. Each entity has its own data mart view file.

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

Make sure that:

• The dimension identities have values that match the identities of the referenced dimensions. This includes checking NULL identities.
• Measures and light items, also calculated ones, are correct.
• Columns to be used for natural/true join are available for all referenced dimensions.

Metadata File

A fact 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. Each entity has its own metadata files.

Note: Always use the IFS Developer Studio to create/modify the model that in turn will be used to create the metadata file.

It is possibility to add/modify fact metadata in the Information Source feature but this is not recommended. Instead use IFS Developer Studio.

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. Modifying the metadata via the Information Source feature means doing a customization in the current installation. If the changes are not saved and kept in an installation specific metadata file version, the manual changes will be overwritten next time the metadata is deployed, e.g. a new standard version of the file via a bug correction.

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

• Open the Information Source log feature
• 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 Fact can be found here.