Tag Archives: OBIEE blogs

OBIEE topics of interest

Good morning All,

I’m back from Florida vacation. What a change – to come from sunny Florida to cold and raining New York.

Trying to see what’s going on in the OBIEE world. Couple interesting topics on the OTN:

Write back – topic – very interesting and growing

Example on how not to ask questions about complex joins – the author should have provided all the details in the beggining

Not using AGO and TODATE functions – I don’t know why anyone would like to avoid using AGO and TODATE functions for time-series analysis. The only two options are: non-Oracle DB which doesn’t support complex PARTITION BY queries or someone doesn’t want to create time dimension.

Also, there’re some very good discussions on the OBIEE Enterprise Methodology Group.

Good luck and enjoy your week.

OBIEE issues – you asked I answer (find answer)

[ad#banner]
Hello,

I’m back after being busy with some really interesting issues at work.  I found a minute and I’d like to post few items.

First of all – the question “In OBI, How to move System Wide default settings from one environment to another?” comes up a lot – here’s an official response that I found on the knowledge base:

System wide default settings cannot be moved from one environment to another by design. The recommended way to move any other changes is to perform an Archive and then an unarchive. But, this method does not work for System wide default settings

The following Bug has been logged to make this feature available in the future releases:
BUG 8271294 – ARCHIVING AND UNARCHIVING DOES NOT STORE SYSTEM WIDE DEFAULTS

So now you know what to tell your project manager.

Second item I found interesting is:

“How to reduce the space between multiple pivot tables placed side by side?”

1. Provided that s_oracle10 is in use; backup the following files before editing them
– C:\OracleBI\web\app\res\s_oracle10\b_mozilla_4\views.css
– C:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_oracle10\b_mozilla_4\views.css

2. Set the following section in both files above:
.PTSectsTable

{
border:
none;
border-spacing:0px;
padding:0px;
margin:0px;
}

3. Restart the OC4j and the Presentation server
Third item,
“When defining multiple connection pools for a database in the physical layer of the rpd, is it possible to make the queries use the 2nd connection pool because current behavior seen is that the 1st connection pool is used by default for all queries?”

There is currently no way to make a query use a specific connection pool. The following bug has been raised to address this requirement- As you are already aware, current workaround is to have separate database defined in physical layer :

Bug 9392303 – FIRST CONNECTION POOL CHOSEN BY DEFAULT FOR ALL QUERIES

Customers requirement :

They have a new application that need connect to mutiple database, so under
physical layer root have two connection pool.
So under Database in physical layer 1st connection pool points to an oracle
instance and second connection pool points to a different oracle instance.
When we run report in answers, the connection information is always taken
from the 1st connection pool by default – hence we get table cannot be found
ora error. Reason being the table exists in the database instance referenced
in the 2nd connection pool.
When we do view data in RPD , the results return fine – this is because while
clicking on view data, we explicitly have to select the connection pool.
While creating the logical table/column, we only specify the database name
and the schema name and the table name- no place to provide connection pool
info – hence the server chooses the 1st one by default.
Need a way to fix this – current workaround suggested is to have multiple
database created in physical layer.

Thank you wonderful Oracle support representatives. You’re doing great job – often overlooked, but appreciated nevertheless.
Have a safe OBIEE day

Several new bugs

Happy New Year! It’s been a busy time, however, I’m back with some new posts. I wish the readers the best of the best of the best :-) I wish that your repository always stays consistent, that your physical layer is a true star, and your users never have problems with BI Answers.  I’ve digged up a few things that might be interesting:

Issue 1

Duplicate records are being eliminated from the Answers request results, when the desired results would be to include all rows, including duplicates.

The duplicate rows can have both the same case or different case.  Setting the case sensitivity parameter has no impact.

Example:

One row may be:
Dinner

Another row may be:
dinner or Dinner

The second row is not included in the Answers results.  Running the same SQL directly on the server does return all rows, including duplicates.  Analytic server is removing the duplicates.

Cause

Analytic Server issues the distinct clause.

Solution

Include the table key column in the request.  Hide the column so it is not actually seen in the request results.  This will make each row unique and thus each row, including what would be considered duplicate, is included in the results.

=======

Item 2

How to configure External Db Authentication that supports OBIS Impersonation (i.e. Delivers, BI Publisher)

Solution

To implement external DB authentication (i.e., authentication via a user/pw record in a database), you should NOT use AUTHENTICATION_TYPE = DATABASE. Instead, define it exactly like the Siebel Integrated setup in SiebelAnalytics.rpd’s/EnterpriseBusinessAnalytics.rpd’s Authentication init block. That is:
Use the default NQS Authentication type in NQSConfig.ini by commenting out all authentication types.
Define a separate physical database in the rpd which has a connection pool configured to hit the external database used which has the defined OBI users.
Enable Shared logon for this connection pool; set User name to :USER and Password to :PASSWORD.
Only the connection pool configuration is required for this physical database – no table/column objects are required.
Define an Authentication Session initialization block to set the session system security variable ‘USER’ to be used for OBIS External DB Authentication.
Point the initialization block to the connection pool defined in step 2.
Use an initialization string like “Select ‘:USER’ from external_table WHERE USERID=':USER’.
Define the ‘Edit Data Target’ to use system security variable ‘USER’ – this will make this initialization block the defacto OBIS authentication init block.
Define all connection pools used for backend data access to use ‘Shared logon’ with an explicit database user/password with at least read access to all backend tables.
This configuration supports OBIS Impersonation (i.e. Delivers). Database Authentication will be done via the connection pool defined in the Authentication init block. The user / password entered in an OBIPS client browser session will be passed to OBIS via the :USER / :PASSWORD system session variables. These values will then be passed to the database for user validation – if a database session is established using this user/password combo, the user will be considered authenticated to OBI.

Also, it does not require all OBI users to be defined in the rpd. In fact, remove all the OBI users from the rpd that will be authenticated via a database user entry. If you require OBIS Group Security assignments for users, you should use a separate external table (either existing or new) that stores the user-to-OBIS group associations, and make the associations via a session initialization block. Again, similar to the SiebelAnalytics.rpd’s/EnterpriseBusinessAnalytics.rpd’s Autherization initialization block.

Great visualization of which chart to choose.

This image – apparently copyrighted by A. Abela (thank you very much, by the way) – is a great starting point for deciding which chart to choose. Of course, OBIEE currently doesn’t support some of them (such as Variable width column chart) – but I think it’s a very good illustration on various chart types. The chart is available here:
Chart

Also, please check this presentation with some og the AJAX/HTML 5 chart examples – Cool charts – link here
Those are small things – but they could get a lot of brownie points for developers – they would make OBIEE look hip and modern. I really loved the interactive bouncing charts. Not terribly original – but they do create a better experience. Think about users!

I think that the future hold tighter web integration (xml / ajax) for BI systems as well as some great UI. Just look at Iphone – their UI was one of the major reasons for their success. I noticed the trend that many products get that slick look (salesforce, qlickview) – and I hope OBIEE 11G isn’t far behind. I’ve not had a chance to look at it hand-on (just screenshots from presentations) – but again – I hope that product managers looks closely into user experience. The latest version isn’t bad at all – but it’s not far away from distant siebel analytics’s screen/layout. I hope developers would get the facility to customize front-end more liberaly (right now – it involves a lot of xml hacking if you go beyond some CSS stylesheets).

Best wishes.

OBIEE heats up, OBIEE blogging cools down

Thanksgiving holiday, pre-Christmas routine, and a general lethargic state due to the weather. They don’t help us to write more about OBIEE. Also, I have a feeling that everyone has a feeling that all OBIEE topics have been covered. So everyone is just waiting for 11g – and then there’ll be a race.

RNM1978 posted a very good visual representation of OBIEE documentation – http://rnm1978.wordpress.com/2009/11/27/i-think-this-summarises-everything/
From one side, bad documentation has opened a niche for experts to blog and discover new things. On the other hand – if it’s not in the docs – it’s probably not covered by Oracle’s support – so some companies might be wary of installing new things.

As far as a new forum – I’m still working on identifying an open-source forum platform that would have all features that OTN has and more. There’re some candidates – but I’m still in the process of search.

I will be honest – I still check OTN – however, I give it a quick glance and then just leave. I still give respect to everyone who’s answering questions, however silly lazy ones. I’m wondering if the influx of new OBIEE will contribute to rates pushed down in the future. For one thing I’m sure now that there’re 2 different kind of “freshers” – one making a transition from another BI tool (Cognos, MS Analysis Services, Discoverer) or a closely-related area (Oracle data warehousing, OLAP) and those who have taken “$600 courses”. It’s very conflicting – since everyone has to start somewhere. However, starting by spamming OTN isn’t the right way to do it.

This is why I was actually against point system when it was implemented – it incentives seasoned consultants reply to silly questions – the ones they would have left without attention before.

Anyway, I hope everyone had a nice break! Back to work! And please stay healthy and well.