OBIEE Blog

Symptoms
After migration from 10.1.3.2.1 to 10.1.3.4 Prompt selection window not rendering correctly when a Multi Select Prompt is created for OBI EE Dashboard Prompts.

In Answers, On clicking to go to the selector screen for the Multi-Select Prompt, in it we see on the right:
match: begins with…. the right side is empty. Also the left side (‘Selected’) is empty.
So, No values display in the list.

Details:
- Customer has tried entering a value in the textbox and clicking go, nothing happens.
- The issue does not happen on customers 10.1.3.2.1 instance. It only happens on their 10.1.3.4 instance which they have migrated from 10.1.3.2.1.
- Web Server is Apache\Tomcat 5.5
- Java version is JDK 1.5.18

Steps to reproduce:

- Log into Answers
- At top of left Answers window select ‘New Dashboard Prompt icon, & select a subject area
- from the list on the left select a dimension (eg Calendar Month Desc)
- change Control to ‘Multi-select’
- select ‘Constrain’
- change Defaults To to ‘Specific Value’
- from that list select a value
- top right of screen select the Preview icon
- next to default value, select the icon to bring up the Multi-select window.
- the issue is seen here, no values in the list.
Cause

Client migrated from 10.1.3.2 to 10.1.3.4. During the migration they replaced the ‘res’ folder with the copy from their 10.1.3.2 version and then regenerated the war file. The same war file was deployed in Tomcat.

This is the root of the problem. The ‘res’ folder should not be copied from the older version instance.
Solution

Obtain a fresh 10.1.3.4 ‘res’ folder and replace the copied 10.1.3.2.1 version with that.

Modified the NQSConfig.ini file to change the TMP directory and the DATA_STORAGE_PATH (Cache).
Restarted the BI server and presentation server and noticed that files are not being written to the expected locations.

For example, the TMP directory was changed from:

WORK_DIRECTORY_PATHS = “/u01/app/oracle/product/10.1.3/obiee/OracleBIData/tmp”;

To:
WORK_DIRECTORY_PATHS = “/obi-tmp/tmp”;

The DATA_STORAGE_PATH was changed from:
DATA_STORAGE_PATH = “C:\OracleBI\server\Data\Temp\Cache” 500 MB ;

To:
DATA_STORAGE_PATH = “/obi-tmp/cache” 500 MB;

After making these changes, the TMP and cache files are still being written to the original locations.
Cause

Cache and TMP file locations are controlled by NSQConfig.ini parameters and the SATEMPDIR environment variable.
While the NSQConfig.ini file had been partially updated, the SATEMPDIR had not been updated at all.

The cache settings need to be updated in the NSQConfig.ini file under “[MDX_MEMBER_CACHE]” and “Query Result Cache Section.”

If the SATEMPDIR environment variable is not set and both cache locations are not updated in the
NSQConfig.ini file, the files will continue to write to the default locations.
Solution

Please complete/verify the following steps to change TMP and Cache File locations:

1. Open the NQSConfig.ini file and verify/modify desired paths for the TMP and Cache files.
You should leave original values in the file, but prefix with a “#” symbol to comment out the value.
For example:

TMP Files

#WORK_DIRECTORY_PATHS = “/u01/app/oracle/product/10.1.3/obiee/OracleBIData/tmp”;
WORK_DIRECTORY_PATHS = “/obi-tmp/tmp”;

Cache Files

Query Result Cache Section

[ CACHE ]

ENABLE = NO;
// A comma separated list of <directory maxSize> pair(s)
// e.g. DATA_STORAGE_PATHS = “d:\OracleBIData\nQSCache” 500 MB;
#DATA_STORAGE_PATHS = “/u01/app/oracle/product/10.1.3/obiee/OracleBIData/cache” 500 MB;
DATA_STORAGE_PATHS = “/obi-tmp/cache” 500 MB;

[ MDX_MEMBER_CACHE ]

#DATA_STORAGE_PATH = “/u01/app/oracle/product/10.1.3/obiee/OracleBIData/cache” 500 MB;
DATA_STORAGE_PATH = “/obi-tmp/cache” 500 MB;

2. Then update the SATEMPDIR environment variable to show the same physical path as the WORK_DIRECTORY_PATHS variable in the NSQConfig.ini file (“/obi-tmp/tmp”).
The SATEMPDIR is only applicable to the TMP files.

NOTE: On Linux/Unix systems, this environment variable is defined in the common.sh file in the OracleBI/setup directory.

On Windows, the SATEMPDIR is defined under System Settings -> Advanced -> Environment Variables.

3. After making these changes, stop the BI Presentation Server and BI Server. Then restart.

4. After restarting, you will see .tmp and cache files written to the new directories.

We would like to reset the ‘Administrator’ password for:

i) ‘OBIEE’
ii) ‘BI Publisher’ and
iii) ‘BI Scheduler’

Please provide a solution to perform a password reset.
Solution

The steps to perform this task are below.

The example solution begins by creating a BI Publisher superuser.

On both OBIEE and BI Publisher.

Start username/password:    Administrator/Administrator
After username/password    Administrator/admin3

BI Publisher superuser username/password admin2/admin2

After the test I also proved you could log into OBIEE and click on the preconfigured Dashboard.

Please follow the exact steps below and create the same usernames and passwords to start off with.

1. Take a backup copy of the

c:\OracleBI\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml

file.
2. Log into BI Publisher
3. Select Admin > Security Center > Security Configuration
4. Enable a Local Superuser called: admin2 with the password admin2
. Click <Apply>

5. Restart oc4j
6. Sign into BI Publisher as  admin2/admin2
This should log in successfully.

7. Log into OBIEE Admintool (Online) and change the Administrator password to ‘admintool’.
8. Check in and save the changes.
9. Log into OBIEE Answers as Administrator/admin3

10. Change the ‘Administrator’ password under the ‘Security Model’ to ‘admin3′. Click <Apply>
11. Restart oc4j
12. Login to BI Publisher as Administrator/admin3
This should log in successfully.

13. Log into OBIEE Answers as Administrator/admin3
14. Click on More Products > BI Publisher
15. This should go straight through to BI Publisher without prompting for a username and password.

If the above method still fails attach your c:\OracleBI\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml file for comparison. I would expect the encrypted password strings to exactly match my environment.

** BI Scheduler password **

This password can be changed in ‘schconfig’ from the DOS/UNIX command line/shell.
C:\>schconfig
Copyright (c) 1997-2006 Oracle Corporation, All rights reserved

***** Delivers Configuration Menu *****
1 – Configure Scheduler

>>> Enter Choice: 1

***** Scheduler Configuration *****
1 – Database
2 – General
3 – Advanced
0 – Quit

>>> Enter Choice:

>>> Enter Choice: 2

***** Scheduler General Configuration *****
10 – Administrator Name : Administrator
11 – Administrator Password : *****
0 – Quit

>>> Enter Choice: 10, then 11

0 – Quit
0 – Quit
0 – Quit

Quit 3 times and restart all Analytics Services (to bring them back in sync – restarting the BI Scheduler on its own also works, but Job Manager connections may exhibit stale behaviour depending on what has been changed) .

I thought this might be useful for someone struggling with Essbase.

Client was on OBI 10.1.3.4.0 QF 170 and Oracle’s Hyperion Essbase 9.3.1 and running a report in Answers based on ASOSample Essbase cube which is generating an error.

In Answers Customer has created a report with a measure column (selected twice) which is being filtered with 2 different values using the filter function as below:

Add the 1st column
a. In Answers, Navigate to the Criteria Tab,
b. Select the column “Original Price”
c. On the column, click ‘edit formulae’,
d. in the edit formulae box, click the ‘Filter’ button
e. Insert a filter for the column, by selecting from the selection pane
column “Products”.”Product SKU” = ‘Boomboxes’.
.
Add the second column
Repeat the above steps a-d
In step e, Insert a filter for the column, by selecting from the selection pane column “Products”.”Product SKU” = ‘DVD’

The report can also be generated by directly entering the following SQL in Advanced Tab
select
Filter( Sample.”Original Price” using “Products”.”Product SKU” = ‘Boomboxes’ ),Filter( Sample.”Original Price” using “Products”.”Product SKU” = ‘DVD’ )
from ASOSamp

————–
When the report is run the error message that is generated is as below

Query Failed: [nQSError: 42043] An external aggregate is found in an outer query

The MDX generated is

With
set [Products5] as ‘{[Products].[Boomboxes], [Products].[DVD]}’
set [Axis1Set] as ‘crossjoin ({[Age3]},crossjoin ({[Attribute Calculations2]},crossjoin ({[Geography5]},crossjoin ({[Income Level2]},crossjoin ({[Payment Type2]},crossjoin ({[Products6]},crossjoin ({[Promotions2]},crossjoin ({[Stores4]},crossjoin ({[Time5]},crossjoin ({[Transaction Type2]},{[Years2]}))))))))))’
select
{[Measures].[Original Price]} on columns,
NON EMPTY {[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [ASOsamp.Sample]
Cause

By default in the OBI rpd, the Aggregation rule is set to “External Aggregation”. As per Engineering this needs to be change, so OBI EE knows what those rules are.

Filter By clauses are not supported by MDX (or Essbase), so OBI EE has to perform those internally. If the Aggregation rule is set to “External Aggregation”, then OBI EE doesn’t know what those rules are and hence throws this error.
Solution

It is possible to fix the error by doing the below

1. Changing the OBI Aggregation rule in both the Physical and BMM layer to the corresponding rule in Essbase, would stop the error.

2. Alternatively, using OBI Answers filter clauses would allow the BI Server to re-write the MDX to push down the filter to Essbase and may be more efficient anyway.

There was an enhancement raised within Oracle to address this matter in a future release, as the client stated that changing the aggregation rule in the rpd is not a suitable option.

This tutorial applies to:Business Intelligence Server Enterprise Edition – Version: 10.1.3.4.0 [1900] – Release: 10g

The question was rather frequent – “How to create a table view with two levels of headers?”

i.e. the high-level heading as the table title heading and then another level of heading where there is one sub-heading/title for one set of columns and a different sub-heading for another set of columns

The high-level heading can be created by adding a title view for this.

And then for 2nd level of sub-heading that should be different for one set of columns and another sub-heading for another set of columns, you can achieve this by displaying the table headings as well in addition to the column headings and then set the custom table heading appropriately for the respective set of columns that require the same heading.

To do this, you need to follow a 2-step approach
1) go to the table view and edit the table view properties. Set the ‘display column & table headings’ property to ‘as separate rows’ instead of ‘only column headings’.
2) Then go to each column and set the custom table heading. Set the same table heading for all the columns that require the same heading.