Creating the fact table

Question

Creating the fact table

ray_higdon

SSCarpal Tunnel

Points: 4589
More actions
December 6, 2002 at 6:04 am

#105384

I realize this is a begginer question but I have to ask, how do you create the fact table? I get the idea that all of the columns are pointed to PK's on your dimension tables but what is the best way to create the fact table with all the pointers? I am just now getting a decent place to put my cubes and due to space issues I have just put everything in one table and created dimensions off of that, any help would be appreciated
------------

Ray Higdon MCSE, MCDBA, CCNA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 1

Here is a link to a brief article and example of a fact table. Basically, you want your fact table to consist of foreign keys pointing to the dimension tables. Also, the fact table will include the numerical (usually but not always!) columns containing the data that will make up your measures. Obviously, this is an over-simplification of what can be a very complex issue depending on your data, business requirements, etc. Hopefully this will help shed some light on the subject though...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdw/createdw_20fn.asp

hth,

Michael

Michael Weiss

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 2

I am begining to understand the concept, Now just trying to figure out how to take a date and change it to a pointer to a PK in the dimensional table, not sure how to do it yet.

------------

Ray Higdon MCSE, MCDBA, CCNA

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 3

If you set your date dimension table up with a surrogate key (say an integer identity column), then your foreign key column in your fact table would be of the type integer. Do a lookup in your transformation and loading processes between the raw data inserted in your staging table (prior to loading the new records into the fact table) and the date dimension table and insert in your staging table the surrogate key value for the matching date record found in the date dimension table. Once all of your records in the staging table are properly coded with corresponding dimension key values you can simply insert the records in the staging table into your fact table and you are good to go.

UPDATE s SET s.date_key = d.date_key FROM staging_table s JOIN date_dimension_table d ON s.date_column = d.date_column

...or something like that.

hth,

Michael

Michael Weiss

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 4

With your help and a few books, I have it working! Thanks much

------------

Ray Higdon MCSE, MCDBA, CCNA

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 5

You are welcome! If you have any more questions, I am happy to help if I can...

Michael

Michael Weiss