none
How to get TimescaledUnitAvailability from Reporting DB for a given resource/date RRS feed

  • Question

  • Hi,

    I would like to get TimescaledUnitAvailability for a given Resource/date from the Reporting DB.

    I know how to receive it by using TimeScaleData from VBA but how to get it without the Client?

    Thanks,
    Stefan


    • Edited by stefan-pmo Friday, August 3, 2012 12:21 PM
    Friday, August 3, 2012 12:20 PM

Answers

  • Hi there,

    You can use the MSP_EpmResourceByDay_UserView and MSP_EpmAssignmentByDay views in the reporting database. To get availability you could subtract the assigment work from the resource capacity.

    Thanks

    Paul


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

    • Marked as answer by stefan-pmo Tuesday, November 20, 2012 9:39 AM
    Friday, August 3, 2012 2:08 PM
    Moderator

All replies

  • Hi there,

    You can use the MSP_EpmResourceByDay_UserView and MSP_EpmAssignmentByDay views in the reporting database. To get availability you could subtract the assigment work from the resource capacity.

    Thanks

    Paul


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

    • Marked as answer by stefan-pmo Tuesday, November 20, 2012 9:39 AM
    Friday, August 3, 2012 2:08 PM
    Moderator
  • There are three By Day tables available, Resource, Task and Assignment. (MSP_ResourceByDay, MSP_TaskByDay, MSP_AssignmentByDay, respectively).

    Two cautions are advised with the use of these tables.

    First, limit the timescale that you are querying as you can easily retrieve thousands of records. I usually limit my query to a six month floating window. You can use the particular SQL statements from this post from Pinal Dave to set your date window. http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/ 

    Second, if you intend to join the data to other tables/views, aggregate and perform any WHERE conditions on the ByDay records first, then do the join. Otherwise, the I/O related to joining to all of those ByDay records can kill your Query performance. I presented this technique at the last Project Conference in the BI Hands-On Lab.

    Hope this helps!

    Treb Gatte @tgatte

    Friday, August 3, 2012 4:56 PM
    Moderator
  • Thank you Paul, that I was looking for.

    Stefan.

    Tuesday, November 20, 2012 9:40 AM