Almost all BI projects consider Time a dimension. It may have different granularity such as year, quarter, month, week or day. While there should be only one Time dimension in all related projected we can give it a different meaning like Create Date, Sell Date etc.

Members of the Time dimension should span from the 1^st of January of the lowest year being analyzed to the 31^st December of the last year in interest. We also should create one member to match the facts with the unknown date. A good practice is to use “1900-01-01” for this purpose, as it is equal 0 when converted to a number.

If you would like to name the members by yourself don’t us the “Server time dimension”. It is a simple and quick method of creating the dimension but gives you very little control of the members value. Much better way is to create the time dimension using your own table.

Make sure that you match all relations in the hierarchies. The dimension will work with default relations between attributes but if you join the dimension on not key granularity it won’t be work in the hierarchy as you want.

In ISO standard there are 52 weeks in year. First week of the year doesn’t have to start on the 1^st of January as well as the last week of year doesn’t have to end on December 31. In some cases January 1-3 may belong to the previous year and December 29-31 may belong to the next year. If you want to create ISO week don’t use the same key column for year level as in the month hierarchy. If you use the same key for year in both week and month hierarchies, you may get invalid results with the members between December 29 and January 3.