SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Loading data into a fact table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

newdwlearner
Starting Member

2 Posts

Posted - 01/20/2013 :  19:32:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 01/20/2013 :  20:17:08  Show Profile  Reply with Quote
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 - 01/20/2013 :  20:54:05  Show Profile  Reply with Quote
You sir are a genius - God bless you, i'm very grateful.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/21/2013 :  01:22:51  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000