Sunday, July 27, 2014

OBIEE - Fines and Payments (or Orders and Order Lines) with correct totals

The problem is a "popular" one. You have 2 facts with different levels of aggregation. It's often Orders fact and Order Lines fact (where there are Order Total in the Orders fact and Lines values in Lines Fact that might not the same due to shipping or general discount). In my case this is Fines fact and Payments fact. In this case they sums often don't match since they might be only partial payment of the fine. 
 We can't solve it as the regular OBIEE "2 facts with common dimensions" solution since we have specific non-aggregate columns for each fact (Fine code, fine date, payment number, payment date...)

I blogged about the solution of the problem on the Analysis level here. The customer insisted on natural solution in the repository model. This post is about the modeling solution. 

Initially I was sure I'll find a solution in the extended Sample Sales available here. There is a subject area "X5 - OLTP (Fusion Order)". As always, I was sure would be perfect. This time they didn't cover it yet, and I had the default, wrong results:

In the example, the first order total was multiplied by the number of lines, so we have 102 instead of 51, as desired.
My first attempt was to divide by the number of lines in the model. I didn't mind seeing the relevant portion of the total fine in each line, as long as the total was correct. It was almost perfect, but the default aggregation of the column wasn't sum, so it forced the user to change it manually.

Lets start from the begging.
I have 4 tables: Fines, Payments, Dates and People.

I'll duplicate (alias) the Fines and Payment so we will have fact and dimension for each.
I'll also duplicate Dates, so we will have Fine Date and Payment Date.


In the Business Model I created a unified Fact of Fines an Payments:

In Payment data source I also joined the Fines (I'm not sure this step is needed).


For each of the duplicates of the facts: Fine Details and Fine Payment details I created a simple level based dimension of totals and details only:

Now comes the tricky part. For FINE_SUM I defined the Aggregation to be based on dimensions, with SUM for the Fines Dimension and AVG for Others.


The result is working fine.




Please note, the order here is critical. That is the difference between Sum(AVG(xxxx)) and AVG(SUM(xxxx)). 





Tuesday, July 15, 2014

OBIEE BUNDLE PATCH 11.1.1.7.140715 AKA 11.1.1.7.8 (?) is available

Please note OBIEE Bundle Patch 11.1.1.7.151020 is available. You might prefer it.

OBIEE 11.1.1.7 new general bundle patch (number 8?), 11.1.1.7.140715 is available in Patch 18818588 it can be installed on any previous 11.1.1.7 release.

I had no problems downloading the patch this time. If you have problems, try an add-on to Firefox "DownloadThemAll".

The Patch is platform specific and is available for:
  • HP-UX Itanium
  • IBM AIX on POWER Systems (64-bit)
  • Linux x86
  • Linux x86-64
  • MS Windows (32-bit)
  • MS Windows (64-bit)
  • Oracle Solaris on SPARC (64-bit)
  • Oracle Solaris on x86-64 (64-bit)
You can read the readme file here.

Unlike 11.1.1.6 patches, this patchset is downloaded in a single big file+one bonus patch. So just for the record (since you don't need to download them separately):
Patch Abstract
16913445 Oracle Business Intelligence Installer (BIINST) - same as previous patches
18841070 Oracle Business Intelligence Publisher (BIP)
18657616 Enterprise Performance Management Components (BIFNDNEPM) - same as previous
18841084 Oracle Business Intelligence Server (BISERVER)
18841078 Oracle Business Intelligence Presentation Services (BIPS)
17300045 Oracle Real-Time Decisions (RTD) - same as previous patches
16997936 Oracle Business Intelligence ADF Components (BIADFCOMPS) - same as previous patches
18841089 Oracle Business Intelligence Platform Client Installers and MapViewer

As usual there is a bonus patch: Patch 16569379  (1111770)- Dynamic Monitoring Service patch. It is platform dependent. If you installed it with 11.1.1.7.1 or later, that's enough. It's the same.
In the ReadMe here there are special instructions for upgrading from 11.1.1.6.X to 11.1.1.7.140715 at  "Section 3.1: For Customers Moving From 11.1.1.6.x to 11.1.1.7.140715". DON'T upgrade before you read and understand it.



Bugs fixes 

BI Publisher

 
New bug fixes in this patch are shown in the following list:
12646762: BIP11.1.1.5.0 IS MISSING WINDOWS SPECIFIC SCRIPT OF BIPCATALOGUTIL

13487560: QA: DATE FILTERING TOP N/BOTTOM N FILTERS DOES NOT WORK CORRECTLY

14784942: CREATEOBJECT THROWS INVALIDPARAMETERSEXCEPTION FOR OBJECTTYPE XSB/XDO/XSS/XDM

15893446: INTERMITTENT BIP REPORT CORRUPTION OR ERRORS WHEN RUN ON AIX

16239014: EVENT TRIGGER RETURNING NO ROW CAUSES SCHEDULED REPORT JOB TO FAIL

17168401: SCHEDULE TRIGGER FAILS THE SCHEDULED JOB

17169975: 500 ERROR ON CLICK OF REPORT JOB NAME IN REPORT JOB HISTORY PAGE

17307903: NOTIFICATION FOR SCHEDULED JOB HISTORY SHOW NULL SERVLET CONTEXT

17481776: UPGRADE ASSISTANT CREATES TYPE MISMATCH DUE TO MISSING DATATYPE ATTR

17560693: UPGRADE ASSISTANT DOES NOT PROPAGATE PROPERTY TAGS FROM 10G TO 11G.

17648888: BUG IN BI PUBLISHER 11G DATA MODEL: ANY CONSTANT STRING IN SQL CONTAINING ':' IS

17895566: BI SCHEDULER FAILS TO START IF THE ADMIN SERVER IS NOT RUNNING

18463773: QA: NO DATA FOUND ERROR WHEN PASS DASHBOARD PROMPT VALUE WITH APOSTROPHE

18504952: ZQF : POST REL-7 UPGRADE DATES ARE RETURNING WITH A -7HR OFFSET

18678153: CHANGE FOLDERNAMES WITH BLANK CAUSES LOST OF PERMISSION


BISERVER

New bug fixes in this patch are shown in the following list:


16632759: CRASH IN NQSSERVER (ABORTED)

16775551: CRASH IN NQSSERVER (SEGMENTATION FAULT)

16827646: INIT BLOCK PROXYLEVEL FAILS AFTER UPGRADE TO OBIEE 11G CAUSING ACT AS FAILURE

18149291: CANNOT SET UP CLIENT SIDE ORACLE SQL*NET TRACING ON OBIEE SERVER

Presentation Services

New bug fixes in this patch are shown in the following list:

13872457: DIMENSION UNDER VARY BY COLOR HIDES MEASURE VALUES IN MOUSEOVER DISPLAY

16718354: CASE WHEN STATEMENTS DO NOT WORK IN 11G WHEN THERE IS NOT A SPACE BEFORE END

16878581: QA : EXPORT TO PDF THROWS 'ERROR 500--INTERNAL SERVER ERROR'

16968969: SORT ICON IS HIGHLIGHTED IN THE OTHER COLUMN

16984599: NLS:DOUBLE QUOTE NEEDS TO BE ESCAPED IN TAB DELIMITED FORMAT CSV OUTPUT

17029364: OBI 11.1.1.7: MULTIPLE COLUMN SELECTORS REMOVES OTHER COLUMN SELECTOR VALUES

17198861: ASSERTION FAILURE:NEXPRS >= 2 IN FILTEREXPRSQLVISITOR FOR NULL VALUE IN PROMPT

17388356: DASHBOARD PROMPT SETTING A PRESENTATION VARIABLE DOESNT WORK WITH SCORECARD VIEW

17398212: ALL NODES ARE SELECTED WHEN CHOOSING "MOVE ALL" IN DASHBOARD HIERARCHY PROMPT

17427667: UNABLE TO CREATE SECOND VIEW BASED ON ESSBASE CRITERIA.

17458081: ASENG: IBOTS RESULTS IN NO DEVICES FOUND ERROR & SKIPPED DELIVERIES R7P24

17707496: DMS METRICDUMP UNZIP BIEE-DATA.ZIP TO /TMP/BIEE-WORKSPACE BUT NO CLEAN UP .

17741427: PERCENTAGE SIGN NOT SHOWING IN PIVOTED CHART

17836865: CEAL:SCOTIA:SELECTED VALUES ARE MISSING FROM THE RIGHT SIDE IN HIERARCHY PROMPT.

17841675: GRAPH LEGEND DISPLAYS ONLY 1 COLUMN OUT OF A GROUPED FILTER

17913225: LINK- IN A SEPARATE WINDOW" DROPDOWN DOES NOT WORK.

18044957: NO USER CREATED IN SQL AUTHENTICATOR CAN USE IMPERSONATION

18106558: EXPORT METADATA DICTIONARY - ADMIN TOOL GENERATES EXTRA " IN TREEINDEX.XML

18106800: "[NQSERROR: 37005] TRANSACTIONAL UPDATE FAILED" APPLYING PATCH IN ONLINE MODE

18202689: QA:RGRN:UNDEFINED APPEARED FOR COLUMN DB PROMPT LABEL WHICH IS INCORRECT IN IE 9

18249432: AD-HOC ADD RELATED PARENTS, KEEP ONLY OPERATIONS RESULTS IN UNEXPECTED (NO DATA)

18340605: CAN NOT SAVE AGENT WITH IE 7

18363001: CATALOG VALIDATION FAILS WITH ERROR "ROLE "AUTHENTICATEDUSER" NOT FOUND..."

18393317: DO NOT TRIM SPACES WHEN USERS TYPE IN VALUES IN FILTER DIALOG

18427805: EDIT REPORT TO OPEN IN "CRITERIA" TAB INSTEAD OF "RESULTS" ALWAYS EDIT

18459235: QA:ASSERTION FAILURE ERROR THROWN WHEN DOING PREVIEW WITHIN DB PAGE.

18474956: RECIPIENT IS NOT NOTIFIED WHEN EMAIL IS IN RECIPIENT'S LIST

18495535: BUG 16913399 NOT FIXED BY ONE-OFF PATCH: PERMISSIONS->USERS->SEARCH ERRORS OUT

18511402: UNABLE TO NAVIGATE TO TABS AS THE USER DOES'NT APPEAR IN SPECIFIED USER.

18548153: ABSOLUTE POSITION IN DASHBOARD SECTION MIXED WITH COLUMN BREAK

18556600: IE: WHEN FILTER A VALUE WHICH IS SPACE IT GIVES NO RESULTS.

18629694: AFTER EDIT SAVE AGENT FAILURE FOR INVALID PATH VIA IE 7

Remember to upgrade the BI Administration Clients with the 18841089 patch content that comes in the bundle patch (not relevant for Windows servers).

You might want to look at note OBIEE 11g: How To Improve the Functionality of Exporting Data from Analyses, Dashboards, and other Oracle BI Presentation Catalog Objects into Microsoft Excel with OBIEE 11.1.1.7.131017 and Later (Doc ID 1595671.1)

Monday, July 14, 2014

OBIEE - Comparing performance to peers - next step

In the post OBIEE - Comparing performance to peers I covered 2 options to create an analysis where we compare the performance of particular group member, with the entire group. We used 2 methods, Option 1: with aggregation on the entire group (and selection step) and Option 2: with filter function on the group member.
I promised to try and improve it. They are few thing I'd like to improve:
  • Compare the member to the rest of the group and not entire group (subtract  the member data from the rest).
  • Comparing values or % of total and not Ranking. 
  • Limiting presentation by minimal % and not specifically top 10.
The first option described here is not connected to the other 2.

We start with this:


Compare to "All the group" or "the Rest of the group"

In the post we compared the revenue of member (specific Company) with Revenue of the entire group (all members). In small groups the influence of each member might be significant. In our example there are only 3 companies. I believe our comparison would be better if we remove the specific company Revenue from the total.
We can subtract the member value from the group total to achieve it. This should be done with the second option - the filter function. Why?

Because the first option of the previous post would end up with problematic result.

Changing from this:
The basic columns: Company, Product, Revenue

Calculations:


  • Sum(Revenue by Product) = the total Revenue for each Product
  • Rank (Sum(Revenue by Product)) = The Ranking of each Product Revenue for the entire data
  • Rank(Revenue by Company) = The Ranking of each Product Revenue for each Company

We move to this option, that doesn't work:

The basic columns: Company, Product, Revenue

Calculations:


  • Sum(Revenue by Product)-Revenue = the total Revenue for each Product of the rest
  • Rank (Sum(Revenue by Product)-Revenue) = The Ranking of each Product Revenue for the entire data of the rest of the group
  • Rank(Revenue by Company) = The Ranking of each Product Revenue for each Company
Since there is a problem with the Total Ranking order, where are Ranks 2 and 3?


 In the previous example the grand total for each company of a specific product was the same so the relevant Ranks were the same. Now since we subtract different Revenues, the Ranks are per combination of product and company!

This problem does not happen in the Filter Function version, because we subtract the same value for each product. 

So we replace this:
  • Product
  • FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' ))
  • Rank(FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' )) )
  • Revenue (we don't need the sum(revenue by Product since there is no company column)
  • RANK(Revenue)
  • The relevant case statement


With this:
  • Product 
  • FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' )) 
  • Rank(FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' )) )
  • Revenue - FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' ))
  • RANK(Revenue - FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' )))
  • The relevant case statement



Remember to change the analysis filter to the updated Rank<11.






Comparing % of total and Not Ranks

Comparing Ranks is a problematic practice. In many cases we might miss valuable information this way. For example: the top 3 products are responsible for 90% of the revenue. So the step from rank 3 to rank 4 might be only 1 in ranks, but is a very big in the actual revenue. They are 2 options I prefer:
  • Comparing revenue to the average revenue per product of the others.
  • Comparing to % of Total products.
The second option is better in our case since the companies might be of different sizes.
To make things readable I will start from the previous post example of comparing to all group members.

I'll add 2 columns:

This is the Option 1, Selection Step solution:

  • The % of Revenue per product of all Companies: 100*sum(Revenue by Product)/sum(Revenue)
  • The % of Revenue per product of current Company: 100*Revenue/sum(Revenue by Company)

This is the Option 2, Filter Function Solution:

  • The % of Revenue per product of all Companies: 100*Revenue/sum(Revenue)
  • The % of Revenue per product of current Company: 100*FILTER("Revenue" USING ("Company" = 'Genmind Corp' ))/Sum(FILTER("Revenue" USING (""Company" = 'Genmind Corp' )))



Now we can decide what is the percentage difference we want to emphasize.
For example I'll create a column that subtracts the 2 new columns and mark the cases there is a difference of over 0.4% (or anything that works for you).



Now we can hide/exclude/remove any column we don't need or don't want to show.


Using minimal % instead of top 10

The selection of top 10 is somewhat random, since not all data is evenly distributed like in our case.I'd rather use a specific % of total. For example 4.5%.






Compare to "All the group" or "the Rest of the group" - again

Now we can return to the selection steps option again, since we don't depend on Rank any more. The calculations of the previous type work fine with percentage options.

Sunday, July 13, 2014

Oracle Webcast "Oracle Big Data Breakthrough:Connect All Your Data with SQL" 15-Jul-2014

Oracle is planning an interesting (for me) webcast on 15-Jul-2014: Oracle Big Data Breakthrough:Connect All Your Data with SQL. The link with additional info and time is here. I always enjoy hearing  Andy Mendelsohn.




Announcing Your All-Access Pass to Big Data

What if a single Big Data breakthrough could enable you to:
  • Reuse the skills you already have to access all your Hadoop data?
  • Leverage existing applications to query Hadoop?
  • Keep new data as secure as your existing enterprise data?
Join us on July 15 to learn how Oracle is breaking down barriers to deliver business value from Big Data, faster.

Oracle Executive Vice President Andrew Mendelsohn will unveil Oracle’s revolutionary new solution for seamlessly integrating Hadoop, NoSQL and Oracle Database.

Oracle’s complete Big Data solution will enable you to:
  • Discover and predict faster
  • Simplify access to your data
  • Govern and secure all data

Tuesday, July 8, 2014

OBIEE - Comparing performance to peers

I was asked about an analysis where I compare the list of top 10 products of a company ( or sales person or anything else) with the top 10 off all the others. The idea is to see the top products of peers in compare to me.
I will describe here the solution I was asked about and in the next post some of my ideas how this could be done (better?).
(Done on OBIEE 11.1.1.7.140527).

Option 1

I'll use Sample Sales and compare Rank of Revenue by all companies with Rank of Revenue of specific company.
The Analysis:
The basic columns: Company, Product, Revenue
 Calculations:

Sum(Revenue by Product) = the total Revenue for each Product
Rank (Sum(Revenue by Product)) = The Ranking of each Product Revenue for the entire data
Rank(Revenue by Company) = The Ranking of each Product Revenue for each Company

Now I was requested to mark the cases where Rank(Revenue by Company) is not in top 10 while it is in top 10 in Rank (Sum(Revenue by Product)). This way I can see the products I can improve with.

So I created a column with the following cumbersome case statement:
case 
when rank(sum(Revenue by Product))<11 and rank(Revenue by Company)<11 THEN 'OK'  when rank(sum(Revenue by Product))<11 and rank(Revenue by Company)>10   then 'XXX' Else '' end
And used Conditional format so this column will be red when it's value is XXX.

The result is (I left the Original Column Header on purpose):

 We can add filter to the Analysis, to see only top 10 rank(sum(Revenue by Product)).

The result will be OK:

Now, I was told, it's very nice but we want to filter the company with dashboard prompt, so each Company will get it's own results by default.
THIS is a problem.
If we try a regular filter, we will loose the data of the other companies, and see only the selected one. 

What can we do?
Use selection steps!
Why? Because Selection steps are executed only after the general Query. 

Select the Company in List:

Edit "Stat with all members" 

Change the Action to "Start with all members" and mark "Override with prompt".

Now when we call the analysis from a dashboard, it is all working fine:


Option 2


Noy Dekel from Vision.BI remarked that this could be achieved with the Filter Function an presentation variable, as well:

This time we don't have the Company column:
We have only:
  • Product 
  • Revenue (we don't need the sum(revenue by Product since there is no company column)
  • FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' )) 
  • RANK(Revenue)
  • Rank(FILTER("Revenue" USING ("Company" = '@{PV_COMP}{Genmind Corp}' )) )
  • The relevant case statement
Since the filter formula is Presentation Variable based, we can change it using a Prompt.




There is one small difference between the results: the second option always forces us to select by default a specific company, while the first allows us to see all values by default.


You are welcome to continue to OBIEE - Comparing performance to peers - next step


Thursday, July 3, 2014

OBIEE - Adding "Export Entire Dashboard to Excel' link to dashboard

In the past I wrote about OBIEE - Custom Export to Excel link. Lets talk about dashboards...

As you might know in OBIEE 11.1.1.7 there is an option to export Entire Dashboard or Specific Dashboard page to excel. It exports the current page /dashboard. Here we will describe how to add such link to an Analysis or Dashboard and select the Dashboard manually.
(Done with OBIEE 11.1.1.7.140415)

This is what you can see on your OBIEE 11.1.1.7:

If you check the code behind it, you will see:

That is (with extra line breaks):
a class="NQWMenuItem NQWMenuItemWIconMixin" name="html"
href="javascript:void(null)"
onclick="return saw.dashboard.exportToExcel('/users/weblogic/_portal', 'page 1', false);"
id="popupMenuItem"
tabindex="0"
role="menuitem"
aria-label="Export Entire Dashboard">
<table class="MenuItemTable" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td class="MenuItemIconCell">
<img alt="" src="res/sk_blafp/catalog/exporttoexcel_ena.png">
</td>
<td class="MenuItemTextCell">Export Entire Dashboard</td>
</tr></tbody></table></a>

Since I prefer minimal code, it's:
<a href="javascript:void(null)" onclick="return saw.dashboard.exportToExcel('/users/weblogic/_portal', 'page 1', false);">Export Entire Dashboard</a>

Where:
  • "Export Entire Dashboard" is just the string we present, the one with Hyperlink. 
  • '/users/weblogic/_portal' is the path to dashboard. In this case it's "My Dashboard" of user weblogic.
  • 'page 1' is the page name in Dashboard.
  • false in "saw.dashboard.exportToExcel('/users/weblogic/_portal', 'page 1', false)" means to export Entire Dashboard, while true would export only 'page1', or any other page of the second parameter.
Now we can add it to Narrative or text in the Dashboard... And that's all.

Narrative example in an analysis:


Here is an example of exporting only page1 from dashboard test1:
<a href="javascript:void(null)" onclick="return saw.dashboard.exportToExcel('/shared/test1', 'page 1', true);">Export test1 Dashboard</a>

Adding text object to dashboard with that content:

Running it from the Dashboard:
 

The excel file is ready:


The top link in the above example is slightly more interesting since it exports the entire "My Dashboard" of user weblogic (naturally there is a security issue for non weblogic users) from another dashboard. It's the same code we have seen in the narrative above.

It works as well:


You might ask, how can I make it dynamic, so any users ca see his own "My dashboard"?
Well lets create an analysis with 1 column: VALUEOF(NQ_SESSION.USER)
And the following Narrative would do the job:
<a href="javascript:void(null)" onclick="return saw.dashboard.exportToExcel('/users/@1/_portal', 'page 1', false);">Export Entire Dashboard1</a>
(all I did was to replace "weblogic" in previous narrative with @1)



The next question is how to do the same with GO URL? I have no idea at the moment.