Datamart: Technical White Paper

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

Introduction:

This document provides additional information on configuring Datamart settings and describes additional technical information about the tables and data that is generated for the 'NBI' Datamart tables. This document is useful for understanding the basic concepts of the Datamart purpose and functional aspects from inception.

Solution:

Customers require the ability to present summary and detailed analytical data. Before the Datamart was introduced, the reporting tool was used to access data directly from database tables. This approach became cumbersome since it requires extensive table joins, does not easily provide data aggregation, and does not make good use of the organizational breakdown and other application structures needed for comparison and drill down purposes.

The Datamart is still required for some of the core functionality within the application not related to reporting.  With the introduction of Data Warehouse and Advanced Reporting in v14.2, it is our recommendation to use the Adhoc Views and Reports for custom reporting.

Reference TEC610968 : Is the Datamart Extraction Job and Datamart Rollup Time Facts and Time Summary Job required? (Answer = Yes)

The Datamart consists of:

  • A collection of data points that pertain to projects and resources.
  • Data organized by easy-to-report on "flat" tables organized by fiscal period, calendar periods, and organizational break down structures (OBS).
  • Data aggregated and stored in an easily understood set of "rollup" tables. These tables are also organized by fiscal period, calendar periods, and by organizational break down structures (OBS).
  • Configuration and set up options to configure the Datamart. Configuration choices include "What OBS is the Datamart grouped by" and "Datamart Currency".
  • Support for alerting (or stop-lighting) and trending analysis.
  • Four background scheduled jobs ("Time Slicing", "Rate Matrix Extraction", "Datamart Extraction" and "Datamart Rollup - Time Fact and Time Summary") that can be scheduled to run at recurring intervals.

Database Table and View Descriptions

The following information provides details to identify the CA PPM database tables populated by the Datamart jobs and related database views that can be leveraged for custom reporting.

The information provided regarding the tables is informational; it is not required that you use these tables for your custom reporting needs.

It is our recommendation to use Advanced Reporting to develop Adhoc Views and Reports in v14.2 and beyond. 

Use the Technical Reference Guide for additional information.

Table (T) or View (V) Name 

Extract Data

  • Project, Resource, Task information per day
    • T: NBI_PRT_FACTS
    • V: NBI_PROJECT_RES_TASK_FACTS_N_R  
  • Project management and Financial management information in one view
    • T: NBI_PROJECT_CURRENT_FACTS
    • T: NBI_PROJECT_FORECAST 
    • V: NBI_PROJECT_CURRENT_FACTS_V 
  • Resource management information as-of-today
    • T: NBI_RESOURCE_CURRENT_FACTS 

Rollup Data

  • Project management time-bucketed information (including the project id) will be available for calendar weeks and calendar months
    • T: NBI_PM_PT_FACTS
    • V: NBI_PM_PROJECT_TIME_FACTS 
  • Financial management time-bucketed information (including project id) will be available for fiscal periods (months) only
    • T: NBI_FM_PT_FACTS
    • V: NBI_FM_PROJECT_TIME_FACTS 
  • Project management rolled up information (no project ids, just OBS and calendar hierarchy) will be available by calendar weeks, months, quarters and years
    • T: NBI_PM_PROJECT_TIME_SUMMARY 
  • Financial Management rolled up information (no project ids, just OBS and fiscal calendar hierarchy) will be available by fiscal periods, fiscal quarters and fiscal years
    • T: NBI_FM_PROJECT_TIME_SUMMARY  
  • Rolled up resource (including resource id) information will be available per day
    • T: NBI_R_FACTS
    • V: NBI_RESOURCE_FACTS 
  • Resource time-bucketed information (including the resource id) will be available for calendar weeks and calendar months
    • T: NBI_RT_FACTS
    • V: NBI_RESOURCE_TIME_FACTS 
  • Rolled up resource information (excluding resource id) will be available per calendar week, months, quarters and years
    • T: NBI_RESOURCE_TIME_SUMMARY

 

CA PPM Application

Configuration and Setup

A number of steps must be performed before the Datamart can be run for the first time. If those steps have not been followed, the Datamart Extraction job will throw an exception and stop processing. If this happens, verify that all configuration and setup steps have been completed and then rerun the extraction.

  • Set up Organizational Breakdown Structures (mandatory)

    The natural OBS Units associated with all data will remain intact, however, all Datamart tables are grouped by up to five OBS types for use in custom reporting. This means that customers can look at resource and project data from five different angles, either combined or one-by-one. The mapping of Datamart tables to OBS types is done using the Administration, Data Administration: Datamart Settings administration page.  It is mandatory to have at least 1 Project Default OBS Unit and 1 Resource Default OBS Unit for a successful execution of the Datamart jobs, even if you do not plan to use these grouping in any custom reporting.
    • Reference TEC559904 : Datamart Default OBS Units
  • Set up Financial Periods (mandatory)

    All financial management (NBI_FM%) tables are grouped by fiscal time period rather than calendar time period (NBI_PM% and NBI_R%). The Datamart picks up on "Monthly" time periods and none of the other period types. This has to do with the fact that the financial management information is stored by fiscal period (monthly), quarter and year which can all be derived from the "Monthly" time period. Financial Management Time Periods are set up under Administration, Finance Setup: Entities, open or create an Entity and go to the Fiscal Time Periods tab. 
    Below is a list of technical articles that can help in understanding the configuration of setting up Fiscal Periods and what messages can be encountered it the periods are not configured properly. 
    • Reference TEC500348 : Datamart Entity  

    • Reference TEC497531 : The Datamart Extraction job is failing with the error NBI_CFG_NO_FISCAL_PERIOD - No Fiscal Periods have been setup  

    • Reference TEC549469 : Datamart Rollup Job Failed - Determine current fiscal period no data found

    • Reference TEC549470 : Datamart Rollup Job Failed - Cannot insert the value NULL into column 'FISCAL_TIME_KEY' table 

    • Reference TEC439811 : Additional periods automatically created when Fiscal Time Periods are set to a 4-4-5 weekly structure for monthly period type 

    • Reference TEC550794 : Is there a way to modify, archive or deactivate Fiscal Time Periods that have already been used? 

    • Reference TEC439153 : Why don't weekly fiscal periods appear in NBI_DIM_FISCAL_TIME table? 

    • Reference TEC534747 : Can Fiscal Time Periods be XOGGED? 

  • Currency Exchange Rates (mandatory)

    All financial data in the Datamart is stored in one currency (as specified in the Administration Home Datamart Settings page). The decision to only have one Datamart currency was made so that aggregated summary information does not need to be stored by currency in addition to time period and OBS type. In addition to that, reporting becomes much easier because you to convert only from one currency (Datamart currency) to the report's currency. In a mult-currency configuration, to perform that currency conversion, conversion rates must be set up under Administration, Finance, Setup: Foreign Exchange Rates . Note: If are operating in a single currency environment, you do not need to set up any exchange rates.

  • Datamart Settings (mandatory)

    Now that the prerequisite configuration is setup, the Administrator can now configure the Datamart Settings. The purpose of the Datamart Settings page is to designate the Datamart currency, the Entity for providing the Monthly Fiscal Time Periods and to map at least 1 OBS Unit to both resources and projects. Navigation Path: Administration, Data Administration, Datamart Settings.

    The 3 options for the section titled 'Customization of Datamart Extraction' by default are enabled and when the job runs, Project Management, Financial Management and Resource time facts and time summary facts are extracted into the facts and rollup tables.  The Datamart Extraction uses these options for populating some of the data before the Rollup activities occur from the Rollup job.  Our recommendation is to be sure to have all 3 options selected to ensure that you do not have any missing data from the Extraction or from the Rollup activities.

    Even after the Datamart Extraction job has been run, the OBS type assignments can be changed on this page.  Running the job after making the changes to this page will clean out the table and rebuild it with the new configuration.  

  • Stoplighting (optional)

    Stoplights provide the capability to flag projects that meet certain criteria. For example, customers may want to flag all those projects in the application with a "Red Stoplight" whose actual hours are greater or equal to the budgeted hours. Up to 15 stoplights for projects are stored in NBI_PROJECT_CURRENT_FACTS ( STOPLIGHT_1 through STOPLIGHT_15 ). Value 1 is designated as Green, 2 is Yellow, and 3 is Red. Stop light conditions are implemented using ANSI SQL. Those conditions are written in ANSI SQL and can only include columns that are available in the NBI_PROJECT_CURRENT_FACTS table. The columns contained in this table are documented in the Technical Reference Guide . Stoplights are administered from Administration, Data Administration: Datamart Stoplights.

Following are several SQL examples of stoplights:

Stoplight ANSI SQL

Project is less than 90% complete : PCT_COMPLETE < 90
More than 50 tasks remain open : OPEN_TASKS > 50
Actual hours are equal or greater than Budgeted Hours : ACTUAL_HOURS > BUDGET_HOURS
Actual equipment cost is more than actual labor cost : COST_ACT_EQUIP > COST_ACT_LAB
More complex WHERE clauses such as " (A + B) / ((C - D) * (E + F)) " are also supported.

  • Time Slice Management (mandatory)

    Reference TEC435572 : How should I configure my Time Slices?

    Five time slices are used in the Datamart:

    • Slice ID =  1 : DAILYRESOURCEAVAILCURVE (Availability) 
    • Slice ID =  2 : DAILYRESOURCEACTCURVE (Actuals) 
    • Slice ID =  3 : DAILYRESOURCEESTCURVE (Estimates) 
    • Slice ID = 10 : DAILYRESOURCEALLOCCURVE (Allocation)  
    • Slice ID = 11 : DAILYRESOURCEBASECURVE (Baseline) 

    Each slice is administered from Administration, Data Administration: Time Slices.

    Our recommendation is to ensure that the slices are defined with a From Date for the first day of the month, at least a minimum of 3 months prior to the first day of the current month.  Refer to the technical article on configuring slices for more details.

    Important: If you have not loaded any historical project data and your project planning into the future does not go beyond 365 days, there is no need to change the default time slice settings. However, if this is not true, you need to reconfigure the time slices. Following is a description of how the time slice attribute should be set up for these time slices. When you change the time slice settings, all data for the respective time slice is deleted and re-calculated. This could be a lengthy process so care should be taken when you reconfigure time slices. If you have changed any of the time slice definition you MUST wait until the time slice engine has caught up before running the Datamart again. The time slice engine will have caught up once the "Time Last Run" column on the "Time Slice Management" page is not blank anymore for the respective time slice. One other important setting to consider is the fact that all labor resources should have a "Date of Hire" set to non-blank. This way availability data is being extracted starting with the date of hire rather than from the "From Date" specified for time slice "DAILYRESOURCEAVAILCURVE". 
     
  • Default Rate Matrix (optional)

    Only follow the below steps if you are using financial management capabilities and want to extract resource cost and revenue information into the datamart.  The financial data is populated when you have the Datamart Extraction options for financial enabled.

    Projects that are not enabled for financial management will have a cost and billing rate of zero unless you set up a default rate matrix and assign it to the respective transaction types. A rate matrix is created and administered from Administration, Finance: Manage Matrix. Once the default matrix has been defined, it can be associated with the Datamart Entity, as a default.  
  • Including or Excluding Resources from Datamart

    If you want to exclude resource-specific information from the Datamart, you can update the Resource general properties to uncheck the box 'Include in Datamart'.  You may want to exclude resources that do not have assignments or do not need availability tracking extracted into the Datamart.  This flag only affects the population of the RESOURCE fact tables.  It does not impact the Project level fact tables.
    • Reference TEC439733 : What is the purpose of the Resource - Include in Datamart Flag? 

 

Scheduling Datamart Jobs

Reference TEC528729 : Configuring and Scheduling Some of the Main Jobs

There are four jobs that make up the Datamart and they should all be marked incompatible so that they can run serially and not concurrently to ensure you have correct data.

  1. Time Slicing 
    • This job extracts daily slice values for Actuals, Estimates, Availability, Allocations and Baselines.  
    • The date ranges of these slices determine the data that is put into the Datamart tables
  2. Rate Matrix Extraction 
    • This job extracts rate matrix information and is used when the Datamart Extraction and Rollup jobs populate financial data
    • The Rate Matrix Extraction job should be updated prior to executing either Datamart job  
  3. Datamart Extraction 
    • The Datamart Extraction data tables are cleaned out and repopulated for improved performance  
    • This job populates the Monthly Fiscal Time Periods from the Datamart Entity (NBI_DIM_FISCAL_TIME) 
    • This job populates the calendar date ranges that are used within TSV time-varying attributes (NBI_DIM_CALENDAR_TIME) 
      • Reference TEC435578 : Why does the Datamart Calendar table set up the weekly periods to start on Mondays?
    • This job populates a flat table of all OBS Units within the system (NBI_DIM_OBS_FLAT) ; this configuration table is cleaned out and repopulated so that any changes to OBS are updated  
    • The investment financial data is only populated if it has BOTH a Department and Location OBS Unit selected on the Financial Properties subpage
      • Reference TEC534959 : Project Financial Plans are not appearing in the Datamart Table NBI_PROJECT_FORECAST 
    • This job should execute before the Datamart Rollup job
  4. Datamart Rollup - Time Facts and Time Summary 
    • Reference TEC439442 : Datamart Rollup tables - How are the tables populated?
    • This job depends on some data that is populated from the successful execution of the Datamart Extraction job 
    • The Time Facts tables will rollup data by Investment or Resource into the calendar buckets for week, months, quarters and years 
    • The Time Summary tables will rollup data into calendar buckets for week, months, quarters and years  

 

Using Data Warehouse (v14.2+)

With the introduction of the Data Warehouse (DWH) in CA PPM 14.2 for advanced reporting, you will be able to find comparable database views within the CA PPM database.  It is our recommendation that you first explore the Advanced Reporting, Adhoc feature to retrieve the data you are seeking.  If you do not find an existing stock report, the Adhoc feature allows you to build a report quickly.  You can also engage our CA Services team for assistance in custom reporting needs.

For more information on the current reporting functionality, please visit our product documentation site.