Loading relational data and OLAP cube data incrementally

  1. In EPM Administration, select Dashboards > Data Integrations > Relational Modeling.
  2. In the Target Connection field, select Staging.
  3. To create a copy of a fact table, click New Table and specify this information:
    Name
    Specify the same name as the original table name and add a suffix _temp to the name.
    Description
    Optionally, specify a description of the table.
  4. Click Next.
  5. Click Add columns from query and specify this information:
    Query Name
    Specify IncrementalLoad.
    Application
    Select the application to which to load data.
    Source Connection
    Select Staging.
    Query Description
    Optionally, specify a description of the query.
    Query
    Specify the SELECT * FROM ['Table'] query to extract data from the table. For example, SELECT * FROM ['CA_FactCoffeeAdventure'].
  6. Click Add.
  7. In the table, verify data types for each column and, if required, update those types in the Type column.
  8. Select the Primary Key check box for the ID column and click Finish.
  9. Select the original table from the list and click the Load Queries tab.
  10. Select the IncrementalLoad query and copy the query from Query Editor. For example, this query: 
    SELECT [RecordID]
,[ID]
,[Calendar]
,[Adventurer]
,[Coffee]
,[Measure]
,[Value]
,[ModifyDate]
,CURRENT_TIMESTAMP AS dEPMLastModified
,'Actuals' AS Scenario
FROM ['CA_FactCoffeeAdventure']
WHERE LastModified > '<<LAST_IMPORT>>'
  11. From the list, select the table with the _temp suffix and click the Load Queries tab.
  12. Select the IncrementalLoad query, then select Data Lake in the Source Connection field, and paste the copied query into Query Editor.
  13. In the query, make these changes:
    • In the SELECT condition, add 0 AS [Value].
    • In the FROM condition, use the Infor.IncludeDeleted['Table name'] function.
    • Add this condition: AND ToDelete = 1. 1 is an example value. You must specify a value that you have defined as an indication that a record is deleted.

    For example:

    SELECT [RecordID] 
,[ID] 
,[Calendar] 
,[Adventurer] 
,[Coffee] 
,[Measure] 
,0 AS [Value] 
,[ModifyDate] 
,CURRENT_TIMESTAMP AS dEPMLastModified 
,'Actuals' AS Scenario 
FROM Infor.IncludeDeleted['CA_FactCoffeeAdventure'] 
WHERE LastModified > '<<LAST_IMPORT>>' 
AND ToDelete = 1
  14. Click Save.
  15. Click Load and then Load Data.
  16. Select Dashboards > OLAP > Edit Database and select the appropriate cube.
  17. Click the Mappings tab and then New Mapping.
  18. On the Source tab, specify this information:
    Mapping Name
    Specify the mapping name with the _IncrementalLoad suffix.
    Mapping Unique Name
    Specify the mapping unique name.
    Comment
    Optionally, specify a comment about the facts mapping definition.
    Data Connection
    Select Staging.
    Fact Table Name
    Select the fact table with the _temp suffix.
  19. Click the Column Mapping tab and specify this information:
    Select Value Column Mode
    The Single option is selected by default.
    Value Column Name
    Select Value.
    Source Column Name
    In the table, select appropriate names of source columns for each dimension.
    Clear Values
    Disable this function.
    Writing Mode
    Select Overwrite.
  20. Click Save.
    With this mapping, new records are added and data and deleted records with a value of 0 are overwritten. This ensures that no value is left out.
Related topics