What are the best practices for a time dimension?

Hot Topic

Shading By Value
Dimension as Parameter in Reporting Services
Using AMO to manipulate cube calculations
ODBC DSN for SQL Database
SNAPSHOT Replciation - Last distributed transaction LSN ?
SQL Server 2005 data export wizard did not transfer stored procedures
Report Model Deployment : The model ID of the submitted model must match that of the
Custom Sql Exceptions through the CLR
Error on bcp
Connect to Remote Analysis Server

SQL Server

Exporting Packages
How to read parent Package in Script Task
process cube
Data format conversion.
Inconsistent Errors Using Bulk Insert with a Format File
Custom ControlFlow
Concurrent snapshot still locking tables for a long time
Microsoft best practices for implementing windows authentication for sql server 2005
SetPolicy - Using SSRS web service
Freetext search not working after SQL 2005 upgrade

What are the best practices for a time dimension?

Data is timestamped. Using smalldatetime provides minute resolution. However, different reports may need 5 minute, 10 minute, 1 hour, daily, weekly, or monthly aggregates.

How should this be structured

Thanks,

David

Answer this question

What are the best practices for a time dimension?

  • Carl Perry - Microsoft

    In my experiencie the besto solution is to make a snowflake time dimension. A separate table for each level do you want to represent.
    Then you should specify hierarchies. For the key it's convenient to make them char and not timestamp. Because the representation of it depends of the client application.
    For example:
    Tables for time dimension:
    Dim_Date_STD_Year
    Dim_Date_QRT_Quarter
    Dim_Date_STD_Month
    Dim_Date_WEK_Week
    Dim_Date_STD_Day
    Dim_Date_HOU_Hour
    Dim_Date_HOU_Minute

    With this schema you can construct any hierarchie you want.
    For example:
    Hierarchie: Standard (Dim_Date_STD_Year
    , Dim_Date_STD_Month, Dim_Date_STD_Day)
    Hierarchie: Quarter (Dim_Date_STD_Year, Dim_Date_QRT_Quarter, Dim_Date_STD_Month, Dim_Date_STD_Day)

    I hope it helps you.

  • NitinVRaj

    You may also want to check the following articles:

  • Getting Time right in Analysis Services - by Tom Chester for SQL Junkies Web site http://www.sqljunkies.com/Article/D1E44392-592C-40DB-B80D-F20D60951395.scuk
  • About Time - by Brian Altmann http://www.geocities.com/brianaltmann/olaptopic-time.html

    ==============================­====================
    Mosha Pasumansky  - http://www.mosha.com/msolap
    Analysis Services blog at http://www.sqljunkies.com/WebL­og/mosha
    Development Lead in the Analysis Server team
    All you need is love (John Lennon)
    Disclaimer : This posting is provided "AS IS" with no warranties, and
    confers no rights.
    ==============================­====================


  • Frode G

    I setup a system dimension that I called date. After creating a cube, I add this dimension to my project and then use the dimension usage tab to manually add the date dimension and connect it to the different dimensions of my cube.

    I hope this helps,

    Taylor



  • What are the best practices for a time dimension?
    • Answer this question