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.