none
Time Series forecasting RRS feed

  • Question

  • I have 5 years worth of call volume data split by day.

    I need to use this to create a forecast. I have read a lot about the Microsoft time series algorithm and it appears to do what I need.

    I have the data in a database and exposed to the user via an SSAS Tabular model using Power BI

    From what I can see I can only use the Algorithm on a Data Mining model which is only available via an OLAP cube.

    Is there any way I can use Time Series with Tabular?


    Thanks for reading!
    If you found my answer useful please mark as answered
    David

    Thursday, April 18, 2019 10:31 AM

Answers

  • SQL Server (starting from 2016) has extensive Machine Learning capabilities implemented via DataBase engine with R services (+Python starting from 2017, then +Java from 2019)
    R has all needed tools to model Time-Series (sampling/training/validating) and you don't even have to have R installed once model is trained (it can be done on a different server), serialized and stored in database as varbinary(max) - because scoring can be done invoking T-SQL native Predict() function (or even sp_rxPredict procedure - but it does require Advanced Analytics elements installed with your SQL Server instance) - assuming modelling part was done with limited set of RevoScaleR functions (and model is lightweight enough for Real-Time scoring, otherwise it's better to invoke isolated R session)

    All you have to do is connecting these two (T-SQL and SSAS) into single solution, normally if materialization (into MOLAP partition) isn't possible - then you still can go with ROLAP (by invoking Predict() function in your view, preliminary making sure your sets are filtered) or even build your own SSAS assembly to handle such integration (the only problem to avoid would calling it in cell-by-cell mode, as it might fire too many connections/sessions to SQL server, therefore performance will drop significantly)

    With R (or Python) you can create forecasts even based on OLAP data (aggregated), then materialize it into SQL table and reuse back in SSAS.
    Wednesday, April 24, 2019 12:40 PM

All replies

  • Hi David, 

    As I know, we  can't develop or deploy data mining solutions on an instance of Analysis Services that supports tabular models. So if you wnat to develop data mining, this can't be achieved.

    In addition, for powerbi, it also could use data from multiple dimension  model. So I think you also  could try to use Multiple dimension.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 19, 2019 2:56 AM
  • Unfortunately shifting the entire infrastructure from MultiDimension to Tabular is not an option

    Thanks for reading!
    If you found my answer useful please mark as answered
    David

    Tuesday, April 23, 2019 8:11 AM
  • Hi David

    I am sorry  for your inconvenience! This data mining is not be supported in tabular models currently. You could submit this requirement in feedback.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 24, 2019 5:46 AM
  • SQL Server (starting from 2016) has extensive Machine Learning capabilities implemented via DataBase engine with R services (+Python starting from 2017, then +Java from 2019)
    R has all needed tools to model Time-Series (sampling/training/validating) and you don't even have to have R installed once model is trained (it can be done on a different server), serialized and stored in database as varbinary(max) - because scoring can be done invoking T-SQL native Predict() function (or even sp_rxPredict procedure - but it does require Advanced Analytics elements installed with your SQL Server instance) - assuming modelling part was done with limited set of RevoScaleR functions (and model is lightweight enough for Real-Time scoring, otherwise it's better to invoke isolated R session)

    All you have to do is connecting these two (T-SQL and SSAS) into single solution, normally if materialization (into MOLAP partition) isn't possible - then you still can go with ROLAP (by invoking Predict() function in your view, preliminary making sure your sets are filtered) or even build your own SSAS assembly to handle such integration (the only problem to avoid would calling it in cell-by-cell mode, as it might fire too many connections/sessions to SQL server, therefore performance will drop significantly)

    With R (or Python) you can create forecasts even based on OLAP data (aggregated), then materialize it into SQL table and reuse back in SSAS.
    Wednesday, April 24, 2019 12:40 PM
  • I started to look into R once I discovered Tabular was off the table

    Working through this currently

    https://buildmedia.readthedocs.org/media/pdf/a-little-book-of-r-for-time-series/latest/a-little-book-of-r-for-time-series.pdf

    It looks good so far.

    I think I am going to try and do it all in R, then export the data into a SQL table and expose via SSAS as you say.

    Thanks for your reply.



    Thanks for reading!
    If you found my answer useful please mark as answered
    David

    Wednesday, April 24, 2019 12:48 PM