Datamart Extraction and Datamart Rollup jobs and when they need to run

Products

Clarity PPM On Premise

Issue/Introduction

What data does the Datamart Extraction job prepare and is this data required for the successful operation of the Clarity application? Can you deactivate this job and not use it? Similarly, with the Datamart Rollup Job, is this job needed?

Resolution

For successful operation of the product, execute the Datamart Extraction job on a regular schedule.

Best Practice for the Datamart Extraction Schedule is to run the job daily and schedule it to run during off-peak hours. Based on the volume of data to extract, the time and system resources that are required to process the request can be significant.

It's recommended to review the details at Configure the Datamart for detailed information regarding the Datamart jobs and configuration. Some highlights of the Datamart:

Datamart Extraction job

The Datamart Extraction job is required to run nightly because the job populates the core data points described below. The job calls several stored procedures to populate the following tables:

NBI_DIM_OBS and NBI_DIM_OBS_FLAT, which are needed for OBS references
NBI_DIM_CALENDAR_TIME, which is needed for all non-fiscal time-scaled varying (TSV) grids
NBI_DIM_FISCAL_TIME, which is needed for all fiscal TSV grids and financial processing.

Essentially, all portlets rely on the successful completion of the job. The remaining actions of the Datamart Extraction job populate other NBI tables used to summarize or roll up data based on daily sliced data. The data in these other NBI tables are not required for standard Clarity functionality and are meant for you to use in custom reporting. The execution of the remaining data is dependent on the configuration of Time Slice Definitions, Resource, and Project activities that generate event records in the NBI_EVENTS table.

It's recommended to run this job during periods of low or no user activity as it uses many system resources.

See the below excerpts as referenced at Datamart Jobs:

You need to execute the Datamart Extraction job daily to ensure you can use Clarity effectively in your organization. The job should run with the below options checked:

Extract Only OBS
Extract Only Calendar

The Datamart Extraction job updates the OBS and CALENDAR tables in the database.

Running the FULL Data Extraction job (without selecting the parameters) updates all the EXTRACTION tables which are required for a successful run of the Datamart ROLLUP job. The only reason you want to do this is if you are using these tables for CUSTOM reports (not DWH) or CUSTOM Portlets that use these tables. (all other NBI tables)

These remaining NBI Tables are NOT required for the successful operation of the product. They are not used in the UI, not used in stock reports, and not used in Data Warehouse.

Datamart Rollup job

The Datamart Rollup Job is used for custom reports / portlets.

See the below excerpts as referenced at Datamart Jobs:

Additionally:

Custom Portlets and Reports

If you have any custom portlets or reports which use the Datamart and/or the Datamart Rollup tables, you need to run the Datamart Extraction job without selecting the parameters. The job will update all the extraction tables. If you use the Datamart Rollup tables:

You will need need to run the Datamart Extraction job without any parameters.
Run the Datamart Rollup job.

Tables populated by the jobs

The ones with an asterisk (*) are required for a successful operation of the product. The other ones are only required if there are legacy custom reports or custom portlets using them for which you have not converted to using transactional data.

We don’t recommend using the optional tables anymore because we now have the Data Warehouse.

NBI_DIM_CALENDAR_TIME	*Datamart Extraction - Extract Only Calendar
NBI_DIM_FISCAL_TIME	*Datamart Extraction - Extract Only Calendar
NBI_DIM_OBS	*Datamart Extraction - Extract Only OBS
NBI_DIM_OBS_FLAT	*Datamart Extraction - Extract Only OBS
NBI_PRJ_MATRIX_DATES	*Rate Matrix Extraction
NBI_PROJ_RES_RATES_AND_COSTS	*Rate Matrix Extraction Job
NBI_PROJ_RES_RATES_AND_COSTSCP	*Rate Matrix Extraction Job
NBI_CFG_OBS_ASSIGNMENTS	Datamart Configuration Table - updated from UI
NBI_CFG_STOPLIGHT_QUERIES	Datamart Configuration Table - updated from UI
NBI_EVENTS	Datamart Extraction Job
NBI_PROJECT_CURRENT_FACTS	Datamart Extraction Job
NBI_PRT_FACTS	Datamart Extraction Job
NBI_RESOURCE_CURRENT_FACTS	Datamart Extraction Job
NBI_R_FACTS	Datamart Extraction Job
NBI_RUN_LOGS	Datamart Extraction Job
NBI_RT_FACTS	Datamart Extraction Job
NBI_FM_PROJECT_TIME_SUMMARY	Datamart Rollup Job
NBI_FM_PT_FACTS	Datamart Rollup Job
NBI_PM_PROJECT_TIME_SUMMARY	Datamart Rollup Job
NBI_PM_PT_FACTS	Datamart Rollup Job
NBI_PROJECT_FORECAST	Datamart Rollup Job
NBI_PRTF_FM	Datamart Rollup Job
NBI_RESOURCE_TIME_SUMMARY	Datamart Rollup Job
NBI_ROLLUP_SQL	Datamart Rollup Job

Additional Information

Reference Configure the Datamart for information on Datamart Settings and Financial Management minimum configuration setup.