SSAS data mining with time series

• Wednesday, December 12, 2012 10:43 PM

   

   
  

  0

  Here is the context: I have a Time Series table that I have transformed in multiple cases. Each row represents a block of time t for a hockey player. The measures I want to predict using a Neural Network in SSAS are Goals, Assists and GamesPlayed. I also include columns with t-1, t-2 and t-3 values additionally to the t value for each of the measures I want to predict.

  Here is the problem: How can I control what do I feed into the inputs and what comes out as the outputs? For instance: I want to feed the data mining model blocks 1 through 12 to predict block 13 and 14. By using the "Save query results" in Mining model prediction tab in SSAS, how could I do this?

  SELECT
    t.[BlockId],
    t.[Player_id],
    [V Time Series Cases].[Games Played T],
    [V Time Series Cases].[Assists T],
    [V Time Series Cases].[Goals T]
  From
    [V Time Series Cases]
  PREDICTION JOIN
    OPENQUERY([Intelli Stats DW],
      'SELECT
        [BlockId],
        [Player_id],
        [Team],
        [Position],
        [OverallDraftRank],
        [Salary],
        [GamesPlayedT],
        [GamesPlayedT-1],
        [GamesPlayedT-2],
        [GamesPlayedT-3],
        [GoalsT],
        [GoalsT-1],
        [GoalsT-2],
        [GoalsT-3],
        [AssistsT],
        [AssistsT-1],
        [AssistsT-2],
        [AssistsT-3]
      FROM
        [dbo].[V_TimeSeriesCases]
      ') AS t
  ON
    [V Time Series Cases].[Team] = t.[Team] AND
    [V Time Series Cases].[Position] = t.[Position] AND
    [V Time Series Cases].[Overall Draft Rank] = t.[OverallDraftRank] AND
    [V Time Series Cases].[Salary] = t.[Salary] AND
    [V Time Series Cases].[Games Played T] = t.[GamesPlayedT] AND
    [V Time Series Cases].[Games Played T-1] = t.[GamesPlayedT-1] AND
    [V Time Series Cases].[Games Played T-2] = t.[GamesPlayedT-2] AND
    [V Time Series Cases].[Games Played T-3] = t.[GamesPlayedT-3] AND
    [V Time Series Cases].[Goals T] = t.[GoalsT] AND
    [V Time Series Cases].[Goals T-1] = t.[GoalsT-1] AND
    [V Time Series Cases].[Goals T-2] = t.[GoalsT-2] AND
    [V Time Series Cases].[Goals T-3] = t.[GoalsT-3] AND
    [V Time Series Cases].[Assists T] = t.[AssistsT] AND
    [V Time Series Cases].[Assists T-1] = t.[AssistsT-1] AND
    [V Time Series Cases].[Assists T-2] = t.[AssistsT-2] AND
    [V Time Series Cases].[Assists T-3] = t.[AssistsT-3]

  
  

  
  

  • Reply
  • Quote