create table dept( 
  deptno number(2,0), 
  dname  varchar2(14), 
  loc    varchar2(13), 
  constraint pk_dept primary key (deptno) 
)

create table emp( 
  empno    number(4,0), 
  ename    varchar2(10), 
  job      varchar2(9), 
  mgr      number(4,0), 
  hiredate date, 
  sal      number(7,2), 
  comm     number(7,2), 
  deptno   number(2,0), 
  constraint pk_emp primary key (empno), 
  constraint fk_deptno foreign key (deptno) references dept (deptno) 
)

insert into dept 
values(10, 'ACCOUNTING', 'NEW YORK')

insert into dept 
values(20, 'RESEARCH', 'DALLAS')

insert into dept 
values(30, 'SALES', 'CHICAGO')

insert into dept 
values(40, 'OPERATIONS', 'BOSTON')

insert into emp 
values( 
 7839, 'KING', 'PRESIDENT', null, 
 to_date('17-11-1981','dd-mm-yyyy'), 
 5000, null, 10 
)

insert into emp 
values( 
 7698, 'BLAKE', 'MANAGER', 7839, 
 to_date('1-5-1981','dd-mm-yyyy'), 
 2850, null, 30 
)

insert into emp 
values( 
 7782, 'CLARK', 'MANAGER', 7839, 
 to_date('9-6-1981','dd-mm-yyyy'), 
 2450, null, 10 
)

insert into emp 
values( 
 7566, 'JONES', 'MANAGER', 7839, 
 to_date('2-4-1981','dd-mm-yyyy'), 
 2975, null, 20 
)

insert into emp 
values( 
 7788, 'SCOTT', 'ANALYST', 7566, 
 to_date('13-JUL-87','dd-mm-rr') - 85, 
 3000, null, 20 
)

insert into emp 
values( 
 7902, 'FORD', 'ANALYST', 7566, 
 to_date('3-12-1981','dd-mm-yyyy'), 
 3000, null, 20 
)

insert into emp 
values( 
 7369, 'SMITH', 'CLERK', 7902, 
 to_date('17-12-1980','dd-mm-yyyy'), 
 800, null, 20 
)

insert into emp 
values( 
 7499, 'ALLEN', 'SALESMAN', 7698, 
 to_date('20-2-1981','dd-mm-yyyy'), 
 1600, 300, 30 
)

insert into emp 
values( 
 7521, 'WARD', 'SALESMAN', 7698, 
 to_date('22-2-1981','dd-mm-yyyy'), 
 1250, 500, 30 
)

insert into emp 
values( 
 7654, 'MARTIN', 'SALESMAN', 7698, 
 to_date('28-9-1981','dd-mm-yyyy'), 
 1250, 1400, 30 
)

insert into emp 
values( 
 7844, 'TURNER', 'SALESMAN', 7698, 
 to_date('8-9-1981','dd-mm-yyyy'), 
 1500, 0, 30 
)

insert into emp 
values( 
 7876, 'ADAMS', 'CLERK', 7788, 
 to_date('13-JUL-87', 'dd-mm-rr') - 51, 
 1100, null, 20 
)

insert into emp 
values( 
 7900, 'JAMES', 'CLERK', 7698, 
 to_date('3-12-1981','dd-mm-yyyy'), 
 950, null, 30 
)

insert into emp 
values( 
 7934, 'MILLER', 'CLERK', 7782, 
 to_date('23-1-1982','dd-mm-yyyy'), 
 1300, null, 10 
)

Create Table calendar As 
Select To_Char(v.day_dt,'YYYYMMDD') row_wid, v.day_dt, To_Char(v.day_dt, 'YYYY') year_name,  
       To_Char(v.day_dt, 'Q') qtr_name, To_Char(v.day_dt,'MM') month_name,  
       To_Char(v.day_dt,'IW') week_num  From ( 
  Select Trunc(Sysdate,'YYYY') + Level - 1 day_dt 
    From dual 
  Connect By Level <= Add_Months(Trunc(Sysdate,'YYYY'),12) - Trunc(Sysdate,'YYYY') 
) v

Create Table employees As  
Select rownum row_wid, v.empno, v.ename, 
       Coalesce(v.lvl1_mgr, v.ename) lvl1_mgr, 
       Coalesce(v.lvl2_mgr, lvl1_mgr, v.ename) lvl2_mgr, 
       Coalesce(v.lvl3_mgr, v.lvl2_mgr, lvl1_mgr, v.ename) lvl3_mgr 
  From ( 
Select e.empno, e.ename,  
      Trim('|' From RegExp_Substr(Sys_Connect_By_Path(e.ename,'|'),'\|[^\|]+',1,1)) Lvl1_mgr, 
      Trim('|' From RegExp_Substr(Sys_Connect_By_Path(e.ename,'|'),'\|[^\|]+',1,2)) Lvl2_mgr, 
      Trim('|' From RegExp_Substr(Sys_Connect_By_Path(e.ename,'|'),'\|[^\|]+',1,3)) Lvl3_mgr 
  From emp e 
Start With e.mgr Is Null 
Connect By Prior e.empno=e.mgr) v

Create Table emp_sales As 
Select To_Char(Trunc(Sysdate,'YYYY')+Round(dbms_random.value(0,364)),'YYYYMMDD') date_wid, 
       Round(dbms_random.value(1,(Select Count(*) From emp))) emp_wid, 
       Round(dbms_random.value(1,1000),2) amount 
  From dual 
Connect By Level < 100000

Create Bitmap Index emp_sales_cal On emp_sales(date_wid)

Create Bitmap Index emp_sales_emp On emp_sales(emp_wid)

Create Index cal_yr On calendar(year_name)

Create Index cal_mnt On calendar(month_name)

Create Index cal_wk On calendar(week_num)

Create Index cal_day On calendar(day_dt)

Create Index emp_no On employees(empno)

Create Index emp_mgr1 On employees(lvl1_mgr)

Create or Replace Attribute Dimension date_dim 
Using calendar 
Attributes ( 
  year_name, 
  qtr_name, 
  month_name, 
  week_num, 
  day_dt, 
  row_wid 
) 
Level cal_day 
  Key row_wid 
  Member Name To_Char(row_wid) 
  Order by row_wid 
  Determines (week_num) 
Level cal_week 
  Key week_num 
  Member Name To_Char(week_num) 
  Order by week_num 
  Determines (month_name) 
Level cal_month 
  Key month_name 
  Member Name To_Char(month_name) 
  Order by month_name 
  Determines (qtr_name) 
Level cal_qtr 
  Key qtr_name 
  Member Name To_Char(qtr_name) 
  Order by qtr_name 
  Determines (year_name) 
Level cal_year 
  Key year_name 
  Member Name To_Char(year_name) 
  Order by year_name 
All Member Name 'Total'

Create or Replace Attribute Dimension emp_dim 
Using employees 
Attributes ( 
  empno, 
  ename, 
  lvl1_mgr, 
  lvl2_mgr, 
  lvl3_mgr, 
  row_wid 
) 
Level emp 
  Key row_wid 
  Member Name To_Char(empno) 
  Order By empno 
  Determines (lvl3_mgr) 
Level lvl3_mgr 
  Key lvl3_mgr 
  Member Name lvl3_mgr 
  Order By lvl3_mgr 
  Determines (lvl2_mgr) 
Level lvl2_mgr 
  Key lvl2_mgr 
  Member Name lvl2_mgr 
  Order By lvl2_mgr 
  Determines (lvl1_mgr) 
Level lvl1_mgr 
  Key lvl1_mgr 
  Member Name lvl1_mgr 
  Order By lvl1_mgr 
All Member Name 'Total'

Create Hierarchy calendar_hier 
Using date_dim ( 
  cal_day Child Of 
  cal_month Child Of 
  cal_qtr Child Of 
  cal_year 
)

Create Hierarchy employee_hier 
Using emp_dim ( 
  emp Child Of 
  lvl3_mgr Child Of 
  lvl2_mgr Child Of 
  lvl1_mgr 
)

Create or Replace Analytic View emp_sales_av 
Using emp_sales 
Dimension By ( 
  date_dim 
    Key date_wid References row_wid Hierarchies (calendar_hier Default), 
  emp_dim 
    Key emp_wid References row_wid Hierarchies (employee_hier Default) 
) 
Measures ( 
  amount Fact amount 
)

Select calendar_hier.member_name, amount 
  From emp_sales_av Hierarchies (calendar_hier) 
Where calendar_hier.level_name = 'CAL_QTR' 
Order By calendar_hier.member_name