Prologika Forums
Making sense of data
Smart Date Keys Got Smarter

Blogs

Prologika (Teo Lachev's Weblog)

Books

Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling)Learn PowerPivot and Analysis Services-Tabular at your own pace with our latest book Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling). It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Training

Applied Microsoft SQL Server 2008 Reporting Services

We offer onsite and online Business Intelligence classes!  Contact us about in-person training for groups of five or more students.

For more information or to register click here! 

Syndication

Archives

Using "smart" date surrogate integer keys in the format YYYYMMDD is a dimensional modeling best practice and venerable design technique. To make them smarter though, consider using the Date data type which was introduced in SQL Server 2008. The Date data type stores the date without the time component which is exactly what you need anyway. Using the Date data type instead of integers have the following benefits:

  • The Date data type allows you to perform date arithmetic easier as the date semantics is preserved, so you can use date functions, such as YEAR and MONTH when querying the table or partitioning a cube.
  • Self-service BI users can conveniently filter dates on import. For example, PowerPivot enables relative date filtering, such as Last Month, when the user filters a date column.
  • The Date data type has a storage of three bytes as opposed to four bytes for integers.

The only small downside of using the Date data type is that you will end up with funny looking unique names for the Date members in your Date dimension in an SSAS cube. The unique name will include the time portion, such as [Date].[Date].&[2013-07-01T00:00:00]. If you construct date members dynamically, e.g. to default to the current date, you have to append the time portion or use the Format function, such as Format(Now(),"yyyy-MM-ddT00:00:00").


Posted Wed, Jun 12 2013 8:53 AM by tlachev
Filed under:

Comments

SQLbyoBI wrote re: Smart Date Keys Got Smarter
on Sun, Jun 30 2013 9:52 AM

Teo - very interesting. One thing probably worth mentioning is that SSAS doesn't support the DATE data type and will convert it to System.Datetime which is internally stored as a double (8 bytes).  On the other hand, the INT data type is stored as System.Int32 (4 bytes).

tlachev wrote re: Smart Date Keys Got Smarter
on Tue, Jul 2 2013 12:43 PM

A good point although a date data type without the time portion should compress equally well.

SQLbyoBI wrote re: Smart Date Keys Got Smarter
on Wed, Jul 24 2013 11:13 AM

Another good point.  Truth be told, I'm not too familiar with the compression mechanism for SSAS data. Do you have any recommended reading?