Create an MS SQL Database Connection to Load Actual Costs

Use this procedure to create a connection to an MS SQL database in order to load actual costs during integration.

It is expected that managing the database tables will be handled by your company's IT personnel/Database Administrator (DBA).

To create an MS SQL database connection to load actual costs:

1. Connect to an MS SQL server.
2. In the Object Explorer pane, right-click Databases and select New Database....
3. On the New Database dialog box, enter a database name and click OK.
   In this example, the database is named DELTEK_ACTUAL.

   The new database will be listed as one of your databases in the Databases tree.

4. In the Object Explorer pane, expand the new database by clicking the + icon.
5. Right-click Tables and click New, and then click Table...
   

   The database table displays in the right pane.

   

   In this example, we are creating a simple database for actual costs using data Format 1 to load actuals (Results are specified as column headers). The columns created should use the same Data Type used throughout Cobra for that same value. For example, in the CAWP table, the CA1 (WBS) field is NVARCHAR (59, not null). The WBS field in the Actuals table should use the same data type setting.

6. Enter the following information and clear the Allow Nulls checkbox next to each entry.
   

   In this example, we are going to load 3 results. Since were are using Format 1, each result needs to be its own column (HOURS, DIRECT, and OH). The project is collecting actuals at the control account (CA) level, so we only need to identify the CA fields, resource, and results. If collecting actuals at the work package (WP) level, you need to create a column for the WP name.

7. Click Save. When prompted, enter a name for the new table.
   

   The new table, dbo.ACTUAL_COSTS, is now listed in the Database tree. If the new table is missing, right-click the database, and select Refresh.

   
8. Right-click your database, select New Query, and execute the Select statement to make sure the new actuals table is working and all the column names you created are working.
   Select * FROM [name of your database]

   

   

9. Populate the Actual Costs table with the actual cost data.
   Important: This is a very simple example. These queries will create actual costs for two control accounts only.

   
   Insert INTO dbo.ACTUAL_COSTS (PROJECT,WBS,OBS,RESOURCE,HOURS,DIRECT,OH) 
   VALUES ('Learn Cobra','1.01.01','1410','ENG','100','1000','2000'); 
   Insert INTO dbo.ACTUAL_COSTS (PROJECT,WBS,OBS,RESOURCE,HOURS,DIRECT,OH) 
   VALUES ('Learn Cobra','1.01.03','1110','ENG','200','2000','3000');

10. Right-click your database, select New Query, and execute the Select statement to make sure the table is populated.
    Select * FROM [name of your database]
    
11. Create an ODBC connection in order for Cobra to see the database holding the actual cost data.
    1. Launch the ODBC Data Source Administrator.
    2. Click the Add button.
    3. On the Create New Data Source dialog box, select SQL Server in the list of drivers for which you want to set up a data source, and click Finish.
    4. Enter a name for the data source so you can easily recognize it in Cobra. In this example, the data source name is DELTEK_ACTUAL.
    5. Select the server on which the database is located and click Next.
       
    6. Set up the authentication and click Next.
       In this example, SQL Server authentication is used.

       

    7. Select the Change the default database to checkbox and click Next, and then Finish.
       
    8. Click the Test Data Source button. When the test completes, click OK to close out the ODBC Data Source Administrator.
       
12. Log into Cobra.
13. On the Integration tab, click Actual Cost in the Import group.
14. On the Integration Configuration page, select the Create a new configuration option
15. On the File Selection page, do the following:
    1. Use the Project field to define the project where the actual costs will be imported.
    2. Select the Connection name option and click New to create a new connection.
    3. On the New Connection dialog box, enter in a name for the connection. In this example, the connection name is Actual Costs.
    4. Select the Data source option and select the data source you created using the ODBC Data Source Administrator.
    5. Enter the username and password for the actuals database and click Test.
    6. When the test completes, click OK.
       The connection should be populated in the Connection Name field. Click Next.

       Note: If the connection name is not populated, select it from the list.

       

    7. On the Database Table Selection page, click to display the Database Table Lookup dialog box.
    8. Select the table holding the actual costs, and click Next.
       
    9. Complete the pages of the Integration Wizard-Actual Cost.