Title

Friday, May 3, 2013

Shared Services Roles and their Internal IDs

Today, I was asked a question by one of my colleagues regarding the shared services and the roles used in Hyperion Planning. He was supposed to do some documentation and wanted to determine what does HP:0001 or HP:0002 mean.

This is not limited to Hyperion Planning, Shared Services maintains a unique id for each role which is assigned to each group or user when they are provisioned in shared services. These internal id can be exported using Life Cycle Management (LCM) but they do not provide easy names like Planner, Interactive user, etc.

We can fetch this information from the relational tables of configured Shared Services. There are two tables which maintain this information - CSS_ROLES, CSS_ROLE_LOCALES. CSS_ROLES contains information about the internal ID, Product Code, Role ID and CSS_ROLE_LOCALES contain names of these role ids in different locales. All we have to do is run a SQL query joining these two tables on IDENTITY_ID fields.

Following is a sample query for pulling roles for Hyperion Planning:

SELECT tb1.[ROLE_ID]
      ,tb1.[PRODUCTCODE]
      , tb2.[NAME]
FROM [CSS_ROLES] tb1, [CSS_ROLE_LOCALES] tb2
WHERE
tb1.[PRODUCTCODE] = 'HP'
AND  tb1.[IDENTITY_ID] =  tb2. [IDENTITY_ID]
AND tb2.[LOCALE] = 'en'

This  gives us following records:


Similarly, for other products, we can change the PRODUCTCODE field to any of the following

Hope this helps :)