The method I am going to through shortly does not require a load rule and uses a combination of a customised knowledge module, jython and the essbase Java API. I originally only wanted to use Java and not Jython but I hit upon an issue with the way ODI translates some of the API calls where quotes around column and table names are generated, thanks goes out to Dev of ODIExperts for trying to assist me with this issue, if a solution is found I will probably provide an update.
First I would like to stress I am not a Java programmer and you may find yourself questioning the code I write, though feel free to contact me if you have a different take on it.
I don’t get as much opportunity as I would like to get involved using Java and the APIs available in the EPM world so I acknowledge I am a bit rusty.
In my example I will be using ODI 11g but it will be exactly the same process if using 10g.
The overall aim is to create a solution that will take a source containing member names and will process the members and delete them from an essbase database, the solution should log any deletions and members not found, it should also have the option to define how to restructure the database.
The members that are to be deleted are from everybody’s favourite BSO database Sample.Basic, the members are “Connecticut” and “New Hampshire”, I randomly selected two members so is no logic to why these are being deleted.
The database is BSO but the solution should work just as well with ASO.
A nonexistent member has been included in the source, the process should log that this member is not found in the outline.
The source is a flat file but just as easily can be an rdbms technology.
Basically the idea is to have an interface with the members to delete as the source and a datastore that is associated with an essbase database as the target; the interface will use a custom IKM that will delete the members from the essbase database.
In ODI a new Datastore was created and reversed for 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 Sample.Basic database.
A new Datastore was added to the model.
One column was manually added to the Datastore, the column is going to be used to map the source member names to.
The Datastore is now complete and can be dragged on to the target of any interface; the Datastore will only be used to retrieve the physical details about the essbase database to delete the members from, if a different database is required the model can be copied and the logical schema updated to point to a different physical database.
The source and target Datastores are now ready to be used but before an interface can be created the custom IKM has to be developed to do the main work of deleting the members.
The IKM will use the same principal of the current essbase knowledge modules, it will use jython to store all the physical information about the essbase database in variables, it will store the values held in the KM options, it will execute a SQL query to return all the source records and store them in a ResultSet, this information will then be passed into a Java class for processing.
If you have ever looked at the essbase KMs you will recognise some of the jython code I am going to use, no point in re-inventing the wheel.
A new IKM was created with Muti-Connections enabled and Target Technology set to “Hyperion Essbase”, this is so the IKM will be available for essbase related targets in interfaces.
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 “DELETE_MEMBERS” was added to the KM, change the technology to Jython.
As described earlier 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 “DeleteMembers”
I will break down the jython code used in the command and briefly go over what it does.
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 delete 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 will be passed into the Java class.
The 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 output generated from the statement is the reason I had to use both jython and Java, when I tried using it in Java there were issues in handling quotes.
You can always check out the description tab in the Operator after running an essbase related interface to see what SQL statement has been generated.
sql= """select C2_NEWMEMBER "ParentName",C1_MEMBERNAME "MemberName" from "C$_0Measures" where (1=1) """
Once the statement has been stored the source JDBC connection used in the interface is stored.
The SQL is executed and a ResultSet is returned, the ResultSet and HashMap are then passed into external Java class “Delete Members”
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 delete it, if the enable log option is set then log the deletions and members not found.
The database should then restructured depending on the value stored in the RESTRUCTURE_DATABASE variable.
I am not going to go through the Java code as I have briefly gone over what the class will do and I have put some comments within the script, if you are unclear about any of it then feel free to contact me but it is pretty simple stuff as I didn’t want to over complicate this example.
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 DeleteMembers {
/* 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 DeleteMembers(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();
/* cycle through records in ResultSet */
while (rs.next()) {
try{
/* find member in current row of the ResultSet */
essMem = cubeOutline.findMember(rs.getString(1));
/* delete member */
essMem.delete();
if(logger != null){
/* if logging enabled write to log to inform of deletion */
logger.info("Member - " + rs.getString(1) + " deleted.");
}
}catch(EssException noMember){
/* exception is caught if no member is found */
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();
}
}
}
private IEssCube.EEssRestructureOption retRestructureOption(String resOpt){
/* method to set the database restructuring option required*/
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;
}
private static void cleanUp(IEssbase ess, IEssOlapServer olapSvr) {
/* cleans up any open connections to essbase */
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 class was compiled and a jar file created, if you are using ODI 10g then the jar file needs placing in the
The interface to perform the deletions is now created.
The Datastore associated with the flat file is dragged on to the source and the “DEL_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.
The KM “IKM DELETE 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 DELETE_MEMBERS step was executed and then the temporary table dropped.
In the code (description in 10g) tab for the DELETE_MEMBERS step the code that has been generated can be viewed.
Opening the essbase Sample.Basic outline you can see the members “Connecticut” and “New Hampshire” have been deleted.
In the log that was specified by the KM option “LOG_FILE_NAME” the deleted members are logged and the member that was not found in the outline.
If there is any exceptions in the execution of the interface such as not being able to communicate with essbase these will be logged directly in the operator and the interface should fail.
To delete members from 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.
If required the Java code can be enhanced to be much more sophisticated and include more functionality, overall I think once this method has been developed it is a valuable and re-usable solution for deleting essbase members.
No comments:
Post a Comment
Note: only a member of this blog may post a comment.