SQL Server Performance Forum – Threads Archive
Building a CUBE in non-equi join case
Hi, Can anyone tell me how to build a cube using emp and salgrade tables of Oracle? select * from Emp e, salgrade swhere e.sal between s.losal and s.hisal; CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL,
CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO)); INSERT INTO EMP VALUES (7839,’KING’,’PRESIDENT’,NULL,’17-NOV-81′,5000,NULL,10);
INSERT INTO EMP VALUES (7698,’BLAKE’,’MANAGER’,7839,’1-MAY-81′,2850,NULL,30);
INSERT INTO EMP VALUES (7782,’CLARK’,’MANAGER’,7839,’9-JUN-81′,2450,NULL,10);
INSERT INTO EMP VALUES (7566,’JONES’,’MANAGER’,7839,’2-APR-81′,2975,NULL,20);
INSERT INTO EMP VALUES (7654,’MARTIN’,’SALESMAN’,7698,’28-SEP-81′,1250,1400,30);
INSERT INTO EMP VALUES (7499,’ALLEN’,’SALESMAN’,7698,’20-FEB-81′,1600,300,30);
INSERT INTO EMP VALUES (7844,’TURNER’,’SALESMAN’,7698,’8-SEP-81′,1500,0,30);
INSERT INTO EMP VALUES (7900,’JAMES’,’CLERK’,7698,’3-DEC-81′,950,NULL,30);
INSERT INTO EMP VALUES (7521,’WARD’,’SALESMAN’,7698,’22-FEB-81′,1250,500,30);
INSERT INTO EMP VALUES (7902,’FORD’,’ANALYST’,7566,’3-DEC-81′,3000,NULL,20);
INSERT INTO EMP VALUES (7369,’SMITH’,’CLERK’,7902,’17-DEC-80′,800,NULL,20);
INSERT INTO EMP VALUES (7788,’SCOTT’,’ANALYST’,7566,’09-DEC-82′,3000,NULL,20);
INSERT INTO EMP VALUES (7876,’ADAMS’,’CLERK’,7788,’12-JAN-83′,1100,NULL,20);
INSERT INTO EMP VALUES (7934,’MILLER’,’CLERK’,7782,’23-JAN-82′,1300,NULL,10); CREATE TABLE SALGRADE (
GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER); INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999); Thanks,
Sankar
Hi Sankar,
In analysis services you can not have a dimension which is not linked to fact table in any ways.
Even if you create it, it will not have impact in cube while slicing it to analyse the historic data.
Alternative is to create dummy tables which will hold relation from salgrade table with fact data.
Can you explain your complete scenario, what data are you trying to analyse and measure.
Also, what role SALGRADE will be playing in this cube.
Are you implementing this cube in MSAS2000/MSAS2005?
Hi Ranjit, Thanks for your response. I am implementing the cube in MSAS2005. Here I would like to know the grade of the employee based on the salary. I know here there is no relation between Emp and SalGrade(Even Grade field has the Primary Key) tables. How we can accomplish the result (run the above query) using dummy tables. Thanks,
Sankar
Hi Sankar,
If you are implementing the cube in 2005 then you don’t need to create any dummy dim tables for joining.
In 2005, ms has introduced a new feature i.e. Named Queries.
Using named queries, you can have the grade as attribute of each employee.
Named queries is like creating a SQL query and adding it as table in data source view. Following query will act as your named query and grade you can set as attribute of each employee
select e.*,grade from emp e
inner join salgrade s on e.sal>=s.losal and e.sal<=s.hisal Also Please read more about named queries in BOL.
]]>