OBIEE - Selecting columns dynamically - part5, the single prompt solution

 OBIEE - Selecting columns dynamically - part5, the single prompt solution

If you read posts 2,3,4 of this "OBIEE - Selecting columns dynamically" saga, you can create a set of prompts where each prompt controls a column and enables the user to select the column formula or decide not to show it at all. 

Something like this:

But the users might ask you to have a single prompt with friendly column names, in which they can select all the relevant columns for the analysis (or 2 prompts, one for attributes and one for measures).
Something like one of those 2:

This is what this post talks about.

What I intend to do?

1. Create the above prompt to allow the user to select desired columns.
2. Create a hidden set of prompts where I split the above prompt to column names and have additional hidden prompts for their actual formula.
3. Create an analysis where the column values are either the selected column or the None / Hidden  column from solution 2 of post 4 of this series.  

As a result while user doesn't select any columns, the analysis is mostly empty and as columns are selected it grows.  

For example in my analysis there is only one default measure column, so all I see is:

But if I select few values in the prompt:

I get the following result of the same analysis:

How to achieve it?

To get such a list I will create a table of prompt values, in this case, column names. Since I was lazy, I created it by a set of "select from dual", instead of actual table (don't do it in real life):

With 4 columns:

• Column Name - A user friendly representation of the column (example 'Year')
• Column Code - The formal Column definition in Analysis formula (example ( "Time"."T05 Per Name Year")
• Column Type - In case I decide to split the prompt between attributes and measures and deal with special formats such as date.
• Column Sort  - This will allow me to control the selected values in prompt. (better explanation later in this post).
  

Next I created a specific Business Mode just for the Prompt table and use one of the options for single table Model described here.  In my case, I duplicated the table at the Business layer and defined the copy as Dimension (after adding a key). Later I used only the "Fact" part in the Subject Area (See presentation layer on the left, Business Model on the right):

With that, I can create a prompt based on the column col_name. It can be a check boxes:

Or  a choice list:

  
Now I have a Presentation Variable (P_col_list) with all the column "friendly" names I want.
It's value might be something like this:'Company, Customer Name, Department'. 
What I need is to find columns that are in that string and control how do I get them in some controlled method.

The naive approach is treating the P_col_list Variable as one long string and cut it to peaces. I don't recommend it. The functions are long and unfriendly. It's much easier to filter the column table by this presentation Variable and then select a specific row, using the Rank function (with different value of Rank for each final Column).
So I need to check for each potential column, it's in the P_Col_list and select it's rank to get them in an orderly manner.

To check if the column is in the P_col_list I will use the function Locate: LOCATE("Prompt List"."Col_name",'@{P_col_list}') .To get the Ranking I will use RANK("Prompt List"."col_sort") . (The "col_sort' column from above allows me to control the columns. I can set numeric values there and use them. You might notice my values of col_sort column goes from higher to lower. Since the Ranking goes, by default, from larger to smaller values, that is the way to control the order of columns.)

(There is another / better option instead of the Locate above. See P.S 2 why I didn't use it)  

For example, the first selected column formal column name is:


SELECT "Prompt List"."Col_code" saw_0
FROM "ZZ_Prompt_list"
WHERE (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0)
 AND RANK("Prompt List"."col_sort") = 1


and the first "friendly" name is:

SELECT "Prompt List"."Col_name" saw_0
FROM "ZZ_Prompt_list"
WHERE (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0)
AND RANK("Prompt List"."Col_sort") = 1



In a similar way the second pair would be: 

SELECT "Prompt List"."Col_code" saw_0
FROM "ZZ_Prompt_list"
WHERE (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0)
 AND RANK("Prompt List"."col_sort") = 2


SELECT "Prompt List"."Col_name" saw_0
FROM "ZZ_Prompt_list"
WHERE (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0)
AND RANK("Prompt List"."Col_sort") = 2

 


So I decided that my case would have up to 5 dynamic columns (you can do as many as you like) and my second (soon to be hidden) prompt is, named one_list_actual:

Where P_C1A is the friendly Prompt name and P_C1 is the formal formula.

P_C1:

P_CA1: 

The analysis would be:
1. A set of 5 columns with formula like:IFNULL(@{P_C1}, "None"."None") and Column Header like: @{P_C1A}:

2. The original '@{P_col_list}' prompt, defined as hidden in the column properties. (I need it so the analysis "knows" that prompt changed values and refresh).
3. As many columns I want that would be constant in the analysis. It should be at least one measure from a fact for the system to always work properly. It's "Base Facts"."2- Billed Quantity" in my case.

This is how the criteria looks:

 

The last step i did was to put the 2 Dashboard Prompts and the analysis in a single dashboard and see it all works fine and then hide the second prompt (one_list_actual) with all the P_C... variables.

 



After selecting few values:

Last part is to hide the second prompt.I already explained this step in the post: OBIEE - Creating dependent - implicitly declared presentation variable: In Dashboard Properties, under Filters and Variables I'll add the Dashboard Prompt one_list_actual.

and delete it from the dashboard.
this is how it looks with no values selected.

thi sis after selecting several values in the prompt:

You might want to split the Prompt that select column to 2 prompts, one for measures and one for attributes. The "Null" column for measures should probably be default as 0.0, to force it to be float.


   

P.S. 1

In case I wanted the Prompt solution to be reusable in many dashboards, I don't need many tables of column lists. I could add a column / columns with filtering values and filter the prompt by it, each time.

P.S. 2

LOCATE("Prompt List"."Col_name",'@{P_col_list}') will return numbers greater than zero if the column name is in the Presentation Variable. So we can use the condition:  (LOCATE("Prompt List"."Col_name",'@{P_col_list}') > 0). It is problematic in cases where one value is contained in another, for example 'Product' and 'Product Type'. To prevent this problem you might add a comma string before and after the P_col_list an search for values with comma before and after.
(LOCATE(','||"Prompt List"."Col_name"||',',','||'@{P_col_list}'||',') > 0

 So the select statement for P_C1 should be actually:
SELECT "Prompt List"."Col_code" saw_0
FROM "ZZ_Prompt_list"
WHERE locate(','||"Prompt List"."Col_name"||',', ','||'@{P_col_list}'||',')>0
AND (RANK("Prompt List"."Col_sort") = 1)

Why did I use it and not a better option of looking for specific value in multivalued Presentation variable,cover  here by Deepak:
If I want to check whether the column "Prompt List"."Col_name" is part of the values in presentation Variable P_col_list, I can run the following: WHERE "Prompt List"."Col_name" IN (@{P_col_list}['@']) .
Well I had some voodoo problem with. When I didn't select any value in the initial P_col_list variable I had an error:

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27002] Near <,>: Syntax error [nQSError: 26012] . (HY000)

SQL Issued: {call NQSGetQueryColumnInfo('SELECT IFNULL(, "None"."None") FROM "A - Sample Sales"')}

SQL Issued: SELECT IFNULL(, "None"."None") FROM "A - Sample Sales"

It happens only if no columns at all where selected. One default was enough to prevent this error.   

P.S. 3

I would like to thank Rakefet Barad from Oracle ACS and Dov Ziskin from Vision BI for the chats we had on the issue.

OBIEE - Selecting columns dynamically - part4, working with and selecting none

 OBIEE - Selecting columns dynamically - part4, working with and selecting none

In this part I will talk about how to remove column from analysis with column selector or prompt. That means, selecting a column I don't want to present (or actually influence the analysis at all). Why do I want such a stupid thing? When working with dynamic columns, I want the ability to control the number of columns. So if the user wants only 3 columns, out of possible 5, only 3 columns would appear in the analysis. The other 2 columns will not show.

How do I add such "None" column?
I will describe 2 options. First is created at the User Interface level, the second in RPD only and works better with session variables. Usually the second should be your default option and it is the one to be used in the next post.

Solution 1 

I add a column in the repository with empty value and default this column to be hidden, system wide. Now I can use the column anywhere I want. Lets have a detailed explanation.

I'm working with the extended sample available as part of the VM here. So I'm working with real Oracle DB tables in this case. (The following doesn't work well with the XML files of the default Sample).
In the Business layer I randomly selected the Time dimension and added a column with '' value. I named it None.

As a next step I created a new Presentation Table under the Sample Sales and named the table None, then dragged the none column under it.
Now I have the None column. OBIEE enables me to create analysis with this column:

What is nice here, is the fact that the SQL doesn't join unnecessary tables:
This is the left one (only the fact table in the original From):

WITH 
SAWITH0 AS (select sum(T42433.Revenue) as c1,
     '' as c2
from 
     BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ ),
SAWITH1 AS (select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3
from 
     (select 0 as c1,
               D1.c2 as c2,
               D1.c1 as c3,
               ROW_NUMBER() OVER (PARTITION BY D1.c2 ORDER BY D1.c2 ASC) as c4
          from 
               SAWITH0 D1
     ) D1
where  ( D1.c4 = 1 ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
     D1.c2 as c2,
     D1.c3 as c3
from 
     SAWITH1 D1
order by c1, c2 ) D1 

In a similar way, the right one is correct as well, with no unnecessary joins to date dimension.
 SAWITH0 AS (select sum(T42433.Units) as c1,
     T42412.Department as c2,
     '' as c3,
     T42412.Dept_Key as c4
from
     BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ ,
     BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */
where  ( T42412.Office_Key = T42433.Office_Key )
group by T42412.Department, T42412.Dept_Key)...

Next step, as administrator I will create an Analysis with the None column and select it's Column Properties.

In Column Format I'll select "Hide".

And at the bottom of that screen I will select Save As default / Save as system-wide default for "None"."None": 

the same with Zoom-In:

That's all. Now I can use the None column in any other analysis as Column Selector, described in the first post, or as Presentation variable, described in second and third.

For example the same analysis with Column selector, selecting once the None column and then some other column:

When None is in the column of Column selector:

When Per Name Year is in the column of Column selector:

Solution 2

Just as in solution 1, I add a column in the repository with empty value and default this column to be hidden, system wide. Now I can use the column anywhere I want. I'll name it None1. The process of creating this table is the same as in Solution 1, with the formula '':

 

With one critical difference, the hiding will be done at the repository (presentation level) and not UI, as solutions 1.

At the presentation level I will go to the None properties and set the "Hide object if "condition to 1=1. This way the column is always hidden.

Now when going to Analysis we don't see the column None1:

But it doesn't mean it's not there; You just have to know it's name.

For example an analysis with Revenue and the new None1 column:

 Results:

(Thank You Dov Ziskin, from VisionBI for reminding me the second solution). 

Now we are ready to do some real Dynamic Columns example, in the next post

OBIEE - Selecting columns dynamically - part2, simple Prompt

 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.