How to choose null, not null or all values from drop down list

Example

Prompt the user to select only null, not null or all values from drop down harcoded list and reference it to the column.

Table for this example:

create table value_test (id number,col1 number, col2 varchar2(100))



After importing table to Administrator we join it to the dummy fact table in the BMM (not shown).

Dashboard prompt with drop down harcoded list:



SQL Results:

SELECT case when VALUE_TEST.COL1 is not null then 'Not null' when VALUE_TEST.COL1 is null then 'Null' end FROM "Write back test" union all SELECT case when 1=2 then VALUE_TEST.COL2 else 'All' end FROM "Write back test"

Put All as default:



Answers report:



Advanced SQL filter:

case
when '@{pv}'='All' then 'All'
else
case when VALUE_TEST.COL1 is not null then 'Not null' else 'Null' end
end='@{pv}'

Test

All (column col1 all values):



NQQuery.log:



Not null (column col1 is not null):



NQQuery.log:



Null (column col1 is null):



NQQuery.log:

Aggregates in OBIEE

Aggregate fact tables contain same measure data like in the lowest granularity fact table but summarized on certain level. Aggregates in obiee can be created using aggregate persistence wizard or manually.

For the first option:

http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/aggpersist/aggpersist.htm
http://www.rittmanmead.com/2007/10/26/using-the-obiee-aggregate-persistence-wizard
http://obiee101.blogspot.com/2008/11/obiee-aggregate-persistence-wizard.html

Advanced option is using materialized views, dimensions and query rewrite.

I'll show the second option (manually).

Creating database objects

For this example we'll create database objects, higher level dimension tables, aggregates, indexes, ect. Something about higher dimension tables, it depends how you understand normalized and denormalized structure in business intelligence term. Dimension tables are always denormalized, each level is placed inside it. If you for example query sh.products table you'll see that the lowest level has information about high levels. If you are using dimension operator in OWB to load data into, the result is dimension table with addition that all levels are separately loaded with each with its own ID, primary key. So other aggregation fact tables can reference high level dimension ID from the same dimension. The very similar way is how olap dimension works, see global.channel_dimview. Anyway, we'll create higher dimension level tables for this example purpose.

create table months as
select
distinct
calendar_month_id,
calendar_month_desc,
calendar_year_id,
calendar_year
from times

alter table months
add constraint
months_pk primary key (calendar_month_id);

create table categories as
select
distinct
prod_category_id,
prod_category
from products

alter table categories
add constraint
categories_pk primary key (prod_category_id)

create table years as
select
distinct
calendar_year_id,
calendar_year
from times

alter table years
add constraint
years_pk primary key (calendar_year_id)

create table sales_months as
select
t.calendar_month_id,
sum(s.amount_sold) as amount_sold,
sum(s.quantity_sold) as quantity_sold
from sales s, times t
where s.time_id=t.time_id
group by t.calendar_month_id;

alter table sales_months
add constraint sm_months_fk
foreign key (calendar_month_id)
references months (calendar_month_id)

create bitmap index sm_months_idx
on sales_months (calendar_month_id);

create table sales_year_cat as
select
t.calendar_year_id,
p.prod_category_id,
sum(s.quantity_sold) as quantity_sold,
sum(s.amount_sold) as amount_sold
from sales s, products p, times t
where s.prod_id=p.prod_id
and s.time_id=t.time_id
group by t.calendar_year_id, p.prod_category_id;

alter table sales_year_cat
add constraint syc_years_fk
foreign key (calendar_year_id)
references years (calendar_year_id)

create bitmap index syc_years_idx
on sales_year_cat (calendar_year_id);

alter table sales_year_cat
add constraint syc_categories_fk
foreign key (prod_category_id) references categories (prod_category_id)

create bitmap index syc_categories_idx
on sales_year_cat (prod_category_id);

create table sales_months_cat_ch as
select
t.calendar_month_id,
p.prod_category_id,
c.channel_id,
sum(s.quantity_sold) as quantity_sold,
sum(s.amount_sold) as amount_sold
from sales s, products p, times t, channels c
where s.prod_id=p.prod_id
and s.time_id=t.time_id
and s.channel_id=c.channel_id
group by t.calendar_month_id, p.prod_category_id, c.channel_id;

alter table sales_months_cat_ch
add constraint smcc_months_fk
foreign key (calendar_month_id)
references months (calendar_month_id)

create bitmap index smcc_months_idx
on sales_months_cat_ch (calendar_month_id);

alter table sales_months_cat_ch
add constraint smcc_channels_fk
foreign key (channel_id) references channels (channel_id)

create bitmap index smcc_channels_idx
on sales_months_cat_ch (channel_id);

alter table sales_months_cat_ch
add constraint smcc_categories_fk
foreign key (prod_category_id)
references categories (prod_category_id)

create bitmap index smcc_categories_idx
on sales_months_cat_ch (prod_category_id);

The focus is on how to implement this in obiee, not how these tables are refreshed with data or recreated as a part of the job of ETL process.

Implementation in obiee

Physical layer:



Foreign keys:

SALES.PRODUCT_ID >- PRODUCTS.PRODUCT_ID
SALES.TIME_ID >- TIMES.TIME_ID
SALES.CHANNEL_ID >- PRODUCTS.CHANNEL_ID

SALES_MONTHS_CAT_CH.CHANNEL_ID >- CHANNELS.CHANNEL_ID
SALES_MONTHS_CAT_CH.PROD_CATEGORY_ID >- CATEGORIES.PROD_CATEGORY_ID
SALES_MONTHS_CAT_CH.CALENDAR_MONTH_ID >- MONTHS.CALENDAR_MONTH_ID

SALES_YEAR_CAT.PROD_CATEGORY_ID >- CATEGORIES.PROD_CATEGORY_ID
SALES_YEAR_CAT.CALENDAR_YEAR_ID >- YEARS.CALENDAR_YEAR_ID

SALES_MONTHS.CALENDAR_MONTH_ID >- MONTHS.CALENDAR_MONTH_ID

BMM:

Drag and drop attributes from the physical layer to BMM, for example CALENDAR_YEAR_ID and CALENDAR_YEAR from YEARS physical table to TIMES logical table to create additional logical table sources. We repeat this step for other higher level dimension tables on the physical layer as weel as for SALES_MONTHS, SALES_YEAR_CAT and SALES_MONTHS_CAT_CH aggregate fact tables that contains measures AMOUNT_SOLD and QUANTITY_SOLD.





Dimensions:



On each logical fact table source on the logical fact table SALES we need to set aggregation levels and this is mandatory step for obiee to redirect SQL query on aggregate tables.

Aggregate sources are activated on certain levels of dimension.







Test

If we add CALENDAR_MONTH_DESC, instead of going to SALES (TIME_ID lowest level) and summarize it on the month level, the SQL query is redirected to SALES_MONTHS:



NQQuery-log:



In case of CALENDAR_YEAR the SQL query is also redirected to SALES_MONTHS:



Some other cases:

CALENDAR_MONTH_DESC, PROD_CATEGORY and CHANNEL_DESC:



NQQuery-log:



CALENDAR_YEAR and PROD_CATEGORY:





NQQuery.log:

OBIEE expert status on OTN forum

I got expert level status on the obiee OTN forum today:





Thanks to everyone for points.

Using more then one GO URL request navigation with HTML drop down list

There was a question on OTN forum http://forums.oracle.com/forums/thread.jspa?threadID=968563&tstart=0 and it's very good base for me to write this post on how to place multiple GO URL navigation to a different subject areas in one column using drop down list.

In one of my previous posts http://108obiee.blogspot.com/2009/09/go-url-request-navigation-between.html I describe how to use/make GO URL request navigation between different subject areas.

I this example we'll use GO URLs from previous post and put them in only one column and give the user ability which one GO URL navigation from the drop drown list to choose.

As a reference for writing this post I use this link http://www.tek-tips.com/faqs.cfm?fid=2960 and thanks to that site for placing the code in. The code is combination of javascript and html select and option tag which makes drop down list and put links inside them. So when the user chooses value from the drop down list he navigates on the target report.

This first thing is to make Answers report, I used my Normal model subject area:



Write expression with HTML select and option tag to make drop down list and inside put your GO URLs:





We call javascript function inside column expression in Answers in the select tag and onChange so now we need to declare it somewhere.

This can be done in the dashboard text object in the text properties:





Now place report along with the text object inside the dashboard:



Test

Initial start:



The first report navigation from the drop down list:



Result:



The second report navigation from the drop down list:



Result:

Referencing GROUP session variable in initialization blocks for different types of authentication

I was looking and reading some threads on OTN obiee forum about GROUP session variable on how to reference it in initialization blocks when using internal authentication. When I said internal authentication I mean that we don't use external table (normal or row-wise data in table) or any other form of authentication, just define users and groups internal in RPD and assign those users to one or many groups internally, in RPD.

In this post I'll show how to reference GROUP session variable in initialization blocks for different types of authentication and whether it is even possible and if not what is the workaround for that. The idea is to use GROUP session variable in the target initialization block that returns index Yes or No if the user belongs to special group.

You may read my previous post http://108obiee.blogspot.com/2009/03/external-table-authentication-and-row.html about external table authentication and row-wise initialization in OBIEE.

Example one - users are assigned to groups internal (in the RPD)

Power user is assigned to Special Group in RPD:



Now, make one initialization block that we'll use in all cases. It will give us Yes or No indicator if the user is the member of the Special Group and will populate session variable SPECIAL_GROUP:



select
case when INSTR('VALUEOF(NQ_SESSION.GROUP)', 'Special Group')>0 then 'Yes'
else 'No'
end
from dual

We need to use INSTR because the user can be a member of many groups. In this example user Power is the member of three groups. In normal condition we could retreive this session variable and receive data in this format:

Users (A-M);Users (N-Z);Special Group

Groups are separeted by semicolons.

Log on to Answers with Power user:



Use the simple report to retrieve session variables from our initialization block:



The result is:



SPECIAL_GROUP takes default value Yes/No. This brings me the question why we cannot use the fact that we assign user to group(s) internal (in RPD) and use this information in VALUEOF(NQ_SESSION.GROUP) in initialization blocks.

We see that VALUEOF(NQ_SESSION.GROUP) is working in Answers but not in initialization blocks when we used internal (RPD) authentication, to assing user to group(s).

And now if we try to copy/paste code from initialization block for retreiving SPECIAL_GROUP variable into Answers, just replace INSTR with obiee LOCATE function:



case
when LOCATE('Special Group', VALUEOF(NQ_SESSION.GROUP))>0 then 'Yes'
else 'No'
end

This code is equal to the one from the initialization block:

case
when INSTR('VALUEOF(NQ_SESSION.GROUP)', 'Special Group')>0 then 'Yes'
else 'No'
end

And the results is:



Conclusion is that if at the end of all initialization blocks GROUP session variable is not filled (what is our example case) it will be filled with the groups that we have defined internal (in the RPD) for current user so therefore we couldn't reference it in the initialization block because it is not yet filled.

So in case that we want to use GROUP session variable for this case in initialization block and if we need this SPECIAL_GROUP session variable indicator and we don't want to have external table authentication because we use internal user - group(s) authentication the workaround is:

1. Make one initialization block for re-filling internal groups into GROUP session variable, just rewrite information from RPD for each user. This block will be execute before the target:



select
case
when ':USER'='Power' then 'Special Group;Users (A-M);Users (N-Z)'
end
from dual



Now, go to Answers and use the same report to retrieve SPECIAL_GROUP session variable from our initialization block:



The result is:



This works fine, but the VALUEOF(NQ_SESSION.GROUP) is taking groups from the RDP and the VALUEOF(NQ_SESSION.SPECIAL_GROUP) use groups filled from the initialization block b_int_rpb_user_groups.

Note that if you assign user to groups in the RPD and do the same in initialization block the first one takes priority, and you can see this if you retrieve VALUEOF(NQ_SESSION.GROUP) in Answers.

We don't want to have the same information twice or different information so we checkout groups for Power user in RPD:



Now we use groups only from the initialization block b_int_rpb_user_groups:



Example two - users are assigned to groups from external table

Assign Power user to multiple groups in external table:



Disable block b_int_rpb_user_groups from the previous example and use b_ext_tab_auth_all explained in http://108obiee.blogspot.com/2009/03/external-table-authentication-and-row.html.

This block takes information from external table and re-fill session variables USER, GROUP, DISPLAYNAME and LOGLEVEL:

select username, groupname, displayname, to_number(loglevel)
from OBIEE_EXT_TAB_AUTH
where username=':USER'
and password=':PASSWORD'



Use this block as execution precedence in the b_int_special_group_indicator block:



Test in Answers and result:



The SPECIAL_GROUP session variable is filled correct because we used external table autentication for re-filling GROUP session variable in initialization block before.

Example three - users are assigned to groups from external table using row-wise initialization

USER session variable is authenticated (and re-filled) from normal external table:



select username
from OBIEE_EXT_TAB_AUTH
where username=':USER'
and password=':PASSWORD'

Other session variables we re-filled from row-wise initialization:



select
session_variable_name,
session_variable_value
from obiee_row_wise_init
where username=':USER'

Row-wise external table:



The block for SPECIAL_GROUP session variable is the same like in previous examples:



Test in Answers and result:



The SPECIAL_GROUP session variable is filled correct even when we used external table autentication for USER session variable and row-wise initialization for re-filling GROUP and other session variables in initialization block before.