1

I'm creating a warehouse using SQL Server 2008 and Analysis Services. I've managed to create and populate the dimension tables, but I'm having a lot of trouble writing the SQL for loading the fact table. For one thing, I'm not sure how to load the keys of the fact table with the PKs from the dimension table. I tried writing a query that had a series of JOINs to get the keys and the measures I want, but the statement got so complicated that I got lost.

This is the star schema that I have to work from:

https://i.stack.imgur.com/L2O24.png

What am I doing wrong? I have a feeling that I'm missing something pretty basic, but I'm fairly new to this and most of the information I found online seemed to deal with using SSIS, which I don't have installed.

Any help would be appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Natesh Mayuranathan
  • 21
  • 1
  • 4

1 Answers1

3

Todays Data Warehouse Developer uses SSIS for loading dimensional models. Typically, lookups are used to convert the dimensional attribute into a key. Most of the time, the data is going to be on another server or in a flat file or something else that forces you to use an ETL tool like SSIS, but in your case, you can get it done without. If your enterprise is serious about BI, you should push to get SSIS installed and learn it.

For your situation, assuming you have a table loaded with raw facts locally, you should be able to do an insert/select.

Basically, you'll want to inner join (since you've had no problems populating the dimension tables) each dimension to the raw facts table. Something like: 

INSERT trainingcentrefact
(timekey,locationkey,instructorkey,coursekey,paid,notpaid,... etc)
SELECT
   t.timekey
  ,l.locationkey
  ,i.instructorkey
  ,c.coursekey
  ,rf.paid
  ,rf.notpaid
  ,... etc
FROM rawfacts rf
INNER JOIN timedimension t ON rf.time = t.time
INNER JOIN locationdimension l on rf.location = l.location
INNER JOIN instructordimension i on rf.instructor = i.instructor
INNER JOIN coursedimension c on rf.course = c.course
brian
  • 3,635
  • 15
  • 17