OBIEE Blog

John Minkjan posted some interested and useful articles as usual. First is How to edit OBIEE system-wide defaults, the second one is about making a distinct date prompt in OBIEE, and finally OBIEE reporting on data ranges.

Adrian Ward has several wonderful OBIEE tips submitted by his readers.

Mark Rittman hosts a free seminar in London in a few weeks. So if you happen to be around, I strongly recommend you attend.

Venkatarishan J has posted an extensive article on Hyperion. Data Archive and Replay in BSO Cubes.

The dropdown box is disabled when I open the join dialog.

For the benefits of others readers, the customer wanted to know if it is possible to specify an outer join in the physical layer. Actually it is not possible explicitly specify an outer join in the physical layer. However, is possible to do this in the Business Model layer at either the “Business Model Diagram” level or within logical sources. Once done in either of those areas, the Analytics Engine will pass that join type into the generated physical SQL. The behavior of the two types of joins is:

- Full Outer Join defined in the “Business Model Diagram” between Logical Tables A & B – join will be performed between all foreign key relationships for the set of logical sources in logical tables A & B.

- Full Outer Join defined in a Logical Source – join will be performed only on the foreign key relationship chosen to define the join on.

The database features options for LEFT_OUTER_JOIN_SUPPORTED and RIGHT_OUTER_JOIN_SUPPORTED relate to whether the generated SQL will contain that syntax.

OBI EE and Hyperion Workspace / Smartspace integration was only introduced in EPM 11.1.1 and OBI EE 10.1.3.4.

To be able to have a seemless integration (No OBI EE Login Screen) when navigating from Hyperion Workspace 9.3.1 to OBI EE 10.1.3.4 you have the following options which might meet your implementation requirement: -

1) Use the ‘&NQUser=uuu&NQPassword=ppp’ URL arguments.

These are detailed in the section ‘Incorporating Oracle Business Intelligence Results into External Portals or Applications Using the Go URL’ of the Presentation Server Guide.

2) Enable OBI EE to use SSO. We support any SSO Vendor (SiteMinder, ClearTrust, Oracle SSO, Java SSO, etc…) which supports either HTTP Headers, Server Variables or Cookies.

Please see Chapters 8 and 10 of the Deployment Guide for more information on this area of functionality.

Neither of these options have been designed specifically for Workspace, but they should give you a generic option to implement a solution where no login is required when navigating to OBI EE from Workspace.

3) Just create a custom Init Block and custom session variable. Make the session variable to be initialized with the password. The query for the password initialization would be

SELECT ‘:PASSWORD’ FROM DUAL

Now, go to answers and create a report which would generate the Smartcut link. To this link pass the username (through the USER system session variable) and the password (through the custom session variable above). This will enable seamless login.

4) Enable BI EE to use the Table Authentication method, where usernames and passwords are stored in a database table. Passwords would be stored in encrypted form using obfuscation packages provided with the database.

Then create a report which would generate the Smartcut link. To this link pass the username (through the USER system session variable) and the password (through EVALUATE and a reverse obfuscation package function which would return the password in clearcase) in the report.
Then just use this report in the dashboard for providing the link. This will provide a seamless login.

5) I believe Workspace supports Impersonation. Technical Support have not tested this but it should work if the impersonation is possible. Using the same report approach above pass the Administrator username and password in the URL (these would be static) and also pass the actual BI EE username as the impersonation user in the URL. Provider services and Essbase JAPI support impersonation. We assume Workspace should support that as well. But of course, if its not supported then this would not work. Please liaise with Hyperion Technical Support or a Consultancy Department like Expert Services to look into this option further.

The above options are only supplied as possible workarounds, but Technical Support highly recommends that you upgrade to Hyperion Workspace 11.1.1 so you can leverage the built-in integration functionality.

There’s a bug in OBIEE 10.3.1.2 that renders time-series OBIEE function AGO useless if you’re using Filter on Logical Table Source (Content – WHERE). Apparently, the query generating AGO doesn’t pick it up and as a result – you will not see correct report. It might affect you in many ways if you’re using the LTS filters. The fix that Oracle suggested was to upgrade to 10.3.1.4, however, it’s not possible at the moment. We’ve created database views applying necessary filters there. Performance-wise we got a hit, but our business intelligence server is showing correct data.

Applies to:
Business Intelligence Server Enterprise Edition – Version: 7.9.5 [AA 1900] – Release: V7
Information in this document applies to any platform.
Symptoms
The user is modelling Time Series functionality (Todate) with aggregated dimensions and aggregated facts using BI Server 10.1.3.3.2.

You have created aggregate tables. You have both Logical Dimension and Fact tables mapped to the Aggregate tables.
In Answers you have built a report using the Dimension Hierarchy, which has the measures Sales and Sales YTD. When you drilldown in the report, the measures show the correct figures, until you get to the lowest level where the Sales YTD results are incorrect.
Cause

The cause of this incorrect aggregation when you drilldown to the lowest level of the hierarchy has been determined to be an incorrect sql being generated. The incorrect sql generated lacks the
SUM..GROUP BY

The main difference is that when you are at the lowest level of the aggregate table it doesn’t use “SUM..GROUP BY” in it’s core query.

The issue has been determined to be a Bug, as per Engineering who reviewed the issue.

Solution

However a workaround was provided for this specific customer scenario that resolved the issue. With the implementation below which engineering tested and verified the SUM and Group by was generated in the sql when drilling into the lowest level of the hierarchy.

”
I created a dimension that I just created in the business model and added the additional dimension to the content filter for the problem fact source. That resulted in SQL generation with a SUM clause. My workaround was used just for the problem query submitted. I did not even join the new dimension to the fact table.

So I would recommend creating a virtual dimension in the physical and business model layers. It should not be exposed in the presentation catalog.
“