Hyperion Shared Services – Getting Provisioning Information for users and groups for a particular application

In this blog, I would be talking about how to the get the provisioning information for users and groups for a particular application by designing some queries that hit the backend relational tables of Hyperion Shared Services. 
I am not a fan of the provisioning report generated by Hyperion Shared Services so this is my stab at the report to generate some good formatted reports.

The below two snapshots show me provisioning two groups and giving them Planner and Mass-Allocation access to the respective groups.

Now, the provisioning information is stored in a table called as CSS_PROVISIONING_INFO which is as shown in the below snapshot.

The list of users provisioned to the Hyperion Planning REVPLAN application is as shown in the below snapshot.

The metadata structure for the CSS_PROVISIONING_INFO table is as shown in the below snapshot.

The data that is present in the CSS_PROVISONING_INFO table pivoted for reference is present in the below snapshot. 

Now, the MEMBER_IDENTITY in the above snapshot refers to the user or group provisioned for access. This information will come from either the CSS_USERS or CSS_GROUPS table.

The ROLE_IDENTITY table basically comes from the CSS_ROLES_LOCALE table.

Now that we have got the provisioning information, the real trick would be to get the list of users and groups who are provisioned for access to this application.

The below SQL query can be used for getting the list of users who are provisioned for access to the application. The query basically joins the CSS_USERS, CSS_ROLES_LOCALE and CSS_PROVISONING_INFO table to get the provisioning details is a nice format. 

The output on running the above SQL query is as shown in the below snapshot.

The next query shown in the below snapshot can be used for getting the lists of groups provisioned for access to the REVPLAN application.

The output of this query is as shown in the below snapshot.

Now, if we do a UNION of the above two queries, we can get a list of all the users and groups provisioned for access to REVPLAN application.

The output of the join query is as shown in the below snapshot.

SQL queries: -
 
To get a list of users provisioned for access to an application, use the following SQL: -

/* Users Provisioned for access to the application */
 
select CSS_USERS.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE  FROM CSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_USERS
WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY)
and CSS_PROVISIONING_INFO.APPLICATION_ID='HP:REVPLAN'
and CSS_ROLE_LOCALES.locale='en'
AND CSS_PROVISIONING_INFO.MEMBER_TYPE=1
AND lower(CSS_USERS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY)
To get a list groups provisioned for access to an application, use the following SQL: -

/* Groups Provisioned for access to the application */
 
select CSS_groups.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE  FROM CSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_GROUPS
WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY)
and CSS_PROVISIONING_INFO.APPLICATION_ID='HP:REVPLAN'
and CSS_ROLE_LOCALES.locale='en'
AND CSS_PROVISIONING_INFO.MEMBER_TYPE=2
AND lower(CSS_GROUPS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY)

The consolidate ed list of users and groups provisioned for access to an application is as shown in the below SQL: -

/* Consolidated list of users and groups for an application*/

(
select CSS_USERS.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE  FROM CSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_USERS
WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY)
and CSS_PROVISIONING_INFO.APPLICATION_ID='HP:REVPLAN'
and CSS_ROLE_LOCALES.locale='en'
AND CSS_PROVISIONING_INFO.MEMBER_TYPE=1
AND lower(CSS_USERS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY)
)
UNION
(
select CSS_groups.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE  FROM CSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_GROUPS
WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY)
and CSS_PROVISIONING_INFO.APPLICATION_ID='HP:REVPLAN'
and CSS_ROLE_LOCALES.locale='en'
AND CSS_PROVISIONING_INFO.MEMBER_TYPE=2
AND lower(CSS_GROUPS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY)
)
To get the list of all users and groups provisioned for access in the Hyperion environment, use the following SQL: -

/* Consolidated access list for users and groups */
(
select CSS_USERS.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE  FROM CSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_USERS
WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY)
and CSS_ROLE_LOCALES.locale='en'
AND CSS_PROVISIONING_INFO.MEMBER_TYPE=1
AND lower(CSS_USERS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY)
)
UNION
(
select CSS_groups.NAME,CSS_ROLE_LOCALES.NAME,APPLICATION_ID, MEMBER_IDENTITY, ROLE_IDENTITY, MEMBER_TYPE  FROM CSS_PROVISIONING_INFO, CSS_ROLE_LOCALES,CSS_GROUPS
WHERE LOWER(CSS_ROLE_LOCALES.IDENTITY_ID)= LOWER(CSS_PROVISIONING_INFO.ROLE_IDENTITY)
and CSS_ROLE_LOCALES.locale='en'
AND CSS_PROVISIONING_INFO.MEMBER_TYPE=2
AND lower(CSS_GROUPS.IDENTITY_ID)=lower(CSS_PROVISIONING_INFO.MEMBER_IDENTITY)
)

The consolidated lists of access for my environment is as shown in the below snapshot.