Clarity

One of our BU's has changed their child role setup to better reflect the competences of their resources.

They have given their resources (100+) a new primar role. but this did'n't update the project roles on the project team pages. Updating this manually would require a huge amount of effort as people are allocated to multiple projects.

We updated the project roles in our test instance via below query:
select srm.id Resource_db_id,
srm.full_name Resource_Name,
prj.prprimaryroleid primary_role_db_id,
SRPRI.FULL_NAME primary_role
from srm_resources srm,
prj_resources prj,
srm_resources srpri
where SRM.ID=prj.prid
and prj.prprimaryroleid=srpri.id
and SRPRI.FULL_NAME in (select distinct full_name
from srm_resources
where full_name like '***%')

This worked well for the project team pages and the resource planning portlets (e.g. weekly detail, role capacity portlets), but for the role allocations capacity portlet (select a role, select allocation tab, capacity page), the demand figures were missing for the new roles. We noticed this was because the query didn't trigger the slices to update, when we changed an allocation and saved it, the demand figures would show up.

On our test system we then ran another query to update the slice status and then ran the time slicing job:

Update prteam pr set pr.slice_status=2
where PR.PRRESOURCEID in (select distinct srm.id
from srm_resources srm,
prj_resources prj,
srm_resources srpri
where SRM.ID=prj.prid
and prj.prprimaryroleid=srpri.id
and SRPRI.FULL_NAME in (select distinct full_name
from srm_resources
where full_name like ***%'))

This all worked fine and resolved the issue. But we are still in doubt to run the same queries on our Production instance. We feel a bit uncomforable and are afraid missed some other issues.

Has anybody experience with such a mass update of roles? is there an alternative way of doing this when manual updates is not an option?

Thanks in advance!

Regards,
Wouter

Hi Wouter
Direct database updates like this are not supported, and I would not recommend doing something of the scale you describe at all in a production environment. Clarity has a tendency to surprise you with the complexity of the data model and it's easy to get things out of step.

The supported way for a bulk update is to read out the project via XML and write it back with the amended project role for each resource on the team, again via XOG and XML.

An alternative is to use an object based portlet, which references the Team object. This will give you an editable portlet with all projects and resources in one place, and you simply put it in edit mode and change each role as required - still manual, but will save you having to go in and out of projects and their team tab. A very quick example in the screenshot.

Owen

Thanks Owen,

We will give it a try using xog.

Wouter

We have tested this via xog, it is indeed possible to change the project Roles, however the slices are still not updated, it is still required to update the slice status of the updated records to value 2 to ensure the slice job picks up the new Project Roles.

I don't see another option to update the Project Roles for 1000+ allocations, other then via a DB query/ combination of xog and query, via ui is too time consuming especially because you can't configure weekly detail portlet to display primairy Role and project Role in the same list

But we have done more tests, I really don't see any problem to update it like this

Wouter

Hi Wouter,

We had issues with slices in the past and CA has suggested db update, to set slice_status=1 for reslicing.

I think, setting slice_status to 1 from db only re-slices the blob. When ever we find missing slices cause of unplanned outages, we reslice the appropriate blobs using this method.

Thanks and Regards,
Rajini