Category Archives: Obiee 10g

The Art of Pivoting

One of the nice features of OBIEE is being able to view your data from various angles. A table of data is prefered by some people, but for others they would prefer to see totals by Month, then drill in where they see issues.

One of the mistakes I often see is that multiple reports are prepared, showing the same data, but in different ways. A Table on one, a pivot by Month for another, a pivot by region on another.  Worst still they put these reports on the same dashboard.

The best practice would be to create one report, then create various views that can be placed on dashboards, or even create multiple compound layouts.  Obviously Pivots are the best way to slice and dice the data, and you can go to town with these, creating as many as you like on one dataset (table).

When creating Pivot tables in 10g it was very slow and often impossible to remove columns from the left panel using the UI. The best way to do this was by updating the report XML, removing the columns referenced in the relevent section.

While the XML editing is also possible and useful in 11g, the UI is more helpful, allowing whole tables’ columns to be moved out, and of course the remove section is now below the columns making the process smoother.

Here are the steps, using a small example to demonstrate the point, using both methods:

Starting with a table view

Add a pivot

Drag the table name down to the excluded section

(drag the table name section)

The XML method involves selecting the Advanced tab

Locate the Pivot table section, and note the column ids (they are now much longer in 11g)

remove the columns you don’t want, and apply the XML

And the result is a pivot table with just the columns you want in.

 

Til Next time

@ddi

Multiple OBIEE Environments on one Box

The Oracle preferred method of having multiple environments is to have them installed on their own machines, either real or virtual. For many organisations this is simple enough, particularly if you run a good environments service which facilitates the easy set-up of virtual Windows or Linux boxes.

Where there are limited resources you can actually choose to run OBIEE 10g many times on the same box. This is particularly useful in development and test servers, but can also work in production.

I have blogged about this in the past, but not passed on too many details, so here is the full step by step on how to create more than one environment.

These instructions are for a Linux box, if you want to see a windows sample head over to Robin Moffats blog. I am also using the inbuilt oc4j web/application server.

Some of these environments can be used in a cluster, there is no SSO here but you can also configure that in the web layer if you need. Integration with LDAP is not effected.

Here I will use example of a Development environment (DEV), and therefore you will see that in the shared areas I have referenced a folder for DEV. You can easily place all config files and repositories in one share for all environments, but would place Production ones on their own.

STEPS

  1. Create users
  2. Set-up box
  3. Install a full set-up
  4. Re-assign the Ports
  5. Configure environment
  6. Test
  7. Install a second set-up
  8. Re-assign the Ports
  9. Configure environment
  10. Test

repeat step 7 to 10 until you have built all your envs.

I built 10 environments in total on each box.

CREATE USERS

I prefer to have specific user accounts for OBI. Create them with an appropriate name, such as obiee_1, obiee_2, etc. Make them all part of the same group, say obi.

Example Commands

Adding a group:

groupadd obiee

Add Users:

useradd obiee_1
passwd obiee_1
useradd -G obiee

(or you could try adduser)

see my friend the internet!

SET-UP BOX

In order to manage your environments you need an install area, logs area, config files area and somewhere for the repositories.  You will also need somewhere to for cluster files (mainly webcat).  For clusters that use multiple boxes I like to use a common set of config files, which means placing the config files on a share that both machines point to.

My advice is to use the following:

/usr/obiee      	Installation folder
/var/obiee_logs         Logs folder
/obiee_share/config	config files
/obiee_share/publish	Webcat and RPD files

Once you have the top level folders in place then just add a sub folder for each environment. So for example, the env 1, which is used by obiee_1 has the following folders:

/usr/obiee/env_1  		Installation folder
/var/obiee_logs/env_1		Logs folder
/obiee_share/DEV/config/env_1	config files
/obiee_share/DEV/publish/env_1	Webcat and RPD files

Only obiee_1 has access to these folder (or if you prefer you can have the group have read only access)

The config folder has sub folders for each service:

/obiee_share/DEV/config/env_1/nq_server  (or bi_server if you prefer)
/obiee_share/DEV/config/env_1/saw_server
/obiee_share/DEV/config/env_1/bip
/obiee_share/DEV/config/env_1/web_server

Now the only thing to add some helpful environment settings, such as aliases and variables. These will help when moving around and finding files. Below are some example ones I have used.

alias l='ls -l'
alias u='ps -u obiee_1'
alias bi='cd /usr/obiee/env_1/OracleBI'
alias cata='cd /obiee_share/publish/env_1/webcat'

The above can be added to a .profile (or similar) file.

INSTALL 1

Now we are ready to install the software, and I will assume that there is not an already running version installed.  If there is, skip the install and just reconfigure it to use a different set of ports (see further below).

Make sure you have a suitable Java installed and decide how you are going to use java installs.  You can choose to use the one install for all the Obiee environments or you can have a version of java installed for each obiee env.

Run the obiee set-up script

/setup.sh –console

When prompted for the install locations specify:

/usr/obiee/env_1/OrcleBI
/usr/obiee/env_1/OrcleBIData

After the install check that your services are up and running (u shortcut).

RE-ASSIGN THE PORTS

Now we can update the ports to use numbers that are not going to clash with other obiee envs.  Note that once you have updated ports you may have to update your firewall settings to external access (i.e. for online repository access).

Richard Halldearn came up with the idea of just adding one thousand to each port setting, and it seems to work great.  So, we have installed environment 1, therefore we will add 1 x 1000 to each port number, for example 9703 becomes 10703.  Below are the ports that relate to OBIEE, and the file they are in.

9700 Monitor Controller port for Intra Cluster Communication with Cluster Controllers NQClusterConfig.INI
9701 Monitor Server port for Intra Cluster Communication with clustered Analytics Server OracleBI/server/Config/ NQClusterConfig.INI
9703 Remote procedure call port of Oracle BI Server, Outfacing port used for ODBC connections NQSConfig.ini / odbc.ini / NQClusterConfig.ini
9704 Standard port for OC4J used for Web Browser access, in Catalog manager etc. OracleBI/oc4j_bi/j2ee/home/config/instanceconfig.xml / default-web-site.xml
9705 standard port for BI Server scheduler instanceconfig.xml
9706 Client controller part Clustering NQClusterConfig.ini / odbc.ini
9707 Scheduler instanceconfig.xml
9708 scheduler instanceconfig.xml
9710 Listener Port for presentation services and ISAPI instanceconfig.xml / isapiconfig.xml / web.xml
9810 Java Host port instanceconfig.

Configure Environment

The next step is to edit the config files for your settings – SSO, Usage tracking, memory, clusters, logging, tns entries, dns entries, etc

Test

Now you have updated the ports and config settings, and pointed at new config file locations it is time to test the first install. The one component that will be shared across the environments is the chart server, so make sure that is running properly at this stage.

Repeat!

You can leave the first installation up and running if you like while you create a second environment, as above but add 2000 to the port numbers.

Keeping repeating until all your environments are built.

You can cluster two together as well and they should work as if you clustered to separate machines (although clustering two machines would be better!).

Footnote

Oh, even though it works absolutely fine, Oracle probably don’t want to support your install with this set-up, so best to use in dev and test environments, and if you have it in prod then you may have to turn off all but one if you get an issue (even though it will not be related to the multi environments!)

@drian