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.

When running a report using eight BETWEEN clauses or less for a report that should bring back zero rows, for example

SELECT Products.Brand saw_0 FROM Paint
WHERE (Products.Brand BETWEEN ’0′ AND ’1′)
OR (Products.Brand BETWEEN ’1′ AND ’2′)
OR (Products.Brand BETWEEN ’2′ AND ’3′)
OR (Products.Brand BETWEEN ’3′ AND ’4′)
OR (Products.Brand BETWEEN ’4′ AND ’5′)
OR (Products.Brand BETWEEN ’5′ AND ’6′)
OR (Products.Brand BETWEEN ’6′ AND ’7′)
OR (Products.Brand BETWEEN ’7′ AND ’8′)
ORDER BY saw_0

the following warning is displayed:

“…

No Results
The specified criteria didn’t result in any data. This is often caused by applying filters that are too restrictive or that contain incorrect values. Please check your Request Filters and try again. The filters currently being applied are shown below ..”

This is the correct result.

However, when an additional BETWEEN clause is added (i.e nine BETWEEN clauses in total) for example

SELECT Products.Brand saw_0 FROM Paint
WHERE (Products.Brand BETWEEN ’0′ AND ’1′)
OR (Products.Brand BETWEEN ’1′ AND ’2′)
OR (Products.Brand BETWEEN ’2′ AND ’3′)
OR (Products.Brand BETWEEN ’3′ AND ’4′)
OR (Products.Brand BETWEEN ’4′ AND ’5′)
OR (Products.Brand BETWEEN ’5′ AND ’6′)
OR (Products.Brand BETWEEN ’6′ AND ’7′)
OR (Products.Brand BETWEEN ’7′ AND ’8′)
OR (Products.Brand BETWEEN ’8′ AND ’9′)
ORDER BY saw_0

the result should also return zero rows but this report returns all rows in the table.

In the query using nine BETWEEN clauses, the WHERE clause of the SQL generated does not include the BETWEEN filter conditions hence all rows in the table are returned.

It was determined to be a bug – OBI SERVER GENERATES INCORRECT SQL WHEN BETWEEN AND OR CLAUSES EXCEED EIGHT, has been raised to address this sissue.

The issue is that for the case when there are more than 8 filters, e.g 9 filters in a query, OBI Server seems to drop the filter with the result that it erroneously returns incorrect number of rows.

It always amazes me how some people don’t bother doing simple research before asking question on OTN. I’m sure that sometimes they just don’t have time to explore the issue by themselves or maybe they don’t know where they should look for information. Questions in one sentence like “My BI Server isn’t starting” or “I have ODBC error” without detailed description pop-up all the time on OTN. I’m a huge fan of metalink (i’m using metalink 3) – I’ve been able to locate some answers always instantly especially before OTN has become such a useful place as it’s now. Filing a service request is a sure way to at least get to the cause of the problem. Of course many times you would hit a BUG or a ENHANCEMENT REQUEST but at least you would know that it’s not your fault.  Through trial and error I’ve compiled a list of best practices that will help you to maximize your Oracle Support experience. Enjoy my SR tutorial:

Most important pre-SR exercise – run a simple search in Metalink / OTN to make sure that this issue haven’t been identified yet – there’s nothing worse than going through days of support e-mail back and forth and then receiving an e-mail that it’s a well-known bug / feature.

1. Make sure to give as detailed description of the problem as possible. Try to describe circumstances when it happens. If you have a question about functionality, be specific about your needs and what you are trying to achieve. If your description is very long – I suggest you type it in word and attach along with the rest of your SR.

2. Take screen shots of the error screens. Circle the problematic area or error message to help support analyst to pinpoint the problem.

3. Put your RPD, web catalog, screen shots into 1 archive. Attach lines from relevant log files (not the whole thing, but extracts). And attach it to the SR. Don’t forget to give your RPD’s admin password.I realized that most of the time, support would request those anyway, so you can be proactive about it. Why shouldn’t you do it now, rather than wait for them to ask you to submit those.

4. Be patient. You SR is important, but sometimes analysts get busy with high-priority tickets. Don’t escalate if nobody is replying within 1 day.  My experience shows that people want to help – it’s just maybe they’re taking their time to counsel with someone else and that’s the reason of the hold-up.

5. Be courteous. If there’s an update or request for more information – do your diligence and reply right away. If you receive an Oracle survey afterward, take a few minutes and fill it out. I don’t know for sure, but I’d guess that can have an impact on someone’s job. If they helped you, why shouldn’t you help out.

Do you have your favourite SR tips? Please share them in the comments.

This is an interesting one. I can see what they’ve tried to accomplish with that, however, to me it sounds as hammering nails with a golden watch. The whole point of OBIEE is to isolate users from PL/SQL and make their life easier. That’s why I get surprised that there’re always numerous questions regarding using stored procedures in OBIEE on OTN forums.

I wonder if you are using many direct query requests at all in your systems?

Someone has been investigating the Direct Query security options under the Admin menu, Manage Privileges in Analytics.

They wish to allow Web Administrators to be able to write direct query requests, and want users to only be able to run the requests and not be able to modify the SQL/Criteria tab.

They have accomplished this by setting the “Issue SQL Directly” option to Web Administrators only, the “Edit Direct Database Requests” option to Web Administrators only, and the “Execute Direct Database Requests” to All Users. This works fine. Now what they want to allow the users to modify the requests but only be able to view the Results tab. The idea here is to not let the users modify the SQL (criteria tab), but to allow them to build pivot tables and charts off of the dataset.

Is this possible? What security need to be set to accomplish this? And the answer was

This is not currently possible

Dimensional modeling is very important in OBIEE. Many mistakes happen because of incorrectly defined time dimension or other dimensions. This service request below is a demo of how an incorrectly defined key in the beginning can block the development efforts in the future.

Overview:
========

Modified rpd and added a new dimension for Key Accounts (Sales.”Key Account”.”Key Acct”) into the Sales subject area . After this change when building a report with Sales.Brand and drillingdown on Brand to show Brand_SKU ( a different dimension throws an error in Answers.

ERROR
===========-
[nQSError: 14020] None of the fact tables are compatible with the query request Brand.Brand.

STEPS
===========
By following these steps the issue can be reproduced:

1. Add dimension called Sales.”Key Account”.”Key Acct into sales subject area in rpd
2. save rpd and checkin changes
3. create answers report with a different dimension Brand.
4. Drilldown on Brand to show the next level “Brand”.”Brand_SKU”.
5. Get the error mentioned above

EXPECTED BEHAVIOR
===========
The report should work fine because the “Brand”.”Brand_SKU” was not modified at all.

Business Impact
===========
This is a very important issue that is holding up all BI projects. You cannot move forward until this issue is fixed.
Cause

The error thrown when drillingdown from Brand to Brand_SKU is caused because of incorrect definition of level keys in your rpd. The issue is, that by definition, a key is the lowest level of a table. Any higher levels are made up of aggregating the lowest or detail level. Since Brand and Product have the same detail key, there should be some consistency between the detail or lowest level of their respective hierarchies. There is not. The lowest level of the Brand hierarchy has a level key of CHILD_KEY_WID. The lowest level of the Product hierarchy has a level key of ‘SKU Nbr’ which is mapped to LVL6_KEY. It seems that the navigator is expecting the same detail key but is not seeing it. This seems to be the cause of the navigation error.

As confirmed by reviewing your rpd inhouse, the level keys are not properly defined for Brand and Product dimensions.
The levels were not defined according to best practices. The document from Engineering that lists the issues in your rpd that dont meet best practice is attached to the SR.
Solution

Please follow these steps in order to fix the error “[nQSError: 14020] None of the fact tables are compatible with the query request Brand.Brand”

1) Open rpd using Admin tool
2) Select BMM layer and delete the current hierarchy created for Brand
3) Make the following change to Product Dimension

–Dimension –Logical File –Physical File
Product Product HSAMI_SKU_DH and
HSAMI_BRAND_SKU_DH

The Product Dimension has two hierarchies, CMDYGRP and BRAND:
ALL
CMDYGRP
COMMODITY
SKU
BRAND
SKU

So now both hierarchies are constructed in one dimension over
one logical dimension table, with multiple phyiscal source tables.

4) Save changes to rpd
5) Test in Answers by creating report with Brand and Product columns
6) Navigation/Drilldown from brand and product should work fine.