Tag Archives: Obiee

OBIEE Administration Tool – Import Metadata shows no schemas

Importing Metadata with the Administration Tool

The client-only install of the OBIEE 11g Administration Tool is installed with a set of OCI libraries. This means that it can support basic Oracle Database interaction, without the need for a full Oracle Client installation on the machine. For example, you can update row counts in the Physical layer of the RPD of tables that are on Oracle.

Unfortunately, the supplied OCI libraries are not complete, which leads to a rather tricky problem to diagnose. When you use the Import Metadata operation (either from the File menu, or context menu on an existing Connection Pool), the step (“Select Metadata objects”) which ought to show the schemas just shows a stub, and no schemas.

No schemas shown in Select Metadata Objects / Import Metadata

No error is shown by the Administration Tool, giving the erroneous impression that there just aren’t any schemas in the database.

Missing OCI library

The Administration Tool writes a log, which by default is in the following rather long path: C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\oraclebi\orainst\diagnostics\logs\OracleBIServerComponent\coreapplication\Administrator_NQSAdminTool.log

If you examine the log, you’ll see this error:

[2011-12-16T15:10:12.000+00:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: ] [tid: 8b4]  [nQSError: 93001] Can not load library, oracore11.dll, due to, The specified module could not be found. [[
.
The specified module could not be found.

]]
The key bit in this is “Can not load library, oracore11.dll“. This is the library which is missing and on which there is a dependency. The library isn’t provided by InstantClient, so you must install the full Oracle Client.

 

Installing the Oracle Client

Download the Oracle Client from the Oracle Website. The link is currently this, but may change. In this instance I downloaded “Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit)” (all 600MB+ of it).

 

Unzip the installer and run setup.exe. If you want as minimal an installation as possible, then select the custom installation, and choose just the “Oracle Call Interface (OCI)” option.

Oracle Client installer - OCI libraries

Once you’ve installed the Full Client, restart the AdminTool and the Import Metadata function will now work.

 

Footnote – tnsnames.ora

Don’t forget that if you don’t install the full Oracle Client and use the OCI functionality provided by the OBIEE installation alone, you will need to configure your tnsnames.ora file in C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client\oraclebi\orahome\network\admin\tnsnames.ora. The exception is if you are using Easy Connect DSNs (dbserver:port/sid) in your RPD rather than TNS entries (orcl etc)

Footnote – troubleshooting library issues

Microsoft’s SysInternals suite includes the program ProcMon. You can point this at a running process and see what it’s up to in terms of file access, DLLs, and networking. It is great for detecting things like:

  • Which files a process writes to (eg where is a user preference stored)
  • Check which PATHs are being searched for an executable / library
  • Which tnsnames.ora is being picked up
  • What network connections are being made, or failing
  • Registry key access

When you run ProcMon you’ll realise how much is going on in the background of your Windows machine – there’ll be screenful upon screenful of output. To focus on the target of your analysis, use the Include Process option to just show AdminTool.exe:

Include AdminTool in procmon traces

You can then see things like it searching for the oracore11.dll which it is missing:

oracore11.dll missing

The next entry shows the log file being updated, giving you the path if you didn’t know it already:

AdminTool log file

OBIEE performance tuning myth : BI Server logging

One of the frequent recommendations around performance in OBIEE that one hears is a blanket insistence on disabling the BI Server log. It is a line that is repeated by Oracle support, propogated in “Best Practice” guides, and repeated throughout blog posts on the subject. Antony Heljula did a talk on the subject at the recent RittmanMead BI Forum in Brighton, and I would like to echo and expand on it here.

The Myth: 

If you are having performance problems in OBIEE, you should switch off BI Server logging

The arguments for:

  1. Instinct would tell us that writing a log is going to take longer than not writing to a log
  2. On a system with high user concurrency, we would expect to see contention for writing to the log file
  3. Usage Tracking records report response times, so why do we also need the server logging
  4. Log files will cause the disk to fill up, which left uncontrolled could cause system instability

The arguments against:

  1. If you have performance problems in OBIEE, then you need logging in place to be able to trace and diagnose them. The BI Server log gives us vital information such as what physical SQL results from a logical query from the front end. If you turn off logging, you lose all visibility of query behaviour, timings, and row counts. 
  2. OBIEE writes lots of logs, more so now in 11g. Why only disable one of them? Why not all logs? 
  3. If a query takes 30 seconds to run, how much of that 30 seconds is actually going to be in log overhead? You disable logging and now your query runs in 29.999 seconds. It’s still slow, it’s still a performance problem – and now you don’t have the data available with which to diagnose the problem!
  4. Usage Tracking doesn’t record the same level of detail around a query’s behaviour (response time profile, row counts) that the server log does.
  5. By default, Usage Tracking chops off Logical SQL above 1024 characters in length. 
  6. Sometimes you need the log file to confirm that Usage Tracking is reporting correctly (especially in circumstances where report run times seem unusually high)
  7. Error messages returned from the database are not captured in Usage Tracking  

It Depends

To a point, I am being contrary in arguing this specific issue, but it is important with this and other broad-stroke pronouncements around performance that get regurgitating without context and caveats that they are understood. In particular, labelling it a “Best Practice” is a dangerous fallacy as it implies that it should be done without much further thought or consideration of its consequences.

If the NFR for a report’s performance is [sub]-second and it is not being met, then profiling of the end-to-end response time breakdown should be done, and it might be that it demonstrates that the logging is impeding performance. But the point is that it is proven rather than done blindly. 

Further reading

Cary Millsap’s paper, Thinking Clearly About Performance, is an excellent starting point for developing an understanding of a logical and methodical approach to performance problem solving. 

James Morle wrote an great blog post on the subject of “Best Practice” and why it is dangerous terminology, entitled “Right Practice”


 
Thanks to Tony for reviewing & making further suggestions for this article.
 

 

Public training schedule for 2012-H2 announced

Rittman Mead offer training courses at centres in Brighton UK, Atlanta GA, Bengaluru India, and Melbourne Australia. These courses are our standard, “bootcamp” courses, five days in length, and is typically taught by consultants such as Mark Rittman, Robin Moffatt, Stewart Bryson, Ashley Beauman and Venkatakrishnan J.

If you’ve wanted to attend one of our courses but your company didn’t want to train up an entire team, here’s your opportunity to learn Oracle BI from the experts!

We are pleased to announce the following new dates for our popular public training courses in the UK, India, and Australia. Dates for America will follow shortly: 

TRN202 : OBIEE 11g Bootcamp (5 days) Prices: £2000 + VAT (UK), US$ 3,250 (USA), Rs. 44,000 (India), AUD 3,250 + GST (Australia)

  • 11th – 15th June, Brighton UK
  • 11th – 15th June, Bengaluru India
  • 9th – 13th July, Bengaluru India
  • 23rd – 27th July, Melbourne Australia
  • 6th – 10th August, Bengaluru India
  • 13th – 17th August, Brighton UK
  • 10th – 14th September, Bengaluru India
  • 8th – 12th October, Brighton UK
  • 8th – 12th October, Bengaluru India
  • 12th – 16th November, Bengaluru India
  • 10th – 14th December, Brighton UK
  • 10th – 14th December, Bengaluru India

TRN205 : Oracle BI EE 11g Create Reports, Dashboards, Alerts and Scorecards (2 days) Prices: £800 + VAT (UK), US$ 1,300 (USA), Rs. 17,600 (India), AUD 1,300 + GST (Australia)

  • 23rd August – 24th August, Melbourne, Australia

TRN 403 : Oracle Data Integrator 11g Bootcamp (5 Days) Prices: £2000 + VAT (UK), US$ 3,250 (USA), Rs. 44,000 (India), AUD 3,250 + GST (Australia)

  • 11th – 15th June, Atlanta GA
  • 16th – 20th July, Bengaluru India
  • 30th July – 3rd August, Brighton UK
  • 17th – 21st September, Bengaluru India
  • 24th – 29th September, Melbourne Australia
  • 29th October – 2nd November, Brighton UK

TRN303 : Oracle Business Intelligence Applications 7.9.6.3 Bootcamp (3 Days) Prices: £2000 + VAT (UK), US$ 3,250 (USA), Rs. 44,000 (India), AUD 3,750 + GST (Australia)

  • 20th – 22nd August, Bengaluru India
  • 15th – 17th December, Bengaluru India

Don’t forget, there is a 10% discount for UKOUG members. For more information, or discuss your training requirements in detail, please email us.

screen and OBIEE

With the recent delivery of the Rittman Mead Exalytics server, I have been fortunate enough to be spending time setting it up, most of it at a Linux terminal window. One of the tools I come back to, again and again, is screen. It allows one to be so much more productive that I want to share my enthusiasm for it here. If you do any work with Linux/Unix and don’t use it, then give it a go – you won’t regret it!

Screen is one of those pieces of software whose name makes it inherently difficult to Google for, so you’ll often see it referred to as GNU screen. It is described as a “Screen Manager” – which for me doesn’t really describe its rich functionality and goodness.

Here are some of the many reasons why screen is great:

Session persistence

Reconnect and pick up exactly where you left off. Whether you’re on a dodgy wifi connection that’s dropped out, or you’re in the office and need to reconnect to your session when you get home, screen keeps your session running regardless of your SSH connection and lets you reconnect to it at any time. Whilst you’re disconnected, the processes associated with your session are still executing.

You may wondering why I wouldn’t just use nohup ... & for keeping processes running to survive a disconnect (a SIGHUP, hence NOHUP). With nohup ... & , you have to decide in advance that you want to run the process in the background, and you can’t easily interact with it once it’s there. To do the same in screen you just run the process, and then reconnect to your screen session when you want to.

Think of it like auto-save on steroids for your ssh session. If you use no other functionality in screen, this alone is reason to use it.

Multiple sessions in one

So you could run multiple copies of puTTY, but why not connect once, and have multiple sessions within that one? Often you’ll want to have a WebLogic process, bi_server process, opmn, maybe top, and sqlplus. If you create five puTTY connections, and they drop, you have to reconnect all five and restart what you were doing. If you are using screen, you just reconnect and resume the screen session, and it is literally as it was when you left it. If you’re halfway through a masterpiece of SQL query development or execution in sqlplus, you’ll appreciate being able to go straight back into it, and not start a new sqlplus session.

Each session can be named, with a “task bar” across the bottom of the screen to show which sessions there are and which you’re currently looking at

Scrollback

Most ssh clients offer scrollback history, but it’s native to the client, and relies on you being connected to the session.

With screen you can scroll up and down a session’s history from the keyboard, and see whatever’s been output whether you were connected to the session at the time or not

What’s this got to do with OBIEE?

OBIEE is not a simple beast, particularly when you are setting it up and deploying it. Being able to have multiple sessions on the server is invaluable. For example, tail the server log in one session and start up a process in the other, whilst running top to monitor system usage in a third.

As you may well know, the WebLogic processes (AdminServer and Managed Server) do not run as daemon (background) processes when started from the command line. On Linux this means you either have to run them with nohup ... & or as a session within screen. If you run it with nohup ... & you would be sensible to redirect output to disk so that you can see the console output. So to see what’s going on with the process, you need to find the output file and tail it. If you’ve run it with screen, you just need to switch to that session and the output’s there to see. In addition, you can see at a glance what is running, without having to start looking at process lists, as you would have to for a process started using nohup.

Sounds good, where do I get it?

screen is installed by default on lots of Linux distributions (including Exalytics’ OEL5) – just type screen -v to see. If it’s not, then a quick Google should turn up how to install it for your particular Linux/Unix distribution. It’s almost certainly available on any Linux/Unix variant (and certainly any on which OBIEE will run!).

What’s the catch?

screen has a steep learning curve, there is no denying it. But when you reach the crest of that curve, the warm glow of enlightenment as the power of it dawns on you is a rewarding one ;-)

The main thing to get your head around is the keyboard navigation. It’s pretty much all based on pressing Ctrl-A following by another key sequence. If you get stuck, do Ctrl-A and press the question mark key ?.

So how do I use it?

First off, I would strongly recommend setting up the .screenrc file – this configures the “toolbar” at the bottom of the window. If you don’t do this, then you don’t have the visual indication that screen is even running, let alone which session you are in. The file goes in your home directory (which is what the tilde ~ means). Note that the first character of the filename is a dot.

Put the following in ~/.screenrc :

# Minimal .screenrc
# RNM 2012-05-11
#
hardstatus alwayslastline "%{= RY}%H %{kG}%{G} Screen(s): %{c}%w %=%{kG}%c %D, %M %d %Y "
startup_message off
msgwait 1
defscrollback 100000 

This is a one-time configuration step. If you look on Google you will find plenty of examples of pimped-out config files doing lots of funky things.

So now type screen, and you should see something like this:

NewImage

Now I’m going to walk through how I would use it to start up and working with an OBIEE instance. So the first step is start WebLogic AdminServer. Note that I’m not using nohup

 cd user_projects/domains/bifoundation_domain/bin
./startWebLogic.sh 
NewImage
At the bottom of the screen you can see it says “0* bash”. This means that it is screen/window zero, and the title is bash. The title is an arbitrary name, it doesn’t have to relate to the process running within. Since we’re going to leave WebLogic Admin Server running in this screen, I am going to rename it.
 
Press Ctrl-A and then Shift-A. You’ll be prompted to change the title. NewImage
 
Enter AdminServer (or whatever you’d like). The title now changes:
NewImage
 
We can also see that the server has completed start up (Server started in RUNNING mode)
 
Now, close your terminal window. Go ahead. Your WebLogic Admin Server will still be running (connect to Enterprise Manager or WLS Console to check). Now log back on to your server, and instead of typing screen, enter screen -x. The -x flag tells screen to attach to an existing screen session. You should now be back at your screen window as before, with the session titled AdminServer.
 
To start up the Managed Server, we want a new session (window). To do this, press Ctrl-A and then Ctrl-C to create a new session. Your window should look like this:
NewImage
 
You’ll notice that along the bottom of the window you now have a second screen shown, number 1 (the first was number 0) with the default title of “bash”. The asterisk shows that it’s the currently displayed screen. Don’t forget, whichever screen you’re currently looking at -or none, if you’ve disconnected- the processes in all of the screens are still active and running.
 
To switch between screens, you have several options:
  1. Ctrl-A and then Ctrl-A switches between the current screen and the one you were on previously.
  2. Ctrl-A and then Ctrl-N switches to the next screen (0 -> 1 -> 2, etc)
  3. Ctrl-A and then Ctrl-P switches to the previous screen (2 -> 1 -> 0 etc)
  4. Ctrl-A and then a number switches to the screen of the corresponding number
This may seem like overkill, but you may have many screens, so being able to access them directly is important.
 
So, back to OBIEE. We’re going to be running the Managed Server, bi_server1, in this screen, so let’s set the title of it (Ctrl-A, Shift-A) and then start the process
 cd user_projects/domains/bifoundation_domain/bin
./startManagedWebLogic.sh bi_server1 
NewImage
 
Now, for the purpose of this demo, let’s imagine that we want to scroll back and check what command we entered. You’ll notice that if you try to scroll your terminal window up, you don’t see any history. That’s because screen works in a different way, and you do scrollback within it natively. Press Ctrl-A and then the square bracket key [ to enter Copy mode, which also enables you to do scrolling. You'll notice the message "Copy mode' appear at the bottom of the screen.
NewImage
 
Now you can use standard vi key mappings to navigate - Ctrl-B to move up a page, Ctrl-F to move forward a page, etc. If you first type a number, it moves that number of pages (e.g. type 50 Ctrl-B will scroll back fifty pages of history).
 
From within this mode you can actually copy output into the screen buffer, but that is beyond this tutorial. For now, press square bracket key ] to exit Copy mode.
 
Having started up the Admin Server and Managed Server, we now just need to start Node Manager and opmn plus the system components. Create two screens (Ctrl-A then Ctrl-C), and change the title (Ctrl-A, Shift-A) of each to reflect what will run in it
NewImage
 
You can now see why being able to switch between screens by number is so useful. Press Ctrl-A and then 2 to go to the NodeManager window, and start NodeManager:
 cd wlserver_10.3/server/bin/
./startNodeManager.sh 

Switch to the opmn window, by pressing Ctrl-A and then Ctrl-N to move to the next window, and start opmn and the system components:

 cd instances/instance1/bin
./opmnctl startall 

Whilst opmn starts, cycle through all of your windows (Ctrl-A and then Ctrl-N to move to the next window) and observe that you can see the status of each process that you’ve started.

 

Finally, create a fifth window (Ctrl-A, Ctrl-C) and call it “sar”. Type sar 1 600 which will run sar for ten minutes. Note the times on the left column, and switch to the previous screen (Ctrl-A, Ctrl-A). Wait a few seconds, and switch back (Ctrl-A, Ctrl-A). You’ll see that sar has been running all the time, whether you were on the screen or not

NewImage

To prove the point, close your ssh session and then reopen it, and resume your screen session (screen -x). You’ll see that the processes have been running all the time, whether you are connected or not.

So, to summarise, I now have a screen session with:

  • The control of each process available directly from each window
  • The output of the processes which write to stdout
  • Additional windows for monitoring (sar, collectl, etc), running sqlplus, etc

I can disconnect any time, and I know that the processes are still running, and I can reconnect whenever I want. I don’t have to go furtling through folders to find my nohup.out files. If a colleague needs to take over admin of the server, they too can just connect with screen -x and see the same session.

Finding out more about screen

What I’ve described here is really only scratching the surface of what you can do with screen. It’s a very popular tool, and hence widely written about. Some links which I’ve bookmarked in the past are:

OBIEE install avoid RTD license problems

If you are not going to use RTD (Real Time Decisions) uncheck the box during installation:

image

This avoids extremely difficult discussions with Oracle over the license fee.

Till Next Time