SQLTeam.com | Weblogs | Forums

Help with getting data from DIM and Fact Tables


#1

When I run the select statement at the end, I'm receiving the "no selected statement" message even though I selected some variables.

Also I am having trouble inputting data into the FACT Table

REM Drop table
REM ============================================
REM ADD the DROP TABLES After Here
REM ============================================
Drop table Dummy cascade constraint;
Drop table Budget_Fact cascade constraint;
Drop table Dept_Dim cascade constraint;
Drop table Time_Dim cascade constraint;

REM ============================================
REM ADD the DROP SEQUENCE After Here
REM ============================================
drop sequence Deptidseq;
drop sequence timeidseq;
REM DO NOT CHANGE!!!!!
REM ============================================
CREATE TABLE DUMMY(DUMMY NUMBER);
INSERT INTO DUMMY VALUES (0);
REM ============================================

REm Create Tables
REM ============================================
REM ADD the CREATE TABLE STATEMENT for
REM all Dim and Fact Tables After Here!!!!
REM ============================================
Create table time_dim (
timeid number(8),
hireyear Date,
Constraint timeid_pk Primary Key (timeid));

Create Table dept_dim (
deptid number (8),
deptno number (6),
dname varchar2(14),
Constraint deptid_pk Primary Key (deptid));

Create Table Budget_Fact(
timeid number(8),
deptid number(8),
totalbudget Number (10,2),
constraint timeid_fk Foreign Key (timeid) references time_dim(timeid),
constraint deptid_fk Foreign Key (deptid) references dept_dim(deptid));

REM ============================================
REM Add the SEQUENCE Code here for each
REM Surrogate Key(s).
REM ============================================
create sequence deptidseq;
create sequence timeidseq;
REM ============================================
REM Add the Trigger Code for each Dim table.
REM ============================================
CREATE OR REPLACE TRIGGER departmenttrig
BEFORE INSERT ON dept_dim
FOR EACH ROW
BEGIN

SELECT Deptidseq.NextVal INTO :New.Deptid 
FROM Dummy;

END;
/

Create OR Replace Trigger timeidtrig
Before Insert ON Time_dim
For each row
Begin
Select timeidseq.nextval Into :New.timeid
From dummy;
End;
/

REM ============================================
REM Add the Function Code for each Dim table.
REM ============================================
CREATE OR REPLACE FUNCTION GetTimeId (in_YY IN VarChar2)

 Return Number

IS

Timeid_Num	Number(12);
TmpIn_Date VARCHAR2(12);

BEGIN

TmpIn_Date := '01-JAN-' || in_YY;

SELECT TimeId INTO TimeId_Num
From Time_Dim
WHERE to_date(HireYear, 'DD-MON-YY') = to_date(TmpIn_Date,'DD-MON-YY');

RETURN TimeId_Num;

End;
/

Create or Replace Function GetDeptID (inDeptNo IN varchar2)
Return Number
Is
DeptID_num number(8);
Begin
Select Deptid into DeptID_num
From Dept_Dim
where Deptno = inDeptNo;
Return DeptID_num;
End;
/

Create or Replace Function GetTotalBudget(in

REM INSERT . . . SELECT
REM ============================================
REM Add the INSERT . . . SELECT Code here.
REM ============================================
Insert Into Time_Dim (hireyear)
Select Distinct hiredate
From emp;

Insert Into Dept_Dim (deptno,dname)
Select deptno, dname
From dept;

Insert Into Budget_Fact (Timeid, Deptid, TotalBudget)
Select GetTimeID(To_Char(HireDate,'YYYY')),GetDeptID(DeptNo), Sum(Sal)
From EMP
Group By To_Char(HireDate,'YYYY'), DeptNo;

REM Formatting Statements
REM ============================================
column HireYear format a10
column Department format a15
column TotalBudget format $9,999,999.99
REM ============================================

REM SELECT
REM ============================================
REM Add SELECT with ROLLUP from Data Warehouse
REM ============================================
Select D.DeptID ||'' ||D.DName As Department, HireYear, TotalBudget
From Time_Dim T, Dept_Dim D, Budget_Fact B
Where (B.Timeid= T.Timeid) and (B.DeptId= D.DeptId);

select * from Budget_Fact;

REM SELECT
REM ============================================
REM Add SELECT with ROLLUP from Source Databased
REM ============================================
Select
From
Where
Group By Rollup


#2

That's not T-SQL. This is a SQL Server forum