Loading relational data incrementally and reloading OLAP cube data

Before you load relational data incrementally and reload OLAP cube data, fulfill these prerequisites:

  • In the Staging database in relational modeling, create separate tables with fact data, metadata, and mappings.
  • For each table in relational modeling, store a full load query.
  • In OLAP, create an OLAP cube with dimensions that contain fact data, metadata, and mappings.
  1. To load relational data incrementally, in EPM Administration, select Dashboards > Data Integrations > Relational Modeling.
  2. Select the appropriate staging table and click the Load Queries tab.
  3. Copy the full load query from the Query Editor.
  4. Click Add to create a new load query and then specify this information:
    Name
    Specify IncrementalLoad.
    Source Connection
    Select Data Lake.
    Description
    Optionally, specify the description of the query.
    What to do with conflicts?
    The Update check box is selected by default. If selected, new records are added and existing records are updated.
    Query Editor
    Paste the copied query. Then, add this condition to the query:
    WHERE LastModified > '<<LAST_IMPORT>>'

    For example:

    SELECT [RecordID]
    ,[ID]
    ,[Calendar]
    ,[Adventurer]
    ,[Coffee]
    ,[Measure]
    ,[Value]
    ,[ModifyDate]
    ,CURRENT_TIMESTAMP AS dEPMLastModified
    ,'Actuals' AS Scenario
    FROM ['CA_FactCoffeeAdventure']
    WHERE LastModified > '<<LAST_IMPORT>>'
    
  5. Click Load and then Load Data.
    The Last Loaded date and time is displayed.
  6. To reload OLAP cube data, select Dashboards > OLAP > Edit Database and select the appropriate cube.
  7. Click the Mappings tab and select the mapping with the _FullLoad suffix.
  8. In the Details section, click the Column Mapping tab and ensure that the Clear Cube Area column contains Actuals for the Scenario dimension.
  9. If you make any updates, click Save.