Hyperion & stuff

Stuff about Hyperion and stuff

Deploying WAR to OBIEE WebLogic (for OBIEE drillthrough)

This time the requirement is to drill from any level in the hierarchy to transactions (a bit too much, right?).  It’s a bit difficult to use OBIEE federation to do this which means a custom solution is required.  Anyhow, I found an excellent post about this here.

So I’ve decided to follow the steps and write my own JSP page to manage the HTTP post request.  Most of the steps worked like a charm except for the JSP deployment step since the WebLogic version I’m on requires a WAR file.  To build a WAR file, a Java compiler (javac) is required and I was too lazy to install one.  Luckily, WebLogic works with a directory structure to build a WAR file.

Follow these easy steps:

Create web application folder structure

  1. Create a folder structure for the web application.  In the below example, I’ve named my application and JSP page “Drillthrough”
    • E:\
      • DrillthroughApp (folder)
        • Drillthrough (folder)
          • WEB-INF (folder)
            • web.xml
          • Drillthrough.jsp
  2. An example web.xml is as follows

Deploy to Web Logic

  1. Login to OBIEE console (http://server_name:7001/console) as weblogic user
  2. Under “Domain Structure”, select “Deployments” and click “Lock and Edit” to prepare for deployment.
  3. Under “Summary of Deployments”, click “Install”
  4. Navigate to or type “E:\\DrillthroughApp” on the “Path” field.  The “Drillthrough (open directory)” object will now show up.  Select “Drillthrough (open directory)” and click Next
  5. Select the “Install this deployment as an application” option and click Next.
  6. Select “Admin Server” as the target and click Next
  7. Accept the default options and click Finish.
  8. Once the application is deployed, click “Release Configuration”
  9. Now start the Drillthrough application. Under Deployments, find the “Drillthrough” application and select it.  Click “Start” and select “Servicing all requests”.
  10. Now have fun with drilling through to transactions

Passing dashboard filters/parameters in OBIEE

One of the common things to do in OBIEE is to jump between dashboards with all the filters preserved in the both places.  Assuming that there are conformed dimensions between the two dashboards, the easiest way to do this is by using the “Navigate to Web Page” function in a column “Action Link”.  Here are the high level steps:

  • Get the URL from the target dashboard – select “Page Options” from top right corner and select the “Create Prompted Link” option1
  • Once selected, the “Prompted Link” will be shown on the URL address bar and the following message will show up:”A Prompted link capturing the prompts and values of this pages has been created.  It is shown in the browser’s Address Bar”.
  • This”Prompted Link” URL will have the necessary variables required by OBIEE to pass the parameters between dashboards.  Now copy the prompted link and save it somewhere (such as a text file, etc)
  • Create an “Action Link” in the source dashboard.  This can be done by editing the Analysis in the Criteria section
  • Select one of the column (typically a measure) and select “Column Properties”
  • Now switch to the “Interaction” tab and add a new Action Link by clicking +.  Select “Navigate to a Web Page”
  • This will open another option to specify the URL.  Paste the “Prompted Link” URL and select “Define Parameters” to populate parameters to be passed with the values from the source dashboard


  • Now all the required parameters will be populated.  Modify the ones with “val”, i.e. val1, val2, val3, etc, and populated them with the values from the source dashboard by selecting the “Column Value” option.  This will bring an available columns to be selected.  Typically, all the other columns can be left as default since they dictate all the required column names and such


  • Repeat the above step to set all the required parameters (val) and click OK to save
  • Optionally this “Action Link” can be set as a conditional link, i.e. the link will only be visible if certain conditions are met.  To do this, simply select the “Conditionally” option and add a filter.  The filters are basically all the columns available in  the Analysis.  Click OK to save.


  • Now the “Action Link” is ready and can be access from the source dashboard.  Pivot/table cell will turn blue is an interaction is defined.  Right click and and select the appropriate “Action Link”



OBIEE Authentication with Multiple Providers (MSAD + Default)

The challenge of the week was to bring in MSAD users in addition to the existing native OBIEE users.  The process is actually pretty simple, but it took me a while to figure out.  I found most of the steps in the admin guide which I simplified.  The OBIEE version is

The high level steps are as follows:

  • Add the MSAD provider to Console
  • Modify the Default Authenticator to work with multiple authentication providers
  • Add a custom property to the Identity Store Configuration in EM
  • Restart both Admin Server and BI Server

Add the MSAD Provider

  • From the Admin Console (http://servername:7001/console), select Security Realms->Providers tab
  • Lock & Edit before making any changes and click New.
  • Name: unique name of the MSAD provider.
  • Type: ActiveDirectoryAuthenticator
  • Once the provider is created, click on the provider to make Provider Specific configuration.
  • Important: Under the Common tab, set Control Flag to SUFFICIENT.  This will allow the usage of other configured authentication providers in case authentication fails.
  • Switch to Provider Specific tab to specify MSAD configuration
  • Host: AD server name or IP address
  • Port: the default is 389 for MSAD
  • Principal: AD user with admin/read access to users and groups information.  An example is CN=admin,OU=Service Accounts,DN=Company,DN=com
  • Credential: password for the Principal user specified above.
  • User Base DN: typically OU where all users exist.  An example is OU=Users,DN=Company,DN=com
  • All Users Filter: (&(sAMAccountName=*)(objectclass=user))
  • User From Name Filter: (&(sAMAccountName=%u)(objectclass=user))
  • User Name Attribute: sAMAccountName
  • Group Base DN: typically OU where all groups exist.  An example is OU=Groups,DN=Company,DN=com
  • All Groups Filter: (&(sAMAccountName=*)(objectclass=group))
  • Group From Name Filter: (&(sAMAccountName=%g)(objectclass=group))
  • Save and select Activate Changes.

Modify the Default Authenticator

  • Select Security Realms->my realm->Providers tab.
  • The new provider created in the above steps will show up at the bottom.  Do not change the order.
  • Select the Default Authenticator and select Lock & Edit to modify.
  • Change Control Flag to SUFFICIENT.
  • Save and select Activate Changes.

Add Custom Property to Identity Store Configuration

  • From Enterprise Manager (http://servername:7001/em)
  • Expand Web Logic Domain, right click on bifoundation_domain and select Security->Security Provider Configuration from the menu.
  • Under Identity Store Provider, select Configure.
  • Click Add to add a new custom property
  • Property Name: virtualize
  • Value: true
  • The above property will enable multiple authentication providers.
  • Save all changes.

Once all the changes have been made, restart both Admin Server and BI Server.  The good thing about this is OBIEE assign these external users the “authenticated-role” which belongs to the BI Consumer role.  Thus, external users can login to OBIEE by default.
Hope this helps.

JExtract update

Finally. I’ve been able to spend more time on JExtract. Fixed the underlying problem with handling shared members by using member number as unique ID instead of member name. Looking forward to post the Alpha version on Source Forge soon. Any volunteers to test this thing?

An Exception occurred during Application deployment.: Unable to load the following resource: %RESOURCE_NAME%

EPMA never ceases to amaze me.

The other day, I was getting this error when trying to deploy a Planning app

Detail : An Exception occurred during Application deployment.: Unable to load the following resource: %RESOURCE_NAME%com.hyperion.planning.olap.HspVerifyOutlineException: Verify Outline failed with the following errors:
Error [1200497] detected in member formula for member “ABCDEFG”.

After a few hours of troubleshooting, I noticed that this member “ABCDEFG” is enabled on both Plan1 and Workforce plan types but has HSP_NOLINK attached to it.  Since Plan1 and Workforce have different dimensionality, the member formula returned with an error on the other plan type.  The weird thing is deployment was working earlier that day.

After tracing back my steps, I remember that I reordered some alternate hierarchies in which the shared members were found before the primaries.  This didn’t cause any error, only warning messages when deploying.  So I tried changing the order back and redeployed.  Guess what?  It worked.  Apparently, this warning stopped EPMA / Planning from erroring out on the member formula.

Apparently in EPMA two wrongs make is right.

Dimension does not contain any member or hierarchy items

When using interface tables to update EPMA (version dimensions, I sometimes got the “Dimension does not contain any member or hierarchy items” warning.  A quick way to remedy this issue is to recreate the interface tables using the EPM System Configurator.

Select Hyperion Foundation -> Performance Management Architect -> Interface Datasource Configuration -> Edit an Existing Datasource Link.  Make sure Create Interface Tables is checked to recreate all the tables.  Config tool will give a warning that it will wipe out all the interface tables.  This method has worked for me 2 out of 2 times.  Give it a try.

Update: Turns out leaving the Load ID empty when using the UI also fixed the issue.

Moving members in EPMA using interface tables

Another undocumented feature that I learned today from Oracle support is the ability to move primary members in interface tables.  However, this only works in the latest patch of EPMA (I think).  Without any changes, I don’t think it’s possible to move a primary member already in EPMA when loading using interface table.  What happens is EPMA will add the member in question to a new parent as a SHARED member.  Annoying isn’t it.

To remedy this, simply add a new column to the HS_XXX_HIERARCHY tables called ISPRIMARY.  The value for this column is ‘Y’ when member is a primary member and ‘N’ when member is a shared member.  Quite a nifty feature when using the interface tables and your dimensional hierarchies keep changing.

In 11.1.2, the ISPRIMARY column comes pre-build in the interface tables.  So no need to do the above.

BTW, I’ve only tested this on moving PRIMARY members and not SHARED members.  I don’t know whether it’s even possible to move SHARED members using this method.

Update: In the workaround above only work if the load profile mode is set to “Replace” in EPMA and will NOT work with “Merge”.

OBIEE 10g – Workspace Integration (update)

I finally got the OBIEE – Workspace integration to work on OAS.  Amazing, right?  I still following the same steps from the OBIEE “New Features” doc.  The only differences this time are:

  • Hyperion Workspace, Hyperion Shared Services and OBIEE exist on separate servers.  This is to be our new production environment.
  • When registering EPM Workspace from OBIEE Administration, I copied the reg.properties file from the existing Workspace installation.  So I did “Copy File” instead of “Generate File“.
The last missing piece is the Essbase security pass-through.  Using “:USER” and “:PASSWORD” in the Essbase connection pool in the RPD will not work.  So I had to hardcode these information in the mean time.  The good news (maybe) is there seems to be a patch to address this issue.  Basically, in addition to USER, GROUP and DISPLAYNAME session variables, there will be a new variable called SSO_TOKEN or something.  This new variable can be used in the RPD to enable passthrough.
I’m keeping my fingers crossed on this one since this will save me a great deal of headache.

Oracle WebLogic and Hyperion Shared Services

While looking for a way to integrate HSS, I also explored integrating HSS users with Oracle WebLogic (that came with OBIEE 11g).  Turned out that the integration must be done on the RPD level, but there’s no harm of blogging about it.  Maybe it’ll even be useful someday.  Anyhow, I managed to get BOTH the USERS and GROUPS into WebLogic from HSS.  As in the RPD, the integration needs to be done through the OpenLDAP component.

Here’s how I did it

  1. From the console, http://server:7001/console/, select Security Realms from Home Page.
  2. Select myrealm
  3. Create a new Authentication Provider, specify a Name (anything) and select OpenLDAPAuthenticator as the Type.  Click OK to Save.
  4. Now select the new Authentication Provider.
  5. Select the Provider Specific tab and specify the following
  6. Host: server where HSS is installed.
  7. Port: the default HSS port is 28089 for HSS
  8. Principal: this is the Base DN.  Set it to cn=root,dc=css,dc=hyperion,dc=com
  9. Credential: the default password is security
  10. User Base DN: ou=People,dc=css,dc=hyperion,dc=com
  11. User From Name Filter: (&(cssDisplayNameDefault=%u)(objectclass=cssInetOrgPersonExtend))
  12. User Search Scope: select onelevel
  13. User Name Attribute: cssDisplayNameDefault
  14. User Object Class: cssInetOrgPersonExtend
  15. Group Base DN: ou=Groups,dc=css,dc=hyperion,dc=com
  16. Group From Name Filter: (&(cssDisplayNameDefault=%g)(objectclass=groupOfUniqueNames))
  17. Group Search Scope: select onelevel
  18. Static Group Name Attribute: cssDisplayNameDefault
  19. Static Group Object Class: groupOfUniqueNames
  20. Static Member DN Attribute: uniqueMember
  21. Static Group DNs from Member DN Filter: (&(uniqueMember=%M)(objectclass=groupOfUniqueNames))
  22. Restart everything.  Now go back to myrealm and click Users and Groups.  If done correctly, they should all show up.

Thanks to JExplore (LDAP explorer).  I couldn’t have figured out nothin’ without it.  Peace.

Integration (sort-of) of OBIEE and Hyperion Shared Services

Since we’re doing a major upgrade on the whole Hyperion hardware, I was exploring to upgrade our current OBIEE 10 to 11.  However, I can’t seem to find any docs on how to integrate OBIEE 11g with our Hyperion Shared Services  Apparently only Hyperion 11.1.2.x is supported in this version of OBIEE.  So, I had to find a workaround to get all the HSS users to authenticate with OBIEE.  Since all of our users and groups are native users, it was possible to connect OBIEE directly with HSS OpenLDAP.  I got this solution PARTIALLY working (I’m so happy about it).  I managed to bring in all the HSS users AND authenticate OBIEE with HSS.  The sad thing is there’s a lot more customizations to be done to bring in all the groups.  So, I decided to scrap OBIEE 11 and went back to 10.

Here’s how I did it

  1. From OBIEE Administration, open the RPD, select Manage->Identity from the Menu and add a new LDAP server.
  2. Specify the Name of the server (anything you want).
  3. Specify the Host Name where HSS is installed.
  4. The default port number of HSS is 28089
  5. Base DN is path to tree where users are under.  Base DN is ou=People,dc=css,dc=hyperion,dc=com
  6. Bind DN is how we connect to OpenLDAP.  Bind DN is cn=root,dc=css,dc=hyperion,dc=com
  7. The default Bind password is security
  8. Switch to the General tab and change User Name attribute type to cssDisplayNameDefault.  The Automatically generated text box needs to be unchecked.
  9. Click Test Connection to verify the OpenLDAP connection.
  10. Now create a new initialization variable for the LDAP server.  Select Manage->Variables from menu.
  11. Specify the name of the new Initialization block.
  12. Click Edit Data Source, select LDAP as Data Source Type and select the new LDAP server created above.  Click OK.
  13. Click Edit Data Target and add a new variable called USER.  There will be a warning and stuff, but just continue.
  14. Once created, modify the Mapped Variable to cssDisplayNameDefault.  Click OK to save.
  15. Now we can test the connectivity.  Click Edit Data Source.  Now the Test button should be enabled.  Click it.
  16. Enter any valid HSS user and password.  Click OK.  If everything is good, it should return with Results with Variable = cssDisplayNameDefault and Value = the specified username.  Click Close.
  17. Restart everything and HSS users should authenticate against OBIEE (I hope).