Where are OBIEE Logfiles?

This applies to OBIEE 11G logfiles.

When it comes to hunting down the source of a problem in OBIEE it is a good idea to go through the logfiles to see where an error has occurred. There are a number of OBIEE logfiles, with each component generating it own set.

Assuming that You have installed OBIEE with a middleware home of D:\OBIEE11G then

• the Administration Server has logfiles in: D:\OBIEE11G\user_projects\domains\bifoundation_domain\servers\AdminServer\logs\AdminServer-diagnostic.log
• the Managed Server has logfiles in: D:\OBIEE11G\user_projects\domains\bifoundation_domain\servers\bi_server1\logs\bi_server1-diagnostic.log
• the BI Component logs are in: D:\OBIEE11G\instances\instance1\diagnositc\logs\*
• the OPMN logs are in: D:\OBIEE11G\instances\instance1\diagnositc\logs\OPMN\opmn\*
• the Enterprise Manager logs is in: D:\OBIEE11G\user_projects\domains\bifoundation_domain\sysman\log\emoms.trc

It may be worth putting a shortcut to these files / directories on your desktop.

Also helpful is wintail which allows you to view a logfile and all the writes being made to it without the constraints of notepad etc...

If you have configured windows services for AdminServer and bi_server1 under beasvc, then hopefully you configured them with a logfile. The logfiles from the services help in guiding you to any problems as they are the record of what you would normally see when starting OBIEE manually.

Until next time.

No more Complex Joins please!

Avoid Physical Layer Complex Joins.

While it is perfectly acceptable to use complex joins in the physical layer, this can (and usually does) result in poor query performance when OBIEE generates the query against the database. Far better to use the foreign key joins that should be implicit in the star/snowflakes of your data model.

For example where the complex join involves a between condition, then you are looking at index range scans on the database, repeat this several million times when you need an average on a large dataset, and you are potentially going to be looking at a spinning clock symbol for a long time.

Any work that has to be done by the BI server costs time. Much as you tune a query in the database to avoid making function calls in the 'WHERE' clause, so you should aim to do the same in your physical joins. - No more BICOLUMN = TRUNC(DATECOLUIMN) or BICOLUMN = SUBSTR(CHARCOLUMN,1,4).

Set a Dashboard Landing Page for all Users

If you want all your default and new users to go to a specific page when they login, and they have not set a specific page or their 'home' page is set to default then they can be directed via the PORTALPATH setting.

PORTALPATH

The PORTALPATH setting will only work if the user has not set a new default home page in their account settings.

You will need to make a few changes in the repository to enable this setting.

Open the repository and go to manage --> variables

Navigate the tree displayed and open up Session and then Variables and then System
You will create a new Session Variable here

Right click on the right hand side and select the new PORTALPATH or create a new variable PORTALPATH if no option is presented.

In the Default Initialiser - give the path to the required dashboard. eg /shared/samplesales/_portal/landingpage

Close the repository and those users without a default page in their account setting will instead land on the new landing page.

Making a Pivot Table Paginate

Wouldn't it be nice if you could have your pivot table split across several 'virtual' pages.

I must admit, it's not an everyday requirement and probably only applicable where your pivot table runs down and off the page.

So, how can we create a paging mechanism, as this is not native to pivot tables, and will only work under a closely defined set of circumstances.

In answers we create a new column, and this will take some fiddling around to get the optimal number, edit the column formula to something like

TRUNCATE((RCOUNT(1)-1)/12, 0) +1

and rename the column to something like page. This will give us an increasing number every 12 rows. I am assuming here that each year has 12 months and we are ordering our report year - month.

In the pivot table drag the 'page' column into the pages section. You should then see a drop down in the pages section that mimics the action of the next/previous buttons on tabular reports.

Now, not all reports - especially those that we will display as pivot tables align themselves so conveniently, where 'pages' can be grouped equally (12 months in a year). In that case you should think about a grouping using the BY clause.

Patching 11.1.1.6 to 11.1.1.6.2

The patch for upgrading to 11.1.1.6.2 is available. Details here.

Pretty much a straightforward upgrade, though a few things to watch out for:
1. Set your environment variables correctly

       set ORACLE_HOME=C:\mwhome\Oracle_BI1
       set PATH=%ORACLE_HOME%\bin;%PATH%
       set JAVA_HOME=%ORACLE_HOME%\jdk
       set PATH=%JAVA_HOME%\bin;%PATH%
       set PATH=%ORACLE_HOME%\OPatch;%PATH%

2. Make sure you run opatch from the correct directory

Open a command window (with the correct environment variables set - see 1) in the Oracle_BI1 directory.

3. When you come to Part 2. JDeveloper patch 13952743

Unpack the patch into oracle_common.
Change directory to oracle_common
Using the environment variables set in step 1 run

opatch apply 13952743 -OH C:\mwhome\oracle_common

This will patch the oracle_common directory, otherwise the patch reports nothing to upgrade.

OBIEE 11G - Changing Your Password

The Challenge 

There is always more than one way to do something. Setting up the functionality to change your password in OBIEE 11G is no different.

Our experience proved that it is not technically challenging, all the documentation is there, you merely have to spend an hour or so gathering all the facts, doing a little research, and then the fun starts.

Our Solution

We decided to deploy a JSP and class as a WAR. Mainly because that is something we could achieve and where our experience lay.

We are currently running OBIEE 11.1.1.6 so a careful hunt through the compatibility matrix reveals that Jdeveloper 11.1.2.1 is a good match.

Coding the Java Class

In essense we are going to make a connection to the security mbean and invoke a changeUserPassword

invoke(securityMBeanName,"changeUserPassword",objuser, objstr);

So fire up JDeveloper and lets start

Calling the JSP

We will create a narrative report and use the Session Variable  USER as a parameter in our URL call to the JSP.

The JSP

So we have coded and tested our class now we need a JSP that will use the class. From the narrative report we know the user name and now we can concentrate on the password change functionality. 

We use 3 fields Old Password, New Password and Repeat New Password. By using a web page we have the ability to obscure the characters being entered.

Deploying the WAR 

Wrap everything JSP and Class in a WAR - fortunately this is nearly a one click operation in JDeveloper.

Place the WAR file in a directory on the OBIEE server and deploy using the console. 

You can then call your password change using the narrative report.

OBIEE 11G and tnsnames.ora

Where do I put tnsnames.ora in OBIEE 11G?

In versions of OBIEE prior to 11g you would use the tnsnames of the Oracle client and the tnsnames would be found in the network/admin directory.

In OBIEE 11G you can place your tnsnames file directly into the middleware_home/Oracle_BI1/network/admin directory.

Works for me.

Update:

You can connect WITHOUT a tnsnames file.

OBIEE 12c uses a different directory

What Hour is it?

You can extract time elements from dates in OBIEE at both the repository and answers level.

Just use something along the lines of :

CAST ( EXTRACT( HOUR FROM "CentralCal"."date") AS INTEGER )

Conditional format on a null

If you want to highlight the empty columns in your report, and make them stand out visually you want to look at adding a condition on the is null operator.

<image>

11G authentication failure - Validate Catalog

When you move repositories between machines and copy the catalog it is possible that there will be conflicting permissions between the details in the catalog and details held in weblogic.

I have previously mentioned how to reset the GUIDS - the glue between weblogic authentication and the catalog.

Sometimes this is not enough - usually when you find that have no permission to change settings on your My Dashboard or you cannot change settings in My Account.

A light touch 'refresh' may be something like:
If you add the following to your instanceconfig.xml file
<Validate>OnStartup</Validate>
<ValidateItems>None</ValidateItems>
<ValidateLinks>None</
ValidateLinks>
<ValidateAccounts>Clean</ValidateAccounts>
<ValidateHomes>None</ValidateHomes>
and open a command line in ../instances/instance1/bin
you can use opmnctl to stop and start the services. This may take some time depending on the size of your catalog.
Remove the Validate section from your instanceconfig.xml and stop/start opmnctl.

To force a 'deep clean' you can opt for something like:

<ps:Validate>OnStartupAndExit</ps:Validate>

<ps:ValidateAccounts>Clean</ps:ValidateAccounts>

<ps:ValidateHomes>Report</ps:ValidateHomes>

<ps:ValidateItems>Clean</ps:ValidateItems>

<ps:ValidateLinks>Clean</ps:ValidateLinks>

As always the documentation is available here from Oracle.

Hiding Catalog and Open links from Users in OBIEE 11.1.1.5

Oracle support have thought of this for us - Useful but limiting and only really applies to 11.1.1.5

How to remove Catalog, Open, Advanced, Help, Home and Search links in OBIEE 11.1.1.5 [ID 1349315.1]

In order to accomplish this following steps need to be performed:

In OBIEE 11.1.1.5 the following new expressions have been added in saw.sessioninfos.xml file. This file is under $ORACLE_HOME/bifoundation/web/msgdb/common/

Edit saw.sessioninfos.xml file, you will see following tags:

<gdexpression id="hdrLinkCatalog" expr="true" />
<gdexpression id="hdrLinkOpen" expr="true" />
<gdexpression id="hdrLinkAdvanced" expr="true" />
<gdexpression id="hdrLinkHelp" expr="true" />
<gdexpression id="hdrLinkHome" expr="true" />
<gdexpression id="hdrLinkGSearch" expr="true" />

Each of these control the display of a UI element in the global header.
By setting the value to "false" instead of "true", the corresponding UI element in the global header can be hidden.

For changes to take effect, please re-start BI Services and clear Browser cache.

IMPORTANT: Finally, note that this change is global, meaning will affect all users. Currently there is an Enhancement Request which has as objective to control these changes via permissions so it can be set at user level, (Bug 10173641).

What is a Bridge Table ?

If you want to connect two tables where there is no relationship, you can use a third table, or bridge table for connecting them. This table will have common columns in both tables, this is used in BMM Layer.

You would mainly use a bridge table to perform a many-to-many join without the bridge table being seen as the fact table. The bridge table is then joined in the Business Modelling Layer and 'tagged' as a bridge type or assignment.

Many-to-many relationships can cause nightmares in the modelling and, producting correct and

How to avoid a bridge table?
There are several approaches:

Hide the many-to-many relationship. You can publish two versions of the schema: the full one for use by structured reporting and a handful of power users, and a version that eliminates the many-to-many relationship for use by more casual users.

Eliminate the many-to-many relationship by collapsing or aggregating multiple rows. Add a row to the many-to-many dimension table: “Multiple rows”. The fact table can then link directly with the dimension. As with all design decisions, the IT organization cannot choose this approach without consulting with the user community. For something which has limited information value, this approach may be quite acceptable.

Identify a single primary row (The top 1) It may be possible to identify a primary row, either based on some logic in the transaction system or by way of business rules.
 
Pivot out the many-to-many dimension (see below the Boolean Column Method). If the domain of the multi-choice space is small, you can eliminate the bridge table by creating a dimension table with one column for each choice.

More on Dates

Do you have timestamp values????

And do you want to......, well join them to a date????

Cast(timestamp_value as DATE)

Does the same as TRUNC(timestamp_value) in Oracle. It removes the time section and you are left with just the date part.

Works in Prompts as well as prompted columns (fortunately).

Which table is selected at runtime

You have many tables under your logical table in OBIEE. Is there any way to guarantee the order in which they are used when multiple paths through the data are available?

Well there is a way to ensure that given tables are ranked lower in the list for selection.

Priority Group

Found on the General Tab of the Logical table source properties.

You can set priority group numbers to determine which logical table source should be used for queries for which there is more than one logical table source that can satisfy the requested set of columns.

For example, you might have a lookup table and another very large table where the data is repeated. Both have the same data, just one has many more rows. Often, scanning a large (many million row table) for distinct values is expensive, while access to a lookup table is “cheap.” In this situation, you can assign a higher priority to the lookup to ensure that all queries are fulfilled using the smaller (more efficient) table if possible.

Note that the priority group of a given logical table source does not always ensure that a particular query will be fulfilled by that source.

Note that the default value for Priority Group is 0 (zero), and changing this value to 1 (one) makes it lower in priority. Zero is therefore highest priority.

Char to Date

How to convert a CHAR to a Date datatype.

Assuming that the format of the char date is consistent the easiest is to use the EVALUATE function

EVALUATE('TO_DATE(%1,%2)' AS DATE,,'dd/mm/yyyy')

Here's a good article on more with changing a CHAR using the CAST function in OBIEE.

Happy Hacking