Share via

Time Series Prediction DMX Tutorial

  • Article

 

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.

Tutorial Scenario

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.

What You Will Learn

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.

Requirements

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 https://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.

Note


When you review tutorials, we recommend that you add Next topic and Previous topic buttons to the document viewer toolbar.

See Also

Basic Data Mining Tutorial
Intermediate Data Mining Tutorial (Analysis Services - Data Mining)