locked
why do we need to have a seperate DATE table for Time Intelligence? RRS feed

  • Question

  • For Time Intelligence to work perfectly why do we need to have a Date Table, why can't we just have a column "datekey" in say Sales Table and run Time Intelligence on top of it? Just curious.

    I do not know that there's a nice date stream on Azure data market but Why create an additional table when you already have a datekey in the sales Table? Would like the perspective on this.

    Thursday, October 11, 2012 12:37 AM

Answers

  • Hi,

    It's important to have a standardised date dimension with the most detailed granuality of YYYYMMDD to enable the cube developer to provide the business intelligence user with a generic date period slicing facility which is consistent over all dates within their cube; - http://msdn.microsoft.com/en-us/library/ms174884.aspx

    This standard date slicing functionality can include features such as a year->month->date hierarchy, separate hierarchies for fiscal and calender dates, etc.

    The same date dimension design can then be added to the cube multiple times where each addition of the date dimension is defined within the "dimension usage tab". The AdventureWorks cube provides a good example of this; - http://msftdbprodsamples.codeplex.com/releases/view/88252

    The Date Dimension Design view provides detail of functionality and data members which can be re-used within each cube date; -


    The Cube Design tab presents you with an example of multiple dates within a cube; -

    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI,MCC, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com/ http://uk.linkedin.com/in/kieranpatrickwood


    Thursday, October 11, 2012 1:27 AM
  • The dimension usage tab presents you with the different relationships (different business contexts) of these dates; -


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI,MCC, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com/ http://uk.linkedin.com/in/kieranpatrickwood

    Thursday, October 11, 2012 1:38 AM

All replies

  • Hi,

    It's important to have a standardised date dimension with the most detailed granuality of YYYYMMDD to enable the cube developer to provide the business intelligence user with a generic date period slicing facility which is consistent over all dates within their cube; - http://msdn.microsoft.com/en-us/library/ms174884.aspx

    This standard date slicing functionality can include features such as a year->month->date hierarchy, separate hierarchies for fiscal and calender dates, etc.

    The same date dimension design can then be added to the cube multiple times where each addition of the date dimension is defined within the "dimension usage tab". The AdventureWorks cube provides a good example of this; - http://msftdbprodsamples.codeplex.com/releases/view/88252

    The Date Dimension Design view provides detail of functionality and data members which can be re-used within each cube date; -


    The Cube Design tab presents you with an example of multiple dates within a cube; -

    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI,MCC, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com/ http://uk.linkedin.com/in/kieranpatrickwood


    Thursday, October 11, 2012 1:27 AM
  • The dimension usage tab presents you with the different relationships (different business contexts) of these dates; -


    If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI,MCC, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com/ http://uk.linkedin.com/in/kieranpatrickwood

    Thursday, October 11, 2012 1:38 AM