ODI Series – Executing Hyperion Business Rules

At a conference I was attending a few weeks back a question was asked – “Is it possible to run business rules through ODI?”.
Now it is certainly true that none of the adaptors have the functionality to run business rules but I thought I would go through a couple of ways that it is possible to execute them.

This is going to be based around classic business rules and not calc manager ones. I always thought the available functionality outside EAS has been pretty poor for business rules, you have the choice of the not so great web launcher or the command line launcher, and there has never been an API that I feel would have been a lot more useful.

Anyway in your business processes you may have business rules that are run a scheduled basis and you want to integrate them with your current ODI processes.

The first option is to use the OS functionality in ODI procedures, I am going to use the command line launcher in version 11 which is pretty much the same as previous versions except you have to use an encrypted password file. I am also going to be using a windows O/S but the example could easily be modified to fit a different environment.

The documentation for the command line launcher is completely out of date but the usage should be :-

CmdLineLauncher [-p:] -Sservername -Uusername [-rBusiness Rule Name | -sSequence Name] [-fRun Time Prompts file]

The important parameter to watch out for is –fRun Time Prompts file as even if you are not using run time prompts you still need to provide information on which database you want the rule to be run against.
The run time prompts file can be generated from within EAS by right clicking on a rule and choosing “Automate Launch Variables” and this will generate a file.



The ExecDB parameter is always required, all parameters are paired together with ::
You can manually edit the file to add in more run time prompts if required.

If you are using version 11 then you will have to create an encrypted password file first, there is a utility (PasswordEncryption.bat) that is in the same directory as the command line utility (\Hyperion\products\Essbase\eas\console\bin)

So an example to run a business using the command line launcher would be



Ok, so we need to transfer this example to ODI. In the designer you will need to create a new procedure, add a new command and set it against “Operating System” technology.



To use the command shell it is good practice to start with cmd /C this means the command will be terminated after execution



If you execute the procedure as it is then it will fail.



Not the most useful error code but what you need to realise is that the command is not being executed in the directory of the utility, if you have a look at the command line launcher batch script you will see one of the reasons why it is failing



The script uses a relative path so none of the java files will be added to the classpath, I created an environment variable to point to the EAS directory and updated the batch script to use it.



It is up to you whether you want to use an environment variable or just set one up in the batch script.



You also need to be aware that when calling the command line launcher from ODI you need to specify the location of the password file and run time prompts file otherwise it will not locate the files.

Once again, this can be done by hard coding the path or using environment variables.



Another option would be use ODI variables and they could be set a run time if required. (If you want to know more about ODI variables have a read of this previous blog)



You could set a variable up for each parameter of the command line call.



This way the variables could be set when you run the integration or they could be constants or set by retrieving the information from tables or files, there are many different options to choose from.

This time when you execute the procedure it should through without any problems.


So that is one method which does what it is supposed to but is a little limited, the next option would to be use a little piece of Java to call the launcher, now unfortunately there is no HBR launcher API but that is not going to stop me and I will show you how it can be done.

Before you start you will need to use a number jar files in your classpath, most of them are displayed in the batch file but I found that it required an extra jar file to get it working (xalan.jar). I just copied them all to another directory.



You will also need the HBRCLient.propeties file as the java code looks for this file by default.



Basically you just need to pass in the server name, username, password, rule name, the RTP file location and the location of the HBRClient.properties file, this is set as a system property (HBR_HOME)

The main method is not required this is just to test the java code was functioning; it can be removed after testing.

In using the java example you don’t need to provide the location of the password file you can pass it straight into the method, unfortunately you still need to provide the location of the run time prompts file. I did have a look whether you could get round this and it is possible but you would have to change the core java code and I didn’t want to head in that direction.

If you are interested then in the LaunchManager class there is HashTable that stores the run time prompt properties.



If this is changed from private to public you could probably push the RTP values into it, as it is currently private you can’t access it from outside of the class.

Once you have the code working and executing the rules then it can be compiled and put into a jar file.

To be able to use this java in ODI you need to let ODI know where all the jar files are, there are few ways of doing this, you can just copy all the files in the oracledi\drivers directory, make sure you restart any ODI GUI and agents.

Another way would be to update the classpath in odiparams file in the \oracledi\bin directory.



This will dynamically add all the jar files to the classpath from the directory you specify, in my case E:\Java\HBRlauncer

If you are running an agent as service then you will have to update a configuration file that is called when the service is started, it is snpsagent.conf and is located in \oracledi\tools\wrapper\conf



I just added a new line with location to the jar files.
One thing to watch out for with the above methods is not to include xercesImpl.jar as it causes conflicts with the other classes that ODI uses.



You can just remove the file and the code will still function correctly as it will use a class already referenced by ODI.

With the files in place you can now create a new procedure in ODI to call the java method, the procedure could use “Java BeanShell” or “Jython” technology.

I am going to use Jython the same technology that the Hyperion KMs use.



In the example I just import the class in, set up the variables and pass them into the Java method.

Once again you can set use ODI variables instead of hard coding all the parameters.



If you any of the parameters are incorrect it should raise an error.



If the rule has run successfully and you are running an agent as a service then it will be logged in \OraHome_1\oracledi\bin\agentservice.log



The hbrlaunch.log will also be populated with information about the execution just like it would if it was run from EAS or planning.

The only issue at the moment with this solution is that you have to manually edit the RTP file each time you want to make changes, well as usual there are ways around this and you can let ODI do the work and create the RTP file for you.

You could have the information stored in a db table, a file or an ODI variable and create an interface to generate the file. I am going to show how this can be done using an ODI variable that could be populated at run time.

First you need to create a File Datastore point to the directory and name of the file.



Create one column with a length that will be long enough to hold RTP information.



Now create a variable of text type to hold the RTP information



Create a new interface, set the staging area (I am using SQL Server as the staging for this example) and drag the file Datastore that was just created to the target.



Next in the mapping I added the reference to ODI variable and used a replace function to replace spaces, this is because with a ODI text variable the lines are separated by a space, I replaced the space with a line break char(10).

This means it should write a new line for each line of the text variable.



I know your asking, but what if the value element of your variable has spaces in it, well if that is the case you could add something to distinguish each separate line for example.



Then use the replace as

replace('#HBR_Variable','@NEWLINE@ ',char(10))

In the flow section of the interface the IKM to use is “IKM SQL to File Append”, though it will require some changes as if you try and run the interface it will generate an error in its current state.



This is because the IKM is based on the interface having a source and we are not using a source so the SQL generated is incorrect.



As you can see there is no table name, to get around this, duplicate the “IKM SQL to File Append” and give it a new name



In the details tab, you can delete the Command “Insert column headers” as it is not required and then edit the Command “Insert new rows”

In the source tab you view the code that is generating the SQL when the IKM is used



As you can see it has from and then an ODI command to retrieve the table name, this can be updated, if you are using SQL server as the Staging Area then you can remove everything from just before from.



If you are going to be using Oracle as the staging area you would change it to “from dual”



So what this means it will ignore generating any source table name and will just run SQL to display what you have in the target mapping of the interface, in this example it will be the text from the ODI variable.

Once you have saved it you can update the IKM in the flow of the interface to use the newly updated IKM.



I have used TRUNCATE option so the file is recreated each time.



When you execute the interface and check the steps in the operator you can now see that it has generated the correct SQL.



The output file now has all the required information to be used with HBR launcher.

A package can be created to bring it all together.



If you generate a scenario from the package and integrate some of the web functionality I have shown how to create in previous blogs, you will have your own HBR web launcher with the benefit of being able to add other ODI elements into the package.

ODI Series - Quick look at user functions

Today is going to be a quick blog on how to create user functions as it is something I have not covered in the past and they can be quite useful, if you have used user functions then this is probably going to be no use to you and you may as well do something far more interesting instead.

User functions allow you define a customised function that can be used in interfaces or procedures, the function can be used across different technologies. They are useful when you use a manually created function across many interfaces; it gives you the benefit that you don’t have to keep typing all the code into each interface.

They work on the same principle as functions in other applications or programming languages, you pass variables into the function, the variables are used to say calculate a value and in the end a value is passed back from the function to the caller.

A function can be created as global so it is common to all projects or it can be defined at project level.

I am going to go through an example of a user function I had to create recently and go the process of how it was defined, it is nothing complex but I find useful.

Here is the conundrum, I have a standard variable that is a text data value e.g. 01/06/09 (UK date format), and this variable is declared when a scenario is executed. I need to generate a numeric value corresponding to the month of the variable and then use it in a calculation. If the month is April then I need to return 1, if the month is March then I need to return 12. Just to add to that the current month to be used in the function is always the prior month of the month entered in the variable, so if it is May in the variable then I want to use April in the function.

This example is based on using Oracle technology.

Before I created the function I had the following code placed in a mapping column on the target Datastore of the interface



So what it is basically doing is converting ‘01/06/09’ to a date format, it is using the function add_months to get the previous month, it then converts the date back to a string, it then uses the CASE function to convert the month to a numerical value.

As I didn’t want to hard code the date I used a variable to hold the current month.



So it could of ended up like



This is fine but it was a piece of code that I was going to use again and again so the best way to handle this was by using a user function.



In this example I am creating a project based function, it is exactly the same process to create a global variable though it is created by going to the Others tab in the Designer.




The fields are :-

Name: Name of the user function e.g. ReturnMonthVal

Group: Group of the user function. If you type a group name that does not exist, a new group will be created with this group name when the function is saved.

Syntax: Syntax of the user function that will appear in the expression editor; The arguments of the function must be specified in this syntax.

The format of the syntax is

<Function Name>($(Variable),$(Variable2)….)

As I am only passing in one value the format is

ReturnMonthVal($(MonthVar))

It doesn’t have to be called MonthVar it can be named anything that is meaningful.



In the Implementations tab click add, now you can enter the code for the function, so I just pasted the code I had used earlier in the interface mapping, the date had to be updated to the variable name defined in the first Syntax screen.



As this example is using Oracle then it was chosen under the Linked Technologies, if the code was acceptable in its current format for other technologies then they could also be chosen.

If the function was going to be used on different technologies and required different code then this can easily be done by just adding a new implementation, entering the code and applying it to a technology.

To use the function in an interface mapping then it can be achieved with



The objective was to pass a variable to the function so the variable created earlier can be used.



Remember though if you are going to use a variable you will need to declare it say in a package, now the function can be used throughout various integrations without having to rewrite the code each time

So there you have it a quick simple example of how to define and put into practice a user function.

ODI Series – Processing all files in a directory

There have been a number of occasions where it has been necessary to process all files in a directory, the files are all of the same structure but could be for different weeks or months and all need to be processed in one session.

Now there are probably a number of ways going about this and I know it might not be your preferred route (just thought I would say it before I get the usual messages from the purists :) ) but this is a method that I believe is worth sharing and it is one I can look back upon once I forget how it is done.

The object of this example is to pick up all the files in a directory and load them into a database table.



An example of the file structure is as follows



The first step is to create a new Datastore holding the structure of one of the files (remember all files must be of the same structure)



I have pointed the Datastore directly to one of the files at the moment, this will be replaced later by using a variable, as we need to load files with different names.

In this example I am loading into a database table so I have already created the table to hold the data and reversed it.



Ok, so now we need an interface to load the data from the file straight into the db table.



Test it works and populates the table.



So now we can load the data we just have to loop through all the files.
Next is to create a variable that will store the current filename.



And now go back to the file Datastore and place the variable name as the resource name, remember variables in ODI start with #
Remember the variables are case sensitive as well.



So this means when the variable is updated with a filename it will load that file in the interface. Though it is important to know that you won’t just be able to run the interface now as the variable has to be declared and this is usually done through the use of a package.

Lets create the package then, all that is required is to declare the variable and then execute the interface to load the data from the file.



Once that has been applied we also need a scenario generated so we call the package when required, right click the package > “Generate Scenario” and accept the default configuration values.



Next we want generate a list of the files that need to be processed and to do this we can use an O/S command, I am working on a windows O/S so you will need to change the command a little if you are using a different O/S.



What this command does is to list all the files in the specified directory and writes the output to a file in another directory, I have used a different directory to write the list to, as I want to keep it separated from the other files that will be processed.

The extra parameters /b means it will run in bare format mode so there are no heading or summary info.

/a:-d means use an attribute of no directories so no sub directory information will be written to the file.

Running the command produces this file.



To be able run this command in ODI a new procedure was created



In the details tab a new command was inserted using the technology “Operating System” and the command pasted into the command window.



If you don’t want to hard code directory names then it is possible to use variables.
You can test whether the procedure produces the file by executing it.

The next thing to do is created a new Datastore pointing to the file just created; it will just contain one column for the filenames.



Ok, now we want to be able to read in all the filenames, store current filename in the filename variable and then call the package to load the data passing in the value of variable.

To do this we need to add another command step to the procedure that was created earlier.



In the Source what we want to do is retrieve all the filename information from the Datastore we just created, this means File technology was selected and the schema that points to the correct directory location.

In the command area we want to enter the command to retrieve the information, I originally thought I could just use SQL like the following,

SELECT loadfile FROM Filenames

When I tried to execute it I got an error about invalid format description, now I am not totally sure if I went about this correct way but what I did was create a quick interface to load the file information into a db table.



I executed the interface and then checked the operator to find what code was generated to retrieve the data from the file.



As you can see ODI generates extra information between /* */ which all the information about the DataStore but in ODI terminology.

So what I did was lift this information and put it into the procedure command window. I am sure this can be achieved by one of ODIs substitution methods but I am not clear which one could be used, maybe I will get some suggestions?



So this will retrieve all the filenames record by record and what we want to do on the target is call the scenario to load the file and pass in the current value of “loadfile”

To do this we can use one of the ODI tools OdiStarScen



So what is happening here is the Scenario “LOADSUMMARYFILES” is executed and the value passed into the variable currFileName is from the current value held on the source “loadfile”
When using a variable make sure you include the project name as well e.g. DEV.currFileName

So executing the procedure will retrieve a filename and then use that filename in the interface to load the data from the file into the db table, it will move on to the next filename and repeat the process until it has processed all the filenames.



You can easily add the procedure into another package to carry out further processes or make it more sophisticated by checking for when the files arrive and then processes them and deleting/archiving them when complete.

ODI Series – Web Services Part 4

In this last instalment of data services I am going to be looking at the ODI tool ODIInvokeWebService, in the previous blogs I have looked at methods of invoking scenarios through web services, using web services on top of datastores, creating your own apps and today the provided tool that can invoke web services from within the ODI environment.

There are many different reasons that would need to invoke a web service to satisfy the requirements of your integration, keeping tradition in the Hyperion arena I am going use this opportunity to call an EPMA web service. The EPMA web services are not too well documented but they are out there and can be used, if you are using version 9.3.1 there is a document available here.
I am not sure where the documentation is for version 11 but the web services do still exist and fully functional.

If you are going to consume the web services of EPMA with ODI then I recommend making sure you are running an ODI release of 10.1.3.5.0_02 or later as it seems to fix a bug with resolving WSDLs correctly, I hit the bug in the past and couldn't call some web services.

The object of this simple example is to connect to EPMA, retrieve a session id and then create a new folder in EPMA. The first step is to create a package and add the tool ODIInvokeWebService to the diagram.



The tool takes a WSDL URL as one of the parameters, if you are going to use any of the EPMA web services then you will need a session id so the first URL to use is :- http://<epma machine>/hyperion-bpma-server/Sessions.asmx?wsdl

Once you have entered the URL, you can click on the advanced button and connect to the WSDL and that will retrieve all the available operations, the advanced editor lets you enter values for the operation variables then send the soap request and review the results.



The CreateSession operation takes a UserName, Password and connectionString (the connectionString is optional)



Invoking the SOAP request from the editor returns the required SessionID, once applied the parameters “Port Type”, “Operation” and “XML Request” are automatically populated.



There is a parameter “Request File” which allows the use of a file to be as the request instead of using editor.

Now after the session id is retrieved we need a way of storing the value so it can be used with the next call, there are a number parameters that come into play.

“Storage Mode for Response File” that takes the values “NO_FILE” (no response is stored), “NEW_FILE” (A new
response file is generated.If the file already exists, it is overwritten) and “APPEND” (The response is appended to the file. If the file does not exist, it is created)

“File Encoding for Response File” - character encoding for the response file.

“XML Encoding for Response File” - Character encoding that will be indicated in the XML declaration header of the response file.

“Response File” - File storing the web service soap response.



Executing the package produces the following XML file.



So now we have the XML file containing the session id but we need to be able to read in the id, like most technologies there is one available for XML documents and allows the ability of reversing XML files into a set of datastores.

As usual the physical details have to be created in the topology manager.



Only a name is required in the definition.



In the JDBC Url the location of the xml file is entered.

In the physical schema definition any values can be entered for the schema and a logical schema has to be given and applied to context.



The process for reversing the XML file follows the same process as other technologies in the Designer.



Once reversed a separate dataStore is created in the Model for each element of the XML file.



Now the XML has been converted to datastores you can use standard SQL to query the information, as I am only interested in the session id I can query the value simply.

To store the session id I created a variable that is refreshed by using SQL to query the dataStore.



This variable can then be added to the package.



For the final step I want to create a new folder in EPMA, to do this there is a WSDL available for Folder operations.



By using the variable the SessionID value can be populated, without using the session id the soap request will fail, as the request will not be authenticated.



In the parameters of the operation the only other value that needs to be entered is for “Storage Mode for Response File” and this is set to “NO_FILE”

Executing the package will first send a soap request for a session id, the response will write to an xml file, a refreshing variable will pick up the session id from the xml file and store it, the last step uses the Create folder operation and passes in the stored session id and creates a new folder in EPMA.



So there we have a really simple example of how to use the ODIInvokeWebService tool with a bit of XML and EPMA thrown in.

ODI Series – Web Services Part 3

After a little bit of a detour I am going to continue today with the Web Services functionality and concentrate on step-by-step configuring Data Services. Data Services enable to create a web services against existing DataStores The Data Service provides the ability to retrieve, update and delete data on a Data Store by using a Web Service. I am going to assume that you have Axis 2 installed and have followed the first part of this blog on Web Services.

The first step of the configuration is to go into the Topology Manager; by default there will be an Axis 2 technology already installed, a Data Server will need to be inserted to set up the connection information to the axis2 web application.



The name, base URL’s and account details are required, the default account for axis2 is admin/axis2.

The upload URL is the location where ODI will upload an aar (axis2 archive file) with compiled information about the data store.

Once this applied set up a logical schema and apply it to a context and that is all that is required in the Topology Manager.



In the designer you will need to import a SKM (Service Knowledge Module), the SKM you choose depends on the underlying technology your Data Store uses that you are going to generate a Data Service on.



Now select the model that you want to apply a data service to and edit, select the services tab.



In the application server dropdown there should be the name of the logical you created.

The Namespace and Package Name you enter is your choice.

The Name of the data source is important, as it will be used later on in the configuration of the web server; the data source name is used to map JBDC connection details to your data store, when using data services the JDBC information for the data store is not picked up from ODI and has to manually entered into the web applications configuration files.

The name has to start java:/comp/env/<name of data source>

The name of the Data Service is once again entirely your choice; you should end up with something like the following.



If you select the “Deployed datastores” tab you can pick which Data Stores you want to become part of the Data Service, a important prerequisite is that the Data Store has a primary key applied otherwise the generation of the Data Service will fail.



If you have not set up the ODI_JAVA_HOME environment variable to point to a JDK instead of a JRE you will get the following error.



All being well the Data Service will be generated and uploaded to the axis2 web application.

You can check it has been deployed correctly by listing available services in the axis2 web application



With each deployment of a Data Service there are a number of available operations such as add, delete, view Data Store through a Web Service.

Now this where many experience difficulties with Data Services, most rightly so assume that is the configuration complete but as I mentioned the Data Services do not pick up the data source connection details to the repository from ODI, these details need to added to the configuration files in the web application server.

If you try use a Web Service without configuring the data source then you will experience an error like.



The location of the files will depend on the application server you are using, in this example I am using Tomcat and the first file to update is context.xml that is located in the conf directory.



In the file additional information needs to be added between <Context>

The format of the additional information is :-

<Resource name=" " type="javax.sql.DataSource" driverClassName=" " url=" " username=" " password="" maxIdle="2" maxWait="-1" maxActive="4"/>

The resource name relates to the Data Source name that was defined in the Services tab of the model.
The driveClassName is JDBC driver name, this will depend on what technology you are using.
The url is the connection details to the database.

If you are unsure of the details you just need to go back into topology manager and check the Data Server



An example of a completed context.xml is :-



The next file that requires updating is web.xml that is located in the axis2 web application directory.



The information that requires adding can be placed anywhere between <web-app>
It basically information that tells the axis2 web application the name of the datasource to map to the context.xml and is in the format of :-

<resource-ref>
<description> </description>
<res-ref-name> </res-ref-name>
<res-type> </res-type>
<res-auth> </res-auth>
</resource-ref>

So in my example when it is has completed it looked like.


Once the configuration files have been completed then the web application server will require a restart.

To test the Data Services I am going to use soapUI again, if you have not downloaded it I recommend doing so as it is perfect for testing any sort of web service, it is full of functionality and free.

To test the service you will need to point soapUI to the WSDL, you can get the URL from the axis2 service list



Selecting the service will point to the WDSL address.



Entering the WDSL URL into soapUI will retrieve the available operations.



So lets take one of the operation getSampleData as an example.



The soap request takes any of the columns from the DataStore, the primary key column is mandatory even though it says optional.



Running the SOAP request will retrieve data from the Data Store against the defined ID number.



So with the different operations you can have all the functionality of deleting, adding and querying records.

A simple example of how this could be used is say you have users that enter manual adjustments into essbase cube, the current process is that the user sends in the data and this has to be loaded into the cube and then rolled up, this process needs to be automated and controlled.

Well as long as they have http access then a web page can be created.



Once the user has entered the information and submitted, the data could be sent directly into a database table using the data service I have just gone through (using the addSampleData operation), to get an idea on how to write Java code to do this have a look at this blog.



So how about automating the process loading the data into essbase, well if you read back to a previous blog I go through exactly how to do it by using CDC on a Datastore, with CDC any changes to a Datastore can be monitored and when a threshold is met then an interface could be run that loads data from the table into essbase, finally you could use the KM option to run a calc script after the data load to roll up part of the cube.

Now you will have an extremely simple automated adjustment load process.

ODI Series - Loading Smart List data into planning

Last time I went through how to load planning smart list data straight into essbase, it is my preferred method due its flexibility but it is not the official way of doing it. Just to make sure there is an even balance I will go through exactly the same example but use the planning layer and KM to load this information.



So we have exactly the same set of data but the first thing we need to decide is how we are going to load, when loading data through the planning layer you have to decide on a “Data Load Dimension” and a “Driver Dimension” with a list of members.

If you are going to be loading smart list data then the dimension that holds the smart list information has to be the driver dimensions and the smart list members have to be the selected members.

This means the “Driver Dimension” is going to be the account dimension as these contain the members with the smart lists.

The Data Load dimension is limited as it cannot be the Year, Period, Scenario, Version or Currency dimensions, so as I am using the Sample application this leaves the Account, Entity and Segments dimensions, the Account dimension is going to be the Driver Dimension so it can only be the Entity or Segments dimensions. In my example either will do so I have chosen the Segments dimension.

With the decisions been made the next step is to set up the data load information in planning, unfortunately at the moment this is a manual process, one of the reasons this is not my preferred choice because when it comes to automation the last thing you want to do is start having to manual select information.

I know that the outline loader that is part of the planning utilities in Version 11 you can set up the data load information from a flat file, hopefully in the future the ODI KM API will be updated so it can be set through ODI.



This is accessed from Planning > Administration > Data Load Administration.

Select the Data Load Dimension and click Go.



This will give a list of available Driver dimensions from the drop down.



The list of members you are going to load data for with the driver dimension have to be manually selected, luckily in my example I am just going to be loading against two account members, lots of fun if you are loading data for many large number of members.

Once you click “Save” it will update the planning database table with the information, if you are interested it populates table “HSP_DRIVER_MEMBER”, this table is cached by planning so you can’t just update it with information to try and automate the process.

The planning application will have to be reversed again in ODI, this is because there are currently no columns available to load data and driver information.



The reversing will create extra columns in the “Data Load Dimension”, these are “Data Load Cube Name” which relates to the essbase database you are going to load the data into, “Point-of-View” this is used to hold a comma separated list of the remaining dimensions, finally columns for each Driver member so in my example members “MakeDriver” and “Unit Sales”.
Remember if you add any extra driver members and want to load data against them you will have to repeat this process.

The interface can now be created as we have the source and target information ready.



The Segments dimension is directly mapped, as this is the “Data Load Dimension”. The “Data Load Cube Name” is mapped to ‘Consol’ as this is the name of the plan type (essbase database).



The remaining dimensions are put against the “Point-of-View” column, these have to be comma separated, in this example I am using SQL Server as the staging area so I am using the + operator to concatenate, if you are using Oracle you can use || to concatenate. It is also possible to use the Concat function in the expression editor.

As there is only one column for the Data in the source and the target requires being split into a column for each driver member then an expression can be used on each of the driver member columns.



The expression is basically saying when the account member being loaded equals “MakeDriver” then use the value in the Data field.
The same expression was used in the mapping of “Unit Sales” and updating it from “MakeDriver” to “Unit Sales”

Running the interface will populate the essbase database with the correct smart list id value and data value. It is your choice which method you choose, if your data is not going to change much and you are happy with the configuration then this method is perfectly acceptable, if your metadata changes frequently and there are additional smart list members then it will require extra manual changes.

ODI - Question on loading planning Smart List data directly into essbase

I am writing this blog as there is a unresolved question on otn planning forum, it is one of those questions where it is probably easier to go through the steps than try to explain in the forum. It might also be also be useful for anybody else that has the same sort of requirement in the future.

(I would just like to point out it is possible to load smart list data through the planning IKM though this does require updating the data load driver information in planning, you can read about loading data through the planning layer here in a previous blog)

The question relates to loading data directly into essbase where the source data is a mixture of numerical values and smart list names, not something I encounter very often and I wouldn’t thought you would mix that sort of data in a warehouse but sometimes it is best not to question the logic.

Now as you are probably aware that smart lists are loaded into essbase in numerical format, when you create a smart list it displays the ID that will be used, this means the data will need to be transformed from text to numerical when it is loaded into essbase.



I am not sure what the data looks like from the original question but I just going to base it around the planning sample application again. The aim is to populate the above composite form with data that contains a mixture of numerical and smart lists values.



What you need to be aware of is the data column is non-numerical and the destination essbase data column is numerical so there will be a requirement in the integration to change data types.

The first steps are to reverse the data and essbase database, no need to cover that off as I am sure you are very familiar with it by now.

Now we need to be able to map the data with the smart list values and to do this you need to access the planning applications database tables. Smart List information is stored in two tables

HSP_ENUMERATION


This table stores the information for the smart list properties.



HSP_ENUMERATION_ENTRY


This table stores the details of the smart list entries


The column ENUMERATION_ID links the tables; both the tables will need to be reversed unless you always know the enumeration id and in that case you will just need to reverse the HSP_ENUMERATION_ENTRY table.



In this example I am loading directly into essbase but before I do this I usually create a text file or db table to load the data into, this is just because I find it easier to sense check the data that is going to be loaded first. In this case I just created a near duplicate of the source data table with the exception of having a few extra columns that are not in the source table but are in the essbase target database.



The additional columns are HSP_Rates and Currency; I also created the Data field as numeric to mirror that is of the essbase data column, this table was then reversed in ODI.

Time to create the interface, now this can be done a number of different ways. There are three source DataStores we need to use though all three can not just be dragged on to the source, this is because the join that will be generated will not give the required results. I did try to use all 3 tables joined at the source but even with changing the ordered join numbers I couldn’t get the correct SQL generated, it probably can be done but I didn’t have the time to spend on it and want to keep this as simple as possible.

Ok, I will go through one of the ways it can be done, the Data was dragged on to the source and the HSP_ENUMERATION_ENTRY DataStore



A join was created between the SL_DATA DataStore and the HSP_ENUMERATION_ENTRY DataStore.



The join in its current state is not enough because it will only return the records where the data matches against the smart list name, in the source data there is the numerical values we want to return as well.



By selecting “Left Outer Join” the join is updated to return records in the source datastore where there is no match, which is what we want to achieve.

Though this will look at all Smart List Entry records and we only require a subset, this is where the HSP_ENUMERATION DataStore comes into play, as I stated before if you just drag the DataStore on to the source and join HSP_ENUMERATION_ENTRY and HSP_ENUMERATION then the SQL generated when the interface is executed does not give the desired results.

What I did was update the expression in the join between SL_DATA and HSP_ENUMERATION



So now it will only return the ID of the records where the name of the Smart List is defined as ‘Make’.



I also updated the expression to use an ODI substitution method that returns the full table name instead of hard coding it.

<%=odiRef.getObjectName("L", "HSP_ENUMERATION", "PLANSAMPSQLSCHEMA", "D")%>

The statement is saying return the object name for HSP_ENUMERATION in the logical schema PLANSAMPSQLSCHEMA, which will return PLANSAMP.dbo.HSP_ENUMERATION.

The data check table was dragged on to the target.



The two columns that are not mapped I need to put against members “Local” for currency and “HSP_InputValue” this is the member where planning stores its inputs against for currency applications.
If it is a non-currency planning application then you don’t need to worry about these columns.



The Data mapping has to be updated because it has defaulted to retrieving all the records from the source data table and what is required is a mixture, an expression has to be created to achieve this.



This is basically saying when there is no entry id i.e. there is no match between the source data and the smart list data then just return the source data otherwise return the smart list entry id.



I used the “IKM SQL Control Append”, set “DELETE_ALL” to yes as I want to clear out the source table each time I execute the interface and turned off “Flow Control” as I am not trying to control or check the data.



Executing the interface generates the desired results as the numerical data has been loaded and the smart list data has been converted to the correct numerical id.

As the results were correct the interface was updated by dragging the essbase data Datastore on to the target, the staging area had to be updated so it was done on the SQL database side, as essbase does not have the capabilities to transform the data.



If the interface is run as it currently stands it will generate an error.



I mentioned earlier that a conversion of the data type would be required; this is because the source data column is non-numeric and the target column is numeric.



Depending on what technology you are using will depend on how you update the expression, I was using SQL server so used the CAST function; if you are using Oracle then you could use the TO_NUMBER function.



Once the interface has been executed and loaded the data into essbase you can see the desired results are available in the planning form.

In the next blog I am going to go through exactly the same example but load data through the planning layer using "IKM SQL to Planning" then it is up to you which method you decide on using.