OBIEE master-detail requests navigation - refreshing details when validate master record

I've been working as a Oracle Forms & Reports developer for many years and I thought that it would be great to have some of Oracle Forms functionality implemented here in OBIEE. Think about having two OBIEE reports. One is the master and the second is the detail. You may use departments employees or order and order items as an example. In Oracle Forms you'll have two blocks, one is master and the second is detail. When you are located in the first block row (master record) you have possibility to get detail data automatically. In other words detail records are fetched immediately when a user queries the master record.

After some time of thinking I figured out the way how to do that in OBIEE and what is the best way to present it. I'll show how to implement master-detail requests navigation using HTML form text field on the master report along with iframes and using javascript events. The idea is to have master and detail reports in the same dashboard page, and including dashboard prompt. In the first report we have one text field (HTML form) in which the cursor is positioned automatically so we use this field to jump on the next or the previous master record, and as the result we are getting immediately detail report. Each report is placed in its own iframe. So, basically we navigate from the master to details on changing a record in the master request. I'll explain the whole process, from the building master-detail reports navigation to final customization.

So I want to have something like this:



Steps

Step one - building master and detail reports

Master



Detail



Master request shows categories and detail shows top ten products ranked by amount sold for the current row master category.

Step two - building current iframe custom expression on master request including both requests using concatenated iframes and placing it in the narrative view to establish navigation in the same page between reports without losing window focus

Current iframe contains two concatenated iframes. Each iframe is build for one request and the detail request iframe is placed below the master. Each iframe has name. We use this column to place it in the narrative view of the master request because we'll show it on the dashboard to show automatically both reports (frames).

Current iframe:



Better view:



As you can see the master request column TIMES.CALENDAR_YEAR is filtered by dashboard prompt presentation variable. When the request is placed inside iframe the classic navigation with column filter set to is prompted is not working so we use presentation variable to pick up value from the dashboard prompt and place it in the master iframe request.

Place current iframe column in narrattive view:



Step three - HTML form text field and javascript events implementation on the master request, triggering detail request when validate master record

Column product category on the master contains complex detail request navigation:



Better view:



Explanation what we have done with the code above:

We made form field for activating navigation, the text field is readonly. OnKeyDown javascript event disables backspace, delete and return keys to disallow user to change it (product category must be visible and not changed). Form text field need to be editable for triggering details. Enter and down-arrow key use tab key to validate next row. OnSelect javascript event opens detail request URL in the iframe and transfers all filters from the master request. OnClick javascript event select all in the text field (prevention if the user decides to click on the field). The focus is always in the master field. Finally we customize the form and hide editable functionality from the user.

Step four - build the dashboard prompt and dashboard page



Show only narrative view:



Step five - the final look and test

Initial:



The position is on the editable field and the detail request is automatically refreshed. You can press enter, tab or down-arrow to cross into new master row and validate detail request record for the current master. The focus is on the same page because we use iframes, so we refresh only detail iframe.









Unfortunately previous master row navigation can be established only using shift+tab or shift+enter keys.

Loading HR employees in Essbase and reporting in OBIEE

This is my first post using Essbase, and Essbase-OBIEE. I'm playing with Essbase for a while and will publish something from time to time here on 108obiee.

In this post I showed how to handle unbalanced employees hierarchy using multiple logical table sources in dimension logical table, using relational source.

Today I'll explain two different examples. In the first one there will be some basics how to load HR employees in Essbase BSO cube with using parent-child (value based, recursive dimension) member relationship and member consolidation method. But in the second example I'll be happy to get in Essbase the same as in this post:

1. Unbalanced employees hierarchy
2. Data information for each employee - salary as well as the sum of salary first level down if the current employee is a manager and has employees under him

I choose BSO data storage because I only have one dimension with not to many members (I don't count accounts dimension). Employees dimension will be tagged as sparse dimension and accounts dimension as dense and everything in this examples will be stored in the database/cube. In example two I'll use some calculations in BSO members, and for that Essbase is using Calculator, or Calc language. It's only for BSO outlines. For ASO outlines Essbase uses MDX language. There are very similar and you can translate Calculator Functions (BSO) to MDX Functions (ASO), for more information read Essbase Technical Reference.

Example one - using parent member consolidation method to load member measure data into BSO cube

Lets make a simple OLAP model using EIS. We use Oracle HR as datatsource and add employees table as dimension table and user-defined table for a fact table, because we need to have fact table, if we don't we receive errors like no table is selected as a fact table and logical join is required in OLAP model, so we must have dimension and fact table to populate OLAP model.



User-defined fact table:



Employees dimension table and user-defined fact relation is 1:1. It's not really 1:N. I put this u-d fact table just to populate OLAP model and be able to made metaoutline later. Employees hierarchy is value-based (self referencial join). This kind of dimension is called recursive dimension.

You can find great info about this in Oracle Essbase Integration Services Online Help.

Recursive dimension:



Logical join between employees dimension and fact:



EMPLOYEES.SALARY has SUM as aggregation rule as well as EMPLOYEES.DUMMY_MEASURE.

At this point we save changes to our OLAP model and begin to create a metaotline with this OLAP model.

I'm trying to find out what is the secret and how Essbase load the data into cube. I supose that because we have here parent-child relationship between the members the process of loading the data will take first the leaf level (the last level), level 0 in the employees hierarchy and try to find the data in the fact table via the logical join. If the level 0 member has data in the fact table the measure value and the cell will be populate. Now, because this dimension is sparse and this is the only dimension that I use in this example (I don't count account dimension which hold actually measures and it is defined as dense) I'll expect single-celled blocks (or two-celled blocks, because we have two measures) as much as we have members in our sparse employees dimension. In this example all level 0 members exists in the fact table so there will be no members with missing values in empty cells. After level 0 members are loaded the parent level members will be loaded into cube in upper level blocks according to consolidation rules in parent members.

From the documentation we learned that Essbase stores data values in data blocks. Essbase creates a data block for each unique combination of sparse dimension members (providing that at least one data value exists for the combination). Each data block contains all the dense dimension member values for its unique combination of sparse dimension members. I our case for example, it creates one data block for the member 109 for example, which is level 0 employee member. This data block contains all measures (account values) for 109.

This is how I setup my metaoutline:



I choose child column member EMPLOYEES.EMPLOYEE_ID to put in the employees dimension. With that I have a possibility to use child alias that refer to child member.



We use EMPLOYEES.EMAIL alias for EMPLOYEES.EMPLOYEE_ID member.



This is very basic setup and I didn't use any of advanced features in dimension or metaoutline properties.

Note that I'm not using data rules to load data into Essbase cube, this I'll use in one of my future posts.

Choose consolidation method addition and store data as data storage type for both measures:



The same properties we choose on the EMPLOYEES.EMPLOYEE_ID member properties, in the metaoutline.

On the OLAP metaoutline properties we choose block storage as data storage option.

Next step is to load members and the data:



Loading results:

SELECT
/*+ */
aa."MANAGER_ID",
aa."EMPLOYEE_ID",
aa."EMAIL"
FROM
HR.EMPLOYEES aa

108 members have been added to dimension "EMPLOYEES".
2 members have been added to dimension "Accounts".
110 members have been added to outline.

Dataload SQL 1:
SELECT
aa."EMPLOYEE_ID",
SUM(aa."SALARY"),
SUM(aa."DUMMY_MEASURE")
FROM
(
select
employee_id,
manager_id,
department_id,
job_id,
salary,
1 as dummy_measure
from employees
) aa
WHERE
aa."EMPLOYEE_ID" is NOT NULL
GROUP BY aa."EMPLOYEE_ID"

If you want to query the server log after the loading process you may find this interesting:

108 members have been added to dimension "EMPLOYEES".
2 members have been added to dimension "Accounts".
110 members have been added to outline.
EssbaseAPI: Declared Dimension Sizes = [109 3 ]
EssbaseAPI: Actual Dimension Sizes = [105 3 ]
EssbaseAPI: The number of Dynamic Calc Non-Store Members = [0 0 ]
EssbaseAPI: The number of Dynamic Calc Store Members = [0 0 ]
EssbaseAPI: The logical block size is [3]
EssbaseAPI: Maximum Declared Blocks is [109] with data block size of [3]
EssbaseAPI: Maximum Actual Possible Blocks is [105] with data block size of [3]
EssbaseAPI: Converted [105] index entries
Successfully completed Member load for metaoutline:Metaoutline3 Application:BSOHrApp DB:BSOHrDb User:admin (Records Loaded: 108, Records Rejected: 0)
EssbaseAPI: Data Load Updated [35] cells

The next step is going to EAS/BSOHrDb database properties:





One thing I don't understand here. It's said that 90 block are with level 0 and that is pretty clear because if you do a query:

select count(*) from employees a
where not exists
(
select 1
from employees
where manager_id=a.employee_id
)
order by a.employee_id

You'll get 90 leaf level rows.

Count upper-level members:

select
count(distinct manager_id)
from employees
where manager_id is not null

You would get 18 rows but for some reason we get only 15 rows in the statistics tab in the database outline properties.

Note: when the upper generation member has only one generation member below itself (201-202, 205-206 and 102-103) Essbase will store only the second one (lower level, 202, 206 and 103), so this explain number of 15 upper-level blocks. Upper members 201, 205 and 102 are not stored.

Employees preview in outline:



With this setup/load only the point one is satisfied:

1. Unbalanced employees hierarchy
2. Data information for each employee - salary as well as the sum of salary first level down if the current employee is a manager and has employees under him

The point two is not because:

1. We have defined consolidation method addition on members and measures
2. Data is loaded first at lower 0 levels and then according to consolidation upper levels are loaded.

Lets make a simple test in Answers after importing BSOHrApp and BSOHrDb into OBIEE Administrator and setup all thre layers correctly (don't forget to set hierarchy type of employees dimension to unbalanced).

OBIEE Physical layer:



BMM:



Test in Answers:



DUMMY_MEASURE counts how many leaf levels (0 levels) are under the current generation member passing through all the branches to the lowest level or lowest generation.

Measure values at this generation (Gen2) are the result of the sum of the previous generations (Gen3):



Drill to the generation 4 employees:



Drill to generation 5 employees from SHIGGINS (employee_id=205):



It seems like SHIGGINS and WGIETZ have same values for both measures which is correct because there is only one level under the SHIGGINS and it summarize data below him.

The same is for LDEHAAN and AHUNOLD:



Ok, that is for this example in which we showed how Essbase load the data and how the upper levels are loaded according consolidation (addition in our case) method.

Example two - loading raw HR employees data into BSO cube without using consolidation method in measures and members, showing employees salary and sum of the salary first level down

Make a new OLAP model in EIS:



It is very similar like the previous model except we changed the code in user-defined fact table:



select
employee_id,
manager_id,
salary,
cast(null as NUMBER) as sum_salary_first_level_down
from employees

And we set aggregation rule for both fact measures to NONE:





The reason why we remove the aggregation rule from both measures is that we want to load raw data into Essbase BSO cube, salary for each employee and the sum of the salary first level down. We need to be sure that no aggregation is used in that case. In the example one we showed how aggregation and member consolidation work.

Note: the alternative for showing each employee salary is to define attribute dimension with the salary attribute and then associate it with the employees dimension but I don't want to associate manually salary to each employee member in EAS and I can associate attribute member to only level zero employees members so this solution is not good.

I use cast(null as NUMBER) as sum_salary_first_level_down in the user-defined fact table to pre-define measure so I can use it in the EIS metaoutline to write some formula that will calculate sum_salary_first_level_down and store it in the cube as separate measure, along with the salary measure, which presents employee salary.

Metaoutline:



Set consolidation to ignore (~), on all members:



Both measures have data storage set to store data.

Open the sum_salary_first_level_down member properties and in the formula tab enter the calculation formula:

@SUMRANGE("SALARY",@RELATIVE(@CURRMBR("EMPLOYEES"),@CURGEN("EMPLOYEES")+1));

This will calculate desired sum.



After loading members and data we go to EAS, refresh application list and query our new database/cube properties:





Level 0 and upper-level block numbers are correct.

EAS outline:



See that members are not using consolidation so we expect raw data in the cube. BSO Calculator formula which we have defined in the EIS is visible on the sum_salary_first_level_down:



The first cube preview in EAS looks fine, 24000 is the salary of the employee_id 100 (SKING) and 170400 summarize salary first level down:



If you drill to the first level down:



Quick test in Answers (after importing a cube into BI Administration):



Drill down to generation 3 members:



Drill down to generation 4 members:



Drill down to generation 5 members:



Note that in all part drills the sum of the salary (grand total) matchs the sum_salary_first_level_down in the previous generation member, so we got the desired result.

Aggregates outside OBIEE - materialized views and query rewrite

In this post I explained how to use aggregates in OBIEE. Then we did manually create aggregate tables on the database and set each logical table source to triggering only on the certain level of dimension.

We then used aggregate tables SALES_MONTHS, SALES_YEAR_CAT and SALES_MONTHS_CAT_CH and dimension tables CATEGORIES, MONTHS i YEARS. Here, we don't need that.

In this post we'll try to explain and set up materialized views and the query rewrite to get the same queries as in the post above, but without setting anything in the BMM in the logical table sources.

I'll use oracle 10g database, oracle SH schema and the measure from the SALES fact table.

For using dbms_mview.explain_rewrite we need to have rewrite_table table (file utlxplan.sql) and for dbms_mview.explain_mview table mv_capabilities_table (file utlxmv.sql).

First set:

ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE
ALTER SYSTEM SET QUERY_REWRITE_INTEGRITY='TRUSTED'

This is the level of the query rewrite. I set TRUSTED only to be able to test this. You should see other options as well. In TRUSTED mode, the optimizer trusts that the relationships declared in dimensions and RELY constraints are correct. In this mode, the optimizer also uses prebuilt materialized views or materialized views based on views, and it uses relationships that are not enforced as well as those that are enforced. In this mode, the optimizer also trusts declared but not ENABLED VALIDATED primary or unique key constraints and data relationships specified using dimensions. This mode offers greater query rewrite capabilities but also creates the risk of incorrect results if any of the trusted relationships you have declared are incorrect (Text reference: Oracle Database Data Warehousing Guide 11g Release 1 (11.1)).

RELY constraints:

We use SALES table as the reference. Existing constraints we need to modify to RELY. RELY only affects those constraints that are ENABLE NOVALIDATE. Parameter QUERY_REWRITE_INTEGRITY is set to TRUSTED (TRUSTED informations are constraints (NOVALIDATE RELY) and dimensions). Oracle will not do the check whether relationships defined with RELY constraints are TRUE. That refers to primary key and unique key constraints (RELY ENABLE NOVALIDATE). Query rewrite also use joinback method for recognition attribute that is not in the materialized view query but can be retrieved with joinback. For example, the query rewrite materialized view has CALENDAR_MONTH_ID and we want to group by CALENDAR_MONTH_DESC and then the query optimizer make the join between materialized view and the TIMES table one more to get CALENAR_MONTH_DESC. TIMES table is joinback table.

Because of the connection with the higher levels we need to have dimensions:



I didnt create them, they are already on the oracle SH schema.

Modify all SALES table constraints to RELY ENABLE NOVALIDATE:

alter table sales modify constraint sales_product_fk RELY ENABLE NOVALIDATE
alter table sales modify constraint sales_channel_fk RELY ENABLE NOVALIDATE
alter table sales modify constraint sales_time_fk RELY ENABLE NOVALIDATE
alter table products modify constraint products_pk RELY ENABLE NOVALIDATE
alter table times modify constraint times_pk RELY ENABLE NOVALIDATE
alter table channels modify constraint channels_pk RELY ENABLE NOVALIDATE

We create materialized view to support all queries like in the post:

create materialized view mv_sales_all
build immediate
refresh force on demand
with primary key
enable query rewrite
as
select t.calendar_month_id,
s.prod_id,
s.channel_id,
grouping_id(t.calendar_month_id, s.prod_id, s.channel_id) as gr_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
grouping sets
(
(t.calendar_month_id),--gr_id 3
(t.calendar_month_id,s.prod_id), --gr_id 1
(t.calendar_month_id,s.prod_id,s.channel_id)--gr_id 0
)

In the grouping sets we support all three combinations like in the post.

Grouping_id function will get the decimal interpretation of the binary. If the attribute gives the contribution to aggregation then the value is 0, otherwise it is 1.

For example, calendar_month_id has value 3 because it's in the combination:

(0, 1, 1) = (calendar_month_id, prod_id, channel_id)

Check:

select bin_to_num(0, 1, 1) from dual--3 decimal
select bin_to_num(0, 0, 0) from dual--0 decimal
select bin_to_num(0, 0, 1) from dual--1 decimal

Example of combinations:



To explain materialized view query we use the table mv_capabilities_table and the procedure dbms_mview.explain_mview.

BMM (clean model):



The focus is on how this works with queries that OBIEE generates, not how to refresh materialized views during the part of the job of the ETL process.

To test this we need to refresh materialized view:

begin
dbms_snapshot.refresh('MV_SALES_ALL','C');
end;

Get schema statistics:

begin
dbms_stats.gather_schema_stats('SH', CASCADE=>TRUE);
end;

Now, if we choose:



NQQuery.log:



Explain plan, table plan_table:



See the joinback to TIMES table to get the CALENDAR_MONTH_DESC.

If we instead of CALENDAR_MONTH_DESC put the CALENDAR_MONTH_ID there is no joiback to TIMES because we use CALENDAR_MONTH_ID which is already in the materialized view query.



To verify that the query did rewrite we can use dbms_mview.explain_rewrite, and the table rewrite_table:



If we choose:



NQQuery.log:



Explain plan, table plan_table:



If we choose:



NQQuery.log:



Explain plan, plan_table:



We see that in all three queries the query rewrite works correctly, query has been rewritten.

I really try to show how this works when you are using OBIEE queries. If you have any question or suggestion please post the comment.

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.