OBIEE and fake totals - weighted avg.

In OBIEE you can set a total line, on any level of table and pivot. In the example the total is at Year and Grand Total level.

The result of the total depends on the aggregation setting in the formula. If we will change the Revenue's Aggregation Rule (Totals Row) from Default(sum) to Average, we will have a different Totals result:

This is great as long as you are happy with the options you have 

in the list. 

In my case I was requested to show weighted average in the totals line.   

What does it mean?

If I sold 1000 items for $1 each, the sale is $1000.

If I sold 1 item for $1000, the sale is $1000 as well.

The average of the sale is (1000+1000)/2 = 1000

The weighted average of the sale is (1000+1000)/(1000+1)=1.998002

There is no default option of weighted average in OBIEE. To make it totally impossible, the source table was aggregated (I don't have 1000 lines of 1 but only 1 line of data). So I decided to fake it.

My original table had 4 columns: Name, code, count and value. For the first line of the above example the count is 1000 and value 1.

I wanted additional column that multiplies count and value at the transactional (not aggregated) level. I could require it as a change in the data source, but I decided to create a calculated column.

In Administration I added a column named multip where the "column source" is "derived from physical mapping".

 There I created a calculation count*value.

The aggregation of the column is Sum.

 

Next I created an Analysis based on those 5 columns.

How to add the total for each name? I selected "Combine results..." at the criteria and selected "Union All":

In the second criteria I have the following:

In the name column, the name column.

In the code column, the string 'Total'.

Then measures (I removed folder names, for clarity):

sum("count1" by "name1")

sum("value1" by "name1")

sum("multip" by "name1")/sum("count1" by "name1")

Now we need the final finishing:

I need to sort it first by name and then by code. But how can I be sure the string 'Total' will be the last one for each name? I decided to concatenate a space before each code (the formula   ' '||"code").

Last step is to change the color of cells in the total line using "Conditional Format", each time the code='Total'.

The result is:

One last problem: we can't allow dashboard user to sort the table or play with it. So at the Analysis properties

I will prevent all interactions:

OBIEE - TO_DATETIME the secret function

Did you know there are secret functions in OBIEE? TO_DATETIME is such a function.

This useful function appears in the book (Metadata Repository Builders Guide - here)  but not in the function lists in the UI (at least not on my 11.1.1.6.4). I'll upgrade to 11.1.1.6.8 soon and update here if it's less secret there.

To be fair, you can see this function in the Administration Tool, but there is no reason not to use it in analysis directly.

What does it do? It lets you control the date format of a given date & time string and converts it. No longer the tyranny of specific formats of the cast function. For example the following returned the timestamp I needed: 

to_datetime('01/01/2001 21:00', 'dd/mm/yyyy hh:mi')

So what the good book says?

This function converts string literals of dateTime format to a DateTime data type.
Syntax
TO_DATETIME('string1', 'DateTime_formatting_string')
Where:
string1 is the string literal you want to convert
DateTime_formatting_string is the DateTime format you want to use, such as
yyyy.mm.dd hh:mi:ss. For this argument, yyyy represents year, mm represents month,
dd represents day, hh represents hour, mi represents minutes, and ss represents
seconds.
 

Examples
SELECT TO_DATETIME('2009-03-03 01:01:00', 'yyyy-mm-dd hh:mi:ss') FROM
snowflakesales
 

SELECT TO_DATETIME('2009.03.03 01:01:00', 'yyyy.mm.dd hh:mi:ss') FROM
snowflakesales

Few more "secret" functions:

Aggregate Functions

FIRST - selects the first non-null returned value of the expression argument.

FIRST_PERIOD - selects the first returned value of the expression argument.

GROUPBYCOLUMN - Used only in Repository.

GROUPBYLEVEL - Used only in Repository.

LAST - selects the last non-null returned value of the expression.

LAST_PERIOD - selects the last returned value of the expression.

STDDEV_POP - returns the standard deviation for a set of values using the
computational formula for population variance and standard deviation.

 

Math Functions

EXTRACTBIT - retrieves a bit at a particular position in an integer. It returns an integer
of either 0 or 1 corresponding to the position of the bit.

 

 Conversion Functions

CHOOSE - returns the first item in the list that the user has permission to see.

INDEXCOL - exists in Admin. Tool. Can use external information to return the appropriate column for the logged-in user to see.

Database Functions

EVALUATE_ANALYTIC - exists in Admin. Tool. This function passes the specified database analytic function with optional referenced columns as parameters to the back-end data source for evaluation.

EVALUATE_PREDICATE - exists in Admin. Tool. Passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for functions with a return type of Boolean.

Hierarchy Navigation Functions

IDOF -
ISANCESTOR
ISCHILD
ISDESCENDANT
ISLEAF
ISPARENT
ISROOT
PARENT

OBIEE - Model based on one table

There are times you want to create a Subject Area or data model based on a single table in the Oracle BI Administration Tool.

If you try to take a table without any joins into the Business Model you will have an error 38133, The Logical Table is not joined to any other logical table.

They are few approaches I can think of. I'll mention one briefly and then talk about what is consider the correct one.

Approach 1. Fake join: create an extra object in Physical Layer. It might be a dummy table or an Alias of the original table. Join the them,  then move both to Business Model but expose only the original table in the Presentation. This approach will limit the usability in OBIEE, for example, you can't have Logical Dimensions this way.

Approach 2. Single Physical and multiple Logical Tables. This is usually the recommended approach. So how can we do it?

Drag your single table several times to the Business Model. One for the fact component, in this logical table we will have only the the columns we can aggregate.
For the descriptive columns we can create one or several dimension. Based on the business needs. For each such dimension we will add one more copy of the original table.
In my case I wanted 2 dimension tables:

Rename them and delete all irrelevant columns in each logical table.

   >>>>

Now create only the basic joins in Business Model Diagram (No Physical Diagram needed).

And define keys for each dimension table.

You can add logical dimension now:

All is left to do is expose it in Presentation Level. If for business needs you want it to appear as a single table, you can do it of course.

When you run queries OBIEE server will know it is the same, single physical table and creates efficient queries. For example the following analysis:

Created an efficient SQL in the DB with single access to the table:

WITH 
SAWITH0 AS (select sum(T48304.STORES) as c1,
     sum(T48304.AREA) as c2,
     T48304.COUNTRY as c3,
     T48304.MALL_NAME as c4
from 
     MALLS T48304 /* Single Table */ 
group by T48304.COUNTRY, T48304.MALL_NAME)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from ( select distinct 0 as c1,
     D1.c3 as c2,
     D1.c4 as c3,
     D1.c2 as c4,
     D1.c1 as c5
from 
     SAWITH0 D1
order by c2, c3 ) D1 where rownum <= 650001

OBIEE 11g Dashboard Background

Shahed Munir from http://deliverbi.blogspot.com/ wrote a great post on 9 January 2013 named "OBIEE 11g Dashboard Background Image or Watermark".
Here are few examples of the same dashboard page with few background:

Original:

With background:

 As Shahed says, all you have to do is add a text object anywhere in the dashboard page and set the text to be of the format:
<script type="text/javascript">var obj = document.getElementById('DashboardPageContentDiv');obj.style.background = "url('/analyticsRes/abstract.jpg') white center no-repeat fixed"</script>
If you have a small picture (like I had in the first 2 examples) you can change the "no-repeat" to "repeat", in that case you don't need the "white", "center" and "fixed".
<script type="text/javascript">var obj = document.getElementById('DashboardPageContentDiv');obj.style.background = "url('/analyticsRes/abstract.jpg') repeat"</script>

Where /analyticsRes/abstract.jpg is the image file you use.

Putting the image file at "$MW_HOME\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes" is a very nice touch, but of course you can have them anywhere you like... At the fmap location like described here or even from the internet.

For example my son is currently playing "Mario Galaxy" whenever he can.
So I could easily take this dashboard page:

Find a picture on the net, and change it to the following:

Of  course it's a User Interface disaster, but the little customer is happy.


P.S.
Just remember, as usual in these cases, when it comes to printing, the HTML print file maintains the background, while the PDF doesn't.

OBIEE 11.1.1.6.8 - bugs fixed

Since obiee 11.1.1.6.8 is available

 here is the list of bugs resolved by 11.1.1.6.8 patch

13553123: CREATE USER THROUGH SOAP FAILS WITH JAVAX.XML.WS.SOAP.SOAPFAULTEXCEPTION:

13720037: BISERVER QUERY FAILS IF AMPERSAND IS USED IN COLUMN NAME

13921691: QUERY BUILDER UNABLE TO PARSE CERTAIN QUERIES, DISALLOWING EDIT OF SUCH QUERIES

14359228: AFTER UPGRADE TO 11.1.1.6.2BP1, BI PUBLISHER LOGIN PAGE SHOWS UP IN ENGLISH ONLY

14478219: AIX WITH IBM JVM: USERS SEE JAVA.LANG.NULLPOINTEREXCEPTION UNDER HIGH LOAD

14605689: IN AIX USING DATA SET OF REMOTE WEB SERVICE RETURNS NULL DATA

14680717: BI PUBLISHER 11.1.1.6:TIMES NEW ROMAN FONT DISPLAYS WRONG FONT IN RTF OUTPUT

15869175: QUERY BUILDER TRUNCATES THE SECOND RANGE OF BETWEEN CLAUSE IN WHERE CONDITION

11824623: NLS:FR:DOUBLE PERCENT SIGN AND WRONG DATA IN PIVOT TABLE ON FRENCH LOCALE

13065019: USAGE TRACKING ISSUE AFTER APPLYING PATCH 12925206- QUERY NOT INSERTED IN LOG

13802667: COMMA DELIMITED CSV FORMAT FOR AGENTS/IBOTS.

13949566: MASTER DETAIL VIEW INTERACTION CONTEXT IS LOST

14262536: OBIEE SSL DOES NOT WORK FOR ACTIONABLE INTELLIGENCE/ACTION LINK

14496280: OBIEE 11.1.1.5.0 CREATING HUGE CORE DUMP FILES

14509257: UNWANTED EXPANDATION OF BACKGROUND OF GAUGE CHART/BULB

14532515: SINGLE ACTION LINK POPUP ISSUE

14597361: OBIEE 11.1.1.6.2: UNABLE TO DELETE HEADER FROM NEWLY ADDED COMPOUND LAYOUT

14781767: QA: PROMPTS: RUNTIME PROMPTS IS NOT WORKING IN FIREFOX 16

14832439: BLOCKING ANALYSES BASED ON CRITERIA NOT WORKING ON RESULTS TAB

14832723: BLOCKING ANALYSES IS BROKEN AFTER SAVNG AND REOPENING THE ANALYSIS

15856175: NO METADATA AUTOSAVE IN NQSMODIFYMETADATA AND NQSIMPORTMETADATA PROCEDURES

15879674: WHEN A VALUE WITH THE '&' SIGN IS CHOSEN, SCORECARD WATCHLIST RESULTS ARE NOT BE

15886643: QA: CALC ITEM DISPLAY 0 OR NAN IN TABLE IF EDIT MEASURE FORMULA, EXCLUDED COL,

15891959: PRESENTATION SERVER CRASH

15899791: DRILLDOWN ADDS FILTER FOR THE EMPTY STRING ALTHOUGH THE VALUE IS NULL OR SPACES

15905237: CUSTOM COLUMN HEADING DOES NOT REFLECT IN THE FILTERS VIEW

15908937: OBIEE AUTHENTICATION PROCESS CHANGED AFTER UPGRADE FROM 11.1.1.6.0 TO 11.1.1.6.4

15917103: DISABLED LTS DURING AFTER UPGRADE FROM OBIEE 10 TO OBIEE 11 CANNOT BE ENABLED

15934601: NQSERROR22032 WHEN SORT ON COMBINED RESULTS BASED ON UNIONS,INTERSECT,MINUS

15934766: BIPS FAILS TO START WITH DNSUTILS::DNSNAMELOOKUPFAILED

15954886: GCMAP/GCWAP: ERROR IN CAMPAIGN LOADS AND LAUNCHES

15958318: 11.1.1.6.5 -EXPORT TO EXCEL/PDF DISPLAYING VIEW DISPLAY ERROR-LENGTH URL EXCEEDS

15961449: REQUEST VAR DEFAULT VALUE SET BY REPOSITORY VARIABLE DOES NOT WORK CORRECTLY

15963008: DIFFERENT RESULTS ARE SHOWN WHEN ACTION LINKS ARE USED WITH CONDITION

15966182: CSV FORMAT OF REPORT AND AGENT DELIVERED REPORT ARE NOT THE SAME OUTPUT

15998971: QA:PINNED KPI KEEPS SPINNING WHEN OPENING A KPI WATCHLIST

16059256: SAWSERVER MAY ALLOCATE VERY LARGE ALLOCATION WITH ODBC ERROR

16169478: ADMIN TOOL AND JOB MANAGER VERSION NOT UPDATED AFTER APPLYING 11.1.1.6.8 PATCHES

16219059: JOB MANAGER GETTING [NQSERROR: 86015] [NQSERROR: 77027] A FATAL ERROR OCCURRED

Known Bugs with This Patch

14228519: ADMINTOOL CLIENT MISSING ALL FILES UNDER EXTENDER DIRECTORY IN CLIENT INSTALLER

14244844: NAVIGATE TO URL ACTIONS BASED ON ENCODED URLS MAY RESULT IN DOUBLE ENCODING

15854192: ERROR MESSAGE RETRIEVED WHEN TRYING TO CREATE AGENT WITH EMAILS AS RECIPIENTS

15854218: ODBC ERROR WHILE CREATING AN AGENT

15983904: 11.1.1.6.7 ADMIN TOOL CLIENT SHOWS INCORRECT VRSN IN THE CLIENT INSTALLER WINDOW

13873434 QA: IE:UNABLE TO RENAME COMPOUND LAYOUT

14158156: MEMORY SPIKE DURING THE COMPILATION PHASE FOR QUERIES WITH LARGE EXPRESSIONS

14743304: FIX FOR BUG 14170374 REQUIRES ADDITIONAL CHANGE TO OPMN.XML

14771782: FIX FOR BUG 14070348 DOES NOT WORK IN CLUSTERED ENVIRONMENT

14772000: UNABLE TO SCROLL AS IE 8.0 PERFORMANCE POOR FOR MAX ROWS IN DASHBOARD

13974353: CONNECTION STRING USED TO ACCESS LEASING TABLE IS INCORRECT

obiee 11.1.1.6.8 is available

Please note, since OBIEE 11.1.6.11 is available (see here) you should consider it instead of 11.1.1.6.8.


Patch 11.1.1.6.8 is available from Oracle support site (support.oracle.com). 

The first part of the patch is Patch 16094198). 

It's the most updated / latest version (actually patch bundle) of OBIEE, installed on top of the basic 11.1.1.6.0 version.

I didn't see a Windows 32 bit server version of this patch. Personally, I think it's a good idea.
Also note that while the 7th patch of client Installers exists on Linux X86 (and not only X86-64), the only Linux version of other patch components is X86-64.

It can be installed on OBIEE 11.1.1.6.0, 11.1.1.6.1, 11.1.1.6.2, 11.1.1.6.2 BP1, 11.1.1.6.4,11.1.1.6.5, 11.1.1.6.6 and  11.1.1.6.7 .

As usual, the real patch has 7+1 parts. The installation steps for the first 7 are similar. I recommend reading the first readme – patch 16094198 readme file. To be totally correct it’s not the first 7, but first 6 since patch number 7 contains only client tools (BI Administration, MapViewer…) and to the best of my knowledge are not relevant to non-Windows installations.

The Oracle Business Intelligence 11.1.1.6.8 patchset comprises a number of patches:

Patch	Abstract
16094198	Patch 11.1.1.6.8 (1 of 7) Oracle Business Intelligence Installer
16094192	Patch 11.1.1.6.8 (2 of 7) Oracle Real Time Decisions
16080520	Patch 11.1.1.6.8 (3 of 7) Oracle Business Intelligence Publisher
16094205	Patch 11.1.1.6.8 (4 of 7) Oracle Business Intelligence ADF Components
16094211	Patch 11.1.1.6.8 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x PLEASE note, this is platform specific patch (Windows 64, Linux X86, X86-64, IBM AIX Power 64, Solaris SPARC 64, Solaris X86-64, HP-UX Itanium)
16067995	Patch 11.1.1.6.8 (6 of 7) Oracle Business Intelligence PLEASE note, this is platform specific patch (Windows 64, Linux X86-64, IBM AIX Power 64, Solaris SPARC 64, Solaris X86-64, HP-UX Itanium)
16097082	Patch 11.1.1.6.8 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer

Additionally there is the patch number 8, it is named JDeveloper Patch, but it is for OBIEE as well. It's the same patch you used if you installed 11.1.1.6.2 BP1 or later. So if you already installed it, I see no reason to do it again.

Patch 13952743 - JDeveloper patch -   - RUP3 MLR4.3  CUMULATIVE: BASED ON JDEVADF_11.1.1.6.2_GENERIC_120329.1447.6268.1 (last release 11.1.1.6.2)

The installation steps are same as 11.1.1.6.2 BP1 as we described here.

Please note, that readme tell you to do the following backups:

• The ORACLE_HOME\bifoundation\server directory
  
• The ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository
  
• The ORACLE_BI_HOME\bifoundation\jee\mapviewer.ear\web.war\WEB_INF\conf\mapViewerConfig.xml, if you have modified it. This last bullet is relevant to those who configured the OBIEE Maps (described, in Hebrew here).

I'm not aware of any new features in 11.1.1.6.8.

You can see a list of bugs fixed by this release in the following:

OBIEE 11.1.1.6.8 - bugs fixed

The table above was updated on 5-Feb. (The blogger who copied this post to his blog is welcome to copy the updates as well).

OBIEE Auto complete prompts

This time I will talk about Auto Complete Prompts. This feature was introduced in 11.1.1.6.0 and is covered in System Administrators Guide chapter 18.4 (18 is a great chapter to read anyway). Simple search in the net will find few posts available on the issue here, here and here, for example. So why do I bother? First to remind myself of the feature (I always forget it), second to go a little deeper into the syntax.

Bellow is the default text I copied from the text book (chater 18.4).

In the end of this post I have the code I use on my system.  

 So what we should do? 

 Go to instanceconfig.xml that is located in MiddleWareHome/instances/instance2/config/OracleBIPresentationServicesComponent/coreapplication_obips1. There you need to check for <Prompts> section (it probably doesn't exist). Directly under the general <ServerInstance> section (not within any other) there should be:

<Prompts>
<MaxDropDownValues>256</MaxDropDownValues>
<AutoApplyDashboardPromptValues>true</AutoApplyDashboardPromptValues>
<AutoSearchPromptDialogBox>true</AutoSearchPromptDialogBox>
<AutoCompletePromptDropDowns>
<SupportAutoComplete>true</SupportAutoComplete>
<CaseInsensitive>true</CaseInsensitive>
<MatchingLevel>MatchAll</MatchingLevel>
<ResultsLimit>50</ResultsLimit>
</AutoCompletePromptDropDowns>
</Prompts>

Next restart, at least, the presentation services of the BI Server (the guide advises to "Restart Oracle Business Intelligence").

Now you enter the dashboard and check the prompt with anticipation and ... probably nothing! Why?

They are one or two more things  to update.

Probably in your user My Account definitions

The Prompts Auto-Complete is set to Off.

Turn it On.

Now it should work, unless in the Dashboard Properties you deliberately turned it off:

So you have the Prompts Auto-Complete feature working now. Lets go back and have a closer look at the instanceconfig.xml modifications we made.

They are 4 parts in the Prompts section:

<MaxDropDownValues>256</MaxDropDownValues>
This part of the Prompts definition is general and has nothing to do with Auto-Complete. It's in the documentation, because the description is general "Configuring Prompts" and not "Configuring Auto-Complete Prompts". It's especially redundant since 256 is the default value :(

   MaxDropDownValues Specifies the maximum number of choices to display in the following locations:

• In choice lists in dashboard prompts.
• In the Available list of the Select Values dialog That is displayed when the user clicks the Search link in a prompt and the More link to display additional choices.
• In the Available list of the Select Values dialog when the user performs a search in that dialog.

<AutoApplyDashboardPromptValues>true</AutoApplyDashboardPromptValues>
Again, nothing to do with Auto-Complete and since the default is True... No need to have this line in unstanceconfig file.

In simple terms, this options allows you to use the 11.1.1.6 option of hiding the apply button if you want.

    AutoApplyDashboardPromptValues Specifies whether to display various fields, as described in the following list:

If True, then

• The Show Apply Button and Show Reset Button fields are displayed on the Edit Page Settings dialog.
• The Prompts Apply Buttons and Prompts Reset Buttons fields are displayed in the
• Dashboard Properties dialog.
• The Prompt Buttons on Current Page option is displayed on the Dashboard builder’s Tools menu.

If False, then

• The Show Apply button and Show Reset button fields are not displayed on the  Edit Page Settings dialog.
• The Prompts Apply Buttons and Prompts Reset Buttons fields are not displayed in the Dashboard Properties dialog.
• The Prompt Buttons on Current Page option is not displayed on the Dashboard builder’s Tools option.

<AutoSearchPromptDialogBox>true</AutoSearchPromptDialogBox>
This option is relevant to Auto-Complete as well, but since the default is True anyway, no need to have it in instanceconfig file.

AutoSearchPromptDialogBox Specifies whether search results are displayed and
highlighted when the user types the search parameter (without clicking the Search button).

 The last part is:

<AutoCompletePromptDropDowns>
         <SupportAutoComplete>true</SupportAutoComplete>
         <CaseInsensitive>true</CaseInsensitive>
         <MatchingLevel>MatchAll</MatchingLevel>
         <ResultsLimit>50</ResultsLimit>
</AutoCompletePromptDropDowns>
Now we are in business.  AutoCompletePromptDropDowns is the section dealing with Auto-Complete prompts.

Lets check all 4 components.

SupportAutoComplete enables or disables the auto-complete functionality of prompts. A setting of True turns auto-complete on, which means that the Prompts Auto-Complete field is displayed and is set to On in the My Account dialog and in the Dashboard
Properties dialog.
A setting of False turns auto-complete off, which means that the auto-complete fields in the My Account and Dashboard Properties dialogs are not available.

Since the default for non Exalytics machines is False. You must have this line.

CaseInsensitive specifies whether the auto-complete functionality is case-insensitive. If set to True, case is not considered when a user enters a prompt value such as "Oracle" or "oracle." If set to False, case is considered when a user enters a prompt value, so the user must enter "Oracle" and not "oracle" to find the Oracle record. The system recommends the value with the proper case.

The default is True, since it what you want in most cases, you can omit this line.

MatchingLevel specifies whether the auto-complete functionality uses matching to find the prompt value that the user enters into the prompt field. These settings do not apply when the user accesses the Search dialog to locate and specify a prompt value. Use the following settings:

• StartsWith - Searches for a match that begins with the text that the user types. For example, the user types "M" and the following stored values are displayed: "MicroPod" and "MP3 Speakers System".
• WordStartsWith - Searches for a match at the beginning of a word or group of words. For example, the user types "C" and the following values are displayed: "ComCell", "MPEG Camcorder", and "7 Megapixel Digital Camera".
• MatchAll  - Searches for any match within the word or words.

Since the default is MatchAll, you can omit this line in most cases.

ResultsLimit specifies the number of matching values that are returned when the auto-complete functionality is enabled.

The default is 50. You should have this line only if you want a different value.

So on my computer I have only the following change in instanceconfig.xml file that is located in MiddleWareHome/instances/instance2/config/OracleBIPresentationServicesComponent/coreapplication_obips1.

<Prompts>
<AutoCompletePromptDropDowns>
<SupportAutoComplete>true</SupportAutoComplete>
<ResultsLimit>30</ResultsLimit>
</AutoCompletePromptDropDowns>
</Prompts>

There is no problem with the full list of parameters that appear at the beginning of this post. They are just unnecessary and create additional clutter in instanceconfig file.

Anyway, the result looks like this:

Bear in mind, there is a reason auto-complete is default in Exalytics only. If the search takes too much time to run. It annoys the user.