How many date dimensions for one fact About designing star schema: I have three date columns in a fact table fact_table (insert_date, trade_date, close_date ...). And I don't know how many date dimensions should be created?



Case 1: Dim A . It means: one row @fact_table have three FKs to A.

Case 2: Dim A (for insert_date), Dim B (trade_date),Dim C (close_date). It means: one row @fact_table have one FK to A, one FK to B, and one FK to C .

Question: How many date dimensions should be created?
A dimension represent a category of information: e.g. date, product...
  in your case you have three attributes in the fact table referring to
  the same analysis axis which is 'DATE', so if you are using a star
  schema only one date dimension is needed, this is a dimension role playing implementation.
  There are two dimension role playing implementations : 



The table alias type uses the dimension more than once in an SQL
statement by assigning an alias for each use.
The database view type, you create as many views as the number of
roles you need the dimension on the fact.


You need to only one date dimension, for exemple  : 
the 'type' of date dimension will mentioned in the fact table as above.



  Updated :


Modeling Date and Time dimensions
There should be only one Date dimension and one Time dimension across all facts and dimension tables.  Not all date and time fields needed to be mapped to date and time dimensions. Map a date-time field to a Date or Time dimension only when you need the extra attributes from the dimension.
Typically the lowest granularity of the Date dimension is Day, and the lowest granularity of the Time dimension is Second. Many warehouses do not need a Time dimension, but a Date dimension is used in virtually every data warehouse system.
In general, create separate dimensions for Date and Time.
If it is necessary to extract contiguous chunks of time that cross day boundaries (for example 11/24/2000 10 p.m. to 11/25/2000 6 a.m.), then it is easier if the hour and day are in the same dimension. Otherwise, separate dimensions for Date and Time are easier to manage and to query.
It is easier to analyze cyclical and recurring daily events if Date and Time are separate dimensions. 
Example:
Number of meetings active at 9AM this week.  This is an easy query if Date and Time are separate; it is much more complex if Date and Time are combined into a single dimension.
Following are issues of having date and time dimensions together in one Date/Time dimension : 


  Issue#1:  
  
  
  Combining Date and Time into a single dimension creates an extremely    large dimension, particularly if you use Seconds as the
  grain. The    problem is not as great if you use Hour as the grain of
  the combined    table.
  Cardinality of Date and Time Dimensions (separated) for 10 years of    data Date Dimension:  10 * 365 = 3650 Time Dimension (granularity:
  seconds): 24 * 60 * 60 = 86400
  Cardinality of Date and Time Dimensions (combined) for 10 years of    data DateTime Dimensions (granularity Hourly): 10 * 365 * 24 = 87600
  DateTime Dimensions (granularity Seconds): 10 * 365 * 24 * 60 * 60 =
  315,360,000
  


The more records in the DateTime dimension, the slower query performance will be. 


  Issue #2 Having both date and time dimension in the same dimension can
  cause interpretation problems for fact tables that have granularity of
  a Day. It would be easy to inadvertently enter two records into the
  fact table for the same day, because of the extra granularity in the
  dimension table.


Find more : source