Data Load Overview

Actual Performance Data

The table below lists the Costpoint sources for the loaded actual performance data and which analytics use that data:

Note that any employees, vendors, vendor employee, or subcontractors who you want to include in calculation of labor utilization data must be set up as employees in Costpoint.

PSR Subtotal Types Loaded

The table below lists subtotal types from the PSR reporting table that are included in the data load:

Projects Loaded

The data load process for Costpoint Analytics – Project only includes projects with one of the following project classifications:

• Direct

• Inter-Company

• Inter-Co Multi

Budget Data

Budget information for the analytics comes from spreadsheet files that you create specifically for Costpoint Analytics using Microsoft Excel. See Set Up Budget Data File for more information on how to set up budget files and Revenue and Cost Budget File Format, Backlog Budget File Format, and Labor Utilization Budget File Format for the file formats.

The table below lists the source files for budget data and which analytics use that data:

Each budget data file can contain two budgets for each fiscal period: a baseline budget (the original budget for the year) and a forecast (the most recent budget revision). If you revise your budget multiple times during the year, you must replace the existing forecast data in the data files with the new forecast data each time and load the updated budget data into Costpoint Analytics.

The Labor utilization budget data file can also contain employee target utilization rates in addition to, or in place of, the baseline budget and forecast.

Proposal Data

You can include proposals (pending or potential contracts) in Costpoint Analytics data. If you do, you then have the option to do the following:

• Include expected contract and funding amounts for proposals in the calculation of projected backlog

• Include expected revenue and costs for proposals in projected revenue and profit

Because proposals are usually not set up as projects in Costpoint, you use the Proposal Attributes file, a Microsoft Excel spreadsheet, to identify proposals and specify their key attributes (name, owning organization, project manager, and so on). See Set Up Proposal Attributes Data File for more information on the Proposal Attributes file.

You then enter budget data for the proposals in the Backlog Budget file and the Revenue and Cost Budget file, along with the budget data for actual projects.

The table below lists the source files for proposal data and which analytics use that data:

AR Comments

You can attach comments to outstanding invoices and have those comments appear in the AR Analysis table on the AR and DSO tab in Costpoint Analytics – Project. To do that, you enter the comments, along with the appropriate company ID and invoice number, in a Microsoft Excel spreadsheet provided with Costpoint Analytics. When you load data into the Costpoint Analytics data model, the comments are included.

That spreadsheet file is ARComments.xlsx, and it is located in the <installation location>\Other Data folder.

See Enter AR Invoice Comments for more information on how to enter comments and AR Invoice Comments File Format for the file format.

Data Load Process

To load data, complete the following basic steps:

1. In the Costpoint Analytics Configuration applications, specify the configuration settings you want.

2. Enter or update proposal information in the proposal attributes data file.

3. Enter or update budget data in the budget data files.

4. Run the processes that load Costpoint data budget data, and proposal attribute data.

For more detailed procedures, see Load the Initial Set of Costpoint Analytics Data and Update the Costpoint Analytics Data.

When to Load Project and Employee Data

Normally, you load data into Costpoint Analytics at the following times:

• As part of the implementation of Costpoint Analytics, to load the initial set of data

• Nightly, to keep the actual performance data as current as possible

Before You Load Project and Employee Data

To ensure that the data in Costpoint Analytics is current, do the following before you load data:

• Update the Project Status Report tables in Costpoint

• Post timesheets in Costpoint

• Post AR invoices and cash receipts in Costpoint

• Make any changes or additions to budget data in the budget data files.

• Review outstanding customer invoices and enter any comments related to them in the comments data file.

Loading Labor Utilization Data Frequently

The advantage of loading labor utilization data more frequently than once each period is that it enables your executives to monitor labor utilization in a more timely way. This is important because labor is the largest single cost on most projects and the cost that is most difficult to control.

The disadvantages result from the fact that you cannot enter labor utilization budgets for Costpoint Analytics for segments of time shorter than a fiscal period. If you want to load actual performance data more frequently than at the end of each period, you have two options:

• Enter labor utilization budgets for full fiscal periods only. This approach minimizes the effort required to create budgets and update the Labor Utilization Budget file. The downside is that when you load actual performance data part way through the period, those partial-period hours and costs are compared to the full-period budget hours and costs in the Detail Analysis and Non-Detail Analysis tables. As a consequence, the actual and budget values displayed in those tables are out of sync, and the variances are not correct.

• Enter labor utilization budgets for part of the current fiscal period each time you want to load actual performance data. For example, if you load actual labor data at mid-period, you would also load budget hours and costs for that fiscal period that are half of the full budget for the period. At the end of the fiscal period, you would again load both actual and budget data, but this time both would be for the full fiscal period. With this approach, the budget hours and amounts for the period are always for the same segment of time as that for which you loaded actual performance data, so the comparisons of actual to budget labor data in the Detail Analysis and Non-Detail Analysis tables are correct and meaningful. The downside in this case is the level of effort required to calculate the partial budgets and to update the Labor Utilization Budget file to include them.

Data Load Programs

Costpoint Analytics provides a set of .bat files for loading and updating data. All of the files are located in the <installation location>\Admin folder.

While Deltek recommends that you always use Full_CpA_Data_Load.bat to load data, you can use the other .bat files to save time when you only need to update certain types of data. Each of those files performs a subset of the actions that Full_CpA_Data_Load.bat performs.

To run any of the .bat files to load data, you must log on to the Costpoint Analytics server as the DeltekAnalytics user or as another QlikView-licensed user that you have set up. Do not log on as the DeltekAnalyticsCW user to run these batch files.

• Full_CpA_Data_Load.bat – This process does the following:

• Updates up all data models, based on your configuration settings

• Loads actual performance data from Costpoint for all project and employee analytics

• Loads proposal data from the Proposal Attributes file

• Loads budget data from all budget data files

• Loads organization security data from the security setup file

• Loads AR invoice comments from the comments data file

• Full_CpA_Data_Load_without_Datapull.bat – This process is the same as Full_CpA_Data_Load except that it does not load actual performance data from Costpoint.

• Load_Employees_and_Summary_Data.bat – You can click Run Employee Data Load Batch File in Costpoint Analytics - Employee Configuration to run this file, or you can run it from the <installation location>\Admin folder. This process does the following:

• Updates only the Costpoint Analytics – Employee data model and Costpoint Analytics – Summary data model, based on your configuration settings

• Loads actual performance data from Costpoint for the Labor Utilization Analytics and the corresponding summary analytics in Costpoint Analytics – Summary

• Loads budget data from the Labor Utilization Budget data file.

• Load_Employees_and_Summary_Data_without_Datapull.bat – This process is the same as Load_Employees_and_Summary_Data except that it does not load actual performance data from Costpoint.

• Load_Projects_and_Summary_Data.bat – You can click Run Project Data Load Batch File in Costpoint Analytics - Project Configuration to run this file, or you can run it from the <installation location>\Admin folder. This process does the following:

• Updates the Costpoint Analytics – Project data model and Costpoint Analytics – Summary data model, based on your configuration settings

• Loads actual performance data from Costpoint for the detailed project analytics and the summary project analytics in Costpoint Analytics – Summary

• Loads proposal data from the Proposal Attributes data file.

• Loads budget data from the Revenue and Cost Budget data file and the Backlog Budget data file.

• Loads AR invoice comments from the comments data file

• Load_Projects_and_Summary_Data_without_Datapull.bat – This process is the same as Load_Projects_and_Summary_Data except that it does not load actual performance data from Costpoint.

• Reload_All_Budget_Data_Only.bat – This process does the following:

• Loads proposal data from the Proposal Attributes file

• Loads budget data from all budget data files

Costpoint Analytics SaaS: If you have a Costpoint Analytics SaaS Subscription, the only data load programs you can run are Load_Projects_and_Summary_Data.bat and Load_Employees_and_Summary_Data.bat, which you run using the Run Project Data Load Batch File and Run Employee Data Load Batch File buttons in the configuration applications.

If your firm has Capture Analytics, you can also load Capture Analytics data along with Costpoint Analytics data using these .bat files:

• Full_CpA_with_CRM_Data_Load.bat – This process does the following:

• Updates up all Costpoint Analytics and Capture Analytics data models, based on your configuration settings

• Loads actual performance data from Costpoint for all project and employee analytics

• Loads proposal data from the Proposal Attributes file

• Loads budget data from all budget data files

• Loads opportunity data from GovWin Capture Management for Capture Analytics

• Loads organization security data from the security setup file (CostpointAnalyticsOrgSecuritySetup.xlsx) for both Costpoint Analytics and Capture Analytics

• Full_CpA_with_CRM_Data_Load_without_Datapull.bat – This process is the same as Full_CpA_with_CRM_Data_Load except that it does not load actual performance data from Costpoint or opportunity data from GovWin Capture Management.

For large Costpoint databases, using Full_CpA_Data_Load.bat to do the full data load may not be the most efficient method.

If the row count for the PSR_Final_Data table in Costpoint is more than 50 million, follow the instructions under Procedure: Load Initial Data Using a QlikView Publisher Task. Using the QlikView Publisher task to load the data can improve performance for large databases.

Who Can Load Data

Costpoint Analytics provides no internal security for the configuration applications or the data load .BAT files. Be sure that only the appropriate persons have access to the network folders in which you installed Costpoint Analytics.

Costpoint Analytics SaaS: If you have a Costpoint Analytics SaaS Subscription, Deltek Cloud Operations provides security for the configuration applications and data load files.

Scheduling the Data Load Process

If you want the data load process to run automatically on a set schedule, use the QlikView Management Console to set up the schedule.

When you install Costpoint Analytics, the install process automatically sets up a QlikView Publisher job—Load Deltek Costpoint Analytics—to run a full Costpoint Analytics data load nightly at 11:00 p.m., but you can adjust that schedule.

You can also use the Scheduled Task feature in Microsoft® Windows® to set up scheduled tasks for the .bat files that you use.

For more information on scheduling the data load process, see Schedule the Data Load Process.

Costpoint Analytics SaaS: If you have a Coatpoint Analytics SaaS Subscription, Deltek Cloud Operations runs the data load process each night. When you have completed your configuration of Costpoint Analytics, send an email to CCOps@deltek.com and ask Cloud Operations to begin the nightly data load processes. Do not make this request before you have completed the configuration. If configuration is not complete, the data load may not run successfully.  

Verifying Costpoint Analytics Data

Actual Performance Data

You can use Costpoint reports to verify actual performance data:

• Actual performance data displayed in the Revenue, Profit, Projects at Risk, and Backlog Analytics comes from the Project Status Report reporting tables in Costpoint. Use the Project Status Report in Costpoint to verify that data.

• To verify AR data, generate the A/R History report and the Accounts Receivable Aging report for specific projects. The Project Revenue Summary report is helpful for verifying DSO values.

• Actual performance data displayed in the Labor Utilization Analytics comes from the Labor History tables in Costpoint. Use the Labor Utilization report in Costpoint to verify that data.

Note, however, that the data displayed on Costpoint reports and in Costpoint Analytics depends on many configuration and reporting options. In some cases, an option in Costpoint Analytics may have no equivalent in Costpoint, or a Costpoint option may have no equivalent in Costpoint Analytics. As a result, it may not be possible, for example, to generate a Project Status Report that you can compare directly to the data displayed in Costpoint Analytics. You may need to do some manual calculations to adjust for differences in configuration and reporting options.

Budget Data

To verify budget data, use the reports provided by the budgeting tool that is the source of the budget information you loaded into Costpoint Analytics. If your budget data originated in Costpoint, you can use the Project Status Report.

You may need to do some manual calculations to adjust for differences in configuration and reporting options between your budgeting tool and Costpoint Analytics.

Inception-to-Date Revenue

Inception-to-date (ITD) revenue in Costpoint Analytics may not match ITD revenue in Costpoint if the rate type used to update prior year history in Costpoint is different from the rate type selected in the Costpoint Analytics configuration settings.

For example, a firm reports on revenue in Costpoint using target rates all year. However, it posts final revenue for the year using actual rates and updates prior year history using actual rates. As a result, revenue amounts for prior years on the Project Status report in Costpoint always reflect actual rates, whether the report is generated using actual or target rates.

In Costpoint Analytics, however, you can only display revenue based on one rate type: actual or target, but not both at once. If, in this example, the firm selects actual rates in Costpoint Analytics and the Project Status report is generated using actual rates, ITD revenue amounts in Costpoint Analytics and those on the Project Status report should match. However, if the firm selects target rates in Costpoint Analytics, the amounts may not match, regardless of the rate type used to generate the Project Status report.

Because the Profit, Backlog, Projects at Risk, and AR and DSO Analytics also display revenue or use it in calculations, this also applies to those analytics.

Beginning Backlog Budget Amounts for Existing Projects

When you load budget data (baseline budget data, forecast data, or both) during implementation, the load process does the following to set the beginning backlog budget amounts for the existing projects in Costpoint:

• The process determines the earliest fiscal period for which the budget data includes a budget contract value, a budget funded value, or a budget revenue value for any project. That fiscal period becomes the period in which the process establishes the beginning backlog budget amounts for all projects. (For performance reasons, the process does not determine the first fiscal period individually for each project.)

• The process then sets the beginning backlog budget amounts and forecast amounts for each project as described below:

The data load process uses the same method to assign beginning amounts for the contract backlog forecast, the funded backlog baseline budget, and the funded backlog forecast.

The above method only applies to projects that already exist at the time you load the initial data. When you later add a new project, the beginning backlog budget amounts are calculated as of the first fiscal period for you which you load a budget contract value, funded value, or revenue value for that project.