I wanted to write something about the tool that I developed and later decided against it. (I’ll explain about that hopefully soon)
An idea was implanted, (no this is not Inception š ) while I was going through a discussion which was about using OutlineLoad utility. Cameron Lackpour made a comment stating that soon there’ll be a GUI interface for OutlineLoad.
Thank you Cameron š for implanting that idea…
The idea starting growing and I later started developing an interface for OutlineLoad. (My kid and wife was away on vacation and I was getting bored š ) I finished the development by December and later Oracle announced 11.1.2.3 which has a web interface for outline load. š
To give you an idea about what I’m talking here are the screenshots of MnD Loader.
It makes “intelligent selections” (if you select export, all other options expect the ones you can use with an export will be disabled).
Remembers the last used server, application, user, password file.
Remembers the last used server, application, user, password file.
It has an interface to develop metadata files.
Hope that I’ll be able to release this soon to you all š
Enough of what I developed, let’s see what’s new about Outline Load web interface.
Outline Load from Web is a simplified version of the familiar command line version.
Import from file
More simplied version where instead of some odd switches (/M, /N) user can see what he is doing :). Not all the options of Outline Load are included here (it doesn’t have import Smart List and Exchange rates)
It will show onscreen whether the load was successful.
Export Metadata
Just mention the dimension name and it’ll export the dimension. I was hoping that a fix will be added to the issue of multi-lined formula getting split into different lines.
Export Data to File
I was interested in seeing “Export Data to File” option with this. So I went ahead and starting testing it.
Ran the export and it got aborted with the below message š
[Fri May 17 15:47:42 EDT 2013]Unable to obtain dimension information and/or perform a data load: An error occurred while running the specified calc script.
[Fri May 17 15:47:42 EDT 2013]Planning data export operation finished. with exceptions. No data output files were written. Examine log and exception file for more information.0 data export files were written: null
Well, so we are writing a data export calc script using this š Essbase logs revealed that there is some syntax error in the calc script!!!!!
[Fri May 17 15:47:42 2013]Local/SMP_PLN/Consol/admin@Native Directory/47832926071104/Error(1200315)
Error parsing formula for [FIX STATEMENT] (line 3): invalid object type
[Fri May 17 15:47:42 2013]Local/SMP_PLN/Consol/admin@Native Directory/47832926071104/Error(1012001)
Invalid Calc Script syntax [
FIX (“BegBalance”,”Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”,”Working”,”CapExLand”,”CapExBuildings”,”CapExLsholdImprov”,”CapExMfgMach”,”CapExOffFurn”,”CapExCompEquip”,”CapExCompSftwr”,”CapExVehicles”,”NI”,”EBITDA”,”Invent…]
[Fri May 17 15:47:42 2013]Local/SMP_PLN/Consol/admin@Native Directory/47832926071104/Error(1200421)
Error encountered on or after line [4]
[Fri May 17 15:49:26 2013]Local/SMP_PLN///47832943966528/Info(1013210)
User [admin@Native Directory] set active on database [Consol]
[Fri May 17 15:49:26 2013]Local/SMP_PLN///47832927123776/Info(1042059)
Connected from [::ffff:127.0.0.1]
I couldn’t figure out where the script is getting stored. I decided to export a single cell (where I know data existed)
Ta daa, it worked!!!!!
[Fri May 17 15:57:30 2013]Local/SMP_PLN/Consol/admin@Native Directory/47832932387136/Info(1013162)
Received Command [Calculate] from user [admin@Native Directory]
[Fri May 17 15:57:30 2013]Local/SMP_PLN/Consol/admin@Native Directory/47833051826496/Info(1012690)
Data Export Completed. Total blocks: [1]. Elapsed time: [0].
[Fri May 17 15:57:30 2013]Local/SMP_PLN/Consol/admin@Native Directory/47833051826496/Info(1012696)
Total Number of Non-Missing cells exported: [1].
[Fri May 17 15:57:30 2013]Local/SMP_PLN/Consol/admin@Native Directory/47833051826496/Info(1012550)
Total Calc Elapsed Time : [0] seconds
Then I decided to run my earlier export (with level 0 of Accounts and level 0 of Period) and to my surprise it started working and now Essbase log is complaining about exporting a dynamic member.
[Fri May 17 16:00:04 2013]Local/SMP_PLN/Consol/admin@Native Directory/47832935545152/Info(1013162)
Received Command [Calculate] from user [admin@Native Directory]
[Fri May 17 16:00:04 2013]Local/SMP_PLN/Consol/admin@Native Directory/47833051826496/Info(1012691)
DataExport detects Dynamic Calc member [511000] in the range. Exporting Dynamic Calc data may slow down performance.
[Fri May 17 16:00:04 2013]Local/SMP_PLN/Consol/admin@Native Directory/47833051826496/Info(1012698)
This DataExport operation will export data from existing blocks only. Any FIX on sparse dynamic calc members will be ignored. Use DATAEXPORTNONEXISTINGBLOCKS ON option to export data from all potential blocks.
[Fri May 17 16:00:04 2013]Local/SMP_PLN/Consol/admin@Native Directory/47833051826496/Info(1012690)
Data Export Completed. Total blocks: [1]. Elapsed time: [0.01].
[Fri May 17 16:00:04 2013]Local/SMP_PLN/Consol/admin@Native Directory/47833051826496/Info(1012696)
Total Number of Non-Missing cells exported: [2].
[Fri May 17 16:00:04 2013]Local/SMP_PLN/Consol/admin@Native Directory/47833051826496/Info(1012550)
Total Calc Elapsed Time : [0.01] seconds
I think they should’ve added options not to extract dynamic calc members.
Since it started working and I’m excited to figure out how the export gave me the same format which is used to import data using Outline Load!!!! (Outline Load can be used to extract data)
Format from Outline Load data export
Account, Jan, Point-of-View, Data Load Cube Name
Price, 200, “FY12, E01_101_1110, BAS, Local, Forecast, Working”, Consol
Units, 100, “FY12, E01_101_1110, BAS, Local, Forecast, Working”, Consol
I was wondering how dataexport script is producing such a format, did Oracle add something to DATAEXPORT commands where you can mention your own format (that would be cool)
Naaa it was some internal scripting that they to reach the final output. Web export (Outline Load) uses a DATAEXPORT calc script and exports data to a tmp folder.
/oracle/Oracle/Middleware/user_projects/epmsystem1/tmp
/oracle/Oracle/Middleware/user_projects/epmsystem1/tmp
"HSP_Rates","Year","Entity","Segments","Currency","Scenario","Version","Account","Period"
"BegBalance","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"
"HSP_InputValue","FY12","E01_101_1110","BAS","Local","Forecast","Working","Price",,200
"HSP_InputValue","FY12","E01_101_1110","BAS","Local","Forecast","Working","Units",,100
and later gets converted to Outline Load format.
Overall it is a nicer simpler approach.
My takes on the new interface.
I don’t think this will make a bigger difference, however it would have been nice if these options were in there.
Things which will make this more nicer
- If a dimension is selected as Row, then what is the point in showing that also in Column (I know we will select the same in Row and Column, but why show it?)
- Instead of POV option for selecting Page members (multiple members from same dimension).
- Make it work always, I tried a second time before writing this post and the same error of invalid calc script occured.
- You’ve to use “Reset” if you planning to export another set. (Rows and Column member selected gets messed up after first export)
- Multi-lined formulas extract as a single line.
- All options of Oultine Load – load Smart List, Exchange Rates.
HTH
As always an interesting one..keep going…all the best. .
Thanks,
Bakul.
I am not able to extract missing values using this feature. I think it does not allows loading or extracting missing values. Is that correct?
Thanks,
Deep
Deep, that is the feature of DATAEXPORT. So if all rows are missing it'll not export those rows.