1. Back To Blog

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

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


Also see our SQL Server interview questions video on triggers, inserted and deleted tables: -


Shiv Prasad Koirala

Visit us @ www.questpond.com or call us at 022-66752917... read more

We are on Social