Data Mart Refresh Recommendations

This page supplies some general recommendations related to refresh of Data Mart sources.

Use this page to learn how to activate, refresh and schedule Data Mart sources related to a dimension or Information Source entity.

Note: The recommendations on this page only targets Data Mart sources referenced by Information Sources available in IFS Business Reporting & Analysis services

Contents

• General Approach
• Predefined Data Mart Refresh Categories
• Incremental Load

General Approach

First make sure that the Data Mart Source Cache is up-to-date.

Step 1

Identify the Information Sources that are needed for the customer requirements.

The next step would then be to define how to refresh involved Data Mart source, i.e. how and when to create/update Data Mart sources, Materialized Views or incrementally loaded tables.

Normally the activation of Data Mart sources the next step, i.e. creating the first snapshot. This task can be handled separately by the assistant related to command Activate DM Source(s) in the Information Source page but since this can be a time consuming operation it is suggested to follow the recommendation in the following steps instead.

Step 2

Use the Explore Data Mart Entities functionality to select an Information Source and set the related Materialized Views as Active.

• In the Explore By Information Source page, select one Information Source
• Next select all rows and use the command Set as Active.

  All selected Data Mart sources, either based on Materialized Views or Incremental tables, referenced by the Information Source are set as Active.

  Continue until all Information Sources have been taken care of.

  Note: This action does not mean that any snapshots are created; selected Materialized Views are only defined as being Active

  

  Figure 1: Set all Data Mart sources related to one Information Source as Active

Step 3

Next create a new Data Mart Refresh Category or modify existing categories to include the Active Data Mart sources.

• To consider:
  • Should all referenced Data Mart sources be defined in the same refresh category or not?
  • What refresh categories to use for the Data Mart sources?

    E.g. fact related Data Mart sources can be defined either in the same category or grouped into categories of same type, i.e. business area, product area, report related, functionality related etc.

• It is suggested to use the Information Source page.
• Use the command Add DM Sources to Refresh Category, that will open an assistant.
• Select one of more of the previously handled Information Sources in the item picker control.
• Choose if a new category should be created or if an existing category should be used.
• Decide referenced Data Mart sources to be included. If the category should only represent the Fact sources, then choose Select only Directly Related DM Sources
• Verify the selections and Finish.

  

  Figure 2: Add Data Mart sources to a Data Mart Refresh Category

• It is advised to include a Data Mart source in only one refresh category.

  The assistant will give a warning if an attempt is made to add a Data Mart source that is already connected to another category.

Step 4

Consider how to schedule the created/modified refresh categories.

• Information Sources that only support a few reports running at the end of the month will need a refresh category that is scheduled closed to when the reports are about to be executed.
• Dimension specific Data Mart sources should be referenced at least once per day.

  Depending on the time it takes to perform the refresh it is of course possible to perform refresh 2-3 times a day as long as there is a time slot that permits this operation to be executed.

• If two or more reports are based on the same Information Source and one runs each day and the other runs once each month, then the Data Mart Refresh Category should be refreshed daily to make sure that the 1st report is accurate when ordered.

Step 5

Use the Data Mart Refresh Categories page to make sure that correct Data Mart sources are defined in each one of the categories to be used.

Figure 3: Data Mart sources added to a Data Mart Refresh Category

• If a category contains Data Mart sources that are referenced by other categories, then this situation should be investigated.

  Locate the Data Mart sources by looking at the list of connected Data Mart sources. Use the column Referenced by Other Categories to find the Data Mart sources that are referenced by more then one category.

• If there are Data Mart sources listed that do not have any information about Refresh Status, the reason is most likely that the Data Mart Source Cache is not up-to-date.

  Please refer to the Special Data Mart Source Related Subjects for more information.

• In the detail list, use command View Connected Categories to locate other categories where a specific Data Mart source is referenced.

   

  

  Figure 4: Connected Data Mart Refresh Categories

Step 6

Create a scheduled task that handles refresh of Data Mart sources connected to a category.

• Use Schedule Refresh command to define a schedule

  

  Figure 5: Schedule Task option in Data Mart Refresh Categories

• The New Task Schedule page will open

  

  Figure 6: New Database Task Schedule

• Modify the task name if needed
• Define how the refresh should be performed and save
• The following happens during refresh
  • If a Data Mart source has state/staleness UNUSABLE and is not marked as Active, it will not be refreshed
  • If a Data Mart source has state/staleness UNUSABLE and is marked as Active, it will be refreshed (actually activated, i.e. creating the first snapshot)
  • If a Data Mart source has a state/staleness not equal to UNUSABLE and is not marked as Active, it will not be refreshed
  • If a Data Mart source has a state/staleness not equal to UNUSABLE and is marked as Active, it will be refreshed

   

Note: It is not required to run the Data Mart Refresh Category schedules sequentially. If a given Data Mart source is connected only to one category, those categories can be refreshed in parallel to reduce the overall time taken to refresh all the Data Mart sources.
Parallel execution can be achieved by defining the Refresh Materialized View Categories schedule job in a separate background queue and assigning more than one process to the queue.
Read about Batch Queues Configuration in the technical documentation for more information.

Predefined Data Mart Refresh Categories

Data Mart source delivered by IFS Applications are by default added to pre-defined refresh categories.

The reason is to simplify the setup of scheduled refresh tasks.

Figure 7: Predefined Data Mart Refresh Categories

With each pre-defined category also comes a Usage Note with a suggested refresh schedule.

All dimension related Data Mart sources have been grouped into the category named DIM_ALL

All translation specific Data Mart sources have been grouped into the category named TRANSLATIONS

Data Mart sources directly referenced by Fact views have been grouped into one category per product area.

Note: If a refresh category is supposed to be used also to activate referenced Data Mart sources, i.e. the schedule is supposed to be used for the first as well as for all following refresh operations, it is necessary to mark the Data Mart sources as Active before the task is scheduled.
How to mark Data Mart sources as Active is described in Special Data Mart Related Subjects.
However in the detail part of the Data Mart Refresh Category feature it is also possible to activate connected Data Mart sources.

Incremental Load

Incremental load, i.e. when the Data Mart source is a table that is incrementally updated, can be administrated in the same way as Materialized Views but since incremental load is, to some extent, supported by a separate framework there are a set of forms that are specifically dedicated for this Data Mart source.

Read more about incremental load on page Handling Incremental Load.