Best practice to extract data from Hyperion Enterprise 5&period;5

We are looking into extracting high-level data from our Hyperion Enterprise 5.5 and am in the process of researching what are the best practices to do that. I am reading the docs for the APIs that I can call from VB6. I am also interested if there are available Java APIs out there.Thanks in advance and Happy Holidays to everyone!Angelito [email protected]

The easiest is using HAL (Hyperion Application Link). I have used HAL to extract data, organizations, account, subs, entities, etc.

If you are sure the entity doesn't exist, trying using the purge entities feature. Go to the Entities module, from its main menu select Task->Unowned Entities and run the Unknown entities report, if the entity that is causing the problem is there, run the purge unowned entities task. Make sure you dont purge other entities that you may need.

Hello and I was wondering what is the preferred and best practice for querying data from an SQL database inside a JSP page. Is it using the JSTL library or another method? Thanks

It depends on the size of the application really.
The "correct and preferred" approach in a large MVC app would be to have a seperate class that does all the database access, retrieving the data into java objects.
Check out [url http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html] DAO pattern
You then "save" the data into request/session attributes, and forward to a jsp page to render the result.
Most approaches recommend a separation between JSP (the view) and SQL code.
The JSTL sql tags are provided more for "quick and dirty" code applicable in small applications, or for fast prototyping. That approach is not really robust for large scale applications.
Cheers,
evnafets

Hello Experts, Can anyone tell me best way to extract data from archived cube. Basically I am trying to pull all the data from archived cube and then load it into another brand new infoprovider which is in different box. Also I need to extract all the master data for all infoobjects. I have two options in my mind: 1) Use open hub destination or 2) Infoprovider>display data>select the fields and download the data. Is it really possible to extract data using option (2) if records are too high and then load it into another infoprovider in new system. Please suggest me the pros and cons for the two options. Thanks for your time in advance.

Hello Reddy,
Thanks a lot for your quick reply.
Actually in my case I am trying to extract archived infocube data and then load it into new infoprovider which is in different system. If I have connectivity I can simply export data source from archived infocube and then reload into new infoprovider.
But there is no connectivity between those two systems (where archived cube is and new infoprovider) and so I am left with the two options I mentioned.
1) Use Open Hub
or
2) Extract data manually from infoprovider into excel.
Can anyone let me know which of the two options is the best and also I doubt on how to use excel in extracting data as excel have limit of no.of records 65536
Thanks
Edited by: saptrain on Mar 12, 2010 6:13 AM

Hi Guru, I want to extract data from hyperion essbase 1 application and then load to another application of hyperion essbase. Can any one know,how to do this. Please,suggest LKM and IKM that should be used for this.

Thank you very much John,
I am using Report script method not your least like method(Calc script as you mentioned in your blog)...:)
I don't know how to check logs of essbase.
what is load rule and how to use that?
My ODI version is:10.1.3.6.2
One more think i want to ask you is i don't have hyperion planning as Technology in topology maneger in my ODI.
Though, in my oracledi\lib\scripts\xml directory, i have TECH_HyperionPlanning.xml
So,how to import technology to have this OR I need something else for this....
Edited by: Maulik Vadodariya on 16 Mar, 2011 3:51 PM
Edited by: Maulik Vadodariya on 16 Mar, 2011 4:11 PM
Edited by: Maulik Vadodariya on 16 Mar, 2011 4:14 PM
Edited by: Maulik Vadodariya on 16 Mar, 2011 4:14 PM
Edited by: Maulik Vadodariya on 16 Mar, 2011 4:15 PM

Hi sutirtha,
I have make one column as key column now i am getting this error,
org.apache.bsf.BSFException: exception from Jython:
Traceback (innermost last):
File "<string>", line 1, in ?
com.hyperion.odi.essbase.ODIEssbaseException: The number of columns returned by script [10] is less than the source data columns exposed [12]
at com.hyperion.odi.essbase.ODIEssbaseDataReader.getAppData(Unknown Source)
at com.hyperion.odi.essbase.AbstractEssbaseReader.extract(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java)
at org.python.core.PyMethod.__call__(PyMethod.java)
at org.python.core.PyObject.__call__(PyObject.java)
at org.python.core.PyInstance.invoke(PyInstance.java)
at org.python.pycode._pyx7.f$0(<string>:1)
at org.python.pycode._pyx7.call_function(<string>)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyCode.call(PyCode.java)
at org.python.core.Py.runCode(Py.java)
at org.python.core.Py.exec(Py.java)
at org.python.util.PythonInterpreter.exec(PythonInterpreter.java)
at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
at com.sunopsis.dwg.codeinterpretor.k.a(k.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)
at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
at com.sunopsis.dwg.cmd.e.k(e.java)
at com.sunopsis.dwg.cmd.g.A(g.java)
at com.sunopsis.dwg.cmd.e.run(e.java)
at java.lang.Thread.run(Unknown Source)
Caused by: com.hyperion.odi.essbase.ODIEssbaseException: The number of columns returned by script [10] is less than the source data columns exposed [12]
at com.hyperion.odi.essbase.wrapper.EssbaseReportDataIterator.validateColumns(Unknown Source)
at com.hyperion.odi.essbase.wrapper.EssbaseReportDataIterator.init(Unknown Source)
... 33 more
com.hyperion.odi.essbase.ODIEssbaseException: com.hyperion.odi.essbase.ODIEssbaseException: The number of columns returned by script [10] is less than the source data columns exposed [12]
at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
at com.sunopsis.dwg.codeinterpretor.k.a(k.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)
at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
at com.sunopsis.dwg.cmd.e.k(e.java)
at com.sunopsis.dwg.cmd.g.A(g.java)
at com.sunopsis.dwg.cmd.e.run(e.java)
at java.lang.Thread.run(Unknown Source)

Hello Can someone tell me please how to Extract Data from (Financial Reporting in) Hyperion Planning & Budgeting into Excel Using ODI? Would the steps be the same as Creating an ODI Project and Interface: Exporting a Flat File to a Flat File? If not, why? Best Regards. Edited by: Saturday on May 26, 2012 9:55 PM

Where do you think the financial reports get their data from? Financial Reports pull data from data sources such as Essbase, HFM.
Cheers
John
http://john-goodwin.blogspot.com/

We are having a healthy debate with our EDW team about extracting data from SAP. They want to go directly against ECC tables using Informatica and my SAP team is saying this is not a best practice and could potentially be a performance drain. We are recommending going against BW at the ODS level. Does anyone have any recommendations or thoughts on this?

Hi,
As you asked for Best Practice, here it is in SAP landscape.
1. Full Load or Delta Load data from SAP ECC to SAP BI (BW): SAP BI understand the data element structure of SAP ECC, and delta mechanism is the continous process of data load from a SAP ECC (transaction system) to BI (Analytic System).
2. You can store transaction data in DSOs (granular level), and in InfoCubes (at a summrized level) within SAP BI. You can have master data from SAP ECC coming into SAP BI separately.
3. Within SAP BI, you SHOULD use OpenHub service to provide SAP BI data to other external system. You must not connect external extractor to fetch data from DSO and InfoCube to target system. OpenHub service is the tool that faciliate data feeding to external system. You can have Informatica to take data from OpenHubs of SAP BI.
Hope I explain to best of your satisfaction.
Thanks,
S

Dear Guru, I have a client that have a data in hyperion and they want to extract those data into SAP BW. Can you tell me how to connect those two system? is it possible to use DB connect? Thanks in advance for the answer. Tienus

I have a MySQL db that contains information about whether respondents are coming to an airshow, what days they plan to attend and how will they travel (www.hollisterairshow.com/helpusplan.php . I can export data from this to a spreadsheet as the summary info will only be seen by the organizers but ideally I'd like to create a page that will summarize responses. Something like this: Saturday Autos: xxxxx Occupants: yyyyyy Aircraft: xxxxx Occupants: yyyyyy Other: xxxxx Occupants: yyyyyy Sunday Autos: xxxxx Occupants: yyyyy Aircraft: xxxxx Occupants: yyyyy Other: xxxxx Occupants: yyyyy Camping: xxxxx Occupants: yyyyy How did you hear about the Airshow: Radio: xxx TV: xxx Newspaper: xxx Magazine: xxx Internet: xxx Other: xxx I thought I would create a couple of recordsets, one for respondents who will attend on Saturday and the other for respondents who will attend on Sunday then all I'd need to do is count the number of respondents and sum the number of occupants for each recordset, and count/ sum campers for the whole file and similarly for the "how did you hear about it" question. Is this the best way or am I missing a more elegant solution? Thanks, Tony

The page I'm trying to create is here www.hollisterairshow.com/plan-results.php and the questionnaire is here www.hollisterairshow.com/helpusplan.php .
I went ahead and created recordsets using advanced recordsets and these seem to be working, here's a sample SQL
SELECT *
FROM plan
WHERE Saturday = 'Saturday' AND plan.howArriving = 'Auto'
I created a recordset for each combination of day and transportation mode - six in all
This selected the correct records.
To display the count of how many records selected I then used in DW Insert/Data Objects/ Display Record Count/Total Records which generated the following code:
<?php echo $totalRows_rsSatAuto ?>
This worked OK - not elegant but OK and I repeated it for each combination of Day and Transportation mode!!
So there are two problems I'm having:
I know I can select the records I want but how do I display the sum of the column containing "How many in your party" - i.e. in the PHP code generated above what do I replace $totalRows_rsSatAuto with?
There are two questions using radio buttons where I'd like to display the count and % for each option selected. I can select all records for these questions but what code do I put to sum the number of selections for each option and what code do I put to calculate the percentage
I understand the arithmetic involved which is pretty simple but it's the mechanics of coding that has me stumped. It would really help if you could give me a sample for say the "Are you camping overnight" question which has a "Yes/No" option only. In my mundane way I would create two recordsets, one for "Yes" and one for "No", and use the same technique I describe above for counting Total Records in each recordset, but then I'm stuck with how do I calculate and display the percentages. I must be missing something very obvious as I'm sure there just has to be a more elegant solution.
Thanks so much for your continued interest.
Tony

Hi I am wanting to find a way of generating my annual tax returns from my financial records, which will be stored in a separate Numbers file for each Calendar Year. As the Tax Year (in the UK) is different from the Calendar Year (in the UK it runs from April 6 to April 5), I would need to extract information from two different Numbers files, to a third Numbers file. Is it possible to do this, and would this be relatively straightforward? I'd be interested to hear from others who have used Numbers for a similar purpose, and how they have organised things. Thanks Nick

The easiest soluce is to forget the use of separate documents.
Use one sheet per year and it will be really easy to achieve your goal using only formulas.
With separate documents, the unique soluce is to use an AppleScript.
I may write one but I'm a bit tired because I posted a lot of such soluce and in most cases, I don't know if they give satisfaction to the askers.
Coding for the clouds isn't my cup of tea so I think that for some days, I will leave AppleScript and will work upon the archives of my workshop.
Yvan KOENIG (VALLAURIS, France) mercredi 4 août 2010 19:15:58

Dear all, we have 2 instance on linux servers vrsion=5 1 instance 11.5.9 2 instance 12.1 we want to take 11i data to 12.1 kindly suggest me the best method to achieve it with all validations and everything kidnly update thank you, journey

Hi hussain,
we want to take whole data exist in 11i to the traget 12.1 there are many techincla change in them like some table name is change from 11 to 12 so wht whould you sugggest kindly help me.

Client uses a ticketing tool,I donot know what is the database for the tool,but I would like to know the best way to extract data from the tool to pex so that we can eliminate working on Excel reports.These excel reports are basically the open request log,resolved tickets,aging tickets etc,currently we work on many excel reports,can we achieve this in Apex ,if so how do we achieve this.Then we would like to use this report and prepare multiple charts,how the above two requirements can be mapped.

A very important function for the system will be - one sends an email in certain format to a dummy email address, then some data will be extracted from the email based on the pre-defined format and inserted into the database my APEX application is using.
Any idea on how I can make it happen please? I agree that this is not really an Apex question, but a more general PL/SQL question.
There are many approaches, all boiling down to one of these two:
1) Push: Some process in the mail server sends/forwards information to your database when new mail arrives.
The language/tools used to do this, and the way it would connect to your database, depends on your environment (what is your operating system? mail server? existing middleware/tools? security protocols used? etc.).
2) Pull: Some process in the database contacts the mail server and polls for new information.
Ie. some PL/SQL code would communicate with the mail server. Again, it depends on your environment (what is your operating system? mail server? existing middleware/tools? security protocols used? etc.).
For example, if you are using Exchange 2007 or newer, it has a web services API:
http://msdn.microsoft.com/en-us/library/dd877045.aspx
The challenge here will be to build the correct SOAP requests from PL/SQL, and to handle the security protocols used.
- Morten
http://ora-00001.blogspot.com

I am currently transitioning from PHP to ADF. I'm looking for the best practice for linking data from multiple entity objects. Example: EO 'REQUESTS' has fields: req_id, name, dt, his_stat_id, her_stat_id EO 'STATUSES' has fields: stat_id, short_txt_descr 'REQUESTS' is linked to EO 'STATUSES' on: STATUSES.stat_id = REQUESTS.his_status_id 'REQUESTS' is also linked to EO 'STATUSES' on: STATUSES.stat_id = REQUESTS.her_status_id REQUESTS.his_status_id is independent of REQUESTS.her_status_id When I create a VO for REQUESTS, I want to display: REQUESTS.name, REQUESTS.dt, STATUSES.short_txt_descr (for his_stat_id), STATUS.short_txt_descr (for her_stat_id) What is the best practice for accomplishing this? It appears I could do it a few different ways: 1. Create the REQUESTS VO with a LOV for his_stat_id and her_stat_id 2. Create the REQUESTS VO with the join to STATUSES performed within the query for the VO. This would require joining on the STATUSES EO twice (his_stat_id, her_stat_id) 3. I just started reading about View Links - would that somehow do what I'm looking for? I also need to be able to update his_status_id and her_status_id through the by selecting a STATUSES.short_txt_descr from a dropdown. Any suggestions on how to approach such a stupidly simple task? Using jDeveloper 11.1.2.2.0 if that makes a difference in the solution. Thanks ahead of time, CJ

CJ,
I vote for solution 1 as it's just your use case. As you said you what to update the his_status_id and her_status_id through the by selecting a STATUSES.short_txt_descr by a drop down. This is exactly the LOV solution.
ViewLinks are used fro master detail navigation (which you don't do here) and Joining the data make it difficult to update (and you still need a LOV for the drop down box.
Timo

Hi, Is there any way besides SQL COMMAND FROM VARIABLE that I could use to extract data from Oracle \ SQL Server tables that filters the amount of data? For instance, I want to extract in all sources data beyond 2007 so I wanted to use this year as a parameter in all OLEDB sources I will be using. Thank you

Hi Pedro,
Based on my research, the Microsoft OLE DB provider for Oracle and Oracle Provider for OLEDB does not allow parameterized queries in OLE DB Source. So the best method to achieve your requirement is still using SQL COMMAND FROM VARIABLE option as Data access
mode.
Considering construct your query in a variable which you can include your parameter dynamically with another variable and set the variable properties "EvaluateAsExpression" to true. In the Expression Property set the value to:
"select * from test
where Col1='" + (DT_WSTR,100) @[User::para] +"'"
The following two similar threads are for your references:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/97f23ef5-35ac-466e-87d9-69edac2525f9/ssis-oledb-source-problem
https://social.msdn.microsoft.com/forums/sqlserver/en-US/32a70752-adab-4180-8b6d-f6100b3e356c/pass-variable-for-ole-db-source-with-connection-to-oracle
Thanks,
Katherine Xiong
Katherine Xiong
TechNet Community Support

Maybe you are looking for

I am getting a new ipod soon, and I downloaded Itunes 6, when I open it and download everything and open Itunes 6, I get the "Itunes has encountered a problem and needs to close. We are sorry for the inconvenience. Bla bla bla. I have uninstalled and

Any time that my mouse goes over a link on a web page a little box in the bottom left corner of the browser pops up showing the web address for that link and it is really annoying, especially when i am on facebook because there are so many links on t

I am trying to send an IDOC to a Non ABAP system. The receiving system consists of an "IDOC Listener" which receives and sends. The RFC Destination for the connection between the PI and that system is type T (TCP/IP). When checking it in SM59 or IDX1