Skip to content

Incremental Load Development

Data Mart access of an Information Source is based on snapshot tables, either Materialized Views or ordinary tables acting as snapshot tables supported by an incremental load framework. This page deals with development of support for incremental load of Fact and Dimension entities. In most cases the focus will be to add support for incremental load of Fact entities. There can however be some rare cases where also dimension entities support incremental load. The framework is general and thus handles incremental load in the same way for dimensions and facts.

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

Note: Development of incremental load for an entity is not supported by IFS Develop Studio. Only Data Mart Access based on Materialized Views is supported by the tool.

Content

Solution Overview

The solution for incremental load can be summarized with the following picture:

Figure 1: Incremental Load Solution Overview

  • One or more tables act as sources for the fact or dimension entity
  • A base view, MVB defines how the entity information is fetched
  • A source view, MVS, is used as the source when performing the incremental refresh, moving data to the storage table, also called the MVT table. The MVS view reads from the MVB view and in addition it may contain filter conditions in order to reduce the number of transactions transferred. The MVS view is created by the framework.
  • The snapshot table is called MVT. This is an ordinary Oracle table.
  • For each entity there is a snapshot metadata file that describes the involved source tables and all other information needed to handle incremental load.
  • The deployed metadata is used by the framework to figure out what has been modified and how to perform the incremental load.
  • The Data Mart (DM) view reads information from the MVT table (instead of reading from a Materialized View).
  • The framework keeps track of changes in referenced source tables and will during incremental load create one or more temporary reference tables with keys that are used in combination with the source view (MVS) in order to only transfer new/modified source rows to the snapshot table (MVT).

Table Dependency Analysis

The first step is to perform a table dependency analysis with the goal to find all tables that affect the contents of the selected entity information.

  • If the entity already supports Data Mart access via a Materialized View, then use the Materialized View as a source for the analysis.
  • If the entity does not support access view via a Materialized View, then use the Online view as a source for the analysis.
  • Another case is when a new entity is created and thus no support has been developed yet and the plan is to only develop support for Data Mart access. In this case it would be necessary to create the access view first. This case is actually the same as first creating the MVB view and then performing an analysis using that view as a source.

It will be assumed that there is an existing Materialized View. So the idea is now to develop new Data Mart access and replace the Materialized View access with access of an incrementally loaded table. As an example the fact entity FACT_CUSTOMER_ORDER_LINE is used.

Looking into Data Mart view FACT_CUSTOMER_ORDER_LINE_DM leads to the references Materialized View CUSTOMER_ORDER_LINE_MV.

Next step will be to start analyzing the Materialized View in order to find the source tables. First look at the tables used in the FROM clause.

    SELECT  
    ...  
    col.order_no || '^' || col.line_no || '^' || col.rel_no || '^' || TO_CHAR(col.line_item_no)                 objid<br/>    FROM CUSTOMER_ORDER_LINE_TAB col, CUSTOMER_ORDER_TAB co<br/>    WHERE col.order_no = co.order_no;<br/>

It is obvious that there are two source tables to consider:

  1. CUSTOMER_ORDER_TAB
  2. CUSTOMER_ORDER_LINE_TAB

Apart from this it is necessary to check all sub-selects and function calls.

    SELECT  
       -- original keys --  
       col.order_no                                                                                                      order_no_key,  
       col.line_no                                                                                                       line_no_key,  
       col.rel_no                                                                                                        rel_no_key,  
       col.line_item_no                                                                                                  line_item_no_key,  
       -- measure items --  
       col.buy_qty_due                                                                                                   buy_qty_due,  
       col.qty_assigned                                                                                                  qty_assigned,  
       col.qty_picked                                                                                                    qty_picked,  
       col.qty_short                                                                                                     qty_short,  
       col.qty_shipped                                                                                                   qty_shipped,  
       col.qty_shipdiff                                                                                                  qty_shipdiff,  
       col.qty_returned                                                                                                  qty_returned,  
       col.qty_invoiced                                                                                                  qty_invoiced,  
       col.revised_qty_due                                                                                               qty_inventory,  
       ABS(col.buy_qty_due - col.qty_shipped)                                                                            inv_qty_incomplete,  
       col.base_sale_unit_price                                                                                          base_sale_unit_price,  
       col.base_unit_price_incl_tax                                                                                      base_unit_price_incl_tax,  
       Customer_Order_Line_API.Get_Base_Sale_Price_Total(col.order_no, col.line_no, col.rel_no, col.line_item_no)        net_amount_base,  
       Customer_Order_Line_API.Get_Base_Price_Incl_Tax_Total(col.order_no, col.line_no, col.rel_no, col.line_item_no)    gross_amount_base,  
       Customer_Order_Line_API.Get_Sale_Price_Total(col.order_no, col.line_no, col.rel_no, col.line_item_no)             net_amount_curr,  
       Customer_Order_Line_API.Get_Sale_Price_Incl_Tax_Total(col.order_no, col.line_no, col.rel_no, col.line_item_no)    gross_amount_curr,  
       col.buy_qty_due * col.price_conv_factor * col.base_sale_unit_price                                                net_amount_before_disc_base,  
       col.buy_qty_due * col.price_conv_factor * col.base_unit_price_incl_tax                                            gross_amount_before_disc_base,  
       ...

Only a part of the Materialized View is shown above. It will be necessary to analyze how the functions Get_Base_Sale_Price_Total, Get_Base_Price_Incl_Tax_Total etc are defined. This work can be rather time consuming due to complexity of the logic and the number of method calls that are performed. There is no tool that can extract the dependent tables so the analysis has to be made manually.

Analyzing the function calls in this case leads to that the following tables are found as potential source tables:

  • CUST_ORDER_LINE_TAX_LINES_TAB
  • CUST_ORDER_LINE_DISCOUNT_TAB
  • RENTAL_OBJECT_TAB

    Note: This is a table in the RENTALcomponent and the ORDER **`** component has a dynamic dependency toRENTAL`

In the analysis process some tables were neglected. In most cases it is not necessary to consider function calls related attributes/columns that define dimension identities or dimension keys.

More important is to figure out how the business logic affects different tables. It might be that some tables can be skipped if changes are always reflected by changes in some other tables. The general rule is to reduce the number of source tables.

Next step will be to define a dependency structure. This is also a manual step but rather easy once the source tables have been found. For FACT_CUSTOMER_ORDER_LINE the main source tables are CUSTOMER_ORDER_TAB and CUSTOMER_ORDER_LINE_TAB. These table are the top most tables in the structure.

Now there are two possible ways of defining the structure

  1. CUSTOMER_ORDER_TAB is the top table and CUSTOMER_ORDER_LINE_TAB is a child table
  2. Both CUSTOMER_ORDER_TAB and CUSTOMER_ORDER_LINE_TAB are top tables.

Please refer to the Incremental Load of Information Sources Overview for more information. The general rule is to define the top tables such that as few records as possible are affected by changes in the table structure. In the current example the suggested model will be to keep both mentioned tables as top source tables.

Figure 2: Table Dependency Structure - Customer Order Line

Incremental Refresh Statement

After having defined the table dependency structure, a SELECT statement should be written that supports incremental refresh. Incremental refresh is based on reading the source view, MVS, with conditions that takes into account the key tables associated with the top level source tables.

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)  

Note: The framework can create the refresh statement if it is not supplied via the model file. It is however recommended to manually create this statement, to make sure that it really is correct, that the performance is ok etc.

Since, in the example, there are two top source tables the framework will during processing create one key table for each one of these tables. The name of the key table is unknown, i.e. it is created during the processing, which makes it necessary to define a key table placeholder.

  • The MVS view is used as the source for the incremental load statement
  • For each top source table a key table placeholder is defined. Define the placeholder as <source_table_name>, i.e. <CUSTOMER_ORDER_TAB>
  • Make sure to join the keys in the key placeholder table with the associated columns in the MVS view. Use an EXISTS statement with a sub select against the key placeholder table.
  • Use a UNION statement to separate each top source table based SELECT statement.
  • The statement will be part of the metadata that describes incremental load for the current entity. However if the statement is not supplied the framework will try to create it automatically. The manual option provides the possibility to write the statement in any suitable way while the generated version only can create it according to one general principle.

Model File Example

Information Source Customer Order Line will be referenced as example entity on this page. The complete model file, as developed in IFS Developer Studio, can be downloaded if needed.

Creating the MVB View

This MVB view is the base view and it is more or less looks the same as the SELECT part of a Materialized View definition. For a case where a Materialized View based solution is to be replaced with a incremental load solution, consider the following:

  • First use the Data Mart view to find the name of the Materialized View. E.g. for FACT_CUSTOMER_ORDER_LINE_DM view the MV name is CUSTOMER_ORDER_LINE_MV.
  • Copy the contents of the SELECT part of the MV to a new file with the name Mvb_<Component><Entityclientname>.apv, e.g. Mvb_OrderFactCustomerOrderLine.apv Note: The file will only be used as an initial version that can facilitate creating the model in IFS Developer Studio. If the view has all necessary columns, security filters and performs well, it is a very good starting point when transforming the information to a model. From the model a new version will be created and then it is easy to compare the versions and investigate differences.
    IFS Developer Studio does not support reverse engineering of incremental definitions but the entity can of course be reverse engineered in order to get all attributes and other definitions in place. Then the model will have to modified by adding properties etc and for incremental support there is a special section that has to added manually.
  • The view will be named according to <entity_name_to_db_name>MVB, e.g. CUSTOMER_ORDER_LINE_MVB The name never includes FACT or DIM_
  • A unique identifier will be created automatically by IFS Developer Studio. For a fact it will use the key attributes to generate a metadata attribute named ID. By default the associated column name will also be named ID. The identity is be default generated as a concatenation of keys, e.g. <key1>||'^'||<key2>||'^'||...||'^'||<keyN>. It is possible to override this definition with a model property. Dimension entities normally have a column names ID that defines the unique key.
  • The generated MVB should have a view comment for the view. Please check that.
...  
DEFINE MODULE      = ORDER  
DEFINE MVB_VIEW    = CUSTOMER_ORDER_LINE_MVB  
DEFINE LU          = BiFactCustomerOrderLine  
...  
TRUNC(col.real_ship_date + col.delivery_leadtime)                                                       actual_delivery_date,  
TRUNC(NVL(col.real_ship_date, col.date_entered))                                                        reporting_date,  
 -- unique key  
 col.order_no || '^' || col.line_no || '^' || col.rel_no || '^' || TO_CHAR(col.line_item_no)            objid<br/>FROM CUSTOMER_ORDER_LINE_TAB col, CUSTOMER_ORDER_TAB co<br/>WHERE col.order_no = co.order_no<br/>WITH read only;<br/><br/>COMMENT ON TABLE &MVB_VIEW<br/>&nbsp;  IS 'LU=&LU^PROMPT=Bi&nbsp;Fact Customer Order Line^MODULE=&MODULE^';<br/>------------------------------------------------------------------------------<br/>-- Make sure to create the source view (MVS) at this point but only if there<br/>-- are any entities that have been registered to use the MVB view.<br/>-- The reason is to be able to handle upgrades that affects the source view but<br/>-- not the incremental metadata. <br/>------------------------------------------------------------------------------<br/>BEGIN<br/>   Is_Mv_Util_API.Create_Mvs_View('&MVB_VIEW');<br/>   COMMIT;<br/>END;<br/>/<br/><br/>UNDEFINE MODULE<br/>UNDEFINE MVB_VIEW    <br/>UNDEFINE LU   <br/>

The example above shows the unique key column with alias OBJID, defining a unique identifier for the fact entity FACT_CUSTOMER_ORDER_LINE.

Note: If only the Online view exists it is not possible to just copy the definition of that view. It is necessary to make sure that client values, user specific values and security definition are taken care of. For more information please refer to storage layer development of dimensions or facts.

Note: The MVB view definition will contain a call to create the MVS view. If no entitiy has been registered to use the MVB view when the MVB file is deployed, the MVS view will not be created. The reason for creating the MVS view is that a change to the MVB view will affect the MVS view but not necessarily the incremental metadata. The MVS view is also created when the incremental metadata is deployed, since there might be criteria definitions that affects the MVS view.

The complete MVB definition example for FACT_CUSTOMER_ORDER_LINE can be downloaded if needed.

Note: The MVB view should not have columns that do not exist in the MVT table since this means that there is information in the MVB that will never get stored in the MVT.
The MVT table should not have columns, except for the created date column (MVT_CREATED_DT), that do not exist in the MVB view since this will lead to an error since columns will be selected based one the MVT table. The MVS view is always created by taking the column names from the MVT but fetching from the MVB view. See Installation and Deployment Considerations for more information

Creating the MVT Table

Note: IFS Developer Studio will create the storage table, called MVT, from the model file. The below instructions should be taken lightly

The following instructions only apply if for some reason the MVT is created manually, something that is not encouraged anymore:

  • Once the MVB view has been defined, then next step is to create the the snapshot table, also called MVT table.
  • Give the table a name similar to the MVB view but replace MVB with MVT.
  • The easiest way is to run the following PL/SQL block in the database:
BEGIN  
  Is_Mv_Util_Api.Create_Mvt_Table_Gen_Stmt(mvb_view_name_ => 'CUSTOMER_ORDER_LINE_MVB',  
                                           mv_table_name_ => 'CUSTOMER_ORDER_LINE_MVT');  
END;   
This call result in an output as follows:
Run Following command to view the statement. Note that it is required that the global execution parameter LOG_DURING_EXECUTION is set to TRUE in BA Execution Parameters  
SELECT *  
FROM XLR_LOG_LARGE_STORE_TAB  
WHERE trace_id = 'RLOG-'||(SELECT MAX(TO_NUMBER(REPLACE(trace_id,'RLOG-',''))) FROM XLR_LOG_LARGE_STORE_TAB)<br/>ORDER BY LINE DESC<br/>
Run the statement and extract the `CREATE TABLE` definition from the `CLOB_DATA` column.
DEFINE MVT_TABLE = CUSTOMER_ORDER_LINE_MVT  
DECLARE   
   columns_    Database_SYS.ColumnTabType;  
   column_     Database_SYS.ColRec;  
   table_name_ VARCHAR2(30) := '&MVT_TABLE';  
BEGIN  
   Database_SYS.Reset_Column_Table(columns_);  
   Database_SYS.Set_Table_Column(columns_,'ORDER_NO_KEY', 'VARCHAR2(48)', 'N');  
   Database_SYS.Set_Table_Column(columns_,'LINE_NO_KEY', 'VARCHAR2(16)', 'N');  
   Database_SYS.Set_Table_Column(columns_,'REL_NO_KEY', 'VARCHAR2(16)', 'N');  
   Database_SYS.Set_Table_Column(columns_,'LINE_ITEM_NO_KEY', 'NUMBER', 'N');  
   ...  
   ...  
   Database_SYS.Set_Table_Column(columns_,'OBJID', 'VARCHAR2(252)', 'Y');  
   Database_SYS.Set_Table_Column(columns_,'MVT_CREATED_DT', 'DATE', 'N');  
   Database_SYS.Create_Or_Replace_Table(table_name_, columns_, '&IFSAPP_DATA',&nbsp;NULL, TRUE);  
END;   
/  

UNDEFINE MVT_TABLE  

Note: The column MVT_CREATED_DT has been added. This column is very important since it contains the date and timestamp when a row was created in the MVT table. The tabular model framework needs this date to be able to perform incremental transfer of records from the MVT table to the staging table in the SQL Server database.
Please realize that the MVT_CREATED_DT column will be automatically added if IFS Developer Studio is used.

Consider the following:

  • Make sure to define primary key attributes as mandatory in the model, leading to that the associated columns will be defined as NOT NULLable. In the example above the columns ORDER_NO_KEY, LINE_NO_KEY, REL_NO_KEYand LINE_ITEM_NO_KEY have been defined using e.g. Database_SYS.Set_Table_Column(columns_,'ORDER_NO_KEY', 'VARCHAR2(48)', 'N'); where the last parameter defines that the column can not be NULL. Make sure that handle this in all necessary files, i.e. in the CDB, UPG and CRE file
  • A Primary Key constraint based on the key columns will be automatically generated by IFS Develop Studio. The constraint will be named <mvt_table_name>_PK, e.g. CUSTOMER_ORDER_LINE_MVT_PK
  • An index will automatically be defined for the created date column MVT_CREATED_DT The index will be named <mvt_table_name>_MVX, e.g. CUSTOMER_ORDER_LINE_MVT_MVX
  • Indexes can be defined on dimensions identities to support Data Mart access based on Star Schema access, e.g. as performed by Business Reporter. Normally a concatenated index is more useful than individual column indexes. The general rule is to concatenate dimension identity columns that represents the most common dimension combinations. Do not create too many indexes. Customized indexes can be added upon request in a customer installation. Indexes are defined in the model according to the following syntax:

indexinfo {  
   indexes {  
      index on columns DimCustomerId;  
      index on columns DimSupplierId;  
      index on columns DimInventoryPartId;  
   }  
}
- The MVT table definition will be defined in a CRE file named Mvt_<component><Entityclientname>.CRE e.g. Mvt_OrderFactCustomerOrderLine.CRE - If the table is modified as part of a bug (or defined as part of project development) it is necessary to define a CDB file with the table definition or the changes to the definition. - If the table is modified from one release to another, the changes must be defined in a UPG script.

The complete MVT definition example for FACT_CUSTOMER_ORDER_LINE can be downloaded and used as template.

Note: The MVB view and the MVT table should have matching columns except for the create date column, MVT_CREATED_DT, that only exists in the MVT. See Installation and Deployment Considerations for more information

MVS View

The framework will make sure to create the view called MVS, i.e. the source view that reads from the base view (MVB) when inserting into the incremental load table  (MVT).

When the MVS view is created it will for a given entity add the registered MVT column name, i.e. MVT_CREATED_DATE_COL_NAME, as the alias for SYSDATE. Thus the column name defined via MVT_CREATED_DATE_COL_NAME in the MVT will contain the timestamp when a row was saved  in the table.

Note: IFS Developer Studio will always create the timestamp column with the name MVT_CREATED_DT as the last column in the MVT table definition. Thus in the snapshot metadata the property MVT_CREATED_DATE_COL_NAME will get that column name as value. The MVS view is created based on column names in the MVT but fetching from the MVB. See Installation and Deployment Considerations for more information.

Keeping Track of Changes

In order for the framework to keep track of changes in source tables it is necessary to create an associated Materialized View for each one of the source tables. The MV is named CMV and it means Check MV, i.e. a MV that checks/keeps track of the latest rowversion/timestamp.

Figure 3: Incremental Load - using Materialized View to store ROWVERSION

  • The Materialized View is defined to select the maximum ROWVERSIONfrom the source table
  • The ROWVERSIONmust be of DATE type.
  • If the table does not contain a ROWVERSION column, it must be added and taken care of by the business logic
  • If ROWVERSION of data type NUMBER exists, it is necessary to either modify the data type to DATE or to add a new column of DATE type that has the same meaning as ROWVERSION but expressed as a date with timestamp. In both cases this leads to changes to the business logic.
  • It is suggested to create an index on the ROWVERSIONcolumn in the source table
    • The index will improve the time it takes to find the maximum ROWVERSION in a source tables
    • The effect on other framework generated statements might improve to some extent in some cases but the important thing is still to quickly find the maximum ROWVERSION.
    • If there is a worry that a new index will have negative effects on the source table performance due to total number of indexes, typically in a case where the source table may have large number of transactions, the index can be skipped. There is always a possibility to add this type index as a customization. In this case, please add a note in the entity metadata that some or all associated source tables are lacking index on ROWVERSION.
  • IFS Developer Studio create a so called CMV file with all source table specific check MVs in an entity specific file. The CMV file is named Cmv_<component><Entityclientname>.CRE e.g. Cmv_OrderFactCustomerOrderLine.CRE

Below follows a definition of a Materialized View that gets the maximum ROWVERSION from CUSTOMER_ORDER_TAB

DEFINE MV_NAME       = CUST_ORD_CHK_MV  
DEFINE SOURCE_TABLE  = CUSTOMER_ORDER_TAB  

DECLARE  
   stmt_            VARCHAR2(32000);  
BEGIN  
   Database_SYS.Remove_Materialized_View('&MV_NAME',TRUE);  
   stmt_ :=      
'CREATE MATERIALIZED VIEW &MV_NAME  
BUILD DEFERRED  
 USING NO INDEX  
 REFRESH COMPLETE ON DEMAND  
 AS  
 SELECT MAX(ROWVERSION) MAX_ROWVERSION  
 FROM &SOURCE_TABLE';  
      
   EXECUTE IMMEDIATE stmt_;    
     
   IS_MV_REFRESH_INFO_API.Clear_Refresh_Info('&MV_NAME');  
END;  
/  
UNDEFINE MV_NAME  
UNDEFINE SOURCE_TABLE  

About files:

  • Each entity has its own CMV file
  • If the entity has a dynamic dependency, i.e. referring a source table in a dynamic dependent component, then the MV definition is not created right away. Instead the definition will be posted in an internal storage and if the source table component exists then the installation process will make sure that the CMV is created. Note: The file creating CMVs can be manually deployed in a development situation. The file contains instructions how to handle the manual case. For all other cases it is necessary to run the IFS Installer that will generate and execute a proper database installation template that performs all necessary actions.
  • If the check MVs are added, modified or removed as part of a bug (or defined as part of project development) it is necessary to define a CDB file with all definitions included.
  • If the check MVs are added, modified or removed from one release to another, the changes must be defined in a UPG script.

Note: The CMV file generated by IFS Developer Studio should be used as the template when creating CDB/UPG files.

The complete CMV definition example for FACT_CUSTOMER_ORDER_LINE can be downloaded and used as template.

Also consider the section about handling of dynamic source table references.

How to Automate Refresh of Check MVs

The Materialized Views that are needed to store the current max value of ROWVERSION in the referenced source tables needs to be refreshed to have up-to-date content.

The incremental load framework will, during the incremental processing, always refresh any referenced Materialized Views that are not FRESH. This ensures that the incremental load is based on accurate information.

It is however possible to set up a scheduled task that takes care of keeping all the referenced Materialized Views up-to-date. The benefit of doing this is to reduce the time for the incremental load processing.

  • Use the scheduled task Refresh Source Change MVs of Incremental Sources
  • Define a schedule that runs e.g. once every half hour.

Using Source Views

In some cases the table dependency analysis will lead to that it is very difficult to define a dependency path between the tables. Since it is recommended to keep the number of tables in the dependency structure to a minimum, the framework provides a possibility to define source views that are associated with a source table. It might also be so that the source table is rather general and that only a sub set of the existing records have to be considered. In order to handle the filtering, thus reducing the number of rows to check for updates, a source view can be defined. When the framework is processing a source table, it will check if one or more associated source views have been defined and if so, use these views to check for updates instead of using the source tables.

A example is the fact FACT_CUSTOMER_ORDER_LINE. The dependency analysis gives at hand that there is a dependency to the source table RENTAL_OBJECT_TAB. This table contains rental information of different types and it is only necessary to handle rentals related to customer orders.

A source view is defined. The parent table according to the analysis is CUSTOMER_ORDER_LINE_TAB so it is natural trying to connect RENTAL_OBJECT_TAB with CUSTOMER_ORDER_LINE_TAB using the parent table keys. We also want to limit the number of rental objects by defining a filter.

-------------------------------------------------------------  
-- Source view related to changes in RENTAL_OBJECT_TAB  
-- Means that this view should be placed in RENTAL component  
-------------------------------------------------------------  
DEFINE VIEW = RENTAL_OBJECT_BIS  
PROMPT Creating snapshot source view &VIEW  
CREATE OR REPLACE VIEW &VIEW AS  
SELECT order_ref1             order_no,  
       order_ref2             line_no,  
       order_ref3             rel_no,   
       TO_NUMBER(order_ref4)  line_item_no,  
       rowversion             rowversion  
FROM rental_object_tab  
WHERE rental_type = 'CUST ORDER'  
WITH READ ONLY;  

COMMENT ON TABLE &VIEW  
   IS 'LU=&LU^PROMPT=Rental Object^MODULE=&MODULE^';  

Some observations/comments:

  • The view is selecting the necessary general key columns that are related to customer order line and they are given an alias that matches the name in customer order line table.
  • A filter condition has been added to sort out only rental objects related to customer orders
  • The view has been given a name ending with _BIS, e.g. Business Intelligence Source view
  • The view comment is important but note that the above definition is just an example.
  • The view should preferably be defined in the RENTAL component.

Note: From Applications 9 it will be necessary to define the BIS view in a VIEWS file. This view can be regarded as a server only view which means that the annotation @ServerOnlyAccess could be used.

Below is an example of a VIEWS file.

@ServerOnlyAccess  
VIEW Rental_Object_Bis IS  
   Prompt = 'Rental Object'  
SELECT order_ref1             order_no,  
       order_ref2             line_no,  
       order_ref3             rel_no,   
       TO_NUMBER(order_ref4)  line_item_no,  
       rowversion             rowversion  
FROM rental_object_tab  
WHERE rental_type = 'CUST ORDER';  

Note: The incremental load specific metadata file is yet to be defined. How to more in detail define this file can be found on a page called Incremental Load Specific Metadata.
Since IFS Developer Studio supports generation of all necessary files, use the tool to create a model that generates all necessary files with accurate content.

Modifying Data Mart and Online Views

For the Data Mart view the following applies:

  1. The view should read from the MVT table (instead of reading from a Materialized View). This is of course handled in the model. If the model support incremental load, it will be expected that the source table end with _MVT
  2. Make sure that the column MVT_CREATED_DT of data type DATE is defined in generated view file. The column that contains the timestamp for each created row.
  3. The tool generates a unique key column default named ID. In the data mart version the derived value will be present in the MVT table. Just make sure that the unique column is selected as the first column in the view.
DEFINE MV = CUSTOMER_ORDER_LINE_MVT  
...  
CREATE or REPLACE VIEW &VIEW AS  
SELECT   
id                                                      id,  
...  
reporting_date                                          reporting_date,  
mvt_created_dt                                          mvt_created_dt  
FROM &MV col  
-- Standard security implementat