none
How to write Time Series forecast to table? RRS feed

  • Question

  • I believe in SQL server 2005 it was possible to write the time series forecast to a table. I cannot find this setting in sql 2008, is it gone or just me being blind?
    Monday, June 22, 2009 6:56 PM

All replies

  • There's a "Save Query Result" button in the Mining Model Prediction tab in BI Dev Studio.  You can execute a Time Series prediction there and then save it via the UI.

    If you want to do this programmatically check out the article Getting Data Mining Results into SQL Tables
    -- Jamie MacLennan -- SQL Server Data Mining -- http://blogs.msdn.com/jamiemac
    Tuesday, June 23, 2009 6:37 PM
  • In addition to Jamie's suggestion you could use the Query Task in SSIS.

    Allan
    Monday, June 29, 2009 3:29 PM
  • Thanks Jamie, I found it!

    Now, since I have the experts at reach please allow me to ask about another issue that I run into.


    I used the wizard to setup a time series forecasting model, parameterized, run it and got the results. The biggest issues here was the fact that the time dimension member cannot have a name attribute. Unless later one is set to none one will get error messages.

    My next step was to constraint the time frame that goes into the analysis a little and so I picked the same monthly time dimension attribute and created a range from: to (please see the MDX below). I only used the BIDS wizard!

    From here on however I get only errors. At this point I can create and run a basic model but only against all of my data. Can somebody may point at potential areas to look.

    Any help is highly appreciated!

    // data query
    SELECT  DATAID ( [CUBE].[$DIM CUSTOMER].[KATTT] )
      AS [TOMER0_0]
       FROM [CUBE].[$DIM CUSTOMER]
      WHERE 
      (
       MDXFilter("( SELECT ( { [FILTER Y Numbers].[Bool].&[True] } ) ON COLUMNS FROM ( SELECT ( [Time].[FMonth].&[2007-04-01T00:00:00] : [Time].[FMonth].&[2009-05-01T00:00:00] ) ON COLUMNS FROM ( SELECT ( { [FILTER Predictive Modeling].[Bool].&[True] } ) ON COLUMNS FROM [CUBE])))")
      )
     
      GROUP BY [TOMER0_0],[TOMER0_0]
      ORDER BY  DATAID ( [CUBE].[$DIM CUSTOMER].[KATTT] )
     
      ASC
      
    //This is the error message that is thrown
    //OLE DB error: OLE DB or ODBC error: Server: The operation 
    //has been cancelled..
    
    
      
      
    // second query  
    SELECT  DATAID ( [CUBE].[FACT ORDER DETAIL].[$DIM CUSTOMER.KATTT] )
        AS [ETAIL0_0], KEY ( [CUBE].[FACT ORDER DETAIL].[$Time.FMonth],0 )
        AS [ETAIL0_1], AGGREGATE ( [CUBE].[FACT ORDER DETAIL].[Net Weight] )
        AS [ETAIL0_2], DATAID ( [CUBE].[FACT ORDER DETAIL].[$Time.FMonth] )
        AS [ETAIL0_3]
         FROM [CUBE].[FACT ORDER DETAIL]
        WHERE 
        (
         MDXFilter("( SELECT ( { [FILTER Y Numbers].[Bool].&[True] } ) ON COLUMNS FROM ( SELECT ( [Time].[FMonth].&[2007-04-01T00:00:00] : [Time].[FMonth].&[2009-05-01T00:00:00] ) ON COLUMNS FROM ( SELECT ( { [FILTER Predictive Modeling].[Bool].&[True] } ) ON COLUMNS FROM [CUBE])))")
        )
       
        GROUP BY [ETAIL0_0],[ETAIL0_1],[ETAIL0_0],[ETAIL0_3]
        ORDER BY  DATAID ( [CUBE].[FACT ORDER DETAIL].[$DIM CUSTOMER.KATTT] )
       
      ASC
    
    //this also creates an error
    //OLE DB error: OLE DB or ODBC error: Internal error: An 
    //unexpected exception occurred..
    
     
    
    //final error message for processing is  
    //Errors in the OLAP storage engine: An error occurred while 
    //processing the '~CaseDetail ~MG' partition of the '~CaseDetail 
    //~MG' measure group for the 'Mining_SBU ~MC' cube from the 
    //CUBE database.










    Tuesday, June 30, 2009 11:58 AM