Wednesday, February 25, 2015

OBIEE - Selecting columns dynamically - part2, simple Prompt

In previous post I talked about column selectors. They are not enough in complex cases, for example: 
  • Single selection of columns for several reports.
  • Using the same column selection in several columns of the same analysis.
  • Dependency between selection of columns (for example, I can select "Target Revenue" in measures only if "Year" selected in one of the dimensions.)
In this post I will create an example where selecting a column from prompt would influence 2 columns in analysis.

First I create a Dashboard Prompt:

All I want is to create a presentation variable, I will name P_col1, so I add a new Variable prompt (and not a Column Prompt).
There I will select Presentation Variable and it's name (P_col1) the Label and the user input format. Here you can select whatever you like, just make sure user can select only one value. I selected a "Radio Buttons" option. 
Next I add Custom Values. It is important that those Custom Values will be exactly the same way those columns appear in the column Formula. For example this is the Formula of the "Offices"."Department" column:

and this is the Prompt definition (I selected the Year, Product Type and Department columns):

The last thing I did was to select a specific value as default selection (this is important).

This is the resulting prompt:

Now i will create an Analysis that uses the above described presentation Variable and place them both in a Dashboard.
First I will create an equivalent to a column selector:
In the formula I create a presentation variable with a default value:

the result in my case is: @{P_col1}{"Time"."Per Name Year"}
I'll add few more columns:

And this is the result:
I could do exactly the same with column selector. Now, for fun,  lets add something I couldn't do with column selector: I'll add a measure that will show "Discount Amount" if the "Per Name Year" was selected and "Billed Quantity" otherwise. 
So my extra column will have the formula:
 CASE WHEN '@{P_col1}{"Time"."Per Name Year"}'='"Time"."Per Name Year"' THEN "Base Facts"."Discount Amount" ELSE "Base Facts"."Billed Quantity"  END
This part I couldn't do with column selectors.

Now I'll place both in a dashboard:

 You might have noticed the first column name didn't change after I selected the "Product Type". We didn't have this issue with column selectors. 
To fix it I'll place the Presentation Variable in the Column Heading:

Now the result header is dynamic:

While developers might be happy with the described above, users and UI / UX people might not share my enthusiasm with headers and Prompt Values such as "Time"."Per Name Year" and column header such as Measure2.
They tend to express their feelings:

How to make them happier? That would be covered in the next post.

Sunday, February 22, 2015

OBIEE - Selecting column dynamically - part1, column selector

In OBIEE, in Analysis, we can use column selector to dynamically change a column in the analysis. Unfortunately, in complex cases, when you have to use the selected column few times, or you have some complex dependency between selected columns, we have to move the selection to prompts.
In the following 5 posts I will describe:
  1. How to use column selectors. (this post)
  2. How to use column names in prompts with presentation Variables.
  3. How to use better looking column names in prompts.
  4. How to remove column from analysis with column selector or prompt. 
  5. How to create analysis with one prompt to cover multiple columns.

Lets start with column selector.

I have a simple analysis with "Per Name Year", "Product Type", "Revenue" and "Billed Quantity":

Now I want to let the user select other columns instead of "Product Type" and "Billed Quantity". Al I have to do is add column selector in OBIEE:

After pressing "Edit View" on Column Selector, I can edit it:

Mark the desired column and select other options from the list on the left.

Please note: 
  • We can change both the column properties and function of each selection.
  • The clear choices option.
  • While we can place a non-measure column in a measure column selector it's not always a good idea ("Organization", that replaces "Billed Quantity", in my case).

Now the user can select a column. OBIEE actually replaces the column in the select statement, so the result is efficient. 
In the following case we replaced "Product Type" with "Department". 

But when I try and replace "Billed Quantity" with "Organization", I have problems in places that treat that column as measure, such as Pivot and Graph, but not in other, such as Table:

If we decide to add labels to columns:

The Label Position option becomes relevant:

Unmarking the automatically refresh option, lets the user select few options in column selector and then press OK.

One more logical result: if selected in column selector, the column is not controlled  via the Criteria:

If this is so good, why do I need other options? For complex cases.
I will talk about that in the next posts.

Thursday, February 19, 2015

Oracle Big Data Discovery is available

Oracle Big data Discovery (BDD) is available.
BDD, the visual face of Hadoop was declared at Strata Conference today. 

You can find it's main page here: That includes data sheet, executive briefing and video. You can read there about Capabilities.

The documentation is here.
The YouTube channel is here.
The Learning library is here.

BDD is installed on the Hadoop Cluster and currently works on the latest Cloudera Distribution (5.3).
You can find the installation on edelivery (under Business Intelligence).

It's time to have fun with BDD.

Few additional resources:

Richard Tomlinson from Oracle  giving 5 part introduction posts: "Unmask Your Data. The Visual Face of Hadoop". Part 1, 2, 3, 4, 5

RitttmanMead blogging on BDD:
  1. Introducing Oracle Big Data Discovery Part 1: “The Visual Face of Hadoop”.
  2. Introducing Oracle Big Data Discovery Part 2: Data Transformation, Wrangling and Exploration.
  3. Introducing Oracle Big Data Discovery Part 3: Data Exploration and Visualization.
  4. Some Oracle Big Data Discovery Tips and Techniques

BranchBird blog:
  1. Bringing Data Discovery To Hadoop – Part 1.
  2. Bringing Data Discovery To Hadoop – Part 2.
  3. Bringing Data Discovery To Hadoop – Part 3
  4. Big Data Discovery – Custom Java Transformations Part 1
  5. Big Data Discovery – Custom Java Transformations Part 2
And you can always try BranchBird

Rene Kuipers guide to BDD installation (should not replace the installation guide): Installing Oracle Big Data Discovery 1.0.0

Tuesday, February 17, 2015

Oracle Big Data day in Israel 11-3-2015

ב11 למרץ, יתקיים יום Big Data של אורקל בAvenue.
התכנים מעניינים, ההרשמה חינם, המרצים מוצלחים וארוחת צהריים על חשבון אורקל. אני אישית מתעניין במיוחד בהרצאה של 11:30.

זה הקישור להרשמה.

אלו הפרטים:

Sunday, February 15, 2015

OBIEE - Few cache related links for me

There are many blogs about cache in OBIEE. Just a reminder for myself about the post by OBIEE Revisited " Different ways to Manage Cache in OBIEE!!" and OBIEE A B C: "How to Seed & Purge the OBIEE query cache".
I might add few more here later. Too much work at the moment.