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

« Hyperion Essbase Alternate Hierarchy Sync Up | Main | BLOCK CHAIN, WAY MORE THAN BITCOIN »

Hyperion Essbase Metadata Comparison Tool

Hyperion Essbase Metadata Comparison Tool:

In my earliear blog, I have explained about How the Essbase Java API can be used for alternate hierarchy sync up. On this blog, I going to explain about how the Essbase API can be used to find out the inactive members in the Essbase outline. I have followed same procedure as similar to alternate hierarchy sync up tool to develop this Essbase metadata comparision tool.

During the Essbase implementations, we received lot of new members from SAP source system for testing and loaded into Essbase outline. Later, those test members were not used for any reporting and validation. So business admin requested us to find out those test members and remove it from outline. It is very difficult to find out the test members manually. So I have developed a tool to compare Essbase outline members against the metadata files received from Source system and to check if there are any inactive members in the outline.

Hyperion Essbase API:

I have used Essbase Jave API to develop this Essbase Metadata comparion tool. Essbase API has a more efficient features that can be used for hyperion administration tasks, such as outline extraction, user provisioning , etc. I have developed Java programs using these Essbase API to pull the Essbase outline members for specific dimension and those extracted level 0 members could be compared against source files received from SAP system.

Metadata Comparison:

 

The following flow chart describe about the flow of Essbase metadata comparison against Source metadata files.

 

Figure 1View image

A) Business Requirements:

 

SAP source system was providing metadata source files to the business and these source files were loaded into Essbase outline to perform the system testing and mock testing as per the business requirements.  The SAP development team has created many dummy members to test the metadata extraction functionality in the SAP source system and these dummy members are loaded into the Essbase outline through the dimension build process. This dimension build process is automated.

 

Later, Business admin has requested us to find out the dummy or inactive members and remove it from the Essbase outline to increase the performance of the application. Initially this comparison activity was done manually by the developers using the Excel VLookup method. But this method was more time consuming and there was a possibility of human error in the end results. Later I have developed the comparison tool using JAVA API which has been used to pull the outline members from Essbase application and does the comparison against metadata source files. During the testing phase, This tool has reduced the manual efforts in terms of testing.

B) Comparison Tool


The aim is to extract the leaf level members from the Essbase outline and perform the VLookup against the Source metadata files to produce missing members in the source files where the missing members might be exists as inactive members in the Essbase outline.

This metadata comparison tool has the following sub modules.

1.    Essbase OLAP Server Sign-On

2.    Extracting Level 0 members from Essbase outline

3.    VLookup Operation

4.    Check if there are any data for Missing Members

5.    Inactive members report

  

          The following are the Essbase API packages which has been used to develop the comparison tool:

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 2View image

C)  Pseudo Code

 

I have given pseudo code which is used for building this comparison tool.

1.  Essbase OLAP Server Sign-On

 

To Sign on to the Essbase applications , you have to pass  the login credentials , name of the OLAP server and the URL of the provider services as parameters.

 

acceptArgs(args);

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

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

IEssDomain dom = ess.signOn(ess_userName, ess_password, false, null, s_provider);

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

// Give the olap server name

olapSvr.connect(); //Sign on into Essbase server.

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

 

2.  Level 0 Members Extraction:

 

The source system provides the required metadata for the business reporting. You need to get the extraction of all the level 0 members for the specific dimension from the Essbase outline and it should be compared with SAP source file.

The Method listAccountMembers() pulls all the leaf level members from the Account dimension and writes the output to excel or CSV file. If you need to pull any specific members from any of the base dimension, you should update the arguments of executeQuery() method. The executeQuery() method extract the level0 member from the Account dimension in the below code.

          static void listAccountMembers(IEssCube cube) throws EssException

          {

                   IEssCubeOutline ebOtl = null;  //Outline object creation and initiation

FileWriter fWriter = null; //File object creation and initiation

                   try {

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

//Cube operation and object initiation

EssMbrSel.executeQuery("Accounts", IEssMemberSelection.QUERY_TYPE_BOTTOMLEVEL, IEssMemberSelection.QUERY_OPTION_MEMBERSONLY,

 "Accounts", "", ""); //Accounts - Standard Dimension, QUERY_TYPE_BOTTOMLEVEL - Level0 members

                   IEssIterator mbrs = mbrSel.getMembers();

                   fileWriter = new FileWriter("ZAccounts.csv"); //Create output file object.

 

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

{

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

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

                              }

          } catch (EssException essx) {}

}

3.   VLookup Operation:


In the above code, the leaf level members are fetched from the Account dimension using listAccountMembers () method and then these level0 members should be compared with source metadata account file to generate the absent members.  GetAccounts () method has the code to perform VLookup operation. All the missing members are written to the output file in VLookup operation.

The below psudeo code get the members from Account.CSV file which is generated from listAccountMembers () method and get the members from glaccount.csv file which is uploaded from source system.

 

private static void GetAccounts(IEssbase essX, IEssCubeView csv) throws EssException

{       

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

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

   Boolean matchVar = false;

   FileWriter fileW = null; //Initiate File Writer object

   String ESSdata = null; //String Variable

        try

          {

  colHyp = readFile("Accounts.CSV"); //Read extracted level 0 members

            colSAP = readFile("glaccount.csv");//Read Source Metadata file

            fileW = new FileWriter("CompareAccounts.csv"); // output file object initiation.

            for(int i = 0; i < colHyp.size();i++)

            //For loop iteration for each line from the Essbase extaction file

                   {

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

                             //Iteration for each line in the SAP source file

                             {

                                       //VLookup Operation

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

                                      {

                                                matchVar = true;

                                                break;

                                      }                                    

                                      else

                                      {

                                                match = false;

                                      }

                             }

                             if (matchVar == false)

                             {

 fileW.append(colHyp.get(i)[0]+" "+ ESSdata +"\n" ) ;  

//append the output into the new file       

                             }

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

          }

}

 

 

4.   Check if there are any data for Missing Members


Once the list of missing members are populated after the VLookup operation, the script checks if there are any data for those missing members. If the data found for any of the members, that may be retained in the outline.

The method performCubeViewOperationACC () is implemented for verifying data for the year FY17. The New Year can be updated in the code if needed.

Static String performCubeViewOperationACC (IEssbase essArg, IEssCubeView csv, String opeStr) throws EssException

          {

                    String value = null;

                    IEssGridView grid = csv.getGridView(); //Create object for data retrieval grid

                    grid.setSize(5, 10); //Set size for the grid

       grid.setValue(3, 1, opStr); //opeStr - Level0 member

       grid.setValue(1, 2, "FY17"); //Year

       grid.setValue(0, 2, "Total Entities"); //Below fields are optional

       grid.setValue(2, 2, "YearTotal");

       grid.setValue(0, 5, "Actual");

       grid.setValue(0, 6, "Final");

                    grid.setValue(0, 7, "LOC");

       

                   IEssOperation op = null; //Create object for the data retrieval

       

                   op = cv.createIEssOpRetrieve();

                   csv.performOperation(op); //Performs data retrieval for the given pov.

 

                   if (grid.getValue(3, 2).toString().equals("#Missing")

                                      && grid.getValue(3, 3).toString().equals("#Missing")

                                      && grid.getValue(3, 4).toString().equals("#Missing"))

                   //Checks if the data is present.

                   {

                             value = "- This member has No Data (#Missing)";

                   }

                   else

                   {

                             value = "- This member has Data ";

                   }

                   return value;

    }

 

5.   Inacitive members report

Finally, the list is produced for missing members from the Essbase outline. These members can be deleted from the outline if the members are not required.

Example

 

The below example explains about how the missing members are found using comparison tool.

Level 0 members from Essbase outline - Account Dimension:

Account Parent

Account Child

Desc

100

10000XX

Sales

300

30000XX

Consumable

400

40000XX

Salary

500

50000XX

Marketing

 

 

Level 0 members from Source System:

Account Parent

Account Child

Desc

100

10000XX

Sales

400

40000XX

Salary

500

50000XX

Marketing

 

After the execution using Comparison tool:

Output File:

Missing Members

30000XX - This member has No Data (#Missing)

 

Conclusion:

I have used this in all the testing phases and save lot of efforts. Also there are option to delete the members automatically from the outline.Based on the business requirments, you can modify the Java program and execute it.

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