data mining by example forecasting and cross prediction using microsoft time series
TRANSCRIPT
![Page 1: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/1.jpg)
Data Mining By Example – Forecasting and Cross Prediction Using Microsoft Time Series
by Shaoli Lu
![Page 2: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/2.jpg)
Microsoft Time Series
• Microsoft Time Series algorithm provides a unique approach to time series forecasting that is both intuitive and accurate. It is used to forecast future series points based on past history
![Page 3: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/3.jpg)
Goal
• To forecast and cross-predict based on past sales history
![Page 4: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/4.jpg)
Prerequisite
• An SQL Server instance created (2005 or above)• SQL Server Analysis Service (SSAS) –
Multidimensional Feature Installed(this is used to host and browse the mining structures; cube is not required for data mining!)
• AdventureWorksDW database attached(download from CodePlex - tailor to the SQL Server version you have)
• Visual Studio 2010 or above with SQL Server Data Tools (SSDT) installed
![Page 5: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/5.jpg)
My Demo Setup
• Visual Studio 2010 • SQL Server 2012
![Page 6: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/6.jpg)
Create Data Mining Project
• Name the project as DM Forecasting (DM = Data Mining)
![Page 7: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/7.jpg)
![Page 8: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/8.jpg)
Create Data Source and Impersonation
![Page 9: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/9.jpg)
![Page 10: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/10.jpg)
![Page 11: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/11.jpg)
Create Data Source View
![Page 12: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/12.jpg)
![Page 13: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/13.jpg)
![Page 14: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/14.jpg)
Create Mining Structure
• Use relational data source• Choose Microsoft Time Series model• Select Data Source View• Select key, input and predict• Name the mining structure and model• Add Amount to the mining structure and
change it to Predict• Tune Algorithm Parameters
![Page 15: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/15.jpg)
![Page 16: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/16.jpg)
![Page 17: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/17.jpg)
![Page 18: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/18.jpg)
![Page 19: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/19.jpg)
![Page 20: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/20.jpg)
![Page 21: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/21.jpg)
![Page 22: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/22.jpg)
![Page 23: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/23.jpg)
Deploy the mining structure and model
![Page 24: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/24.jpg)
Process the mining model
• This is also called “training the model”
![Page 25: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/25.jpg)
![Page 26: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/26.jpg)
![Page 27: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/27.jpg)
Mining Model Viewer
• Forecast trend• Select items to predict• View standard deviation• View forecasting mode in a tree graph• View regression formula• Use Generic Content Tree View to inspect
stats details
![Page 28: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/28.jpg)
![Page 29: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/29.jpg)
![Page 30: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/30.jpg)
![Page 31: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/31.jpg)
Mining Model Prediction
• Convert to DMX query• Adjust the DMX query by adding FLATTENED
clause• Add filters to the DMX query• View query results
![Page 32: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/32.jpg)
![Page 33: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/33.jpg)
![Page 34: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/34.jpg)
![Page 35: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/35.jpg)
![Page 36: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/36.jpg)
![Page 37: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/37.jpg)
![Page 38: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/38.jpg)
Adding Additional Data
• DMX EXTEND_MODEL_CASES clause will add additional data to the existing data
![Page 39: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/39.jpg)
![Page 40: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/40.jpg)
![Page 41: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/41.jpg)
![Page 42: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/42.jpg)
Cross Prediction
• Create new named query AllRegions for cross-prediction
• Create a query for a specific region and model• Create a new mining structure for cross-
prediction• Deploy and process the cross-prediction mining
structure and model• Use REPLACE_MODEL_CASES in the DMX query
for cross-prediction
![Page 43: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/43.jpg)
![Page 44: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/44.jpg)
![Page 45: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/45.jpg)
![Page 46: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/46.jpg)
![Page 47: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/47.jpg)
![Page 48: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/48.jpg)
![Page 49: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/49.jpg)
![Page 50: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/50.jpg)
Browse mining model on SQL Server
• Time Series Chart View• Model Tree Graph• Prediction Query Window• DMX• Query result
![Page 51: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/51.jpg)
![Page 52: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/52.jpg)
![Page 53: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/53.jpg)
![Page 54: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/54.jpg)
![Page 55: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/55.jpg)
![Page 56: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/56.jpg)
Summary
• Microsoft Time Series is a powerful data mining model, yet it is intuitive to build, train and use
• It is useful in forecasting future event points • Algorithm Parameters can be tuned• DMX EXTEND_MODEL_CASES clause will add additional
data to the existing data• Use REPLACE_MODEL_CASES in the DMX query for
cross-prediction• Relational database can be used for data mining; cube is
not required
![Page 57: Data mining by example forecasting and cross prediction using microsoft time series](https://reader036.vdocuments.us/reader036/viewer/2022062522/58a34b201a28ab62248b668d/html5/thumbnails/57.jpg)
The End