Variables in direct database requests

In Answers we have possibility to write SQL directly to the database.



I'll show how to use repository, session and presentation variables in direct database request and whether this is possible or not and compare this with normal Answers request.

Repository initialization block:
select to_char(min(time_id), 'dd.mm.yyyy') from sales

Repository variable (dynamic):
rv_test_date_to_char

Session initialization block:
select 'Photo' from dual

Non-system session variable:
CAT, enable any user to set the value checked, without default initializer

Dashboard prompt fields:
PRODUCTS.PROD_CATEGORY, drop-down list, set request variable CAT
CHANNELS.CHANNEL_DESC, drop-down list, set presentation variable pv_channel_desc

The first one re-sets session variable and the second one sets presentation variable.

Normal Answers request columns and filter:
PRODUCTS.PROD_CATEGORY
VALUEOF(NQ_SESSION.CAT)
VALUEOF(rv_test_date_to_char)
'@{pv_channel_desc}'
PRODUCTS.PROD_CATEGORY is prompted

Direct database request:



SQL statement:
select
'VALUEOF(NQ_SESSION.CAT)' session_variable,
'VALUEOF(rv_test_date_to_char)' repository_variable,
'@{pv_channel_desc}{Internet}' presentation_variable,
channel_desc
from channels
where channel_desc='@{pv_channel_desc}{Internet}'

We see inside the statement what is the syntax for referencing variables, for that I know that works correctly.

Now if we put all three objects in the dashboard page at initial we get this:



We change values from the prompt and re-set session and presentation variable:



Everything works fine in the direct database request except we cannot view new value of non-system session variable no mather how many times we refresh (re-set) it, it only takes value that we defined in the initialization block code. New (refreshed) value affects only Answers request.

Denormalized HR employees table, dimension hierarchy and level drills in OBIEE

I looked at the table of employees which is placed on standard Oracle HR schema and I thought that it would be great to see it in the denormalized shape in which every row contains data about parent levels.

Table employees has a self referential join (employee_id - manager_id) which describes us relation. Each employee have its parent employee (manager) except employee_id 100 which is president and placed on the top of hierarchy.

Employee hierarchy is unbalanced, and balanced hierarchy means that each branch has the same number of levels (for example products dimension table, sh.products). We'll denormalize employee table (dimension) to get user friendly hierarchy. After we make denormalized structure we'll build dimension with levels in obiee and use the second copy of employees table as a fact table to find out what is the sum of the salary of all employees first level down for the current level of employee/manager.

First we want to find out what is a maximum level depth in employees table:

select
max(LEVEL)
from employees
start with email='SKING'
connect by prior employee_id=manager_id

The maximum level depth is 4. Four levels we'll have in the denormalized shape. We use email column instead of first name and last name to identify each employee and this would be a primary key in each level.

Number of employees on each level:

select
count(email),
LEVEL
from employees
start with email='SKING'
connect by prior employee_id=manager_id
group by LEVEL
order by 2



This is employee data in the basic form:

select LEVEL,
employee_id,
email,
job_id,
manager_id
from employees
start with email='SKING'
connect by prior employee_id=manager_id
order by 1,2



In this example we don't have loops in the data, value below the parent cannot be his parent, so we don't need NOCYCLE parameter in the CONNECT BY condition.

So, employees are placed on each level. If you look at level 3 for example there are lots of employees whose job_id is ST_CLERK and they managers are at level 2 having ST_MAN as job_id. The similar is with level 4, IT_PROG, FI_ACCOUNT and AC_ACCOUNT having managers with job_id IT_PROG, FI_MGR and AC_MGR. Some employees ends at level 2, for example I add new employee which job_id is PRES_ASST (add this job_id into table jobs with president personal assistant as job title), and he doesn't have levels below him.

The first step is going into obiee Administrator physical level and make new physical table as select table type:



I use this code for employee table levels denormalization:

select
employee_id,
employee_level,
value_below,
nvl(level4_id, nvl(level3_id, nvl(level2_id, level1_id))) level4_id,
nvl(level4_desc, nvl(level3_desc, nvl(level2_desc,level1_desc))) level4_desc,
nvl(level3_id, nvl(level2_id, level1_id)) level3_id,
nvl(level3_desc, nvl(level2_desc,level1_desc)) level3_desc,
nvl(level2_id, level1_id) level2_id,
nvl(level2_desc,level1_desc) level2_desc,
level1_id,
level1_desc,
salary,
department_id,
manager_id
from
(
select
LEVEL,
concat('LEVEL ', cast(LEVEL as varchar2(1))) as employee_level,
case when CONNECT_BY_ISLEAF =1 then 'No' else 'Yes' end as value_below,
employee_id,
manager_id,
email,
salary,
department_id,
decode(LEVEL,1,to_char(employee_id),substr
(
SYS_CONNECT_BY_PATH(employee_id, '/'),
instr(SYS_CONNECT_BY_PATH(employee_id, '/'),'/',1,1)+1,
instr(SYS_CONNECT_BY_PATH(employee_id, '/'),'/',1,2)-instr(SYS_CONNECT_BY_PATH(employee_id, '/'),'/',1,1)-1
)
) level1_id,
decode(LEVEL,1,to_char(email),substr
(
SYS_CONNECT_BY_PATH(email, '/'),
instr(SYS_CONNECT_BY_PATH(email, '/'),'/',1,1)+1,
instr(SYS_CONNECT_BY_PATH(email, '/'),'/',1,2)-instr(SYS_CONNECT_BY_PATH(email, '/'),'/',1,1)-1
)
) level1_desc,
--Level2
decode(LEVEL,2,to_char(employee_id),substr
(
SYS_CONNECT_BY_PATH(employee_id, '/'),
instr(SYS_CONNECT_BY_PATH(employee_id, '/'),'/',1,2)+1,
instr(SYS_CONNECT_BY_PATH(employee_id, '/'),'/',1,3)-instr(SYS_CONNECT_BY_PATH(employee_id, '/'),'/',1,2)-1
)
) level2_id,
decode(LEVEL,2,to_char(email),substr
(
SYS_CONNECT_BY_PATH(email, '/'),
instr(SYS_CONNECT_BY_PATH(email, '/'),'/',1,2)+1,
instr(SYS_CONNECT_BY_PATH(email, '/'),'/',1,3)-instr(SYS_CONNECT_BY_PATH(email, '/'),'/',1,2)-1
)
) level2_desc,
--Level 3
decode(LEVEL,3,to_char(employee_id),substr
(
SYS_CONNECT_BY_PATH(employee_id, '/'),
instr(SYS_CONNECT_BY_PATH(employee_id, '/'),'/',1,3)+1,
instr(SYS_CONNECT_BY_PATH(employee_id, '/'),'/',1,4)-instr(SYS_CONNECT_BY_PATH(employee_id, '/'),'/',1,3)-1
)
) level3_id,
decode(LEVEL,3,to_char(email),substr
(
SYS_CONNECT_BY_PATH(email, '/'),
instr(SYS_CONNECT_BY_PATH(email, '/'),'/',1,3)+1,
instr(SYS_CONNECT_BY_PATH(email, '/'),'/',1,4)-instr(SYS_CONNECT_BY_PATH(email, '/'),'/',1,3)-1
)
) level3_desc,
--Level 4
decode(LEVEL,4,to_char(employee_id),substr
(
SYS_CONNECT_BY_PATH(employee_id, '/'),
instr(SYS_CONNECT_BY_PATH(employee_id, '/'),'/',1,4)+1,
instr(SYS_CONNECT_BY_PATH(employee_id, '/'),'/',1,5)-instr(SYS_CONNECT_BY_PATH(employee_id, '/'),'/',1,4)-1
)
) level4_id,
decode(LEVEL,4,to_char(email),substr
(
SYS_CONNECT_BY_PATH(email, '/'),
instr(SYS_CONNECT_BY_PATH(email, '/'),'/',1,4)+1,
instr(SYS_CONNECT_BY_PATH(email, '/'),'/',1,5)-instr(SYS_CONNECT_BY_PATH(email, '/'),'/',1,4)-1
)
) level4_desc,
SYS_CONNECT_BY_PATH(email, '/') "desc_all",
SYS_CONNECT_BY_PATH(employee_id, '/') "id_all",
SYS_CONNECT_BY_PATH(job_id, '/') "job_all"
from employees
start with employee_id=100
connect by prior employee_id=manager_id
order by employee_id
)

For employees that are identified at the high levels I'll propagate current level id and description to lowest levels and for that I used:

nvl(level4_id, nvl(level3_id, nvl(level2_id, level1_id))) level4_id,
nvl(level4_desc, nvl(level3_desc, nvl(level2_desc,level1_desc))) level4_desc,
nvl(level3_id, nvl(level2_id, level1_id)) level3_id,
nvl(level3_desc, nvl(level2_desc,level1_desc)) level3_desc,
nvl(level2_id, level1_id) level2_id,
nvl(level2_desc,level1_desc) level2_desc

This is not mandatory.

For example SKING is identified only at level 1 and the same value is propagated till the level 4.

Before examples, one thing is left to do. In the physical layer make alias of Employee Hierarchy View that we build and name it as Employee Hierarchy Normal. We'll use this as complete employees denormalization data view. Make another alias for employees table, we'll use it as a fact table to get summ of the first level down for the current employee.

So our physical diagram looks like this:



Foreign keys:

"Employee Hierarchy View".employee_id = "Employee Fact Sum First Level Down".MANAGER_ID
"Employee Hierarchy View Normal".employee_id = "Employee Fact Sum First Level Down".MANAGER_ID

In the BMM we make left join from Employee Hierarchy Drills to Employee Fact to return all record even if there is no sum salary of employees first level down for the current employee (manager):



Example - employees dimension hierarchy and level drills using multiple logical table sources



Logical table sources in order:

















Little explanation why we put in the filter of each logical table source current level, for example if we are on the high level (SKING) and want to drill down to second level this query will be generated:

select
distinct
level1_desc,
level2_desc
from Employee Hierarchy View
where level1_desc='SKING'

You will get 16 rows, including the row that has level1_desc='SKING' and level2_desc='SKING' which is actually data placed on the first level (values propagated to lowest level). So with the logical table source filter we are excluding these rows.

Employee hierarchy and drills test in Answers

Report structure:



First level:



Value below column tell us if this level has level below it. Notice measure sum salary first level down and salary current employee dimension attribute.

Drill to the second level:



Now if you try to drill from the TTEST which doesn't have level below him you would get:



This is because when crossing from the second to the third logical table source it trying to execute where condition:

select
distinct
level1_desc,
level2_desc,
level3_desc
from Employee Hierarchy View
where level1_desc='SKING'
and level2_desc='TTEST'
and employee_level='LEVEL 3'

There is no level 3 for TTEST.

Drill now to the third level for any value that has level down:



Not bad, NGREENBE has a salary 12000 and all employees which he's a manager have sum of the salary 39600. Chech this when drilling from him to lowest level, the last fourth level:



The sum works fine.

What if we want to show in the report all employees with denormalized structure and the fact measure without starting from the higher level and without using drills?

Lets try that:



The result is not good because the query goes to the last logical table source which contains all columns on the level 4 and the employee_level='LEVEL 4' filter is applied:





So we must think something else in order to see all employees.

Options to show detailed (complete) view of all employees

1. Change the filter of the last logical table source dynamic with non-system session variable and re-set it from the dashboard prompt

We'll make two reports, one starts with highest level and used for drill down, other is detailed with all levels shown in the report but without using drills.

Create initialization block and non-system session variable:



Create dashboard prompt which shows only Complete and Dimension hardcoded values and set request (session) variable SV_EMPLOYEE_HIERARCHY. Create two sections, each has guided navigation report to show or hide it according to session variable value that user sets from the prompt. I'll not show how to implement that, just the result.

In the logical table source for level 4 write:

orcl."".HR."Employee Hierarchy View".employee_level=CASE WHEN VALUEOF(NQ_SESSION."SV_EMPLOYEE_HIERARCHY")='Dimension' THEN 'LEVEL 4' ELSE orcl."".HR."Employee Hierarchy View".employee_level END



Test

Choose dimension view with drills enabled:



Don't forget to enable Drill in place option in the section properties.

Now you are able to drill down to the lowest level (level 4) like we have shown and described before, everything is the same.



And if you drill to the level 4 it used valid code from the last logical table source:



Now choose complete view with drills disabled.



The result:



In complete view all employees are included:



NQQuery.log:



2. Make the separate employees logical table without dimension drills

Make new logical table using Employee Hierarchy View Normal alias from the physical layer that we made before:



In the BMM join this table to Employee fact logical table:



Don't forget left join:



You can use this logical table for detailed (complete) view to show all employees and still use previous logical table with dimension and drills. In that case you don't need this filter in the Employee Hierarchy Drills logical table in the logical table source for level 4:

orcl."".HR."Employee Hierarchy View".employee_level=CASE WHEN VALUEOF(NQ_SESSION."SV_EMPLOYEE_HIERARCHY")='Dimension' THEN 'LEVEL 4' ELSE orcl."".HR."Employee Hierarchy View".employee_level END

Make detailed report using Employee Hierarchy Normal logical table.

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 contains 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.