OBIEE Blog

If you had problems using EVALUATE function -  please look at the following SR that I just found. Apparently, the work-around is to use CAST function.

QL Request: SET VARIABLE QUERY_SRC_CD=’Report’,SAW_SRC_PATH=’/users/administrator/last_working_day test 1′;SELECT “Task Type”.Type saw_0, “Task – Creation Date”.Date saw_1, EVALUATE(’num_of_working_days(%1,%2)’,”Task – Creation Date”.Date,”Task – Last Updated Date”.Date) saw_2, “Task – Last Updated Date”.Date saw_3 FROM “SPS – Operation” ORDER BY saw_0, saw_1, saw_2, saw_3

The query fails and the following error occurs.

ERROR
———————–
Query Status: Query Failed: [nQSError: 46035] Datatype(DOUBLE PRECISION) nullable(1) unicode(1) external char(0) conversion to datatype(TIMESTAMP) nullable(1) unicode(0) external char(0) is not supported.

These columns are from W_DAY_D. These are type date which the function is expecting. The function returns a number.

BUSINESS IMPACT
———————–
The issue has the following business impact:
Due to this issue, this is holding up resolution of a critical issue on the delivery of the project – which plans to go live next Friday (30th October).

Cause

Comments
——–
The cause of this issue is that EVALUATE expects to return data with a data type that is the same as the data
type of the parameter.
This has been raised in : EVALUATE FUNCTION SHOULD RETURN DATA TYPE OF FUNCTION

The Bug explains that when you use this function:

EVALUATE(’my_function(%1)’, logical_table.ROWID)

It will set the data type of the column to INT because ROWID is also INT. However my_function does not return an INT but a REAL.

============================
The above is what you are experiencing and why you get the error suggesting converting double precision to timestamp is not supported

[nQSError: 46035] Datatype(DOUBLE PRECISION) nullable(1) unicode(1) external char(0) conversion to datatype(TIMESTAMP) nullable(1) unicode(0) external char(0) is not supported.

Proposed Solution

Customer was asked to do an explicit CAST to set the datatype to the datatype of the value that will be returned by my_function.

So something like

CAST(EVALUATE(’my_function(%1)’, logical_table.ROWID) AS FLOAT)

The above resolved the issue.

Hello everyone.

I just realized that this post is inspired by Venkat.

I’m finally posting a solution to the problem I’ve identified a while ago related to a specific Pivot Table View. I think the final outcome should look something like this image:

To get this result – I’ve used the “New calculated column” menu on the pivot table (circled in red):

OBIEE pivot table menu

Then, what you really need to have in the Columns is the most granular level column (sounds redundant – in this case it’s a Date column). I used Excel to quickly generate formulas, such as this for Q1.  It’s really easy to generate these formulas below. I hope it’s pretty self-explaining – you add things up in a granular level – this way you can combine things – that you wouldn’t be able to combine otherwise:

‘1/1/2007′+’1/2/2007′+’1/3/2007′+’1/4/2007′+’1/5/2007′+’1/6/2007′+’1/7/2007′+’1/8/2007′+’1/9/2007′+’1/10/2007′+’1/11/2007′+’1/12/2007′+’1/13/2007′+’1/14/2007′+’1/15/2007′+’1/16/2007′+’1/17/2007′+’1/18/2007′+’1/19/2007′+’1/20/2007′+’1/21/2007′+’1/22/2007′+’1/23/2007′+’1/24/2007′+’1/25/2007′+’1/26/2007′+’1/27/2007′+’1/28/2007′+’1/29/2007′+’1/30/2007′+’1/31/2007′+’2/1/2007′+’2/2/2007′+’2/3/2007′+’2/4/2007′+’2/5/2007′+’2/6/2007′+’2/7/2007′+’2/8/2007′+’2/9/2007′+’2/10/2007′+’2/11/2007′+’2/12/2007′+’2/13/2007′+’2/14/2007′+’2/15/2007′+’2/16/2007′+’2/17/2007′+’2/18/2007′+’2/19/2007′+’2/20/2007′+’2/21/2007′+’2/22/2007′+’2/23/2007′+’2/24/2007′+’2/25/2007′+’2/26/2007′+’2/27/2007′+’2/28/2007′+’3/1/2007′+’3/2/2007′+’3/3/2007′+’3/4/2007′+’3/5/2007′+’3/6/2007′+’3/7/2007′+’3/8/2007′+’3/9/2007′+’3/10/2007′+’3/11/2007′+’3/12/2007′+’3/13/2007′+’3/14/2007′+’3/15/2007′+’3/16/2007′+’3/17/2007′+’3/18/2007′+’3/19/2007′+’3/20/2007′+’3/21/2007′+’3/22/2007′+’3/23/2007′+’3/24/2007′+’3/25/2007′+’3/26/2007′+’3/27/2007′+’3/28/2007′+’3/29/2007′+’3/30/2007′+’3/31/2007′

Calculated item OBIEE

The same logic works for Q2,3,4. For individual dates, it would be the same idea:

And for months, it’s the same logic:

To sum up  – by using the “calculated item” feature of pivot table view – you can create reports with mixed granularity on them. The pros are that you’ll be able to support legacy views (in some instances it’s absolutely critical to mimic existing view). The cons are that it involves some manual entry / data manipulation and that it’s not very flexible (I couldn’t find out how to use variables in this example).

Last thing – it seems as first column gets added on the left and the consequent ones to the right – keep this in mind while designing this view – since I’ve noticed you can’t really manipulate with the locations of the calculated columns.

I saw this post on OTN and decided to take a look:

“I need to create a prototype for the following Pivot Table. I’m putting a view as I would see in Answers.
————-|9/1/2009|9/2/2009|9/3/2009|MTD|Q1|Q2|Q3|Q4|YTD
________ |_______________________________________________
Customer |Some metric (for example Income $)

I’ve been unable to come up with a way to do this in Answers without creating additional columns (such as Income YTD, Income MTD, Income Today, Income Yesterday, etc.). Even with that, I can’t figure out what’s the best way of arranging pivot columns in Answers to create this report. Does Answers allow for mixed granularity like this in pivot?”

“The question was how to transpose various hierarchiecal levels on x-axis? Not homgenous, but different ones. For example, if I dragged columns “Year” and “Qtr” to the Columns section in Pivot I get the following:
2009
—-
q1|q2|q3|q4

What I want is to have it liks this:
2009|q1|q2|q3|q4

It seems as it’s not possible right now. But I’ll be glad if you can correct me”

Someone suggested that this wasn’t possible. Original author suggested using only tab “Measure labels” in Columns. Call appropriate metrics as Q1,Q2,Q3, etc. (use filter for them) – and use them in measures section.”

However, I thought there should be another solution. I’ve contacted metalink and was told that this wasn’t a technical, but implementation issue and as such services of Oracle Services’ were necessary.

So far, i wasn’t able to come up with anything better than using custom columns. This method isn’t ideal – since it’s very cumbersome to implement and would fail relatively easy.

Main challenge is hiding some values in Pivotal columns without affecting aggregate’s total.

I’d welcome any suggestions, since I can’t throw this out of my mind.

If you want to see correct calculations – then a BI Server Restart might be required after you change Aggregation Rule for a column from SUM to AVG (or in any way).

I noticed that problem on Grand-Total calculations – it just wouldn’t perform correct aggregation for the column after RPD change in online mode (with Aggregation Rule – Default).

After services restart, OBIEE would perform correct grand-total aggregation.

I found an interesting bug in metalink. I wish there were more specifics as to how complex the report was. I just had a similar Assertion error yesterday which I tried to solve by increasing STACK size. However, it seems as it’s possible to crash BI server with longer reports.

When a custom report is executed on Windows the following error is received: -

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46036] Internal Assertion: Condition FALSE, file .\NQThreads\SUGThread.cpp, line 515. (HY000)

However, on Linux the OBI Server crashes with this typical Stack Trace from the Core file generated: -

0 0xb427f9b2 in samem_details_800::ThreadAllocator<0>::Allocate
(this=0×86fcd00, Index=2) at threadallocator.cpp:443
443 { (gdb) bt
#0 0xb427f9b2 in samem_details_800::ThreadAllocator<0>::Allocate
(this=0×86fcd00, Index=2) at threadallocator.cpp:443 @ #1 0xb42784df in samem_details_800::ThreadAllocator<0>::allocate
(this=0×86fcd00, nBytes=20, nIndex=2,
pFile=0xb4a171dc “thirdpartysource/STLport-4.5/src/nqnodealloc.cpp”,
nLine=10) at threadallocator.cpp:966
#2 0xb4286098 in samem_details_800::Manager::Allocate (this=0xb429cac0, @ Bytes=20,
pFile=0xb4a171dc “thirdpartysource/STLport-4.5/src/nqnodealloc.cpp”,
nLine=10) at manager.cpp:469
#3 0xb4283dae in samem_800_allocate_dbg (Bytes=20, pFile=0xb4a171dc @ “thirdpartysource/STLport-4.5/src/nqnodealloc.cpp”, nLine=10)
at memoryallocator.cpp:160
#4 0xb49fbfb8 in NQNodeAlloc::allocate (__n=20) at @ thirdpartysource/STLport-4.5/src/nqnodealloc.cpp:10
#5 0xb721a1f3 in _SASSTL::allocator<_SASSTL::_Rb_tree_node
>::allocate (this=0×87f7bac, __n=1)
at thirdparty/include/stlport/stl/_alloc.h:372
#6 0xb721a02e in
_SASSTL::_STLP_alloc_proxy<_SASSTL::_Rb_tree_node*,
_SASSTL::_Rb_tree_node,
_SASSTL::allocator<_SASSTL::_Rb_tree_node > >::allocate @ (this=0×87f7bac, __n=1)
at thirdparty/include/stlport/stl/_alloc.h:514
#7 0xb72198c6 in _SASSTL::_Rb_tree, _SASSTL::less, @ _SASSTL::allocator >::_M_create_node (this=0×87f7bac, @ __x=@0xb0e23264)
at thirdparty/include/stlport/stl/_tree.h:243
#8 0xb673169d in _SASSTL::_Rb_tree, _SASSTL::less, @ _SASSTL::allocator >::_M_insert (this=0×87f7bac, __x_=0×0, @ __y_=0×8d895b8, __v=@0xb0e23264, __w_=0×0)
at thirdparty/include/stlport/stl/_tree.c:366
#9 0xb6730c6d in _SASSTL::_Rb_tree, _SASSTL::less, @ _SASSTL::allocator >::insert_unique (this=0×87f7bac, @ __v=@0xb0e23264)
at thirdparty/include/stlport/stl/_tree.c:412
#10 0xb6730499 in _SASSTL::set, _SASSTL::allocator >::insert @ (
this=0×87f7bac, __x=@0xb0e23264) at
thirdparty/include/stlport/stl/_set.h:137
#11 0xb672fe6b in RqNode::AddRqNodePtr (this=0×87f7b88, pRqNodePtr=0×8d999e8)
at server/Query/Optimizer/Request/Src/SQORRqNode.cpp:432
#12 0xb672e34e in SmartRqNodePtr (this=0×8d999e8, rhs=@0×8d995a0) at @ server/Query/Optimizer/Request/Src/SQORRqNode.cpp:55
#13 0xb66a7d17 in RqDerivedColumnReference (this=0×8d999b8, rhs=@0×8d99570, @ bNewIDs=false, bDeepCopy=true)
at server/Query/Optimizer/Request/Src/SQORRqExpr.cpp:1418
#14 0xb66a8898 in RqDerivedColumnReference::DeepCopy (this=0×8d99570, @ bNewIDs=false)
at server/Query/Optimizer/Request/Src/SQORRqExpr.cpp:1525
#15 0xb672e748 in RqNode (this=0×8d977b0, rhs=@0×8d97368, bNewIDs=false, @ bDeepCopy=true)
at server/Query/Optimizer/Request/Src/SQORRqNode.cpp:146
#16 0xb66a0d3c in RqExpr::RqExpr$base () at @ server/include/Query/Optimizer/Request/SQORRqNode.h:77
#17 0xb66d0f68 in RqExprCond::RqExprCond$base () at @ server/include/Query/Optimizer/Request/SQORRqList.h:33
#18 0xb66d6c58 in RqExprCondIsNull (this=0×8d977b0, rhs=@0×8d97368, @ bNewIDs=false, bDeepCopy=true)
at server/Query/Optimizer/Request/Src/SQORRqExprCond.cpp:1299
#19 0xb66d6e88 in RqExprCondIsNull::DeepCopy (this=0×8d97368, bNewIDs=false)
at server/Query/Optimizer/Request/Src/SQORRqExprCond.cpp:1342
#20 0xb672e748 in RqNode (this=0×8d8f878, rhs=@0×8d8f430, bNewIDs=false, @ bDeepCopy=true)
at server/Query/Optimizer/Request/Src/SQORRqNode.cpp:146
Cause
It appears that due to the complexity of the Expressions in the Answer Columns of the custom Report, the Expression Builder makes several recursive calls which eventually increases the Stack Size of the Thread until it reaches its maximum and it throws an Asertion Error.

On a Windows environment we have a check of the ‘LowStackCheck’ parameter which is not present in Linux and therefore it crashes the OBI Server giving a ’sigsegv’ error.
Solution

Currently, there is no solution. The workaround is to re-design the report so that the Expressions are less complicated (e.g. Creating Measures that sum up at various combinations of Dimension Levels that allows the Users to avoid creating the complex Formulas in Answers and performs well)

This looks like a major change in the code is required to fix this type of behavior and therefore we will not be able to fix this until at least our 11.x release.