SQL SERVER: What is the difference between Fact Table and dimensions?

Answer 1

Following can be identified as the difference between the two -

1) Dimension: It provides the context /descriptive information for fact table measurements.
Fact Table: It provides measurement of an enterprise.

2) Structure of Dimension - Surrogate key, one or more other fields that compose the natural key and set of Attributes.
Structure of Fact Table - foreign key, Degenerated Dimension and Measurements.

3) Size of Dimension Table is smaller than Fact Table.
Size of Fact Table is larger than Dimension Table.

4) In a schema more no of dimensions tables are presented than Fact tables.
In a schema less no of Fact tables are presented than Dimension tables.

5) Dimension Values of fields are in text representation.
Fact Table Values of the fields always in integer form.

6) Dimension: We can load the dimension table directly
Fact Table We can't load the fact table first. So to load the fact table we need to load the dimension table first. Also while loading the fact table we will make a lookup on the dimension table cause the fact table contains the measures/facts & the foreign keys which are primary keys in the dimension tables surrounded to that fact table.

Answer 2

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)