How many date dimensions for one fact

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 : enter image description here
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