none
Best Practices for BI with Master Projects RRS feed

  • Question

  • This is not really a specific question; I'd love to hear thoughts on how to handle reporting for master projects. For example, By default, master projects roll up things like costs, but not tasks. So, if you build a report for all projects that aggregates values, the costs would be incorrect, as they would have aggregated the total from the master as well as the subprojects. You could filter out sub-projects, but that would filter out the tasks as well... Is it not a common request to show costs and task specific info for master projects?

    Also, what about the cube? How are power users who are building excel reports with cube data supposed to interact with master projects? msp_epmproject_olapview does have the parentprojectuid field, but how is a user supposed to interact with that from excel? (Is that clear? In SQL you can easily set up a join, but how would the user set up an excel report using cube data show the relationship between the master and child projects?)

    edit: let me see if I can be a bit more specific with this question. It seems that to get basic data from project server is a power user level skill using excel and the cube or a basic SSRS report. It seems that as soon as one user creates a master project, doing basic BI becomes a developer task, as excel won't be able to show data for master / sub-projects, and the queries involved for SSRS become quite a bit more complicated. Agree? Disagree? I am missing something about master projects?

    Thanks!

     

     

     


    Mike G.
    Wednesday, July 6, 2011 8:31 PM

Answers

  • see, what you can do is write the query which can fetch this data and store this excel as template in the template library in the PS 2010 >> BI section that way people can access the excel and once they open the excel they can get that data which directly comes from the reporting database...

     


    Thanks, Parth
    • Marked as answer by m g Thursday, July 28, 2011 2:24 PM
    Thursday, July 28, 2011 12:34 PM

All replies

  • Hello Mike,

    There are various ways to handle the reporting of the Master & Subprojects. There may be pros & cons of each way. As you mentioned that  You could filter out sub-projects, but that would filter out the tasks which you will not see. Yes, It is a common request to view all data related to a program (Master project).

    Check the Ben's article:

    http://www.applepark.co.uk/master-projects/

    It is a complex to write the SQL query for master & Subprojects data but in SSRS you can write a subreport which might point to your subproject. I implemened Program Reports in SSRS which lets say shows the Master Projects data (calculated based on subprojects data) & when you click on the Subprojects, You will get the data of Subprojects. I agree that SSRS might be a complicated but doable for a developer as compared with OLAP Reports which is just drag & drop.
    I would like to use the SSRS Reports instead of OLAP as SSRS Report will be nearly a real time data compared to OLAP's data.

    Does that help?


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82
    Monday, July 11, 2011 10:04 AM
  • Thanks for your thoughts on this. You mentioned using SSRS instead of OLAP because of real time issues. Is it possible to work with master projects / subprojects from excel? (Without code: drag and drop for the user)?

    Thanks,


    Mike G.
    Monday, July 11, 2011 1:28 PM
  • I don't think so you need to write the query in the excel also you retrieve the data and show the same in the excel.

    please let me know in case you required the help in writing the Query 


    Thanks, Parth
    • Proposed as answer by Parth Rawal Thursday, July 28, 2011 5:22 AM
    • Unproposed as answer by m g Thursday, July 28, 2011 12:25 PM
    Thursday, July 28, 2011 5:22 AM
  • Thanks for the reply. Could you tell more what you mean by "write the query"? Do you mean a custom MDX  statement? Or a sql query to get a table? If the latter, how would you then make that available to other users in Excel Services?

     

    Mike


    Mike G.
    Thursday, July 28, 2011 12:27 PM
  • see, what you can do is write the query which can fetch this data and store this excel as template in the template library in the PS 2010 >> BI section that way people can access the excel and once they open the excel they can get that data which directly comes from the reporting database...

     


    Thanks, Parth
    • Marked as answer by m g Thursday, July 28, 2011 2:24 PM
    Thursday, July 28, 2011 12:34 PM
  • Thanks for that reminder. Create a new ODC, publish it, and train the users to recognize that existing ODCs / templates will not handle master projects "correctly", and to use the custom ODC for Master Projects. This still increases the workload on IT, as they will have to keep the ODC(s) for master projects updated with desired fields, as necessary. But that's probably the best we can do for now.

     

    Thanks,

     


    Mike G.
    Thursday, July 28, 2011 2:23 PM