I was planning to post this a lot sooner but I am finding it increasingly difficult to find any spare time lately, in the last blog I went through the final instalment of deleting essbase members and I thought that it would be worthwhile just finishing off with a post closely linked to the last blog but this time going through how to rename essbase members.
Currently there is no way to rename essbase members without engaging in some sort of customisation in ODI, even with just essbase it is not as easy as it should be but anyway let me take you through a method to achieve this. If you didn’t read the last blog on deleting members this blog will not really make much sense because it uses the same concept and I will be reusing elements I created during the blog.
In this example I am using the ASOSamp.Sample essbase database and the members that are going to be renamed are children of “Televisions” within the Products dimension and they are “Flat Panel”, “HDTV” and “Stands”
In the previous blog the source flat file only contained one column with the member to be deleted, as this solution will be renaming members it requires one column with the original member name and another column with the renamed member.
The source does not have to be a flat file it could be easily an RDBMS source.
In the source file there is a member that doesn’t exist in the outline so the solution should log and ignore this record.
Within the designer a new Datastore was created and reversed to handle the source file.
A new model was then created, this step is not essential as it is possible to modify existing essbase related models and add a new Datastore.
The model is associated with a Logical schema that physically points to the ASOSAMP.Sample database.
A new Datastore was created.
Two columns were added one to handle the current member name and the other for holding what the member will be renamed to.
To save time the customized IKM that was created in the last blog could be duplicated, I am going to assume you read through the last blog but didn’t create the IKM so a new would need to be created.
The IKM was named “IKM RENAME ESSBASE MEMBERS” with Muti-Connections enabled and Target Technology set to “Hyperion Essbase”.
Three options were then created for the KM, the options are exactly the same as the ones used in the current “IKM SQL to Hyperion Essbase (METADATA)” and the information can be copy and pasted across.
The RESTRUCTURE_DATABASE option will hold the value of how the database should be restructured once the members have been deleted.
Option to set whether logging will be enabled or not.
Option to hold path and filename of log.
A new command “RENAME_MEMBERS” was added to the KM and technology set to Jython.
The command will process and store the essbase connection information, KM options, execute SQL to retrieve the source records and then pass all this information into a Java class, the Java class that will be called is going to be named “RenameMembers”
The following code was then added to the command.
I will briefly go over what the script does; once again it is pretty much like the delete member script.
First the Java classes that are going to be used in the jython script are imported; this includes the Java class that will be called to rename the members. Next the essbase connection properties are stored; these values are retrieved from the target Datastore used in the interface using the ODI API getInfo method.
The KM options used in the interface are then retrieved using the ODI API getOption method and stored in variables.
The variables are all stored in a HashMap, the HashMap is passed into the Java class.
A SQL select statement is generated and stored by using a combination of ODI API methods, this code is exactly the same as used in the current essbase KMs. The SQL will retrieve all the records in the source/staging area.
The source JDBC connection that is being used the interface is then stored.
The SQL is executed and a ResultSet is returned, the ResultSet and HashMap are then passed into external Java class “RenameMembers”
The requirement for the Java class is to extract the variables stored in the HashMap and then use this information to connect to essbase using the API, once connected open the database outline, cycle through the members held in the ResultSet and search for them in the outline, if the member is found then rename it, if the enable log option is set then log the renames and members not found.
The database should then restructured depending on the value stored in the RESTRUCTURE_DATABASE option.
Hopefully the Java class code makes perfect sense as I have included comments
The only real change from the delete members class is the rename(java.lang.String newMbrName) method is used.
The class was compiled and a jar file created, if you are using ODI 10g then the jar file needs placing in the <odi_home>/oracledi/drivers directory, all ODI components including agents will need restarting to pick up the new file, if on 11g and using a standalone agent then place the file in <odi_home>/oracledi/agent/drivers, to use the local agent in the studio you will need to place the file in %APPDATA%\odi\oracledi\userlib on windows (windows 2008 it is %APPDATA%\Roaming\odi\oracledi\userlib) or USER_HOME/.odi/oracledi/userlib for unix.
The interface to perform the renames is created.
The Datastore associated with the flat file is dragged on to the source and the “REN_MEMBERS” Datastore created earlier is dragged on to the target, there is only one column to map between target and source. The memory engine was used as the staging area as the transformation is simple and the number of records to process is small.
The KM “IKM RENAME ESSBASE MEMBERS” is selected and the options populated.
After executing the interface the Operator can be opened to view if all the steps were successful, as you can see the process followed was a temporary table was created, the records from the flat file were loaded into the temporary table, the RENAME_MEMBERS step was executed and then the temporary table dropped.
Opening the essbase ASOSAMP.Sample outline you can see the members have been renamed.
In the log that was specified by the KM option “LOG_FILE_NAME” the renamed members are logged and the member that was not found in the outline.
If in the source a member to rename to already exists in the outline the interface will fail, for example if the interface was executed again with the following information in the source
The interface will fail, as the member “LCD HDTV” exists in the outline and it will not validate.
If you didn’t want the interface to fail due to duplicate members then the Java code could be updated to check whether the rename member name already exists in the outline, one way could be to use the findMembers(java.lang.String[] mbrNames) method and pass the original member and member to be renamed into it, if the rename member name already exists then the member rename would not be executed and the information written to the log.
To rename members from a different essbase database the Model could be easily updated to point to a different logical schema or the model copied, renamed and then pointed to a different logical schema, once again this makes it a useful and re-usable solution.
Currently there is no way to rename essbase members without engaging in some sort of customisation in ODI, even with just essbase it is not as easy as it should be but anyway let me take you through a method to achieve this. If you didn’t read the last blog on deleting members this blog will not really make much sense because it uses the same concept and I will be reusing elements I created during the blog.
In this example I am using the ASOSamp.Sample essbase database and the members that are going to be renamed are children of “Televisions” within the Products dimension and they are “Flat Panel”, “HDTV” and “Stands”
In the previous blog the source flat file only contained one column with the member to be deleted, as this solution will be renaming members it requires one column with the original member name and another column with the renamed member.
The source does not have to be a flat file it could be easily an RDBMS source.
In the source file there is a member that doesn’t exist in the outline so the solution should log and ignore this record.
Within the designer a new Datastore was created and reversed to handle the source file.
A new model was then created, this step is not essential as it is possible to modify existing essbase related models and add a new Datastore.
The model is associated with a Logical schema that physically points to the ASOSAMP.Sample database.
A new Datastore was created.
Two columns were added one to handle the current member name and the other for holding what the member will be renamed to.
To save time the customized IKM that was created in the last blog could be duplicated, I am going to assume you read through the last blog but didn’t create the IKM so a new would need to be created.
The IKM was named “IKM RENAME ESSBASE MEMBERS” with Muti-Connections enabled and Target Technology set to “Hyperion Essbase”.
Three options were then created for the KM, the options are exactly the same as the ones used in the current “IKM SQL to Hyperion Essbase (METADATA)” and the information can be copy and pasted across.
The RESTRUCTURE_DATABASE option will hold the value of how the database should be restructured once the members have been deleted.
Option to set whether logging will be enabled or not.
Option to hold path and filename of log.
A new command “RENAME_MEMBERS” was added to the KM and technology set to Jython.
The command will process and store the essbase connection information, KM options, execute SQL to retrieve the source records and then pass all this information into a Java class, the Java class that will be called is going to be named “RenameMembers”
The following code was then added to the command.
I will briefly go over what the script does; once again it is pretty much like the delete member script.
First the Java classes that are going to be used in the jython script are imported; this includes the Java class that will be called to rename the members. Next the essbase connection properties are stored; these values are retrieved from the target Datastore used in the interface using the ODI API getInfo method.
The KM options used in the interface are then retrieved using the ODI API getOption method and stored in variables.
The variables are all stored in a HashMap, the HashMap is passed into the Java class.
A SQL select statement is generated and stored by using a combination of ODI API methods, this code is exactly the same as used in the current essbase KMs. The SQL will retrieve all the records in the source/staging area.
The source JDBC connection that is being used the interface is then stored.
The SQL is executed and a ResultSet is returned, the ResultSet and HashMap are then passed into external Java class “RenameMembers”
The requirement for the Java class is to extract the variables stored in the HashMap and then use this information to connect to essbase using the API, once connected open the database outline, cycle through the members held in the ResultSet and search for them in the outline, if the member is found then rename it, if the enable log option is set then log the renames and members not found.
The database should then restructured depending on the value stored in the RESTRUCTURE_DATABASE option.
Hopefully the Java class code makes perfect sense as I have included comments
package org.moretolife.blog;
import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
import com.essbase.api.domain.*;
import com.essbase.api.metadata.*;
import java.util.HashMap;
import java.sql.*;
import org.apache.log4j.*;
import java.io.IOException;
/* Code attempted by John Goodwin, please don't flame me for it */
public class RenameMembers {
/* embedded mode to directly communicate with essbase*/
private static String s_provider = "Embedded";
/* pattern used for writing to log */
private static String logPattern = "%d{dd-MM-yyyy HH:mm:ss} %5p [%t]: %m%n";
public RenameMembers(HashMap paramMap, ResultSet rs) throws EssException,SQLException,IOException{
/* retrieve and store information essbase connection details from HashMap*/
String serverHost = ((String)paramMap.get("SERVER"));
String applicationName = ((String)paramMap.get("APPLICATION_NAME"));
String databaseName = ((String)paramMap.get("DATABASE_NAME"));
String userName = ((String)paramMap.get("USER"));
String password = ((String)paramMap.get("PASSWORD"));
/* retrieve and stoe ODI KM options from HashMap*/
String restructureDatabase = ((String)paramMap.get("RESTRUCTURE_DATABASE"));
boolean logEnabled = ((Boolean)paramMap.get("LOG_ENABLED")).booleanValue();
String logfileName = (String)paramMap.get("LOG_FILE_NAME");
IEssbase ess = null;
IEssOlapServer olapSvr = null;
IEssMember essMem = null;
FileAppender fileAppend = null;
Logger logger = null;
try{
/* check if the log enable option was selected*/
if(logEnabled){
/* use Log4J to set up log based on pattern and log file name*/
fileAppend = new FileAppender(new PatternLayout(logPattern), logfileName, true);
fileAppend.activateOptions();
logger = Logger.getLogger(logfileName);
logger.addAppender(fileAppend);
}
/* connect to essbase */
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
IEssDomain dom = ess.signOn(userName, password, false, null, s_provider);
olapSvr = dom.getOlapServer(serverHost);
olapSvr.connect();
/* essbase application/database defined by variables passed into class */
IEssCube essCube = olapSvr.getApplication(applicationName).getCube(databaseName);
/* open essbase database outline */
IEssCubeOutline cubeOutline = essCube.openOutline(false,true,true);
/* cycle through records in ResultSet */
while (rs.next()) {
try{
/* search for member in the outline from the current record of the ResultSet */
essMem = cubeOutline.findMember(rs.getString(1));
/* rename the member */
essMem.rename(rs.getString(2));
/* if logging enabled write to log to inform of rename */
if(logger != null){
logger.info("Member - " + rs.getString(1) + " renamed to " + rs.getString(2));
}
/* exception is caught if no member is found */
}catch(EssException noMember){
if(logger != null){
/* if logging enabled write to log to inform member not found*/
logger.error("Member - " + rs.getString(1) + " not found.");
}
}
}
/* verify outline, if outline does not verify interface will fail*/
cubeOutline.verify();
/* save outline */
cubeOutline.save();
/* restructure database, type of restructure depends on KM option passed in*/
cubeOutline.restructureCube(retRestructureOption(restructureDatabase));
cubeOutline.close();
/* catch all errors and pass them back to ODI, these will cause a fatal error in ODI and stop the interface*/
}catch(EssException exp){
System.out.println(exp.getMessage());
throw new EssException(exp.getMessage());
}
catch(SQLException sqlExcep){
System.out.println(sqlExcep.getMessage());
throw new EssException(sqlExcep.getMessage());
}
catch(IOException ioExcep){
System.out.println(ioExcep.getMessage());
throw new EssException(ioExcep.getMessage());
}
finally{
/* clean up the connection to essbase */
cleanUp(ess,olapSvr);
if (logger != null){
logger.removeAllAppenders();
}
}
}
/* method to set the database restructuring option required*/
private IEssCube.EEssRestructureOption retRestructureOption(String resOpt){
IEssCube.EEssRestructureOption essResOpt = IEssCube.EEssRestructureOption.KEEP_ALL_DATA;;
if (resOpt.equalsIgnoreCase("KEEP_LEVEL0_DATA"))
essResOpt = IEssCube.EEssRestructureOption.KEEP_ONLY_LEVEL0_DATA;
else if (resOpt.equalsIgnoreCase("KEEP_ALL_DATA"))
essResOpt = IEssCube.EEssRestructureOption.KEEP_ALL_DATA;
else if (resOpt.equalsIgnoreCase("DISCARD_ALL_DATA"))
essResOpt = IEssCube.EEssRestructureOption.DISCARD_ALL_DATA;
else if (resOpt.equalsIgnoreCase("KEEP_INPUT_DATA"))
essResOpt = IEssCube.EEssRestructureOption.KEEP_ONLY_INPUT_DATA;
return essResOpt;
}
/* cleans up any open connections to essbase */
private static void cleanUp(IEssbase ess, IEssOlapServer olapSvr) {
try {
if (olapSvr != null && olapSvr.isConnected() == true)
olapSvr.disconnect();
} catch (EssException x) {
System.err.println("Error: " + x.getMessage());
}
try {
if (ess != null && ess.isSignedOn() == true)
ess.signOff();
} catch (EssException x) {
System.err.println("Error: " + x.getMessage());
}
}
}
The only real change from the delete members class is the rename(java.lang.String newMbrName) method is used.
The class was compiled and a jar file created, if you are using ODI 10g then the jar file needs placing in the <odi_home>/oracledi/drivers directory, all ODI components including agents will need restarting to pick up the new file, if on 11g and using a standalone agent then place the file in <odi_home>/oracledi/agent/drivers, to use the local agent in the studio you will need to place the file in %APPDATA%\odi\oracledi\userlib on windows (windows 2008 it is %APPDATA%\Roaming\odi\oracledi\userlib) or USER_HOME/.odi/oracledi/userlib for unix.
The interface to perform the renames is created.
The Datastore associated with the flat file is dragged on to the source and the “REN_MEMBERS” Datastore created earlier is dragged on to the target, there is only one column to map between target and source. The memory engine was used as the staging area as the transformation is simple and the number of records to process is small.
The KM “IKM RENAME ESSBASE MEMBERS” is selected and the options populated.
After executing the interface the Operator can be opened to view if all the steps were successful, as you can see the process followed was a temporary table was created, the records from the flat file were loaded into the temporary table, the RENAME_MEMBERS step was executed and then the temporary table dropped.
Opening the essbase ASOSAMP.Sample outline you can see the members have been renamed.
In the log that was specified by the KM option “LOG_FILE_NAME” the renamed members are logged and the member that was not found in the outline.
If in the source a member to rename to already exists in the outline the interface will fail, for example if the interface was executed again with the following information in the source
The interface will fail, as the member “LCD HDTV” exists in the outline and it will not validate.
If you didn’t want the interface to fail due to duplicate members then the Java code could be updated to check whether the rename member name already exists in the outline, one way could be to use the findMembers(java.lang.String[] mbrNames) method and pass the original member and member to be renamed into it, if the rename member name already exists then the member rename would not be executed and the information written to the log.
To rename members from a different essbase database the Model could be easily updated to point to a different logical schema or the model copied, renamed and then pointed to a different logical schema, once again this makes it a useful and re-usable solution.
