none
Project Server 2010 Cube Question RRS feed

  • Question

  • I need to develop custom project server 2010 cube using SSRS BI Studio.  I am wanting to combine data from the Timesheet cube and data from the Project Timephase cube.   I'm fairly new to anaylsis services cube design structure.  Creating the cubes from the PWA screen is pretty intutive but obviously you are not able to combine cube data at that level.  

    I have tried creating my own custom cube based on the timesheet cube using the same dimensions/measures.  Unfortunately I'm not really understanding the particular tables that need to be pulled to create that cube.

    Would someone have any suggestions on how to design this structure.  Unfortunately I have not found any documentation specifice to cube design for Project. 

    Thanks for any information provided.  Julie

    Tuesday, March 13, 2012 3:19 AM

All replies

  • Hi Julie,

    If I understand correctly, You want to create a report which will need to shwo the data for project Timesheets.

    YOu may want to check the SSRS report from project server 2007 report pack 2. There is a report "Timesheet compliance" which works well on PS2010 with minor changes in SQL.

    http://archive.msdn.microsoft.com/EPMReportPack

    Once you downlaod the report pack, You can open the solution in BIDS & see the EPM Governance Reports, You will get the SQL query from Timesheet Compliance.rdl.


    here is the sample query:

    Declare @sqlquery1 nvarchar(max)
    Declare @ReportingDB nvarchar(100)
    -- Set DB variable to name of EPM Reporting database in your environment.
    Set @ReportingDB = N'ProjectServer_Reporting'
    /* These declarations are commented out in reporting services because it will be
    	the reporting services parameters that bind directly to the parameters in the
    	query below. But they are included here for your convenience because
    	when you copy out the query to run directly against SQL Server, it is handy
    	to have the parameters declarations here to help you test values.
    */
    /*
    Declare @RP_TSPeriodUIDs nvarchar(max)
    declare @RP_RBS nvarchar(255)
    declare @RP_Teams nvarchar (255)
    -- Set Timesheet Period variable to Timesheet Period UID's that are to be
    -- examined.
    set @RP_TSPeriodUIDs = N'31F02FF3-197E-453B-A093-599E7A6FE585''
    		,''A671C3E7-BAD0-4F46-A398-5BF7EC12234B''
    		,''FBAFA6BF-9A98-4632-8123-BF301209B2DB'
    -- Set value for RBS filter
    set @RP_RBS = N'Litware.CIO.IT PMO'
    --Set value for Team filter
    set @RP_Teams = N'Teams.IT Teams.PMO Team'
    */
    set @sqlquery1 = N'use [' + @ReportingDB + N']
    if object_id(N''tempdb..#ts_periods_specified'',''U'') is not null drop table #ts_periods_specified
    select tp.PeriodName + '' ('' + CONVERT(varchar(10),tp.StartDate,1) +  '' - '' 
    	+ CONVERT(varchar(10),tp.EndDate,1) + '')'' as [Timesheet Period Name],
    	tp.PeriodUID as [Timesheet Period UID],
    	tp.StartDate as [Timesheet Period Start Date],
    	tp.EndDate as [Timesheet Period End Date]
    	into #ts_periods_specified
    	from MSP_TimesheetPeriod tp 
    	where cast(tp.PeriodUID as varchar(100))  in (''' + @RP_TSPeriodUIDs + N''')'
    set @sqlquery1 = @sqlquery1 + N'
    if object_id(N''tempdb..#ts_resources'',''U'') is not null drop table #ts_resources
    select
    	r.ResourceName AS [Resource Name],
    	r.ResourceUID as [Resource UID],
    	isnull(r.RBS,''<No RBS Information Available>'') as [RBS],
    	isnull(r.[Team Name],''<No Team Information Available>'') as [Team Name],
    	isnull(rm.ResourceName,''<No Timesheet ManagerInformation Available>'') AS [Timesheet Manager]
    	into #ts_resources
    	from
        dbo.MSP_EpmResource_UserView r 
    	LEFT OUTER JOIN
        dbo.MSP_EpmResource_UserView rm 
    	ON r.ResourceTimesheetManagerUID = rm.ResourceUID 
    WHERE
         (r.ResourceIsActive = 1) AND
         (r.ResourceType = 2) AND
         (r.ResourceIsGeneric = 0)
    		
    	and r.RBS in (''' + @RP_RBS + N''')'
    set @sqlquery1 = @sqlquery1 + N'
    	and r.[Team Name] in (''' + @RP_Teams +  N''')'
    set @sqlquery1 = @sqlquery1 + N'
    if object_id(N''tempdb..#ts_timesheets'',''U'') is not null 
    	drop table #ts_timesheets
    select
    	ts.TimesheetUID as [Timesheet UID],
    	ts.PeriodUID as [Timesheet Period UID],
    	ts.TimesheetName as [Timesheet Name],
    	tsr.ResourceUID as [Timesheet Resource UID],
    	ruv.ResourceName as [Timesheet Owner Resource Name],
    	ruv.RBS, 
    	ruv.[Team Name],
        /*
    		Possible values for Timesheet Status:
    			0	In Progress
    			1	Submitted
    			2	Acceptable
    			3	Approved
    			4	Rejected
         */
        case when (ts.TimesheetStatusID = 4) then 1 end as [Rejected],
        case when (ts.TimesheetStatusID in(0,1,2)) then 1 end as [In Progress],
        case when (ts.TimesheetStatusID  = 3) then 1 end as [Approved],
    	sum( ActualWorkBillable + ActualWorkNonBillable
    		+ ActualOvertimeWorkBillable + ActualOvertimeWorkNonBillable) 
    		as [Timesheet Actual]
    	into #ts_timesheets
    	from
    	MSP_Timesheet ts
    	inner join
    	MSP_TimesheetLine tsl
    	on ts.TimesheetUID = tsl.TimesheetUID
    	inner join
    	MSP_TimesheetActual tsa
    	on tsa.TimesheetLineUID = tsl.TimesheetLineUID
    	left outer join
    	MSP_TimesheetResource tsr
    	on tsr.ResourceNameUID = ts.OwnerResourceNameUID
    	left outer join
    	MSP_EpmResource_UserView ruv
    	on ruv.ResourceUID = tsr.ResourceUID
    	where cast(ts.PeriodUID as varchar(100))  
    		in (''' + @RP_TSPeriodUIDs + N''')
    	and ruv.RBS in (''' + @RP_RBS + N''')
    	and ruv.[Team Name] in (''' + @RP_Teams +  N''')
    	group by
    	ts.TimesheetUID,
    	ts.PeriodUID,
    	ts.TimesheetName,
    	tsr.ResourceName,
        tsr.ResourceUID,
        ts.TimesheetStatusID,
    	ruv.ResourceName,
    	ruv.RBS, 
    	ruv.[Team Name]
    select 
    res.[Resource Name],
    res.[RBS],
    res.[Team Name],
    res.[Timesheet Manager],
    tsp.[Timesheet Period Name],
    tsp.[Timesheet Period Start Date],
    tsp.[Timesheet Period End Date],
    ts.[Timesheet Name],
    case when (ts.[Timesheet Name] IS NULL) then 1 end as [Not Started],
    ts.[In Progress],
    ts.[Approved],
    ts.[Rejected],
    ts.[Timesheet Actual]
    from 
    #ts_resources res
    cross join
    #ts_periods_specified tsp
    left outer join
    #ts_timesheets ts
    on 
    res.[Resource UID] = ts.[Timesheet Resource UID]
    and 
    tsp.[Timesheet Period UID] = ts.[Timesheet Period UID]
    order by res.[Resource Name],tsp.[Timesheet Period Name]
    '
    exec (@sqlquery1)

    Hope that helps.


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    Tuesday, March 13, 2012 5:22 AM
  • Amit,

    So the report pack for Project 2007 works with Project 2010 and SQL 2008R2?  Is there a specific report pack for Project 2010/SQL 2008R2?  Thanks, Julie

    Tuesday, March 13, 2012 5:11 PM
  • Julie,

    As I mentioned that Some of the reports can be used with PS2010 data. As far as I know that there is no report pack for PS2010 released yet. You just need to download the report pack & changes the data source & run the reports. based on the configuration of system, You may need to do few changes in report.

    Hope that helps.


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    Wednesday, March 14, 2012 5:17 AM
  • Amit,

    If I use the Project Timephase cube how would I go about adding the Actual Work Billable field.  Is it possilbe to do this through an MDX query in PWA when creating the configuration for the cube build?  Thanks, Julie

    Thursday, March 15, 2012 1:09 AM
  • Hi Julsannie

    trought MDX add new measure from one cube to another is tricky. in this scenario i advise to crete your own OLAP database and make customization in this database. In BI studio you can easily add new measures from another cube.

    tomas

     

    Tuesday, March 27, 2012 4:47 PM