ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

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

book

Article ID: 18756

calendar_today

Updated On:

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

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.