Avoid Non-numeric facts!

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.

Date dimension in BI Apps 7.9.5 is not checked to be Time dimension. Do you know if there is any particular reason why it is so?

I found an answer to something that bothered me for a while.

In setting up a Time dimension, there are requirement of the physical Tables that can be used, for e.g. Time Dimension table cannot join any physical table other than the fact table Time Dimension sources on the physical layer cannot be part of a complex join In the BM layer any logical Dimension defined as a Time Dimension cannot be part of any other logical tables . In the current 7.9.5 rpd the Date Dimension does not meet some of these requirements So if we were to designate the out of the box OBIApps rpd Date dimension as time dimension we can not have complex join to w_day_d in the physical layer. Currently out of the box OBIApps rpd has several complex join defined with the w_day_d_common alias table which is the detailed level LTC in the Date logical dimension. This issue causes error if you then try to check the time dimension flag and do a consistency check.

A comment on this from one of our Consultant as below “apps 7.9.5 was not ready to convert it to a true time dim. If you check the checkbox you will see all of the consistency errors. Part of it is due to the date fields being used in the inner joins of LTSs on other Dims and Facts which is a no-no for the Time Dim. It is easier just to create your own Time Dim that is used for the Time Series formulas. Or you could configure time series the old school way.” However the OBI 7.9.6 apps does have the Date Dimension checked as a Time Hierarchy. Also an additional response from Engineering as below

==================== The simple answer was that we didn’t use the OBIEE Time Series functions in BI Apps 7.9.5, and used them in BI Apps 7.9.6, hence configuring the Date dimension as a time dimension. In BI Apps 7.9.5, none of the new OBIEE Time Series functions were used. This was because the functionality was immature and had many bugs. These bugs were fixed in OBIEE 10.1.3.4.1 and BI Apps 7.9.6 has uptaken and used the new Time Series functions quite a bit. So for correct functionality of BI Apps 7.9.6, OBIEE 10.1.3.4.1 is a must.

This is interesting. My favorite part is “In BI Apps 7.9.5, none of the new OBIEE Time Series functions were used. This was because the functionality was immature and had many bugs. These bugs were fixed in OBIEE 10.1.3.4.1″ – soif you’re on 10.1.3.2. – you should upgrade as soon as possible (although this might mean months). I knew time dim was broken for a while – it’s just an official confirmation.

Nominate your Organization for Oracle BI & EPM Excellence

It’s Tuesday, and there was a big tragedy in DC that everyone knows about. I ride on Red Line every day  – and now I’m starting to think that I’m not riding in front car anymore. However,  it’s quite possible that I’d have been riding the same train.

Official info about BI nomination is here – http://www.oracle.com/webapps/dialogue/dlgpage.jsp?p_ext=Y&p_dlg_id=7687732&src=6642150&Act=335

Probably the most interesting category for blog’s readers is this:

Using Oracle BI EE Plus to deliver intelligence and analytics from data spanning multiple sources and applications.

The criteria’s don’t seem too difficult – I suggest you at least forward the link to your manager – supervisor.The only thing that is not clear is whether the award is for 1 person, 10 people total, or 10 teams. Please let me know if you can figure it out.

Summer was slow for many business intelligence blogers – Adrian Ward posted something a humorous albeit interesting story –   “Don’t Try This At Home Kids” . John Minkjan came up with some cool scripts, such as Getting All Users and Roles from RPD

Other interesting findings – I found a post about all OBIEE and legacy products versions – http://blogs.oracle.com/bi/2009/06/ships_in_the_night.html by Darryn Hinett.

Something I just learned by an accident –  Venkatakrishnan J (one of the most respected Oracle BI experts and an author of authoritative OBIEE blog http://oraclebizint.wordpress.com/)  joined Rittman Mead consulting. Congratulations and Best wishes for all parties involved. You can read about it here – http://www.rittmanmead.com/2009/06/22/my-first-post-here-joining-rittman-mead/

Please stay safe

Direct Database Request in OBIEE using Essbase causes [nQSError: 46008] Internal error

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.

Business Intelligence Consultancy

I decided to come up with my own list of an ideal Business Intelligence consulting company. I know this is rudimentary, however, I’ve been thinking about various factors that help to distinguish between good and bad consulting companies. Partly, my motivation to write this post is coming from a surprising number of new companies that all claim to be business intelligence experts. Don’t get me wrong – I’m not bashing all new companies – I just have a grudge against fake companies / consultants that trigger a general distrust in BI technology software suite. Here’s a list in no particular order:

Consultancy that is successful:
– has adequate financial resources to maintain payroll and fight invoice lag
– has leaders who have excellent understanding of what’s involved in successful BI projects from the points of staffing, project management, and client relations
– rewards its consultants accordingly, deals with them fairly. it’s helping to increase performance and productivity and in the long run will bring more business from satisfied customers
– it is realistic – doesn’t promise to over deliver… doesn’t underbid the project intentionally and then “blackmails” the client
– actively participates in conferences and trade shows
– has a good informational web site that it’s using to communicate to prospective clientele

Those are few things that I’ve thought about. Please feel free to add more in your comments.