Skip to content

Incremental Load Specific Metadata

This page deals with the metadata specific for entities that support incremental load. This page provides a sub set of the Incremental Load Development documentation.

To get a general overview of incremental load please refer to Incremental Load of Information Sources.

General

The metadata related to incremental load can be defined for dimensions as well as for facts. The most common scenario is probably that incremental load support if developed for facts since facts represent transactional sources and it is essential to be able to reduce the time to update the snapshot (MVT) tables.

The name standard for the file is SnapshotMetaData_<component>BIFact<Entityclientname>.ins, e.g. SnapshotMetaData_OrderBIFactCustomerOrderLine.ins

The file SnapshotMetaData_OrderBIFactCustomerOrderLine.ins can be downloaded and used as a template/example.

Note: IFS Developer Studio is used to create the metadata file (as will as all other related files). However the tool currently only support facts.

File Header Definitions

The file header contains dome general comments about component, file name and purpose and so on.

There is also a set of DEFINE variables to be defined.


-----------------------------------------------------------------------------  
--  Module : PURCH  
--  
--  Purpose: Add Snapshot Metadata Related to FactCustomerOrderLine  
--  
--  File   : SnapshotMetaData_OrderBIFactCustomerOrderLine.ins  
--  
--  IFS Developer Studio Template Version 2.5  
--  
--  Date           Sign    History  
--  ------------   ------  --------------------------------------------------  
--  Oct 16, 2014   abcdse   
--  ------------   ------  --------------------------------------------------  
-----------------------------------------------------------------------------  

DEFINE MODULE            = ORDER  
DEFINE ENTITY_ID         = FACT_CUSTOMER_ORDER_LINE  
DEFINE IS_FACT           = TRUE  
DEFINE MVB_VIEW          = CUSTOMER_ORDER_LINE_MVB  
DEFINE MVS_VIEW          = CUSTOMER_ORDER_LINE_MVS  
DEFINE MVT_TABLE         = CUSTOMER_ORDER_LINE_MVT  
DEFINE LU                = BiFactCustomerOrderLine  
DEFINE MVS_CRITERIA      =  ""  
DEFINE LOG_MESSAGE       = " * * Incremental Load Entity &ENTITY_ID&nbsp; * *"<br/>DEFINE LOG_SOURCE_ID     = "Entity &ENTITY_ID"<br/>...<br/>

The variables are:

Variable Name Comment
ENTITY_ID The identity if the entity, i.e. the dimension or fact identity
IS_FACT Set to TRUE if the entity is a fact, set FALSE if dimension
MVB_VIEW The name of the base view. This view is very similar to the how a Materialized View would be generated. If the Materialized View solution is to be replaced by an incremental load solution, use the existing Materialized View as a starting point. It is suggested to skip the DIM_ or FACT_ part in the beginning of the name. It is also recommended to end the name with _MVB
MVS_VIEW The name of the source view, i.e. the view used by the framework when performing the incremental load. Use the same name pattern as the base view but let the name end with _MVS
MVT_TABLE The name of the snapshot table that will be updated by the incremental framework. Use the same name pattern as the base view but let the name end with _MVT
LU Name of the LU. There are no ordinary LUs related to dimension or facts but the recommended name is Fct<EntityIdentityClientName>, e.g. FctCustomerOrdeLine
MVS_CRITERIA The identity of the criteria to be used when creating the MVS view from the MVB view. Normally this variable is not used and can be set to ""
LOG_MESSAGE Log message for the entity during deployment. Use the suggested default variable
LOG_SOURCE_ID Log identity for the entity during deployment. Use the suggested default variable

General Entity Information

The general section defines the entity, table and view names and some other necessary information.


PROMPT Install MV info of fact &ENTITY_ID&nbsp;in component &MODULE  
DECLARE  
&nbsp;  rec_    IS_ENTITY_MV_INFO_API.New_Rec;  
BEGIN  
   rec_.entity_id                      := '&ENTITY_ID';  
   rec_.is_fact_entity                 := &IS_FACT;  
   rec_.mv_table_name                  := '&MVT_TABLE';  
   rec_.mvb_view_name                  := '&MVB_VIEW';  
   rec_.mvs_view_name                  := NULL;  
   rec_.refresh_mode_db                := Is_Mv_Refresh_Mode_API.DB_INCREMENTAL;  
   rec_.mvt_created_date_column_name   := 'MVT_CREATED_DT';  
   rec_.id_column_name                 := 'OBJID';  
   rec_.supports_delete                := FALSE;  
   rec_.incr_fetch_stmt                :=   
   'SELECT * from CUSTOMER_ORDER_LINE_MVS f   
    WHERE EXISTS (   
       SELECT 1 FROM  s1   
       WHERE  f.order_no_key = s1.order_no)   
    UNION  
    SELECT * FROM CUSTOMER_ORDER_LINE_MVS f  
    WHERE EXISTS (   
       SELECT 1 FROM  s2   
       WHERE  f.order_no_key     = s2.order_no   
       AND    f.line_no_key      = s2.line_no   
       AND    f.rel_no_key       = s2.rel_no  
       AND    f.line_item_no_key = s2.line_item_no)';  
   rec_.notes                          := NULL;   
   IS_ENTITY_MV_INFO_API.Insert_New(rec_);  
END;  
/  

  • Main part of the information in this section is given by the variables file header definitions.
  • The MVS view name should NOT be defined in this section.
  • Other details
Record Item Comment
refresh_mode_db This is the db value defining the refresh mode. Two values are possible:
Is_Mv_Refresh_Mode_API.DB_INCREMENTAL if incremental load is supported
Is_Mv_Refresh_Mode_API.DB_FULL if only full load is supported
mvt_created_date_column_name This is the name of the column in the MVT table that will contain the date and timestamp for each row in the MVT table. This column is normally named MVT_CREATED_DT. The column name must be known for the framework to enable correct creation of the MVS view.
id_column_name This is the name of the column that represents the unique row identifier. Normally this column is called OBJID or ID. This column is not mandatory but if the entity supports delete and the MVT table has no Primary Key defined, then this column must be supplied.
supports_delete Specifies if the refresh mechanism should also perform delete of records in the MVT table that does not exist in the source (typically by comparing with the MVB view). Supply TRUE is yes and FALSE if no. It is not recommended to enable the delete option for transaction entities that may contain large amounts of data.
incr_fetch_stmt This is the statement to be used when performing the incremental load. The statement is not mandatory if only full refresh/load is supported. If incremental load is supported it can be supplied via metadata but if it is not supplied then the framework will automatically generate a statement.

The general principles how to define the incremental statement can be found in the Incremental Refresh Statement section on the Incremental Load Development page.

Two typical examples will still be presented.

One Highest Level Parent Table

Figure 1: Source Dependency Structure with one highest level parent table

Case specifics:

  • There is only one highest level parent source table
  • Other parent source tables exist in the dependency structure

For the described case there is only one highest level parent. The incremental load should be performed using the keys propagated in the structure up to this highest level source. This means that there is only one key table to consider. The statement will typically look like this:

SELECT * from CUSTOMER_ORDER_LINE_MVS f   
WHERE EXISTS (   
   SELECT 1 FROM <CUSTOMER_ORDER_TAB> s1   
   WHERE f.order_no_key = s1.order_no)
  • The MVS view should always be used as the source view, e.g. CUSTOMER_ORDER_LINE_MVS
  • Keys will, according to the example, be fetched from a temporary key table that holds the keys related to the top table CUSTOMER_ORDER_TAB, represented by the placeholder <CUSTOMER_ORDER_TAB>
  • Use an EXISTS condition
  • Join the placeholder key table with the parent table using the in metadata defined key columns

Parent Tables On One Level

Figure 2: Source Dependency Structure with only one level of parent tables

Case specifics:

  • There is only one level of parent source tables
  • There are two or more parent tables
  • Changes in all lower level tables will be propagated to the source tables on the highest level

For the described case there are two highest level parents. The incremental load should be performed using the keys propagated in the structure up to the highest level sources. There are two key tables to consider in the example above. The statement will typically look like this:

SELECT * from CUSTOMER_ORDER_LINE_MVS f   
WHERE EXISTS (   
   SELECT 1 FROM <CUSTOMER_ORDER_TAB> s1   
   WHERE f.order_no_key = s1.order_no)  UNION SELECT * FROM CUSTOMER_ORDER_LINE_MVS f WHERE EXISTS (     SELECT 1 FROM <CUSTOMER_ORDER_LINE_TAB> s2 WHERE  f.order_no_key     = s2.order_no AND    f.line_no_key      = s2.line_no AND    f.rel_no_key       = s2.rel_no AND    f.line_item_no_key = s2.line_item_no)
  • The MVS view should always be used as the source view, e.g. CUSTOMER_ORDER_LINE_MVS
  • Keys will, according to the example, be fetched from two temporary key tables
    • one key table that holds the keys related to the top table CUSTOMER_ORDER_TAB, represented by the placeholder <CUSTOMER_ORDER_TAB>
    • one key table that holds the keys related to the top table CUSTOMER_ORDER_LINE_TAB, represented by the placeholder <CUSTOMER_ORDER_LINE_TAB>
  • Use an EXISTS condition
  • Join the placeholder key tables with the corresponding parent table using the in metadata defined key columns
  • Use a UNION operator to separate each source table specific SELECT

Filter Conditions

The metadata file provides the possibility to define filter conditions to be applied when creating the MVS view. The conditions are defined as part of a criteria set and it is necessary to update the entity with the criteria set identity as a last step when defining the criteria set details. See details further down.

Note: This option will not be used for incremental load entities developed by RnD.

Only information not given by the file header definitions will be explained.

Criteria Header


PROMPT Install filter conditions for fact &ENTITY_ID&nbsp;in component &MODULE  
DECLARE  
&nbsp;  rec_   IS_ENTITY_MV_CRITERIA_API.New_Rec;     
BEGIN  
     
   /*  
   rec_.entity_id                 := '&ENTITY_ID';  
   rec_.is_fact_entity            := &IS_FACT;  
   rec_.criteria_set_id           := '&MVS_CRITERIA';  
   IS_ENTITY_MV_CRITERIA_API.Insert_New(rec_);  
     
   */  
   NULL;  
END;  
/
Record Item Comment
criteria_set_id Identity of the criteria set with conditions to be used when creating the MVS view

Criteria Details


PROMPT Install filter condition details for fact &ENTITY_ID&nbsp;in component &MODULE  
DECLARE  
&nbsp;  rec_   IS_ENTITY_MV_CRITERIA_DET_API.New_Rec;     
BEGIN  
   /*  
   rec_.entity_id                 := '&ENTITY_ID';  
   rec_.is_fact_entity            := &IS_FACT;  
   rec_.criteria_set_id           := '&MVS_CRITERIA';  
   rec_.condition_no              := 1;  
   rec_.name_is_expression        := TRUE;  
   rec_.column_name               := 'EXTRACT (YEAR FROM PLANNED_DELIVERY_DATE)';  
   rec_.data_type_db              := Fnd_Data_Type_API.DB_NUMBER;  
   rec_.operator_db               := Is_Emvc_Operator_API.DB_IN;  
   rec_.condition_value           := '2011,2012,2013,2014';  
   IS_ENTITY_MV_CRITERIA_DET_API.Insert_New(rec_);  
     
   IS_ENTITY_MV_INFO_API.Set_MVS_Criteria_Set_Id(rec_.entity_id, rec_.is_fact_entity, '&MVS_CRITERIA');  
   */  
   NULL;  
END;  
/  

Record Item Comment
criteria_set_id Identity of the criteria set with conditions to be used when creating the MVS view
condition_no Sequence number for current condition
name_is_expression Set to TRUE if column_name is an Oracle SQL Expression. Set FALSE if column_name represents a column in the MVB view
column_name Column name in MVB view that the condition applies to. Can be an Oracle SQL expression.
operator_db Db value of valid SQL operation. Can be one of
Is_Emvc_Operator_API.DB_EQUAL_TO
Is_Emvc_Operator_API.DB_NOT_EQUAL_TO
Is_Emvc_Operator_API.DB_GREATER_THAN
Is_Emvc_Operator_API.DB_GREATER_OR_EQUAL
Is_Emvc_Operator_API.DB_LESS_THAN
Is_Emvc_Operator_API.DB_LESS_OR_EQUAL
Is_Emvc_Operator_API.DB_BETWEEN
Is_Emvc_Operator_API.DB_LIKE
Is_Emvc_Operator_API.DB_NOT_LIKE
Is_Emvc_Operator_API.DB_IN
Is_Emvc_Operator_API.DB_NOT_IN
Is_Emvc_Operator_API.DB_IS_NULL
Is_Emvc_Operator_API.DB_IS_NOT_NULL
condition_value Conditional value

If data type is NUMBER the supplied string value must be possible to convert to a NUMBER

If data type is DATE the supplied string value must be possible to convert to a DATE

Creating the MVS View

The MVS view will be created after defining possible MVS conditions.


PROMPT Create MVS view &MVS_VIEW&nbsp;for fact &ENTITY_ID&nbsp;in component &MODULE  
BEGIN  
&nbsp;  IS_MV_UTIL_API.Create_Mvs_View('&ENTITY_ID',&nbsp;&IS_FACT,&nbsp;'&MVS_VIEW');  
END;  
/

Source Tables

Next step will be to define all source tables, i.e. to define the dependence structure.


PROMPT Install MV source info for fact &ENTITY_ID&nbsp;in component &MODULE  
DECLARE  
&nbsp;  rec_    IS_ENTITY_MV_SOURCE_API.New_Rec;  
BEGIN  
   rec_.entity_id             := '&ENTITY_ID';  
   rec_.is_fact_entity        := &IS_FACT;  
   rec_.source_table_name     := 'CUSTOMER_ORDER_TAB';  
   rec_.master_table_name     := NULL;  
   rec_.mv_chk_name           := 'CUST_ORD_CHK_MV';  
   rec_.mod_date_column       := 'ROWVERSION';  
   rec_.dynamic_dependency    := FALSE;  
   rec_.dynamic_module        := NULL;  
   IS_ENTITY_MV_SOURCE_API.Insert_New(rec_);  
   ...  

For each source table the following information is defined, however neglecting information given by the file header definitions.

Record Item Comment
source_table_name Name of source table to be checked for changes
master_table_name Name of master table in the dependency structure. Define as NULL if source_table_name is the highest level parent table
mv_chk_name Reference to a Materialized View used to check max ROWVERSION in source_table_name
mod_date_column Name of column in source_table_name or source view(s) that represent the ROWVERSION of DATE type
dynamic_dependency Set to TRUE if source_table_name belongs to a dynamically dependent component, else set to FALSE
dynamic_module Name of the dynamically depenent component. Only to be defined if dynamic_dependency is set to  TRUE

Source Views

Next step will be to define source views to be considered. Source views can be used as a replacement for the source table during the process of finding out changes since last refresh. It is sometimes necessary to use one or more views in order to correctly filter out the changes in the source table. More information can be found in the Using Source Views section on the general development documentation page.


PROMPT Install info about source views for fact &ENTITY_ID&nbsp;in component &MODULE  
DECLARE  
&nbsp;  rec_    IS_ENTITY_MV_SOURCE_VIEW_API.New_Rec;  
BEGIN  
     
   rec_.entity_id             := '&ENTITY_ID';  
   rec_.is_fact_entity        := &IS_FACT;  
   rec_.source_table_name     := 'RENTAL_OBJECT_TAB';  
   rec_.row_no                := 1;  
   rec_.source_table_view     := 'RENTAL_OBJECT_BIS';  
   IS_ENTITY_MV_SOURCE_VIEW_API.Insert_New(rec_);  

END;  
/  

For each source table the following information is defined, however neglecting information given by the file header definitions.

Record Item Comment
source_table_name Name of source table to be checked for changes
row_no Sequence number
source_table_view Name of a view that can be used to fetch the keys affecting a parent table. If source_table_view is specified, it will be used instead of source_table_name

Key Columns for Parent Sources

Next step will be to define the key columns for the source tables acting as parents in the dependency structure.


PROMPT Install key columns for all parent source tables for entity &ENTITY_ID&nbsp;in component &MODULE  
DECLARE  
&nbsp;  rec_    IS_ENTITY_MV_SRC_MAST_COL_API.New_Rec;  
BEGIN  
   rec_.entity_id             := '&ENTITY_ID';  
   rec_.is_fact_entity        := &IS_FACT;  
   rec_.table_name            := 'CUSTOMER_ORDER_TAB';  
   rec_.column_name           := 'ORDER_NO';  
   rec_.entity_column_name    := 'ORDER_NO_KEY';  
   rec_.col_no                := 1;  
   IS_ENTITY_MV_SRC_MAST_COL_API.Insert_New(rec_);  
   -----------------------------------------------------------  
   ...  
END;  
/     

For each source table the following information is defined, however neglecting information given by the file header definitions.

Record Item Comment
table_name Name of the parent source table
column_name Name of the key column in the source table or view.
 
Note: If source views are used as a replacement for the source table, it is necessary that the column_name column exists in the registered source views.
entity_column_name Alias/name of the column in the entity (MVB, MVS views or MVT table) corresponding to column_name in table_name
col_no Column sequence

Source Details

The last step specifies how child tables are connected to parent tables in the dependency structure. This information is used when creating a join between a modified child table and its parent table in order to fond the affected key columns in the parent table.


PROMPT Install MV SOURCE detail info for entity &ENTITY_ID&nbsp;in component &MODULE  
DECLARE  
&nbsp;  rec_    IS_ENTITY_MV_SOURCE_DETAIL_API.New_Rec;  
BEGIN     
   rec_.entity_id                       := '&ENTITY_ID';  
   rec_.is_fact_entity                  := &IS_FACT;  
   rec_.child_table_name                := 'CUST_ORDER_LINE_TAX_LINES_TAB';  
   rec_.join_no                         := 1;  
   rec_.join_type                       := Is_Entity_Mv_Src_Join_Type_API.DB_COLUMN_TO_COLUMN;  
   rec_.child_column_name               := 'ORDER_NO';  
   rec_.master_table_name               := 'CUSTOMER_ORDER_LINE_TAB';  
   rec_.master_column_name              := 'ORDER_NO';  
   rec_.child_constant                  := NULL;  
   rec_.child_constant_type             := NULL;  
   rec_.master_constant                 := NULL;  
   rec_.master_constant_type            := NULL;  
   IS_ENTITY_MV_SOURCE_DETAIL_API.Insert_New(rec_);  
     
   rec_.entity_id                       := '&ENTITY_ID';  
   rec_.is_fact_entity                  := &IS_FACT;  
   rec_.child_table_name                := 'CUST_ORDER_LINE_TAX_LINES_TAB';  
   rec_.join_no                         := 2;  
   rec_.join_type                       := Is_Entity_Mv_Src_Join_Type_API.DB_COLUMN_TO_COLUMN;  
   rec_.child_column_name               := 'LINE_NO';  
   rec_.master_table_name               := 'CUSTOMER_ORDER_LINE_TAB';  
   rec_.master_column_name              := 'LINE_NO';  
   rec_.child_constant                  := NULL;  
   rec_.child_constant_type             := NULL;  
   rec_.master_constant                 := NULL;  
   rec_.master_constant_type            := NULL;  
   IS_ENTITY_MV_SOURCE_DETAIL_API.Insert_New(rec_);  
     
   rec_.entity_id                       := '&ENTITY_ID';  
   rec_.is_fact_entity                  := &IS_FACT;  
   rec_.child_table_name                := 'CUST_ORDER_LINE_TAX_LINES_TAB';  
   rec_.join_no                         := 3;  
   rec_.join_type                       := Is_Entity_Mv_Src_Join_Type_API.DB_COLUMN_TO_COLUMN;  
   rec_.child_column_name               := 'REL_NO';  
   rec_.master_table_name               := 'CUSTOMER_ORDER_LINE_TAB';  
   rec_.master_column_name              := 'REL_NO';  
   rec_.child_constant                  := NULL;  
   rec_.child_constant_type             := NULL;  
   rec_.master_constant                 := NULL;  
   rec_.master_constant_type            := NULL;  
   IS_ENTITY_MV_SOURCE_DETAIL_API.Insert_New(rec_);  

   rec_.entity_id                       := '&ENTITY_ID';  
   rec_.is_fact_entity                  := &IS_FACT;  
   rec_.child_table_name                := 'CUST_ORDER_LINE_TAX_LINES_TAB';  
   rec_.join_no                         := 4;  
   rec_.join_type                       := Is_Entity_Mv_Src_Join_Type_API.DB_COLUMN_TO_COLUMN;  
   rec_.child_column_name               := 'LINE_ITEM_NO';  
   rec_.master_table_name               := 'CUSTOMER_ORDER_LINE_TAB';  
   rec_.master_column_name              := 'LINE_ITEM_NO';  
   rec_.child_constant                  := NULL;  
   rec_.child_constant_type             := NULL;  
   rec_.master_constant                 := NULL;  
   rec_.master_constant_type            := NULL;  
   IS_ENTITY_MV_SOURCE_DETAIL_API.Insert_New(rec_);  
   ----------------------------------------------------------------------------------  

   ...  
END;  
/

For each source table the following information is defined, however neglecting information given by the file header definitions.

Record Item Comment
child_table_name Name of child/source table that triggers fetch of keys in the parent table
join_no Join sequence no
join_type Type of join to be performed. Can be one of
Is_Entity_Mv_Src_Join_Type_API.DB_COLUMN_TO_COLUMN
Is_Entity_Mv_Src_Join_Type_API.DB_COLUMN_TO_CONSTANT
Is_Entity_Mv_Src_Join_Type_API.DB_CONSTANT_TO_COLUMN
Is_Entity_Mv_Src_Join_Type_API.DB_CONSTANT_TO_CONSTANT

Defines how the join is performed between the parent and the child tables. Column-To-Column is the most common case. e.g. master_table_name.master_column_name = child_table_name.child_column_name

If Column-To-Constant is specified, it means that the join is defined by using a constant for the child, e.g. master_table_name.master_column_name = child_constant

If Constant-To-Column is specified, it means that the join is defined by using a constant for the master, e.g. master_constant = child_table_name.child_column_name

Constant-To-Constant means using a constant for both parent and child
child_column_name Name of column in child table or associated source views
master_table_name Name of master/parent table
master_column_name Name of column in master/parent table
child_constant Value of child constant defined as a string. Only to be supplied for join types DB_COLUMN_TO_CONSTANT and DB_CONSTANT_TO_CONSTANT
child_constant_type Data type for child constant. Can be one of:
Fnd_Data_Type_API.DB_NUMBER
Fnd_Data_Type_API.DB_STRING
Fnd_Data_Type_API.DB_DATE
master_constant Value of parent constant defined as a string. Only to be supplied for join types DB_CONSTANT_TO_COLUMNand DB_CONSTANT_TO_CONSTANT
master_constant_type Data type for master constant. Can be one of:
Fnd_Data_Type_API.DB_NUMBER
Fnd_Data_Type_API.DB_STRING
Fnd_Data_Type_API.DB_DATE

For both child_constant and master_constant apply that if the constant data type is NUMBERthen the constant must be defined such that a conversion to number is possible. If the constant data type is DATE, it is necessary to define the date according to the standard client format, e.g. YYYY-MM-DD-HH24.MI.SS