Dates as Filters

Date Filters in OBIEE

Filtering dates by default in your Answers is a fairly common theme.
You also have the option to replace the IN with greater than or less than.

Yesterday:

From Your Analysis
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE))



Setting the Dashboard Prompt:
SELECT TIMESTAMPADD (SQL_TSI_DAY,-1,CURRENT_DATE) FROM "ReportDate"


I'll leave out setting the dashboard prompt with the next few examples as the syntax is similar and the changes from the filter can be worked out from the example above.


First Day of the Month:
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , CURRENT_DATE ))


First Day of the Year:
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFYEAR(CURRENT_DATE )-1) , CURRENT_DATE ))


First Day of the Quarter:
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY, -1*(DAY_OF_QUARTER(CURRENT_DATE )-1) , CURRENT_DATE ))


This is not an exhaustive list of what is possible, but should get you started.

Firewall Ports and OBIEE 12c

Firewalls for OBIEE 12c

Firewalls are incredibly useful and alsy immensely frustrating, especially when you're in a large corporation and want to get things done. You have a new server sitting somewhere on the network and install (or try to) OBIEE only to discover that only you can see this machine as localhost and it is buried behind firewalls and varying levels of security.

Getting approval to open this machine up to be useful is bound to be a challenge.

Here's a list of the ports I have uncovered that I had opened enabling a multi node OBIEE install.

OBI System Components

9507: BI Presentation Services
9508: Cluster Controller
9509: Cluster Controller (monitor)
9510: Java Host
9511: Scheduler
9512: Scheduler (monitor)
9513: Scheduler (script)

Weblogic Components

9500: Admin Server (HTTP)
9501: Admin Server (HTTPS)
9504: Admin Server (Internal)
9502: Managed Server (HTTP)
9503: Managed Server (HTTPS)
9505: Managed Server (Internal)
9506: Node Manager

And don't forget 1521 - very important if you want to talk to your Oracle database.

BICS Upload files

BICS allows you to upload files that you can include in your models and visualisations.

But, there is a limitation on the file size of your uploads.
  1MB per file
 10MB per user
100MB per instance

Now this will probably change over time to be more generous, but, it is a frustration.

OBIEE 12c Architecture vs 11g

Quite a lot has happened architecturally under the covers with the release of OBIEE 12c.

OBIEE 12c Architecture diagram

OBIEE 11g Architecture Diagram

There is one major difference...... OPMN is gone in OBIEE 12c.

OBIEE components are now started via a series of commands run from {DOMAIN_HOME}/bitools/bin

• start
• stop 
• status

and it is also possible to run the 'commands' to stop and start the components in (the new look) FMW Enterprise Manager.

OBIEE 12c - the correct location for the tnsnames.ora file

Where is the correct location for the tnsnames.ora file in OBIEE 12c?

Create or copy your tnsnames.ora file to 

         {DOMAIN_HOME}/config/fmwconfig/bienv/core  

(e.g. /.../Oracle_Home/user_projects/domains/bi/config/fmwconfig/bienv/core/tnsnames.ora)

Note: This would also include other OCI files, such as sqlnet.ora if used.

This is documented in the Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition 12c

see: Setting Up Oracle Database Data Sources

OBIEE 12c - RCU - Create Repository

There are a few dependencies when running the OBIEE 12c RCU, one of which is that the database must be at least 11.2.0.4.

After that the next thing you notice after starting the RCU is the options available.

You now have the ability to generate the RCU scripts and pass them on to a DBA to run in seperately, rather than beg for admin access, organise meetings etc... just to run the RCU.

If you have the permissions already then nothing has changed and the top option System Load and Product Load is for you.

The remaining two options are to generate scripts and then load in the product information

• Select Prepare Scripts for System Load if you are unable to provide login credentials for a user with DBA privileges. RCU will create the necessary SQL scripts for creating the component schema and also update schema_version_registry entry for selected components and change the status to "LOADED." Once the scripts are generated, another user with DBA privileges can run the scripts using SQL*Plus to complete the system load phase. 
• Select Perform Product Load only after the system load phase scripts have been run to update the schema_version_registry and set the status to "VALID." Users with non-DBA privileges are able to select and execute this option. During the system load phase, some components will generate an additional post-data load script that requires DBA permissions. These scripts will also be run during the product load phase.

This means that the DBA gets to keep control, and you get your repository without ruffling too many feathers.

OBIEE 12c is Out!

Just arrived OBIEE 12c. This from a message on the Business Analytics - Proactive Support blog

I'm having a look and will have a play.

Find out more here

It looks like there is no in place upgrade, more a install and migrate, but, like I say, I'll let you know how I get on.

OBIEE - Remember Username and Password

I came accross this 'hack' and thought it may be worth a mention if used sparingly on a non-production (i.e. DEVELOPMENT) environment.

Use at your own risk.

Remember Username and Password in Obiee11g

Go to your BIPS config path, for example like below,
D:\OBIEE11G\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1

and the search Security tab in your instanceconfig file and add

<AllowRememberPassword>true</AllowRememberPassword>

Then save it and restart bips servervices.
Include the elements and their ancestor elements as appropriate, as shown in the following example:

<Security>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<ClientSessionExpireMinutes>210</ClientSessionExpireMinutes>
<AllowRememberPassword>true</AllowRememberPassword>
<CookieDomain>value</CookieDomain>
<CookiePath>/analytics</CookiePath>
<InIFrameRenderingMode>prohibit</InIFrameRenderingMode>
</Security>

Save your changes and close the file.

Restart Oracle Business Intelligence Presentation Services using OPMN

Login to your BI Home page ..then logout and login again. OBIEE will remember your Username and Password and go straight to your BI home page.

For More please refer to the Oracle documentation,
http://docs.oracle.com/cd/E23943_01/bi.1111/e10541/psconfigset.htm#BIESG1292

OBIEE 11g : RPD Consistency Check

A Consistency Check ensures that the repository is in a fit state to process any queries passed through.

A "Consistency Check" is a feature of the Administration Tool that checks whether a repository has met certain requirements, such as the following:

•  All logical columns are mapped directly or indirectly to one or more physical columns.
•  All logical dimension tables have a logical key.
•  All logical tables have a logical join relationship to another logical table.
•  There are at least two logical tables in the business model: a logical fact table and a logical dimension table. Both can map to the same physical table. 
• There are no circular logical join relationships. 
• A subject area exists for the business model.

You can check consistency for the entire repository or for a single object:

For the entire repository: select File > Check Global Consistency.  or Tools > Show Consistency Checker.

For an individual repository object: select the object, right-click and select Check Consistency. You can select multiple objects and check their consistency.

When you check in changes to a repository you are also prompted to Check Consistency before saving, this however is for the entire repository. A large repository can take some time to complete a check.

It is good practise to keep checking consistency in the local area you are working and making changes. You may even notice that the consistency checker has reported no errors but has 'fixed' some inner workings. It may be worth digging in to what is actually being adjusted automatically to suit what the consistency checker expects. If anyone knows please drop me a line.

Oracle Business Intelligence Enterprise Edition 11.1.1.9.0

Oracle Business Intelligence Enterprise Edition 11.1.1.9.0 is now available.

The newest version of OBIEE is now available to download from OTN.

The patching and upgrading looks fairly straightforward and I'll let you know what happens and how it happened as soon as I've done the upgrade.

Instructions and documentation on upgrading are available here.

General documentation about 11.1.1.9 is available here.

Until then

Oracle BI Mobile Apps - Latest Update Available from App Store

Oracle has released new version 11.1.1.7.0 (11.6.20) of Oracle BI Mobile HD app for Apple.

Oracle Business Intelligence Mobile for Apple iPad is a mobile analytics app that allows you to view, analyze and act on Oracle Business Intelligence 11g content

So what's new:

•  iOS 8 Support
• Immersive Mode. Changes and improvements including show/Hide options for the Prompts bar, the Page bar, and the title bar.
• Local iOS Notification for Alerts. Receive your Oracle BI Mobile alerts in the iOS Notification Center, from which your alerts take you directly to the app.
• iPhone: Improvement to First-Time Help. Now you can reset the first-time help so that it shows again whenever you like.
• iPad: Improvements to UI. Simplified welcome dialog allows you to quickly choose the demo, the server configuration screen, or offline mode. Simplified sharing options in the Settings screen.
• Translation Update

Compatibility: 

Requires iOS 7.1 or later. Compatible with iPhone, iPad, and iPod touch. This app is optimized for iPhone 5, iPhone 6, and iPhone 6 Plus.

Android App is currently in version 11.1.1.7.0.195 and was updated at last in November 2014.

OBIEE 11g 11.1.1.7.150120 Bundle Patch is Available

As mentioned by proactive support (https://blogs.oracle.com/proactivesupportEPM/entry/obiee_11g_11_1_11) the latest OBIEE 11.1.1.7 patchset is available.

OBIEE bundle patch 11.1.1.7.150120 can be downloaded as Patch 20124371 and is comprised of the following patches.

• Patch 16913445 - Patch 11.1.1.7.150120 (1 of 8) Oracle Business Intelligence Installer (BIINST)
• Patch 19822893 - Patch 11.1.1.7.150120 (2 of 8) Oracle Business Intelligence Publisher (BIP)
• Patch 19825503 - Patch 11.1.1.7.150120 (3 of 8) Enterprise Performance Management Components Installed from BI Installer 11.1.1.7.0 (BIFNDNEPM)
• Patch 19822857 - Patch 11.1.1.7.150120 (4 of 8) Oracle Business Intelligence Server (BIS)
• Patch 19822826 - Patch 11.1.1.7.150120 (5 of 8) Oracle Business Intelligence Presentation Services (BIPS)
• Patch 19822874 - Patch 11.1.1.7.150120 (6 of 8) Oracle Real-Time Decisions (RTD)
• Patch 16997936 - Patch 11.1.1.7.150120 (7 of 8) Oracle Business Intelligence ADF Components (BIADFCOMPS)
• Patch 20022695 - Patch 11.1.1.7.150120 (8 of 8) Oracle Business Intelligence Platform Client Installers and MapViewer

Also you must download and install:

Patch 16569379 - Dynamic Monitoring Service patch.

If you use Internet Explorer 11, download and install:

Patch 18277370 - JDeveloper patch

Is this the last patchset for 11.1.1.7?

OBIEE 11g: Exceeded configured maximum number of allowed input records

Oracle have limited various numbers of outputs for rows, columns, prompts, cells and pivots to default values. These values are ususally set high enough to never become an issue, but for really large reports you will bump up against an error message saying you have exceeded some maximum.

So what do you do?

Step 1

Edit the instanceconfig.xml file. Typically, this file is located in the following directory: <OBIEE11g_install>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1
If you are running a load balanced environment please make sure all environments have the same settings (or share the same settings) or you'll be forever chasing down 'weird' customer experiences.

Step 2

There are parts of the instanceconfig.xml you cannot edit directly and those sections will be marked as only being updateable via Oracle Enterprise Manager. The parts we can edit I have marked in bold.

<Views>
<Pivot>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DisableAutoPreview>false</DisableAutoPreview>
<MaxCells>10000000</MaxCells>
<MaxVisibleColumns>1000</MaxVisibleColumns>
<MaxVisiblePages>1000</MaxVisiblePages>
<MaxVisibleRows>10000</MaxVisibleRows>
<MaxVisibleSections>25000</MaxVisibleSections>
<DefaultRowsDisplayed>128000</DefaultRowsDisplayed>
</Pivot>
<Table>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->
<DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload>
<MaxVisiblePages>1000</MaxVisiblePages>
<MaxVisibleRows>10000</MaxVisibleRows>
<MaxVisibleSections>2000</MaxVisibleSections>
<DefaultRowsDisplayed>64000</DefaultRowsDisplayed>
<MaxCells>10000000</MaxCells>
</Table>
</Views>

Note that the value you choose for <MaxCells> should never be more than <MaxVisibleColumns> * <MaxVisibleRows>.

In the case above <MaxVisibleColumns>1000</MaxVisibleColumns> * <MaxVisibleRows>10000</MaxVisibleRows> = <MaxCells>10000000</MaxCells>

Please restart all presentation services to pick up the new settings.

OBIEE - Catalog Cleanup

Catalog Cleanup

Do you sometimes look at the catalog and wonder what kind of timebomb is lurking in there? I have nightmares. You've designed a catalog layout and asked developers to follow standards and follow the guidelines, but one year later......? What about two or even three years later? What Answers work has been started and abandoned, unfinished and unworking? What Answers remain from 'deleted' or 'hidden' dashboards and now no longer work due to repository changes?

The challenge

We are a small team of developers so so for us automation of tasks is key. For each report in the catalog what we need to know is:
Does this report generate a useable query? (Is it broken)?
Is this report on a dashboard, or more than one dashboard?
If the report is on a dashbord, is it in the correct directory?

We anticipate that this will be repeated periodically, you can never be sure what creeps in and slips through the cracks, but at least the generation of the report can be automated and scheduled.

At a high level what we do is to combine the output of some Catalog reports output, and use NQCMD to generate logical queries and track the outputs.

The Catalog Reports

Firstly we will generate a Dashboard Report and then an Analysis Report, and we'll upload the data into some database tables and stitch the two together with an outer join so we can see those reports on dashboards as well as those without.

We call the Analysis Report and a Dashboard with a script like below; Note, your system will have different settings.

D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\runcat.cmd -cmd report -excelFormat -distinct -offline D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\samplesales -forceOutputFile D:\CatalogCapture\Analysis.txt -type "Analysis" "Name" "Path" "Table" "Column" "Subject Area"

D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\runcat.cmd -cmd report -excelFormat -distinct -offline D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\samplesales -forceOutputFile D:\CatalogCapture\Dashboard.txt -type "Dashboard" "Name" "Path" "Dashboard Style" "Dashboard Page Name" "Dashboard Page Path" "Dashboard Page Description" "Analysis Name" "Analysis Path"

Join the two outputs on "Path" and "Analysis Path" - think outer join here because we still want to process those Analysis not on dashboards.

Generating a Logical Query

What we are going to do is pass the each analysis into NQCMD using the current repository to test whether a valid Logical Query.

Create a text file called cred.txt and make the contents something like
login=weblogic
pwd=weblogic123

Now using the report output from part 1 we can run the following to extract the query from the catalog for the answer. The Query will be saved into a file called myquery.lsql
D:\OBI\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\runcat.cmd -cmd report -online http://%SERVR%/analytics/saw.dll -credentials "D:\CatalogCapture\creds.txt" -forceOutputFile "myquery.lsql" -folder "/shared/...../myreport" -type "Analysis" "SQL"

One of two things happen here:
1. The file containing the query is generated, or
2. An "empty file" is generated.

If we have an empty file the Answer failed to generate a Query and should be flagged for investigation.

However, if we have a query in the file, we need to pass the contents through NQCMD to see if the Answer works.

Extending into NQCMD

We have previously used NQCMD to do impact analysis on repository changes and can use the functionality almost unchanged.

"D:\OBI\Oracle_BI1\bifoundation\server\bin\nqcmd.exe" -d coreapplication_OH140093583 -u weblogic -p weblogic123 -s "myquery.lsql" -o "output.lres"

Once again one of two things can happen here:
1. The logical query passed in returns a result, or
2. An error is generated.

The error may be something like

[10058][State: S1000] [NQODBC] [SQL_STATE: S1000] [nQSError: 10058] A general error has occurred.
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43119] Query Failed: 
[nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist
 at OCI call OCIStmtExecute.
[nQSError: 17010] SQL statement preparation failed.
Statement execute failed


Processed: 1 queries
Encountered 1  errors

Once again flag the error.

Wrapping things up and bringing it together

From each Answer we now know
1. Is it used on a dashboard?
2. Where is the Answer stored?
3. Does it generate a logical query?
4. Does the logical query work?

Now we can focus efforts on fixing, rewriting or deleting those Analysis on dashboards, then those that are not on a dashboard but do not work.
Your initial steps may just be to identify those Answers that don't generate a logical query for a maintenance routine?

OBIEE - OBI_METRICS_AGENT on Windows

OBI_METRICS_AGENT

I would like to firstly extend my thanks to Rittman Mead for making this freely available. It managed to fill the gap in performance analysis that I had been struggling to fill. Yes, I had started down the WLST route and was pulling my hair out.

So what did I do to get it to work for me?

So what's the setup: OBIEE 11.1.1.7 on Windows Server 2008 64.

Firstly, download the zip file containing the obi_metrics_agent from https://github.com/RittmanMead/obi-metrics-agent.

The zip file contains a python script, and being windows nothing is easy, we have to go and download and install python. Please make sure you download the correct version for your machine. I donwloaded Python 2.7 for 64Bit Windows (python-2.7.6.amd64.msi) and ran the installer and put Python into C:\Python27.

Job done....... not quite. The obi_metrics_agent needs the lxml library. Fortunately my trusty steed "Google" was at hand and kept nudging me in the right direction.

It transpires that you need to run ez_setup.py and this will download the setup tools allowing you to "easy install" other packages and any dependant packages. Download the ez_setup.py script and place it in the C:\Python27 directory.

Easy I thought and from the command line: C:\Python27\python.exe ez_setup.py and apart from a few error messages about proxy unavailable.... nothing. A few minutes of head-scratching later and I realised that none of the Servers can access the internet, so a few minutes more on the good steed "Google" grazing the plains of knowledge, I dowloaded the setuptools-3.4.4.zip file and placed it in the C:\Python27 directory. Now the ez_setup.py script command completed without error.

Now I have the easy_install.exe and can download the lxml-2.3-py2.7-win-amd64.egg file - once again make sure you get the correct version for your Python install. You will find the easy_install.exe in the Python27\scripts folder, so once again placing the egg file on the server we run c:\Python27\Scripts\easy_install.exe lxml-2.3-py2.7-win-amd64.egg. This unpacks into the C:\Python27\Lib\site-packages directory.

So, if everything has worked so far, you are nearly good to go.

I then unpacked the obi_metrics_agent.zip into the middleware home directory (on my machine D:\OBI) which created a subdirectory off called D:\OBI\obi-metrics-agent-master and here's where the main script resides.

After some faffing around, I settled on the following as appropriate for my needs, yours may be different.

C:\Python27\python.exe D:\OBI\obi-metrics-agent-master\obi-metrics-agent.py --opmnbin D:\OBI\instances\instance1\bin\opmnctl.bat --data-directory D:\OBI\obi-metrics-agent-master\data --interval 10

Now I must admit I have spent a fair amount of time going through the different options and outputs and it realy works, I'll eave it to you to judge, but thanks Rittman Mead.

I have jobs that run every couple of minutes loading the data into a database allowing us to monitor the internals against usage - amazing!

OBIEE - Automatically Generate Repository Documentation

I have strived to close the loop with automating all of the internal reporting on OBIEE.

So far we have automated the capture of what reports, filters and prompts are on our dashboards.

We have in place an automatic system for recording the performance of the reports on the current dashboards. This we run every night and the results uploaded to the database.

We have the option of running an 'after' script which compares the output from dashboards (logical sql, row counts and timings) when we upload a new repository, this gives us impact analysis.

What we lacked was a way to automate the generation of the repository documentation. This was the manual step that was sometimes skipped.

Having a report on the repository, from physical to presentation and then joining this data to what's on the dashboards gives us a view of what's being used where and where the data is sourced from.

Now I must admit, this seems like a 'hack' and I wouldn't use it on any environment where I couldn't freely break things without causing damage. Oh, and it is windows only - there is no AdminTool for Linux/Solaris - sigh.

The Repository Command Script

So what do we have to do?

We are going to use the /command switch of the AdminTool to run a script of, you guessed it, .... commands.

The command script we've called report_rep.build and put in the following few lines, replacing MWHOMW with your own settings, repository with your own and the repository password with your own.

OpenOffline <MWHOME>\instances\instance1\bifoundation\OracleBIServerComponennt\coreapplication_obis1\repository\repository.rpd <repository password>
DescribeRepository D:\scriptoutput\repository_report.csv UTF-8
Close
Exit

The next part involves creating a script to run the AdminTool and pass in the script file. Just to be safe it may be worth setting the environment using some of the bits from the bi-init.cmd as follows:

@echo off
set ORACLE_HOME=<MWHOME>\Oracle_BI
set ORACLE_INSTANCE=<MWHOME>\instances\instance1
set ORACLE_BI_APPLICATION=coreapplication
set JAVA_HOME=<MWHOME>\Oracle_BI1\jdk
set ESSBASEPATH=%ORACLE_HOME%\clients\epm\Essbase\EssbaseRTC
call %ORACLE_INSTANCE%\bifoundation\OracleBIApplication\%ORACLE_BI_APPLICATION%\setup\user.cmd
set PATH=%ORACLE_HOME%\common\ODBC\Merant\5.3\Drivers;%ORACLE_HOME%\bifoundation\server\bin;%ORACLE_HOME%\bifoundation\web\bin;%ESSBASEPATH%\bin;%ORACLE_HOME%\bin;%PATH%
<MWHOME>\Oracle_BI1\bifoundation\server\bin\AdminTool.exe /command d:\scripts\report_rep.build

Before running the above script we copy the latest repository to one side and call it repository.rpd. Not very imaginative I know but that way we don't muck around with the actual repository and, in theory, if we script it right, this will always be a copy of the latest and most up to date RPD.

You should be left with a CSV report of your repository (all being well).

Upload to your favourite database and analysis tool.

OBIEE - Displaying the Value of System Variables

Following from the previous article here on the subject of displaying your repository variables, I thought it may be time to look at showing some of the system variables

On a dashboard, drag in a text item and edit the contents to include something like

We are running OBIEE version [u][b]-@{system.productVersion} [/b][/u]
This is the @{dashboard.name} dashboard
and we are on the @{dashboard.currentPage} page.

OBIEE - Report Footer

Report Footer

You can create a report footer by adding another Title view and editing the layout.
- set the display text to custom and enter "Confidential" or some sort of disclaimer text.
- set the style to show the text as centered, bold and Italic.
- there you go, a confidential footer for your report.

OBIEE - Aggregation Rules and Functions

The following list describes the aggregation rules that are available for measure columns in the "Layout pane" and for columns in the "Edit Column Formula dialog: Column Formula tab". The list also includes functions that you can use when creating a calculated item.

• Default — Applies the default aggregation rule as in the Oracle BI repository or by the original author of the analysis. Not available for calculated items.
• Server Determined — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the Oracle BI repository). The aggregation is performed within Presentation Services for simple rules such as Sum, Min, and Max. Not available for measure columns in the Layout pane or for calculated items.
• Sum — Calculates the sum obtained by adding up all values in the result set. Use this for items that have numeric values.
• Min — Calculates the minimum value (lowest numeric value) of the rows in the result set. Use this for items that have numeric values.
• Max — Calculates the maximum value (highest numeric value) of the rows in the result set. Use this for items that have numeric values.
• Average — Calculates the average (mean) value of an item in the result set. Use this for items that have numeric values. Averages on tables and pivot tables are rounded to the nearest whole number.
• First — In the result set, selects the first occurrence of the item for measures. For calculated items, selects the first member according to the display in the Selected list. Not available in the Edit Column Formula dialog.
• Last — In the result set, selects the last occurrence of the item. For calculated items, selects the last member according to the display in the Selected list. Not available in the Edit Column Formula dialog.
• Count — Calculates the number of rows in the result set that have a nonnull value for the item. The item is typically a column name, in which case the number of rows with nonnull values for that column are returned.
• Count Distinct — Adds distinct processing to the Count function, which means that each distinct occurrence of the item is counted only once.
• None — Applies no aggregation. Not available for calculated items.
• Server Complex Aggregate — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the Oracle BI repository). The aggregation is performed by the Oracle BI Server, rather than within Presentation Services. Not available for calculated items.
• Report-Based Total (when applicable) — If not selected, specifies that the Oracle BI Server should calculate the total based on the entire result set, before applying any filters to the measures. Not available in the Edit Column Formula dialog or for calculated items. Only available for attribute columns.

OBIEE - Connection Pools

Apart from creating your connection pool, filling in the type of database, username and password, do you give much thought to the connection pool.

If you have heavy usage, you should increase the maximum number of connections from the default of 10.
You will definately need a connection pool seperate from the others to set your session variables.
You will also need a separate connection pool for usage tracking.
Keep a separate connection pool for write back.

Just a few thoughts