Friday, November 22, 2013

OBIEE - NQSError 46036 in BI administration after patching BI Server

I had 2 cases in a week of customers complaining about getting Error when they open BI Administration Tool. The same error: [NQSError 46036] Internal Assertion: Condition m_pFile->Read(bytes, sizeof(data))==sizeof(data), file server\objectmodel\Src\SORpArchivePersistStream.cpp, line 381.
This Error means: You upgraded the Server Patch level and did not upgrade the Administration Client. In both cases customers installed the new 11.1.1.7.131017 patch, but had 11.1.1.7.0 Administration Clients.

The solution is to upgrade/reinstall the client from the patch.
The 11.1.1.7 case: In patch of 11.1.1.7.131017 there is a zip file of p17463403_111170_Generic. In side it, under 17463403\files\clients\biserver\ you will find 2 client installations: biee_client_install_x64.exe and biee_client_install.exe

Use the relevant one to upgrade the clients. If you installed OBIEE on Windows server, the Administration on the server is upgraded automatically. 

Friday, November 15, 2013

OBIEE - Calculation between results of a union





Some times the data model does not allow you to create a single query to get the results you need. then we are forced to do a Union. (actually they are 4 possible operations Union, Union All, Minus and Intersect). The default is Union. I seldom prefer Union All over Union, since it's a little faster in the DB (doesn't check the intersect).


This post is about additional column that does calculations between measures from 2 parts of the Union. In this case division.
Just as an example I will create an unnecessary Union based on SampleSales. One query with "Product Type" and "Revenue" and the other with "Product Type" and "Billed Quantity".
What I want to get is a result of "Revenue" divided by "Billed Quantity" when each of them comes from a different part of the Union.
I will show 2 ways to do it. (I personally prefer the second, but the first is considered more general and intuitive than the other). 

Option 1: separate columns and zeros in the other part of the union.

I created a union where: 
The first part is       "Product Type", "Revenue" and zero. 
The second part is "Product Type" , zero          and "Billed Quantity".

Why in the picture my Zero is Sum(0) and not just 0? Because the parallel column in the analysis is "Billed Quantity" and it's defined as aggregated measure (Sum) by default. It's always best to unite apples with apples and not oranges.
Same goes with the other half of the union:
"Product Type", sum(0) and "Billed Quantity".
 
Now I selected the "Result Columns" part and pressed on Add result Column on the left:

I was a bit lazy and named the "Billed Quantity" column B.Q. sorry.
You might think that is enough to add a column that divides the second column (Revenue) by the third column (Billed Quantity). But it doesn't work and the division result is 0 both in Table and in Pivot Table:
I'll have to do some extra work.

I added 3 result columns that exist only on the union result level. Actually only the last one was enough, but doing all 3 will explain the process.  
 I add a column that will return a total Revenue on the Union level. While I select from the column selector Sum("Revenue" by "Product Type").


what I actually see in the formula is Sum(saw_1 by saw_0). saw_0 is the first column and so on... i'll call the column "R".


Similarly, the "Q"  column is Sum("Billed Quantity" by "Product Type"), that is translated to Sum(saw_2 by saw_0).
What I actually wanted is to divide "R" by "Q". actually this column alone is sufficient. the Formula is: Sum(saw_1 by saw_0)/Sum(saw_2 by saw_0)
 Why to add the division (or multiply) by 1.0 as you see in the picture? To hint OBI it's not an Integer.
The result is (left is Table and right is Pivot):

Pivot presentation eliminates the double rows, but the division result is doubled!
You might be inclined to divide the formula by 2, so it's Sum(saw_1 by saw_0)/Sum(saw_2 by saw_0)/2.0, but the calculation in the Table will be wrong.

What is better it to change, in pivot, the aggregation rule of the division column from default  to "Server Complex Aggregate".  Now all is perfect: 

Naturally you will leave the Pivot presentation only:

 

Option 2: same column measures and description column

In this option, the measure in both parts of the union are at the same place and I have an additional column that describes the origin of the data. In my case 'R' for "Revenue" and 'Q' for "Billed Quantity" (later they will act as column names) I names this column "value".
The above is the first query in Analysis.
The above is the second query in Analysis.

The result in Pivot is:
Now for the "value" column I added a "New Calculated Item" I named 'div":
The calculated Item is $2/$1 (second value divided by first, in this case R/Q).
This is the desired result:
Now I just want to remove the misleading name OBI automatically gave to the combined Revenue and Billed Quantity (Revenue).
There are few options, in this case I will just move the "Measure Labels" to the Excluded section.

Now it's perfect.
By the way, the calculation will remain correct even if I change the sort order of the "value" column:



One more example of what can be done with calculations between result of a union, you can see it the post "OBIEE with Key / Name / Value table, the Union solution".

Friday, November 8, 2013

OEID 3.1 - (Oracle Endeca nformation Discovery 3.1) is available for download

Since 7-Nov-2013,  OEID 3.1 - (Oracle Endeca nformation Discovery 3.1) is available for download.

 
Download of OEID Studio and Integrator 3.1 is here.
Download of OEID Server 7.6 is here.
Documentation is here. At the moment the downloadable VM is still 3.0.

 The YouTube Chanel with 3.1 demos is here.


You can also see:
And the very interesting 3.1 Web Acquisition Toolkit.

They are lots of cool new options of self service data mash-up, OBIEE integration, web acquisition toolkit, Improved UI and many other features.

Can't wait to start playing with it.

P.S.

Please note the specific download needs for Integrator described in this blog: http://branchbird.com/blog/adventures-in-installing-oracle-endeca-3-1-integrator/
(and don't use folder with spaces in the name, like in any java solution). 


The VM of EID 3.1 can be downloaded here: http://www.oracle.com/technetwork/middleware/endeca/downloads/eid-vm-1968135.html please note it's without Integrator components and is "for testing purposes only".

Monday, November 4, 2013

OBIEE 11g – How to undo a right click action

You have done some right click (or other) modifications to the dashboard and want to undo it? 
Select "Clear My Customization".

An  example:

In the sample dashboard I Right click and select Keep Only on the FunPod (by default you don't have this interaction and you have to enable it in the Analysis):


The result is:

To clear it I select Clear My Customization in the Dashboard Options:


And we are back to default:



You prefer to create a custom "Clear" button like this?
Add a narrative in an analysis, with the following code:

<div class="XUIPromptEntry minibuttonOn"><a href="#" onclick="return PersonalizationEditor.removeDefaultSelection(false)"}>Clear</a><align=left></div>

Like this:

You can find it in the SampleSales demo, QuickStart dashboard, "Product Detail" page.



If you prefer you can add it as a text object in the dashboard , with the same code (just remember to mark the "contains HTML markup").