1. Back To Blog

SQL Server interview questions: - What is the difference between Fact Table and Dimensions?

• By Shiv Prasad Koirala in SQL
• May 7th, 2014
• 11
• 0

Fact Table contains two things: -

1. Foreign keys which pointing to primary key in dimension tables.
2. Measures - This identifies some numbers in a business. Example Total number of sales, amount given, purchased amount etc.
   
   

Dimension tables contain denormalized data. Every record in this table will contain a unique identification key which will refer by Fact Tables.

Data warehouse design - It's all about creating dimensions and fact tables.

1)  Dimensions are extracted from traditional database. Multiple tables in a traditional table are joined and considered as one

     dimension table.

     Example - Let say we have following database with use

     Customer ( CustomerId, CustomerName, CustomerMobile, CItyId, GroupId)

     City (CityId,CityName)

     Group(GroupId, GroupName)

     Dimension table will look like this,

     CustomerDimension(CustomerId,CustomerName,CustomerMobile,CityName,GroupName)

2)  Fact table will be created containing only numbers and foreign keys pointing to other dimensions.
     Now let say we have following dimensions

    CustomerDimension(CustomerId,CustomerName,CustomerMobile,CityName,GroupName)

    TimeDimension(Timekey,Year,Month,Day)

    ProductDimension(ProductId,ProdudctName,ProductPrice, ....)

    Now One Fact table look like

    ProductSale(ProductId,CustomerId,TimeKey,QtySold)

For technical trainings on various topics like WCF, MVC, Business Intelligence, Design Patterns, WPF, TFS and basic fundamentals feel free to contact SukeshMarla@Gmail.com or visit www.sukesh-marla.com

For more stuff like this, click here. Subscribe to article updates or follow at twitter @SukeshMarla

See the video explaining triggers, inserted and deleted tables in SQL Server.

Watch below latest interview questions and answers video on SQL Server :-