When you bring in an Essbase database into the Oracle BI EE 10.1.3.4 and higher semantic model, you typically import it using the "admin" Essbase user ID. This is a standard account that comes with the default installation of Essbase and provides full access to all of the applications and databases in your Essbase server. After the Essbase outline import has completed, if you take a look at the connection pool that's been set up it uses this "admin" account as the Essbase login.
This is all great, and every user that logs into Answers that has access to this data source will connect as the Essbase "admin" user. The problem then comes if you want to make use of Essbase security, to take advantage of filters or to restrict access to Essbase databases based on Essbase users and groups. To do this, you would typically substitute the values :USER and :PASSWORD in the connection pool connection properties section, like this:
This tells the BI Server to substitute the logged in users's User ID and password for these two variables, so that they can be passed through to Essbase. If you then create corresponding user accounts in either the BI Server repository, or in a connected LDAP server, this will then work as long as the user account names and passwords match those used by the Essbase server. If however, you plug the Shared Services directory into the Oracle BI Server, via the Hyperion Custom Authenticator, and try and log into Answers using a Shared Services login that also has access to the Essbase cube, you get this error when accessing the cube.
So what's going on here then? Why is Essbase rejecting our login, especially as it's come in via Shared Services in the first place? Well a clue cam be found when you view the user's account details in the "My Account" page of the dashboard. Notice how "@Native Directory" has been appended to the "admin" user name?
For some reason, the Custom Authenticator used by the BI Server to authenticate against Shared Services is appending the Shared Services directory name to the :USER variable. Although you might think, looking at the above screenshot, that you could use Display Name instead, in most cases this is the first name and last name of the user which again would fail as a valid login for Essbase. So what can we do?
Well Venkat and I were discussing this yesterday evening, and a suggestion that he came up with was to capture the login, as entered by the user into the dashboard login page, save it into another session variable and then use this instead of the :USER variable which is then subsequently "corrupted" by the Hyperion Custom Authenticator. To put this idea in place, I first define a new BI Server session variable, and call it INITUSER, and then I configure the Init Block so that it copies the :USER value into it.
I then configure the Init Block that runs that Hyperion Custom Authenticator to run my new Init Block before it, so that I can capture the value of :USER before It corrupts it.
Finally I configure the Essbase connection pool to use this new session variable for the User ID, using VALUEOF(NQ_SESSION.INITUSER), like this:
Now if I save my repository and then log back in to Answers, I can run my report and access Essbase without any problems, as the BI Server is now connecting via my alternative user ID variable.
There is one final twist to the story though. So far, we've been talking about users logging in to Answers directly through the standard OBIEE dashboard interface, but in reality, they may wish to log into Answers via EPM Workspace, which has single sign-on between the EPM products and OBIEE. The problem here though is that whilst the BI Presentation Server will SSO you into Answers using your Shared Services login, directly from the Workspace UI, I'm told that the SSO process doesn't pass across the user's password into the :PASSWORD session variable used by the BI Server, and therefore this password can't then be used to connect you into Essbase via the connection pool. Using our new session variable. if we try and connect via EPM Workspace into Answers we get this error, which is showing the CSS token as the User ID it's trying to log in as:
whereas if we revert back to :USER and :PASSWORD, it's failing now both because of the @Native Directory issue, and because the :PASSWORD variable isn't now being populated.
So from what we can tell, firstly this appending of @Directory Name to the HSS user ID is causing Essbase connection pool logins to fail, but we can work around it using this approach of an additional custom Init Block, but so far we've not found a way around the EPM Workspace SSO issue, which means that if we want HSS users to be able to log into OBIEE and then access Essbase cubes using their own Essbase login, we have to restrict them to directly logging in to the OBIEE web interface rather than going through EPM Workspace. If anyone's got a solution to this last issue, add a comment to this posting.