Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

« Essbase Users Last Login Time Using API | Main | Hyperion Essbase Metadata Comparison Tool »

Hyperion Essbase Alternate Hierarchy Sync Up

Hyperion Essbase Alternate Hierarchy Sync Up

I was working with Essbase Implementations project for one of the Retails customer and the business requested us to create the alternate hierarchies to fulfill their month end reporting requirements. These alternate hierarchies are derived from base hierarchy and this should be updated whenever the new member is added into base hierarchy. So business admin has to find out the new member added to the base hierarchy and add that member to the alternate hierarchy. But it is difficult to find out the new member added to the Essbase outline. On this blog, I have explained about how the Alternate Hierarchy sync tool can be used to find out the new members added to the base hierarchy.

Hyperion Essbase API:

The Essbase Java API is an interface which has the powerful features such as application and database administration. I have written Java program using this Essbase API that can extract the outline members from the base hierarchy and does the VLookup against alternate hierarchy. Also the members can be added to the alternate hierarchy automatically. But business wants to review the members and add it manually.

Alternate Hierarchy Sync up:

The below flow chart describe about the flow of Essbase alternate hierarchy sync up process.

 

Figure 1   View image

 

Business Requirements:

 

Business users were using the alternate hierarchy for the month end reporting requirements and these alternate hierarches are manually built by the business based on the base hierarchy structure. If any new members added to the base hierarchy, then these new members might be added to the associated alternate hierarchy structure. Both base hierarchy and alternate hierarchy should be sync for certain hierarchy. After the dimension build process, users need to manually search for the new members added in the base hierarchy and add those new members to the alternate hierarchy. This process was more time consuming.

The requirement was to develop and automate the process to identifying the new members added to base hierarchy and those members which are not added the alternate hierarchy. I have developed alternate hierarchy sync up tool using Essbase API programming which can pull the outline members from Essbase application and does the comparison against the given alternate hierarchy to provide the new member details. This tool has saved lot of efforts in terms of alternate hierarchy building and also this sync process can be automated using the sync up tool.

A)  Alternate Hierarchy Sync up tool:

This Essbase API Java programming is used to develop this Alternate Hierarchy Sync up tool which can extract the level0 members from the outline base hierarchy and does the VLookup against the alternate hierarchy members to produce the missing members from the alternate hierarchy. These missing members are might be added to the alternate hierarchy if it is needed for business reporting.

This Alternate Hierarchy Sync up tool has the following modules.

1.    Connecting into the Essbase Server

2.    Extracting Level 0 members from Base Hierarchy

3.    Extracting Level 0 members from Alternate Hierarchy

4.    VLookup

5.    Generate the results

             The below are the Essbase API packages used in the Java Program:

com.essbase.api.base.*;

com.essbase.api.datasource.*;

com.essbase.api.domain.IEssDomain;

com.essbase.api.metadata.*;

com.essbase.api.session.*;

com.essbase.api.dataquery.*;

com.essbase.api.domain.*;

 

Figure 2   View image

B) Pseudo Code

 

I have given the pseudo code in the below section and explained about how it is used for building the Alternate hierarchy sync up tool.

1.     Connecting into the Essbase Server

The parameters - Username, pwd, OLAP server name and the provider services URL are passed through the signOn() method to sign on into Essbase applications.

The connection is created for TestApp.TestDB application.

acceptArgs(args);

ess = IEssbase.Home.create (IEssbase.JAPI_VERSION);

/**Please provide username, password, and provider services URL**?

IEssDomain dom = ess.signOn(UserName, PWD, false, null, s_provider);

olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);

olapSvr.connect(); // Connecting into the Essbase server.

IEssCube cube = olapSvr.getApplication("TestApp").getCube("TestDB");

                  

Once the connection is made, system gets the name of base hierarchy and alternate hierarchy from user.

System.out.println("\nEnter Base Hierarchy Name (Ex - 200):");

                   Scanner baseScanner = new Scanner(System.in);//Reads input from user

                   String baseHier = baseScanner.nextLine();

                                     

System.out.println("\n\nEnter Alternate Hierarchy Name (Ex - AH_200_Total):");

                   Scanner alterScanner = new Scanner(System.in); //Reads input from user

                   String alterHier = alterScanner.nextLine();

2.    Extracting Level 0 Members from Base Hierarchy

The Base hierarchy level 0 members is extracted and it is prepared to compare with level 0 members from alternate hierarchy. The sync up tool reads the hierarchy name details from user and extract the level0 members for those given names only.

The Method listBaseEntityMembers () pulls the level 0 members from the Entitiy dimension for the given hierarchy and writes the output to csv file. If we want to pull the specific dimension, we need to modify the parameters in the executeQuery() method. In the below sample code, the executeQuery() method pulls the bottom level member from the Entity dimension.

static void listBaseEntityMembers(IEssCube essCube, String essBaseHier) throws EssException

          {

                   IEssCubeOutline essOtl = null; //Create object for outline and initiate

                   FileWriter fileWriter = null; //Create object and initiate to write output                        

                  

          try {

          IEssMemberSelection mbrSel = cube.openMemberSelection("Sample ");

//Create object and initiate for cube operation

 

mbrSel.executeQuery(baseHier, IEssMemberSelection.QUERY_TYPE_BOTTOMLEVEL,

IEssMemberSelection.QUERY_OPTION_MEMBERSONLY,

 "Entities", "", "");

//Entities - Base Dimension, QUERY_TYPE_BOTTOMLEVEL - Level0 members

 

          IEssIterator mbrs = mbrSel.getMembers();

                                     

          fileWriter = new FileWriter("ZBaseEnt_"+baseHier+".csv");

//Create new output file

 

          fileWriter.append("Entities"+","+"Desc"+"\n");

for (int i = 0; i < mbrs.getCount(); i++) //Iteration for each level 0 members in the outline

 

{

              IEssMember mbr = (IEssMember)mbrs.getAt(i); //Get the member

                   {

fileWriter.append(mbr.getName()+","+ mbr.getAlias("Default")  +","+mbr.getShareOption()+"\n"); //Write output to the file

                    }

          }

          } catch (EssException x) {System.err.println("Error" + x.getMessage()); }

           }

                                  }

         

3.    Extracting level 0 members from alternate hierarchy

The alternate hierarchy level 0 members is extracted and it is prepared to compare with level 0 members from base hierarchy. The sync up tool reads the alternate hierarchy name details from user and extract the level0 members for those given names only.

The Method listAlterHierMembers () pulls the level 0 members from the Entitiy dimension for the given hierarchy and writes the output to csv file. If we want to pull the specific dimension, we need to modify the parameters in the executeQuery() method. In the below sample code, the executeQuery() method pulls the bottom level member from the Entity dimension.

static void listAlterHierMembers (IEssCube cube, String baseHier) throws EssException

          {

                   IEssCubeOutline otl = null; //Create object for outline and initiate

                   FileWriter fileWriter = null; //Create object and initiate to write output                        

                  

          try {

          IEssMemberSelection mbrSel = cube.openMemberSelection("Sample ");

//Create object and initiate for cube operation

 

mbrSel.executeQuery(baseHier, IEssMemberSelection.QUERY_TYPE_BOTTOMLEVEL,

IEssMemberSelection.QUERY_OPTION_MEMBERSONLY,

 "Entities", "", "");

//Entities - Base Dimension, QUERY_TYPE_BOTTOMLEVEL - Level0 members

 

          IEssIterator mbrs = mbrSel.getMembers();

                                     

          fileWriter = new FileWriter("ZAltEnt_"+baseHier+".csv");

//Create new output file

 

          fileWriter.append("Entities"+","+"Desc"+"\n");

for(int i = 0; i < mbrs.getCount(); i++) //Iteration for each level 0 members in the outline

 

{

              IEssMember mbr = (IEssMember)mbrs.getAt(i); //Get the member

          if (mbr.getShareOption().toString().equals("Shared member") && !mbr.getShareOption().toString().equals("Never share")) //Gets only shared member

                   {

fileWriter.append(mbr.getName()+","+ mbr.getAlias("Default")  +","+mbr.getShareOption()+"\n"); //Write output to the file

                   }

          }

          } catch (EssException x) {System.err.println("Error" + x.getMessage()); }

           }

                                  }

         

 

4.     VLookup:

Now the level 0 members are extracted from both base and alternate hierarchy for the given name. Both base and alternate hierarchy level 0 members should be compared to get the new members which are not added to the alternate hierarchy members.  GetEntities () method is implemented to perform VLookup operation that lists all the missing members from the alternate hierarchy.

The below code reads the level0 members from base hierarchy .CSV file which is generated from listBaseEntityMembers () method and reads the level0 members from alternate hierarchy .CSV file which is generated from listAlterHierMembers method.

 

private static void GetEntities (IEssbase ess, IEssCubeView cv, String baseHier, String alterHier) throws EssException

{       

   List<String[]> colSAP; //Initiate list object

   List<String[]> colHyp; //Initiate list object

   boolean match = false;

   FileWriter fileWriter = null; //Initiate File Writer object

   String dataESS = null; //String Variable

        try

          {

                        colHyp = readFile("ZBaseHier.CSV"); //Read Base hierarchy members

                   colSAP = readFile("ZAlterHier.csv");//Read Alternate Hierarchy members

                   fileWriter = new FileWriter("Results_Base&AlterHier.csv");

//New output file

 

                    for (int i = 0; i < colHyp.size();i++) //Iteration for each line from the

essbase level 0 extaction file

                   {

                              for (int j = 0;j < colSAP.size();j++)

                             //Iteration for each line in the SAP metadata file

                             {

                                       //VLookup Operation

if(       colHyp.get(i)[0].equals(colSAP.get(j)[0])) 

                                      {

                                                match = true;

                                                break;

                                      }                                    

                                      else

                                      {

                                                match = false;

                                      }

                             }

                             if (match == false)

                             {

 fileWriter.append(colHyp.get(i)[0]+" "+dataESS+"\n" ) ;        

//Write the output to the new file  

                             }

                    catch (IOException e)         {e.printStackTrace();}

          }

}

 

5.    Produce the results

Finally the list is produced for new members which are not available in the alternate hierarchy. These members can be added if it is needed for business reporting.

1.  Example

The below example explains about how the new members are found using alternate hierarchy sync up tool.

Level 0 members from Base Hierarchy - Entity Dimension:

Entity Parent

Entity Child

Desc

100

10000XX1

HR

100

10000XX2

Corps

100

10000XX3

Sales

100

10000XX4

Wholesale

 

Level 0 members from Alternate Hierarchy:

Entity Parent

Entity Child

Desc

100

10000XX1

HR

100

10000XX2

Corps

100

10000XX3

Sales

 

After the execution using Alternate Hierarchy sync up tool:

Output File:

New  Members

10000XX3

 

2.  Conclusion:

I have used this tool while building the alternate hierarchy based on the business request and it saved lot of efforts and also this sync process can be automated.

Reference(s)

1.    Oracle Documentations https://docs.oracle.com/cd/E40248_01/epm.1112/aps_java_api/com/essbase/api/metadata/IEssCubeOutline.html

 

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles