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?

enter image description here

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 : 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 :


  • 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 =

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


I found the answer.

This concept is called a Role-Playing Dimensions

A single physical dimension can be referenced multiple times in a fact table, with each reference linking to a logically distinct role for the dimension. For instance, a fact table can have several dates, each of which is represented by a foreign key to the date dimension. It is essential that each foreign key refers to a separate view of the date dimension so that the references are independent. These separate dimension views (with unique attribute column names) are called roles.

From mmarie answer in stack-overflow .


enter image description here

  • There are two types of dimension role playing implementations, table alias and database view. the first one is presented above. May 9 '16 at 16:50
  • @YassineLAADIDI : what are difference between table alias and database view mentioned above ? Both are created at physical layer ? Thanks .
    – Luan Huynh
    May 9 '16 at 17:05
  • 1
    in physical laye you need just to use attributes in the fact table e.g. inssert_date. these two type are implemented using SQL statements. May 9 '16 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.