Time Series Prediction DMX Tutorial
Applies To: SQL Server 2016 Preview
In this tutorial, you will learn how to create a time series mining structure, create three custom time series mining models, and then make predictions by using those models.
The mining models are based on the data contained in the AdventureWorksDW2012 sample database, which stores data for the fictitious company Adventure Works Cycles. Adventure Works Cycles is a large, multinational manufacturing company.
Adventure Works Cycles has decided to use data mining to generate sales projections. They have already built some regional forecasting models; for more information, see Lesson 2: Building a Forecasting Scenario (Intermediate Data Mining Tutorial). However, the Sales Department needs to be able to periodically update the data mining model with new sales data. They also want to customize the models to provide different projections.
Microsoft SQL Server Analysis Services provides several tools that can be used to accomplish this task:
The Data Mining Extensions (DMX) query language
The Microsoft Time Series Algorithm
Query Editor in SQL Server Management Studio
The Microsoft Time Series algorithm creates models that can be used for prediction of time-related data. Data Mining Extensions (DMX) is a query language provided by Analysis Services that you can use to create mining models and prediction queries.
This tutorial assumes that you are already familiar with the objects that Analysis Services uses to create mining models. If you have not previously created a mining structure or mining model by using DMX, see Bike Buyer DMX Tutorial.
This tutorial is divided into the following lessons:
Lesson 1: Creating a Time Series Mining Model and Mining Structure
In this lesson, you will learn how to use the CREATE MINING MODEL statement to add a new forecasting model and a related mining model.
Lesson 2: Adding Mining Models to the Time Series Mining Structure
In this lesson, you will learn how to use the ALTER MINING STRUCTURE statement to add new mining models to the time series structure. You will also learn how to customize the algorithm used for analyzing a time series.
Lesson 3: Processing the Time Series Structure and Models
In this lesson, you will learn how to train the models by using the INSERT INTO statement and populating the structure with data from the AdventureWorksDW2012 database.
Lesson 4: Creating Time Series Predictions Using DMX
In this lesson, you will learn how to create time series predictions.
Lesson 5: Extending the Time Series Model
In this lesson, you will learn how to use the EXTEND_MODEL_CASES parameter to update the model with new data when you make predictions.
Before doing this tutorial, make sure that the following are installed:
Microsoft SQL Server
Microsoft SQL Server Analysis Services
The AdventureWorksDW2012 database
By default, the sample databases are not installed, to enhance security. To install the official sample databases for Microsoft SQL Server, go to http://www.CodePlex.com/MSFTDBProdSamples or on the Microsoft SQL Server Samples and Community Projects home page in the section Microsoft SQL Server Product Samples. Click Databases, then click the Releases tab and select the databases that you want.