none
Fact table design help please RRS feed

  • Question

  • I want to design a fact table to report the name, ssn, mothers name, fathers name, sisters name, address, zip, birthday of every patient . what shud be mu fact table design be like, also, what are the different dimensions design?

    Sunday, May 27, 2012 4:53 AM

Answers

  • Hi,

    Firstly you should know about star and snow flake concepts of data warehouse. suppose that you are going to implement a star schema to explore and analyze data. Measures or facts would be in the center of your star schema while surrounding by dimensions. Facts are what you want to measure and dimensions are where, whom, when, who and ... .  dimensions have a one-to-many relationship with fact tables.

    This is a part of may DW. There is three dimensions and one fact table

    Sunday, May 27, 2012 12:17 PM
  • Hello,

    Here is an article about introduction to Dimensions(Analysis Services - Multidimensional Data) for your reference, please see:
    http://msdn.microsoft.com/en-us/library/ms175439.aspx

    Regards,
    Bin Long


    Bin Long

    TechNet Community Support

    Wednesday, May 30, 2012 6:04 AM
    Moderator

All replies

  • Hi,

    Fact table normally contains measures a.k.a facts along with reference keys to the dimension tables. Dimension table contains details about the dimension member which could be used for slicing and dicing the information stored in the fact tables. Their are typically 2 designs methodologies been followed - Star schema and Snowflake schema. You can see plethora amount of details in internet about these topics.

    In your case, at a high level, you need a fact table which is referencing to other dimension tables like - Patient, Date, Hospital etc. etc. depending upon your business requirements. In each dimension table you need to have a Primary Key (Identity key or Surrogate Key) which is then used by Fact table for referencing them.

    HTH.

    Regards,
    Santosh


    http://microsoftbizintel.wordpress.com/

    Sunday, May 27, 2012 5:25 AM
  • Hi,

    Firstly you should know about star and snow flake concepts of data warehouse. suppose that you are going to implement a star schema to explore and analyze data. Measures or facts would be in the center of your star schema while surrounding by dimensions. Facts are what you want to measure and dimensions are where, whom, when, who and ... .  dimensions have a one-to-many relationship with fact tables.

    This is a part of may DW. There is three dimensions and one fact table

    Sunday, May 27, 2012 12:17 PM
  • Hello,

    Here is an article about introduction to Dimensions(Analysis Services - Multidimensional Data) for your reference, please see:
    http://msdn.microsoft.com/en-us/library/ms175439.aspx

    Regards,
    Bin Long


    Bin Long

    TechNet Community Support

    Wednesday, May 30, 2012 6:04 AM
    Moderator