Datamart Rollup tables - How are the tables populated?

Document ID : KB000027009
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

When I execute the 'Datamart Rollup - Time Facts and Time Summary' job for the first time, I only see data for the current period in the Financial or Project Management Time Facts and Time Summary tables, however I see historical data that goes back to the 'From Date' defined on my Daily Time Slices for the Resource Time Facts and Time Summary Tables. Why is there more data in the Resource Tables than the Project tables?

Answer:

The 'Datamart Rollup - Time Facts and Time Summary' job is an optional job that populates tables for use by the customer for developing additional, user-defined reports.  If this job is executed, the 'Datamart Extraction' job should be successfully executed first so that prerequisite data is populated.

Extract project management time facts and summary

The job populates the data in the PROJECT Management Time Facts and Time Summary tables. The program retrieves the data from the NBI_PROJECT_CURRENT_FACTS table (populated by the Datamart Extraction job) for the CURRENT period and if data exists in the Rollup for the prior period, the program will update the data for that period. The program creates two types of periods: the prior or current ISO week and the prior or current calendar month. If the Datamart Rollup job is successful, consistently, you can build historical Project Management rollup date for use in user-defined reports and portlets.
Tables populated by this option: NBI_PM_PROJECT_TIME_SUMMARY, NBI_PM_PT_FACTS

Extract financial management time facts and summary

The job populates the data in the FINANCIAL Management Time Facts and Time Summary tables. The program retrieves the data from the NBI_PROJECT_CURRENT_FACTS table (populated by the Datamart Extraction job) for the CURRENT Fiscal (monthly) period ONLY. If the Datamart Rollup job is successful, consistently, you can build historical Financial Management rollup data for use in user-defined reports and portlets.
Tables populated by this option: NBI_FM_PROJECT_TIME_SUMMARY, NBI_FM_PT_FACTS

Extract resource time facts and summary

The job populates the data in the RESOURCE Time Facts and Time Summary tables. The program retrieves the data from the Time Slice tables. The program checks for a "first-time" run. If this is the first-time the rollup resource tables are populated, then the program will populate records as far back as the 'From Date' in the current set of sliced records. After the "first-time" runs, the program will rebuild (rollup again) data in the Resource Time Facts and Time Summary tables for the prior 8 periods. The 8 periods are calculated as a number of ISO weeks between 8-12 (rounded up) based on the current date. Also, the 8 periods are calculated as a number of calendar months between 2-3 (rounded up) based on the current date. Since the Datamart Rollup job behaves in the same manner as the Datamart Extraction job for the Resource Rollup data, no historical information is retained. The Resource Rollup data is rebuilt each time the Datamart Rollup job is successfully executed.
Tables populated by this option: NBI_RESOURCE_TIME_SUMMARY, NBI_RT_FACTS


Based on the information provided below, we strongly recommend that you do the following to ensure consistency:

  1. Ensure that ALL daily Time Slice definitions have a 'From Date' for the first day of the month, at least 3 months prior to the first day of the current month.

  2. If you elect to run this job, ensure that the 'Datamart Rollup - Time Facts and Time Summary' job is SUCCESSFULLY executed at least once a week. You may schedule this job to run daily after running the 'Datamart Extraction' job.

Additional Information:

Reference TEC435612 : Datamart : Technical White Paper

Reference TEC435572 : How should I configure my Time Slices?