Forum Stats

  • 3,851,368 Users
  • 2,263,966 Discussions
  • 7,904,689 Comments

Discussions

Dynamic Columns

Apologies in advance for the length and complexity of this; I seem to be trying an advanced maneuver with newbie skills.

Here's what I have:
<li>The sample data DEPT table
<li>The sample data EMP table (+ some extra rows)
<li>A table of Custom Attributes, CUSTOM_ATTRIBUTE, (e.g. Favorite Sport, Number of Children)
<li>A table of Custom Attribute Values, CUSTOM_ATTRIBUTE_VALUES (e.g., Baseball, Football, 0, 1, >5). Each Custom Attribute will have multiple values available.
<li>A table that maps the Custom Attributes to Departments. Each Department can have 1 or more CAs, (e.g., Accounting: Years Experience, Education Level; Research: Favorite Sport)
<li>A table that maps the Custom Attribute Values to Employees. Each Employee can have one value per Custom Attribute. The CAs available are determined by the mapping of the Employee's Department to the Custom Attribute. (e.g., Clark: Years Experience - 1, Education Level - Bachelor's Degree; Jones: Favorite Sport - Football)

Query #1 shows all data, once loaded.

Here's what I would like: To be able to treat the Custom Attributes as Columns in OBI, so that they could be used in various reports, including the ability to use them in pivot tables and compare them against each other. (e.g., Query #2 shows what I would like to work with for the Accounting Department.)

<li>The Custom Attributes that are mapped to a Department are fairly stable, but can be added or removed at any time.
<li>The fact that would tie the info together would be the # of employees.
<li>There wouldn't be any need to compare one department against another.
<li>Currently, the number of Custom Attributes per Department is unlimited, but it could probably be restrained to 10 or 20 without the users getting up in arms.

Is this possible? If so, how?

Thanks! :D

--JenniferS

(Creates, inserts, queries follow)
-- Default sample data DEPT table
CREATE TABLE "DEPT" 
 (	"DEPTNO" NUMBER(2,0), 
	"DNAME" VARCHAR2(14 BYTE), 
	"LOC" VARCHAR2(13 BYTE), 
	 PRIMARY KEY ("DEPTNO") ENABLE
 );

Insert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');

/

-- Sample data EMP table + extra rows for DEPTNO = 40
CREATE TABLE "EMP" 
 (	"EMPNO" NUMBER(4,0) NOT NULL ENABLE, 
	"ENAME" VARCHAR2(10 BYTE), 
	"JOB" VARCHAR2(9 BYTE), 
	"MGR" NUMBER(4,0), 
	"HIREDATE" DATE, 
	"SAL" NUMBER(7,2), 
	"COMM" NUMBER(7,2), 
	"DEPTNO" NUMBER(2,0), 
	 PRIMARY KEY ("EMPNO")ENABLE, 
	 FOREIGN KEY ("MGR")
	  REFERENCES "EMP" ("EMPNO") ENABLE, 
	 FOREIGN KEY ("DEPTNO")
	  REFERENCES "DEPT" ("DEPTNO") ENABLE
 );

Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7839,'KING','PRESIDENT',10);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7934,'MILLER','CLERK',10);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7782,'CLARK','MANAGER',10);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7788,'SCOTT','ANALYST',20);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7876,'ADAMS','CLERK',20);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7369,'SMITH','CLERK',20);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7902,'FORD','ANALYST',20);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7566,'JONES','MANAGER',20);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7698,'BLAKE','MANAGER',30);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7900,'JAMES','CLERK',30);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7844,'TURNER','SALESMAN',30);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7654,'MARTIN','SALESMAN',30);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7521,'WARD','SALESMAN',30);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (7499,'ALLEN','SALESMAN',30);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (1111,'DAISY','MANAGER',40);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (2222,'GOOFY','OPERATOR',40);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (3333,'MINNIE','OPERATOR',40);
Insert into EMP (EMPNO,ENAME,JOB,DEPTNO) values (4444,'DAFFY','OPERATOR',40);

/

-- Custom attributes that will have associated values and will be mapped to a department
CREATE TABLE "CUSTOM_ATTRIBUTE" 
 (	"CA_ID" NUMBER NOT NULL ENABLE, 
	"NAME" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	 CONSTRAINT "CUSTOM_ATTRIBUTE_PK" PRIMARY KEY ("CA_ID") ENABLE
 );

Insert into CUSTOM_ATTRIBUTE (CA_ID,NAME) values (1,'Favorite Sport');
Insert into CUSTOM_ATTRIBUTE (CA_ID,NAME) values (2,'Years Experience');
Insert into CUSTOM_ATTRIBUTE (CA_ID,NAME) values (3,'Number of Children');
Insert into CUSTOM_ATTRIBUTE (CA_ID,NAME) values (4,'Education Level');

/

-- Custom attribute values that belong to a Custom Attribute and will be mapped to an employee
CREATE TABLE "CUSTOM_ATTRIBUTE_VALUE" 
 (	"CA_VAL_ID" NUMBER NOT NULL ENABLE, 
	"CA_ID" NUMBER NOT NULL ENABLE, 
	"VALUE" VARCHAR2(255 BYTE) NOT NULL ENABLE, 
	 CONSTRAINT "CUSTOM_ATTRIBUTE_VALUE_PK" PRIMARY KEY ("CA_VAL_ID") ENABLE, 
	 CONSTRAINT "CAV_CA_ID_FK" FOREIGN KEY ("CA_ID")
	  REFERENCES "CUSTOM_ATTRIBUTE" ("CA_ID") ENABLE
 );

Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (1,1,'Baseball');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (2,1,'Football');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (3,1,'Basketball');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (4,1,'Other');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (5,2,'< 1');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (6,2,'1-3');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (7,2,'4-6');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (8,2,'7-10');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (9,2,'> 10');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (10,3,'0');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (11,3,'1');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (12,3,'2');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (13,3,'4');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (14,3,'5');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (15,3,'> 5');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (16,4,'High School Diploma');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (17,4,'Some College');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (18,4,'Bachelor''s Degree');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (19,4,'Master''s Degree');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (20,4,'PhD');
Insert into CUSTOM_ATTRIBUTE_VALUE (CA_VAL_ID,CA_ID,VALUE) values (21,4,'Other');

/

-- Mapping between Custom Attributes and Departments
CREATE TABLE "MAP_DEPT_CA" 
 (	"ID" NUMBER NOT NULL ENABLE, 
	"DEPTNO" NUMBER NOT NULL ENABLE, 
	"CA_ID" NUMBER NOT NULL ENABLE, 
	 CONSTRAINT "MAP_DEPT_CA_PK" PRIMARY KEY ("ID") ENABLE, 
	 CONSTRAINT "MDC_DEPTNO_FK" FOREIGN KEY ("DEPTNO")
	  REFERENCES "DEPT" ("DEPTNO") ENABLE
 ) ;
 
Insert into MAP_DEPT_CA (ID,DEPTNO,CA_ID) values (1,10,2);
Insert into MAP_DEPT_CA (ID,DEPTNO,CA_ID) values (2,10,4);
Insert into MAP_DEPT_CA (ID,DEPTNO,CA_ID) values (3,20,1);
Insert into MAP_DEPT_CA (ID,DEPTNO,CA_ID) values (4,30,2);
Insert into MAP_DEPT_CA (ID,DEPTNO,CA_ID) values (5,30,3);
Insert into MAP_DEPT_CA (ID,DEPTNO,CA_ID) values (6,40,1);
Insert into MAP_DEPT_CA (ID,DEPTNO,CA_ID) values (7,40,2);
Insert into MAP_DEPT_CA (ID,DEPTNO,CA_ID) values (8,40,3);
Insert into MAP_DEPT_CA (ID,DEPTNO,CA_ID) values (9,40,4);

/

-- Mapping between Custom Attribute Values and Employees
CREATE TABLE "MAP_EMP_CA_VAL" 
 (	"ID" NUMBER NOT NULL ENABLE, 
	"EMPNO" NUMBER NOT NULL ENABLE, 
	"CA_VAL_ID" NUMBER NOT NULL ENABLE, 
	 CONSTRAINT "MAP_EMP_CA_VAL_PK" PRIMARY KEY ("ID") ENABLE
 ) ;

Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (1,7782,7);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (2,7782,18);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (3,7934,5);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (4,7934,16);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (5,7839,9);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (6,7839,19);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (7,7788,1);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (8,7566,2);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (9,7902,3);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (10,7369,4);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (11,7654,8);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (12,7654,10);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (13,7521,6);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (14,7521,12);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (15,7499,7);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (16,7499,15);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (17,1111,2);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (18,1111,9);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (19,1111,13);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (20,1111,21);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (21,2222,1);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (22,2222,8);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (23,2222,11);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (24,2222,16);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (25,3333,4);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (26,3333,9);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (27,3333,14);
Insert into MAP_EMP_CA_VAL (ID,EMPNO,CA_VAL_ID) values (28,3333,18);

/

COMMIT;

/

-- QUERY #1
-- List of all mapped Departments, Employees, Custom Attributes, 
-- and Custom Attribute Values
SELECT D.deptno
     , D.dname
     , E.empno
     , E.ename
     , MDC.ca_id
     , CA.name ca_name
     , MDCV.ca_val_id
     , CAV.value
  FROM dept D
     , emp E
     , map_dept_ca MDC
     , custom_attribute CA
     , map_emp_ca_val MDCV
     , custom_attribute_value CAV
 WHERE E.deptno = D.deptno
   AND MDC.deptno = D.deptno
   AND CA.ca_id = MDC.ca_id
   AND MDCV.empno = E.empno
   AND MDCV.ca_val_id = CAV.ca_val_id
   AND CAV.ca_id = MDC.ca_id
ORDER BY D.deptno
       , E.empno
       , MDC.ca_id;

/

-- Desired "end state" in OBI; want to be able to treat Custom Attributes as columns
-- for the purposes of reporting, even though the Attributes will vary 
-- from Department to Department.  (The associated Fact would be the number of employees).

-- QUERY #2
-- ACCOUNTING Department
SELECT deptno
     , dname
     , empno
     , ename
     , MAX( DECODE( ca_name, 'Years Experience', value, NULL ) ) YEARS_EXPERIENCE
     , MAX( DECODE( ca_name, 'Education Level', value, NULL ) ) EDUCATION_LEVEL
  FROM (     
SELECT D.deptno
     , D.dname
     , E.empno
     , E.ename
     , MDC.ca_id
     , CA.name ca_name
     , MDCV.ca_val_id
     , CAV.value
  FROM dept D
     , emp E
     , map_dept_ca MDC
     , custom_attribute CA
     , map_emp_ca_val MDCV
     , custom_attribute_value CAV
 WHERE E.deptno = D.deptno
   AND MDC.deptno = D.deptno
   AND CA.ca_id = MDC.ca_id
   AND MDCV.empno = E.empno
   AND MDCV.ca_val_id = CAV.ca_val_id
   AND CAV.ca_id = MDC.ca_id
)
WHERE deptno = 10
GROUP BY deptno, dname, empno, ename;

-- QUERY #3
-- RESEARCH department
SELECT deptno
     , dname
     , empno
     , ename
     , MAX( DECODE( ca_name, 'Favorite Sport', value, NULL ) ) FAVORITE_SPORT
  FROM (     
SELECT D.deptno
     , D.dname
     , E.empno
     , E.ename
     , MDC.ca_id
     , CA.name ca_name
     , MDCV.ca_val_id
     , CAV.value
  FROM dept D
     , emp E
     , map_dept_ca MDC
     , custom_attribute CA
     , map_emp_ca_val MDCV
     , custom_attribute_value CAV
 WHERE E.deptno = D.deptno
   AND MDC.deptno = D.deptno
   AND CA.ca_id = MDC.ca_id
   AND MDCV.empno = E.empno
   AND MDCV.ca_val_id = CAV.ca_val_id
   AND CAV.ca_id = MDC.ca_id
)
WHERE deptno = 20
GROUP BY deptno, dname, empno, ename;

-- QUERY #4
-- SALES department
SELECT deptno
     , dname
     , empno
     , ename
     , MAX( DECODE( ca_name, 'Years Experience', value, NULL ) ) YEARS_EXPERIENCE
     , MAX( DECODE( ca_name, 'Number of Children', value, NULL ) ) NUMBER_OF_CHILDREN
  FROM (     
SELECT D.deptno
     , D.dname
     , E.empno
     , E.ename
     , MDC.ca_id
     , CA.name ca_name
     , MDCV.ca_val_id
     , CAV.value
  FROM dept D
     , emp E
     , map_dept_ca MDC
     , custom_attribute CA
     , map_emp_ca_val MDCV
     , custom_attribute_value CAV
 WHERE E.deptno = D.deptno
   AND MDC.deptno = D.deptno
   AND CA.ca_id = MDC.ca_id
   AND MDCV.empno = E.empno
   AND MDCV.ca_val_id = CAV.ca_val_id
   AND CAV.ca_id = MDC.ca_id
)
WHERE deptno = 30
GROUP BY deptno, dname, empno, ename;

-- QUERY #5
-- OPERATIONS department
SELECT deptno
     , dname
     , empno
     , ename
     , MAX( DECODE( ca_name, 'Years Experience', value, NULL ) ) YEARS_EXPERIENCE
     , MAX( DECODE( ca_name, 'Number of Children', value, NULL ) ) NUMBER_OF_CHILDREN
     , MAX( DECODE( ca_name, 'Favorite Sport', value, NULL ) ) FAVORITE_SPORT
     , MAX( DECODE( ca_name, 'Education Level', value, NULL ) ) EDUCATION_LEVEL
  FROM (     
SELECT D.deptno
     , D.dname
     , E.empno
     , E.ename
     , MDC.ca_id
     , CA.name ca_name
     , MDCV.ca_val_id
     , CAV.value
  FROM dept D
     , emp E
     , map_dept_ca MDC
     , custom_attribute CA
     , map_emp_ca_val MDCV
     , custom_attribute_value CAV
 WHERE E.deptno = D.deptno
   AND MDC.deptno = D.deptno
   AND CA.ca_id = MDC.ca_id
   AND MDCV.empno = E.empno
   AND MDCV.ca_val_id = CAV.ca_val_id
   AND CAV.ca_id = MDC.ca_id
)
WHERE deptno = 40
GROUP BY deptno, dname, empno, ename;
Tagged:
This discussion has been closed.