Hyperion & stuff

Stuff about Hyperion and stuff

OBIEE Essbase relational drillthrough

Wow.  I still can’t believe that I did this one.  We had a new requirement to create an HR cube and drill down to employee levels for more details.  I had thought of using Hybrid Analysis in EIS, but that means the cube will be totally dependent on EIS.  So I decided to use the “vertical” federation of OBIEE data model.  The employee level will exist on relational DB while other levels are on Essbase.

I used this Oracle by Example to get an idea on how to accomplish this: Federating Essbase and Relational Data Sources in Oracle Business Intelligence Suite Enterprise Edition Plus. Pretty helpful.

OBIEE – Workspace SSO

I can’t believe how frustrating it is to get OBIEE Workspace SSO to work in OAS installations.  If I didn’t remove the two lines in mod_oc4j.conf, OBIEE won’t even show up under Workspace Applications.  However, I suspect it’s got something to do with that bips thing.  Gotta keep looking into this.

Anyhow, getting this thing to work in Tomcat turned out to be quite easy.  Took me only 1/2  a day to get everything to work.  So the docs was good.

OBIEE 10g – Workspace Integration

Although OBIEE 10g and Workspace 11.1.1.x is now well documented, there are still some items that need to be manually configured.  The place where I work uses Oracle AS 10.1.3.1 (I know, I know) for all the Hyperion applications while J2EE is on the default OC4J container.  After following the steps in the New Feature docs, OBIEE was still not running on OAS port (7777).  So when I tried http://host:7777/analytics, it came back with nothing.  After hours of tinkering and staring at Apache logs, I finally figured out the trick.  Apparently, after configuring Workspace Web Server, there are these two lines that made it to the mod_oc4j.conf

Oc4jMount /analytics bips
Oc4jMount /analytics/* bips

Apache redirects request for /analytics to OAS instance called bips.  This will work fine (I think), if OBIEE is configured to run on OAS.  So what I did was I removed these 2 lines, and added the ProxyPass lines to the httpd.conf

ProxyPass /analytics http://host:9704/analytics
ProxyPassReverse /analytics http://host:9704/analytics

Restarted both HTTP_Server from opmnctl and OBIEE OC4J, and it worked.  Now moving on to SSO.

My new pet project, JExtract

The other day, I’ve decided to learn myself the Essbase Java API.  I figured the best way to do it is to build something from scratch.  Since I need to maintain metadata from several cubes (my day job, sadly), I thought this would be a good utility to create.  So I call my new pet project, JExtract, the Java based Essbase Outline Extractor.  The basic stuff is mostly there such as

  • Connect to Essbase server and application.
  • Disconnect from Essbase (obviously).
  • Store configuration in text file.
  • Query and extract dimension.
  • Build level, generation and parent child output file.

I’ve also created a Source Forge project to host this thing.  Check out http://jextract.sourceforge.net for details.  However, I haven’t uploaded any files just yet.

EPMA wrong deployment host

I had a scare recently with yet another EPMA issue.  After a load balancing exercise on the Hyperion servers by distributing the components, suddenly EPMA deployment for Essbase apps wouldn’t work anymore.  It returned with HTTP Error 500, bla bla bla.  Previously I had moved EAS and APS from the box running EPMA to another box.  When trying to deploy an Essbase app manually, I noticed that the EPMA deployment host was somehow pointing to the server running EAS.  After several tries of restarting EPMA, reconfiguring Web Server and restarting EAS, I finally decided to reconfigured the old EAS and Web Server running on the same box as EPMA.  Guess what, it worked.  The EPMA deployment host was now back to the right server.  I didn’t think that EAS would be related to Essbase deployment, but I guess it is.

ASO memory issue on AIX

Essbase 11.1.1.3

We were puzzled the other day by the spikes in memory usage on our Essbase (AIX) server.  This happened after we deployed an additional historical ASO cube.  It seemed odd since the memory usage (Paging Space) of this particular application kept increasing with each user request.  At point it got up to several hundred MBs.  The first place we looked was the ASO cache limit which was set to 32 MB (default).  After some trial and error, it turned out that all data files were stored on the default location at $HYPERION_HOME/products/EssbaseServer/app/APP/APP.  So I changed this and point the data fil

OBIEE 11 and Essbase

OBIEE 11g, Essbase 11.1.1.3

After installing OBIEE 11g on my Windows XP laptop which runs Essbase 11.1.1.3, I found out that the Oracle BI admin tool was unable to connect to the installed version of Essbase.  After a bit of research, I found out that OBIEE 11g will only run with the included Essbase 11.1.2 version client.  This caused a problem on my machine since all Essbase environment variables on my machines are pointing to version 11.1.1.3.  My workaround for this is by creating a separate batch file to start the Oracle BI admin tool as pasted below.

setlocal

set ARBORPATH=C:\OBIEE\Oracle_BI1\clients\epm\Essbase\EssbaseRTC
set ESSBASEPATH=C:\OBIEE\Oracle_BI1\clients\epm\Essbase\EssbaseRTC

set PATH=c:\OBIEE\Oracle_BI1\bin;c:\OBIEE\Oracle_BI1\opmn\bin;c:\OBIEE\Oracle_BI1\opmn\lib;c:\OBIEE\Oracle_BI1\perl\bin;%ARBORPATH%;%ARBORPATH%\bin

C:\WINDOWS\system32\cmd.exe “/cc:\OBIEE\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\bi-init.cmd coreapplication_obis1 2 && c:\OBIEE\Oracle_BI1\bifoundation\server\bin\AdminTool.exe”

endlocal

Now my Admin Tool can connect to the installed Essbase.

Deployment pending

EPMA 11.1.1.3

In EPMA, there’s this “awesome” feature that forces failed deployment to wait until time out is reached.  Typically after a failed deployment, EPMA changes the application status to “Deployment pending”.  In this state, the application may not be deployed or deleted which is quite annoying (I think).  Turns out there’s a setting to change this time out value.

  • Look in the configuration file here %APP_SERVER_DEPLOYMENT_DIR%\EPMAWebServer\applications\EPMAWebServer\awb\WEB-INF\conf\AWBConfig.properties.  Change the settings called
  • There’s a setting called DEPLOY_PENDING_TIMEOUT_MINUTES.  The default is 480 minutes (or was it 240).  Change this to a small value, like 10, to expire the deployment.
  • Restart EPMAWebServer web application.
  • Log in to Workspace Application Library and check the status.  The status should change to Deployed.
  • Change the DEPLOY_PENDING_TIMEOUT_MINUTES setting back to normal and restart EPMAWebServer.

Target application does not exist

EPMA 11.1.1.3

Recently, I accidentally removed an EPMA-managed Essbase application directly from EAS console.  This resulted in EPMA showing the “Target application does not exist” for the particular application.  I found out there are two (possibly more?) ways to fix this.

The easy (lucky) way is to recreate the Essbase application directly in EAS.  This is the second time this thing happen to me.  The first time around, I recreated the delete application as an empty application.  It didn’t work, so I had to take the hard way.  This time around, I had a backup that I can restore in EAS.  Maybe EPMA had to make sure that the structure is there, so empty application didn’t work.  Once restored, I managed to remove the application from EPMA.

The hard way is to remove the application from the SQL back-end (avoid).  Mind you that this way is not supported by our good, hardworking friends at Oracle support.  Here’s the SQL (tested this once and seem to work just fine).  Use at your own risk !!!
delete from ds_property_application where I_APPLICATION_ID=(select distinct I_APPLICATION_ID from ds_application where c_application_name = ‘APP_NAME’)
select count(*) from ds_property_dimension where I_APPLICATION_ID=(select distinct I_APPLICATION_ID from ds_application where c_application_name = ‘APP_NAME’)
delete from ds_property_dimension where I_APPLICATION_ID=(select distinct I_APPLICATION_ID from ds_application where c_application_name = ‘APP_NAME’)
delete from ds_property_dimension_ref where I_APPLICATION_ID=(select distinct I_APPLICATION_ID from ds_application where c_application_name = ‘APP_NAME’)
delete from DS_PROPERTY_RELATIONSHIP where I_APPLICATION_ID=(select distinct I_APPLICATION_ID from ds_application where c_application_name = ‘APP_NAME’)
delete from ds_property_application_array where I_APPLICATION_ID=(select distinct I_APPLICATION_ID from ds_application where c_application_name = ‘APP_NAME’)
delete from ds_property_application_ref where I_APPLICATION_ID=(select distinct I_APPLICATION_ID from ds_application where c_application_name = ‘APP_NAME’)
delete from ds_application where I_APPLICATION_ID=(select distinct I_APPLICATION_ID from ds_application where c_application_name = ‘APP_NAME’)
delete from ds_view where I_View_ID=(select distinct I_view_id from ds_application where c_application_name = ‘APP_NAME’)
delete from ds_library where I_library_ID in (select distinct I_library_id from ds_application where c_application_name = ‘APP_NAME’)
delete from or_object where c_object_ID=’1_’ || (select distinct I_APPLICATION_ID from ds_application where c_application_name =
‘APP_NAME’)

Typically, I would do a select before deleting anything to make sure everything is OK.