This blog is an effort to share new ideas and showcase different capabilities within the capacity of Hyperion applications and ODI. The views shared through this blog are solely mine and do not represent the views of any organisation I am employed with or any person I work with.
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