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=0x86fcd00, Index=2) at threadallocator.cpp:443
443 { (gdb) bt
#0 0xb427f9b2 in samem_details_800::ThreadAllocator<0>::Allocate
(this=0x86fcd00, Index=2) at threadallocator.cpp:443 @ #1 0xb42784df in samem_details_800::ThreadAllocator<0>::allocate
(this=0x86fcd00, 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=0x87f7bac, __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
at thirdparty/include/stlport/stl/_alloc.h:514
#7 0xb72198c6 in _SASSTL::_Rb_tree
at thirdparty/include/stlport/stl/_tree.h:243
#8 0xb673169d in _SASSTL::_Rb_tree
at thirdparty/include/stlport/stl/_tree.c:366
#9 0xb6730c6d in _SASSTL::_Rb_tree
at thirdparty/include/stlport/stl/_tree.c:412
#10 0xb6730499 in _SASSTL::set
this=0x87f7bac, __x=@0xb0e23264) at
thirdparty/include/stlport/stl/_set.h:137
#11 0xb672fe6b in RqNode::AddRqNodePtr (this=0x87f7b88, pRqNodePtr=0x8d999e8)
at server/Query/Optimizer/Request/Src/SQORRqNode.cpp:432
#12 0xb672e34e in SmartRqNodePtr (this=0x8d999e8, rhs=@0x8d995a0) at @ server/Query/Optimizer/Request/Src/SQORRqNode.cpp:55
#13 0xb66a7d17 in RqDerivedColumnReference (this=0x8d999b8, rhs=@0x8d99570, @ bNewIDs=false, bDeepCopy=true)
at server/Query/Optimizer/Request/Src/SQORRqExpr.cpp:1418
#14 0xb66a8898 in RqDerivedColumnReference::DeepCopy (this=0x8d99570, @ bNewIDs=false)
at server/Query/Optimizer/Request/Src/SQORRqExpr.cpp:1525
#15 0xb672e748 in RqNode (this=0x8d977b0, rhs=@0x8d97368, 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=0x8d977b0, rhs=@0x8d97368, @ bNewIDs=false, bDeepCopy=true)
at server/Query/Optimizer/Request/Src/SQORRqExprCond.cpp:1299
#19 0xb66d6e88 in RqExprCondIsNull::DeepCopy (this=0x8d97368, bNewIDs=false)
at server/Query/Optimizer/Request/Src/SQORRqExprCond.cpp:1342
#20 0xb672e748 in RqNode (this=0x8d8f878, rhs=@0x8d8f430, 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.
This has been bugging me for a while. Now, I know what the problem was.
When are Subject Areas and View Privileges visible in the Admin > Manage Privileges link?.
Definitions:
The functionality is as follows:
1. A Subject Area is stored in a webcat once a user has accessed it via Answers.
2. The Subject Area will be visible in the Admin >Manage Privileges link only if a user has accessed the Answers link.
3. Subject Areas will persist for the life of the webcat, but will not be accessible via the Admin > Manage Privileges link after the webserver services have been stopped and restarted. They will only be accessible once a user (any user) has accessed the Answers page in a webclient session.
4. The behavior described in Step 3 ensures that potentially archived, deleted or renamed subject areas are not visible for setting privileges.
5. The View privileges will also not be accessible via the Admin > Manage Privileges link after a webserver service recycle until and unless a user (any user) has accessed it in a webclient session.
a. When you run a request, the following privileges become visible in the Admin > Manage Privileges link
View Compound
View Filters
View Narrative
View Nested Request
View Pivot Table
View Logical SQL
View Table
View Ticker
View Titleb. When the user clicks on “Customize View”, the following privileges become visible:
View Create Segment
View Chartc. When the user clicks on Views Tab, the following privileges become visible:
View Question
View Column Filter
View Global Filter
View Image
Something interesting I found while browsing OBIEE docs. This should be incredibly useful to force users select enough columns for the report.
How to not allow the execution of the request and show an appropriate error message to the user If request contains any of the following columns: (Sales Amount, Sales Amount With Tax, Ticket Count), and if request does not contain at least one of the columns (Date, Month, Year, Day of Week)
This is possible via the Blocking Requests Based on Formula functionality using a custom js file
This is explained in Oracle Business Intelligence Presentation Services Administration Guide > Administering Oracle BI Answers > Blocking Requests in Answers
To locate the documentation please navigate to OTN: http://www.oracle.com/technology/documentation/bi_doc.html
Then drill on “Oracle Business Intelligence Enterprise Edition Documentation”
> View Library (for the required version) > Documentation tab > Oracle Business Intelligence Presentation Services Administration Guide
The documentation does not state where to place the js file.
*Using oc4j as the web server, you need to put the custom .js in “OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla” in order for the fmap: reference in the CustomMessages xml file to find it (also need to bounce both OBIPS & oc4j).
*Using IIS, you need to put the .js file in “OracleBI\web\app\res\b_mozilla”, not “OracleBIData/web/res” as the doc states (and bounce OBIPS & WWW Pub Service).
Also, you can use a full network path reference for the src argument in the custom xml file – e.g.:
<script language=”javascript” src=”\\mylabmachine\OracleBIData\web\res\myblocking.js” />
That way, you can place the .js file anywhere – in this example in the “OracleBIData\web\res” folder, not “OracleBI\web\app\res\b_mozilla”.
Even though, I believe that OBIEE Answers is a very fine piece of software, sometimes, I’m annoyed about certain OBIEE bugs and quirks. Here’re some of my favorites (least favorite OBIEE Answers limitations).
1. You can not use prompts on a calculated column in answers. Workaround: you can create this calculated column in the rpd in the business layer and than add that field to the presentation layer. This also adheres to best practices (put as many metrics as you can to Business Model Mapping layer).
2. When a prompt is created based on an SQL Condition (in “Show” “Sql Results” Option), if a filter is applied when this Prompt is used the condition created originally is lost. This is most likely an OBIEE bug and I’m not aware of any workarounds.
3. Inability to display the beginning date and the end-date in the report based on the operator “betweenâ€, because with this operator, you can’t define a presentation variable. In this case, the Between Date Prompt isn’t working properly and is a known OBIEE Answers bug. The workaround is to create a second report. This will have the date column used by the report, only twice. For the first date column, change the column formula to min(date) and for the second date column change the column formula to max(date). Then use a narrative view something like ‘Report for period beginning @1 and ending @2′.
4. In Answers, when creating a filter, you sometimes see an incorrect behavior in the Edit Filter popup where the wrong values are loaded for a column. This behavior occurs when clicking the ‘All Choices’ link in the filter popup, for a column and then cancelling it. When you then navigate to another column to create a filter and select ‘All Choices’ you get the dropdown values for the previous column being loaded. No workaround, however, you can refresh the same filter again to get correct dropdown values.
5. Download Data and Download to Excel work differently. OBIEE is downloading all the columns present in Report Crieteria page of Answers part (even if they’re not included in the Dashboard). The workaround is to use Download to Excel.