Automation of Hyperion Shared Services User (LDAP) addition process 5

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 🙂