There are quite a few options available when it comes to choice of an ETL tool for Oracle Hyperion Essbase and Planning data load. In this blog, I will discuss how you can select EPM workspace for the same. This is an excellent platform to use to launch jobs that has batch / shell scripts wrapped over MaxL or SQL. But Wait! Why should I use workspace ? Why not just running batch scripts or shell scripts in the server? Because in workspace you will have audit trail with ability to restrict access for users. Also one can schedule job, check run history, debug logs etc.(yeah yeah...we can do that in server too...) Finally, better user experience!
To set it up in workspace, first you need to make sure MaxL, SQL , batch files are running properly in workspace server. i.e. path related to ARBORPATH, ESSBASEPATH etc set in the server and Essbase is installed.
1. Create a generic Application or job launcher by Navigate -> Administer->Report and Analysis ->Generic job Applications. Then click '+' sign to create a new job. Lets call it Run_CMD as it will run our batch job. Product host is your workspace server. Have the command template set up like below. To read more about the command template setup click the help button in the popup window. Lastly provide the full path to your cmd.exe in windows as executable. click ok.
To set it up in workspace, first you need to make sure MaxL, SQL , batch files are running properly in workspace server. i.e. path related to ARBORPATH, ESSBASEPATH etc set in the server and Essbase is installed.
1. Create a generic Application or job launcher by Navigate -> Administer->Report and Analysis ->Generic job Applications. Then click '+' sign to create a new job. Lets call it Run_CMD as it will run our batch job. Product host is your workspace server. Have the command template set up like below. To read more about the command template setup click the help button in the popup window. Lastly provide the full path to your cmd.exe in windows as executable. click ok.
2. Now we need to provide the batch job information that we want to run with this generic job launcher. To make it more interesting, lets consider our batch job uses both Maxl and SQL in it. Which will be supplied to the script as an input file. To do this we can put our .sql and .mxl files in the server and use that path in the batch script. But by doing that we will not be able to check those files from workspace. To make it more debug friendly we will import these files.
Lets see the next steps....
3. Navigate ->Explore. Create a folder where you want to store these jobs. My folder structure looks like this ...I redirect all my outputs under the log folder.
4. Now you have to import your batch file. I had batch file called MyBatchFile.bat that looks like ...
Note: that I do not have any path specified for files InputSQL.sql and InputMaxL.mxl. We will import them into workspace.
5. Select File->Import->File as Job
Check Import as Generic Job and click next
Select Run_CMD from drop down list and click next
Click 'Go'
Add InputSQL.sql and InputMaxL.mxl files from your PC and click ok.
At this point you may get an error if you don't have mime type set up for .sql and .mxl file type. To do that go to Navigate -> Administer->Report and Analysis -MIME Types. Click go at the bottom of the page and add .sql and .mxl type
Finally run the job by double clicking on it or by using Run job option. Use Logs folder as your output directory.
No comments:
Post a Comment