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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

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
    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)
        FROM Customer, 

James K
Flowing Fount of Yak Knowledge

3873 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
    select distinct Day,Month,Year from [Data]
That aside, your insert into the Fact table should be like this:
    (CUID, AID, TID, Sale)
    CUID, AID, TID, Sale
    [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

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

Very Important crosS Applying yaK Herder

52326 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

SQL Server MVP

Go to Top of Page
  Previous Topic Topic Next 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.03 seconds. Powered By: Snitz Forums 2000