How to write Time Series forecast to table?

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?

Answer 1

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

Answer 2

In addition to Jamie's suggestion you could use the Query Task in SSIS.

Allan

Answer 3

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.