Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Loading data into a fact table

Author  Topic 

newdwlearner
Starting Member

2 Posts

Posted - 2013-01-20 : 19:32:17
Hello all, I'm new to sql and trying to create and populate three dimensions and a fact table with data. The dimensions work just fine but i'm unable to get any data into my fact table. If anyone will be so kind to point out what i'm doing wrong - i will be grateful.

Thank you all very much.

This is my code

[create table Customer
(
CUID integer identity(1,1) primary key,
Name varchar (25)
)
insert into Customer select distinct customer from [Data]

create table Agent
(
AID integer identity(1,1) primary key,
Agent varchar (25)
)
insert into Agent select distinct Seller from [Data]

create table Time
(
TID integer identity(1,1) primary key,
Week varchar (25),
Month varchar (25),
Year int
)
insert into Time
(Week,Month,Year)
select distinct Day,Month,Year from [Data]

Create Table Fact
(
CUID integer,
AID integer,
TID integer,
Sale money,
constraint pk_Fact primary key (CUID, AID, TID),
constraint fk1_Fact foreign key (CUID)
references Customer (CUID),
constraint fk2_Fact foreign key (AID)
references Agent (AID),
constraint fk3_Fact foreign key (TID)
references Time (TID),
)
insert into Fact
(CUID, AID, TID, Sale)
SELECT CUID, AID, TID, Sale
FROM Customer,
Agent,
Time,
[Data]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-20 : 20:17:08
One comment - The insert into the Time Dimension does not seem right - based on what I can infer from the column names. You are inserting something into a column named Week data that comes from a column named Day.
insert into Time
(Week,Month,Year)
select distinct Day,Month,Year from [Data]
That aside, your insert into the Fact table should be like this:
INSERT INTO Fact
(CUID, AID, TID, Sale)
SELECT
CUID, AID, TID, Sale
FROM
[Data] d
INNER JOIN Agent a ON
a.Agent = d.Seller
INNER JOIN Time t ON
t.Week = d.Day AND t.Month = d.Month AND t.Year = d.Year
INNER JOIN Customer c ON
c.Name = d.Customer
Go to Top of Page

newdwlearner
Starting Member

2 Posts

Posted - 2013-01-20 : 20:54:05
You sir are a genius - God bless you, i'm very grateful.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-21 : 01:22:51
Though this will work fine for initial population, for subsequent population you need to find a way of getting only records from parent tables which got inserted in current run. Do you've timestamp(datetime) or an identity field) for that?

Alternatively you could use OUTPUT clause to retrieve the inserted ids

http://msdn.microsoft.com/en-us/library/ms177564.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -