Time Series Reporting Stored Procedure - Part 4 of 3

  • Article

OK, OK, I know it's just not right to do a part 4 of a three part series, but I've gotten enough demand to expand these stored procedures just a little bit.  I casually left out a class of time series models that contain nested tables since it made things a bit more complicated to explain, but a bunch of people reminded me ever so kindly that the only way to create Time Series models on OLAP cubes (using BIDS at least) is to use nested tables!

So here I am back again into my three part series on time series reporting.  To simplify the code I just made a new function which you would call like this

 CALL TSSprocs.TimeSeriesReportNested('Store Forecasting','Store Cost','Store 1',10,5)

With the parameters being, in order, the model name, the column to forecast, the name of the series to forecast, the number of historical points (0 -> all), and the number of forecasted points.  In this version it is assumed that the dimension containing the names of the series are on the case level and Time and the measures are in the nested table.  The series name is required for this version (since in the OLAP scenario it always should be there).

I'm not going to go through the function line-by-line, but I will show you the queries I put together in order to fetch the historical and predicted data.  To fetch a limited amount of historical data from a nested table required a bit of a different query.  In this case, I had to use the TopCount function to retrieve the last rows and then use the same ORDER BY trick I used in the non-nested scenario to reverse the row order.  Note also the syntax required to reference the nested column in the outer select - I had to put my nested alias along with the nested column name inside the brackets for it to be properly referenced.

 SELECT * FROM 
   (SELECT FLATTENED 
       (SELECT [Year Month],[Store Cost]
            FROM  TopCount([Time],[Year Month],5)) 
        AS ex 
    FROM [Store Forecasting].CASES 
    WHERE [Store]='Store 1') AS t
ORDER BY [ex.Year Month]

The case where you aren't selecting a subset of the data is fairly trivial, as is the actual prediction query - you just need to be aware of the subselect from the nested tables.

 // Fetch all historical data for a series
// with nested tables
SELECT FLATTENED 
  (SELECT [Year Month],[Store Cost] FROM  [Time]) 
FROM [Store Forecasting].CASES WHERE Store='Store 1'

// Predict a series with nested tables
SELECT FLATTENED 
  (SELECT PredictTimeSeries([Store Cost],5) FROM Time) 
FROM [Store Forecasting] WHERE [Store] = 'Store 1'

In any case, the code for all of the stored procedures is attached, so you can replace the existing TSSprocs.cs with this one and start creating OLAP Mining Model Time Series reports!

TSSprocs.cs