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 Lalise
-- ------------ ------ --------------------------------------------------
-----------------------------------------------------------------------------
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 * *"<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 in component &MODULE
DECLARE
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 supportedIs_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 no longer exists in the source (typically by comparing with the MVB view). The item value should be set to TRUE for all incremental entities where the business logic might remove rows in related source tables. |
| 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
EXISTScondition - 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>
- one key table that holds the keys related to the top table
- Use an
EXISTScondition - Join the placeholder key tables with the corresponding parent table using the in metadata defined key columns
- Use a
UNIONoperator to separate each source table specificSELECT
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 in component &MODULE
DECLARE
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 in component &MODULE
DECLARE
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 ofIs_Emvc_Operator_API.DB_EQUAL_TOIs_Emvc_Operator_API.DB_NOT_EQUAL_TOIs_Emvc_Operator_API.DB_GREATER_THANIs_Emvc_Operator_API.DB_GREATER_OR_EQUALIs_Emvc_Operator_API.DB_LESS_THANIs_Emvc_Operator_API.DB_LESS_OR_EQUALIs_Emvc_Operator_API.DB_BETWEENIs_Emvc_Operator_API.DB_LIKEIs_Emvc_Operator_API.DB_NOT_LIKEIs_Emvc_Operator_API.DB_INIs_Emvc_Operator_API.DB_NOT_INIs_Emvc_Operator_API.DB_IS_NULLIs_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 NUMBERIf 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 for fact &ENTITY_ID in component &MODULE
BEGIN
IS_MV_UTIL_API.Create_Mvs_View('&ENTITY_ID', &IS_FACT, '&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 in component &MODULE
DECLARE
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 in component &MODULE
DECLARE
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 in component &MODULE
DECLARE
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 in component &MODULE
DECLARE
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 ofIs_Entity_Mv_Src_Join_Type_API.DB_COLUMN_TO_COLUMNIs_Entity_Mv_Src_Join_Type_API.DB_COLUMN_TO_CONSTANTIs_Entity_Mv_Src_Join_Type_API.DB_CONSTANT_TO_COLUMNIs_Entity_Mv_Src_Join_Type_API.DB_CONSTANT_TO_CONSTANTDefines 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_nameIf 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_constantIf 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_nameConstant-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_NUMBERFnd_Data_Type_API.DB_STRINGFnd_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_NUMBERFnd_Data_Type_API.DB_STRINGFnd_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