none
Excel 2013 Timeline and Date Dimensions in SSAS

    Question

  • I am currently building out some Excel 2013 views of SSAS 2012 data.  I have a date dimension built which uses an int as key.  I have formatted the date as well as added a value for the Day field as a Date Datatype.  When I try to add the Timeline filter, I get the following error:  We couldn't create a Timeline because we couldn't get data about the date field. 

    I created a new dimension with just the Day level and it still does not work.  This appears to be an issue with the date from cube.  Excel seems to recognize that it is a date field, but will not create the slicer.  This makes it tough to use in these "reports".  Anyone else run into this or know what the issue may be?


    Regards, Steve @dataonwheels http://www.dataonwheels.com

    Thursday, February 21, 2013 6:12 PM

All replies

  • Hi,

    Check the ValueColumn properties for each attribute in Data Tools /BIDS. If the properties are empty you need to update that.

    HTH

    Thomas Ivarsson

    Thursday, February 21, 2013 6:21 PM
    Moderator
  • Thomas,

    I have the lowest level, Day, set up this way.  I actually created a new Date dimension with only the Day level in it.  The key was an int (20130101) that I formatted as short date.  The Name was 01/01/2013 which was formatted as Short Date, but was a WChar type.  And added the value 01/01/2013 which was a Date data type and formatted as Short Date.  Even in this scenario, Excel seemed to recognize that dates were in use, but it was unable to retrieve the data.

    Is it possible this will not work for a large date dimension?  We have dates from 1970 - 2025.  I will try a shorter date later today. 

    Regards,

    Steve


    Regards, Steve @dataonwheels http://www.dataonwheels.com

    Friday, February 22, 2013 1:05 PM
  • Hi,

    Build a natural hierarchy on top of dates like date->month->year and set the type properties correct for the levels. Add the settings for the valuecolumn properties for these new attributes.

    BR

    Thomas Ivarsson

    Friday, February 22, 2013 3:32 PM
    Moderator
  • Hi,

    Build a natural hierarchy on top of dates like date->month->year and set the type properties correct for the levels. Add the settings for the valuecolumn properties for these new attributes.

    BR

    Thomas Ivarsson

    I am having the same problem.  I have specified the Type for my Date dimension of type Time, have a dimension key attribute whose Key column is of type Date, Name column is a WChar, and Value column is also Date.  I've set the dimension key attribute's Type to Date > Calendar > Days.

    I also have Month, Quarter, and Year attributes that are part of a user defined hierarchy, which also have the corresponding types specified (Months, Quarters, and Years).  For each of these attributes, I've set the Value column to be the last day of the specified period and of type date.

    Any other ideas what I could be missing?


    Trent

    Tuesday, April 30, 2013 6:36 PM
  • I've resolved the issue. My problem was I had a dummy date record for 12/31/9999 that represented the end of time. After, removing this record and reprocessing, I was then able to add the Timeline.

    Trent

    Tuesday, April 30, 2013 8:55 PM
  • I've resolved the issue. My problem was I had a dummy date record for 12/31/9999 that represented the end of time. After, removing this record and reprocessing, I was then able to add the Timeline.

    Trent

    Thank you Trent.  I also had a 12/31/9999 date in my date dimension table that was preventing my Timeline from working.
    Thursday, September 26, 2013 6:10 PM