Part 1. Adding your custom fields to Deltek's cubes

This is Part 1 of a 2 part series.

In this installment, I will simply list out, at a high level, the steps required to to adding custom project columns (projectcustomtabfields) to deltek's out of the box data cubes in Analysis services.

This article assumes you have installed the analysis services from the deltek vision utilities. You can contact me if you have any questions...

In addition, I always recommend copying exisiting environments that actually work so that you have a working copy to refer back to when trouble shooting during this exercise.

$COMPANYNAME$ = Your company name

1. Copy analysis folder from installdrive:\program files\deltek\analysis to
installdrive:\program files\deltek\$COMPANYNAME$analysis\

2. Rename solution and project within ETL folder within the $COMPANYNAME$analysis folder from Vision ETL.* to $COMPANYNAME$Vision ETL.*

3. Rename file dim_Projects.dtsx to dim_$COMPANYNAME$Projects.dtsx.

4. Adjust the sql statement used by dim_$COMPANYNAME$Projects.dtsx.

5. Alter the project table and the PRInternal table in your dw database and add the fields you want.


6. Open dim_$COMPANYNAME$Projects.dtsx.

7. Right click the "DFT - LoadPRInternal" within the Load Internal Tables group, and then click edit. Then click the "GET PR Data" icon and click edit.

8. You will edit the sql statement loaded from step 7 and add the appropriate custom project fields from a table, such as ProjectCustomTabFields.

9. In the Load Project Dimension group, right click the "DFT - Load Projects" icon and click edit. Then Right Click the "Get PR Internal Data" icon and click edit.

10. You will edit the sql statement loaded from step 9 and add the appropriate custom project fields from a table, such as ProjectCustomTabFields.

11. Build solution.

12. Backup current datacube database.

13. Restore it with another name, maybe using the $COMPANYNAME$ naming convention

14. Open up the Analysis services project in visual studio 2005.

15. Changed datasources to point at your databases.

Sorry for the cliffhanger folks, but we will start backup again with Part 2 in a few days.

Enjoy....