Automation of Hyperion Shared Services User (LDAP) addition process


I was on a project where client wanted to have an automated process for adding the LDAP users into Hyperion Shared services. They had security reasons in doing so and I had to find a way to do it.

The key here was to use CSSImport file. (as discussed in the previous blog

The challenge here was how to automatically create the csv file with the provisioning information.
We made use of LDAP and Oracle to generate the csv file automatically!!!!!!!

The method implemented was to load the LDAP users (the users that we need will be tagged as Hyperion) into an Oracle table and create a procedure around it. The output will load data into a table with the required format.
create or replace PROCEDURE PRC_HSS_ESS_WA_FR AS
BEGIN
Declare
      Cursor MapTable is (Select distinct emp_num from I_Employee_Hierarchies);
        Rs MapTable%RowType;
        SecLine VARCHAR2(2000);
        Ct Number;
  Begin      
        Ct := 1;
        delete from HSS_ESS_WA_FR_PROVISION;
        SecLine := ‘#group’;
        insert into HSS_ESS_WA_FR_PROVISION(SL, PROVISION) values (Ct, SecLine); ct := ct +1;
        SecLine := ‘id,provider,name,description’;
        insert into HSS_ESS_WA_FR_PROVISION(SL, PROVISION) values (Ct, SecLine); ct := ct +1;
SecLine := ‘GR_ESS_WA_FR_HSS,Native Directory,GR_ESS_WA_FR_HSS,Online Users Group’;
        insert into HSS_ESS_WA_FR_PROVISION(SL, PROVISION) values (Ct, SecLine); ct := ct +1;
SecLine := ‘#group_children’;
        insert into HSS_ESS_WA_FR_PROVISION(SL, PROVISION) values (Ct, SecLine); ct := ct +1;
SecLine := ‘id,group_id,group_provider,user_id,user_provider’;
        insert into HSS_ESS_WA_FR_PROVISION(SL, PROVISION) values (Ct, SecLine); ct := ct +1;

          Open MapTable;
          Loop
              Fetch MapTable into Rs;
              Exit when MapTable%notfound;    
              SecLine := ‘GR_ESS_WA_FR_HSS,,,’||Rs.emp_num ||’,Ceratechsoft LDAP’;
              insert into HSS_ESS_WA_FR_PROVISION(SL, PROVISION) values (Ct, SecLine); ct := ct +1;


—            dbms_output.put_line(SecLine);
            
    end Loop;
    SecLine := ‘#provisioning’;
    insert into HSS_ESS_WA_FR_PROVISION(SL, PROVISION) values (Ct, SecLine); ct := ct +1;
    SecLine := ‘project_name,application_name,role_id,product_type,user_id,user_provider,group_id,group_provider’;
    insert into HSS_ESS_WA_FR_PROVISION(SL, PROVISION) values (Ct, SecLine); ct := ct +1;
    SecLine := ‘Analytic Servers:ceraessb-1531:1,PMS,Filter,ESBAPP-9.3.1,,,GR_ESS_WA_FR_HSS,Native Directory’;
    insert into HSS_ESS_WA_FR_PROVISION(SL, PROVISION) values (Ct, SecLine); ct := ct +1;
    SecLine := ‘#provisioning’;
    insert into HSS_ESS_WA_FR_PROVISION(SL, PROVISION) values (Ct, SecLine); ct := ct +1;
    SecLine := ‘project_name,application_name,role_id,product_type,user_id,user_provider,group_id,group_provider’;
    insert into HSS_ESS_WA_FR_PROVISION(SL, PROVISION) values (Ct, SecLine); ct := ct +1;
    SecLine := ‘Hyperion System 9 BI+,Hyperion System 9 BI+:ceraessb-1531.ceratechsoft.com:6800::1,Explorer,HAVA-9.3.1,,,GR_ESS_WA_FR_HSS,Native Directory’;
    insert into HSS_ESS_WA_FR_PROVISION(SL, PROVISION) values (Ct, SecLine); ct := ct +1;


    dbms_output.put_line(‘Successful Exit’);


  end;
END PRC_HSS_ESS_WA_FR;

Rest was easy….(create a bat/sh file to run the CSSImport.bat file).

For the people who would like to have a more dynamic procedure can make the server information (highlighted as green) as a parameter and store the params in a table and call it from there.

Hope it helps 🙂

Advertisements

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.


Leave a Reply

0 thoughts on “Automation of Hyperion Shared Services User (LDAP) addition process