OBIEE Blog

Yes, I’ve done this mistake more than once, usually unwilfully. Using varchar columns in fact table.

Everyone knows that using anything but numeric facts is a bad idea. However, there’re situations when it might be deemed necessary, especially when dealing with reporting at the detailed level. For example, client once wanted Condition Grade for previous year using AGO function in Fact Table. There was a need for previous year reporting using AGO and TODATE functions. Also, there was a complex function evaluating variance in condition. Since there’re limited aggregation functions available for varchars in OBIEE  (FIRST and LAST..I don’t countCOUNT – since it doesn’t really do it for that report). And you need to set some aggergation, because otherwise, you can’t use those column in time-series functions since those require aggergated metrics.

Well, what do you know. Query performance for the whole dataset was very slow (it’s supposed to be, since nobody should get inital report of more than a page) . About 20 minutes. Setting up bitmap indexes reduced it to 4 minutes. And setting textual facts’ aggregation setting to “None” reduced that time further acceptable 20 seconds. However, time-series functionality was lost.

So, please don’t use textual facts – they won’t make you any good:

a) You can’t really get any meaning out of them except on the most detailed level. How do you measure A vs B ?

b) You run into performance issues.

What to do:

I guess it reallty depends on the situation and your gut feeling.  Ideally, you want to convert letter grades to numeric alternative.  Such as A=4, B=3, etc. then you can make all kinds of fun analysis with it.  If that’s not an option, then you should try  pushing that data to your dimension (using an aliased combo table if you don’t have that dimension). I understand there might be more viable solutions. I’m going to find my post and discussion on OTN.

Direct Database Request in OBIEE using Essbase causes [nQSError: 46008] Internal error: File .\Src\SQXDGEssbaseCAPI.cpp, line 1003. (HY000)

Applies to:
Business Intelligence Answers Option – Version: 10.1.3.2.1 to 10.1.3.4.1 [1900] – Release: 10g to 10g

Symptoms

When running a query in Direct Database Request in OBIEE against Essbase, the following error occurs:

ERROR
———————–

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. [nQSError: 46008] Internal error: File .\Src\SQXDGEssbaseCAPI.cpp, line 1003. (HY000)

The query works in Answers screen but not in Direct Database Request screen.

STEPS
———————–
By following these steps the issue can be reproduced using Essbase as data source:
1. Create a report in Answers
2. Verify that the query returns results
3. Now copy the physical SQL of the above query from the query log (section ‘…sending query to database…’)
5. Copy it into the Direct Database Request SQL statement field
6. Verify that the columns in SELECT statement are displayed in the Results Column section
7. Run the results and the above error is generated

Cause

This issue has been caused by Bug 6869282 “DIRECT DATABASE REQUEST THROWS ERROR WHEN RUNNING AGAINST ESSBASE”.

There is an issue with the code that checks for FROM clause in the SQL. The parsing code expects a space before and after the FROM clause.

Solution

The behavior is reproducible in the latest version of OBIEE, 10.1.3.4.1.

The recommended workaround is in the MDX  query to enter space before the “FROM” clause in the physical SQL and execute the query.

A single space before the FROM clause resulted in the following error (the error moves from line 1003 in the error described above to contrary to line 1050 in error below):

Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. [nQSError: 46008] Internal error: File .\Src\SQXDGEssbaseCAPI.cpp, line 1050. (HY000)

Adding two spaces in front of the FROM clause so that the FROM clause is aligned with the text in the upper line of the query, resolved the error and it was possible to run Direct Direct Database requests in OBIEE on Essbase.

Bug 6869282 “DIRECT DATABASE REQUEST THROWS ERROR WHEN RUNNING AGAINST ESSBASE” is planned for resolution in the next release of OBIEE.

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.