none
Extracting data from Project Server 2010 RRS feed

  • Question

  • Hi folks,

    I'd like to ask for your expertise.  I have been givin the task of extracting data from project server 2010 into a list, perhaps within excel from project server.  This data will then be updated into a bespoke SQL server database.

    The file would include such fields as:
    Project Name
    Project Description
    Benifit Type
    Risk Name
    Risk Category
    etc...

    However I thought it would be as simple as querying the data from the Project Server SQL server database.  I'm now not sure if this is the case as I've been told the PS database is unquerable as the schema is complex.  I'm unsure of the Project server layout and was hoping someone could give me a little insight into obtaining this type of data.

    Can I retrieve this data from a SQL database?
    Do I have to extract it via Sharepoint using XML? (so I've been told)

    Many thanks


    • Edited by Cidr Tuesday, October 9, 2012 11:02 AM
    Tuesday, October 9, 2012 10:58 AM

All replies

  • Hi Cidr

    Project Server data can be queried but for readonly purposes , refer to the SDK documentation you will find the complete schema of reporting database, from where you can get the values you are looking for typically within project server reporting DB within MSP_EPMProject_Userview you will get most of the project level values

    Let us know if this helps


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com

    Tuesday, October 9, 2012 3:33 PM
    Moderator
  • Hi Cidr,

    Following on from Sunil's sound advice, you will also find all of the default Project site (SharePoint) data in the reporting database. The views and tables start with MSP_WSS... Any custom fields added to the project sites will need to be accessed via the SharePoint web services / object model.

    Hope that helps

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Tuesday, October 9, 2012 7:07 PM
    Moderator
  • On Paul's & Sunil's great advice, Another option that You may use the out of box excel reports in project server 2010. 
    PWA>  Business Intelligence Center > Site Action> View All site contents> Reports under Document library > English (United States)>All Documents , You can see a report - IssuesAndRisks.

    You can also download the report locally & see the report query if needed for modification. From Excel , Data tab> Connections> Select the "project Server - Risk Data" , Click on Properties & under the definition tab, verify the command text (SQL query ).

    Hope that helps.

      

    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”. Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    Wednesday, October 10, 2012 5:35 AM
  • Hi and thanks for the responses.

    Sunil 09/10/12
    Project Server data can be queried but for read-only purposes, refer to the SDK documentation you will find the complete schema of reporting database,
    

    @Sunil, this is handy and I think I can get most of the 'Project' fields from the report database.

    For the Risk data, there is a cube built over night that will give me most of the 'Risk' fields.

    The idea is to have an SSIS package to run each night and pull the Project data to a different source.  Getting the data to the source is easy.  The trick is to get all the fields I need.

    Unfortunately the cube doesn't hold the custom fields I need (names like Risk Description etc).

    Paul 09/10/12
    Any custom fields added to the project sites will need to be accessed via the SharePoint web services / object model.
    

    @Paul, would this give me the custom fields from the lists? Any insight or known articles?  I know if I pull the data from the lists using XML I think by using a function (get splist for example) which I'm trying to find.

    Another issue I'm concerned about with this technique is that there are 500 projects so perhaps a lot of 'Risk' lists.  I'd be able to get this data with the Project as an entity and retrieving all the risk data for all projects.  As I can't get all my custom fields this way I think another way is needed.

    Any more help would be much appreciated

    Thursday, October 11, 2012 12:52 PM
  • Hi Cidr

    The risk data is also available within Reporting DB, refer to the view MSP_WSSRisk_OlapView within Project Server reporting DB, it gives you all the out of box fields but if there are other fields you have created that will not come over, if there are any other custom fields you need customization to handle it,

    let us know if this helps


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com

    Thursday, October 11, 2012 5:20 PM
    Moderator
  • Hi and thanks for help

    I'll have a look at these links Paul.  Hopefully I'll find something I can use.



    Monday, October 15, 2012 8:51 AM
  • Hi Everyone,

    I've just been reading this article

    http://msdn.microsoft.com/en-us/library/office/ee767696.aspx#pj14_CustomFieldsOlapCubes_Adding

    It explains that I can add custom fields to the project server OLAP cubes.

    Just to reiterate, I need to extract all risk data via SSIS for (500 ish) projects.  I can do this with the built-in fields but not custom fields (or so I thought)

    In terms of the article above, does this mean that I can add custom 'risk' fields to the cube that's built (or can be built) each night and extract built-risk data along with custom fields to another destination?

    Thanks


    • Edited by Cidr Wednesday, October 17, 2012 10:17 AM
    Wednesday, October 17, 2012 10:16 AM
  • Hi Cidr,

    Only Project Server custom fields can be added to the cube in the cube config page, these are the fields you see in Project Pro / PWA Project Center / resource center etc. The custom Risk list fields are SharePoint fields so can not be added to the cube using the cube configuration page, it would require custom development / custom OLAP cube to get the custom SharePoint fields into the cube.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Thursday, October 18, 2012 7:14 PM
    Moderator