Using Cobra Data To Develop an EAC or Budget Changes by Owner/Manager (OBS, CA or WP)

Step 1 - Generating the Cobra Data
If this does not make sense (no pun intended) send this information to your BI Team (Report Builders) or DBA

There are several reasons that companies do not put all resource information in the schedule. I could devote this entire page to the reasons but I'd rather focus on how to best do an EAC or BAC update directly from an earned value (EV) tool. There are many projects that do, in fact, hold all of the resources in MS Project and the methods to update status and cost estimates are explained in the Resource Management section of this site. For those who don't please read on.

The goal of this page is to help those who do not load all resources into MS Project do an EAC update or Budget update (BCR/ECR). This means the only place all the resources would reside is in the EV tool. This tutorial is only devoted to the EV tool Deltek Cobra. We could set it up for another tool using the same methods but most government contracts utilize Cobra, or are in the process of using Cobra once MPM is retired.

Exporting Data From Deltek Cobra

Let's begin with describing the columns we are going to export, then import into EAC/BAC workbooks with an option to combine schedule and earned value data.

The columns required are below

  • Worksheet Grouping (Cobra Column MANAGER/CAM/OBS [WP or CA] in most cases)
  • CA1
  • CA2
  • CA3
  • Work Package
  • Work Package Description
  • Resource
  • Resource Description
  • Direct Element Of Cost (Cobra Result)
  • Resource Date (Cobra DF_DATE moved to closest Period End date > DF_DATE)
  • Direct Value (Hours, Dollars, Etc...)
  • Earned Value Technique
  • Status Date

SQL Statement - Oracle and SQL Server

We can convert a Cobra Time-phased report into a version of the columns listed above. Future versions might even have this option. However, nothing can beat the flexibility of writing your own script to pull the data using Cobra's SQL Command Utility. If you're not comfortable with using Cobra's SQL Command Utility give these statements to your report writing team (BI - Business Intelligence Team) and it will save them hours of development time because the foundation to build the objects they need to create the report is below. Your BI team could then provide you with hundreds of options to customize the report.

The first thing you need to understand is that every implementation can be different. Company A's direct elements is not going to match Company B, in most cases. You'd be surprised how similar they are, however. Direct elements are elements of cost that are the foundation for rate buildup. In our example below they are Hours, ODC, MATERIAL, and SUBCONT. They are important in this process because the people who provide your budget/estimate think in these terms. Then the total value is calculated from there. (For example, a Test Engineer knows he/she will need 100 hours and 50K of test equipment to complete a task. The tool should inform them of the cost based on this information alone)

I'm going to use Oracle SQL to demonstrate but I've included the SQL Server SQL below. The same general areas are changed based on your implementation. The only difference between Oracle and SQL Server is the syntax. However, the syntax is not that different where changes are needed. This is explained below.

Let's start with the Oracle SQL statement that pulls one program. Samples are then included for pulling multiple programs and master programs for Oracle and SQL Server in the sections titled SQL for EAC/BAC below. But first, come back to this tab after you click this link to the Oracle SQL to display it in a new tab. The areas we're talking about are in a larger font.

Below is the first section that should be changed. There are two CASE sections that define "res" and "direct". Sorry for the confusion but "res" refers to the direct element of cost or result (mentioned above). Change your WHEN section to apply to the direct elements you need. You may need rename, remove or add within these WHEN lines depending on the direct elements your Cobra instance has.

CASE WHEN Hours <> 0 THEN 'Hours' WHEN ODC <> 0 THEN 'ODC' WHEN MATERIAL <> 0 THEN 'MATERIAL' WHEN SUBCONT <> 0 THEN 'SUBCONT' END AS "res", CASE WHEN Hours <> 0 THEN Hours WHEN ODC <> 0 THEN ODC WHEN MATERIAL <> 0 THEN MATERIAL WHEN SUBCONT <> 0 THEN SUBCONT END AS "direct",

The only other line that will change is the one that adds all your direct elements to make sure they're not equal to 0. You could do without this line but it could increase processing time. You will want to modify this in the same way you modify the CASE statements above.

AND Hours + ODC + MATERIAL + SUBCONT <> 0

Let's change this for another a company with the following scenario. Hours are named HRS, SUBCONT is SUB but ODC and MATERIAL are the same. However, there's another direct result named IWO. Here's how this would change those lines.

CASE WHEN HRS <> 0 THEN 'HRS' WHEN ODC <> 0 THEN 'ODC' WHEN MATERIAL <> 0 THEN 'MATERIAL' WHEN SUB <> 0 THEN 'SUB' WHEN IWO <> 0 THEN 'IWO' END AS "res", CASE WHEN HRS <> 0 THEN HRS WHEN ODC <> 0 THEN ODC WHEN MATERIAL <> 0 THEN MATERIAL WHEN SUB <> 0 THEN SUB WHEN IWO <> 0 THEN IWO END AS "direct",

AND HRS + ODC + MATERIAL + SUB + IWO <> 0

Many companies only have DIRECT and HOURS. You should change the CASE like so and the not equals part is below that.

CASE WHEN HOURS <> 0 THEN 'HOURS' WHEN DIRECT <> 0 AND HOURS = 0 THEN 'DIRECT' END AS "res", CASE WHEN HOURS <> 0 THEN HOURS WHEN DIRECT <> 0 AND HOURS = 0 THEN DIRECT END AS "direct",

AND HOURS AND DIRECT <> 0

Please note, if you're using SQL Server AS "res" and AS "direct" above should be just AS res and AS direct without the double quotes, like below.

CASE WHEN HRS <> 0 THEN 'HRS' WHEN ODC <> 0 THEN 'ODC' WHEN MATERIAL <> 0 THEN 'MATERIAL' WHEN SUB <> 0 THEN 'SUB' WHEN IWO <> 0 THEN 'IWO' END AS res, CASE WHEN HRS <> 0 THEN HRS WHEN ODC <> 0 THEN ODC WHEN MATERIAL <> 0 THEN MATERIAL WHEN SUB <> 0 THEN SUB WHEN IWO <> 0 THEN IWO END AS direct,

Options

There are several options to choose here. This one is probably the most important. Changing the line
AND cost = 'EAC'
to
AND cost = 'BCWS'
switches the report from an EAC report to a Budget At Complete BAC report.

The very first line starts with
SELECT CONCAT(CONCAT(program, '-'), "CAManager") AS "eacgroup"
 This assumes you want to use the Control Account manager for each program as your grouping. There are many options here as well but some of the popular one's are:
SELECT CONCAT(CONCAT(program, '-'), "WPManager") AS "eacgroup"
 which groups by program-work package manager
OR
SELECT ca2 AS "eacgroup"
 which groups by OBS if ca2 is your OBS.
This is one of the more important options because it determines how many Excel Workbooks will be created during the Create ECR/BCR process

Currently, there's a line that reads
AND df_date > TO_DATE('01/01/2019', 'mm/dd/yyyy')
This can be taken out but has been included for those with enormous amounts of data. You might not want to export all dates and that line helps you isolate which dates are exported. It may be required to isolate data in Cobra's SQL Command Utility. Although extremely generous, there is a cap on the amount of data it can handle. Your best option for extremely large programs is to get your BI team to build you an export using this SQL method, or a better more efficient one

AND cecode NOT IN ('0')
 This can be used to exclude certain resources. You may not want certain apportioned resources to show up in the task owners report because they are system controlled. The task owner might assume his or her inputs affect the total when they will be automatically calculated for them in Cobra.

AND PMT NOT IN ('J')
 Again, you may not want isolate certain types of work packages (especially apportioned). This line allow you to do that. Some would put ('J', 'M') to isolate both types of apportioned. In some other cases we've seen, they do all their discrete labor planning in the schedule but want an option to just do Material and LOE packages. In this case it could be
AND PMT IN ('C', 'A')
 or something similar like Units. Let you imagination run wild because there are hundreds if not thousands of options.

With very little effort you can add another way to get the data you're looking for. They are usually added in the SELECT statement aliased as DirectTP (I use IN and NOT IN a lot for demo/flexibility, you can make it equals or not equals if you'd like). Here are some popular ones:
AND cawp.flag IN ('O', ' ') <== That's the letter O, not 0
AND cawp.ssd < ADD_MONTHS(SYSDATE, 3) <== Perhaps 4 months or a year, maybe even add 90 days or add to the program status date

This just helps prove there's thousands of possibilities. Currently, the SQL statement key's off five tables, TPHASE, CAWP, PROGRAM, FISCDETL, CALCDESC. Any column off of any one of those tables can be used plus the distinct values within those columns can isolate data. Like we've mentioned several times on this site, every project has a different way to do it. We can help you and your unique needs in a variety of ways.

SQL for EAC/BAC (Oracle)

Master and Single Programs are easier to ensure the Calendar is the same and the Resource file is the same. If you chose to use the Multi-program version there is more risk they don't share these two items so results might not be as expected. The general rule is that if you want to pull multiple programs they should share a common resource pool and calendar. Master programs and stand-alone programs inherently do this. Although a Master can be manipulated to not share these, it really should if you plan on running reports from the Master. (I know some clients only use master programs to load actuals. If that's you, you probably wouldn't have a need to do an EAC/BAC change at the Master level anyways)

SQL for EAC/BAC (SQL Server)

Master and Single Programs are easier to ensure the Calendar is the same and the Resource file is the same. If you chose to use the Multi-program version there is more risk they don't share these two items so results might not be as expected. The general rule is that if you want to pull multiple programs they should share a common resource pool and calendar. Master programs and stand-alone programs inherently do this. Although a Master can be manipulated to not share these, it really should if you plan on running reports from the Master. (I know some clients only use master programs to load actuals. If that's you, you probably wouldn't have a need to do an EAC/BAC change at the Master level anyways)

Now you're ready to Create ECR/BCRs in the Excel Add-in

Visit Using Cobra Data To Change BAC or EAC (Step 2 - Generating the Workbooks)

Don't let the above information scare you. When the above is set up, it takes seconds to get the data you need. There's just a lot of information because clients want/need options.

Below is an old method I used to get the same data above but run into issues on the SQL Server side so changed my approach and it shortened the time and turned out to be much better. I'm keeping it here in case I come across another special case where it might be useful again.

Old SQL Method

There will be a UNION ALL for each direct element in addition to the first one you need to export. Only direct elements are exported because the people who are going to update the final form think in base hours and dollars and not the "burdens/overheads". Once they update the basis for the total cost the tools will give the user the total cost with "burdens/overheads" for them.

First, you will need to replace COBRAPROGRAMNAME with your Cobra Program's name. Next, if you're not working with EAC you will need to change cost = 'EAC' with cost = 'BCWS' for BAC changes. Finally, the examples linked below have UNION ALL for ODC, MATERIAL, SUBCONT. (Hours is the first query therefore no UNION ALL is necessary) You will need to replace each with your particular Cobra database direct elements. If you have less you will need to remove one or more of the UNION ALL(s). If you have more you will need to add UNION ALL(s). There are 4 instances of each direct element that will need to be replaced in each UNION ALL. This will be apparent in the links below The 4 instances to replace with your direct elements in each section are in a larger font.

Oracle SQL Direct Elements Time-phased 2D
SQL Server Direct Elements Time-phased 2D

To help you understand what the UNION ALL is doing I've put a line through the words that are not required to only export one direct element. It would be uncommon to have just one direct element but if you did you would delete the strike-through text. Each subsequent UNION ALL adds another direct element to the file and you would change the 4 instances mentioned above with the direct element you want to export.

One Direct Element Oracle SQL Direct Elements Time-phased 2D
One Direct Element SQL Server Direct Elements Time-phased 2D

The links below highlight an example where I add an additional direct element. There is no limit to how many elements can be added but they should be placed in a similar position anytime you add one. The added direct element in this example is IWO (Inter-company Work Order).

Add IWO Direct Element to Oracle SQL Direct Elements Time-phased 2D
Add IWO Direct Element to SQL Server Direct Elements Time-phased 2D