Search code examples
ssasmdxtime-series

SSAS Time Series, predict when something will fail

I would like to use SSAS and a TimeSeries Mining Structure to predict when the predictable value will reach a certain threshold.

For Example:

SELECT [Info Key],
PredictTimeSeries([Free Space], 200) as ForcastedSize
FROM [Drive Module Information]
WHERE ForcastedSize < 10000 --(<< this does not work)

This will tell me the date that it forecasts that the drive space will be below 10000.

How do I write an MDX query to accomplish this? Thanks, Brian

UPDATE 1: I think I can accomplish it this way, with some limitations:

SELECT [Drive Module Information].[Info Key],
    (SELECT *
    FROM PredictTimeSeries([Drive Module Information].[Free Space], 5000) as [FUTURE]
    ) AS T
FROM [Drive Module Information]
  WHERE
  [Info Key] = 'MyMachine C:' AND
     [Free Space] <     10000

The limitation is that I can only look X# of steps forward without getting crazy. Which is ok. I am ok with knowing that the drive will not fill up over the next week, or month.

I did not figure out how to use FILTER in this situation, and am still curious as to if there is a "What date will this predictable value be equal to this value".

UPDATE 2: I have come to the conclusion that SSAS was not meant to do this, so until I find out differently, I will mark icCube as the answer since he helped out.

Solution

  • MDX is not SQL, the where clause of MDX is not a real filter. As a quick introduction you can go through this MDX gentle Tutorial.

    There is a MDX Filter function you can use.