Creating the fact table

  • 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

  • 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


    Michael Weiss

  • 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

  • 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


    Michael Weiss

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

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

    Ray Higdon MCSE, MCDBA, CCNA

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

    Michael

    Michael Weiss


    Michael Weiss

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

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