Project Programmability

This blog focuses on customizations and programming for Project Web App, Project Server, Project Professional and Project Standard. Includes User Interface (UI) customizations, Project Server Interface (PSI) and Visual Basic for Applications (VBA) Programming. It also covers Business Intelligence.
 
 

November, 2007

  • Project Programmability and Business Intelligence

    EPM 2007 Test Data Population Tool

    • 2 Comments

    Christophe just passed along this information:

    The EPM 2007 Test Data Population Tool solution starter was published on CodePlex today: http://www.codeplex.com/EPMDatapop

    MWSnap048 2007-11-26, 19_26_50.jpg

    The EPM 2007 Test Data Population Tool enables you to load large amounts of EPM data: resources, projects, tasks, assignments into a Project Server 2007 database. You can then use this data to test loads and help your organization plan for your Project Server 2007 deployment. This tool is similar to the EPM 2003 Data Population utility but contains more features plus since the sources are provided you can customize it to fit your specific needs.  It was written by EPM World Wide Center of Excellence (WW COE) to generate data needed to perform performance and scalability labs for large and complex EPM deployments.

    The EPM 2007 Test Data Population Tool is a Win Form application that leverages the Project Server Interface (PSI) and/or WinProj (Project Professional).

    This solution starter includes a 15 pages document, a setup.exe to deploy it, as well as all source code.

    A special thank you to Mike Shughrue and Michael Jordan from the EPM WW COE for creating and testing the tool extensively during scalability labs, as well as Boris Scholl from the Product Marketing Group for reviewing the code and documentation. 


    About CodePlex

    CodePlex is Microsoft's open source project hosting web site. You can use CodePlex to create new projects to share with the world, join others who have already started their own projects, or use the applications on this site and provide feedback. A word about Microsoft’s role: Microsoft does not control, review, revise, endorse or distribute the third party projects on this site. Microsoft is hosting the CodePlex site solely as a web storage site as a service to the developer community.

    Q&A

    Q: Is the tool supported?

    A: There is no support in terms of CSS/PSS. We expect the support being a CodePlex community effort. Please note that the customization code uses standard supported web service calls available out of the box in EPM2007.

    Q: Is the tool free?

    A: Yes.

    Q: Can I distribute the tool and the source code to customers and partners?

    A: Customers and Partners can use both. Please point them to CodePlex as they have to agree on the license terms

    Q: Can a partner distribute the tool and code as is?

    A: No, but he can point his customer to the website to download it, so he makes sure that the customer agrees with the license terms.

    Q: Can a customer install the customization and use it?

    A: Yes the customer can, but he/she is responsible for testing it and running it.

    Q: Can I suggest changes to it?

    A: Yes, join the CodePlex community or send us an email: epmssdev@microsoft.com

    Q: Will this tool be distributed in other ways (i.e. DVDs)?

    A: No.

    Q: What skills do I need to modify or change the tool?

    A: C#, Project Server Interface, and a good understanding of the EPM 2007 data schema.

    Q: I’m trying to modify the code and do have questions. Who do I ask?

    A: Go to the EPMDatapop discussion forum on CodePlex.

    Regards,

    Christophe Fiessinger

  • Project Programmability and Business Intelligence

    Local Custom Fields

    • 1 Comments

    Introduction

    This post contains interim documentation for tables in the Published database of a RTM-edition Project Server 2007 installation. Entities in this schema are subject to change without notice. This documentation is made available on a “best efforts” basis, Microsoft Product Support Services have not been trained in its usage and will not offer technical support for issues related to custom queries against this schema. This schema should not be updated by custom code, any updates to the data may break Project client cache code.

    This post contains two sections:

    - Schema fragments, showing the core tables required to traverse local custom field data

    - Sample queries, showing how to extract custom field and lookup table data for a specific project

    Many of the principles of local custom fields were initially developed in earlier Project Server versions and further questions may be answered by the server schema documentation supplied with those versions, for example:

    DOCS/PJDB.HTM // Primary source of custom field schema documentation

    DOCS/PJSVR.HTM // Additional enumerations

    Both of these files are supplied on the Project Server 2003 installation media.

    Joining Publishing and Reporting

    It is recommended that you implement views in the Reporting database that reference the four tables below as well as MSP_PROJECT_RESOURCES (as local resources are aggregated in the Reporting database)

    For example:

    CREATE VIEW dbo.MSP_PROJECT_RESOURCES AS
    SELECT * FROM [PUBLISHED_DB].dbo.MSP_PROJECT_RESOURCES
    

    Note that if the Reporting Database has been installed on a separate SQL Server instance that you will have to use a four part name in the views including the linked server name.

    Schema (Fragment)

    This section documents the relevant attributes and entities required to extract local custom field data for reporting purposes.

    This table holds the master list of all local custom fields in use, keyed on the Proj_UID. Obtain the MS_PROP_UID (which keys the custom field values in the next two tables) from the MD_PROP_NAME or by using the MSP_WEB_VIEW_FIELDS table (get the WFIELD_UID that corresponds to the WFIELD_NAME_OLEDB of the local custom field that you want) – we use “Magic GUIDs” to identify local custom fields (ie 000039b7-8bbe-4ceb-82c4-fa8c0b400033 is always TaskText1)

    This table contains the values (or a pointer to a lookup table value) for task local custom fields. Obtain the MD_PROP_UID of the field and then query into this table to get the various values. Note that the rules for Duration Format have not changed since those documented in the Project Server 2003 documentation.

    Depending on the type of the field (FIELD_TYPE_ENUM taken with the presence of a lookup table entry MD_LOOKUP_TABLE_UID in the previous table data is held in the appropriate *_VALUE column.

    This table contains the values (or a pointer to a lookup table value) for resource local custom fields. Obtain the MD_PROP_UID of the field and then query into this table to get the various values. Note that the rules for Duration Format have not changed since those documented in the Project Server 2003 documentation.

    Depending on the type of the field (FIELD_TYPE_ENUM taken with the presence of a lookup table entry, MD_LOOKUP_TABLE_UID in the Project Custom Fields table, data is held in the appropriate *_VALUE column.

    This table contains lookup table values pointed to by the previous two tables. Assuming either text or hierarchical selection the LT_VALUE_TEXT and LT_VALUE_FULL are most interesting. (_FULL contextualizes the selection and corresponds to the value stored in the enterprise equivalents in the Reporting database.

    Working With Project Task and Resource Custom Fields

    This section contains a series of queries to get you started. These all return tables that can be joined (use an outer join to ensure that data doesn’t get stripped) onto our Reporting USERVIEW views to obtain custom field data.

    Note 1: I’m assuming that the majority of data will be in text custom fields or outline codes, the *_custom_field_values tables contain other buckets for the other field types.

    Note 2: I haven’t qualified these query snippets with a PROJ_UID so they will return all data, you should remember to add this field for project-specific reports otherwise performance will be sub optimal.

    Note 3: I’ve packaged the resource outline code query in two ways, the second as a sample function to make usage a little bit simpler, as the function abstracts out all the joining to get the values.

    Join Table of Task_UID and a text Custom Field
    SELECT task.task_uid, tlcf.text_value 
    FROM dbo.msp_tasks AS task 
    INNER JOIN dbo.msp_task_custom_field_values AS tlcf 
    ON (task.task_uid = tlcf.task_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (tlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'CustomFieldName'
    Join Table of Res_UID and a text Custom Field
    SELECT res.res_uid, rlcf.text_value 
    FROM dbo.msp_project_resources AS res 
    INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
    ON (res.res_uid = rlcf.res_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'RescourceCustomFieldName'
    Join Table of Task_UID and an Outline Code
    SELECT task.task_uid, tloclt.lt_value_full, tloclt.lt_value_text 
    FROM dbo.msp_tasks AS task 
    INNER JOIN dbo.msp_task_custom_field_values AS tlcf 
    ON (task.task_uid = tlcf.task_uid) 
    INNER JOIN dbo.msp_project_lookup_table_structures AS tloclt 
    ON (tlcf.code_value = tloclt.lt_struct_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (tlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'TaskOutlineCodeName'
    Join Table of Res_UID and an Outline Code
    SELECT res.res_uid, rloclt.lt_value_full, rloclt.lt_value_text 
    FROM dbo.msp_project_resources AS res 
    INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
    ON (res.res_uid = rlcf.res_uid) 
    INNER JOIN dbo.msp_project_lookup_table_structures AS rloclt 
    ON (rlcf.code_value = rloclt.lt_struct_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'RescourceOutlineCodeName'
    Sample Function for Ease of Use
    CREATE FUNCTION dbo.Custom_ResOC (@OutlineCodename NVARCHAR(50)) 
    RETURNS TABLE AS 
    RETURN (SELECT res.res_uid, rloclt.lt_value_full, rloclt.lt_value_text 
    FROM dbo.msp_project_resources AS res 
    INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
    ON (res.res_uid = rlcf.res_uid) 
    INNER JOIN dbo.msp_project_lookup_table_structures AS rloclt 
    ON (rlcf.code_value = rloclt.lt_struct_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = @OutLineCodename) 
    GO 
    -- Can be used in a join clause
    SELECT * FROM dbo.Custom_ResOC(N'ResourceOutlineCodeName') 
    GO
    

  • Project Programmability and Business Intelligence

    Tied Mode between Timesheet & My Tasks

    • 1 Comments

    We have published today an EPM 2007 solution starter that introduces Tied Mode between Timesheet & My Tasks on CodePlex: http://www.codeplex.com/EPMTSST

    MWSnap024 2007-11-15, 16_16_05.jpg

    Project Server 2007 provides for full separation between the project-focused My Tasks “Statusing” functionality and the time-period-focused Timesheet functionality, together with the ability to manually import data from one set of functionality to the other to keep them synchronized.

    This solution attempts to address this frustration by treating the timesheet system as the master system and attempting to synchronize the data into the My Tasks functionality (including task approvals) whenever a timesheet is saved (similar to Project Server 2003 integrated (“Tied”) timesheet and statusing functionality).

    This solution starter includes a 18 pages document as well as source code and a compiled DLL ready to deploy and test.

    A special thank you to Julie Ripoteau and Olivier Laymand from MCS France for testing the code extensively, as well as Patrick Conlan and Chris Boyd from the Product Group for reviewing the code and documentation. 

Page 1 of 1 (3 items)
Recent Posts