none
Using Excel and Analysis services to forecast

    Pregunta

  • Hi,

    I have a cube I've created on SSAS 2008.  I can connect with excel and browse the cube (DMAddin is present).

    I'd like to forecast some values from within excel, but it doesn't seem to be working.

    I have values that look like this - 'Row Labels' is a date dimension.  Actual consumption is a measure I'd like to forecast.

    Row Labels ACTUAL CONSUMP
    Monday, November 26 2001 $34,140.00
    Tuesday, November 27 2001 $9,009.00
    Wednesday, November 28 2001 $29,121.00
    Thursday, November 29 2001 $10,222.00
    Friday, November 30 2001 $182,460.00
    Monday, December 03 2001 $401,626.00
    Tuesday, December 04 2001 $452,424.77
    Wednesday, December 05 2001 $162,987.04
    Thursday, December 06 2001 $1,097,491.38
    Friday, December 07 2001 $1,628,549.98
    Saturday, December 08 2001 $316,961.80
    Monday, December 10 2001 $365,520.70
     

    When I run the Datamining -> Forecasting wizard, the column which holds the date is not recognized and is unavailable to be picked as a timestamp.

    Is it possible to do this without copying the data into a new table and running the table forecast tool on the data? 

    Thanks.
    miércoles, 02 de septiembre de 2009 22:41

Todas las respuestas

  • This sounds like a bug, I will investigate a post more details here.

    However,  this should not affect the functionality, only the resulting report. the Time Stamp column is only used as an X-axis label in the generated chart. The forecasting results will be the same even if you don't select a Time Stamp column. You can modify the chart (right click, Select Data) to specify the X-axis set of labels once the forecasting is complete.


    bogdan crivat [sql server data mining] / http://www.bogdancrivat.net/dm
    jueves, 03 de septiembre de 2009 2:51
  • Hi Sam,
    I understood your issue.
    This is bacause your Date format Data is recognised by Excel.

    If you atleast remove the "DAY" from the total column, that will be recognised.

    Try with that and let me know if it resolves your issue...

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP
    jueves, 03 de septiembre de 2009 18:30
  • Bikash,
    I'm not sure I understand what you are recommending. 

    I'm not selecting any total areas from the pivot table.  I have only selected what I posted. 

    What looks strange to me is the column header "Row Labels" - why doesn't it say "Date"?
    jueves, 03 de septiembre de 2009 20:53
  • Hey Sam,

    Asi mentioned ,

    If you remove "Monday" from the below

    Monday, November 26 2001 --> Then it will work

    You can remove this part by preparing some query.

    Let me know if you have any further questions.

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP
    viernes, 04 de septiembre de 2009 7:54
  • Hi,

    I changed the name of pk_date to be the actual date and got this info in the pivot table - which gave the same behavior.  As Bogdan said, I was able to run the report without the timestamp field.

    Row Labels ACTUAL CONSUMP
    2001-11-26 00:00:00.000 $34,140.00
    2001-11-27 00:00:00.000 $9,009.00
    2001-11-28 00:00:00.000 $29,121.00
    2001-11-29 00:00:00.000 $10,222.00
    2001-11-30 00:00:00.000 $182,460.00
    2001-12-03 00:00:00.000 $401,626.00
    2001-12-04 00:00:00.000 $452,424.77
    2001-12-05 00:00:00.000 $162,987.04
    2001-12-06 00:00:00.000 $1,097,491.38
    2001-12-07 00:00:00.000 $1,628,549.98
    2001-12-08 00:00:00.000 $316,961.80
    2001-12-10 00:00:00.000 $365,520.70
    2001-12-11 00:00:00.000 $71,147.20
    2001-12-12 00:00:00.000 $30,378.00
    2001-12-13 00:00:00.000 $7,002.00
    2001-12-14 00:00:00.000 $1,100.00
    2001-12-17 00:00:00.000 $9,718.00
    2001-12-18 00:00:00.000 $5,812.00
    2001-12-19 00:00:00.000 $197,356.00

    Thanks for your assistance.
    Sam
    martes, 08 de septiembre de 2009 16:00
  • I changed my date dimension so the pk_date field is using the pk_date value for name and value which produced this output in my pivot table:

    Row Labels ACTUAL CONSUMP
    2001-11-26 00:00:00.000 $34,140.00
    2001-11-27 00:00:00.000 $9,009.00
    2001-11-28 00:00:00.000 $29,121.00
    2001-11-29 00:00:00.000 $10,222.00
    2001-11-30 00:00:00.000 $182,460.00
    2001-12-03 00:00:00.000 $401,626.00
    2001-12-04 00:00:00.000 $452,424.77
    2001-12-05 00:00:00.000 $162,987.04
    2001-12-06 00:00:00.000 $1,097,491.38
    2001-12-07 00:00:00.000 $1,628,549.98
    2001-12-08 00:00:00.000 $316,961.80
    2001-12-10 00:00:00.000 $365,520.70
    2001-12-11 00:00:00.000 $71,147.20
    2001-12-12 00:00:00.000 $30,378.00
    2001-12-13 00:00:00.000 $7,002.00
    2001-12-14 00:00:00.000 $1,100.00
    2001-12-17 00:00:00.000 $9,718.00
    2001-12-18 00:00:00.000 $5,812.00
    2001-12-19 00:00:00.000 $197,356.00


    The timestamp is still not recognized when performing data mining > forecast .  As Bogdan said, I was able to run the forecast without the time stamp.
    martes, 08 de septiembre de 2009 16:01
  • Sam,


    "What looks strange to me is the column header "Row Labels" - why doesn't it say "Date"?"
    ----



    Have you changed the (Analysis Serives) dimension - Attribute property 'Type' for PKDate to "Date"?



    Rok
    miércoles, 09 de septiembre de 2009 17:15
  • Rok,

    Tried that and am getting the same behavior.   It was Day.

    Thanks
    Sam
    miércoles, 09 de septiembre de 2009 17:41