Wednesday, January 11, 2006

Simplification and or automation of the EPM RBS


Simplification and or automation of a Microsoft Enterprise Project Management RBS from other applications such as PeopleSoft can be very complex. My suggestions and opinions are below. Please feel free to comment as these are my thoughts and opinions not a rule or an explicit guide.

If you belong to a large company then the following will generally apply:
There is a need to simplify the RBS structure. How to simplify the structure is the real question.

My first suggestion is to handle this manually, create a simple structure and have an administrator keep the RBS in line with appropriate values. If this is not an option however… See Below.

If your company has a structure like this:

Company
Division
Sub Division
Department
Sub Department
Group
Sub Group

Or

Company
Location/Country
State/City/Loc ID
Division
Sub Division
Department
Sub Department
Group
Sub Group

You will need to simplify the RBS or it will most likely be useless. My suggestion is to stop at level 4 or 5 if at all possible. If you go beyond 4 or 5 levels within the RBS then many issues may arise. This is due to a tremendous amount of changes in larger companies that can effect the RBS structure.

To determine how to set up you company RBS you will need to understand how the RBS effects security within EPM. Microsoft has a guide which explains this information in detail.

Reference:
EPM Application Configuration Guide
(Page 121 to Page 142)

Using the top RBS example above the RBS in the enterprise global would look like this.
(Company.Division.Sub Division.Department.Sub Department.Group.Sub Group)

In your company however, you may be able to stop after department. You will still want to get all the resources in the correct department though. To accomplish this you could take a feed from PeopleSoft bring it into SQL and create a function that sets the level. Then use an automation technique to update your resources daily from the Peoplesoft feed.

Below is an example of a SQL function that create a RBS based on a Poition_ID and Level of the resource. (Ex: Mike a level 3 resource reports to John a level 2 resource).

Basically the code below would provide an RBS with the values below:
(Company.Division.Sub Division.Department.Mgr.Resources) at the lowest possible level. All people with a lower RBS then would role up to this RBS.

The idea here is that you capture all the RBS information up to the Department. Then decide if the resource is a (manager) a (resource) or a (resource under a manager).
You use that information to set the specific value.
An example would be a level 5 resouce Mike Adams who reports to a level 4 resource Vicky White:

Vicky White RBS = XYZ Company.HR.HR Support.Mgr
Mike Adams RBS = XYZ Company.HR.HR Support.Mgr.Resource

Again, this is provided that the company only cares about defining the RBS down to a specified level, in this case the department level.


-- Code Begins
CREATE FUNCTION dbo.fn_RBS()

RETURNS @retCodes TABLE

(Position_Nbr varchar(8),
RES_NAME varchar(255),
RBS varchar(255),
DESCR varchar(255),
RES_RBS varchar(255),
DETAIL varchar(255),
LEVEL int,
SORTORDER varchar(255))

AS
BEGIN

DECLARE @tblCode TABLE -- table variable to hold accumulated results

(Position_Nbr varchar(8),
RES_NAME varchar(255),
RBS varchar(255),
DESCR varchar(255),
RES_RBS varchar(255),
DETAIL varchar(255),
LEVEL int,
SORTORDER varchar(255),
RowID [uniqueidentifier])

INSERT @tblCode
SELECT distinct
H.ID as Position_Nbr,
p.Res_Name AS RES_NAME,
RBS =

case level
-- Note1- dbo.fn_Scrub(PS_DEPTID_Descr) is a scrub function created to abbreviate -- the actual Peoplesoft names as the as too long.

when 0 then 'Division'
when 1 then isnull(dbo.fn_Scrub(PS_DEPTID_Descr),'EMPTY')
when 2 then isnull(dbo.fn_Scrub(PS_DEPTID_Descr),'EMPTY')
when 3 then isnull(dbo.fn_Scrub(PS_DEPTID_Descr),'EMPTY')
when 4 then isnull(dbo.fn_Scrub(PS_DEPTID_Descr),'EMPTY')
else isnull(dbo.fn_Scrub(PS_DEPTID_Descr),'EMPTY')
end,

DESCR =

case level
when 0 then 'EBS'
when 1 then dbo.fn_Scrub(PS_DEPTID_Descr)
when 2 then dbo.fn_Scrub(PS_DEPTID_Descr)
when 3 then dbo.fn_Scrub(PS_DEPTID_Descr)
when 4 then dbo.fn_Scrub(PS_DEPTID_Descr)
else 'Resources'
end,

NULL AS RES_RBS,
DETAIL,
LEVEL,
SortOrder,
rowid

-- fn_ Hierarchy Returns a table of hierarchy records above or below a starting position

FROM fn_Hierarchy('dn',(A Starting Poition ID)) H, PSToday P
WHERE h.id = p.position_nbr and h.parentid = p.reports_to_position

and h.id <> [Top Position ID]

ORDER BY sortorder

-- run thru the table with a cursor, updating the RES_RBS

BEGIN

DECLARE
@RES_NAME varchar(255),
@RBS varchar(255),
@DESCR varchar(255),
@RES_RBS varchar(255),
@DETAIL varchar(2),
@LEVEL int,
@SORTORDER varchar(255),
@RowID [uniqueidentifier]

DECLARE
@RBS_0 varchar(255),
@RBS_1 varchar(255),
@RBS_2 varchar(255),
@RBS_3 varchar(255),
@RBS_4 varchar(255),
@RBS_5 varchar(255),
@RBS_6 varchar(255),

@SORTCODE varchar(150)


DECLARE Select_RBS CURSOR

FOR SELECT RES_NAME, RBS, [LEVEL], DETAIL, RowId
FROM @tblCode ORDER by sortorder


OPEN Select_RBS

FETCH NEXT FROM Select_RBS INTO @RES_NAME, @RBS, @LEVEL, @DETAIL, @RowId

WHILE @@FETCH_STATUS = 0

BEGIN
SET @rbs = dbo.fn_SCRUB(@rbs)
IF @LEVEL = 0 begin SET @RBS_0 = 'COMPANY.' + @RBS
SET @RES_RBS = 'COMPANY.' +@RBS
end

IF @LEVEL = 1 and @DETAIL = 'Y'
begin SET @RBS_1 = @RBS
SET @RES_RBS = @RBS_0 + '.' + @rbs -- + '.Mgr'
end

IF @LEVEL = 1 and @DETAIL <> 'Y'
begin SET @RBS_1 = @RBS
SET @RES_RBS = @RBS_0 + '.' + @rbs + '.Mgr'
end

IF @LEVEL = 2 and @DETAIL = 'Y'
begin SET @RBS_2 = @RBS
SET @RES_RBS = @RBS_0 + '.' + @RBS_1 + '.Mgr'
end

IF @LEVEL = 3 and @DETAIL = 'Y' -- Treat level 3 with resources as level2
begin SET @RBS_2 = @RBS
SET @RES_RBS = @RBS_0 + '.' + @RBS_1 + '.Mgr'
end

IF @LEVEL = 2 and @DETAIL <> 'Y'
begin SET @RBS_2 = @RBS
SET @RES_RBS = @RBS_0 + '.' + @RBS_1 + '.Mgr'
end

IF @LEVEL > 3 and @DETAIL = 'Y'
begin SET @RBS_2 = @RBS
SET @RES_RBS = @RBS_0 + '.' + @RBS_1 + '.Mgr.Resources'
end

IF @LEVEL >2 and @DETAIL <> 'Y'
begin SET @RBS_2 = @RBS
SET @RES_RBS = @RBS_0 + '.' + @RBS_1 + '.Mgr.Resources'
end

begin
UPDATE @tblCode
SET RES_RBS = @RES_RBS,
RBS = @rbs
WHERE rowid = @rowid

end

FETCH NEXT FROM Select_RBS INTO @RES_NAME, @RBS, @LEVEL, @DETAIL, @RowId

END

-- UPDATE @tblCode
-- SET level = level + 2
-- UPDATE @tblCode
-- SET level =5 where level > 5

CLOSE Select_RBS
DEALLOCATE Select_RBS
END

INSERT @retCodes

SELECT Position_Nbr, Res_Name, RBS,descr, RES_RBS, Detail, Level, SortOrder
FROM @tblCode

--order by rbs,sortorder

RETURN
END

-- Code Ends

4 comments:

Anonymous said...

The link to the "EPM Application Configuration Guide" takes you to the "Project Server 2003 Configuration Planning Guide" and this guide doesn't include the RBS information you were referencing in your article. Can you suggest where this can be found?

Thanks
Scott

Scott M. Wagner said...

It is the app config guide. I am fixing the link and posting it here as well:


http://www.microsoft.com/downloads/details.aspx?FamilyID=dd7c1bad-6217-4568-be01-c3c3c907a526&DisplayLang=en

Starting on page 121

HardCode said...

Scott,

Thanks for the quick reply. I'm having problems finding detailed information on developing an RBS for MS Project Server 2007. Most of the company working the project is geographically co-located and limited to about 60 people and our organization is pretty flat, but I think the RBS could be useful. I'm just not sure what features to include in the RBS structure. I've found some limited information on Microsoft's site, but nothing of substance. Can you provide some suggestions or point me toward some resources?

Thanks
Scott

Anonymous said...

Still wrong