GsivMNZ4

1
2Lesson 1: Preparing the Analysis Services Database
3(Basic Data Mining Tutorial)
4You are a new employee of Adventure Works Cycles who has been tasked with designing
5a business intelligence application in SQL Server 2012. Adventure Works Cycles hopes to
6leverage your Analysis Services data mining experience to discover interesting and
7actionable information about people who have purchased bicycles. They then want you
8to predict which prospective customers are most likely to purchase a bicycle in the
9future.
10Designing this application in SQL Server starts with the creation in SQL Server Data Tools
11(SSDT) of a SQL Server Analysis Services project based on the Analysis Services project
12template for multidimensional modeling and data mining. After you create an Analysis
13Services project, you define one or more data sources. Then, you define a view of the
14metadata, called a data source view, from selected tables and views from the data
15sources.
16Procedures
171. Open SQL Server Data Tools (SSDT).
182. On the File menu, point to New, and then select Project.
193. Verify that Business Intelligence Projects is selected in the Project types
20pane. 4. In the Templates pane, select
21Mining Project Analysis Services Multidimensional and Data .
225. In the Name box, name the new project BasicDataMining.
23To create an Analysis Services project
246. Click .
251. In SQL Server Data Tools (SSDT), on the Project menu, select Properties.
262. On the left side of the Property Pages pane, under Configuration Properties,
27click Deployment.
283. On the right side of the Property Pages pane, under Target, verify that the
29Server name is localhost. If you are using a different instance, type the name of
30the instance. Click .
31Creating a Data Source (Basic Data Mining Tutorial)
32A data source is a data connection that is saved and managed in your project and
33deployed to your Microsoft SQL Server Analysis Services database. The data source
34contains the names of the server and database where your source data resides, in
35addition to any other required connection properties.
36Procedures
371. In Solution Explorer, right-click the Data Sources folder and select New Data
38Source.
392. On the Welcome to the Data Source Wizard page, click Next.
403. On the Select how to define the connection page, click New to add a
41connection to the database.
424. In the Provider list in Connection Manager, select Native OLE DB\SQL Server
43Native Client 11.0.
445. In the Server name box, type or select the name of the server on which you
45installed .
46To change the instance where data mining objects are stored
47To create a data source
48For example, type localhost if the database is hosted on the local server.
496. In the Log onto the server group, select Use Windows Authentication.
507. In the Select or enter a database name list, select and then click OK.
518. Click Next.
529. On the Impersonation Information page, click Use the service account, and
53then click Next.
54On the Completing the Wizard page, notice that, by default, the data source is
55named Adventure Works DW 2012.
5610. Click Finish.
57The new data source, Adventure Works DW 2012, appears in the Data Sources
58folder in Solution Explorer.
59Creating a Data Source View (Basic Data Mining Tutorial)
60A data source view is built on a data source and defines a subset of the data, which you
61can then use in your mining structures. You can also use the data source view to add
62columns, create calculated columns and aggregates, and add named views. By using
63data source views, you can select the data that relates to your project, establish
64relationships between tables, and modify the structure of the data, without modifying
65the original data source. For more information, see Designing Data Source Views
66(Analysis Services).
67Procedures
68To create a data source view
691. In Solution Explorer, right-click Data Source Views, and select New Data
70Source View.
712. On the Welcome to the Data Source View Wizard page, click Next.
723. On the Select a Data Source page, under Relational data sources, select the
73Adventure Works DW 2012 data source that you created in the last task. Click
74Next.
75Note
76If you want to create a data source, right-click Data Sources and then
77click New Data Source to start the Data Source Wizard.
784. On the Select Tables and Views page, select the following objects, and then
79click the right arrow to include them in the new data source view:
80• ProspectiveBuyer (dbo) - table of prospective bike buyers
81• vTargetMail (dbo) - view of historical data about past bike buyers
825. Click Next.
836. On the Completing the Wizard page, by default the data source view is named
84Adventure Works DW 2012. Change the name to Targeted Mailing, and then
85click Finish.
86The new data source view opens in the Targeted Mailing.dsv [Design] tab.
87Lesson 2: Building a Targeted Mailing Structure
88(Basic Data Mining Tutorial)
89The Marketing department of Adventure Works Cycles wants to increase sales by
90targeting specific customers for a mailing campaign. The company's database, ,
91contains a list of past customers and a list of potential new customers. By investigating
92the attributes of previous bike buyers, the company hopes to discover patterns that they
93can then apply to potential customers. They hope to use the discovered patterns to
94predict which potential customers are most likely to purchase a bike from Adventure
95Works Cycles.
96Creating a Targeted Mailing Mining Model Structure (Basic Data
97Mining Tutorial)
98The first step in creating a targeted mailing scenario is to use the Data Mining Wizard in
99SQL Server Data Tools (SSDT) to create a new mining structure and decision tree mining
100model.
101In this task you will set up a new mining structure, and add an initial mining model based
102on the Microsoft Decision Trees algorithm. To create the structure, you will first select
103tables and views and then identify which columns will be used for training and which for
104testing.
105Procedures
1061. In Solution Explorer, right-click Mining Structures and select New Mining
107Structure to start the Data Mining Wizard.
1082. On the Welcome to the Data Mining Wizard page, click Next.
1093. On the Select the Definition Method page, verify that From existing relational
110database or data warehouse is selected, and then click Next.
1114. On the Create the Data Mining Structure page, under Which data mining
112technique do you want to use?, select Microsoft Decision Trees.
113To create a mining structure for the targeted mailing scenario
1145. Click Next.
1156. On the Select Data Source View page, in the Available data source views pane,
116select Targeted Mailing. You can click Browse to view the tables in the data
117source view and then click Close to return to the wizard.
1187. Click Next.
1198. On the Specify Table Types page, select the check box in the Case column for
120vTargetMail to use it as the case table, and then click Next. You will use the
121ProspectiveBuyer table later for testing; ignore it for now.
1229. On the Specify the Training Data page, you will identify at least one predictable
123column, one key column, and one input column for your model. Select the check
124box in the Predictable column in the BikeBuyer row.
12510. Click Suggest to open the Suggest Related Columns dialog box.
126The Suggest button is enabled whenever at least one predictable attribute has
127been selected. The Suggest Related Columns dialog box lists the columns that
128are most closely related to the predictable column, and orders the attributes by
129their correlation with the predictable attribute. Columns with a significant
130correlation (confidence greater than 95%) are automatically selected to be
131included in the model.
132Review the suggestions, and then click Cancel to ignore the suggestions.
133Note
134If you click OK, all listed suggestions will be marked as input columns in
135the wizard. If you agree with only some of the suggestions, you must
136change the values manually.
13711. Verify that the check box in the Key column is selected in the CustomerKey row.
138Note
139If the source table from the data source view indicates a key, the Data
140Mining Wizard automatically chooses that column as a key for the model.
14112. Select the check boxes in the Input column in the following rows. You can check
142multiple columns by highlighting a range of cells and pressing CTRL while
143selecting a check box.
144• Age
145• CommuteDistance
146• EnglishEducation
147• EnglishOccupation
148• Gender
149• GeographyKey
150• HouseOwnerFlag
151• MaritalStatus
152• NumberCarsOwned
153• NumberChildrenAtHome
154• Region
155• TotalChildren
156• YearlyIncome
15713. On the far left column of the page, select the check boxes in the following rows.
158• AddressLine1
159• AddressLine2
160• DateFirstPurchase
161• EmailAddress
162• FirstName
163• LastName
164Ensure that these rows have checks only in the left column. These columns will be
165added to your structure but will not be included in the model. However, after the
166model is built, they will be available for drillthrough and testing. For more
167information about drillthrough, see Using Drill through on Mining Models and
168Mining Structures (Analysis Services - Data Mining).
16914. Click Next.
170Specifying the Data Type and Content Type (Basic Data Mining
171Tutorial)
172Now that you have selected which columns to use for building your structure and
173training your models, make any necessary changes to the default data and content types
174that are set by the wizard.
1751. On the Specify Columns' Content and Data Type page, click Detect to run an
176algorithm that determines the default data and content types for each column.
1772. Review the entries in the Content Type and Data Type columns and change
178them if necessary, to make sure that the settings are the same as those listed in
179the following table.
180Typically, the wizard will detect numbers and assign an appropriate numeric data
181type, but there are many scenarios where you might want to handle a number as
182text instead. For example, the GeographyKey should be handled as text, because
183it would be inappropriate to perform mathematical operations on this identifier.
184Column Content Type Data Type
185Address Line1 Discrete Text
186Address Line2 Discrete Text
187Age Continuous Long
188Bike Buyer Discrete Long
189Commute Distance Discrete Text
190CustomerKey Key Long
191DateLastPurchase Continuous Date
192Email Address Discrete Text
193English Education Discrete Text
194English Occupation Discrete Text
195FirstName Discrete Text
196Gender Discrete Text
197Geography Key Discrete Text
198Review and modify content type and data type for each column
199House Owner Flag Discrete Text
200Last Name Discrete Text
201Marital Status Discrete Text
202Number Cars Owned Discrete Long
203Number Children At Home Discrete Long
204Region Discrete Text
205Total Children Discrete Long
206Yearly Income Continuous Double
2073. Click Next.
208Specifying a Testing Data Set for the Structure (Basic Data Mining
209Tutorial)
210In the final few screens of the Data Mining Wizard you will split your data into a testing
211set and a training set. You will then name your structure and enable drillthrough on the
212model.
213Specifying a Testing Set
214Separating data into training and testing sets when you create a mining structure makes
215it possible to easily assess the accuracy of the mining models that you create later.
2161. On the Create Testing Set page, for Percentage of data for testing, leave the
217default value of 30.
2182. For Maximum number of cases in testing data set, type 1000.
2193. Click Next.
220To specify the testing set
221Specifying Drillthrough
222Drillthrough can be enabled on models and on structures. The checkbox in this dialog
223box enables drillthrough on the named model. After the model has been processed, you
224will be able to retrieve detailed information from the training data that were used to
225create the model.
2261. On the Completing the Wizard page, in Mining structure name, type Targeted
227Mailing.
2282. In Mining model name, type TM_Decision_Tree.
2293. Select the Allow drill through check box.
2304. Review the Preview pane. Notice that only those columns selected as Key, Input
231or Predictable are shown. The other columns you selected (e.g., AddressLine1)
232are not used for building the model but will be available in the underlying
233structure, and can be queried after the model is processed and deployed.
2345. Click Finish.
235Lesson 3: Adding and Processing Models
236The mining structure that you created in the previous lesson contains a single mining
237model that is based on the Microsoft Decision Trees algorithm. You can use this model
238to identify customers for the targeted mailing campaign. However, to ensure that your
239analysis is thorough, it is a common practice to create related models using different
240algorithms and compare their results. That way you can get different insights as well.
241Therefore, you will create two additional models, then process and deploy the models.
242To name the model and structure and specify drillthrough
243Adding New Models to the Targeted Mailing Structure (Basic
244Data Mining Tutorial)
2451. Switch to the Mining Models tab in Data Mining Designer in SQL Server Data
246Tools (SSDT).
247Notice that the designer displays two columns, one for the mining structure and
248one for the TM_Decision_Tree mining model, which you created in the previous
249lesson.
2502. Right-click the Structure column and select New Mining Model.
2513. In the New Mining Model dialog box, in Model name, type TM_Clustering.
2524. In Algorithm name, select Microsoft Clustering.
2535. Click .
254To create a clustering mining model
255The new model now appears in the Mining Models tab of Data Mining Designer. This
256model, built with the Microsoft Clustering algorithm, groups customers with similar
257characteristics into clusters and predicts bike buying for each cluster. Although you can
258modify the column usage and properties for the new model, no changes to the
259TM_Clustering model are necessary for this tutorial.
2601. In the Mining Models tab of Data Mining Designer, right-click the Structure
261column, and select New Mining Model.
2622. In the New Mining Model dialog box, under Model name, type
263TM_NaiveBayes.
2643. In Algorithm name, select Microsoft Naive Bayes, then click OK.
265A message appears stating that the Microsoft Naive Bayes algorithm does not
266support the Age and Yearly Income columns, which are continuous.
2674. Click Yes to acknowledge the message and continue.
268A new model appears in the Mining Models tab of Data Mining Designer. Although you
269can modify the column usage and properties for all the models in this tab, no changes to
270the TM_NaiveBayes model are necessary for this tutorial.
271Processing Models in the Targeted Mailing Structure (Basic Data
272Mining Tutorial)
273Before you can browse or work with the mining models that you have created, you must
274deploy the Analysis Services project and process the mining structure and mining
275models. Deploying sends the project to a server and creates any objects in that project
276on the server. Processing is the step, or series of steps, that populates Analysis Services
277objects with data from relational data sources. Models cannot be used until they have
278been deployed and processed.
279Ensuring Consistency with HoldoutSeed
280When you deploy a project and process the structure and models, individual rows in your
281data structure are randomly assigned to the training and testing set based on a random
282number seed. Typically, the random number seed is computed based on attributes of the
283data structure. For the purposes of this tutorial, in order to ensure that your results are
284To create a Naive Bayes mining model
2851. Click on the Mining Structure tab or the Mining Models tab in Data Mining
286Designer in SQL Server Data Tools (SSDT).
287Targeted Mailing MiningStructure displays in the Properties pane.
2882. Ensure that the Properties pane is open by pressing F4.
2893. Ensure that CacheMode is set to KeepTrainingCases.
2904. Enter 12 for HoldoutSeed.
291Deploying and Processing the Models
292In Data Mining Designer, you can process a mining structure, a specific mining model
293that is associated with a mining structure, or the structure and all the models that are
294associated with that structure. For this task, we will process the structure and all the
295models at the same time.
2961. In the Mining Model menu, select Process Mining Structure and All Models.
297If you made changes to the structure, you will be prompted to build and deploy
298the project before processing the models. Click Yes.
2992. Click Run in the Processing Mining Structure - Targeted Mailing dialog box.
300The Process Progress dialog box opens to display the details of model
301processing. Model processing might take some time, depending on your
302computer.
3033. Click Close in the Process Progress dialog box after the models have completed
304processing.
3054. Click Close in the Processing Mining Structure - <structure> dialog box.
306To set the Holdout Seed
307To deploy the project and process all the mining models
308Lesson 4: Exploring the Targeted Mailing Models
309(Basic Data Mining Tutorial)
310After the models in your project are processed, you can explore them to look for
311interesting trends. Because the results of mining models are complex and can be difficult
312to understand in a raw format, visually investigating the data is often the easiest way to
313understand the rules and relationships that the algorithms have discovered within the
314data. Exploring also helps you to understand the behavior of the model and discover
315which model performs best before you deploy it.
316When you use SQL Server Data Tools (SSDT) to explore your models, each model you
317created is listed in the Mining Model Viewer tab in Data Mining Designer. You can use
318the viewers to explore the models. These viewers are also available in SQL Server
319Management Studio.
320In this lesson you will look at the same data using your three models. Each model type is
321based on a different algorithm and provides different insights into the data. The Decision
322Tree model tells you about factors that influence bike buying. The Clustering model
323groups your customers by attributes that include their bike buying behavior and other
324selected attributes. The Naive Bayes model enables you to explore the relationship
325between different attributes. Finally, the Generic Content Tree Viewer reveals the
326structure of the model and provides richer detail including formulas, patterns that were
327extracted, and a count of cases in a cluster or a particular tree.
328Exploring the Decision Tree Model (Basic Data Mining Tutorial)
329The Microsoft Decision Trees algorithm predicts which columns influence the decision to
330purchase a bike based upon the remaining columns in the training set.
331The Microsoft Decision Tree Viewer provides the following tabs for use in exploring
332decision tree mining models:
333Decision Tree
334Dependency Network
335Decision Tree Tab
336On the Decision Tree tab, you can examine all the tree models that make up a mining
337model.
338Because the targeted mailing model in this tutorial project contains only a single
339predictable attribute, Bike Buyer, there is only one tree to view. If there were more trees,
340you could use the Tree box to choose another tree.
341Reviewing the TM_Decision_Tree model in the Decision Tree viewer reveals that age is
342the single most important factor in predicting bike buying. Interestingly, once you group
343the customers by age, the next branch of the tree is different for each age node. By
344exploring the Decision Tree tab we can conclude that purchasers age 34 to 40 with one
345or no cars are very likely to purchase a bike, and that single, younger customers who live
346in the Pacific region and have one or no cars are also very likely to purchase a bike.
3471. Select the Mining Model Viewer tab in Data Mining Designer.
348By default, the designer opens to the first model that was added to the structure
349-- in this case, TM_Decision_Tree.
3502. Use the magnifying glass buttons to adjust the size of the tree display.
351To explore the model in the Decision Tree tab
35224
353By default, the Microsoft Tree Viewer shows only the first three levels of the tree.
354If the tree contains fewer than three levels, the viewer shows only the existing
355levels. You can view more levels by using the Show Level slider or the Default
356Expansion list.
3573. Slide Show Level to the fourth bar.
3584. Change the Background value to 1.
359By changing the Background setting, you can quickly see the number of cases in
360each node that have the target value of 1 for [Bike Buyer]. Remember that in this
361particular scenario, each case represents a customer. The value 1 indicates that
362the customer previously purchased a bike; the value 0 indicates that the customer
363has not purchased a bike. The darker the shading of the node, the higher the
364percentage of cases in the node that have the target value.
3655. Place your cursor over the node labeled All. An tooltip will display the following
366information:
367• Total number of cases
368• Number of non bike buyer cases
369• Number of bike buyer cases
370• Number of cases with missing values for [Bike Buyer]
371Alternately, place your cursor over any node in the tree to see the condition that
372is required to reach that node from the node that comes before it. You can also
373view this same information in the Mining Legend.
3746. Click on the node for Age >=34 and < 41. The histogram is displayed as a thin
375horizontal bar across the node and represents the distribution of customers in
376this age range who previously did (pink) and did not (blue) purchase a bike. The
377Viewer shows us that customers between the ages of 34 and 40 with one or no
378cars are likely to purchase a bike. Taking it one step further, we find that the
379likelihood to purchase a bike increases if the customer is actually age 38 to 40.
380Because you enabled drillthrough when you created the structure and model, you can
381retrieve detailed information from the model cases and mining structure, including those
382columns that were not included in the mining model (e.g., emailAddress, FirstName).
3831. Right-click a node, and select Drill Through then Model Columns Only.
384The details for each training case are displayed in spreadsheet format. These
385details come from the vTargetMail view that you selected as the case table when
386building the mining structure.
3872. Right-click a node, and select Drill Through then Model and Structure
388To drill through to case data
389Columns.
390The same spreadsheet displays with the structure columns appended to the end.
391Back to Top
392Dependency Network Tab
393The Dependency Network tab displays the relationships between the attributes that
394contribute to the predictive ability of the mining model. The Dependency Network
395viewer reinforces our findings that Age and Region are important factors in predicting
396bike buying.
3971. Click the Bike Buyer node to identify its dependencies.
398The center node for the dependency network, Bike Buyer, represents the
399predictable attribute in the mining model. The pink shading indicates that all of
400the attributes have an effect on bike buying.
4012. Adjust the All Links slider to identify the most influential attribute.
402As you lower the slider, only the attributes that have the greatest effect on the
403[Bike Buyer] column remain. By adjusting the slider, you can discover that age
404and region are the greatest factors in predicting whether someone is a bike
405buyer.
406Exploring the Clustering Model (Basic Data Mining Tutorial)
407The Microsoft Clustering algorithm groups cases into clusters that contain similar
408characteristics. These groupings are useful for exploring data, identifying anomalies in
409the data, and creating predictions.
410The Microsoft Cluster Viewer provides the following tabs for use in exploring clustering
411mining models:
412Cluster Diagram
413Cluster Profiles
414Cluster Characteristics
415Cluster Discrimination
416To explore the model in the Dependency Network tab
41726
418Cluster Diagram Tab
419The Cluster Diagram tab displays all the clusters that are in a mining model. The lines
420between the clusters represent "closeness" and are shaded based on how similar the
421clusters are. The actual color of each cluster represents the frequency of the variable and
422the state in the cluster.
4231. Use the Mining Model list at the top of the Mining Model Viewer tab to switch
424to the TM_Clustering model.
4252. In the Viewer list, select Microsoft Cluster Viewer.
4263. In the Shading Variable box, select Bike Buyer.
427The default variable is Population, but you can change this to any attribute in the
428model, to discover which clusters contain members that have the attributes you
429want.
4304. Select 1 in the State box to explore those cases where a bike was purchased.
431The Density legend describes the density of the attribute state pair selected in
432the Shading Variable and the State. In this example it tells us that the cluster with
433the darkest shading has the highest percentage of bike buyers.
4345. Pause your mouse over the cluster with the darkest shading.
435A tooltip displays the percentage of cases that have the attribute, Bike Buyer =
4361.
4376. Select the cluster that has the highest density, right-click the cluster, select
438Rename Cluster and type Bike Buyers High for later identification. Click .
4397. Find the cluster that has the lightest shading (and the lowest density). Right-click
440the cluster, select Rename Cluster and type Bike Buyers Low. Click .
4418. Click the Bike Buyers High cluster and drag it to an area of the pane that will
442give you a clear view of its connections to the other clusters.
443When you select a cluster, the lines that connect this cluster to other clusters are
444highlighted, so that you can easily see all the relationships for this cluster. When
445the cluster is not selected, you can tell by the darkness of the lines how strong
446the relationships are amongst all the clusters in the diagram. If the shading is
447light or nonexistent, the clusters are not very similar.
4489. Use the slider to the left of the network, to filter out the weaker links and find the
449clusters with the closest relationships. The Adventure Works Cycles marketing
450department might want to combine similar clusters together when determining
451To explore the model in the Cluster Diagram tab
452the best method for delivering the targeted mailing.
453Cluster Profiles Tab
454The Cluster Profiles tab provides an overall view of the TM_Clustering model. The
455Cluster Profiles tab contains a column for each cluster in the model. The first column
456lists the attributes that are associated with at least one cluster. The rest of the viewer
457contains the distribution of the states of an attribute for each cluster. The distribution of
458a discrete variable is shown as a colored bar with the maximum number of bars
459displayed in the Histogram bars list. Continuous attributes are displayed with a
460diamond chart, which represents the mean and standard deviation in each cluster.
4611. Set Histogram bars to 5.
462In our model, 5 is the maximum number of states for any one variable.
4632. If the Mining Legend blocks the display of the Attribute profiles, move it out of
464the way.
4653. Select the Bike Buyers High column and drag it to the right of the Population
466column.
4674. Select the Bike Buyers Low column and drag it to the right of the
468Bike Buyers High column.
4695. Click the Bike Buyers High column.
470The Variables column is sorted in order of importance for that cluster. Scroll
471through the column and review characteristics of the Bike Buyer High cluster. For
472example, they are more likely to have a short commute.
4736. Double-click the Age cell in the Bike Buyers High column.
474The Mining Legend displays a more detailed view and you can see the age range
475of these customers as well as the mean age.
4767. Right-click the Bike Buyers Low column and select Hide Column.
477Cluster Characteristics Tab
478With the Cluster Characteristics tab, you can examine in more detail the characteristics
479that make up a cluster. Instead of comparing the characteristics of all of the clusters (as
480in the Cluster Profiles tab), you can explore one cluster at a time. For example, if you
481select Bike Buyers High from the Cluster list, you can see the characteristics of the
482customers in this cluster. Though the display is different from the Cluster Profiles viewer,
483the findings are the same.
484To explore the model in the Cluster Profiles tab
485Unless you set an initial value for holdoutseed, results will vary each time you
486process the model. For more information, see HoldoutSeed Element
487Back to Top
488Cluster Discrimination Tab
489With the Cluster Discrimination tab, you can explore the characteristics that distinguish
490one cluster from another. After you select two clusters, one from the Cluster 1 list, and
491one from the Cluster 2 list, the viewer calculates the differences between the clusters
492and displays a list of the attributes that distinguish the clusters most.
4931. In the Cluster 1 box, select Bike Buyers High.
4942. In the Cluster 2 box, select Bike Buyers Low.
4953. Click Variables to sort alphabetically.
496Some of the more substantial differences among the customers in the
497Bike Buyers Low and Bike Buyers High clusters include age, car ownership,
498number of children, and region.
499Exploring the Naive Bayes Model (Basic Data Mining Tutorial)
500The Microsoft Naive Bayes algorithm provides several methods for displaying the
501interaction between bike buying and the input attributes.
502The Microsoft Naive Bayes Viewer provides the following tabs for use in exploring Naive
503Bayes mining models:
504Dependency Network
505Attribute Profiles
506Attribute Characteristics
507Attribute Discrimination
508The following sections describe how to explore the other mining models.
509To explore the model in the Cluster Discrimination tab
51029
511• Exploring the Decision Tree Model (Basic Data Mining Tutorial)
512• Exploring the Clustering Model (Basic Data Mining Tutorial)
513Dependency Network
514The Dependency Network tab works in the same way as the Dependency Network tab
515for the Microsoft Tree Viewer. Each node in the viewer represents an attribute, and the
516lines between nodes represent relationships. In the viewer, you can see all the attributes
517that affect the state of the predictable attribute, Bike Buyer.
5181. Use the Mining Model list at the top of the Mining Model Viewer tab to switch
519to the TM_NaiveBayes model.
5202. Use the Viewer list to switch to Microsoft Naive Bayes Viewer.
5213. Click the Bike Buyer node to identify its dependencies.
522The pink shading indicates that all of the attributes have an effect on bike buying.
5234. Adjust the slider to identify the most influential attribute.
524As you lower the slider, only the attributes that have the greatest effect on the
525[Bike Buyer] column remain. By adjusting the slider, you can discover that a few of
526the most influential attributes are: number of cars owned, commute distance, and
527total number of children.
528Back to Top
529Attribute Profiles
530The Attribute Profiles tab describes how different states of the input attributes affect
531the outcome of the predictable attribute.
5321. In the Predictable box, verify that Bike Buyer is selected.
5332. If the Mining Legend is blocking display of the Attribute profiles, move it out of
534the way.
5353. In the Histogram bars box, select 5.
536In our model, 5 is the maximum number of states for any one variable.
537The attributes that affect the state of this predictable attribute are listed together
538with the values of each state of the input attributes and their distributions in each
539state of the predictable attribute.
5404. In the Attributes column, find Number Cars Owned. Notice the differences in
541the histograms for bike buyers (column labeled 1) and non-buyers (column
542labeled 0). A person with zero or one car is much more likely to buy a bike.
5435. Double-click the Number Cars Owned cell in the bike buyer (column labeled 1)
544column.
545To explore the model in the Dependency Network tab
546To explore the model in the Attribute Profiles tab
54730
548The Mining Legend displays a more detailed view.
549Back to Top
550Attribute Characteristics
551With the Attribute Characteristics tab, you can select an attribute and value to see how
552frequently values for other attributes appear in the selected value cases.
5531. In the Attribute list, verify that Bike Buyer is selected.
5542. Set the Value to 1.
555In the viewer, you will see that customers who have no children at home, short
556commutes, and live in the North America region are more likely to buy a bike.
557Back to Top
558Attribute Discrimination
559With the Attribute Discrimination tab, you can investigate the relationship between
560two discrete values of bike buying and other attribute values. Because the
561TM_NaiveBayes model has only two states, 1 and 0, you do not have to make any
562changes to the viewer.
563In the viewer, you can see that people who do not own cars tend to buy bicycles, and
564people who own two cars tend not to buy bicycles.
565Lesson 5: Testing Models (Basic Data Mining
566Tutorial)
567Now that you have processed the model by using the targeted mailing scenario training
568set, you will test your models against the testing set. Because the data in the testing set
569already contains known values for bike buying, it is easy to determine whether the
570model's predictions are correct. The model that performs the best will be used by the
571To explore the model in the Attribute Characteristics tab
57231
573Adventure Works Cycles marketing department to identify the customers for their
574targeted mailing campaign.
575In this lesson you will first test your models by making predictions against the testing set.
576Next, you will test your models on a filtered subset of the data. Analysis Services
577provides a variety of methods to determine the accuracy of mining models. In this lesson
578we will take a look at a lift chart.
579Validation is an important step in the data mining process. Knowing how well your
580targeted mailing mining models perform against real data is important before you
581deploy the models into a production environment. For more information about how
582model validation fits into the larger data mining process, see Data Mining Concepts
583(Analysis Services - Data Mining).
584This lesson contains the following tasks:
585Testing Accuracy with Lift Charts (Basic Data Mining Tutorial)
586Testing a Filtered Model (Basic Data Mining Tutorial)
587Testing Accuracy with Lift Charts (Basic Data Mining Tutorial)
588On the Mining Accuracy Chart tab of Data Mining Designer, you can calculate how well
589each of your models makes predictions, and compare the results of each model directly
590against the results of the other models. This method of comparison is referred to as a lift
591chart. Typically, the predictive accuracy of a mining model is measured by either lift or
592classification accuracy. For this tutorial we will use the lift chart only. For more
593information about lift charts and other accuracy charts, see Tools for Charting Model
594Accuracy (Analysis Services - Data Mining).
595In this topic, you will perform the following tasks:
596• Choosing Input Data
59732
598• Selecting the Models, Predictable Columns, and Values
599Choosing the Input Data
600The first step in testing the accuracy of your mining models is to select the data source
601that you will use for testing. You will test how well the models perform against your
602testing data and then you will use them with external data.
6031. Switch to the Mining Accuracy Chart tab in Data Mining Designer in SQL Server
604Data Tools (SSDT) and select the Input Selection tab.
6052. In the Select data set to be used for Accuracy Chart group box, select Use
606mining structure test cases to test your models by using the testing data that
607you set aside when you created the mining structure.
608Selecting the Models, Predictable Columns, and Values
609The next step is to select the models that you want to include in the lift chart, the
610predictable column against which to compare the models, and the value to predict.
611The mining model columns in the Predictable Column Name list are restricted
612to columns that have the usage type set to Predict or Predict Only and have a
613content type of Discrete or Discretized.
6141. On the Input Selection tab of Data Mining Designer, under Select predictable
615mining model columns to show in the lift chart, select the checkbox for
616Synchronize Prediction Columns and Values.
6172. In the Predictable Column Name column, verify that Bike Buyer is selected for
618each model.
6193. In the Show column, select each of the models.
620By default, all the models in the mining structure are selected. You can decide not
621to include a model, but for this tutorial leave all the models selected.
6224. In the Predict Value column, select 1. The same value is automatically filled in for
623each model that has the same predictable column.
6245. Select the Lift Chart tab to display the lift chart.
625When you click the tab, a prediction query runs against the server and database
626for the mining structure and the input table or test data. The results are plotted
627on the graph.
628When you enter a Predict Value, the lift chart plots a Random Guess Model as
629To select the data set
630Note
631To show the lift of the models
63233
633well as an Ideal Model. The mining models you created will fall between these
634two extremes; between a random guess and a perfect prediction. Any
635improvement from the random guess is considered to be lift.
6366. Use the legend to locate the colored lines representing the Ideal Model and the
637Random Guess Model.
638You'll notice that the TM_Decision_Tree model provides the greatest lift,
639outperforming both the Clustering and Naive Bayes models.
640For an in-depth explanation of a lift chart similar to the one created in this lesson, see Lift
641Chart (Analysis Services - Data Mining).
642Next Task in Lesson
643Adding a Filter to a Model (Basic Data Mining Tutorial)
644See Also
645Creating Predictions (Data Mining Tutorial)
646Lift Chart Tab (Mining Accuracy Chart View)
647Testing a Filtered Model (Basic Data Mining Tutorial)
648Now that you have determined that the TM_Decision_Tree model is the most accurate,
649you should evaluate the model in the context of the Adventure Works Cycles targeted
650mailing campaign. The ssSampleDBCoFull Marketing department wants to know if there
651is a difference in the characteristics of male bike buyers and female bike buyers. This
652information will help them decide which magazines to use for advertising and which
653products to feature in their mailings.
654In this lesson, we will create a model that is filtered on gender. You can then easily make
655a copy of that model, and change just the filter condition to generate a new model
656based on a different gender.
657For more information on filters, see Creating Filters for Mining Models (Analysis Services
658- Data Mining).
659Using Filters
660Filtering enables you to easily create models built on subsets of your data. The filter is
661applied only to the model and does not change the underlying data source. For
662information on applying filters to nested tables, see Intermediate Data Mining Tutorial
663(Analysis Services - Data Mining).
664Filters on Case Tables
665First you will make a copy of the TM_Decision_Tree model.
6661. In SQL Server Data Tools (SSDT), in Solution Explorer, select BasicDataMining.
6672. Click the Mining Models tab.
668To copy the Decision Tree Model
66934
6703. Right click the TM_Decision_Tree model, and select New Mining Model.
6714. In the Model name field, type TM_Decision_Tree_Male.
6725. Click OK.
673Next, create a filter to select customers for the model based on their gender.
6741. Right-click the TM_Decision_Tree_Male mining model to open the shortcut
675menu.
676-- or --
677Select the model. On the Mining Model menu, select Set Model Filter.
6782. In the Model Filter dialog box, click the top row in the grid, in the Mining
679Structure Column text box.
680The drop-down list displays only the names of the columns in that table.
6813. In the Mining Structure Column text box, select Gender.
682The icon at the left side of the text box changes to indicate that the selected item
683is a table or a column.
6844. Click the Operator text box and select the equal (=) operator from the list.
6855. Click the Value text box, and type M.
6866. Click the next row in the grid.
6877. Click OK to close the Model Filter dialog box.
688The filter displays in the Properties window. Alternately, you can launch the
689Model Filter dialog from the Properties window.
6908. Repeat the above steps, but this time name the model
691TM_Decision_Tree_Female and type F in the Value text box.
692You now have two new models displayed in the Mining Models tab.
693Process the Filtered Models
694Models cannot be used until they have been deployed and processed. For more
695information on processing models, see Processing Models in the Targeted Mailing
696Structure (Basic Data Mining Tutorial).
6971. Right-click the TM_Decision_Tree_Male model and select Process Mining
698Structure and all Models
6992. Click Run to process the new models.
7003. After processing is complete, click Close on both processing windows..
701Evaluate the Results
702To create a case filter on a mining model
703To process the filtered model
704View the results and assess the accuracy of the filtered models in much the same way as
705you did for the previous three models. For more information, see:
706Exploring the Decision Tree Model (Basic Data Mining Tutorial)
707Testing Accuracy with Lift Charts (Basic Data Mining Tutorial)
7081. Select the Mining Model Viewer tab in Data Mining Designer.
7092. In the Mining Model box, select TM_Decision_Tree_Male.
7103. Slide Show Level to 3.
7114. Change the Background value to 1.
7125. Place your cursor over the node labeled All to see the number of bike buyers
713versus non-bike buyers.
7146. Repeat steps 1 - 5 for TM_Decision_Tree_Female.
7157. Explore the results for the TM_Decision_Tree and the models filtered for gender.
716Compared to all bike buyers, male and female bike buyers share some of the
717same characteristics as the unfiltered bike buyers but all three have interesting
718differences as well. This is useful information that Adventure Works Cycles can
719use to develop their marketing campaign.
7201. Switch to the Mining Accuracy Chart tab in Data Mining Designer in SQL Server
721Data Tools (SSDT) and select the Input Selection tab.
7222. In the Select data set to be used for Accuracy Chart group box, select Use
723mining structure test cases.
7243. On the Input Selection tab of Data Mining Designer, under Select predictable
725mining model columns to show in the lift chart, select the checkbox for
726Synchronize Prediction Columns and Values.
7274. In the Predictable Column Name column, verify that Bike Buyer is selected for
728each model.
7295. In the Show column, select each of the models.
7306. In the Predict Value column, select 1.
7317. Select the Lift Chart tab to display the lift chart.
732You will now notice that all three Decision Tree models provide significant lift
733compared to the random guess model, and also outperform the Clustering and
734Naive-Bayes models.
735To explore the filtered models
736To test the lift of the filtered models
73736
738Lesson 6: Creating and Working with Predictions
739(Basic Data Mining Tutorial)
740You have trained, tested, and explored the data mining models you created. Now you
741are ready to use the models to identify recipients for Adventure Works Cycles targeted
742mailing campaign. In this lesson you will create a query to predict which customers are
743most likely to purchase a bike. You will also retrieve the probability that the prediction is
744correct, so that you can decide whether to present the recommendation to the
745marketing department or not.
746Once you have identified customers with a high probability of purchasing a bike, you will
747drill through to the details of the cases in the mining model to retrieve names and
748contact information for these customers.
749Creating Predictions (Basic Data Mining Tutorial)
750After you have tested the accuracy of your mining models and decided that you are
751satisfied with them, you can then create prediction queries by using the Prediction Query
752Builder on the Mining Model Prediction tab in the Data Mining Designer. This interface 
75337
754helps you build queries in DMX, or the Data Mining Extensions (DMX) language. DMX
755has syntax like that of T-SQL but is used for queries against data mining objects.
756The Prediction Query Builder has three views. With the Design and Query views, you can
757build and examine your query. You can then run the query and view the results in the
758Result view.
759For more information about how to use the Prediction Query Builder, see Using the
760Prediction Query Builder to Create DMX Prediction Queries.
761Creating the Query
762The first step in creating a prediction query is to select a mining model and input table.
7631. On the Mining Model Prediction tab of Data Mining Designer, in the Mining
764Model box, click Select Model.
7652. In the Select Mining Model dialog box, navigate through the tree to the
766Targeted Mailing structure, expand the structure, select TM_Decision_Tree, and
767then click OK.
7683. In the Select Input Table(s) box, click Select Case Table.
7694. In the Select Table dialog box, in the Data Source list, select Adventure
770Works DW Multidimensional 2012 .
7715. In Table/View Name, select the ProspectiveBuyer (dbo) table, and then click
772OK.
773The ProspectiveBuyer table most closely resembles the vTargetMail case table.
774Mapping the Columns
775After you select the input table, Prediction Query Builder creates a default mapping
776between the mining model and the input table, based on the names of the columns. At
777least one column from the structure must match a column in the external data.
778The data that you use to determine the accuracy of the models must contain a
779column that can be mapped to the predictable column. If such a column does not
780exist, you can create one with empty values, but it must have the same data type
781as the predictable column.
7821. Right-click the lines connecting the Mining Model window to the Select Input
783Table window, and select Modify Connections.
784Notice that not every column is mapped. We will add mappings for several Table
785Columns. We will also generate a new birth date column based on the current
786date column, so that the columns match better.
787To select a model and input table
788Important
789To map the structure columns to the input table columns
79038
7912. Under Table Column, click the Bike Buyer cell and select
792ProspectiveBuyer.Unknown from the dropdown.
793This maps the predictable column, [Bike Buyer], to an input table column.
7943. Click OK.
7954. In Solution Explorer, right-click the Targeted Mailing data source view and
796select View Designer.
7975. Right-click the table, ProspectiveBuyer, and select New Named Calculation.
7986. In the Create Named Calculation dialog box, for Column name, type calcAge.
7997. For Description, type Calculate age based on birthdate.
8008. In the Expression box, type DATEDIFF(YYYY,[BirthDate],getdate()) and then
801click OK.
802Because the input table has no Age column corresponding to the one in the
803mode, you can use this expression to calculate customer age from the BirthDate
804column in the input table. Since Age was identified as the most influential
805column for predicting bike buying, it must exist in both the model and in the
806input table.
8079. In Data Mining Designer, select the Mining Model Prediction tab and re-open
808the Modify Connections window.
80910. Under Table Column, click the Age cell and select ProspectiveBuyer.calcAge
810from the dropdown.
811Warning
812If you do not see the column in the list, you might have to refresh the
813definition of the data source view that is loaded in the designer. To do
814this, from the File menu, select Save all, and then close and re-open the
815project in the designer.
81611. Click OK.
817Designing the Prediction Query
8181. The first button on the toolbar of the Mining Model Prediction tab is the
819Switch to design view / Switch to result view / Switch to query view button.
820Click the down arrow on this button, and select Design.
8212. In the grid on the Mining Model Prediction tab, click the cell in the first empty
822row in the Source column, and then select Prediction Function.
8233. In the Prediction Function row, in the Field column, select PredictProbability.
824In the Alias column of the same row, type Probability of result.
8254. From the Mining Model window above, select and drag [Bike Buyer] into the
826Criteria/Argument cell.
827To design the prediction query
82839
829When you let go, [TM_Decision_Tree].[Bike Buyer] appears in the
830Criteria/Argument cell.
831This specifies the target column for the PredictProbability function. For more
832information about functions, see Data Mining Extensions (DMX) Function
833Reference.
8345. Click the next empty row in the Source column, and then select
835TM_Decision_Tree mining model.
8366. In the TM_Decision_Tree row, in the Field column, select Bike Buyer.
8377. In the TM_Decision_Tree row, in the Criteria/Argument column, type =1.
8388. Click the next empty row in the Source column, and then select
839ProspectiveBuyer table.
8409. In the ProspectiveBuyer row, in the Field column, select ProspectiveBuyerKey.
841This adds the unique identifier to the prediction query so that you can identify
842who is and who is not likely to buy a bicycle
84310. Add five more rows to the grid. For each row, select ProspectiveBuyer table as
844the Source and then add the following columns in the Field cells:
845• calcAge
846• LastName
847• FirstName
848• AddressLine1
849• AddressLine2
850Finally, run the query and browse the results.
8511. In the Mining Model Prediction tab, select the Result button.
8522. After the query runs and the results are displayed, you can review the results.
853The Mining Model Prediction tab displays contact information for potential
854customers who are likely to be bike buyers. The Probability of result column
855indicates the probability of the prediction being correct. You can use these results
856to determine which potential customers to target for the mailing.
8573. At this point, you can save the results. You have three options:
858• Right-click a row of data in the results, and select Copy to save just that value
859(and the column heading) to the Clipboard.
860• Right-click any row in the results, and select Copy All to copy the entire result
861set, including column headings, to the Clipboard.
862• Click Save query result to save the results directly to a database as follows:
863a. In the Save Data Mining Query Result dialog box, select a data source, or
864To run the query and view results
86540
866define a new data source.
867b. Type a name for the table that will contain the query results.
868c. Use the option, Add to DSV, to create the table and add it to an existing data
869source view. This is useful if you want to keep all related tables for a model—
870such as training data, prediction source data, and query results—in the same
871data source view.
872d. Use the option, Overwrite if exists, to update an existing table with the latest
873results.
874You must use the option to overwrite the table if you have added any
875columns to the prediction query, changed the names or data types of any
876columns in the prediction query, or if you have run any ALTER statements on
877the destination table.
878Also, if multiple columns have the same name (for example, the default
879column name Expression) you must create an alias for the columns with
880duplicate names, or an error will be raised when the designer tries to save the
881results to SQL Server. The reason is that SQL Server does not allow multiple
882columns to have the same name.
883Using Drillthrough on Structure Data (Basic Data Mining Tutorial)
884As part of their advertising campaign, Adventure Works Cycles is sending a mailer to
885potential customers in the 34-40 age demographic. The marketing department has
886decided that they would also like to send the mailer to the customers who purchased
887bikes from Adventure Works Cycles more than five years ago. In this lesson you will
888identify customers with older bikes and retrieve their contact information. This
889information is not included in the model, but is included in the structure. To retrieve the
890contact information you will first ensure that drillthrough is enabled for the structure and
891then you will use drillthrough to reveal the names and addresses of the targeted
892customers.
893To enable drillthrough on a mining model
89441
8951. In SQL Server Data Tools (SSDT), on the Mining Models tab of Data Mining
896Designer, right-click the TM_Decision_Tree model, and select Properties.
8972. In the Properties windows, click AllowDrillthrough, and select True.
8983. In the Mining Models tab, right-click the model, and select Process Model.
8991. In Data Mining Designer, click the Mining Model Viewer tab.
9002. Select the TM_Decision_Tree model from the Mining Model list.
9013. Change the Background value to 1. By doing this, you show only the part of the
902model that is related to customer who bought bikes.
9034. Select the Microsoft Tree viewer from the Viewer list. This will force the viewer to
904refresh with the new filter conditions. Then, locate the Age >=34 and <41 node
905and right-click the node.
9065. Select Drill Through, and then select Model and Structure Columns to open
907the Drill Through window.
9086. Scroll to the Structure.Date First Purchase column to view the purchase dates
909for the older bikes.
9107. To copy the data to the Clipboard, right-click any row in the table, and select
911Copy All.
912Congratulations, you have completed the basic data mining tutorial. Now that you are
913comfortable using the data mining tools, we recommend that you also complete the
914intermediate data mining tutorial, which demonstrates how to create models for
915forecasting, market basket analysis, and sequence clustering.
916To view drillthrough data from a mining model
91742
918Intermediate Data Mining Tutorial (Analysis
919Services - Data Mining)
920Microsoft Analysis Services provides an integrated environment for creating and working
921with data mining models. You can easily bind to data sources, create and test multiple
922models on the same data, and deploy models for use in predictive analysis.
923In the Basic Data Mining Tutorial, you learned how to use SQL Server Data Tools (SSDT)
924to create a data mining solution, and you built three models to support a targeted
925mailing campaign for analyzing customer purchasing behavior and for targeting
926potential buyers.
927This intermediate tutorial builds on that experience and introduces several new
928scenarios, including common business requirements such as forecasting and market
929basket analysis. You will learn how to create a time series model, an association model,
930and a sequence clustering model. Finally, you will learn how to use neural network to
931explore correlations in data and to use logistic regression for predictions.
932The lessons are independent and can be completed separately.
933To complete the following tutorials, you should to be familiar with the data mining tools
934and with the mining model viewers that were introduced in the Basic Data Mining
935Tutorial.
936All scenarios use the data source, but you will create different data source views for
937different scenarios. You can do the lessons in any order as long as you create the data
938source first.
939Lesson Scenarios
940After your success with the targeted mailing campaign, you have been asked to apply
941your knowledge of data mining to develop several new models for use in business
942planning. These include the following tasks:
943• Forecasting: You will create a time series model, to forecast the sales of products in
944different regions around the world. You will develop individual models for each
945region and learn how to use cross-prediction.
946• Market basket analysis: You will create an association model, to analyze groupings
947of products that are purchased during visits to the Adventure Works Cycles ecommerce
948site. Based on this market basket model, you can recommend products to
949customers.
950• Sequence analysis: You build a sequence clustering model, to analyze the order in
951which customers buy products. Based on this model, you can plan changes in Web
952site design or new product offerings.
953• Factor analysis: You use a neural network model to explore the possible causes of
954poor service quality in call center data. Based on the insights from the preliminary 
95543
956model, you will create a logistic regression model to predict strategies for improving
957customer experience.
958What You Will Learn
959This tutorial teaches you how to create and work with several types of data mining
960algorithms. This tutorial is divided into the following lessons:
961Lesson 1: Modifying a Data Source (Intermediate Data Mining
962Tutorial)
963In this lesson, you will create a new project based on the database, to support
964several new data sources views and many more mining models.
965Lesson 2: Building the Forecasting Scenario
966In this lesson, you will create a mining model that can be used as part of a forecasting
967scenario. You will also explore mining models that are built with the Microsoft Time
968Series algorithm.
969You will build models for individual regions, and then build a general model that can be
970used for cross-prediction.
971Lesson 3: Building the Market Basket Scenario
972In this lesson, you will add a new data source view and learn how to work with nested
973tables and keys. Based on this data, you will create a mining model that can be used as
974part of a market basket scenario. You will also explore mining models that are built with
975the Microsoft Association algorithm.
976Lesson 4: Building the Sequence Clustering Scenario
977In this lesson, you will create a mining model that can be used as part of a sequence
978clustering scenario. You will also learn how to explore mining models that are built with
979the Microsoft Sequence Clustering algorithm.
980Lesson 5 Neural Net and Logistic Regression
981In this lesson, you will create several related mining models, using the Microsoft Neural
982Network and Microsoft Logistic Regression algorithms. You will also learn to work with
983data source views to explore data underlying the models.
984Requirements
985Make sure that the following are installed:
986• Microsoft SQL Server 2012
987• Microsoft SQL Server Analysis Services
988• SQL Server with the database.
989By default, the sample databases are not installed, to enhance security. To install the
990official databases for Microsoft SQL Server, visit the Microsoft SQL Sample Databases
991page and select the appropriate version of the sample database.
99244
993Lesson 1: Creating the Intermediate Data Mining
994Solution (Intermediate Data Mining Tutorial)
995In the Basic Data Mining tutorial, you created an Analysis Services project that contains a
996simple data mining solution based on the new database.
997For this tutorial, you will create a separate Analysis Services project by using SQL Server
998Data Tools (SSDT). You will create a Analysis Services data source that uses , and
999add several new data source views to that data source, to support the scenarios and
1000model types.
1001Creating a Solution and Data Source (Intermediate Data Mining
1002Tutorial)
1003To work with data mining, you must first create a project in SQL Server Data Tools (SSDT)
1004using the template, Analysis Services Multidimensional and Data Mining Project.
1005When you open the template, it loads into the designer all the schemas that you might 
100645
1007need for data mining: data sources, mining structures and mining models, and even
1008cubes if your mining structure uses multidimensional data.
1009When you create the project, your solution is stored as a local file until the solution is
1010deployed. When you deploy the solution, Analysis Services looks for the Analysis Services
1011server specified in the project properties, and creates a new Analysis Services database
1012with the same name as the project. By default, Analysis Services uses the localhost
1013instance for new projects. If you are using a named instance, or if you specified a
1014different name for the default instance, you must change the deployment database
1015property of the project to the location where you want to create your data mining
1016objects.
1017For more information about Analysis Services projects, see Defining an Analysis Services
1018Project.
1019Procedures
10201. Open SQL Server Data Tools (SSDT).
10212. On the File menu, point to New, and then click Project.
10223. Select Analysis Services Multidimensional and Data Mining Project from the
1023Installed Templates pane.
10244. In the Name box, name the new project DM Intermediate.
10255. Click .
10261. In SQL Server Data Tools (SSDT), on the Project menu, click Properties.
10272. In the left side of the Property Pages pane, click Deployment.
10283. Verify that the Server name is localhost. If you are using a different instance,
1029type the name of the instance. If you are using a named instance of Analysis
1030Services, type the machine name and then the instance name. Click .
10311. In Solution Explorer, right-click the project, and then select Properties.
1032-- or --
1033In SQL Server Data Tools (SSDT), on the Project menu, select Properties.
10342. In the left side of the Property Pages pane, click Deployment.
1035In the Options pane, select Deployment Mode, and set the options to Deploy
1036All to overwrite, or to Deploy Changes Only to update objects or add objects.
1037Creating a Data Source
1038To create a new Analysis Services project for this tutorial
1039To change the instance where data mining objects are stored (optional)
1040To change the deployment properties for a project (optional)
1041In the Basic Data Mining Tutorial, you created a data source that stores connection
1042information for the database. Follow the same steps to create the data source
1043in this solution.
1044• Creating a Data Source (Basic Data Mining Tutorial)
1045A single data source can support multiple data source views, and each data source view
1046can have multiple tables. However, because the data source and data source view are
1047deployed to your Microsoft SQL Server Analysis Services database together with the data
1048mining models that you create, as a best practice you should include in each data source
1049view only those tables that are required for each data mining model or group of models.
1050In the following lessons, you will add data source views to support each of the new
1051scenarios. Only the market basket and sequence clustering lessons use the same data
1052source view; otherwise, each scenario uses a different data source view, so the lessons
1053are independent of each other and can be completed separately.
1054Scenario Data included in the data source view
1055Lesson 2: Building a Forecasting Scenario
1056(Intermediate Data Mining Tutorial)
1057Monthly sales reports for bicycle models in
1058different regions, collected as a single view.
1059Lesson 3: Building a Market Basket
1060Scenario (Intermediate Data Mining
1061Tutorial)
1062A table containing a list of customer orders,
1063and a nested table showing the individual
1064purchases for each customer.
1065Lesson 4: Building a Sequence Clustering
1066Scenario (Intermediate Data Mining
1067Tutorial)
1068The same data that is used for the market
1069basket analysis, with the addition of an
1070identifier that shows the order in which
1071items were purchased.
1072Lesson 5: Building a Neural Network Model
1073(Intermediate Data Mining Tutorial)
1074A single table containing some preliminary
1075performance tracking data from a call
1076center.
1077To create a data source
1078Lesson 2: Building a Forecasting Scenario
1079(Intermediate Data Mining Tutorial)
1080As the sales analyst for Adventure Works Cycles, you have been asked to forecast the
1081sales of products for the next year. In particular, you have been asked to compare
1082forecasts for the different regions and product lines. Additionally, you have been asked
1083to determine whether sales of different products vary depending on the time of the year.
1084To find the requested information, in this lesson you will summarize the company's sales
1085data at the monthly level, and you will also summarize sales figures by three regions:
1086Europe, North America, and the Pacific.
1087After you complete the tasks in this lesson, you will be able to answer the following
1088questions:
1089• How do the sales of different bike models change over time?
1090• Are there differences between the patterns for sales in the three regions?
1091• Can we forecast sales peaks?
1092The lesson can be completed in two parts:
1093• Part One introduces the basics of how to create and use a time series model.
1094• Part Two walks you through creation of a general time series model, based on all
1095regions, that can be used for cross-prediction.
109648
1097Adding a Data Source View for Forecasting (Intermediate Data
1098Mining Tutorial)
1099In this task, you add a data source view that will be used for the forecasting scenario. A
1100forecasting model requires that the data contains a column that can be used to identify
1101steps in a time series. If you plan to analyze multiple series of data, all series must end on
1102the same date or time step.
1103Procedures
11041. In Solution Explorer, right-click Data Source Views, and then select New Data
1105Source View.
11062. On the Welcome to the Data Source View Wizard page, click Next.
11073. On the Select a Data Source page, under Relational data sources, select the
1108data source. Click Next.
1109Note
1110If you do not have this data source, you can find the steps to create the
1111data source in the Basic Data Mining Tutorial.
11124. On the Select Tables and Views page, select the table, vTimeSeries (dbo), and
1113then click the right arrow to add it to the data source view.
1114To add a data source view
11155. Click Next.
11166. On the Completing the Wizard page, by default the data source view is named
1117Adventure Works DW Multidimensional 2012 . Change the name to
1118SalesByRegion, and then click Finish.
1119Data Source View Designer opens and the SalesByRegion data source view
1120appears.
1121Working with the Data Source View
1122After you have created the data source view, you can explore the data in the following
1123ways:
1124• Right-click the table vTimeSeries in the designer, and select Explore Data to open
1125the selected table in a grid.
1126• Click Sampling options and then use the Data Exploration Options dialog box to
1127change the sampling method. Click Refresh to load data in the table using the new
1128option settings. For example, you could specify the number of rows to output in the
1129sample, or choose the top rows.
1130• Right-click the table vTimeSeries and select Properties to assign a new name to the
1131table. You can also select individual columns from the data source view, and the
1132modify the column properties.
1133• Click anywhere in the data source view design area to create a new query and assign
1134a name to it, to create relationships between tables, or to change the layout of the
1135design area.
1136• Right-click a table and select New Named Calculation to create derived columns,
1137including aggregations. You can also add new tables and views from the data source
1138in this view.
1139In the next task, you will explore the time series data and determine the best column to
1140use as the time series identifier. You will also learn how to handle gaps in time series
1141data.
1142Understanding the Requirements for a Time Series Model (Intermediate
1143Data Mining Tutorial)
1144When you are preparing data for use in a forecasting model, you must ensure that your
1145data contains a column that can be used to identify the steps in the time series. That
1146column will be designated as the Key Time column. Because it is a key, the column must
1147contain unique numeric values. 
114850
1149Choosing the right unit for the Key Time column is an important part of analysis. For
1150example, suppose your sales data is refreshed on a minute by minute basis. You would
1151not necessarily use minutes as the unit for the time series; you might find it more
1152meaningful to roll up sales data by the day, week, or even month. If you are unsure
1153which unit of time to use, you can create a new data source view for each aggregation,
1154and build related models, to see if different trends emerge at each level of aggregation.
1155For this tutorial, sales data is collected on a daily basis in the transactional sales
1156database, but for data mining, the data has been pre-aggregated by the month, using a
1157view.
1158Additionally, it is desirable for analysis that the data have as few gaps as possible. If you
1159plan to analyze multiple series of data, all series should preferably start and end on the
1160same date. If the data has gaps, but the gaps are not at the beginning or end of a series,
1161you can use the MISSING_VALUE_SUBSTITUTION parameter to fill in the series. Analysis
1162Services also provides several options for replacing missing data with values, such as
1163using means or constants.
1164The PivotChart and PivotTable tools that were included in earlier versions of the
1165data source view designer are no longer provided. We recommend that you
1166identify gaps in time series data beforehand, by using tools such as the Data
1167Profiler included in Integration Services.
1168Procedures
11691. In the pane, SalesByRegion.dsv [Design], right-click the table vTimeSeries, and
1170then select Explore Data.
1171A new tab opens, titled Explore vTimeSeries Table.
11722. On the Table tab, review the data that is used in the TimeIndex and Reporting
1173Date columns.
1174Both are sequences with unique values and can both be used as the time series
1175key; however, the data types of the columns are different. The Microsoft Time
1176Series algorithm does not require a datetime data type, only that the values be
1177distinct and ordered. Therefore, either column can be used as the time key for the
1178forecasting model.
11793. In the data source view design surface, select the column, Reporting Date and
1180select Properties. Next, click the column TimeIndex and select Properties.
1181The field TimeIndex has the data type System.Int32, whereas the field Reporting
1182Date has the data type System.DateTime. Many data warehouses convert
1183date/time values to integers and use the integer column as the key, to improve
1184indexing performance. However, if you use this column, the Microsoft Time Series
1185algorithm will make predictions using future values such as 201014, 201014, and
1186Warning
1187To identify the time key for the forecasting model
118851
1189so forth. Because you want to represent your sales data forecast by using
1190calendar dates, you will use the Reporting Date column as the unique series
1191identifier.
11921. In the pane SalesByRegion.dsv, select the vTimeSeries table.
11932. Right-click the column, Reporting Date, and select Set Logical Primary Key.
1194Handling Missing Data (Optional)
1195If any series has missing data, you might get an error when you try to process the model.
1196You have several ways to work around missing data:
1197• You can have Analysis Services fill in missing values, either by calculating a mean, or
1198by using a previous value. You do this by setting the MISSING_VALUE_SUBSTITUTION
1199parameter on the mining model. For more information about this parameter, see
1200Microsoft Time Series Algorithm Technical Reference (Analysis Services - Data
1201Mining). For information about how to change parameters on an existing mining
1202model, see How to Parameters.
1203• You can alter the data source or filter the underlying view to eliminate ragged series
1204or to replace values. You can do this in the relational data source, or you can modify
1205the data source view by creating custom named queries or named calculations. For
1206more information, see Designing Data Source Views (Analysis Services). A later task in
1207this lesson provides an example of how to build both a named query and a custom
1208calculation.
1209For this scenario, some data is missing at the beginning of one series: that is, there is no
1210data for the T1000 product line until July 2007. Otherwise, all series end on the same
1211date, and there are no missing values.
1212The requirement of the Microsoft Time Series algorithm is that any series that you
1213include in a single model should have the same ending point. Because the T1000 bicycle
1214model was introduced in 2007, the data for this series starts later than for other bicycle
1215models, but the series ends on the same date; therefore the data is usable.
1216• Right-click the tab, Explore vTimeSeries Table, and select Close.
1217To set the key in the data source view
1218To close the data source view designer
121952
1220Creating a Forecasting Structure and Model (Intermediate Data
1221Mining Tutorial)
1222Next, you will use the Data Mining Wizard to create a new mining structure and mining
1223model based on the data source view that you just created. In this task you will specify
1224that the mining model should use the Microsoft Time Series algorithm.
1225Procedures
12261. In Solution Explorer in SQL Server Data Tools (SSDT), right-click Mining
1227Structures and select New Mining Structure.
12282. On the Welcome to the Data Mining Wizard page, click Next.
12293. On the Select the Definition Method page, verify that From existing relational
1230database or data warehouse is selected, and then click Next.
12314. On the Create the Data Mining Structure page, under Which data mining
1232technique do you want to use?, select Microsoft Time Series, and then click
1233Next.
12345. On the Select Data Source View page, under Available data source views,
1235select SalesByRegion.
12366. Click Next.
12377. On the Specify Table Types page, ensure that the check box in the Case column
1238for the vTimeSeries table is selected, and then click Next.
12398. On the Specify the Training Data page, select the check boxes in the Key
1240column for the ModelRegion and ReportingDate columns.
1241ReportingDate should be selected by default, because you specified this column
1242as the logical primary key when you created the data source view. By adding
1243ModelRegion as a second key, you are telling the algorithm to create a separate
1244time series for each combination of model and region listed in this field.
12459. Select the check boxes in the Input and Predictable columns for the Quantity,
1246column, and then click Next.
1247By selecting Predictable, you indicate that you want to create forecasts on the
1248data in this column. However, because you want to base the forecasts on past
1249data, you must also add the column as an input.
125010. On the page Specify Columns' Content and Data Type, review the selections.
1251The ModelRegion column is designated as a Key column and the ReportingDate
1252column is automatically designated as a Key Time column. You can have only
1253one of each type of key.
125411. Click Next.
125512. On the Completing the Wizard page, for Mining structure name, type
1256To create a forecasting mining structure
125753
1258Forecasting.
1259Note
1260The option to enable drillthrough is not available for time series models.
126113. In Mining model name, type Forecasting, and then click Finish.
1262Data Mining Designer opens to display the Forecasting mining structure that
1263you just created.
1264Modifying the Forecasting Structure (Intermediate Data Mining
1265Tutorial)
1266The mining structure that you created in the previous task contains a single forecasting
1267model. Before you process and explore the model, you must change its structure slightly
1268and modify one of its properties.
1269Modifying the Mining Structure
1270You can change the mining structure by using the Mining Structure tab of Data Mining
1271Designer. When you created the model with the Data Mining Wizard, you used three
1272columns: ReportingDate, ModelRegion, and Quantity. However, the Forecasting table
1273also contains an Amount column, which you can use to forecast the amount of sales. By
1274using the Mining Structure tab, you can add this column from the data source view to
1275the mining structure.
12761. On the Mining Structure tab of Data Mining Designer, in the Data Source View
1277pane, select the Amount column in the vTimeSeries table.
12782. Drag the Amount column from the Data Source View pane into the list of
1279columns for the Forecasting structure.
1280The Amount column is now included in the Forecasting mining structure.
1281Modifying the Columns in the Mining Model
1282Because you added a new column to the structure, you must define how the model will
1283use the column. You can specify how the column will be used on the Mining Models tab
1284of Data Mining Designer.
1285The Mining Models tab lists the columns that the mining structure contains in the
1286Structure column of the grid, and lists the columns that the mining model contains in
1287To add the Amount column to the Forecasting mining structure
128854
1289the column that has the name of the model, in this case Forecasting. Click the names of
1290the columns to make modifications. In the Forecasting mining model, the Amount
1291column is used as an input column and is also used to forecast future sales. Therefore,
1292you must set the properties of the column so that it can be used as both an input
1293column and a predictable column.
1294In the Mining Models tab, you can also create new models based on the same
1295structure, and you can adjust the algorithm and column properties for each
1296model. However, you must process the model before these changes take effect.
12971. In the Forecasting column of the grid on the Mining Models tab, click the cell in
1298the Amount row.
12992. Select Predict from the list.
1300The Amount column is now both an input column and a predictable column.
1301You can also change the properties of individual columns by selecting the column and
1302opening the Properties window. To open the Properties window, right-click the column
1303name, and then select Properties. If you change a property within the column for an
1304individual model, you can change the properties only for that model. However, when you
1305change a property within the Structure column, the change affects every model that is
1306associated with the structure. Whenever you make changes to the model or structure,
1307you must reprocess to see the effects.
1308Customizing and Processing the Forecasting Model (Intermediate
1309Data Mining Tutorial)
1310The Microsoft Time Series algorithm provides parameters that affect how a model is
1311created, and how time data is analyzed. Changing these properties can significantly
1312affect how the mining model makes predictions.
1313For this task in the tutorial, you will perform the following tasks to modify the model:
13141. You will customize the way your model handles time periods by adding a new value
1315for the PERIODICITY_HINT parameter.
13162. You will learn about two other important parameters for the Microsoft Time Series
1317algorithm: FORECAST_METHOD, which lets you control the method used for
1318Note
1319To define how the Amount column will be used
132055
1321forecasting, and PREDICTION_SMOOTHING, which lets you customize the blend of
1322long-term and short-term predictions.
13233. Optionally, you will tell the algorithm how you want missing values to be imputed.
13244. After all the changes have been made, you will deploy and process the model.
1325Setting Time Series Parameters
1326Periodicity Hints
1327The PERIODICITY_HINT parameter provides the algorithm with information about
1328additional time periods that you expect to see in the data. By default, time series models
1329will try to automatically detect a pattern in the data. However, if you already know the
1330expected time cycle, providing a periodicity hint can potentially improve the accuracy of
1331the model. However, if you provide the wrong periodicity hint, it can decrease accuracy;
1332therefore, if you are not sure what value should be used, it is best to use the default.
1333For example, the view used for this model aggregates sales data from Adventure
1334Works DW Multidimensional 2012 on a monthly basis. Therefore each time slice used
1335by the model represents one month, and all predictions will also be in terms of months.
1336Since there are 12 months in a year and you expect that sales patterns more or less
1337repeat on a yearly basis, you will set the PERIODICITY_HINT parameter to 12, to indicate
1338that 12 time slices (months) constitute one complete sales cycle.
1339Forecasting Method
1340The FORECAST_METHOD parameter controls whether the time series algorithm is
1341optimized for short-term or long-term predictions. By default, the FORECAST_METHOD
1342parameter is set to MIXED, which means that two different algorithms are blended and
1343balanced to provide good results for both short-term and long-term prediction.
1344However, if you know that you want to use a particular algorithm, you can change the
1345value to either ARIMA or ARTXP.
1346Weighting Long-Term vs. Short-Term Predictions
1347You can also customize the way that long-term and short-term predictions are combined
1348by using the PREDICTION_SMOOTHING parameter. By default, this parameter is set to
13490.5, which generally provides the best balance for overall accuracy.
13501. On the Mining Models tab, right-click Forecasting, and select Set Algorithm
1351Parameters.
13522. In the PERIODICITY_HINT row of the Algorithm Parameters dialog box, click
1353the Value column, then type {12}, including the braces.
1354By default, the algorithm will also add the value {1}.
13553. In the FORECAST_METHOD row, verify that the Value text box is either blank or
1356set to MIXED. If a different value has been entered, type MIXED to change the
1357parameter back to the default value.
1358To change the algorithm parameters
135956
13604. In the PREDICTION_SMOOTHING row, verify that the Value text box is either
1361blank or set to 0.5. If a different value has been entered, click Value and type 0.5
1362to change the parameter back to the default value.
1363Note
1364The PREDICTION_SMOOTHING parameter is available only in SQL Server
1365Enterprise. Therefore, you cannot view or change the value of the
1366PREDICTION_SMOOTHING parameter in SQL Server Standard. However,
1367the default behavior is to use both algorithms and weight them equally.
13685. Click OK.
1369Handling Missing Data (Optional)
1370In many cases, your sales data might have gaps that are filled with nulls, or a store might
1371have failed to meet the reporting deadline, leaving an empty cell at the end of the series.
1372In such scenarios, Analysis Services raises the following error and will not process the
1373model.
1374"Error (Data mining): Time stamps not synchronized starting with series <series
1375name>, of the mining model, <model name>. All time series must end at the same
1376time mark and cannot have arbitrarily missing data points. Setting the
1377MISSING_VALUE_SUBSTITUTION parameter to Previous or to a numeric constant will
1378automatically patch missing data points where possible."
1379To avoid this error, you can specify that Analysis Services automatically provide new
1380values to fill in the gaps by using any one of the following methods:
1381• Using an average value. The mean is calculated by using all valid values in the same
1382data series.
1383• Using the previous value. You can substitute previous values for multiple missing
1384cells, but you cannot fill starting values.
1385• Using a constant value that you supply.
13861. On the Mining Models tab, right-click the Forecasting column, and select Set
1387Algorithm Parameters.
13882. In the Algorithm Parameters dialog box, in the
1389MISSING_VALUE_SUBSTITUTION row, click the Value column, and type Mean.
1390Build the Model
1391To use the model, you must deploy it to a server, and process the model by running the
1392training data through the algorithm.
13931. On the Mining Model menu of SQL Server Data Tools, select Process Mining
1394To specify that gaps be filled by averaging values
1395To process the forecasting model
139657
1397Structure and All Models.
13982. At the warning asking whether you want to build and deploy the project, click
1399Yes.
14003. In the Process Mining Structure - Forecasting dialog box, click Run.
1401The Process Progress dialog box opens to display information about model
1402processing. Model processing may take some time.
14034. After processing is complete, click Close to exit the Process Progress dialog box.
14045. Click Close again to exit the Process Mining Structure - Forecasting dialog box.
1405Exploring the Forecasting Model (Intermediate Data Mining
1406Tutorial)
1407Now that you have built the forecasting mining model, you can explore the results by
1408using the Mining Model Viewer tab of Data Mining Designer. The Microsoft Time Series
1409Viewer contains two tabs: Charts and Model.
1410Additionally, you can use the Microsoft Generic Tree Viewer with all models. Each view
1411presents a slightly different picture of the information in the time series model.
1412• Charts Tab
1413• Model Tab
1414• Microsoft Generic Content Viewer
1415Charts Tab
1416The Charts tab of the Microsoft Time Series Viewer graphically shows you each of the
1417series, including historical data and predictions. Each line in the time series graph
1418represents a unique combination of product, region, and predictable attribute.
1419The legend on the right side of the viewer lists the time series that available, based on
1420the selections in the drop-down list. You can select and clear the check boxes in the
1421legend to control which time series displays in the graph.
1422You can also change the display options, such as the colors used for each time series, or
1423whether values are displayed at points in the chart.
14241. Click the Charts tab of the Mining Model Viewer tab, if it is not visible.
1425To select a time series
142658
14272. Click the drop-down list to the right of the chart view, and select all the check
1428boxes. Click .
1429The chart should now contain 24 different series lines.
14303. In the check boxes to the right of the chart, clear the boxes to temporarily hide
1431the lines for all series that are based on Amount.
1432Now, clear the check boxes related to the R750 and R250 bicycles.
1433The chart now contains just the following six series lines, so that can you more
1434easily compare trends for the M200 and T1000 bicycles.
1435• M200 Europe: Quantity
1436• M200 North America: Quantity
1437• M200 Pacific: Quantity
1438• T1000 Europe: Quantity
1439• T1000 North America: Quantity
1440• T1000 Pacific: Quantity
1441
1442The chart that is displayed in this viewer includes both historical and predicted data.
1443Predicted data is shaded to differentiate it from historical data. To make it easier to
1444compare different series, you can also change the colors associated with each line in the
1445graph. For more information, see
1446From the trend lines, you can see that total sales for all regions are generally increasing,
1447with a peak every 12 months in December. From the chart, you can also see that the data
1448for the T1000 bicycle starts much later than the data for the other product series. That is 
144959
1450because it is a newer product, but because this series is based on much less data, the
1451predictions might not be as accurate.
1452By default, five prediction steps are shown for each time series, displayed as dotted lines.
1453You can change this value to view more or fewer predictions. You can also graphically
1454view the standard deviation for the predictions by adding error bars to the chart.
14551. Try changing the value for Prediction Steps gradually, increasing it from 5 to 10,
1456and then back to 6.
1457When the historical data has large fluctuations, the fluctuations tend to be
1458repeated or even amplified as you increase the number of predictions. You
1459probably need to do some research at this point, to understand the cause of the
1460big increase in the historical data and then decide whether to accept these
1461results, seek some kind of correction in the source data, or apply some kind of
1462smoothing in the model.
14632. Select the Show Deviations check box.
1464This option displays the estimated error for each predicted value.
14653. Note the scale of the X-axis. The changes over both historical and predicted data
1466are always expressed as a percentage, but the actual values are adjusted
1467automatically to fit all values onto the graph. Therefore you need to be careful
1468when comparing models to not rely on visuals alone. To get the exact value, or
1469the percentage increase and value for predictions, pause the mouse over the
1470dotted line or solid lines, or click the lines to view the values in the Mining
1471Legend.
1472Tip: If the Mining Legend is not visible, switch to Model view, right-click any
1473node, and select Show Legend.
1474From looking at these trends, you are concerned about the lack of data for some of the
1475series, and wonder if you might get more reliable predictions by averaging sales by
1476model, or perhaps averaging sales by region. You will explore this approach in a later
1477lesson in this tutorial.
1478Back to Top
1479Model Tab
1480The Model tab of the Microsoft Time Series Viewer in Data Mining Designer lets you
1481view the forecasting model in the form of a tree graph.
1482First, notice that because your data describes two different measures (Amount and
1483Quantity) for sales of multiple product lines (T1000, etc.) in three different regions
1484(Europe, North America, and Pacific), the model that you built actually contains 24
1485different trees, each tree representing a model of the sales patterns for a different
1486combination of region, product, and predictable attribute.
1487To change prediction and display options in the Chart view
148860
1489You can choose which combination of product line, region, and sales metric you want to
1490view by selecting a series from the Tree dropdown list on the Model tab.
1491So what can you learn from viewing the model as a tree? As an example, let’s compare
1492two models, one that has several levels in the tree, and one that has a single node.
1493• When a tree graph contains a single node, it means the trend found in the model is
1494mostly homogenous over time. You can use this single node, labeled All, to view the
1495formula that describes the relationship between the input variables and the outcome.
1496• When a tree graph for a time series has multiple branches, it means the time series
1497that was detected is too complex to be represented as a single equation. Instead, the
1498tree graph might contain multiple branches, each branch labeled with the conditions
1499that caused the tree to split. When the tree splits, each branch represents a different
1500segment of time, inside which the trend can be described as a single equation.
1501For example, if you look at the chart graph and see a sudden jump in sales volume
1502starting sometime in September and continuing through a year-end holiday, you can
1503switch to the Model view to see the exact date where the trend changed. The
1504branches in the tree that represent “before September” and “after September” would
1505contain different formulas: one formula that mathematically describes the sales
1506trends up to the split, and another formula that describes sales trends for September
1507through the year-end holiday.
15081. In the Tree list on the Model tab of the viewer, select the T1000 Europe:
1509Amount series.
1510Click the node labeled All.
1511For an All node, the ToolTip that appears includes information such as, the
1512number of cases in the entire series, and time series equations derived from
1513analysis of the data.
15142. If the Mining Legend is not visible, right-click the node and select Show Legend.
1515The Mining Legend provides much the same information that is in the Tooltip. If
1516any of your independent variables are discrete, you will also see a histogram that
1517shows the distribution of variables in the node.
15183. Now select a different time series to view. Using the Tree list on the Model tab of
1519the viewer, select the M200 North America: Amount series.
1520The tree graph now contains an All node and two child nodes. By looking at the
1521labels on the child nodes, you can understand at what point the trend line
1522changed.
1523For each child node, the description in the Mining Legend also includes the
1524count of cases in each branch of the tree.
1525To explore the decision tree for a time series model
152661
1527The following list describes some additional features in the tree viewer:
1528• You can change the variable that is represented in the chart by using the
1529Background control. By default, nodes that are darker contain more cases, because
1530the value of Background is set to Population. To see just how many cases there are
1531in a node, pause the mouse over a node and view the ToolTip that appears, or click
1532the node and view the numbers in the Node Legend window.
1533• The regression formula for the node can also be viewed in the ToolTip, or by clicking
1534the node. If you have created a mixed model, you can see two formulas, one for
1535ARTXP (in the leaf nodes) and one for ARIMA (in the root node of the tree).
1536• The little diamonds are used in nodes that represent continuous numbers. The range
1537of the attributes is shown in the bar on which the diamond rests. The diamond is
1538centered on the mean for the node, and the width of the diamond represents the
1539variance of the attribute at that node.
1540Back to Top
1541(Optional) Generic Content Tree Viewer
1542In addition to the custom viewer for time series, Analysis Services provides the Microsoft
1543Generic Content Tree Viewer for use with all data mining models. This viewer provides
1544some advantages:
1545• Microsoft Time Series Viewer: This view merges the results of the two algorithms.
1546Although you can view each series separately, you cannot determine how the results
1547of each algorithm were combined. Also, in this view, the Tooltips and Mining Legend
1548show only the most important statistics.
1549• Generic Content Tree Viewer: Lets you browse and view all of the data series that
1550were used in the model at one time, and if you have created a mixed model, both the
1551ARIMA and ARTXP trees are displayed in the same graph.
1552You can use this viewer to get all the statistics from both algorithms, as well as
1553distributions of the values.
1554Recommended for expert users of data mining who want to know more about the
1555ARIMA and ARTXP analyses.
15561. In the Mining Model Viewer tab, select Microsoft Generic Content Tree
1557Viewer from the Viewer drop-down list.
15582. In the Node Caption pane, click the topmost (All) node.
15593. In the Node Details pane, view the value for ATTRIBUTE_NAME.
1560This value shows you which series, or combination of product and region, is
1561contained in this node. In the AdventureWorks example, the topmost node is for
1562the M200 Europe series.
15634. In the Node Caption pane, locate the first node that has child nodes.
1564To view details for a particular data series in the generic content viewer
156562
1566If a series node has children, the tree view that appears on the Model tab of the
1567Microsoft Time Series Viewer will also have a branching structure.
15685. Expand the node and click one of the child nodes.
1569The NODE_DESCRIPTION column of the schema contains the condition that
1570caused the tree to split.
15716. In the Node Caption pane, click the topmost ARIMA node, and expand the node
1572until all child nodes are visible.
15737. In the Node Details pane, view the value for ATTRIBUTE_NAME.
1574This value tells you which time series is contained in this node. The topmost node
1575in the ARIMA section should match the topmost node in the (All) section. In the
1576AdventureWorks example, this node contains the ARIMA analysis for the series,
1577M200 Europe.
1578Creating Time Series Predictions (Intermediate Data Mining
1579Tutorial)
1580In the previous tasks in this lesson, you created a time series model and explored the
1581results. By default, Analysis Services always creates a set of five (5) predictions for a time
1582series model and displays the predicted values as part of the forecasting chart. However,
1583you can also create forecasts by building Data Mining Extensions (DMX) prediction
1584queries.
1585In this task, you will create a prediction query that generates the same predictions that
1586you saw in the viewer. This task assumes that you have already completed the lessons in
1587the Basic Data Mining Tutorial and are familiar with how to use Prediction Query Builder.
1588You will now learn how to create queries specific to time series models.
1589Creating Time Series Predictions
1590Typically, the first step in creating a prediction query is to select a mining model and
1591input table. However, a time series model does not require additional input for a regular
1592prediction. Therefore, you do not need to specify a new source of data when making
1593predictions, unless you are adding data to the model or replacing the data. 
159463
1595For this lesson, you must specify the number of prediction steps. You can specify the
1596series name, to get a prediction for a particular combination of a product and a region.
15971. On the Mining Model Prediction tab of the Data Mining Designer, in the
1598Mining Model box, click Select Model.
15992. In the Select Mining Model dialog box, expand the Forecasting structure, select
1600the Forecasting model from the list, and then click OK.
16013. Ignore the Select Input Table(s) box.
1602Note
1603For a time series model, you do not need to specify a separate input
1604unless you are doing cross-prediction.
16054. In the Source column, in the grid on the Mining Model Prediction tab, click the
1606cell in the first empty row, and then select Forecasting mining model.
16075. In the Field column, select Model Region.
1608This action adds the series identifier to the prediction query to indicate the
1609combination of model and region to which the prediction applies.
16106. Click the next empty row in the Source column, and then select Prediction
1611Function.
16127. In the Field column, select PredictTimeSeries.
1613Note
1614You can also use the Predict function with time series models. However,
1615by default, the Predict function creates only one prediction for each series.
1616Therefore, to specify multiple prediction steps, you must use the
1617PredictTimeSeries function.
16188. In the Mining Model pane, select the mining model column, Amount. Drag
1619Amount to the Criteria/Arguments box for the PredictTimeSeries function that
1620you added earlier.
16219. Click the Criteria/Arguments box, and type a comma, followed by 5, after the
1622field name.
1623The text in the Criteria/Arguments box should now display the following:
1624[Forecasting].[Amount],5
162510. In the Alias column, type PredictAmount.
162611. Click the next empty row in the Source column, and then select Prediction
1627Function again.
162812. In the Field column, select PredictTimeSeries.
162913. In the Mining Model pane, select the column Quantity, and then drag it into the
1630To select a model and input table
163164
1632Criteria/Arguments box for the second PredictTimeSeries function.
163314. Click the Criteria/Arguments box, and type a comma, followed by 5, after the
1634field name.
1635The text in the Criteria/Arguments box should now display the following:
1636[Forecasting].[ Quantity],5
163715. In the Alias column, type PredictQuantity.
163816. Click Switch to query result view.
1639The results of the query are displayed in tabular format.
1640Remember that you created three different types of results in the query builder, one that
1641uses values from a column, and two that get predicted values from a prediction function.
1642Therefore, the results of the query contain three separate columns. The first column
1643contains the list of product and region combinations. The second and third columns
1644each contain a nested table of prediction results. Each nested table contains the time
1645step and predicted values, such as the following table:
1646Example results (amounts are truncated to two decimal places):
1647ModelRegion PredictAmount PredictQuantity
1648M200 Europe
1649$TIME Amount
16507/25/2008 99978.00
16518/25/2008 145575.07
16529/25/2008 116835.19
165310/25/2008 116537.38
165411/25/2008 107760.55
1655$TIME Quantity
16567/25/2008 52
16578/25/2008 67
16589/25/2008 58
165910/25/2008 57
166011/25/2008 54
1661M200 North America
1662$TIME Amount
16637/25/2008 348533.93
16648/25/2008 340097.98
16659/25/2008 257986.19
166610/25/2008 374658.24
1667$TIME Quantity
16687/25/2008 272
16698/25/2008 152
16709/25/2008 250
167110/25/2008 181
167265
1673ModelRegion PredictAmount PredictQuantity
167411/25/2008 379241.44 11/25/2008 290
1675The dates that are used in the sample database have changed for this release. If
1676you are using an earlier version of the sample data, you might see different
1677results.
1678Saving the Prediction Results
1679You have several different options for using the prediction results. You can flatten the
1680results, copy the data from the Results view, and paste it into an Excel worksheet or other
1681file.
1682To simplify the process of saving results, Data Mining Designer also provides the ability
1683to save the data to a data source view. The functionality for saving results to a data
1684source view is available only in SQL Server Data Tools (SSDT). The results can only be
1685stored in a flattened format.
16861. In the Prediction Query Builder, click Switch to query design view.
1687The view changes to allow manual editing of the DMX query text.
16882. Type the FLATTENED keyword after the SELECT keyword. The complete query
1689text should be as follows:
1690SELECT FLATTENED
1691 [Forecasting].[Model Region],
1692 (PredictTimeSeries([Forecasting].[Amount],5)) as
1693[PredictAmount],
1694 (PredictTimeSeries([Forecasting].[Quantity],5)) as
1695[PredictQuantity]
1696FROM
1697 [Forecasting]
16983. Optionally, you can type a clause to restrict the results, such as the following
1699example:
1700SELECT FLATTENED
1701 [Forecasting].[Model Region],
1702 (PredictTimeSeries([Forecasting].[Amount],5)) as
1703Warning
1704To flatten the results in the Results pane
170566
1706[PredictAmount],
1707 (PredictTimeSeries([Forecasting].[Quantity],5)) as
1708[PredictQuantity]
1709FROM
1710 [Forecasting]
1711WHERE [Forecasting].[Model Region] = 'M200 North America'
1712OR [Forecasting].[Model Region] = 'M200 Europe'
17134. Click Switch to query result view.
17141. Click Save query results.
17152. In the Save Data Mining Query Result dialog box, for Data Source, select .
1716You can also create a data source if you want to save the data to a different
1717relational database.
17183. In the Table Name column, type a new temporary table name, such as Test
1719Predictions.
17204. Click Save.
1721Note
1722To view the table that you created, create a connection to the database
1723engine of the instance where you saved the data, and create a query.
1724Conclusion
1725You have learned how to build a basic time series model, interpret the forecasts, and
1726create predictions.
1727The remaining tasks in this tutorial are optional, and describe advanced time series
1728predictions. If you decide to go on, you will learn how to add new data to your model
1729and create predictions on the extended series. You will also learn how to perform crossprediction,
1730by using the trend in the model but replacing the data with a new series of
1731data.
1732To export prediction query results
173367
1734Advanced Time Series Predictions (Intermediate Data Mining
1735Tutorial)
1736You saw from exploring the forecasting model that although sales in most of the regions
1737follow a similar pattern, some regions and some models, such as the M200 model in the
1738Pacific region, exhibit very different trends. This does not surprise you, as you know that
1739differences among regions are common and can be caused by many factors, including
1740marketing promotions, inaccurate reporting, or geopolitical events.
1741However, your users are asking for a model that can be applied worldwide. Therefore, to
1742minimize the effect of individual factors on projections, you decide to build a model that
1743is based on aggregated measures of worldwide sales. You can then use this model to
1744make predictions for each individual region.
1745In this task, you will build all the data sources that you need to perform the advanced
1746prediction tasks. You will create two data source views for use as inputs to the prediction
1747query, and one data source view to use in building a new model.
1748Steps
17491. Prepare the extended sales data (for prediction)
17502. Prepare the aggregated data (for building the model)
17513. Prepare the series data (for cross-prediction)
17524. Predict using EXTEND
17535. Create the cross-prediction model
17546. Predict using REPLACE
17557. Review the new predictions
1756Creating the New Extended Sales Data
1757To update your sales data, you will need to get the latest sales figures. Of particular
1758interest are the data just in from the Pacific region, which launched a regional sales
1759promotion to call attention to the new stores and raise awareness of their products.
1760For this scenario, we’ll assume that the data has been imported from an Excel workbook
1761that contains just three months of new data for a couple of regions. You’ll create a table
1762for the data using a Transact-SQL script, and then define a data source view to use for
1763prediction.
17641. In a Transact-SQL query window, execute the following statement to add the
1765sales data to the AdventureWorksDW database (or any other database).
1766USE [database name];
1767GO
1768IF OBJECT_ID ([dbo].[NewSalesData]) IS NOT NULL
1769Create the table with new sales data
177068
1771 DROP TABLE [dbo].[NewSalesData];
1772GO
1773CREATE TABLE [dbo].[NewSalesData](
1774[Series] [nvarchar](255) NULL,
1775[NewDate] [datetime] NULL,
1776[NewQty] [float] NULL,
1777[NewAmount] [money] NULL
1778) ON [PRIMARY]
1779GO
17802. Insert the new values using the following script.
1781INSERT INTO [NewSalesData]
1782(Series,NewDate,NewQty,NewAmount)
1783VALUES('T1000 Pacific', '7/25/08', 55, '$130,170.22'),
1784('T1000 Pacific', '8/25/08', 50, '$114,435.36 '),
1785('T1000 Pacific', '9/25/08', 50, '$117,296.24 '),
1786('T1000 Europe', '7/25/08', 37, '$88,210.00 '),
1787('T1000 Europe', '8/25/08', 41, '$97,746.00 '),
1788('T1000 Europe', '9/25/08', 37, '$88,210.00 '),
1789('T1000 North America', '7/25/08', 69, '$164,500.00 '),
1790('T1000 North America', '8/25/08', 66, '$157,348.00 '),
1791('T1000 North America', '9/25/08', 58, '$138,276.00 '),
1792('M200 Pacific', '7/25/08', 65, '$149,824.35'),
1793('M200 Pacific', '8/25/08', 54, '$124,619.46'),
1794('M200 Pacific', '9/25/08', 61, '$141,143.39'),
1795('M200 Europe', '7/25/08', 75, '$173,026.00'),
1796('M200 Europe', '8/25/08', 76, '$175,212.00'),
1797('M200 Europe', '9/25/08', 84, '$193,731.00'),
1798('M200 North America', '7/25/08', 94, '$216,916.00'),
1799('M200 North America', '8/25/08', 94, '$216,891.00'),
1800('M200 North America', '9/25/08', 91,'$209,943.00');
1801Warning
1802The quotation marks are used with the currency values to prevent problems with the
1803comma separator and the currency symbol. You could also pass in the currency 
180469
1805values in this format: 130170.22
1806Note that the dates used in the sample database have changed for this release. If you
1807are using an earlier edition of AdventureWorks, you might need to adjust the
1808inserted dates accordingly.
18091. In Solution Explorer, right-click Data Source Views, and then select New Data
1810Source View.
18112. In the Data Source View wizard, make the following selections:
1812Data Source: Adventure Works DW Multidimensional 2012
1813Select Tables and Views: Select the table that you just created, NewSalesData.
18143. Click Finish.
18154. In the Data Source View design surface, right-click NewSalesData, and then select
1816Explore Data to verify the data.
1817You will use this data for prediction only, so it does not matter that the data is
1818incomplete.
1819Creating the Data for the Cross-Prediction Model
1820The data that was used in the original forecasting model was already grouped somewhat
1821by the view vTimeSeries, which collapsed several bike models into a smaller number of
1822categories, and merged results from individual countries into regions. To create a model
1823that can be used for world-wide projections, you will create some additional simple
1824aggregations directly in the Data Source View Designer. The new data source view will
1825contain just a sum and an average of the sales of all products for all regions.
1826After you have created the data source used for the model, you must create a new data
1827source view to use for prediction. For example, if you want to predict sales for Europe
1828using the new worldwide model, you must feed in data for the Europe region only. So
1829you will set up a new data source view that filters the original data, and change the filter
1830condition for each set of prediction queries.
18311. In Solution Explorer, right-click Data Source Views, and then select New Data
1832Source View.
18332. On the welcome page of the wizard, click Next.
18343. On the Select Data Source page, select Adventure Works DW
1835Multidimensional 2012 , and then click Next.
18364. In the page, Select Tables and Views, do not add any tables—just click Next.
18375. On the page, Completing the Wizard, type the name AllRegions, and then click
1838Create a data source view using the new sales data
1839Warning
1840To create the model data using a custom data source view
184170
1842Finish.
18436. Next, right-click the blank data source view design surface, and then select New
1844Named Query.
18457. In the Create Named Query dialog box, for Name, type AllRegions, and for
1846Description, type Sum and average of sales for all models and regions.
18478. In the SQL text pane, type the following statement and then click OK:
1848SELECT ReportingDate,
1849SUM([Quantity]) as SumQty, AVG([Quantity]) as AvgQty,
1850SUM([Amount]) AS SumAmt, AVG([Amount]) AS AvgAmt,
1851'All Regions' as [Region]
1852FROM dbo.vTimeSeries
1853GROUP BY ReportingDate
18549. Right-click the AllRegions table, and then select Explore Data.
18551. In Solution Explorer, right-click Data Source Views, and then select New Data
1856Source View.
18572. In the Data Source View wizard, make the following selections:
1858Data Source: Adventure Works DW Multidimensional 2012
1859Select Tables and Views: Do not select any tables
1860Name: T1000 Pacific Region
18613. Click Finish.
18624. Right-click the empty design surface for T1000 Pacific Region.dsv, and then
1863select New Named Query.
1864The Create Named Query dialog box appears. Retype the name, and then add
1865the following description:
1866Name: T1000 Pacific Region
1867Description: Filter vTimeSeries by region and model
18685. In the text pane, type the following query, and then click OK:
1869SELECT ReportingDate, ModelRegion, Quantity, Amount
1870FROM dbo.vTimeSeries
1871WHERE (ModelRegion = N'T1000 Pacific')
1872Note
1873Since you will need to create predictions for each series separately, you
1874might want to copy the query text and save it to a text file so that you can
1875re-use it for the other data series.
1876To create the series data for cross-prediction
187771
18786. In the Data Source View design surface, right-click T1000 Pacific, and then select
1879Explore Data to verify that the data is filtered correctly.
1880You will use this data as the input to the model when creating cross-prediction
1881queries.
1882Time Series Predictions using Updated Data (Intermediate Data Mining
1883Tutorial)
1884Creating Predictions using the Extended Sales Data
1885In this lesson, you will create a prediction query that adds the new sales data to the
1886model. By extending the model with new data, you can get up-to-date predictions that
1887include the newest data points.
1888Creating time series predictions that use new data is easy: you simply add the parameter
1889EXTEND_MODEL_CASES to the PredictTimeSeries (DMX) function, specify the source of
1890the new data, and specify how many predictions you want to get.
1891The parameter EXTEND_MODEL_CASES is optional; by default the model is
1892extended any time that you create a time series prediction query by joining new
1893data as inputs.
18941. If the model is not already open, double-click the Forecasting structure, and in
1895Data Mining Designer, click the Mining Model Prediction tab.
18962. In the Mining Model pane, the model Forecasting should already be selected. If
1897it is not selected, click Select Model, and then select the model, Forecasting.
18983. In the Select Input Table(s) pane, click Select Case Table.
18994. In the Select Table dialog box, select the data source, Adventure Works DW
1900Multidimensional 2012 .
1901From the list of data source views, select NewSalesData and then click OK.
19025. Right-click the surface of the design area and select Modify Connections.
19036. Using the Modify Mapping dialog box, map the columns in the model to the
1904columns in the external data as follows:
1905Warning
1906To build the prediction query and add new data
190772
1908• Map the ReportingDate column in the mining model to the NewDate column
1909in the input data.
1910• Map the Amount column in the mining model to the NewAmount column in
1911the input data.
1912• Map the Quantity column in the mining model to the NewQty column in the
1913input data.
1914• Map the ModelRegion column in the mining model to the Series column in
1915the input data.
19167. Now you will build the prediction query.
1917First, add a column to the prediction query to output the series the prediction
1918applies to.
1919a. In the grid, click the first empty row, under Source, and then select
1920Forecasting.
1921b. In the Field column, select Model Region and for Alias, type Model Region.
19228. Next, add and edit the prediction function.
1923a. Click an empty row, and under Source, select Prediction Function.
1924b. For Field, select PredictTimeSeries.
1925c. For Alias, type Predicted Values.
1926d. Drag the field Quantity from the Mining Model pane into the
1927Criteria/Argument column.
1928e. In the Criteria/Argument column, after the field name, type the following
1929text: 5,EXTEND_MODEL_CASES
1930The complete text of the Criteria/Argument text box should be as follows:
1931[Forecasting].[Quantity],5,EXTEND_MODEL_CASES
19329. Click Results and review the results.
1933The predictions begin in July (the first time slice after the end of the original data)
1934and end at November (the fifth time slice after the end of the original data).
1935You can see that to use this type of prediction query effectively, you need to know when
1936the old data ends, as well as how many time slices there are in the new data.
1937For example, in this model, the original data series ended in June, and the data is for the
1938months of July, August, and September.
1939Predictions that use EXTEND_MODEL_CASES always begin at the end of the original data
1940series. Therefore, if you want to get only the predictions for the unknown months, you
1941need to specify the starting point and the end point for prediction. Both values are
1942specified as a number of time slices starting at the end of the old data.
1943The following procedure demonstrates how to do this.
1944Procedures
194573
19461. In Prediction Query Builder, click Query to switch to DMX view.
19472. Locate the DMX statement that contains the PredictTimeSeries function and
1948change it as follows:
1949PredictTimeSeries([Forecasting
195012].[Quantity],4,6,EXTEND_MODEL_CASES)
19513. Click Results and review the results.
1952Now the predictions begin at October (the fourth time slice, counting from the
1953end of the original data) and end at December (the sixth time slice, counting from
1954the end of the original data).
1955Time Series Predictions using Replacement Data (Intermediate Data Mining
1956Tutorial)
1957In this task, you will build a new model based on worldwide sales data. Then, you will
1958create a prediction query that applies the worldwide sales model to one of the individual
1959regions.
1960Building a General Model
1961Remember that your analysis of the results of the original mining model revealed big
1962differences between regions and between product lines. For example, sales in North
1963America were strong for the M200 model, while sales of the T1000 model did not do as
1964well. However, the analysis is complicated by the fact that some series didn’t have much
1965data, or data started at a different point in time. Some data was also missing.
1966Change the start and end points of the predictions
196774
1968
1969To address some of the data quality issues, you decide to merge the data from sales
1970around the world, and use that set of general sales trends to build a model that can be
1971applied to predict future sales in any region.
1972When you create predictions, you will use the pattern that is generated by training on
1973worldwide sales data, but you will replace the historical data points with the sales data
1974for each individual region. That way, the shape of the trend is preserved but the
1975predicted values are aligned with the historical sales figures for each region and model.
1976Performing Cross-Prediction with a Time Series Model
1977The process of using data from one series to predict trends in another series is called
1978cross-prediction. You can use cross-prediction in many scenarios: for example, you might
1979decide that television sales are a good predictor of overall economic activity, and apply a
1980model trained on television sales to general economic data.
1981In SQL Server Data Mining, you perform cross-prediction by using the parameter
1982REPLACE_MODEL_CASES within the arguments to the function, PredictTimeSeries (DMX).
1983In the next task, you will learn how to use REPLACE_MODEL_CASES. You will use the
1984merged world sales data to build a model, and then create a prediction query that maps
1985the general model to the replacement data.
1986It is assumed that you are familiar with how to build data mining models by now, and so
1987the instructions for building the model has been simplified.
19881. In Solution Explorer, right-click Mining Structures, and then select New
1989Mining Structure to start the Data Mining Wizard.
1990To build a mining structure and mining model using the aggregated data
199175
19922. In the Data Mining Wizard, make the following selections:
1993• Algorithm: Microsoft Time Series
1994• Use the data source that you built earlier in this advanced lesson as the
1995source for the model. See Advanced Time Series Prediction.
1996Data source view: AllRegions
1997• Choose the following columns for the series key and time key:
1998Key time: ReportingDate
1999Key: Region
2000• Choose the following columns for Input and Predict:
2001SumQty
2002SumAmt
2003AvgAmt
2004AvgQty
2005• For Mining structure name, type: All Regions
2006• For Mining model name, type: All Regions
20073. Process the new structure and the new model.
20081. If the model is not already open, double-click the AllRegions structure, and in
2009Data Mining Designer, click the Mining Model Prediction tab.
20102. In the Mining Model pane, the model AllRegions should already be selected. If it
2011is not selected, click Select Model, and then select the model, AllRegions.
20123. In the Select Input Table(s) pane, click Select Case Table.
20134. In the Select Table dialog box, change the data source to T1000 Pacific Region,
2014and then click OK.
20155. Right-click the join line between the mining model and the input data and select
2016Modify Connections. Map the data in the data source view to the model as
2017follows:
2018a. Verify that the ReportingDate column in the mining model is mapped to the
2019ReportingDate column in the input data.
2020b. In the Modify Mapping dialog box, in the row for the model column AvgQty,
2021click under Table Column and then select T1000 Pacific.Quantity. Click OK.
2022This step maps the column you created in the model for predicting average
2023quantity to the actual data from the T1000 series for sales quantity.
2024c. Do not map the column Region in the model to any input column.
2025Because the model aggregated the data across all series, there is no match
2026for the series values such as T1000 Pacific, and an error is raised when the
2027To build the prediction query and map the replacement data
202876
2029prediction query runs.
20306. Now you will build the prediction query.
2031First, add a column to the results that outputs the AllRegions label from the
2032model together with the predictions. This way you know that the results were
2033based on the general model.
2034a. In the grid, click the first empty row, under Source, and then select AllRegions
2035mining model.
2036b. For Field, select Region.
2037c. For Alias, type Model Used.
20387. Next, add another label to the results, so that you can see which series the
2039prediction is for.
2040a. Click an empty row, and under Source, select Custom Expression.
2041b. In the Alias column, type ModelRegion.
2042c. In the Criteria/Argument column, type 'T1000 Pacific'.
20438. Now you will set up the cross-prediction function.
2044a. Click an empty row, and under Source, select Prediction Function.
2045b. In the Field column, select PredictTimeSeries.
2046c. For Alias, type Predicted Values.
2047d. Drag the field AvgQty from the Mining Model pane into the
2048Criteria/Argument column by using the drag and drop operation.
2049e. In the Criteria/Argument column, after the field name, type the following
2050text: ,5, REPLACE_MODEL_CASES
2051The complete text of the Criteria/Argument text box should be as follows:
2052[AllRegions].[AvgQty],5,REPLACE_MODEL_CASES
20539. Click Results.
2054Creating the Cross-Prediction Query in DMX
2055You might have noticed a problem with cross-prediction: namely, that to apply the
2056general model to a different data series, such as the T1000 product model in the North
2057America region, you must create a different query for each series, so that you can map
2058the each set of inputs to the model.
2059However, rather than building the query in the designer, you can switch to DMX view
2060and edit the DMX statement that you created. For example, the following DMX
2061statement represents the query that you just built:
2062SELECT
2063 ([All Regions].[Region]) as [Model Used],
2064 ('T-1000 Pacific') as [ModelRegion],
206577
2066 (PredictTimeSeries([All Regions].[Avg Qty],5,
2067REPLACE_MODEL_CASES)) as [Predicted Quantity]
2068 FROM [All Regions]
2069PREDICTION JOIN
2070 OPENQUERY([Adventure Works DW2003R2], 'SELECT [ReportingDate] FROM
2071 (
2072 SELECT ReportingDate, ModelRegion, Quantity, Amount
2073 FROM dbo.vTimeSeries
2074 WHERE (ModelRegion = N''T1000 Pacific'')
2075 ) as [T1000 Pacific] ')
2076 AS t
2077ON
2078[All Regions].[Reporting Date] = t.[ReportingDate]
2079AND
2080[All Regions].[Avg Qty] = t.[Quantity]
2081To apply this to a different model, you simply edit the query statement to replace the
2082filter condition and to update the labels associated with each result.
2083For example, if you change the filter conditions and column labels by replacing 'Pacific'
2084with 'North America', you will get predictions for the T1000 product in North America,
2085based on the patterns in the general model.
2086Comparing Predictions for Forecasting Models (Intermediate Data Mining
2087Tutorial)
2088In the previous steps of this tutorial, you created multiple time series models:
2089• Predictions for each combination of region and model, based only on data for the
2090individual model and region.
2091• Predictions for each region, based on updated data.
2092• Predictions for all models on a worldwide basis, based on aggregated data.
2093• Predictions for the M200 model in the North America region, based on the
2094aggregated model.
209578
2096To summarize the features for time series predictions, you will review the changes to see
2097how the use of the options to extend or replace data affected forecasting results.
2098EXTEND_MODEL_CASES
2099REPLACE_MODEL_CASES
2100Comparing the Original Results with Results after Adding Data
2101Let’s look at the data for just the M200 product line in the Pacific region, to see how
2102updating the model with new data affects the results. Remember that the original data
2103series ended in June 2004, and we obtained new data for July, August, and September.
2104• The first column shows the new data that was added.
2105• The second column shows the forecast for July and later based on the original data
2106series.
2107• The third column shows the forecast based on the extended data.
2108M200 Pacific Updated real sales data Forecast before data
2109was added
2110Extended prediction
21117-25-2008 65 32 65
21128-25-2008 54 37 54
21139-25-2008 61 32 61
211410-25-2008 No data 36 32
211511-25-2008 No data 31 41
211612-25-2008 No data 34 32
2117You will note that the forecasts using the extended data (shown here in bold) repeat the
2118real data points exactly. The repetition is by design. As long as there are real data points
2119to use, the prediction query will return the actual values, and output new prediction
2120values only after the new actual data points have been used up.
2121In general, the algorithm weights the changes in the new data more strongly than data
2122from the beginning of the model data. However, in this case, the new sales figures
2123represent an increase of only 20-30 percent over the previous period, so there only was a
2124slight uptick in projected sales, after which the sales projections drop again, more in line
2125with the trend in the months before the new data.
2126Comparing the Original and Cross-Prediction Results
2127Remember that the original mining model revealed big differences between regions and
2128between product lines. For example, sales for the M200 model were very strong, while
2129sales for the T1000 model were fairly low across all regions. Moreover, some series didn’t
2130have much data. Series were ragged, meaning they didn’t have the same starting point.
213179
2132
2133So how did the predictions change when you made your projections based on the
2134general model, which was based on world-wide sales, rather than the original data sets?
2135To assure yourself that you have not lost any information or skewed the predictions, you
2136can save the results to a table, join the table of predictions to the table of historical data,
2137and then graph the two sets of historical data and predictions.
2138The following diagram is based on just one product line, the M200. The graph compares
2139the predictions from the initial mining model against the predictions using the
2140aggregated mining model.
2141From this diagram, you can see that the aggregated mining model preserves the overall
2142range and trends in values while minimizing the fluctuations in the individual data series.
2143Conclusion
2144You have learned how to create and to customize a time series model that can be used
2145for forecasting.
2146You have learned to update your time series models without having to reprocess them,
2147by adding new data and creating predictions using the parameter,
2148EXTEND_MODEL_CASES.
2149You have learned to create models that can be used for cross-prediction, by using the
2150REPLACE_MODEL_CASES parameter and applying the model to a different data series.
2151Lesson 3: Building a Market Basket Scenario
2152(Intermediate Data Mining Tutorial)
2153The marketing department of Adventure Works Cycles wants to improve the company
2154Web site to promote cross-selling. As part of the site update, they would like the ability
2155to predict products that a customer might want to purchase, based on the other
2156products that are already in the customer's online shopping basket. The marketing
2157department also wants to understand customer purchasing behavior better, so that they
2158can design the Web site so that the items that tend to be purchased together appear
2159together. They have learned that data mining is especially useful for this kind of market
2160basket analysis and have asked you to develop a data mining model.
2161After you complete the tasks in this lesson, you will have a mining model that shows
2162groups of items from historical customer transactions. Additionally, you can use the
2163mining model to predict additional items that a customer may want to purchase.
2164To complete the tasks in this lesson, you will use the solution and data source that you
2165created in the first lesson of the Intermediate Data Mining Tutorial (Analysis Services -
2166Data Mining). You will modify this solution by adding a data source view that contains
2167tables about the customer, including a nested table of customer purchases. You will
2168then build a mining model that uses the Microsoft Association Rules algorithm, which is
2169suited to market basket scenarios.
2170Adding a Data Source View with Nested Tables (Intermediate
2171Data Mining Tutorial)
2172To create a market basket model, you must use a data source view that supports
2173associative data. This data source view will also be used for the sequence clustering
2174scenario.
2175This data source view is different from others that you may have worked with because it
2176contains a nested table. A nested table is a table that contains multiple rows of
2177information about a single row in the case table. For example, if your model analyzes the
2178purchasing behavior of customers, you would typically use a table that has a unique row
2179for each customer as the case table. However, each customer might make multiple
2180purchases, and you might want to analyze the sequence of purchases, or products that
2181are frequently purchased together. To logically represent these purchases in your model,
2182you add another table to the data source view that lists the purchases for each customer.
2183This nested purchases table is related to the customer table by a many-to-one
2184relationship. The nested table might contain many rows for each customer, each row
2185containing a single product that was purchased, perhaps with additional information
2186about the order that the purchases were made, the price at the time of the order, or any
2187promotions that applied. You can use the information in the nested table as inputs to the
2188model, or as the predictable attribute.
2189In this lesson, you do the following tasks:
219082
2191• You add a data source view to the Adventure Works DW Multidimensional
21922012 data source.
2193• You add the case and nested tables to this view.
2194• You specify the many-to-one relationship between the case and nested table.
2195. It is important that you follow the described procedure exactly, to correctly
2196specify the relationship between the case table and the nested table and to
2197avoid errors when you process the model.
2198• You define how the columns of data are used in the model.
2199For more information about working with case and nested tables, and how to choose a
2200nested table key, see Nested Tables (Analysis Services - Data Mining).
2201Procedures
22021. In Solution Explorer, right-click Data Source Views, and then select New Data
2203Source View.
2204The Data Source View Wizard opens.
22052. On the Welcome to the Data Source View Wizard page, click Next.
22063. On the Select a Data Source page, under Relational data sources, select the
2207Adventure Works DW Multidimensional 2012 data source that you created in
2208the Basic Data Mining Tutorial. Click Next.
22094. On the Select Tables and Views page, select the following tables, and then click
2210the right arrow to include them in the new data source view:
2211• vAssocSeqOrders
2212• vAssocSeqLineItems
22135. Click Next.
22146. On the Completing the Wizard page, by default the data source view is named
2215Adventure Works DW Multidimensional 2012 . Change the name to Orders, and
2216then click Finish.
2217Data Source View Designer opens and the Orders data source view appears.
22181. In Data Source View Designer, position the two tables so that the tables are
2219aligned horizontally, with the vAssocSeqLineItems table on the left side and the
2220vAssocSeqOrders table on the right side.
22212. Select the OrderNumber column in the vAssocSeqLineItems table.
22223. Drag the column to the vAssocSeqOrders table, and put it on the OrderNumber
2223Note
2224To add a data source view
2225To create a relationship between tables
222683
2227column.
2228Important
2229Make sure to drag the OrderNumber column from the
2230vAssocSeqLineItems nested table, which represents the many side of the
2231join, to the vAssocSeqOrders case table, which represents the one side of
2232the join.
2233A new many-to-one relationship now exists between the vAssocSeqLineItems and
2234vAssocSeqOrders tables. If you have joined the tables correctly, the data source
2235view should appear as follows:
2236Creating a Market Basket Structure and Model (Intermediate
2237Data Mining Tutorial)
2238Now that you have created a data source view, you will use the Data Mining Wizard to
2239create a new mining structure. In this task, you will create a mining structure and a
2240mining model that is based on the Microsoft Association algorithm.
2241If you encounter an error stating that vAssocSeqLineItems cannot be used as a
2242nested table, return to the previous task in the lesson, and be sure to create the
2243many-to-one join by dragging from the vAssocSeqLineItems table (the many
2244side) to the vAssocSeqOrders table (the one side). You can also edit the
2245relationship between the tables by right-clicking the join line.
2246Procedures
22471. In Solution Explorer in SQL Server Data Tools (SSDT), right-click Mining
2248Structures and select New Mining Structure to open the Data Mining Wizard.
2249Note
2250To create an association mining structure
225184
22522. On the Welcome to the Data Mining Wizard page, click Next.
22533. On the Select the Definition Method page, verify that From existing relational
2254database or data warehouse is selected, and then click Next.
22554. On the Create the Data Mining Structure page, under Which data mining
2256technique do you want to use?, select Microsoft Association Rules from the
2257list, and then click Next. The Select Data Source View page appears.
22585. Select Orders under Available data source views, and then click Next.
22596. On the Specify Table Types page, in the row for the vAssocSeqLineItems table,
2260select the Nested check box, and in the row for the nested table
2261vAssocSeqOrders, select the Case check box. Click Next.
22627. On the Specify the Training Data page, clear any boxes that might be checked.
2263Set the key for the case table, vAssocSeqOrders, by selecting the Key check box
2264next to OrderNumber.
2265Because the purpose of the market basket analysis is to determine which
2266products are included in a single transaction, you do not have to use the
2267CustomerKey field.
22688. Set the key for the nested table, vAssocSeqLineItems, by selecting the Key check
2269box next to Model. The Input check box is also automatically selected when you
2270do this. Select the Predictable check box for Model as well.
2271In a market basket model, you do not care about the sequence of products in the
2272shopping basket, and therefore you should not include LineNumber as a key for
2273the nested table. You would use LineNumber as a key only in a model where the
2274sequence is important. You will create a model that uses the Microsoft Sequence
2275Clustering algorithm in Lesson 4.
22769. Select the check box to the left of IncomeGroup and Region,but do not make any
2277other selections. Checking the leftmost column adds the columns to the structure
2278for later reference, but the columns will not be used in the model. Your selections
2279should look like the following:
228085
228110. Click Next.
228211. On the Specify Columns' Content and Data Type page, review the selections,
2283which should be as shown in the following table, and then click Next.
2284Columns Content Type Data Type
2285IncomeGroup Discrete Text
2286Order Number Key Text
2287Region Discrete Text
2288vAssocSeqLineItems
2289Model Key Text
229012. On the Create testing set page, the default value for the option Percentage of
2291data for testing is 30 percent. Change this to 0. Click Next.
229286
2293Note
2294Analysis Services provides different charts for measuring model accuracy.
2295However, some accuracy chart types, such as the lift chart and crossvalidation
2296report, are designed for classification and estimation. They are
2297not supported for associative prediction.
229813. On the Completing the Wizard page, in Mining structure name, type
2299Association.
230014. In Mining model name, type Association.
230115. Select the option Allow drill through, and then click Finish.
2302Data Mining Designer opens to display the Association mining structure that you
2303just created.
2304Modifying and Processing the Market Basket Model
2305(Intermediate Data Mining Tutorial)
2306Before you process the association mining model that you created, you must change the
2307default values of two of the parameters: Support and Probability.
2308• Support defines the percentage of cases in which a rule must exist before it is
2309considered valid. You will specify that a rule must be found in at least 1 percent of
2310cases.
2311• Probability defines how likely an association must be before it is considered valid.
2312You will consider any association with a probability of at least 10 percent.
2313For more information about the effects of increasing or decreasing support and
2314probability, see
2315After you have defined the structure and parameters for the Association mining model,
2316you will process the model.
2317Procedures
23181. Open the Mining Models tab of Data Mining Designer.
23192. Right-click the Association column in the grid in the designer and select Set
2320Algorithm Parameters to open the Algorithm Parameters dialog box.
2321To adjust the parameters of the Association model
232287
23233. In the Value column of the Algorithm Parameters dialog box, set the following
2324parameters:
2325MINIMUM_PROBABILITY = 0.1
2326MINIMUM_SUPPORT = 0.01
23274. Click .
23281. On the Mining Model menu of SQL Server Data Tools (SSDT), select Process
2329Mining Structure and All Models.
23302. At the warning asking whether you want to build and deploy the project, click
2331Yes.
2332The Process Mining Structure - Association dialog box opens.
23333. Click Run.
2334The Process Progress dialog box opens to display information about model
2335processing. Processing of the new structure and model might take some time.
23364. After processing is complete, click Close to exit the Process Progress dialog box.
23375. Click Close again to exit the Process Mining Structure - Association dialog box.
2338Exploring the Market Basket Models (Intermediate Data Mining
2339Tutorial)
2340Now that you have built the Association model, you can explore it by using the
2341Microsoft Association Viewer in the Mining Model Viewer tab of Data Mining Designer.
2342This tutorial walks you through using the viewer to explore relationships between
2343items. The viewer helps you see at a glance which products tend to appear together, and
2344get a general idea of the emerging patterns.
2345The Microsoft Association Viewer contains three tabs: Rules, Itemsets, and Dependency
2346Network. Because each tab reveals a slightly different view of the data, when you are
2347exploring a model, you will typically switch back and forth between the different panes
2348several times as you pursue insights.
2349• Dependency Network tab
2350• Itemsets tab
2351• Rules tab
2352• Generic Content View
2353To process the mining model
235488
2355For this tutorial, you will start on the Dependency Network tab, and then use the Rules
2356tab and Itemsets tab to deepen your understanding of the relationships revealed in the
2357viewer. You will also use the Microsoft Generic Content Tree Viewer to retrieve
2358detailed statistics for individual rules or itemsets.
2359Dependency Network Tab
2360With the Dependency Network tab, you can investigate the interaction of the different
2361items in the model. Each node in the viewer represents an item, while the lines between
2362them represent rules. By selecting a node, you can see which other nodes predict the
2363selected item, or which items the current item predicts. In some cases, there is a two-way
2364association between items, meaning that they often appear in the same transaction. You
2365can refer to the color legend at the bottom of the tab to determine the direction of the
2366association.
2367A line connecting two items means that these items are likely to appear in a transaction
2368together. In other words, customers are likely to buy these items together. The slider is
2369associated with the probability of the rule. Move the slider up or down to filter out weak
2370associations, meaning rules with low probability.
2371The dependency network graph shows pairwise rules, which can be represented logically
2372as A->B, meaning if Product A is purchased, then Product B is likely. The graph cannot
2373show rules of the type AB->C. If you move the slider to show all rules but still do not see
2374any lines in the graph, it means that there were no pairwise rules that met the criteria of
2375the algorithm parameters.
23761. In Solution Explorer, double-click the Association structure.
23772. In Data Mining Designer, click the Mining Model Viewer tab.
23783. Select Association from the list of mining models in the Mining Model
2379dropdown list.
23801. In the Mining Model Viewer tab, click the Dependency Network tab.
23812. Click Zoom In several times, until you can easily view the labels for each node.
2382By default, the graph displays with all nodes visible. In a complex model, there
2383may be many nodes, making each node quite small.
23843. Click the + sign in the lower right-hand corner of the viewer and hold down the
2385mouse button to pan around the graph.
23864. On the left side of the viewer, drag the slider down, moving it from All Links (the
2387default) to the bottom of the slider control.
2388To open the Association mode in the Microsoft Assocaition Rules Viewer
2389To navigate the dependency graph and locate specific nodes
239089
23915. The viewer updates the graph to now show only the strongest association,
2392between the Touring Tire and Touring Tire Tube items.
23936. Click the node labeled Touring Tire Tube = Existing.
2394The graph is updated to highlight only items that are strongly related to this
2395item. Note the direction of the arrow between the two items.
23967. On the left side of the viewer, drag the slider up again, moving it from the
2397bottom to around the middle.
2398Note the changes in the arrow that connects the two items.
23998. Select Show attribute name only from the dropdown list at the top of the
2400Dependency Network pane.
2401The text labels in the graph are updated to show only the model name.
2402Back to Top
2403Itemsets Tab
2404Next, you will learn more about the rules and itemsets generated by the model for the
2405Touring Tire and Touring Tire Tube products. The Itemsets tab displays three important
2406pieces of information that relate to the itemsets that the Microsoft Association algorithm
2407discovers:
2408• Support: The number of transactions in which the itemset occurs.
2409• Size: The number of items in the itemset.
2410• Items: A list of the items included in each itemset.
2411Depending on how the algorithm parameters are set, the algorithm might generate
2412many itemsets. Each itemset that is returned in the viewer represents transactions in
2413which the item was sold. By using the controls at the top of the Itemsets tab, you can
2414filter the viewer to show only the itemsets that contain a specified minimum support and
2415itemset size.
2416If you are working with a different mining model and no itemsets are listed, it is because
2417no itemsets met the criteria of the algorithm parameters. In such a scenario, you can
2418change the algorithm parameters to allow itemsets that have lower support.
24191. Click the Itemsets tab of the viewer.
24202. In the Filter Itemset box, type Touring Tire, and then click outside the box.
2421The filter returns all items that contain this string.
24223. In the Show list, select Show attribute name only.
24234. Select the Show long name check box.
2424The list of itemsets is updated to show only the itemsets that contain the string
2425Touring Tire. The long name of the itemset includes the name of the table that
2426contains the attribute and value for each item.
2427To filter the itemsets that are shown in the viewer by name
242890
24295. Clear the Show long name check box.
2430The list of itemsets is updated to show only the short name.
2431The values in the Support column indicate the number of transactions for each itemset.
2432A transaction for an itemset means a purchase that included all the items in the itemset.
2433By default, the viewer lists the itemsets in descending order by support. You can click on
2434the column headers to sort by a different column, such as the itemset size or name. If
2435you are interested in learning more about the individual transactions that are included in
2436an itemset, you can drill through from the itemsets to the individual cases. The structure
2437columns in the drillthrough results are the customer's income level and customer ID,
2438which were not used in the model.
24391. In the list of itemsets, click the Itemset column heading to sort by name.
24402. Locate the item, Touring Tire (with no second item).
24413. Right-click the item, Touring Tire, select Drill Through, and then select Model
2442and Structure Columns.
2443The Drill Through dialog box displays the individual transactions used as
2444support for this itemset.
24454. Expand the nested table, vAssocSeqLineItems, to view the actual list of purchases
2446in the transaction.
24471. Clear any text that might be in the Filter Itemset box. You cannot use a text filter
2448together with a numeric filter.
24492. In the Minimum support box, type 100, and then click the background of the
2450viewer.
2451The list of itemsets is updated to show only itemsets with support of at least 100.
2452Back to Top
2453Rules Tab
2454The Rules tab displays the following information that is related to the rules that the
2455algorithm finds.
2456• Probability: The likelihood of a rule, defined as the probability of the right-hand
2457item given the left-hand side item.
2458• Importance: A measure of the usefulness of a rule. A greater value means a better
2459rule.
2460Importance is provided to help you gauge the usefulness of a rule, because
2461probability alone can be misleading. For example, if every transaction contains a
2462water bottle--perhaps the water bottle is added to each customer's cart
2463To view details for an itemset
2464To filter itemsets by support or size
246591
2466automatically as part of a promotion--the model would create a rule predicting that
2467water bottle has a probability of 1. Based on probability alone, this rule is very
2468accurate, but it does not provide useful information.
2469• Rule: The definition of the rule. For a market basket model, a rule describes a
2470specific combination of items.
2471Each rule can be used to predict the presence of an item in a transaction based on the
2472presence of other items. Just like in the Itemsets tab, you can filter the rules so that only
2473the most interesting rules are shown. If you are working with a mining model that does
2474not have any rules, you might want to change the algorithm parameters to lower the
2475probability threshold for rules.
24761. In the Mining Model Viewer tab, click the Rules tab.
24772. In the Filter Rule box, enter Mountain-200.
2478Clear the Show long name check box.
24793. From the Show list, select Show attribute name only.
2480The viewer will then display only the rules that contain the words "Mountain200".
2481The probability of the rule tells you how likely it is that when someone buys
2482a Mountain-200 bicycle, that person will also buy the other listed product.
2483The rules are ordered by probability in descending order, but you can click the column
2484headings to change the sort order. If you are interested in finding out more details about
2485a particular rule, you can use drillthrough to view the supporting cases.
24861. In the Rules tab, right-click the rule that you want to view.
24872. Select Drill Through, and then select Model Columns Only, or Model and
2488Structure Columns.
2489The Drill Through dialog box provides a summary of the rule at the top of the
2490pane, and a list of all cases that were used as supporting data for the rule.
2491Back to Top
2492Generic Content Tree Viewer
2493This viewer can be used for all models, regardless of the algorithm or model type. The
2494Microsoft Generic Content Tree Viewer is available from the Viewer drop-down list.
2495A content tree is a representation of a mining model as a series of nodes, where each
2496node represents learned knowledge about some subset of the data. The node can
2497contain a pattern, a set of rules, a cluster, or the definition of a range of dates that share
2498some characteristics. The exact content of the node differs depending on the algorithm
2499and the type of the predictable attribute, but the general representation of the content is
2500To see only rules that include the Mountain-200 bicycle
2501To view cases that support a particular rule
250292
2503the same. You can expand each node to see increasing levels of detail, and copy the
2504content of any node to the Clipboard.
25051. In the Mining Model Viewer tab, select Microsoft Generic Content Tree
2506Viewer from the Viewer list.
25072. In the Node Caption pane, scroll to the bottom of the list, and click the last node.
2508The viewer shows itemsets first and rules next, but does not group them. The
2509easiest way to find a specific node is to create a content query. For more
2510information, see Querying an Association Model (Analysis Services - Data
2511Mining).
25123. In the Node Details pane, review the value for NODE_TYPE and
2513NODE_DESCRIPTION.
2514A node type of 8 is a rule, and a node type of 7 is an itemset. For a rule, the value
2515of NODE_DESCRIPTION tells you the conditions that make up the rule. For an
2516itemset, the value of NODE_DESCRIPTION tells you the items included in the
2517itemset.
2518Filtering a Nested Table in a Mining Model (Intermediate Data
2519Mining Tutorial)
2520After you have created and explored the model, you decide that you want to focus on a
2521subset of the customer data. For example, you might want to analyze only the baskets
2522that contain a specific item, or to analyze the demographics of customers who have not
2523purchased anything in a certain period.
2524Analysis Services provides the ability to filter the data that is used in a mining model.
2525This feature is useful because you do not need to set up a new data source view to use
2526different data. In the Basic Data Mining Tutorial, you learned how to filter data from a flat
2527To view details about the rule by using the content viewer
252893
2529table by applying conditions to the case table. In this task, you create a filter that applies
2530to a nested table.
2531Filters on Nested vs. Case Tables
2532If your data source view contains a case table and a nested table, like the data source
2533view used in the Association model, you can filter on values from the case table, the
2534presence or absence of a value in the nested table, or some combination of both.
2535In this task, you will first make a copy of the Association model and then add the
2536IncomeGroup and Region attributes to the new related model, so that you can filter on
2537those attributes in the case table.
25381. In the Mining Models tab of SQL Server Data Tools (SSDT), right-click the
2539Association model, and select New Mining Model.
25402. For Model Name, type Association Filtered. For Algorithm Name, select
2541Microsoft Association Rules. Click OK.
25423. In the column for the Association Filtered model, click the IncomeGroup row and
2543change the value from Ignore to Input.
2544Next, you will create a filter on the case table in the new association model. The filter will
2545pass to the model only the customers in the target region or with the target income
2546level. Then, you will add a second set of filter conditions to specify that the model uses
2547only customers whose shopping baskets contained at least one item.
25481. In the Mining Models tab, right-click the model Association Filtered, and select
2549Set Model Filter.
25502. In the Model Filter dialog box, click the top row in the grid, in the Mining
2551Structure Column text box.
25523. In the Mining Structure Column text box, select IncomeGroup.
2553The icon at the left side of the text box changes to indicate that the selected item
2554is a column.
25554. Click the Operator text box and select the = operator from the list.
25565. Click the Value text box, and type High in the box.
25576. Click the next row in the grid.
25587. Click the AND/OR text box in the next row of the grid and select OR.
25598. In the Mining Structure Column text box, select IncomeGroup. In the Value text
2560box, type Moderate.
2561The filter condition that you created is automatically added to the Expression
2562text box, and should appears as follows:
2563To create and modify a copy of the Association model
2564To add a filter to a mining model
256594
2566[IncomeGroup] = 'High' OR [IncomeGroup] = 'Moderate'
25679. Click the next row in the grid, leaving the operator as the default, AND.
256810. For Operator, leave the default value, Contains. Click the Value text box.
256911. In the Filter dialog box, in the first row under Mining Structure Column, select
2570Model.
257112. For Operator, select IS NOT NULL. Leave the Value text box blank. Click OK.
2572The filter condition in the Expression text box of the Model Filter dialog box is
2573automatically updated to include the new condition on the nested table. The
2574completed expression is as follows:
2575[IncomeGroup] = 'High' OR [IncomeGroup] = 'Moderate' AND EXISTS
2576SELECT * FROM [vAssocSeqLineItems] WHERE [Model] <> NULL).
257713. Click .
25781. In the Mining Models tab, right-click the Association Filtered model, and select
2579Properties.
25802. Change the AllowDrillThrough property to True.
25813. Right-click the Association Filtered mining model, and select Process Model.
25824. Click Yes in the error message to deploy the new model to the Analysis Services
2583database.
25845. In the Process Mining Structure dialog box, click Run.
25856. When processing is complete click Close to exit the Process Progress dialog box,
2586and click Close again to exit the Process Mining Structure dialog box.
2587You can verify by using the Microsoft Generic Content Tree viewer and looking at the
2588value for NODE_SUPPORT that the filtered model contains fewer cases than the original
2589model.
2590Remarks
2591The nested table filter that you just created checks only for the presence of at least one
2592row in the nested table; however, you can also create filter conditions that check for the
2593presence of specific products. For example, you could create the following filter:
2594[IncomeGroup] = 'High' AND
2595 EXISTS (SELECT * FROM [<nested table name>] WHERE [Model] = 'Water
2596Bottle' )
2597This statement means that you are restricting the customers from the case table to only
2598those who have purchased a water bottle. However, because the number of nested table
2599attributes is potentially unlimited, Analysis Services does not supply a list of possible
2600values from which to select. Instead, you must type the exact value.
2601To enable drillthrough and to process the filtered model
260295
2603You can click Edit Query to manually change the filter expression. However, if you
2604change any part of a filter expression manually, the grid will be disabled and thereafter
2605you must work with the filter expression in text edit mode only. To restore grid editing
2606mode, you must clear the filter expression and start over.
2607Predicting Associations (Intermediate Data Mining Tutorial)
2608After the models have been processed, you can use the information about associations
2609stored in the model to create predictions. In the final task of this lesson, you learn how to
2610build prediction queries against the association models that you created. This lesson
2611assumes that you are familiar with how to use the Prediction Query Builder and want to
2612learn how to build prediction queries against association models. For more information
2613how to use Prediction Query Builder, see
2614Creating a Singleton Prediction Query
2615Prediction queries on an association model can be very useful:
2616• Recommend items to a customer, based on prior or related purchases
2617• Find related events.
2618• Identify relationships in or across sets of transactions.
2619To build a prediction query, you first select the association model you want to use, and
2620then you specify the input data. Inputs can come from an external data source, such as a
2621list of values, or you can build a singleton query and provide values as you go.
2622For this scenario, you will first create some singleton prediction queries, to get an idea of
2623how prediction works. Then you will create a query for batch predictions that you could
2624use for making recommendations based on a customer's current purchases.
26251. Click the Mining Model Prediction tab of Data Mining Designer.
26262. In the Mining Model pane, click Select Model. (You can skip this step and the
2627next step if the correct model is already selected.)
26283. In the Select Mining Model dialog box, expand the node that represents the
2629mining structure Association, and select the model Association. Click OK.
2630To create a prediction query on an association model
263196
2632For now, you can ignore the input pane.
26334. In the grid, click the empty cell under Source and select Prediction Function. In
2634the cell under Field, select PredictAssociation.
2635You can also use the Predict function to predict associations. If you do, be sure
2636to choose the version of the Predict function that takes a table column as
2637argument.
26385. In the Mining Model pane, select the nested table vAssocSeqLineItems, and
2639drag it into the grid, to the Criteria/Argument box for the PredictAssociation
2640function.
2641Dragging and dropping table and column names lets you build complex
2642statements without syntax errors. However, it replaces the current contents of the
2643cell, which include other optional arguments for the PredictAssociation function.
2644To view the other arguments, you can temporarily add a second instance of the
2645function to the grid for reference.
26466. Click the Criteria/Argument box and type the following text after the table
2647name: ,3
2648The complete text in the Criteria/Argument box should be as follows:
2649[Association].[v Assoc Seq Line Items],3
26507. Click the Results button in the upper corner of the Prediction Query Builder.
2651The expected results contain a single column with the heading Expression. The
2652Expression column contains a nested table with a single column and the following three
2653rows. Because you did not specify an input value, these predictions represent the most
2654likely product associations for the model as a whole.
2655Model
2656Women's Mountain Shorts
2657Water Bottle
2658Touring-3000
2659Next, you will use the Singleton Query Input pane to specify a product as input to the
2660query, and view the products that are most likely associated with that item.
26611. Click the Design button in the corner of the Prediction Query Builder to switch
2662back to the query building grid.
26632. On the Mining Model menu, select Singleton Query.
26643. In the Mining Model dialog box, select the Association model.
2665To create a singleton prediction query with nested table inputs
266697
26674. In the grid, click the empty cell under Source and select Prediction Function. In
2668the cell under Field, select PredictAssociation.
26695. In the Mining Model pane, select the nested table vAssocSeqLineItems, and
2670drag it into the grid, to the Criteria/Argument box for the PredictAssociation
2671function. Type ,3 after the nested table name just as in the previous procedure.
26726. In the Singleton Query Input dialog box, click the Value box next to vAssoc Seq
2673Line Items, and then click the (…) button.
26747. In the Nested Table Input dialog box, select Touring Tire in the Key column
2675pane, and then click Add.
26768. Click the Results button.
2677The results now show the predictions for products that are most likely associated with
2678the Touring Tire.
2679Model
2680Touring Tire Tube
2681Sport-100
2682Water Bottle
2683However, you already know from exploring the model that the Touring Tire Tube is
2684frequently purchased with the Touring Tire; you are more interested in knowing what
2685products you can recommend to customers who purchase these items together. You will
2686change the query so that it predicts related products based on two items in the basket.
2687You will also modify the query to add the probability for each predicted product.
26881. Click the Design button in the corner of the Prediction Query Builder to switch
2689back to the query building grid.
26902. In the Singleton Query Input dialog box, click the Value box next to vAssoc Seq
2691Line Items, and then click the (…) button.
26923. In the Key column pane, select Touring Tire, and then click Add.
26934. In the grid, click the empty cell under Source and select Prediction Function. In
2694the cell under Field, select PredictAssociation.
26955. In the Mining Model pane, select the nested table vAssocSeqLineItems, and
2696drag it into the grid, to the Criteria/Argument box for the PredictAssociation
2697function. Type ,3 after the nested table name just as in the previous procedure.
26986. In the Nested Table Input dialog box, select Touring Tire Tube in the Key
2699column pane, and then click Add.
2700To add inputs and probabilities to the singleton prediction query
270198
27027. In the grid, in the row for the PredictAssociation function, click the
2703Criteria/Argument box, and change the arguments to add the argument,
2704INCLUDE_STATISTICS.
2705The complete text in the Criteria/Argument box should be as follows:
2706[Association].[v Assoc Seq Line Items], INCLUDE_STATISTICS, 3
27078. Click the Results button.
2708The results in the nested table now change to show the predictions, together with
2709support and probability. For more information about how to interpret these values, see
2710Mining Model Content for Association Models (Analysis Services - Data Mining).
2711Model $SUPPORT $PROBABILITY $ADJUSTEDPROBABILITY
2712Sport-100 4334 0.291… 0.252…
2713Water Bottle 2866 0.192… 0.175…
2714Patch Kit 2113 0.142… 0.132
2715Working with Results
2716When there are many nested tables in the results, you might want to flatten the results
2717for easier viewing. To do this, you can manually modify the query and add the
2718FLATTENED keyword.
27191. Click the SQL button in the corner of the Prediction Query Builder.
2720The grid changes to an open pane where you can view and modify the DMX
2721statement that was created by the Prediction Query Builder.
27222. After the SELECT keyword, type FLATTENED.
2723The complete text of the query should be as follows:
2724SELECT FLATTENED
2725 PredictAssociation([Association].[v Assoc Seq Line
2726Items],INCLUDE_STATISTICS,3)
2727FROM
2728 [Association]
2729NATURAL PREDICTION JOIN
2730(SELECT (SELECT 'Touring Tire' AS [Model]
2731 UNION SELECT 'Touring Tire Tube' AS [Model]) AS [v Assoc
2732Seq Line Items]) AS t
2733To flatten nested rowsets in a prediction query
273499
27353. Click the Results button in the upper corner of the Prediction Query Builder.
2736Note that after you have manually edited a query, you will not be able to switch back to
2737Design view without losing the changes. If you wish to save the query, you can copy the
2738DMX statement that you created manually to a text file. When you change back to
2739Design view, the query is reverted to the last version that was valid in Design view.
2740Creating Multiple Predictions
2741Suppose you want to know the best predictions for individual customers, based on past
2742purchases. You can use external data as input to the prediction query, such as tables
2743containing the customer ID and the most recent product purchases. The requirements
2744are that the data tables be already defined as an Analysis Services data source view;
2745moreover, the input data must contain case and nested tables like those used in the
2746model. They need not have the same names, but the structure must be similar. For the
2747purpose of this tutorial, you will use the original tables on which the model was trained.
27481. In the Mining Model menu, select Singleton Query again, to clear the check
2749mark.
27502. An error message appears warning that your singleton query will be lost. Click
2751Yes.
2752The name of the input dialog box changes to Select Input Table(s).
2753Because you are interested in creating a prediction query that provides Customer ID and
2754a list of products as input, you will add the customer table as the case table, and the
2755purchases table as the nested table. Then you will add prediction functions to create
2756recommendations.
27571. In the Mining Model pane, select the Association Filtered model.
27582. In the Select Input Table(s) dialog box, click Select Case Table.
27593. In the Select Table dialog box, for Data Source, select AdventureWorksDW2008.
2760In the Table/View Name list, select vAssocSeqOrders, and then click OK.
2761The table vAssocSeqOrders is added to the pane.
27624. In the Select Input Table(s) dialog box, click Select Nested Table.
27635. In the Select Table dialog box, for Data Source, select AdventureWorksDW2008.
2764In the Table/View name list, select vAssocSeqLineItems, and then click OK.
2765The table vAssocSeqLineItems is added to the pane.
27666. In the Specify Nested Join dialog box, drag the OrderNumber field from the
2767case table and drop it onto the OrderNumber field in the nested table.
2768You can also click Add Relationship and create the relationship by selecting
2769To change the input method for the prediction query
2770To create a prediction query using nested table inputs
2771100
2772columns from a list.
27737. In the Specify Relationship dialog box, verify that the OrderNumber fields are
2774mapped correctly, and then click OK.
27758. Click OK to close the Specify Nested Join dialog box.
2776The case and nested tables are updated in the design pane to show the joins
2777connecting the external data columns to the columns in the model. If the
2778relationships are wrong, you can right-click the join line and select Modify
2779Connections to edit the column mapping, or you can right-click the join line and
2780select Delete to remove the relationship completely.
27819. Add a new row to the grid. For Source, select vAssocSeqOrders table. For Field,
2782select CustomerKey.
278310. Add a new row to the grid. For Source, select vAssocSeqOrders table. For Field,
2784select Region.
278511. Add a new row to the grid. For Source, select Prediction Function, and for Field,
2786select PredictAssociation.
278712. Drag vAssocSeqLineItems, into the Criteria/Argument box of the
2788PredictAssociation row. Click at the end of the Criteria/Argument box and then
2789type the following text: INCLUDE_STATISTICS,3
2790The complete text in the Criteria/Argument box should be: [Association].[v
2791Assoc Seq Line Items], INCLUDE_STATISTICS, 3
279213. Click the Result button to view the predictions for each customer.
2793Lesson 4: Building a Sequence Clustering Scenario
2794(Intermediate Data Mining Tutorial)
2795The marketing department of Adventure Works Cycles wants to understand how
2796customers move through the Adventure Works Cycles Web site. The company suspects
2797that there is a pattern to the order in which customers put products into their shopping
2798baskets. They want to analyze the order of purchase sequences to learn how customers
2799add related items to their baskets. They can then use this information to streamline the
2800flow of the Web site so that it leads customers to purchase additional products.
2801101
2802After you complete the tasks in this lesson, you will have created a mining model that
2803uses the Microsoft Sequence Clustering algorithm to predict the next item that
2804customers will put into their shopping baskets. You will experiment with two versions of
2805the model: one that analyzes only the order of products in the basket, and one that
2806contains some additional customer demographics for clustering. Finally, you will use the
2807models to create predictions that you can use to recommend products to customers.
2808Creating a Sequence Clustering Mining Model Structure
2809(Intermediate Data Mining Tutorial)
2810The first step in creating a sequence clustering mining model is to use the Data Mining
2811Wizard to create a new mining structure and a mining model based on the Microsoft
2812Sequence Clustering algorithm.
2813You will use the same data source view that you used for the market basket analysis, but
2814you will add a column that contains the sequence identifier. In this scenario, the
2815sequence means the order in which the customer added items to the shopping basket. 
2816102
2817You will also add some columns that are used in one of the models to group customers
2818by demographics.
2819Procedures
28201. In Solution Explorer in SQL Server Data Tools (SSDT), right-click Mining
2821Structures and select New Mining Structure.
28222. On the Welcome to the Data Mining Wizard page, click Next.
28233. On the Select the Definition Method page, verify that From existing relational
2824database or data warehouse is selected, and then click Next.
28254. On the Create the Data Mining Structure page, verify that the option Create
2826mining structure with a mining model is selected. Next, click the dropdown list
2827for the option, Which data mining technique do you want to use?, and select
2828Microsoft Sequence Clustering. Click Next.
2829The Select Data Source View page appears. Under Available data source
2830views, select Orders.
2831Orders is the same data source view that you used for the market basket
2832scenario. If you have not created this data source view, see Adding a Data Source
2833View with Nested Tables (Intermediate Data Mining Tutorial).
28345. Click Next.
28356. On the Specify Table Types page, select the Case check box next to the
2836vAssocSeqOrders table, and select the Nested check box next to the
2837vAssocSeqLineItems table. Click Next.
2838nNote
2839If an error occurs when you select the Case or Nested check boxes, it
2840may be that the join in the data source view is not correct. The nested
2841table, vAssocSeqLineItems, must be connected to the case table,
2842vAssocSeqOrders, by a many-to-one join. You can edit the relationship
2843by right-clicking on the join line and then reversing the direction of the
2844join. For more information, see Create/Edit Relationship Dialog Box
2845(Analysis Services - Multidimensional Data).
28467. On the Specify the Training Data page, choose the columns for use in the
2847model by selecting a check box as follows:
2848• IncomeGroup Select the Input check box.
2849This column contains interesting information about the customers that you
2850can use for clustering. You will use it in the first model and then ignore it in
2851the second model.
2852• OrderNumber Select the Key check box.
2853This field will be used as the identifier for the case table, or Key. In general,
2854To create a sequence clustering structure and model
2855you should never use the key field of the case table as an input, because the
2856key contains unique values that are not useful for clustering.
2857• Region Select the Input check box.
2858This column contains interesting information about the customers that you
2859can use for clustering. You will use it in the first model and then ignore it in
2860the second model.
2861• LineNumber Select the Key and Input check boxes.
2862The LineNumber field will be used as the identifier for the nested table, or
2863Sequence Key. The key for a nested table must always be used for input.
2864• Model Select the Input and Predictable check boxes.
2865Verify that the selections are correct, and then click Next.
28668. On the Specify Columns' Content and Data Type page, verify that the grid
2867contains the columns, content types, and data types shown in the following table,
2868and then click Next.
2869Tables/Columns Content Type Data Type
2870IncomeGroup Discrete Text
2871OrderNumber Key Text
2872Region Discrete Text
2873vAssocSeqLineItems
2874Line Number Key Sequence Long
2875Model Discrete Text
28769. On the Create Testing Set page, change the Percentage of data for testing to
287720, and then click Next.
287810. On the Completing the Wizard page, for the Mining structure name, type
2879Sequence Clustering with Region.
288011. For the Mining model name, type Sequence Clustering with Region.
288112. Check the Allow drill through box, and then click Finish.
2882104
2883Processing the Sequence Clustering Model
2884After you create a new mining structure, you must deploy the changes that you made to
2885the data mining solution, and then process the structure. After processing of both the
2886new structure and the mining model is complete, you can browse the mining model.
2887Processing is always required when you create a new data mining structure. However, if
2888you add a new mining model to an existing structure, you can process just the mining
2889model. In this scenario, because you have created a new mining structure and a new
2890mining model, you must process both.
2891Procedures
28921. On the Mining Model menu of SQL Server Data Tools (SSDT), select Process
2893Mining Structure and All Models.
28942. At the warning asking whether you want to build and deploy the project, click
2895Yes.
28963. In the Process Mining Structure - Sequence Clustering with Region dialog
2897box, click Run.
2898The Process Progress dialog box opens to display information about model
2899processing. Processing of the new structure and model might take some time.
29004. After processing is complete, click Close to exit the Process Progress dialog box.
29015. Click Close again to exit the Process Mining Structure - Sequence Clustering
2902with Region dialog box.
2903Exploring the Sequence Clustering Model (Intermediate Data
2904Mining Tutorial)
2905Now that you have built the Sequence Clustering with Region model, you can explore
2906it by using the Microsoft Sequence Clustering Viewer in the Mining Model Viewer tab
2907of Data Mining Designer. The Microsoft Sequence Cluster Viewer contains five tabs:
2908Cluster Diagram, Cluster Profiles, Cluster Characteristics, Cluster Discrimination, and
2909State Transitions. For more information about how to use this viewer, see
2910To process the mining structure and model
2911105
2912• Cluster Diagram tab
2913• Cluster Profiles tab
2914• Cluster Characteristics tab
2915• Cluster Discrimination tab
2916• State Transitions tab
2917• Generic Content View
2918Cluster Diagram Tab
2919The Cluster Diagram tab graphically displays the clusters that the algorithm discovered
2920in the database. The layout in the diagram represents the relationships of the clusters,
2921with similar clusters grouped close together. By default, the shade of each node
2922represents the density of all cases in the cluster: the darker the shade of the node, the
2923more cases it contains. You can change the meaning of the shading of the nodes so that
2924it represents support, within each cluster, for an attribute and a state.
2925You can also rename the clusters, to make it easier to identify and work with target
2926clusters. For this tutorial, you will rename the cluster that has the highest percentage of
2927customers from the Pacific region, and the cluster that has the most cases overall.
2928The cases that are assigned to specific clusters might change when you reprocess
2929the model, depending on the data and the model parameters. Also, if you
2930rename clusters, the names will be lost when you reprocess the mining model.
29311. In the Shading Variable list, select Model.
29322. Select Cycling Cap in the State list.
2933The diagram updates to show the concentration of the selected product in each
2934of the clusters. The cluster that has the darkest shading contains the highest
2935density of cycling caps. You can change the shading variable to use any any state
2936of any input column.
29373. In the Shading Variable list, select Population.
2938When you change the shading variable to population, the diagram updates to
2939compare the clusters by size. The cluster that has the darkest shading contains
2940more cases than the other clusters.
29411. Change Shading Variable to Region, and set State to Pacific.
29422. Highlight the darkest node in the graph.
29433. Right-click this cluster and select Rename Cluster.
29444. Type the name Pacific Cluster.
2945Note
2946To change the attribute used for highlighting clusters
2947To rename nodes in the model
2948106
29495. Change the value of Shading Variable to Population.
29506. In the updated graph, locate the darkest cluster, which should be the largest
2951cluster. If you cannot tell by the shading which cluster is largest, pause the mouse
2952over each cluster and view the ToolTip, and then choose the cluster that contains
2953the most cases.
29547. Right-click this cluster and select Rename Cluster. Type the new name, Largest
2955Cluster.
2956You can drill through from the node that represents the cluster to view details of the
2957cases that are in each cluster. This can be useful if you want to take action on the results
2958of your analysis, such as sending e-mail to a customer. You can also browse the other
2959attributes of the cases that you included in the structure but did not use in the model,
2960such as Region and IncomeGroup. For more information about drilling through from
2961mining models to the underlying cases, see
29621. Right-click Pacific Cluster, select Drill Through, and then select Model and
2963Structure columns.
2964The Drill Through dialog box opens. Columns that are not used in the model but
2965that are available for querying are prefixed with Structure.
2966You can see that this cluster contains mostly customers from the Pacific region,
2967with only a few customers from other regions.
29682. Click the plus sign in the nested column v Assoc Seq Line Items to view the
2969sequence of items in a particular customer order.
29703. Close the Drill Through dialog box.
2971Note
2972The Play button enables you to requery the data; however, requerying
2973does not change the data that is displayed, unless the model has been
2974dynamically updated in the background by some other process.
2975Cluster Profiles Tab
2976The Cluster Profiles tab displays the sequences that are in each cluster. The clusters are
2977listed in individual columns to the right of the States column.
2978In the viewer, the Model row describes the overall distribution of items in a cluster, and
2979the Model.samples row contains sequences of the items. Each line of the color
2980sequences in each cell of the Model.samples row represents the behavior of a randomly
2981selected user in the cluster.
2982Each color in an individual sequence histogram represents a product model. The Mining
2983Legend shows you the sequences of products by using both color-coding and the
2984To drill through to details from the Cluster diagram
2985107
2986product model names. If you have added other columns to the model for clustering,
2987such as Region or Income Group, the viewer will contain an additional row for each
2988column that shows the distribution of these values within each cluster.
29891. Right-click the Model row in the column for the cluster Largest Cluster, and
2990select Show Legend.
2991The Color column contains a shaded bar that indicates the frequency of items
2992found in sequences. Each item is represented by a different color. The Meaning
2993column lists the product model names for each color. The Distribution column
2994tells you the percentage of cases that contained this item in a sequence.
29952. Close the Mining Legend.
29963. Right-click the Model.samples row in the column with the heading, Population,
2997and select Show Legend.
29984. Scan the list of sequences in the overall model.
2999The Mining Legend lists the most common sequences first, so you can see that
3000Mountain Tire Tube is the first item in many sequences. This means that a
3001customer is very likely to put the Mountain Tire Tube in the shopping basket first.
30021. Scroll down in the Attribute pane until you find the row for the Region attribute.
3003The row contains a histogram for each cluster in the model, plus one additional
3004histogram for Population, meaning the entire set of cases used in the model. A
3005histogram is a bar with different colors in it, where each color represents an
3006attribute, and the size of the colored section for that attribute represents the
3007percentage of cases with that attribute.
30082. Compare the histograms for the clusters that you renamed Pacific Cluster and
3009Largest Cluster. Each cluster appears in a different column.
3010Both look like solid colors, but the colors are different.
30113. In the Region row, pause the mouse over the colored histogram for Largest
3012Cluster.
3013The ToolTip displays values that show the actual percentages of cases from each
3014region.
30154. Right-click the colored histogram in the Region row for Pacific Cluster, select
3016Drill Through, and then select Model Columns Only.
30175. Move the scroll bar to review all of the customers in this cluster.
3018Again, from drilling through to the details you can see that the cluster contains
3019mostly orders from the Pacific region but also a few from the North America and
3020Europe regions.
3021To view the sequences that are most common in a cluster
3022To drill through to cases from the cluster viewer
30236. Close the Drill Through dialog box.
3024Cluster Characteristics Tab
3025The Cluster Characteristics tab summarizes the transitions between states in a cluster
3026by displaying bars that visually represent the importance of the attribute value for the
3027selected cluster. The Variables column tells you what the model found to be important
3028for the selected cluster or population: either a particular value or the relationship
3029between values, known as transition. The Values column provides more detail about the
3030value or transition, and the Probability column visually represents the weight of this
3031attribute or transition.
30321. In the Cluster dropdown list, select Pacific Cluster.
3033The list updates to show the characteristics of the cluster that you renamed
3034Pacific Cluster. In this cluster, the most important characteristic is Region.
30352. Pause the mouse over the shaded bar in the row for Region.
30363. Look through the list of characteristics for the cluster until you find the first
3037transition row.
30384. A transition row contains the text Transition in the Variables column, and some
3039combination of sequential attribute values in the Value column. The sequence
3040can also contain starting points and missing values.
3041For example, suppose the transition has the value, [Start] -> Road Tire Tube. This
3042means that customers in this cluster frequently put the Road Tire Tube in their
3043shopping basket first. This might signify that the product is a popular item that
3044customers seek out first, or it might only indicate that the product is easy to find
3045on the purchasing site.
30465. Scroll through the list until you find the first transition that does not have [Start]
3047or missing in it.
3048For example, suppose you find the transition, Touring Tire, Touring Tire Tube.
3049This means that customers in this cluster frequently purchased these items
3050together, in exactly this order.
30516. Pause the mouse over the shaded bar for this transition.
3052The probability of this transition is displayed as a percentage.
30537. In the Cluster dropdown list, select Population (All).
3054The list of attributes updates to show the characteristics of all orders used to
3055create the model. In this mining model, the most important characteristic for
3056To view the important attributes for a cluster
3057distinguishing between clusters is Region, with a value of North America.
3058After reviewing these tasks, you realize two things. The first is that you need a lot of data
3059to obtain a meaningful number of combinations. For example, the sequences with the
3060highest probabilities are likely to include a [Start] or Missing state.
3061The second is that there is a strong clustering effect on attributes for Region, which
3062makes it more difficult to see the groups of sequences. Therefore, you decide to create
3063another model that uses sequences only, and does not include the columns for region or
3064income.
3065Back to Top
3066Cluster Discrimination Tab
3067The Cluster Discrimination tab helps you compare two clusters, to determine which
3068attributes distinguish a particular cluster from another cluster. The tab contains four
3069columns: Variables, Values, Cluster 1, and Cluster 2. You can choose any cluster to use
3070as Cluster 1 and Cluster 2.
3071The Variables column tells you the name of the attribute, which can either be a column
3072name or combination of column name and the word transition. The Values column
3073shows the exact value of the attribute or the transition. The shaded bars in the columns
3074for Cluster 1 and Cluster 2 indicate the strength of the attribute in the clusters that you
3075are comparing. The longer the bar, the more the cluster is likely to include cases with
3076that attribute.
30771. In the Cluster Discrimination tab, for Cluster 1, select Pacific Cluster.
3078By default, the selection for Cluster 2 changes to Complement of Pacific
3079Cluster.
3080The top attribute that distinguishes Pacific Cluster from all other cases is the
3081region. Region is such a strong attribute for clustering that it obscures other
3082attributes. To avoid this effect, try comparing several of the smaller clusters to
3083each other. When you do so, the list of attributes changes and might include
3084more transitions between models.
30852. Locate a transition row, and pause the mouse over the shaded bar.
3086To compare two clusters by using the Cluster Discrimination tab
3087On the State Transitions tab, you can select a cluster and browse through its state
3088transitions. If you select Population (All) from the cluster drop-down list, the diagram
3089shows the distribution of states for the whole mining model.
3090Each node in the graph represents a state, or possible value, of the sequences that you
3091are trying to analyze. The background color of the nodes represents the frequency of
3092that state. Lines connect some states, indicating a transition between states. You can
3093move the slider up or down to change the probability threshold for the transitions.
3094Numbers are associated with some nodes, indicating the probability of that state.
30951. In the State Transitions tab of the Mining Model viewer, select Pacific Cluster
3096from the list of clusters. Ensure that the Show Edge Labels option is selected.
3097The graph updates to show the transitions that are most common in this cluster.
30982. Click any node that is connected by a line to another node.
3099The graph is updated and highlights the related nodes. The numeric value next to
3100the line indicates the probability of the transition.
31013. Raise the slider up to All Links, to increase the number of transitions included in
3102the graph.
31034. Select Population (All) from Cluster.
3104Note that when you load a different cluster, the graph resets to the default
3105display settings, so the slider control is reset to the middle position.
31065. Click the darkest node in the graph, which should be Sport-100.
3107Note that there are no lines connecting this product to other products.
31086. Raise the slider up one step, to increase the number of transitions included in the
3109graph. Do not go all the way to All Links yet.
3110The graph is updated by adding several more transitions to the graph, but none
3111that include the Sport-100 model.
31127. Move the slider control all the way to All Links. Click the Sport-100 node if it is
3113not already selected.
3114The graph updates to show many transitions that include the Sport-100 product.
3115The direction of the arrow on the connecting line tells you whether the Sport-100
3116item was selected as the first item or the second item in the pair.
31178. Clicking the node for Touring Tire and move the slider control back down to the
3118middle position.
3119At first, there are many transition lines connecting Touring Tire to other products,
3120but when you raise the probability threshold, the less likely transitions are
3121eliminated from the graph, leaving just the transition, Touring Tire > Touring Tire
3122Tube. This transition means that if a customer puts a Touring Tire into the
3123shopping basket, there is a strong probability that the customer will next put a
3124To explore the relationships in the State Transition tab
3125Touring Tire Tube into the basket.
3126Back to Top
3127Generic Content Tree Viewer
3128This viewer can be used for all models, regardless of the algorithm or model type. The
3129Microsoft Generic Content Tree Viewer is available from the Viewer drop-down list.
3130A content tree is a representation of any mining model as a series of nodes, wherein
3131each node represents learned knowledge about the training data. The node can contain
3132a pattern, a set of rules, a cluster, or the definition of a range of dates that share some
3133attributes. The exact content of the node differs depending on the algorithm and the
3134predictable attribute, but the general representation of the content is the same.
31351. In the Mining Model Viewer tab, click the Viewer list, and select Microsoft
3136Generic Content Tree viewer.
31372. In the Node Caption pane, click Pacific Cluster (1).
3138The name for this node contains both the friendly name that you assigned to the
3139cluster and the underlying node ID. You can use the node IDs to drill down into
3140additional detail in the model.
31413. Expand the first child node, named Sequence level for cluster 1.
3142The sequence level node for a cluster contains details about the states and
3143transitions that are included in that cluster. You can use these details, available in
3144the NODE_DISTRIBUTION column, to explore the sequences and the states for
3145each cluster or for the model as a while.
31464. Continue to expand nodes and view the details in the HTML viewer pane.
3147To view details for a sequence clustering model by using the Generic Content
3148Tree Viewer
3149112
3150Creating a Related Sequence Clustering Model (Intermediate
3151Data Mining Tutorial)
3152Through your exploration of the sequence clustering model, you learned that other
3153attributes such as Region or Income have a strong effect on the models; therefore, to
3154understand the sequences better, you will create a related sequence clustering model
3155and remove the attributes related to customer demographics.
3156In this task, you will create a copy of the regional sequence clustering model, and then
3157remove from the model any columns that are not directly related to the sequences.
3158The new model will contain all the same columns as the mining model on which it is
3159based. However, you do not need to remove the columns from the mining structure,
3160only specify that the new mining model ignore the columns.
3161Procedures
31621. In SQL Server Data Tools (SSDT), in the Data Mining Designer, click the Mining
3163Models tab.
31642. Right-click the model you want to copy, and select New Mining Model.
31653. In the New Mining Model dialog box, type a model name, and select Microsoft
3166Sequence Clustering.
3167For this tutorial, type the name Sequence Clustering.
31684. Click OK.
31691. In the Mining Model tab, in the column for the new model named Sequence
3170Clustering, click the row for the Income Group attribute, and select Ignore.
31712. Repeat this step for the attribute Region.
31723. Click the plus sign next to the table name, v Assoc Seq Line Items, to expand the
3173table and view the columns from the nested table.
3174The new model should have only the following columns:
3175Order Number Key
3176Line Number Key
3177Model Predict
31781. In the Mining Model tab, right-click the new model named Sequence
3179Clustering, and select Process Model.
3180Because the new simplified mining model is based on a structure that has already
3181To make a copy of the sequence clustering model
3182To remove columns from the mining model
3183To process the new sequence clustering model 
3184been processed, you do not need to reprocess the structure. You can process just
3185the new mining model.
31862. Click Yes to deploy the updated data mining project to the server.
31873. In the Process Mining Model dialog box, click Run.
31884. Click Close to close the Process Progress dialog box, and then click Close again
3189in the Process Mining Model dialog box.
3190Creating Predictions on a Sequence Clustering Model
3191(Intermediate Data Mining Tutorial)
3192After you understand the sequence clustering model better by browsing it in the viewer,
3193you can create prediction queries by using Prediction Query Builder on the Mining
3194Model Prediction tab in Data Mining Designer. To create a prediction, you first select
3195the sequence clustering model, and then select the input data. For inputs, you can use
3196either an external data source, or you can build a singleton query and provide values in a
3197dialog box.
3198Creating Predictions on the Regional Model
3199For this scenario, you will first create some singleton prediction queries, to get an idea of
3200how predictions might be different by region.
32011. Click the Mining Model Prediction tab of Data Mining Designer.
32022. In the Mining Model column menu, select Singleton Query.
3203The Mining Model pane and Singleton Query Input pane appear.
32043. In the Mining Model pane, click Select Model. (You can skip this step if the
3205sequence clustering mode is already selected.)
3206The Select Mining Model dialog box opens.
32074. Expand the node that represents the mining structure Sequence Clustering with
3208Region, and select the model Sequence Clustering with Region. Click OK. For
3209To create a singleton query on a sequence clustering model
3210114
3211now, ignore the input pane; you will specify the inputs after you have set up the
3212prediction functions.
32135. In the grid, click the empty cell under Source and select Prediction Function. In
3214the cell under Field, select PredictSequence.
3215Note
3216You can also use the Predict function. If you do, be sure to choose the
3217version of the Predict function that takes a table column as argument..
32186. In the Mining Model pane, select the nested table v Assoc Seq Line Items,
3219and drag it into the grid, to the Criteria/Argument box for the PredictSequence
3220function.
3221Dragging and dropping table and column names enables you to build complex
3222statements without syntax errors. However, it replaces the current contents of the
3223cell, which include other optional arguments for the PredictSequence function.
3224To view the other arguments, you can temporarily add a second instance of the
3225function to the grid for reference.
32267. Click the Result button in the upper corner of the Prediction Query Builder.
3227The expected results contain a single column with the heading Expression. The
3228Expression column contains a nested table with three columns as follows:
3229$SEQUENCE Line Number Model
32301 Mountain-200
3231What do these results mean? Remember that you did not specify any inputs. Therefore,
3232the prediction is made against the entire population of cases, and Analysis Services
3233returns the most likely prediction overall.
3234Adding Inputs to a Singleton Prediction Query
3235Until now, you have not specified any inputs. In the next task, you will use the Singleton
3236Query Input pane to specify some inputs to the query. First, you will use [Region] as an
3237input to the regional sequence clustering model, to determine whether the predicted
3238sequences are the same for all regions. You will then learn how to modify the query to
3239add the probability for each prediction, and flatten the results to make them easier to
3240view.
32411. Click the Design button in the upper left-hand corner of the Prediction Query
3242Builder to switch back to the query building grid.
32432. In the Singleton Query Input dialog box, click the Value box for Region, and
3244To generate predictions for a specific customer group
3245115
3246select Europe.
32473. Click the Result button to view predictions for customers in Europe.
32484. Click the Design button in the upper left-hand corner of the Prediction Query
3249Builder to switch back to the query building grid.
32505. In the Singleton Query Input dialog box, click the Value box for Region, and
3251select North America.
32526. Click the Result button to view predictions for customers in North America.
3253Adding Probabilities by Using a Custom Expression
3254To output the probability for each prediction is slightly more complicated, because the
3255probability is an attribute of the prediction and is output as a nested table. If you are
3256familiar with Data Mining Extensions (DMX), you can easily alter the query to add a subselect
3257statement on the nested table. However, you can also create a sub-select
3258statement in the Prediction Query Builder by adding a custom expression.
32591. Click the Design button in the upper left-hand corner of the Prediction Query
3260Builder to switch back to the query building grid.
32612. In the grid, under Source, click a new row, and select Custom Expression.
32623. Leave the box under Field blank.
32634. For Alias, type t.
32645. In the Criteria/Argument box, type the complete sub-select statement as shown
3265in the following code sample. Be sure to include the starting and ending
3266parentheses.
3267(SELECT PredictProbability([Model]) FROM
3268PredictSequence([Sequence Clustering with Region].[v Assoc
3269Seq Line Items]))
32706. Click the Result button to view predictions for customers in Europe.
3271The results now contain two nested tables, one with the prediction, and one with the
3272probability for the prediction. If the query does not work, you can switch to query design
3273view and review the complete query statement, which should be as follows:
3274SELECT
3275 PredictSequence([Sequence Clustering with Region].[v Assoc Seq Line
3276Items]),
3277 ( (SELECT PredictProbability([Model]) FROM PredictSequence([Sequence
3278Clustering with Region].[v Assoc Seq Line Items]))) as [t]
3279FROM
3280 [Sequence Clustering with Region]
3281To output probabilities for a predicted sequence by using a custom expression
3282116
3283NATURAL PREDICTION JOIN
3284(SELECT 'Europe' AS [Region]) AS t
3285Working with Results
3286When there are many nested tables in the results, you might want to flatten the results
3287for easier viewing. To do this, you can manually modify the query and add the
3288FLATTENED keyword.
32891. Click the Query button in the corner of the Prediction Query Builder.
3290The grid changes to an open pane where you can view and modify the DMX
3291statement that was created by the Prediction Query Builder.
32922. After the SELECT keyword, type FLATTENED.
3293The complete text of the query should be similar to the following:
3294SELECT FLATTENED
3295 PredictSequence([Sequence Clustering with Region].[v Assoc
3296Seq Line Items]),
3297 ( (SELECT PredictProbability([Model]) FROM
3298PredictSequence([Sequence Clustering with Region].[v Assoc
3299Seq Line Items]))) as [t]
3300FROM
3301 [Sequence Clustering with Region]
3302NATURAL PREDICTION JOIN
3303(SELECT 'Europe' AS [Region]) AS t
33043. Click the Results button in the upper corner of the Prediction Query Builder.
3305After you have manually edited a query, you will not be able to switch back to Design
3306view without losing the changes. You can, however, save the DMX statement that you
3307created manually to a text file, and then change back to Design view. When you do so,
3308the query is reverted to the last version that was valid in Design view.
3309Creating Predictions on the Related Model
3310The previous examples used a case table column, Region, as the input to the singleton
3311prediction query, because you were interested in knowing whether the model had found
3312any differences between regions. However, after exploring the model, you decided that
3313the differences are not strong enough to justify customizing product recommendations
3314by region. What you are really interested in predicting is the items that customers select.
3315Therefore, in the queries that follow, you will use the sequence clustering model that
3316does not include Region, to generate recommendations for all customers.
3317Using Nested Table Columns as Input
3318To flatten nested rowsets in a prediction query
3319117
3320First you will create a singleton prediction query that takes a single item as input and
3321returns the next most likely item. To get a prediction of this kind, you have to use a
3322nested table column as the input value. This is because the attribute that you are
3323predicting, Model, is part of a nested table. Analysis Services provides the Nested Table
3324Input dialog box to help you easily create prediction queries on nested table attributes,
3325by using the Prediction Query Builder.
33261. Click the Design button in the upper left-hand corner of the Prediction Query
3327Builder to switch back to the query building grid.
33282. In the Singleton Query Input dialog box, click the Value box for Region, and
3329select the empty row to clear the input for this field.
33303. In the Singleton Query Input dialog box, click the Value box for
3331vAssocSeqLineItems, and then click the (…) button.
33324. In the Nested Table Input dialog box, click Add.
33335. In the new row, click the box under Model, and select Touring Tire from the list.
3334Click OK.
33356. Click the Result button to view the predictions.
3336The model recommends the following next items for all customers who choose the
3337Touring Tire as the first item. You already know from exploring the model that customers
3338frequently purchase the products Touring Tire and Touring Tire Tube together, so these
3339recommendations look good.
3340$SEQUENCE Line Number Model
33411 Touring Tire Tube
33422 Sport-100
33433 Long-Sleeve Logo Jersey
3344Creating a Bulk Prediction Query using Nested Table Inputs
3345Now that you are satisfied that the model creates the kind of predictions that you can
3346use in making recommendations, you will create a prediction query that is mapped to an
3347external data source. That data source will provide values representing current products.
3348Because you are interested in creating a prediction query that provides Customer ID and
3349a list of products as input, you will add the customer table as the case table, and the
3350purchases table as the nested table. Then you will add prediction functions as you did
3351previously to create recommendations.
3352To use a nested table as input to a prediction
3353This is the same procedure that you use to create predictions for the market basket
3354scenario in Lesson 3; however, in a sequence clustering model predictions also need the
3355order as input.
33561. In the Mining Model pane, select the Sequence Clustering model, if it is not
3357already selected.
33582. In the Select Input Table(s) dialog box, click Select Case Table.
33593. In the Select Table dialog box, for Data Source, select Orders. In the Table/View
3360Name list, select vAssocSeqOrders, and then click OK.
33614. In the Select Input Table(s) dialog box, click Select Nested Table.
33625. In the Select Table dialog box, for Data Source, select Orders. In the Table/View
3363name list, select vAssocSeqLineItems, and then click OK.
3364Analysis Services will try to detect relationships and create them automatically if
3365the data types match and the column names are similar. If the relationships that it
3366creates are wrong, you can right-click the join line and select Modify
3367Connections to edit the column mapping, or you can right-click the join line and
3368select Delete to remove the relationship completely. In this case, because the
3369tables were already joined in the data source view, those relationships are
3370automatically added to the design pane.
33716. Add a new row to the grid. For Source, select vAssocSeqOrders, and for Field,
3372select CustomerKey.
33737. Add a new row to the grid. For Source, select Prediction Function, and for Field,
3374select PredictSequence.
33758. Drag vAssocSeqLineItems, into the Criteria/Argument box. Click at the end of
3376the Criteria/Argument box and then type the following arguments: 2.
3377The complete text in the Criteria/Argument box should be: [Sequence
3378Clustering].[v Assoc Seq Line Items],2
33799. Click the Result button to view the predictions for each customer.
3380You have completed the tutorial on sequence clustering models.
3381To create a prediction query using nested table inputs
3382Lesson 5: Building Neural Network and Logistic
3383Regression Models (Intermediate Data Mining
3384Tutorial)
3385The Operations department of Adventure Works is engaged in a project to improve
3386customer satisfaction with their call center. They hired a vendor to manage the call
3387center and to report metrics on call center effectiveness, and have asked you to analyze
3388some preliminary data provided by the vendor. They want to know if there are any
3389interesting findings. In particular, they would like to know if the data suggests any
3390staffing problems with staffing or ways to improve customer satisfaction.
3391The data set is small and covers only a 30-day period in the operation of the call center.
3392The data tracks the number of new and experienced operators in each shift, the number
3393of incoming calls, the number of orders as well as issues that must be resolved, and the
3394average time a customer waits for someone to respond to a call. The data also includes a
3395service quality metric based on abandon rate, which is an indicator of customer
3396frustration.
3397Because you do not have any prior expectations about what the data will show, you
3398decide to use a neural network model to explore possible correlations. Neural network
3399models are often used for exploration because they can analyze complex relationships
3400between many inputs and outputs.
3401What You Will Learn
3402In this lesson, you will use the neural network algorithm to build a model that you and
3403the Operations team can use to understand the trends in the data. As part of this lesson,
3404you will try to answer the following questions:
3405• What factors affect customer satisfaction?
3406• What can the call center do to improve service quality?
3407Based on the results, you will then build a logistic regression model that you can use for
3408predictions. The predictions will be used by the Operations team as an aid in planning
3409call center operation. 
3410Adding a Data Source View for Call Center Data (Intermediate
3411Data Mining Tutorial)
3412In this task, you add a data source view that will be used to access the call center data.
3413The same data will be used to build both the initial neural network model for
3414exploration, and the logistic regression model that you will use to make
3415recommendations.
3416You will also use the Data Source View Designer to add a column for the day of the
3417week. That is because, although the source data tracks call center data by dates, your
3418experience tells you that there are recurring patterns both in terms of call volume and
3419service quality, depending on whether the day is a weekend or a weekday.
3420Procedures
34211. In Solution Explorer, right-click Data Source Views, and select New Data
3422Source View.
3423The Data Source View Wizard opens.
34242. On the Welcome to the Data Source View Wizard page, click Next.
34253. On the Select a Data Source page, under Relational data sources, select the
3426Adventure Works DW Multidimensional 2012 data source. If you do not have
3427this data source, see
3428To add a data source view
3429121
34304. On the Select Tables and Views page, select the following table and then click
3431the right arrow to add it to the data source view:
3432• FactCallCenter (dbo)
3433• DimDate
34345. Click Next.
34356. On the Completing the Wizard page, by default the data source view is named
3436Adventure Works DW Multidimensional 2012 . Change the name to CallCenter,
3437and then click Finish.
3438Data Source View Designer opens to display the CallCenter data source view.
34397. Right-click inside the Data Source View pane, and select Add/Remove Tables.
3440Select the table, DimDate and click OK.
3441A relationship should be automatically added between the DateKey columns in
3442each table. You will use this relationship to get the column,
3443EnglishDayNameOfWeek, from the DimDate table and use it in your model.
34448. In the Data Source View designer, right-click the table, FactCallCenter, and select
3445New Named Calculation.
3446In the Create Named Calculation dialog box, type the following values:
3447Column name DayOfWeek
3448Description Get day of week from DimDate table
3449Expression (SELECT EnglishDayNameOfWeek AS
3450DayOfWeek FROM DimDate where
3451FactCallCenter.DateKey =
3452DimDate.DateKey)
3453To verify that the expression creates the data you need, right-click the table
3454FactCallCenter, and then select Explore Data.
34559. Take a minute to review the data that is available, so that you can understand
3456how it is used in data mining:
3457Column name Contains
3458FactCallCenterID An arbitrary key created when the data
3459was imported to the data warehouse.
3460This column identifies unique records
3461and should be used as the case key for
3462the data mining model.
3463122
3464DateKey The date of the call center operation,
3465expressed as an integer. Integer date
3466keys are often used in data warehouses,
3467but you might want to obtain the date
3468in date/time format if you were going
3469to group by date values.
3470Note that dates are not unique because
3471the vendor provides a separate report
3472for each shift in each day of operation.
3473WageType Indicates whether the day was a
3474weekday, a weekend, or a holiday.
3475It is possible that there is a difference in
3476quality of customer service on
3477weekends vs. weekdays so you will use
3478this column as an input.
3479Shift Indicates the shift for which calls are
3480recorded. This call center divides the
3481working day into four shifts: AM, PM1,
3482PM2, and Midnight.
3483It is possible that the shift influences the
3484quality of customer service so you will
3485use this as an input.
3486LevelOneOperators Indicates the number of Level 1
3487operators on duty.
3488Call center employees start at Level 1 so
3489these employees are less experienced.
3490LevelTwoOperators Indicates the number of Level 2
3491operators on duty.
3492An employee must log a certain number
3493of service hours to qualify as a Level 2
3494operator.
3495TotalOperators The total number of operators present
3496during the shift.
3497Calls Number of calls received during the
3498shift.
3499AutomaticResponses The number of calls that were handled
3500entirely by automated call processing
3501(Interactive Voice Response, or IVR).
3502Orders The number of orders that resulted
3503from calls.
3504IssuesRaised The number of issues requiring followup
3505that were generated by calls.
3506AverageTimePerIssue The average time required to respond
3507to an incoming call.
3508ServiceGrade A metric that indicates the general
3509quality of service, measured as the
3510abandon rate for the entire shift. The
3511higher the abandon rate, the more likely
3512it is that customers are dissatisfied and
3513that potential orders are being lost.
3514Note that the data includes four different columns that are based on a single date
3515column: WageType, DayOfWeek, Shift, and DateKey. Ordinarily in data mining it is
3516not a good idea to use multiple columns that are derived from the same data, as the
3517values correlate with each other too strongly and can obscure other patterns.
3518However, we will not use DateKey in the model because it contains too many unique
3519values. There is no direct relationship between Shift and DayOfWeek, and
3520WageType and DayOfWeek are only partly related. If you were worried about
3521collinearity, you could create the structure using all of the available columns, and
3522then ignore different columns in each model and test the effect.
3523Creating a Neural Network Structure and Model (Intermediate
3524Data Mining Tutorial)
3525To create a data mining model, you must first use the Data Mining Wizard to create a
3526new mining structure based on the new data source view. In this task you will use the
3527wizard to create a mining structure, and at the same time create an associated mining
3528model that is based on the Microsoft Neural Network algorithm.
3529Because neural networks are extremely flexible and can analyze many combinations of
3530inputs and outputs, you should experiment with several ways of processing the data to
3531get the best results. For example, you might want to customize the way that the 
3532124
3533numerical target for service quality is binned, or grouped, to target specific business
3534requirements. To do this, you will add a new column to the mining structure that groups
3535numerical data in a different way, and then create a model that uses the new column.
3536You will use these mining models to do some exploration.
3537Finally, when you have learned from the neural network model which factors have the
3538greatest impact for your business question, you will build a separate model for
3539prediction and scoring. You will use the Microsoft Logistic Regression algorithm, which is
3540based on the neural networks model but is optimized for finding a solution based on
3541specific inputs.
3542Steps
35431. Create the basic mining structure, using defaults
35442. Create a copy of the predictable column and modify it by binning the values
35453. Add a new model and use the new column as the output for that model
35464. Create an alias for the modified predictable attribute
35475. Assign a seed so that models are processed the same way; process both models
3548Creating the Default Call Center Structure
35491. In Solution Explorer in SQL Server Data Tools (SSDT), right-click Mining
3550Structures and select New Mining Structure.
35512. On the Welcome to the Data Mining Wizard page, click Next.
35523. On the Select the Definition Method page, verify that From existing relational
3553database or data warehouse is selected, and then click Next.
35544. On the Create the Data Mining Structure page, verify that the option Create
3555mining structure with a mining model is selected.
35565. Click the dropdown list for the option Which data mining technique do you
3557want to use?, then select Microsoft Neural Networks.
3558Because the logistic regression models are based on the neural networks, you can
3559reuse the same structure and add a new mining model.
35606. Click Next.
3561The Select Data Source View page appears.
35627. Under Available data source views, select Call Center, and click Next.
35638. On the Specify Table Types page, select the Case check box next to the
3564FactCallCenter table. Do not select anything for DimDate. Click Next.
35659. On the Specify the Training Data page, select Key next to the column
3566FactCallCenterID.
356710. Select the Predict and Input check boxes.
356811. Select the Key, Input, and Predict check boxes as shown in the following table:
3569To create the default neural network mining structure and model
3570125
3571Tables/Columns Key/Input/Predict
3572AutomaticResponses Input
3573AverageTimePerIssue Input/Predict
3574Calls Input
3575DateKey Do not use
3576DayOfWeek Input
3577FactCallCenterID Key
3578IssuesRaised Input
3579LevelOneOperators Input/Predict
3580LevelTwoOperators Input
3581Orders Input/Predict
3582ServiceGrade Input/Predict
3583Shift Input
3584TotalOperators Do not use
3585WageType Input
3586Note that multiple predictable columns have been selected. One of the strengths
3587of the neural network algorithm is that it can analyze all possible combinations of
3588input and output attributes. You wouldn’t want to do this for a large data set, as
3589it could exponentially increase processing time..
359012. On the Specify Columns' Content and Data Type page, verify that the grid
3591contains the columns, content types, and data types as shown in the following
3592table, and then click Next.
3593Columns Content Type Data Types
3594AutomaticResponses Continuous Long
3595AverageTimePerIssue Continuous Long
3596Calls Continuous Long
3597DayOfWeek Discrete Text
3598FactCallCenterID Key Long
3599126
3600IssuesRaised Continuous Long
3601LevelOneOperators Continuous Long
3602LevelTwoOperators Continuous Long
3603Orders Continuous Long
3604ServiceGrade Continuous Double
3605Shift Discrete Text
3606WageType Discrete Text
360713. On the Create testing set page, clear the text box for the option, Percentage of
3608data for testing. Click Next.
360914. On the Completing the Wizard page, for the Mining structure name, type Call
3610Center.
361115. For the Mining model name, type Call Center Default NN, and then click
3612Finish.
3613The Allow drill through box is disabled because you cannot drill through to data
3614with neural network models.
361516. In Solution Explorer, right-click the name of the data mining structure that you
3616just created, and select Process.
3617Understanding Discretization
3618By default, when you create a neural network model that has a numeric predictable
3619attribute, the Microsoft Neural Network algorithm treats the attribute as a continuous
3620number. For example, the ServiceGrade attribute is a number that theoretically ranges
3621from 0.00 (all calls are answered) to 1.00 (all callers hang up). In this data set, the values
3622have the following distribution:
3623127
3624As a result, when you process the model the outputs might be grouped differently than
3625you expect. For example, if you use clustering to identify the best groups of values, the
3626algorithm divides the values in ServiceGrade into ranges such as this one: 0.0748051948
3627- 0.09716216215. Although this grouping is mathematically accurate, such ranges might
3628not be as meaningful to business users. To group the numerical values differently, you
3629can create a copy or multiple copies of the numerical data column and specify how the
3630data mining algorithm should process the values. For example, you might specify that
3631the algorithm divide the values into no more than five bins.
3632Analysis Services provides a variety of methods for binning or processing numerical data.
3633The following table illustrates the differences between the results when the output
3634attribute ServiceGrade has been processed three different ways:
3635• Treating it as a continuous number.
3636• Having the algorithm use clustering to identify the best arrangement of values.
3637• Specifying that the numbers be binned by the Equal Areas method.
3638Default model (continuous) Binned by clustering Binned by equal areas
3639VALUE SUPPORT
3640Missing 0
36410.09875 120
3642VALUE SUPPORT
3643< 0.0748051948 34
36440.0748051948 -
36450.09716216215
364627
36470.09716216215 -
36480.13297297295
364939
36500.13297297295 -
36510.167499999975
365210
3653>=
36540.167499999975
365510
3656VALUE SUPPORT
3657< 0.07 26
36580.07 -
36590.00
366022
36610.09 -
36620.11
366336
3664>= 0.12 36
3665In this table, the VALUE column shows you how the number for ServiceGrade has been
3666handled. The SUPPORT column shows you how many cases had that value, or that fell in
3667that range.
36681. Use continuous numbers (default)
3669If you used the default method, the algorithm would compute outcomes for 120
3670distinct values, the mean value of which is 0.09875. You can also see the number of
3671missing values.
36722. Bin by clustering
3673When you let the Microsoft Clustering algorithm determine the optional grouping of
3674values, the algorithm would group the values for ServiceGrade into five (5) ranges.
3675The number of cases in each range is not evenly distributed, as you can see from the
3676support column.
36773. Bin by equal areas
3678When you choose this method, the algorithm forces the values into buckets of equal
3679size, which in turn changes the upper and lower bounds of each range. You can 
3680specify the number of buckets, but you want to avoid having two few values in any
3681bucket.
3682Alternatively, rather than using the numeric values, you could add a separate derived
3683column that classifies the service grades into predefined target ranges, such as Best
3684(ServiceGrade <= 0.05), Acceptable (0.10 > ServiceGrade > 0.05), and Poor
3685(ServiceGrade >= 0.10).
3686Creating a Copy of a Column and Changing the Discretization Method
3687In Analysis Services data mining, you can easily change the way that numerical data is
3688binned within a mining structure by adding a copy of the column containing the target
3689data and changing the discretization method.
3690The following procedure describes how to make a copy of the mining column that
3691contains the target attribute, ServiceGrade. You can create multiple copies of any column
3692in a mining structure, including the predictable attribute.
3693You will then customize the grouping of the numeric values in the copied column, to
3694reduce the complexity of the groupings. For this tutorial, you will use the Equal Areas
3695method of discretization, and specify four buckets. The groupings that result from this
3696method are fairly close to the target values of interest to your business users.
36971. In Solution Explorer, double-click the mining structure that you just created.
36982. In the Mining Structure tab, click Add a mining structure column.
36993. In the Select column dialog box, select ServiceGrade from the list in Source
3700column, then click OK.
3701A new column is added to the list of mining structure columns. By default, the
3702new mining column has the same name as the existing column, with a numerical
3703postfix: for example, ServiceGrade 1. You can change the name of this column to
3704be more descriptive.
3705You will also specify the discretization method.
37064. Right-click ServiceGrade 1 and select Properties.
37075. In the Properties window, locate the Name property, and change the name to
3708Service Grade Binned .
37096. A dialog box appears asking whether you want to make the same change to the
3710name of all related mining model columns. Click No.
37117. In the Properties window, locate the section Data Type and expand it if
3712To create a customized copy of a column in the mining structure
3713necessary.
37148. Change the value of the property Content from Continuous to Discretized.
3715The following properties are now available. Change the values of the properties
3716as shown in the following table:
3717Property Default value New value
3718DiscretizationMethod Continuous EqualAreas
3719DiscretizationBucketCount No value 4
37209. In Data Mining Designer, click the Mining Models tab.
3721Notice that when you add a copy of a mining structure column, the usage flag for
3722the copy is automatically set to Ignore. Usually, when you add a copy of a
3723column to a mining structure, you would not use the copy for analysis together
3724with the original column, or the algorithm will find a strong correlation between
3725the two columns that might obscure other relationships.
3726Adding a New Mining Model to the Mining Structure
3727Now that you have created a new grouping for the target attribute, you need to add a
3728new mining model that uses the discretized column. When you are done, the CallCenter
3729mining structure will have two mining models:
3730• The mining model, Call Center Default NN, handles the ServiceGrade values as a
3731continuous range.
3732• You will create a new mining model, Call Center Binned NN, that uses as its target
3733outcomes the values of the ServiceGrade column, distributed into four buckets of
3734equal size.
37351. In Solution Explorer, right-click the mining structure that you just created, and
3736select Open.
37372. Click the Mining Models tab.
37383. Click Create a related mining model.
37394. In the New Mining Model dialog box, for Model name, type Call Center
3740To add a mining model based on the new discretized column
3741Binned NN. In the Algorithm name dropdown list, select Microsoft Neural
3742Network.
37435. In the list of columns contained in the new mining model, locate ServiceGrade,
3744and change the usage from Predict to Ignore.
37456. Similarly, locate ServiceGrade Binned, and change the usage from Ignore to
3746Predict.
3747Ordinarily you cannot compare mining models that use different predictable attributes.
3748However, you can create an alias for a mining model column. That is, you can rename the
3749column, ServiceGrade Binned, within the mining model so that it has the same name as
3750the original column. You can then directly compare these two models in an accuracy
3751chart, even though the data is discretized differently.
37521. In the Mining Models tab, under Structure, select ServiceGrade Binned.
3753Note that the Properties window displays the properties of the object,
3754ScalarMiningStructure column.
37552. Under the column for the mining model, ServiceGrade Binned NN, click the cell
3756corresponding to the column ServiceGrade Binned.
3757Note that now the Properties window displays the properties for the object,
3758MiningModelColumn.
37593. Locate the Name property, and change the value to ServiceGrade.
37604. Locate the Description property and type Temporary column alias.
3761The Properties window should contain the following information:
3762Property Value
3763Description Temporary column alias
3764ID ServiceGrade Binned
3765Modeling Flags
3766Name Service Grade
3767SourceColumn ID Service Grade 1
3768Usage Predict
37695. Click anywhere in the Mining Model tab.
3770The grid is updated to show the new temporary column alias, ServiceGrade,
3771beside the column usage. The grid containing the mining structure and two
3772mining models should look like the following:
3773To add an alias for a mining structure column in a mining model
3774Structure Call Center Default NN Call Center Binned NN
3775 Microsoft Neural
3776Network
3777Microsoft Neural
3778Network
3779AutomaticResponses Input Input
3780AverageTimePerIssue Predict Predict
3781Calls Input Input
3782DayOfWeek Input Input
3783FactCallCenterID Key Key
3784IssuesRaised Input Input
3785LevelOneOperators Input Input
3786LevelTwoOperators Input Input
3787Orders Input Input
3788ServceGrade Binned Ignore Predict (ServiceGrade)
3789ServiceGrade Predict Ignore
3790Shift Input Input
3791Total Operators Input Input
3792WageType Input Input
3793Processing the Model
3794Finally, to ensure that the models you have created can be easily compared, you will set
3795the seed parameter for both the default and binned models. Setting a seed value
3796guarantees that each model starts processing the data from the same point.
3797If you do not specify a numeric value for the seed parameter, SQL Server Analysis
3798Services will generate a seed based on the name of the model. Because the
3799models always have different names, you must set a seed value to ensure that
3800they process data in the same order.
38011. In the Mining Model tab, right-click the column for the model named Call Center
3802- LR, and select Set Algorithm Parameters.
38032. In the row for the HOLDOUT_SEED parameter, click the empty cell under Value,
3804and type 1. Click OK. Repeat this step for each model associated with the
3805Note
3806To specify the seed and process the models
3807133
3808structure.
3809Note
3810The value that you choose as the seed does not matter, as long as you
3811use the same seed for all related models.
38123. In the Mining Models menu, select Process Mining Structure and All Models.
3813Click Yes to deploy the updated data mining project to the server.
38144. In the Process Mining Model dialog box, click Run.
38155. Click Close to close the Process Progress dialog box, and then click Close again
3816in the Process Mining Model dialog box.
3817Now that you have created the two related mining models, you will explore the data to
3818discover relationships in the data.
3819Exploring the Call Center Model (Intermediate Data Mining
3820Tutorial)
3821Now that you have built the exploratory model, you can use it to learn more about your
3822data by using the following tools provided in SQL Server Data Tools (SSDT).
3823• Microsoft Neural Network Viewer: This viewer is available in the Mining Model
3824Viewer tab of Data Mining Designer, and is designed to help you experiment with
3825interactions in the data.
3826• Microsoft Generic Content Tree Viewer: This standard viewer provides in-depth detail
3827about the patterns and statistics discovered by the algorithm when it generated the
3828model.
3829Microsoft Neural Network Viewer
3830The viewer has three panes — Input, Output, and Variables.
3831By using the Output pane, you can select different values for the predictable attribute, or
3832dependent variable. If your model contains multiple predictable attributes, you can select
3833the attribute from the Output Attribute list.
3834The Variables pane compares the two outcomes that you chose in terms of contributing
3835attributes, or variables. The colored bars visually represent how strongly the variable
3836affects the target outcomes. You can also view lift scores for the variables. A lift score is
3837calculated differently depending on which mining model type you are using, but
3838generally tells you the improvement in the model when using this attribute for
3839prediction.
3840134
3841The Input pane lets you add influencers to the model to try out various what-if
3842scenarios.
3843Using the Output Pane
3844In this initial model, you are interested in seeing how various factors affect the grade of
3845service. To do this, you can select Service Grade from the list of output attributes, and
3846then compare different levels of service by selecting ranges from the dropdown lists for
3847Value 1 and Value 2.
38481. For Value 1, select the range with the lowest values. For example, the range 0-0-
38490.7 represents the lowest abandon rates, and therefore the best level of service.
3850Note
3851The exact values in this range may vary depending on how you
3852configured the model.
38532. For Value 2, select the range with the highest values. For example, the range with
3854the value >=0.12 represents the highest abandon rates, and therefore the worst
3855service grade. In other words, 12% of the customers who phoned during this shift
3856hung up before speaking to a representative.
3857The contents of the Variables pane are updated to compare attributes that
3858contribute to the outcome values. Therefore, the left column shows you the
3859attributes that are associated with the best grade of service, and the right column
3860shows you the attributes associated with the worst grade of service.
3861Using the Variables Pane
3862In this model, it appears that Average Time Per Issue is an important factor. This
3863variable indicates the average time that it takes for a call to be answered, regardless of
3864call type.
38651. In the Variables pane, pause the mouse over the colored bar in the first row.
3866This colored bar shows you how strongly Average Time Per Issue contributes
3867toward the service grade. The tooltip shows an overall score, probabilities, and lift
3868scores for each combination of a variable and a target outcome.
38692. In the Variables pane, right-click any colored bar and select Copy.
38703. In an Excel worksheet, right-click any cell and select Paste.
3871The report is pasted as an HTML table, and shows only the scores for each bar.
38724. In a different Excel worksheet, right-click any cell and select Paste Special.
3873The report is pasted as text format, and includes the related statistics described in
3874the next section.
3875To compare lowest and highest service grades
3876To view and copy probability and lift scores for an attribute
3877135
3878Using the Input Pane
3879Suppose that you are interested in looking at the effect of a particular factor, such as the
3880shift, or number of operators. You can select a particular variable by using the Input
3881pane, and the Variables pane is automatically updated to compare the two previously
3882selected groups given the specified variable.
38831. In the Input pane, for attribute, select Shift.
38842. For Value, select AM.
3885The Variables pane updates to show the impact on the model when the shift is
3886AM. All other selections remain the same — you are still comparing the lowest
3887and highest service grades.
38883. For Value, select PM1.
3889The Variables pane updates to show the impact on the model when the shift
3890changes.
38914. In the Input pane, click the next blank row under Attribute, and select Calls. For
3892Value, select the range that indicates the greatest number of calls.
3893A new input condition is added to the list. The Variables pane updates to show
3894the impact on the model for a particular shift when the call volume is highest.
38955. Continue to change the values for Shift and Calls to find any interesting
3896correlations between shift, call volume, and service grade.
3897Note
3898To clear the Input pane so that you can use different attributes, click
3899Refresh viewer content.
3900Interpreting the Statistics Provided in the Viewer
3901Longer waiting times are a strong predictor of a high abandon rate, meaning a poor
3902service grade. This may seem an obvious conclusion; however, the mining model
3903provides you with some additional statistical data to help you interpret these trends.
3904• Score: Value that indicates the overall importance of this variable for discriminating
3905between outcomes. The higher the score, the stronger the effect the variable has on
3906the outcome.
3907• Probability of value 1: Percentage that represents the probability of this value for
3908this outcome.
3909• Probability of value 2: Percentage that represents the probability of this value for
3910this outcome.
3911• Lift for Value 1 and Lift for Value 2: Scores that represents the impact of using this
3912particular variable for predicting the Value 1 and Value 2 outcomes. The higher the
3913score, the better the variable is at predicting the outcomes.
3914To review the effect on service grade by changing input attributes
3915136
3916The following table contains some example values for the top influencers. For example,
3917the Probability of value 1 is 60.6% and Probability of value 2 is 8.30%, meaning that
3918when the Average Time Per Issue was in the range of 44-70 minutes, 60.6% of cases were
3919in the shift with the highest service grades (Value 1), and 8.30% of cases were in the shift
3920with the worse service grades (Value 2).
3921From this information, you can draw some conclusions. Shorter call response time (the
3922range of 44-70) strongly influences better service grade (the range 0.00-0.07). The score
3923(92.35) tells you that this variable is very important.
3924However, as you look down the list of contributing factors, you see some other factors
3925with effects that are more subtle and more difficult to interpret. For example, shift
3926appears to influence service, but the lift scores and the relative probabilities indicate that
3927shift is not a major factor.
3928Attribute Value Favors < 0.07 Favors >= 0.12
3929Average Time Per
3930Issue
393189.087 - 120.000
3932Score: 100
3933Probability of
3934Value1: 4.45 %
3935Probability of
3936Value2: 51.94 %
3937Lift for Value1: 0.19
3938Lift for Value2: 1.94
3939Average Time Per
3940Issue
394144.000 - 70.597
3942Score: 92.35
3943Probability of
3944Value1: 60.06 %
3945Probability of
3946Value2: 8.30 %
3947Lift for Value1: 2.61
3948Lift for Value2: 0.31
3949 
3950137
3951Attribute Value Favors < 0.07 Favors >= 0.12
3952Back to Top
3953Microsoft Generic Content Tree Viewer
3954This viewer can be used to view even more detailed information created by the algorithm
3955when the model is processed. The Microsoft Generic Content Tree Viewer represents
3956the mining model as a series of nodes, wherein each node represents learned knowledge
3957about the training data. This viewer can be used with all models, but the contents of the
3958nodes are different depending in the model type.
3959For neural network models or logistic regression models, you might find the marginal
3960statistics node particularly useful. This node contains derived statistics about the
3961distribution of values in your data. This information can be useful if you want to get a
3962summary of the data without having to write many T-SQL queries. The chart of binning
3963values in the previous topic was derived from the marginal statistics node.
39641. In Data Mining Designer, in the Mining Model Viewer tab, select <mining model
3965name>.
39662. From the Viewer list, select Microsoft Generic Content Tree Viewer.
3967The view of the mining model refreshes to show a node hierarchy in the left-hand
3968pane and an HTML table in the right-hand pane.
39693. In the Node Caption pane, click the node that has the name
397010000000000000000.
3971The topmost node in any model is always the model root node. In a neural
3972network or logistic regression model, the node immediately under that is the
3973marginal statistics node.
39744. In the Node Details pane, scroll down until you find the row,
3975NODE_DISTRIBUTION.
39765. Scroll down through the NODE_DISTRIBUTION table to view the distribution of
3977values as calculated by the neural network algorithm.
3978To use this data in a report, you could select and then copy the information for specific
3979rows, or you can use the following Data Mining Extensions (DMX) query to extract the
3980complete contents of the node.
3981SELECT *
3982FROM [Call Center EQ4].CONTENT
3983To obtain a summary of data values from the mining model
3984138
3985WHERE NODE_NAME = '10000000000000000'
3986You can also use the node hierarchy and the details in the NODE_DISTRIBUTION table to
3987traverse individual paths in the neural network and view statistics from the hidden layer.
3988For more information, see Querying a Neural Network Model (Analysis Services- Data
3989Mining).
3990Adding a Logistic Regression Model to the Call Center Structure
3991(Intermediate Data Mining Tutorial)
3992In addition to analyzing the factors that might affect call center operations, you were
3993also asked to provide some specific recommendations on how the staff can improve
3994service quality. In this task, you will use the same mining structure that you used to build
3995the exploratory model and add a mining model that will be used for creating predictions.
3996In Analysis Services, a logistic regression model is based on the neural networks
3997algorithm, and therefore provides the same flexibility and power as a neural network
3998model. However, logistic regression is particularly well-suited for predicting binary
3999outcomes.
4000For this scenario, you will use the same mining structure that you used for the neural
4001network model. However, you will customize the new model to target your business
4002questions. You are interested in improving service quality and determining how many
4003experienced operators you need, so you will set up your model to predict those values.
4004To ensure that all the models based on the call center data are as similar as possible, you
4005will use the same seed value as before. Setting the seed parameter ensures that the
4006model processes the data from the same starting point, and minimizes variations caused
4007by artifacts in the data.
4008Procedures
40091. In SQL Server Data Tools (SSDT), in Solution Explorer, right-click the mining
4010To add a new mining model to the call center mining structure 
4011structure, Call Center Binned, and select Open Designer.
40122. In Data Mining Designer, click the Mining Models tab.
40133. Click Create a related mining model.
40144. In the New Mining Model dialog box, for Model name, type Call Center - LR.
4015For Algorithm name, select Microsoft Logistic Regression.
40165. Click OK.
4017The new mining model is displayed in the Mining Models tab.
40181. In the column for the new mining model, Call Center - LR, leave Fact CallCenter
4019ID as the key.
40202. Change the value of ServiceGrade and Level Two Operators to Predict.
4021These columns will be used both as input and for prediction. In essence, you are
4022creating two separate models on the same data: one that predicts the number of
4023operators, and one that predicts the service grade.
40243. Change all other columns to Input.
40251. In the Mining Model tab, right-click the column for the model named Call Center
4026- LR, and select Set Algorithm Parameters.
40272. In the row for the HOLDOUT_SEED parameter, click the empty cell under Value,
4028and type 1. Click OK.
4029Note
4030The value that you choose as the seed does not matter, as long as you
4031use the same seed for all related models.
40323. In the Mining Models menu, select Process Mining Structure and All Models.
4033Click Yes to deploy the updated data mining project to the server.
40344. In the Process Mining Model dialog box, click Run.
40355. Click Close to close the Process Progress dialog box, and then click Close again
4036in the Process Mining Model dialog box.
4037To customize the logistic regression model
4038To specify the seed and process the models
4039140
4040Creating Predictions for the Call Center Models (Intermediate
4041Data Mining Tutorial)
4042Now that you have learned something about the interactions between shifts, the number
4043of operators, calls, and service grade, you are ready to create some prediction queries
4044that can be used in business analysis and planning. You will first create some predictions
4045on the exploratory model to test some assumptions. Next, you will create bulk
4046predictions by using the logistic regression model.
4047This lesson assumes that you are already familiar with the concept of prediction queries.
4048Creating Predictions using the Neural Network Model
4049The following example demonstrates how to make a singleton prediction using the
4050neural network model that was created for exploration. Singleton predictions are a good
4051way to try out different values to see the effect in the model. In this scenario, you will
4052predict the service grade for the midnight shift (no day of the week specified) if six
4053experienced operators are on duty.
40541. In SQL Server Data Tools (SSDT), open the solution that contains the model that
4055you want to use.
40562. In Data Mining Designer, click the Mining Model Prediction tab.
40573. In the Mining Model pane, click Select Model.
40584. The Select Mining Model dialog box shows a list of mining structures. Expand
4059the mining structure to view a list of mining models associated with that
4060structure.
40615. Expand the mining structure Call Center Default, and select the neural network
4062model, Call Center - LR.
40636. From the Mining Model menu, select Singleton Query.
4064The Singleton Query Input dialog box appears, with columns mapped to the
4065columns in the mining model.
40667. In the Singleton Query Input dialog box, click the row for Shift, and then select
4067midnight.
40688. Click the row for Lvl 2 Operators, and type 6.
40699. In the bottom half of the Mining Model Prediction tab, click the first row in the
4070grid.
407110. In the Source column, click the down arrow, and select Prediction function. In
4072the Field column, select PredictHistogram.
4073A list of arguments that you can use with this prediction function automatically
4074appears in the Criteria/Arguments box.
407511. Drag the ServiceGrade column from the list of columns in the Mining Model
4076To create a singleton query by using the neural network model
4077141
4078pane to the Criteria/Arguments box.
4079The name of the column is automatically inserted as the argument. You can
4080choose any predictable attribute column to drag into this text box.
408112. Click the button Switch to query results view, in the upper corner of the
4082Prediction Query Builder.
4083The expected results contain the possible predicted values for each service grade given
4084these inputs, together with support and probability values for each prediction. You can
4085return to design view at any time and change the inputs, or add more inputs.
4086Creating Predictions by using a Logistic Regression Model
4087If you already know the attributes that are relevant to the business problem, you can use
4088a logistic regression model to predict the effect of making changes in some attributes.
4089Logistic regression is a statistical method that is commonly used to make predictions
4090based on changes in independent variables: for example, it is used in financial scoring, to
4091predict customer behavior based on customer demographics.
4092In this task, you will learn how to create a data source that will be used for predictions,
4093and then make predictions to help answer several business questions.
4094Generating Data used for Bulk Prediction
4095There are many ways to provide input data: for example, you might import staffing levels
4096from a spreadsheet, and run that data through the model to predict service quality for
4097the next month.
4098In this lesson, you will use the Data Source View designer to create a named query. This
4099named query is a custom Transact-SQL statement that for each shift on the schedule
4100calculates the maximum number of operators on staff, the minimum calls received, and
4101the average number of issues that are generated. You will then join that data to a mining
4102model to make predictions about a series of upcoming dates.
41031. In Solution Explorer, right-click Data Source Views, and then select New Data
4104Source View.
41052. In the Data Source View wizard, select Adventure Works DW Multidimensional
41062012 as the data source, and then click Next.
41073. On the Select Tables and Views page, click Next without selecting any tables.
41084. On the Completing the Wizard page, type the name, Shifts.
4109This name will appear in Solution Explorer as the name of the data source view.
41105. Right-click the empty design pane, then select New Named Query.
41116. In the Create Named Query dialog box, for Name, type Shifts for Call Center.
4112This name will appear in Data Source View designer only as the name of the
4113named query.
4114To generate input data for a bulk prediction query
4115142
41167. Paste the following query statement into the SQL text pane in the lower half of
4117the dialog box.
4118SELECT DISTINCT WageType, Shift,
4119AVG(Orders) as AvgOrders, MIN(Orders) as MinOrders,
4120MAX(Orders) as MaxOrders,
4121AVG(Calls) as AvgCalls, MIN(Calls) as MinCalls, MAX(Calls) as
4122MaxCalls,
4123AVG(LevelTwoOperators) as AvgOperators,
4124MIN(LevelTwoOperators) as MinOperators,
4125MAX(LevelTwoOperators) as MaxOperators,
4126AVG(IssuesRaised) as AvgIssues, MIN(IssuesRaised) as
4127MinIssues, MAX(IssuesRaised) as MaxIssues
4128FROM dbo.FactCallCenter
4129GROUP BY Shift, WageType
41308. In the design pane, right-click the table, Shifts for Call Center, and select Explore
4131Data to preview the data as returned by the T-SQL query.
41329. Right-click the tab, Shifts.dsv (Design), and then click Save to save the new data
4133source view definition.
4134Predicting Service Metrics for Each Shift
4135Now that you have generated some values for each shift, you will use those values as
4136input to the logistic regression model that you built, to generate some predictions that
4137can be used in business planning.
41381. In Data Mining Designer, click the Mining Model Prediction tab.
41392. In the Mining Model pane, click Select Model, and choose Call Center - LR from
4140the list of available models.
41413. From the Mining Model menu, clear the option, Singleton Query. A warning
4142tells you that the singleton query inputs will be lost. Click OK.
4143The Singleton Query Input dialog box is replaced with the Select Input Table(s)
4144dialog box.
41454. Click Select Case Table.
41465. In the Select Table dialog box, select Shifts from the list of data sources. In the
4147Table/View name list, select Shifts for Call Center (it might be automatically
4148selected), and then click OK.
4149The Mining Model Prediction design surface is updated to show mappings that
4150are created based on the names and data types of columns in the input data and
4151To use the new DSV as input to a prediction query 
4152143
4153in the model.
41546. Right-click one of the join lines, and then select Modify Connections.
4155In this dialog box, you can see exactly which columns are mapped and which are
4156not. The mining model contains columns for Calls, Orders, IssuesRaised, and
4157LvlTwoOperators, which you can map to any of the aggregates that you created
4158based on these columns in the source data. In this scenario, you will map to the
4159averages.
41607. Click the empty cell next to LevelTwoOperators, and select Shifts for Call
4161Center.AvgOperators.
41628. Click the empty cell next to Calls, select Shifts for Call Center.AvgCalls. and then
4163click OK.
41641. In the grid at the bottom half of the Prediction Query Builder, click the empty
4165cell under Source, and then select Shifts for Call Center.
41662. In the empty cell under Field, select Shift.
41673. Click the next empty line in the grid and repeat the procedure described above to
4168add another row for WageType.
41694. Click the next empty line in the grid. In the Source column, select Prediction
4170Function. In the Field column, select Predict.
41715. Drag the column ServiceGrade from the Mining Model pane down to the grid,
4172and into the Criteria/Argument cell. In the Alias field, type Predicted Service
4173Grade.
41746. Click the next empty line in the grid. In the Source column, select Prediction
4175Function. In the Field column, select PredictProbability.
41767. Drag the column ServiceGrade from the Mining Model pane down to the grid,
4177and into the Criteria/Argument cell. In the Alias field, type Probability.
41788. Click Switch to query result view to view the predictions.
4179The following table shows sample results for each shift.
4180Shift WageType Predicted Service
4181Grade
4182Probability
4183AM holiday 0.165 0.377520666
4184midnight holiday 0.105 0.364105573
4185PM1 holiday 0.165 0.40056055
4186PM2 holiday 0.165 0.338532973
4187To create the predictions for each shift
4188Shift WageType Predicted Service
4189Grade
4190Probability
4191AM weekday 0.165 0.370847617
4192midnight weekday 0.08 0.352999173
4193PM1 weekday 0.165 0.317419177
4194PM2 weekday 0.105 0.311672027
4195Predicting the Effect of Reduced Response Time on Service Grade
4196You generated some average values for each shift, and used those values as input to the
4197logistic regression model. However, given that the business objective is to keep abandon
4198rate within the range 0.00-0.05, the results are not encouraging.
4199Therefore, based on the original model, which showed a strong influence of response
4200time on service grade, the Operations team decides to run some predictions to assess
4201whether reducing the average time for responding to calls might improve service quality.
4202For example, if you cut the call response time to 90 percent or even to 80 percent of the
4203current call response time, what would happen to service grade values?
4204It is easy to create a data source view (DSV) that calculates the average response times
4205for each shift, and then add columns that calculate 80% or 90% of the average response
4206time. You can then use the DSV as input to the model.
4207Although the exact steps are not shown here, the following table compares the effects
4208on service grade when you reduce response times to 80% or to 90% of current response
4209times.
4210From these results, you might conclude that on targeted shifts you should reduce the
4211response time to 90 percent of the current rate in order to improve service quality.
4212Shift, wage, and day Predicted service
4213quality with current
4214average response time
4215Predicted service
4216quality with 90 percent
4217reduction in response
4218time
4219Predicted service
4220quality with 80 percent
4221reduction in response
4222time
4223Holiday AM 0.165 0.05 0.05
4224Holiday PM1 0.05 0.05 0.05
4225Holiday Midnight 0.165 0.05 0.05
4226145
4227Creating and Querying Data Mining Models
4228with DMX: Tutorials (Analysis Services - Data
4229Mining)
4230After you have created a data mining solution by using Microsoft SQL Server Analysis
4231Services, you can create queries against the data mining models to predict trends,
4232retrieve patterns in the data, and measure the accuracy of the mining models.
4233The step-by-step tutorials in the following list will help you learn how to build and run
4234data mining queries by using Analysis Services so that you can get the most from your
4235data. 
4236In this Section
4237• Bike Buyer DMX Tutorial
4238This tutorial walks you through the creation of a new mining structure and mining
4239models by using the Data Mining Extensions (DMX) language, and explains how to
4240create DMX prediction queries.
4241• Market Basket DMX Tutorial
4242This tutorial uses a typical market basket scenario, where you find associations
4243between the products that customers purchase together. This tutorial also
4244demonstrates how to use nested tables when you create a mining structure. You
4245build and train a model based on this structure, and then create predictions using
4246DMX.
4247• Time Series Prediction DMX Tutorial
4248This tutorial creates a forecasting model to illustrate the use of the CREATE MODEL
4249(DMX) statement. You then add related models and customize the behavior of each
4250by changing the parameters of the Microsoft Time Series algorithm. Finally you
4251create predictions and update the predictions with new data. The ability to update a
4252time series while making predictions was added in SQL Server 2008.
4253147
4254Bike Buyer DMX Tutorial
4255In this tutorial, you will learn how create, train, and explore mining models by using the
4256Data Mining Extensions (DMX) query language. You will then use these mining models to
4257create predictions that determine whether a customer will purchase a bicycle.
4258The mining models will be created from the data contained in the sample database,
4259which stores data for the fictitious company Adventure Works Cycles. Adventure Works
4260Cycles is a large, multinational manufacturing company. The company manufactures and
4261sells metal and composite bicycles to North American, European, and Asian commercial
4262markets. Its base operation is located in Bothell, Washington, with 290 employees, and it
4263has several regional sales teams located throughout their international market base. For
4264more information about the sample database, see Data Mining Concepts.
4265Tutorial Scenario
4266Adventure Works Cycles has decided to extend their data analysis by creating a custom
4267application that uses data mining functionality. Their goal for the custom application is
4268to be able to:
4269• Take as input specific characteristics about a potential customer and predict whether
4270they will buy a bicycle.
4271• Take as input a list of potential customers, as well as characteristics about the
4272customers, and predict which ones will buy a bicycle.
4273In the first case, customer data is provided by a customer registration page, and in the
4274second case, a list of potential customers is provided by the Adventure Works Cycles
4275marketing department.
4276In addition, the marketing department has asked for the ability to group existing
4277customers into categories based on characteristics such as where they live, the number
4278of children they have, and their commute distance. They want to see whether these
4279clusters can be used to help target specific kinds of customers. This will require an
4280additional mining model.
4281Microsoft SQL Server Analysis Services provides several tools that can be used to
4282accomplish these tasks:
4283• The DMX query language
4284• The Microsoft Decision Trees Algorithm and the Microsoft Clustering Algorithm
4285• Query Editor in SQL Server Management Studio
4286Data Mining Extensions (DMX) is a query language provided by Analysis Services that
4287you can use to create and work with mining models. The Microsoft Decision Trees
4288algorithm creates models that can be used to predict whether someone will purchase a
4289bicycle. The resulting model can take an individual customer or a table of customers as
4290an input. The Microsoft Clustering algorithm can create groupings of customers based
4291on shared characteristics. The goal of this tutorial is to provide the DMX scripts that will
4292be used in the custom application.
4293148
4294Mining Structure and Mining Models
4295Before you begin to create DMX statements, it is important to understand the main
4296objects that Analysis Services uses to create mining models. The mining structure is a
4297data structure that defines the data domain from which mining models are built. A single
4298mining structure can contain multiple mining models that share the same domain. A
4299mining model applies a mining model algorithm to the data, which is represented by a
4300mining structure.
4301The building blocks of the mining structure are the mining structure columns, which
4302describe the data that the data source contains. These columns contain information such
4303as data type, content type, and how the data is distributed.
4304Mining models must contain the key column described in the mining structure, as well as
4305a subset of the remaining columns. The mining model defines the usage for each column
4306and defines the algorithm that is used to create the mining model. For example, in DMX
4307you can specify that a column is a Key column or a PREDICT column. If a column is left
4308unspecified, it is assumed to be an input column.
4309In DMX, there are two ways to create mining models. You can either create the mining
4310structure and associated mining model together by using the CREATE MINING MODEL
4311statement, or you can first create a mining structure by using the CREATE MINING
4312STRUCTURE statement, and then add a mining model to the structure by using the
4313ALTER STRUCTURE statement. These methods are described in the following table.
4314CREATE MINING MODEL
4315Use this statement to create a mining structure and associated mining model together
4316using the same name. The mining model name is appended with "Structure" to
4317differentiate it from the mining structure. This statement is useful if you are creating a
4318mining structure that will contain a single mining model.
4319ALTER MINING STRUCTURE
4320Use this statement to add a mining model to a mining structure that already exists on
4321the server. This statement is useful if you want to create a mining structure that contains
4322several different mining models. There are several reasons that you would want to add
4323more than one mining model in a single mining structure. For example, you might
4324create several mining models that use different algorithms to see which algorithm works
4325best. You might create several mining models that use the same algorithm, but with a
4326parameter set differently for each mining model to find the best setting for the
4327parameter.
4328Because you will create a mining structure that contains several mining models, you will
4329use the second method in this tutorial. 
4330
4331150
4332Lesson 1: Creating the Bike Buyer Mining Structure
4333In this lesson, you will create a mining structure that allows you to predict whether a
4334potential customer of Adventure Works Cycles will purchase a bicycle. If you are
4335unfamiliar with mining structures and their role in data mining, see Mining Structures.
4336The Bike Buyer mining structure that you will create in this lesson supports adding
4337mining models based on the Microsoft Clustering Algorithm and the Microsoft Decision
4338Trees Algorithm. In later lessons, you will use the clustering mining models to explore the
4339different ways in which customers can be grouped, and will use decision tree mining
4340models to predict whether or not a potential customer will purchase a bicycle.
4341CREATE MINING STRUCTURE Statement
4342To create a mining structure, you use the CREATE MINING STRUCTURE (DMX) statement.
4343The code in the statement can be broken into the following parts:
4344• Naming the structure.
4345• Defining the key column.
4346• Defining the mining columns.
4347• Defining an optional testing data set.
4348The following is a generic example of the CREATE MINING STRUCTURE statement:
4349CREATE MINING STRUCTURE [<mining structure name>]
4350(
4351 <key column>,
4352 <mining structure columns>
4353)
4354WITH HOLDOUT (<holdout specifier>)
4355The first line of the code defines the name of the structure:
4356CREATE MINING STRUCTURE [<mining structure name>]
4357For information about naming an object in Data Mining Extensions (DMX), see
4358The next line of the code defines the key column for the mining structure, which uniquely
4359identifies an entity in the source data:
4360<key column>,
4361In the mining structure you will create, the customer identifier, CustomerKey, defines an
4362entity in the source data.
4363The next line of the code is used to define the mining columns that will be used by the
4364mining models associated with the mining structure:
4365151
4366<mining structure columns>
4367You can use the DISCRETIZE function within <mining structure columns> to discretize
4368continuous columns by using the following syntax:
4369DISCRETIZE(<method>,<number of buckets>)
4370For more information about discretizing columns, see Discretization Methods. For more
4371information about the types of mining structure columns that you can define, see Mining
4372Structure Columns.
4373The final line of the code defines an optional partition in the mining structure:
4374WITH HOLDOUT (<holdout specifier>)
4375You specify some portion of the data to use for testing mining models that are related to
4376the structure, and the remaining data is used for training the models. By default, Analysis
4377Services creates a test data set that contains 30 percent of all case data. You will add the
4378specification that the test data set should contain 30 percent of the cases up to a
4379maximum of 1000 cases. If 30 percent of the cases is less than 1000, the test data set will
4380contain the smaller amount.
4381Lesson Tasks
4382You will perform the following tasks in this lesson:
4383• Create a new blank query.
4384• Alter the query to create the mining structure.
4385• Execute the query.
4386Creating the Query
4387The first step is to connect to an instance of Analysis Services and create a new DMX
4388query in SQL Server Management Studio.
43891. Open SQL Server Management Studio.
43902. In the Connect to Server dialog box, for Server type, select Analysis Services. In
4391Server name, type LocalHost, or type the name of the instance of Analysis
4392Services that you want to connect to for this lesson. Click Connect.
43933. In Object Explorer, right-click the instance of Analysis Services, point to New
4394Query, and then click DMX to open the Query Editor and a new, blank query.
4395Altering the Query
4396The next step is to modify the CREATE MINING STRUCTURE statement described above
4397to create the Bike Buyer mining structure.
43981. In the Query Editor, copy the generic example of the CREATE MINING
4399To create a new DMX query in SQL Server Management Studio
4400To customize the CREATE MINING STRUCTURE statement
4401152
4402STRUCTURE statement into the blank query.
44032. Replace the following:
4404[<mining structure>]
4405with:
4406[Bike Buyer]
44073. Replace the following:
4408<key column>
4409with:
4410CustomerKey LONG KEY
44114. Replace the following:
4412<mining structure columns>
4413with:
4414 [Age] LONG DISCRETIZED(Automatic,10),
4415 [Bike Buyer] LONG DISCRETE,
4416 [Commute Distance] TEXT DISCRETE,
4417 [Education] TEXT DISCRETE,
4418 [Gender] TEXT DISCRETE,
4419 [House Owner Flag] TEXT DISCRETE,
4420 [Marital Status] TEXT DISCRETE,
4421 [Number Cars Owned] LONG DISCRETE,
4422 [Number Children At Home] LONG DISCRETE,
4423 [Occupation] TEXT DISCRETE,
4424 [Region] TEXT DISCRETE,
4425 [Total Children]LONG DISCRETE,
4426 [Yearly Income] DOUBLE CONTINUOUS
44275. Replace the following:
4428WITH HOLDOUT (holdout specifier>)
4429with:
4430WITH HOLDOUT (30 PERCENT or 1000 CASES)
4431The complete mining structure statement should now be as follows:
4432CREATE MINING STRUCTURE [Bike Buyer]
4433(
4434 [Customer Key] LONG KEY,
4435 [Age]LONG DISCRETIZED(Automatic,10),
4436 [Bike Buyer] LONG DISCRETE,
4437 [Commute Distance] TEXT DISCRETE,
4438 [Education] TEXT DISCRETE,
4439 [Gender] TEXT DISCRETE,
4440 [House Owner Flag] TEXT DISCRETE,
4441 [Marital Status] TEXT DISCRETE,
4442 [Number Cars Owned]LONG DISCRETE,
4443 [Number Children At Home]LONG DISCRETE,
4444 [Occupation] TEXT DISCRETE,
4445 [Region] TEXT DISCRETE,
4446 [Total Children]LONG DISCRETE,
4447 [Yearly Income] DOUBLE CONTINUOUS
4448)
4449WITH HOLDOUT (30 PERCENT or 1000 CASES)
44506. On the File menu, click Save DMXQuery1.dmx As.
44517. In the Save As dialog box, browse to the appropriate folder, and name the file
4452Bike Buyer Structure.dmx.
4453Executing the Query
4454The final step is to execute the query. After a query is created and saved, it needs to be
4455executed. That is, the statement needs to be run in order to create the mining structure
4456on the server. For more information about executing queries in Query Editor, see SQL
4457Server Management Studio Transact SQL Query.
44581. In Query Editor, on the toolbar, click Execute.
4459The status of the query is displayed in the Messages tab at the bottom of Query
4460Editor after the statement finishes executing. Messages should display:
4461Executing the query
4462Execution complete
4463A new structure named Bike Buyer now exists on the server.
4464In the next lesson, you will add mining models to the structure you just created.
4465To execute the query
4466154
4467Lesson 2: Adding Mining Models to the Bike Buyer Mining
4468Structure
4469In this lesson, you will add two mining models to the Bike Buyer mining structure that
4470you created Lesson 1: Creating the Bike Buyer Mining Structure. These mining models
4471will allow you to explore the data using one model, and to create predictions using
4472another.
4473To explore how potential customers can be categorized by their characteristics, you will
4474create a mining model based on the Microsoft Clustering Algorithm. In a later lesson,
4475you will explore how this algorithm finds clusters of customers who share similar
4476characteristics. For example, you might find that certain customers tend to live close to
4477each other, commute by bicycle, and have similar education backgrounds. You can use
4478these clusters to better understand how different customers are related, and to use the
4479information to create a marketing strategy that targets specific customers.
4480To predict whether a potential customer is likely to buy a bicycle, you will create a mining
4481model based on the Microsoft Decision Trees Algorithm. This algorithm looks through
4482the information that is associated with each potential customer, and finds characteristics
4483that are useful in predicting if they will buy a bicycle. It then compares the values of the
4484characteristics of previous bike buyers against new potential customers to determine
4485whether the new potential customers are likely to buy a bicycle.
4486ALTER MINING STRUCTURE Statement
4487In order to add a mining model to the mining structure, you use the ALTER MINING
4488STRUCTURE (DMX) statement. The code in the statement can be broken into the
4489following parts:
4490• Identifying the mining structure
4491• Naming the mining model
4492• Defining the key column
4493• Defining the input and predictable columns
4494• Identifying the algorithm and parameter changes
4495The following is a generic example of the ALTER MINING MODEL statement:
4496ALTER MINING STRUCTURE [<mining structure name>]
4497ADD MINING MODEL [<mining model name>]
4498(
4499 [<key column>],
4500 <mining model columns>,
4501) USING <algorithm name>( <algorithm parameters> )
4502WITH FILTER (<expression>)
4503The first line of the code identifies the existing mining structure to which the mining
4504models will be added:
4505155
4506ALTER MINING STRUCTURE [<mining structure name>]
4507The next line of the code names the mining model that will be added to the mining
4508structure:
4509ADD MINING MODEL [<mining model name>]
4510For information about naming an object in DMX, see Identifiers (DMX).
4511The next lines of the code define columns from the mining structure that will be used by
4512the mining model:
4513[<key column>],
4514<mining model columns>
4515You can only use columns that already exist in the mining structure, and the first column
4516in the list must be the key column from the mining structure.
4517The next line of the code defines the mining algorithm that generates the mining model
4518and the algorithm parameters that you can set on the algorithm:
4519) USING <algorithm name>( <algorithm parameters> )
4520For more information about the algorithm parameters that you can adjust, see Microsoft
4521Decision Trees Algorithm and Microsoft Clustering Algorithm.
4522You can specify that a column in the mining model be used for prediction by using the
4523following syntax:
4524<mining model column> PREDICT
4525The final line of the code, which is optional, defines a filter that is applied when training
4526and testing the model. For more information about how to apply filters to mining
4527models, see Creating Filters for Mining Models (Analysis Services - Data Mining).
4528Lesson Tasks
4529You will perform the following tasks in this lesson:
4530• Add a decision tree mining model to the Bike Buyer structure by using the Microsoft
4531Decision Trees algorithm
4532• Add a clustering mining model to the Bike Buyer structure by using the Microsoft
4533Clustering algorithm
4534• Because you want to see results for all cases, you will not yet add a filter to either
4535model.
4536Adding a Decision Tree Mining Model to the Structure
4537The first step is to add a mining model based on the Microsoft Decision Trees algorithm.
45381. In Object Explorer, right-click the instance of Analysis Services, point to New
4539Query, and then click DMX to open Query Editor and a new, blank query.
45402. Copy the generic example of the ALTER MINING STRUCTURE statement into the
4541To add a decision tree mining model
4542156
4543blank query.
45443. Replace the following:
4545<mining structure name>
4546with:
4547[Bike Buyer]
45484. Replace the following:
4549<mining model name>
4550with:
4551Decision Tree
45525. Replace the following:
4553<mining model columns>,
4554with:
4555(
4556 CustomerKey,
4557 [Age],
4558 [Bike Buyer] PREDICT,
4559 [Commute Distance],
4560 [Education],
4561 [Gender],
4562 [House Owner Flag],
4563 [Marital Status],
4564 [Number Cars Owned],
4565 [Number Children At Home],
4566 [Occupation],
4567 [Region],
4568 [Total Children],
4569 [Yearly Income]
4570In this case, the [Bike Buyer] column has been designated as the PREDICT
4571column.
45726. Replace the following:
4573USING <algorithm name>( <algorithm parameters> )
4574with:
4575Using Microsoft_Decision_Trees
4576WITH DRILLTHROUGH
4577157
4578The WITH DRILLTHROUGH statement allows you to explore the cases that were
4579used to build the mining model.
4580The resulting statement should now be as follows:
4581ALTER MINING STRUCTURE [Bike Buyer]
4582ADD MINING MODEL [Decision Tree]
4583(
4584 CustomerKey,
4585 [Age],
4586 [Bike Buyer] PREDICT,
4587 [Commute Distance],
4588 [Education],
4589 [Gender],
4590 [House Owner Flag],
4591 [Marital Status],
4592 [Number Cars Owned],
4593 [Number Children At Home],
4594 [Occupation],
4595 [Region],
4596 [Total Children],
4597 [Yearly Income]
4598) USING Microsoft_Decision_Trees
4599WITH DRILLTHROUGH
46007. On the File menu, click Save DMXQuery1.dmx As.
46018. In the Save As dialog box, browse to the appropriate folder, and name the file
4602DT_Model.dmx.
46039. On the toolbar, click the Execute button.
4604Adding a Clustering Mining Model to the Structure
4605You can now add a mining model to the Bike Buyer mining structure based on the
4606Microsoft Clustering algorithm. Because the clustering mining model will use all the
4607columns defined in the mining structure, you can use a shortcut to add the model to the
4608structure by omitting the definition of the mining columns.
46091. In Object Explorer, right-click the instance of Analysis Services, point to New
4610Query, and then click DMX to open Query Editor opens and a new, blank query.
4611To add a Clustering mining model
46122. Copy the generic example of the ALTER MINING STRUCTURE statement into the
4613blank query.
46143. Replace the following:
4615<mining structure name>
4616with:
4617[Bike Buyer]
46184. Replace the following:
4619<mining model>
4620with:
4621Clustering Model
46225. Delete the following:
4623(
4624 [<key column>],
4625 <mining model columns>,
4626)
46276. Replace the following:
4628USING <algorithm name>( <algorithm parameters> )
4629with:
4630USING Microsoft_Clustering
4631The complete statement should now be as follows:
4632ALTER MINING STRUCTURE [Bike Buyer]
4633ADD MINING MODEL [Clustering]
4634USING Microsoft_Clustering
46357. On the File menu, click Save DMXQuery1.dmx As.
46368. In the Save As dialog box, browse to the appropriate folder, and name the file
4637Clustering_Model.dmx.
46389. On the toolbar, click the Execute button.
4639In the next lesson, you will process the models and the mining structure.
4640Lesson 3: Processing the Bike Buyer Mining Structure
4641In this lesson, you will use the INSERT INTO statement and the vTargetMail view from the
4642sample database to process the mining structures and mining models that you created in 
4643159
4644When you process a mining structure, Analysis Services reads the source data and builds
4645the structures that support mining models. When you process a mining model, the data
4646defined by the mining structure is passed through the data mining algorithm that you
4647choose. The algorithm searches for trends and patterns, and then stores this information
4648in the mining model. The mining model, therefore, does not contain the actual source
4649data, but instead contains the information that was discovered by the algorithm. For
4650more information about processing mining models, see Processing Data Mining Objects.
4651You need to reprocess a mining structure only if you change a structure column or
4652change the source data. If you add a mining model to a mining structure that has already
4653been processed, you can use the INSERT INTO MINING MODEL statement to train the
4654new mining model.
4655Train Structure Template
4656In order to train the mining structure and its associated mining models, use the INSERT
4657INTO (DMX) statement. The code in the statement can be broken into the following
4658parts:
4659• Identifying the mining structure
4660• Listing the columns in the mining structure
4661• Defining the training data
4662The following is a generic example of the INSERT INTO statement:
4663INSERT INTO MINING STRUCTURE [<mining structure name>]
4664(
4665 <mining structure columns>
4666)
4667OPENQUERY([<datasource>],'<SELECT statement>')
4668The first line of the code identifies the mining structure that you will train:
4669INSERT INTO MINING STRUCTURE [<mining structure name>]
4670The next line of the code specifies the columns that are defined by the mining structure.
4671You must list each column in the mining structure, and each column must map to a
4672column contained within the source query data.
4673(
4674 <mining structure columns>
4675)
4676The final line of the code defines the data that will be used to train the mining structure:
4677OPENQUERY([<datasource>],'<SELECT statement>')
4678160
4679In this lesson, you use OPENQUERY to define the source data. For information about
4680other methods of defining the source query, see <source data query>.
4681Lesson Tasks
4682You will perform the following task in this lesson:
4683• Process the Bike Buyer mining structure
4684Processing the Predictive Mining Structure
46851. In Object Explorer, right-click the instance of Analysis Services, point to New
4686Query, and then click DMX.
4687Query Editor opens and contains a new, blank query.
46882. Copy the generic example of the INSERT INTO statement into the blank query.
46893. Replace the following:
4690[<mining structure name>]
4691with:
4692Bike Buyer
46934. Replace the following:
4694<mining structure columns>
4695with:
4696[Customer Key],
4697[Age],
4698[Bike Buyer],
4699[Commute Distance],
4700[Education],
4701[Gender],
4702[House Owner Flag],
4703[Marital Status],
4704[Number Cars Owned],
4705[Number Children At Home],
4706[Occupation],
4707[Region],
4708[Total Children],
4709[Yearly Income]
47105. Replace the following:
4711OPENQUERY([<datasource>],'<SELECT statement>')
4712To process the mining structure by using INSERT INTO
4713161
4714with:
4715OPENQUERY([Adventure Works DW],
4716 'SELECT CustomerKey, Age, BikeBuyer,
4717 CommuteDistance,EnglishEducation,
4718 Gender,HouseOwnerFlag,MaritalStatus,
4719 NumberCarsOwned,NumberChildrenAtHome,
4720 EnglishOccupation,Region,TotalChildren,
4721 YearlyIncome
4722 FROM dbo.vTargetMail')
4723The OPENQUERY statement references the Adventure Works DW
4724Multidimensional 2012 data source to access the view vTargetMail. The view
4725contains the source data that will be used to train the mining models.
4726The complete statement should now be as follows:
4727INSERT INTO MINING STRUCTURE [Bike Buyer]
4728(
4729 [Customer Key],
4730 [Age],
4731 [Bike Buyer],
4732 [Commute Distance],
4733 [Education],
4734 [Gender],
4735 [House Owner Flag],
4736 [Marital Status],
4737 [Number Cars Owned],
4738 [Number Children At Home],
4739 [Occupation],
4740 [Region],
4741 [Total Children],
4742 [Yearly Income]
4743)
4744OPENQUERY([Adventure Works DW],
4745 'SELECT CustomerKey, Age, BikeBuyer,
4746 CommuteDistance,EnglishEducation,
4747 Gender,HouseOwnerFlag,MaritalStatus,
4748162
4749 NumberCarsOwned,NumberChildrenAtHome,
4750 EnglishOccupation,Region,TotalChildren,
4751 YearlyIncome
4752 FROM dbo.vTargetMail')
47536. On the File menu, click Save DMXQuery1.dmx As.
47547. In the Save As dialog box, browse to the appropriate folder, and name the file
4755Process Bike Buyer Structure.dmx.
47568. On the toolbar, click the Execute button.
4757In the next lesson, you will explore content in the mining models you added to the
4758mining structure in this lesson.
4759Lesson 4: Browsing the Bike Buyer Mining Models
4760In this lesson, you will use the SELECT (DMX) statement to explore the content in the
4761decision tree and clustering mining models that you created in Lesson 2: Adding Mining
4762Models to the Predictive Mining Structure.
4763The columns contained in a mining model are not the columns defined by the mining
4764structure, but instead are a specific set of columns that describe the trends and patterns
4765that are found by the algorithm. These mining model columns are described in the
4766DMSCHEMA_MINING_MODEL_CONTENT Rowset schema rowset. For example, the
4767MODEL_NAME column in the content schema rowset contains the name of the mining
4768model. For a clustering mining model, the NODE_CAPTION column contains the name of
4769each cluster, and the NODE_DESCRIPTION column contains a description of the
4770characteristics of each cluster. You can browse these columns by using the SELECT FROM
4771<model>.CONTENT statement in DMX. You can also use this statement to explore the
4772data that was used to create the mining model. Drillthrough must be enabled on the
4773mining structure in order to use this statement. For more information about the
4774statement, see Lesson 5: Executing Prediction Queries.
4775You can also return all the states of a discrete column by using the SELECT DISTINCT
4776statement. For example, if you perform this operation on a gender column, the query will
4777return male and female.
4778Lesson Tasks
4779You will perform the following tasks in this lesson:
4780• Explore the content contained within the mining models
4781• Return the cases from the source data that was used to train the mining models
4782• Explore the different states available for a specific discrete column
4783Returning the Content of a Mining Model
4784163
4785In this lesson, you use the SELECT FROM <model>.CONTENT (DMX) statement to return
4786the contents of the clustering model.
4787The following is a generic example of the SELECT FROM <model>.CONTENT statement:
4788SELECT <select list> FROM [<mining model>].CONTENT
4789WHERE <where clause>
4790The first line of the code defines the columns to return from the mining model content,
4791and the mining model they are associated with:
4792SELECT <select list> FROM [<mining model].CONTENT
4793The .CONTENT clause next to the name of the mining model specifies that you are
4794returning content from the mining model. For more information about the columns
4795contained in the mining model, see DMSCHEMA_MINING_MODEL_CONTENT Rowset.
4796You can optionally use the final line of the code to filter the results returned by the
4797statement:
4798WHERE <where clause>
4799For example, if you want to restrict the results of the query to only the clusters that
4800contain a high number of cases, you can add the following WHERE clause to the SELECT
4801statement:
4802WHERE NODE_SUPPORT > 100
4803For more information about using the WHERE statement, see SELECT (DMX).
48041. In Object Explorer, right-click the instance of Analysis Services, point to New
4805Query, and then click DMX.
4806Query Editor opens and contains a new, blank query.
48072. Copy the generic example of the SELECT FROM <model>.CONTENT statement
4808into the blank query.
48093. Replace the following:
4810<select list>
4811with:
4812*
4813You can also replace * with a list of any of the columns contained within the
4814DMSCHEMA_MINING_MODEL_CONTENT Rowset.
48154. Replace the following:
4816[<mining model>]
4817with:
4818[Clustering]
4819To return the content of the clustering mining model
4820164
4821The complete statement should now be as follows:
4822SELECT * FROM [Clustering].CONTENT
48235. On the File menu, click Save DMXQuery1.dmx As.
48246. In the Save As dialog box, browse to the appropriate folder, and name the file
4825SELECT_CONTENT.dmx.
48267. On the toolbar, click the Execute button.
4827The query returns the content of the mining model.
4828Use Drillthrough
4829The next step is to use the drillthrough statement to return a sampling of the cases that
4830were used to train the decision tree mining model. In this lesson, you use the SELECT
4831FROM <model>.CASES (DMX) statement to return the contents of the decision tree
4832model.
4833The following is a generic example of the SELECT FROM <model>.CASES statement:
4834SELECT <select list>
4835FROM [<mining model>].CASES
4836WHERE IsInNode('<node id>')
4837The first line of the code defines the columns to return from the source data, and the
4838mining model they are contained within:
4839SELECT <select list> FROM [<mining model>].CASES
4840The .CASES clause specifies that you are performing a drillthrough query. In order to use
4841drillthrough you must enable drillthrough when you create the mining model.
4842The final line of the code is optional and specifies the node in the mining model that you
4843are requesting cases from:
4844WHERE IsInNode('<node id>')
4845For more information about using the WHERE statement with IsInNode, see SELECT
4846FROM <model>.CASES (DMX).
48471. In Object Explorer, right-click the instance of Analysis Services, point to New
4848Query, and then click DMX.
4849Query Editor opens and contains a new, blank query.
48502. Copy the generic example of the SELECT FROM <model>.CASES statement into
4851the blank query.
48523. Replace the following:
4853<select list>
4854with:
4855To return the cases that were used to train the mining model
4856165
4857*
4858You can also replace * with a list of any of the columns contained within the
4859source data (such as [Bike Buyer]).
48604. Replace the following:
4861[<mining model>]
4862with:
4863[Decision Tree]
4864The complete statement should now be as follows:
4865SELECT *
4866FROM [Decision Tree].CASES
48675. On the File menu, click Save DMXQuery1.dmx As.
48686. In the Save As dialog box, browse to the appropriate folder, and name the file
4869SELECT_DRILLTHROUGH.dmx.
48707. On the toolbar, click the Execute button.
4871The query returns the source data that was used to train the decision tree mining
4872model.
4873Return the States of a Discrete Mining Model Column
4874The next step is to use the SELECT DISTINCT statement to return the different possible
4875states in the specified mining model column.
4876The following is a generic example of the SELECT DISTINCT statement:
4877SELECT DISTINCT [<column>]
4878FROM [<mining model>]
4879The first line of the code defines the mining model columns for which the states are
4880returned:
4881SELECT DISTINCT [<column>]
4882You must include DISTINCT in order to return all of the states of the column. If you
4883exclude DISTINCT, then the full statement becomes a shortcut for a prediction and
4884returns the most likely state of the specified column. For more information, see SELECT
4885(DMX).
48861. In Object Explorer, right-click the instance of Analysis Services, point to New
4887Query, and then click DMX.
4888Query Editor opens and contains a new, blank query.
48892. Copy the generic example of the SELECT Distinct statement into the blank query.
48903. Replace the following:
4891To return the states of a discrete column
4892166
4893[<column,name>
4894with:
4895[Bike Buyer]
48964. Replace the following:
4897[<mining model>]
4898with:
4899[Decision Tree]
4900The complete statement should now be as follows:
4901SELECT DISTINCT [Bike Buyer]
4902FROM [Decision Tree]
49035. On the File menu, click Save DMXQuery1.dmx As.
49046. In the Save As dialog box, browse to the appropriate folder, and name the file
4905SELECT_DISCRETE.dmx.
49067. On the toolbar, click the Execute button.
4907The query returns the possible states of the Bike Buyer column.
4908In the next lesson, you will predict whether potential customers will be bike buyers by
4909using the decision tree mining model.
4910Lesson 5: Executing Prediction Queries
4911In this lesson, you will use the SELECT FROM <model> PREDICTION JOIN (DMX) form of
4912the SELECT statement to create two different types of predictions based on the decision
4913tree model you created in Lesson 2: Adding Mining Models to the Association Mining
4914Structure. These prediction types are defined below.
4915Singleton Query
4916Use a singleton query to provide ad hoc values when making predictions. For example,
4917you can determine whether a single customer is likely to be a bike buyer, by passing
4918inputs to the query such as the commute distance, the area code, or the number of
4919children of the customer. The singleton query returns a value that indicates how likely
4920the person is to purchase a bicycle based on those inputs.
4921Batch Query
4922Use a batch query to determine who in a table of potential customers is likely to
4923purchase a bicycle. For example, if your marketing department provides you with a list
4924of customers and customer attributes, then you can use a batch prediction to determine
4925who from the table is likely to purchase a bicycle.
4926167
4927The SELECT FROM <model> PREDICTION JOIN (DMX) form of the SELECT statement
4928contains three parts:
4929• A list of the mining model columns and prediction functions that are returned in the
4930results. The results can also contain input columns from the source data.
4931• The source query defining the data that is being used to create a prediction. For
4932example, in a batch query this could be a list of customers.
4933• A mapping between the mining model columns and the source data. If these names
4934match, then you can use NATURAL syntax and leave out the column mappings.
4935You can further enhance the query by using prediction functions. Prediction functions
4936provide additional information, such as the probability of a prediction occurring, and
4937provide support for the prediction in the training dataset. For more information about
4938prediction functions, see Market Basket DMX Tutorial.
4939The predictions in this tutorial are based on the ProspectiveBuyer table in the
4940sample database. The ProspectiveBuyer table contains a list of potential customers and
4941their associated characteristics. The customers in this table are independent of the
4942customers that were used to create the decision tree mining model.
4943You can also create predictions by using the prediction query builder in SQL Server Data
4944Tools (SSDT). For more information, see Using the Prediction Query Builder to Create
4945DMX Prediction Queries.
4946Lesson Tasks
4947You will perform the following tasks in this lesson:
4948• Create a singleton query to determine whether a specific customer is likely to
4949purchase a bicycle.
4950• Create a batch query to determine which customers, listed in a table of customers,
4951are likely to purchase a bicycle.
4952Singleton Query
4953The first step is to use the SELECT FROM <model> PREDICTION JOIN (DMX) in a
4954singleton prediction query. The following is a generic example of the singleton
4955statement:
4956SELECT <select list> FROM [<mining model name>]
4957NATURAL PREDICTION JOIN
4958(SELECT '<value>' AS [<column>], ...)
4959AS [<input alias>]
4960The first line of the code defines the columns from the mining model that the query
4961should return, and specifies the mining model that is used to generate the prediction:
4962SELECT <select list> FROM [<mining model name>]
4963The next lines of the code define the characteristics of the customer that you use to
4964create a prediction:
4965168
4966NATURAL PREDICTION JOIN
4967(SELECT '<value>' AS [<column>], ...)
4968AS [<input alias>]
4969ORDER BY <expression>
4970If you specify NATURAL PREDICTION JOIN, the server matches each column from the
4971model to a column from the input, based on column names. If column names do not
4972match, the columns are ignored.
49731. In Object Explorer, right-click the instance of Analysis Services, point to New
4974Query, and then click DMX.
4975Query Editor opens and contains a new, blank query.
49762. Copy the generic example of the singleton statement into the blank query.
49773. Replace the following:
4978<select list>
4979with:
4980[Bike Buyer] AS Buyer, PredictHistogram([Bike Buyer]) AS
4981Statistics
4982The AS statement is used to alias columns returned by the query. The
4983PredictHistogram function returns statistics about the prediction, including the
4984probability and the support. For more information about the functions that can
4985be used in a prediction statement, see Functions (DMX).
49864. Replace the following:
4987[<mining model>]
4988with:
4989[Decision Tree]
49905. Replace the following:
4991(SELECT '<value>' AS [<column name>], ...) AS t
4992with:
4993(SELECT 35 AS [Age],
4994 '5-10 Miles' AS [Commute Distance],
4995 '1' AS [House Owner Flag],
4996 2 AS [Number Cars Owned],
4997 2 AS [Total Children]) AS t
4998The complete statement should now be as follows:
4999SELECT
5000To create a singleton prediction query
5001169
5002 [Bike Buyer] AS Buyer,
5003 PredictHistogram([Bike Buyer]) AS Statistics
5004FROM
5005 [Decision Tree]
5006NATURAL PREDICTION JOIN
5007(SELECT 35 AS [Age],
5008 '5-10 Miles' AS [Commute Distance],
5009 '1' AS [House Owner Flag],
5010 2 AS [Number Cars Owned],
5011 2 AS [Total Children]) AS t
50126. On the File menu, click Save DMXQuery1.dmx As.
50137. In the Save As dialog box, browse to the appropriate folder, and name the file
5014Singleton_Query.dmx.
50158. On the toolbar, click the Execute button.
5016The query returns a prediction about whether a customer with the specified
5017characteristics will purchase a bicycle, as well as statistics about that prediction.
5018Batch Query
5019The next step is to use the SELECT FROM <model> PREDICTION JOIN (DMX) in a batch
5020prediction query. The following is a generic example of a batch statement:
5021SELECT TOP <number> <select list>
5022FROM [<mining model name>]
5023PREDICTION JOIN
5024OPENQUERY([<datasource>],'<SELECT statement>')
5025 AS [<input alias>]
5026ON <on clause, mapping,>
5027WHERE <where clause, boolean expression,>
5028ORDER BY <expression>
5029As in the singleton query, the first two lines of the code define the columns from mining
5030model that the query returns, as well as the name of the mining model that is used to
5031generate the prediction. The TOP <number> statement specifies that the query will only
5032return the number or the results specified by <number>.
5033The next lines of the code define the source data that the predictions are based on:
5034OPENQUERY([<datasource>],'<SELECT statement>')
5035 AS [<input alias>]
5036170
5037You have several options for the method of retrieving the source data, but in this tutorial,
5038you will use OPENQUERY. For more information about the options available, see <source
5039data query>.
5040The next line defines the mapping between the source columns in the mining model and
5041the columns in the source data:
5042ON <column mappings>
5043The WHERE clause filters the results returned by the prediction query:
5044WHERE <where clause, boolean expression,>
5045The last and optional line of the code specifies the column that the results will be
5046ordered by:
5047ORDER BY <expression> [DESC|ASC]
5048Use ORDER BY in combination with the TOP <number> statement, to filter the results
5049that are returned. For example, in this prediction you will return the top ten bike buyers,
5050ordered by the probability of the prediction being correct. You can use [DESC|ASC]
5051syntax to control the order in which the results are displayed.
50521. In Object Explorer, right-click the instance of Analysis Services, point to New
5053Query, and then click DMX.
5054Query Editor opens and contains a new, blank query.
50552. Copy the generic example of the batch statement into the blank query.
50563. Replace the following:
5057<select list>
5058with:
5059SELECT
5060 TOP 10
5061 t.[LastName],
5062 t.[FirstName],
5063 [Decision Tree].[Bike Buyer],
5064 PredictProbability([Bike Buyer])
5065The TOP 10 specifies that only the top ten results will be returned by the query.
5066The ORDER BY statement in this query orders the results by the probability of the
5067prediction being correct, so only the ten most likely results will be returned.
50684. Replace the following:
5069[<mining model>]
5070with:
5071To create a batch prediction query
5072171
5073[Decision Tree]
50745. Replace the following:
5075OPENQUERY([<datasource>],'<SELECT statement>')
5076with:
5077 OPENQUERY([Adventure Works DW2008R2],
5078 'SELECT
5079 [LastName],
5080 [FirstName],
5081 [MaritalStatus],
5082 [Gender],
5083 [YearlyIncome],
5084 [TotalChildren],
5085 [NumberChildrenAtHome],
5086 [Education],
5087 [Occupation],
5088 [HouseOwnerFlag],
5089 [NumberCarsOwned]
5090 FROM
5091 [dbo].[ProspectiveBuyer]
5092 ') AS t
50936. Replace the following:
5094<ON clause, mapping,>
5095WHERE <where clause, boolean expression,>
5096ORDER BY <expression>
5097with:
5098[Decision Tree].[Marital Status] = t.[MaritalStatus] AND
5099 [Decision Tree].[Gender] = t.[Gender] AND
5100 [Decision Tree].[Yearly Income] = t.[YearlyIncome] AND
5101 [Decision Tree].[Total Children] = t.[TotalChildren] AND
5102 [Decision Tree].[Number Children At Home] =
5103t.[NumberChildrenAtHome] AND
5104 [Decision Tree].[Education] = t.[Education] AND
5105 [Decision Tree].[Occupation] = t.[Occupation] AND
5106 [Decision Tree].[House Owner Flag] = t.[HouseOwnerFlag] AND
5107172
5108 [Decision Tree].[Number Cars Owned] = t.[NumberCarsOwned]
5109WHERE [Decision Tree].[Bike Buyer] =1
5110ORDER BY PredictProbability([Bike Buyer]) DESC
5111Specify DESC in order to list the results with the highest probability first.
5112The complete statement should now be as follows:
5113SELECT
5114 TOP 10
5115 t.[LastName],
5116 t.[FirstName],
5117 [Decision Tree].[Bike Buyer],
5118 PredictProbability([Bike Buyer])
5119FROM
5120 [Decision Tree]
5121PREDICTION JOIN
5122 OPENQUERY([Adventure Works DW2008R2],
5123 'SELECT
5124 [LastName],
5125 [FirstName],
5126 [MaritalStatus],
5127 [Gender],
5128 [YearlyIncome],
5129 [TotalChildren],
5130 [NumberChildrenAtHome],
5131 [Education],
5132 [Occupation],
5133 [HouseOwnerFlag],
5134 [NumberCarsOwned]
5135 FROM
5136 [dbo].[ProspectiveBuyer]
5137 ') AS t
5138ON
5139 [Decision Tree].[Marital Status] = t.[MaritalStatus] AND
5140 [Decision Tree].[Gender] = t.[Gender] AND
5141173
5142 [Decision Tree].[Yearly Income] = t.[YearlyIncome] AND
5143 [Decision Tree].[Total Children] = t.[TotalChildren] AND
5144 [Decision Tree].[Number Children At Home] =
5145t.[NumberChildrenAtHome] AND
5146 [Decision Tree].[Education] = t.[Education] AND
5147 [Decision Tree].[Occupation] = t.[Occupation] AND
5148 [Decision Tree].[House Owner Flag] = t.[HouseOwnerFlag] AND
5149 [Decision Tree].[Number Cars Owned] = t.[NumberCarsOwned]
5150WHERE [Decision Tree].[Bike Buyer] =1
5151ORDER BY PredictProbability([Bike Buyer]) DESC
51527. On the File menu, click Save DMXQuery1.dmx As.
51538. In the Save As dialog box, browse to the appropriate folder, and name the file
5154Batch_Prediction.dmx.
51559. On the toolbar, click the Execute button.
5156The query returns a table containing customer names, a prediction of whether
5157each customer will purchase a bicycle, and the probability of the prediction.
5158This is the last step in the Bike Buyer tutorial. You now have a set of mining models that
5159you can use to explore similarities between you customers and predict whether potential
5160customers will purchase a bicycle.
5161Market Basket DMX Tutorial
5162In this tutorial, you will learn how to create, train, and explore mining models by using
5163the Data Mining Extensions (DMX) query language. You will then use these mining
5164models to create predictions that describe which products tend to be purchased at the
5165same time.
5166The mining models will be created from the data contained in the sample database,
5167which stores data for the fictitious company Adventure Works Cycles. Adventure Works
5168Cycles is a large, multinational manufacturing company. The company manufactures and
5169sells metal and composite bicycles to North American, European, and Asian commercial
5170markets. Its base operation is located in Bothell, Washington, with 290 employees, and it
5171has several regional sales teams are located throughout their international market base.
5172Tutorial Scenario
5173Adventure Works Cycles has decided to create a custom application that employs data
5174mining functionality to predict what types of products their customers tend to purchase
5175at the same time. The goal for the custom application is to be able to specify a set of
5176products, and predict what additional products will be purchased with the specified 
5177174
5178products. Adventure Works Cycles will then use this information to add a "suggest"
5179feature to their website, and also to better organize the way that they present
5180information to their customers.
5181Microsoft SQL Server Analysis Services provides several tools that can be used to
5182accomplish this task:
5183• The DMX query language
5184• The Microsoft Association Algorithm
5185• Query Editor in SQL Server Management Studio
5186Data Mining Extensions (DMX) is a query language provided by Analysis Services that
5187you can use to create and work with mining models. The Microsoft Association algorithm
5188creates models that can predict the products that are likely to be purchased together.
5189The goal of this tutorial is to provide the DMX queries that will be used in the custom
5190application.
5191For more information: Working with Data Mining
5192Mining Structure and Mining Models
5193Before you begin to create DMX statements, it is important to understand the main
5194objects that Analysis Services uses to create mining models. The mining structure is a
5195data structure that defines the data domain from which mining models are built. A single
5196mining structure can contain multiple mining models that share the same domain. A
5197mining model applies a mining model algorithm to the data, which is represented by a
5198mining structure.
5199The building blocks of the mining structure are the mining structure columns, which
5200describe the data that the data source contains. These columns contain information such
5201as data type, content type, and how the data is distributed.
5202Mining models must contain the key column described in the mining structure, as well as
5203a subset of the remaining columns. The mining model defines the usage for each column
5204and defines the algorithm that is used to create the mining model. For example, in DMX
5205you can specify that a column is a Key column or a PREDICT column. If a column is left
5206unspecified, it is assumed to be an input column.
5207In DMX, there are two ways to create mining models. You can either create the mining
5208structure and associated mining model together by using the CREATE MINING MODEL
5209statement, or you can first create a mining structure by using the CREATE MINING
5210STRUCTURE statement, and then add a mining model to the structure by using the
5211ALTER STRUCTURE statement. These methods are described below.
5212CREATE MINING MODEL
5213Use this statement to create a mining structure and associated mining model together
5214using the same name. The mining model name is appended with "Structure" to
5215differentiate it from the mining structure.
5216This statement is useful if you are creating a mining structure that will contain a single 
5217175
5218mining model.
5219For more information, see CREATE MINING MODEL (DMX).
5220CREATE MINING STRUCTURE
5221Use this statement to create a new mining structure without any models.
5222When you use CREATE MINING STRUCTURE, you can also create a holdout data set that
5223can be used for testing any models that are based on the same mining structure.
5224For more information, see CREATE MINING STRUCTURE (DMX).
5225ALTER MINING STRUCTURE
5226Use this statement to add a mining model to a mining structure that already exists on
5227the server.
5228There are several reasons that you would want to add more than one mining model in a
5229single mining structure. For example, you might create several mining models using
5230different algorithms to see which one works best. Alternatively, you might create several
5231mining models using the same algorithm, but with a parameter set differently for each
5232mining model to find the best setting for that parameter.
5233For more information, see ALTER MINING STRUCTURE (DMX).
5234Because you will create a mining structure that contains several mining models, you will
5235use the second method in this tutorial.
5236For More Information
5237Data Mining Extensions (DMX) Reference, Understanding the Select Statement (DMX),
5238Prediction Queries (DMX)
5239What You Will Learn
5240This tutorial is divided into the following lessons:
5241Lesson 1: Creating the Association Mining Structure
5242In this lesson, you will learn how to use the CREATE statement to create mining
5243structures.
5244Lesson 2: Adding Mining Models to the Association Mining Structure
5245In this lesson, you will learn how to use the ALTER statement to add mining models to a
5246mining structure.
5247Lesson 3: Processing the Association Mining Structure
5248In this lesson, you will learn how to use the INSERT INTO statement to process mining
5249structures and their associated mining models.
5250Lesson 4: Creating Association Predictions
5251In this lesson, you will learn how to use the PREDICTION JOIN statement to create
5252predictions against mining models.
5253Requirements
5254176
5255Before doing this tutorial, make sure that the following are installed:
5256• Microsoft SQL Server
5257• Microsoft SQL Server Analysis Services
5258• The database
5259Lesson 1: Creating the Market Basket Mining Structure
5260In this lesson, you will create a mining structure that allows you to predict what
5261Adventure Works Cycles products a customer tends to purchase at the same time. If you
5262are unfamiliar with mining structures and their role in data mining, see Lesson 2: Adding
5263Mining Models to the Market Basket Mining Structure.
5264The association mining structure that you will create in this lesson supports adding
5265mining models based on the Microsoft Association Algorithm. In later lessons, you will
5266use the mining models to predict the type of products a customer tends to purchase at
5267the same time, which is called a market basket analysis. For example, you may find that
5268customers tend to buy mountain bikes, bike tires, and helmets at the same time.
5269In this lesson, the mining structure is defined by using nested tables. Nested tables are
5270used because the data domain that will be defined by the structure is contained within
5271two different source tables. For more information on nested tables, see Nested Tables.
5272CREATE MINING STRUCTURE Statement
5273In order to create a mining structure containing a nested table, you use the CREATE
5274MINING STRUCTURE (DMX) statement. The code in the statement can be broken into the
5275following parts:
5276• Naming the structure
5277• Defining the key column
5278177
5279• Defining the mining columns
5280• Defining the nested table columns
5281The following is a generic example of the CREATE MINING STRUCTURE statement:
5282CREATE MINING STRUCTURE [<Mining Structure Name>]
5283(
5284 <key column>,
5285 <mining structure columns>,
5286 <table columns>
5287 ( <nested key column>,
5288 <nested mining structure columns> )
5289)
5290The first line of the code defines the name of the structure:
5291CREATE MINING STRUCTURE [Mining Structure Name]
5292For information about naming an object in DMX, see Identifiers (DMX).
5293The next line of the code defines the key column for the mining structure, which uniquely
5294identifies an entity in the source data:
5295<key column>
5296The next line of the code is used to define the mining columns that will be used by the
5297mining models associated with the mining structure:
5298<mining structure columns>
5299The next lines of the code define the nested table columns:
5300<table columns>
5301( <nested key column>,
5302 <nested mining structure columns> )
5303Lesson Tasks
5304You will perform the following tasks in this lesson:
5305• Create a new blank query
5306• Alter the query to create the mining structure
5307178
5308• Execute the query
5309Creating the Query
5310The first step is to connect to an instance of Analysis Services and create a new DMX
5311query in SQL Server Management Studio.
53121. Open SQL Server Management Studio.
53132. In the Connect to Server dialog box, for Server type, select Analysis Services. In
5314Server name, type LocalHost, or the name of the instance of Analysis Services
5315that you want to connect to for this lesson. Click Connect.
53163. In Object Explorer, right-click the instance of Analysis Services, point to New
5317Query, and then click DMX.
5318Query Editor opens and contains a new, blank query.
5319Altering the Query
5320The next step is to modify the CREATE MINING STRUCTURE statement described above
5321to create the Market Basket mining structure.
53221. In Query Editor, copy the generic example of the CREATE MINING STRUCTURE
5323statement into the blank query.
53242. Replace the following:
5325[mining structure name]
5326with:
5327[Market Basket]
53283. Replace the following:
5329<key column>
5330with:
5331OrderNumber TEXT KEY
53324. Replace the following:
5333<table columns>
5334( <nested key column>,
5335 <nested mining structure columns> )
5336with:
5337 [Products] TABLE (
5338 [Model] TEXT KEY
5339 )
5340To create a new DMX query in SQL Server Management Studio
5341To customize the CREATE MINING STRUCTURE statement
5342179
5343The TEXT KEY language specifies that the Model column is the key column for the
5344nested table.
5345The complete mining structure statement should now be as follows:
5346CREATE MINING STRUCTURE [Market Basket] (
5347 OrderNumber TEXT KEY,
5348 [Products] TABLE (
5349 [Model] TEXT KEY
5350 )
5351)
53525. On the File menu, click Save DMXQuery1.dmx As.
53536. In the Save As dialog box, browse to the appropriate folder, and name the file
5354Market Basket Structure.dmx.
5355Executing the Query
5356The final step is to execute the query. After a query is created and saved, it needs to be
5357executed (that is, the statement needs to be run) in order to create the mining structure
5358on the server. For more information about executing queries in Query Editor, see
5359• In Query Editor, on the toolbar, click Execute.
5360The status of the query is displayed in the Messages tab at the bottom of Query
5361Editor after the statement finishes executing. Messages should display:
5362Executing the query
5363Execution complete
5364A new structure named Market Basket now exists on the server.
5365In the next lesson, you will add mining models to the Market Basket mining structure you
5366just created.
5367Lesson 2: Adding Mining Models to the Market Basket Mining
5368Structure
5369In this lesson, you will add two mining models to the Market Basket mining structure that
5370you created in Lesson 3: Processing the Market Basket Mining Structure. These mining
5371models will allow you to create predictions.
5372To execute the query
5373180
5374To predict the types of products that customers tend to purchase at the same time, you
5375will create two mining models using the Microsoft Association Algorithm and two
5376different values for the MINIMUM_PROBABILTY parameter.
5377MINIMUM_PROBABILTY is a Microsoft Association algorithm parameter that helps to
5378determine the number of rules that a mining model will contain by specifying the
5379minimum probability that a rule must have. For example, setting this value to 0.4
5380specifies that a rule can be generated only if the combination of products that the rule
5381describes has at least a forty percent probability of occurring.
5382You will view the effect of changing the MINIMUM_PROBABILTY parameter in a later
5383lesson.
5384ALTER MINING STRUCTURE Statement
5385To add a mining model that contains a nested table to a mining structure, you use the
5386ALTER MINING STRUCTURE (DMX) statement. The code in the statement can be broken
5387into the following parts:
5388• Identifying the mining structure
5389• Naming the mining model
5390• Defining the key column
5391• Defining the input and predictable columns
5392• Defining the nested table columns
5393• Identifying the algorithm and parameter changes
5394The following is a generic example of the ALTER MINING STRUCTURE statement that
5395adds a mining model to a structure that includes nested table columns:
5396ALTER MINING STRUCTURE [<Mining Structure Name>]
5397ADD MINING MODEL [<Mining Model Name>]
5398(
5399 [<key column>],
5400 <mining model column> <usage>,
5401 <table columns>
5402 ( [<nested key column>],
5403 <nested mining model columns> )
5404) USING <algorithm>( <algorithm parameters> )
5405The first line of the code identifies the existing mining structure to which the mining
5406model will be added:
5407ALTER MINING STRUCTURE [<mining structure name>]
5408The next line of the code names the mining model that will be added to the mining
5409structure:
5410ADD MINING MODEL [<mining model name>]
5411181
5412The next lines of the code define the columns in the mining structure that will be used by
5413the mining model:
5414[<key column>],
5415<mining model columns> <usage>,
5416You can only use columns that already exist in the mining structure.
5417The first column in the list of mining model columns must be the key column in the
5418mining structure. However, you do not have to type KEY after the key column to specify
5419usage. That is because you have already defined the column as a key when you created
5420the mining structure.
5421The remaining lines specify the usage of the columns in the new mining model. You can
5422specify that a column in the mining model will be used for prediction by using the
5423following syntax:
5424<column name> PREDICT,
5425If you do not specify usage, you do not have to include a data mining structure column
5426in the list. All columns that are used by the referenced data mining structure are
5427automatically available for use by the mining models that are based on that structure.
5428However, the model will not use the columns for training unless you specify the usage.
5429The last line in the code defines the algorithm and algorithm parameters that will be
5430used to generate the mining model.
5431) USING <algorithm>( <algorithm parameters> )
5432Lesson Tasks
5433You will perform the following tasks in this lesson:
5434• Add an association mining model to the structure using the default probability
5435• Add an association mining model to the structure using a modified probability
5436Adding an Association Mining Model to the Structure Using the Default
5437MINIMUM_PROBABILITY
5438The first task is to add a new mining model to the Market Basket mining structure based
5439on the Microsoft Association algorithm using the default value for
5440MINIMUM_PROBABILITY.
54411. In Object Explorer, right-click the instance of Analysis Services, point to New
5442Query, and then click DMX.
5443Query Editor opens and contains a new, blank query.
5444Note
5445To create a DMX query against a specific Analysis Services database,
5446To add an Association mining model
5447182
5448right-click the database instead of the instance.
54492. Copy the generic example of the ALTER MINING STRUCTURE statement into
5450the blank query.
54513. Replace the following:
5452<mining structure name>
5453with:
5454[Market Basket]
54554. Replace the following:
5456<mining model name>
5457with:
5458[Default Association]
54595. Replace the following:
5460[<key column>],
5461<mining model columns>,
5462<table columns>
5463( [<nested key column>],
5464 <nested mining model columns> )
5465with:
5466OrderNumber,
5467 [Products] PREDICT (
5468 [Model]
5469 )
5470In this case, the [Products] table has been designated as the predictable
5471column. Also, the [Model] column is included in the list of nested table columns
5472because it is the key column of the nested table.
5473Note
5474Remember that a nested key is different from a case key. A case key is a
5475unique identifier of the case, whereas the nested key is an attribute that
5476you want to model.
54776. Replace the following:
5478USING <algorithm>( <algorithm parameters> )
5479with:
5480Using Microsoft_Association_Rules
5481The resulting statement should now be as follows:
5482ALTER MINING STRUCTURE [Market Basket]
5483183
5484ADD MINING MODEL [Default Association]
5485(
5486 OrderNumber,
5487 [Products] PREDICT (
5488 [Model]
5489 )
5490)
5491Using Microsoft_Association_Rules
54927. On the File menu, click Save DMXQuery1.dmx As.
54938. In the Save As dialog box, browse to the appropriate folder, and name the file
5494Default_Association_Model.dmx.
54959. On the toolbar, click the Execute button.
5496Adding an Association Mining Model to the Structure Changing the Default
5497MINIMUM_PROBABILITY
5498The next task is to add a new mining model to the Market Basket mining structure based
5499on the Microsoft Association algorithm, and change the default value for
5500MINIMUM_PROBABILITY to 0.01. Changing the parameter will cause the Microsoft
5501Association algorithm to create more rules.
55021. In Object Explorer, right-click the instance of Analysis Services, point to New
5503Query, and then click DMX.
5504Query Editor opens and contains a new, blank query.
55052. Copy the generic example of the ALTER MINING STRUCTURE statement into
5506the blank query.
55073. Replace the following:
5508<mining structure name>
5509with:
5510Market Basket
55114. Replace the following:
5512<mining model name>
5513with:
5514[Modified Association]
55155. Replace the following:
5516<mining model columns>,
5517To add an Association mining model
5518184
5519<table columns>
5520( [<nested key column>],
5521 <nested mining model columns> )
5522with:
5523OrderNumber,
5524[Products] PREDICT (
5525 [Model]
5526 )
5527In this case, the [Products] table has been designated as the predictable
5528column. Also, the [MODEL] column is included in the list because it is the key
5529column in the nested table.
55306. Replace the following:
5531USING <algorithm>( <algorithm parameters> )
5532with:
5533USING Microsoft_Association_Rules (Minimum_Probability = 0.1)
5534The resulting statement should now be as follows:
5535ALTER MINING STRUCTURE [Market Basket]
5536ADD MINING MODEL [Modified Assocation]
5537(
5538 OrderNumber,
5539 [Products] PREDICT (
5540 [Model]
5541 )
5542)
5543USING Microsoft_Association_Rules (Minimum_Probability = 0.1)
55447. On the File menu, click Save DMXQuery1.dmx As.
55458. In the Save As dialog box, browse to the appropriate folder, and name the file
5546Modified Association_Model.dmx.
55479. On the toolbar, click the Execute button.
5548In this next lesson you will process the Market Basket mining structure together with its
5549associated mining models.
5550185
5551Lesson 3: Processing the Market Basket Mining Structure
5552In this lesson, you will use the INSERT INTO statement and the vAssocSeqLineItems and
5553vAssocSeqOrders from the sample database to process the mining structures and
5554mining models that you created in Lesson 1: Creating the Association Mining Structure
5555and Lesson 2: Adding Mining Models to the Association Mining Structure.
5556When you process a mining structure, Analysis Services reads the source data and builds
5557the structures that support mining models. When you process a mining model, the data
5558defined by the mining structure is passed through the data mining algorithm that you
5559chose. The algorithm searches for trends and patterns, and then stores this information
5560in the mining model. The mining model, therefore, does not contain the actual source
5561data, but instead contains the information that was discovered by the algorithm. For
5562more information about processing mining models, see Processing Data Mining Objects.
5563You only have to reprocess a mining structure if you change a structure column or
5564change the source data. If you add a mining model to a mining structure that has already
5565been processed, you can use the INSERT INTO MINING MODEL statement to train the
5566new mining model on the existing data.
5567Because the Market Basket mining structure contains a nested table, you will have to
5568define the mining columns to be trained using the nested table structure, and use the
5569SHAPE command to define the queries that pull the training data from the source tables.
5570INSERT INTO Statement
5571In order to train the Market Basket mining structure and its associated mining models,
5572use the INSERT INTO (DMX) statement. The code in the statement can be broken into
5573the following parts.
5574• Identifying the mining structure
5575• Listing the columns in the mining structure
5576• Defining the training data using SHAPE
5577The following is a generic example of the INSERT INTO statement:
5578INSERT INTO MINING STRUCTURE [<mining structure name>]
5579(
5580 <mining structure columns>
5581 [<nested table>]
5582 ( SKIP, <skipped column> )
5583)
5584SHAPE {
5585 OPENQUERY([<datasource>],'<SELECT statement>') }
5586APPEND
5587( 
5588186
5589 {OPENQUERY([<datasource>],'<nested SELECT statement>')
5590}
5591RELATE [<case key>] TO [<foreign key>]
5592) AS [<nested table>]
5593The first line of the code identifies the mining structure that you will train:
5594INSERT INTO MINING STRUCTURE [<mining structure name>]
5595The next lines of the code specify the columns that are defined by the mining structure.
5596You must list each column in the mining structure, and each column must map to a
5597column contained within the source query data. You can use SKIP to ignore columns
5598that exist in the source data but do not exist in the mining structure. For more
5599information about how to use SKIP, see INSERT INTO (DMX).
5600(
5601 <mining structure columns>
5602 [<nested table>]
5603 ( SKIP, <skipped column> )
5604)
5605The final lines of the code define the data that will be used to train the mining structure.
5606Because the source data is contained within two tables, you will use SHAPE to relate the
5607tables.
5608SHAPE {
5609 OPENQUERY([<datasource>],'<SELECT statement>') }
5610APPEND
5611(
5612 {OPENQUERY([<datasource>],''<nested SELECT statement>'')
5613}
5614RELATE [<case key>] TO [<foreign key>]
5615) AS [<nested table>]
5616In this lesson, you use OPENQUERY to define the source data. For information about
5617other methods of defining a query on the source data, see <source data query>.
5618Lesson Tasks
5619You will perform the following task in this lesson:
5620• Process the Market Basket mining structure
5621Processing the Market Basket Mining Structure
56221. In Object Explorer, right-click the instance of Analysis Services, point to New
5623To process the mining structure by using INSERT INTO
5624187
5625Query, and then click DMX.
5626Query Editor opens and contains a new, blank query.
56272. Copy the generic example of the INSERT INTO statement into the blank query.
56283. Replace the following:
5629[<mining structure>]
5630with:
5631Market Basket
56324. Replace the following:
5633 <mining structure columns>
5634 [<nested table>]
5635 ( SKIP, <skipped column> )
5636with:
5637[OrderNumber],
5638[Products]
5639(SKIP, [Model])
5640In the statement, Products refers to the Products table defined by the SHAPE
5641statement. SKIP is used to ignore the Model column, which exists in the source
5642data as a key, but is not used by the mining structure.
56435. Replace the following:
5644SHAPE {
5645 OPENQUERY([<datasource>],'<SELECT statement>') }
5646APPEND
5647(
5648 {OPENQUERY([<datasource>],'<nested SELECT statement>')
5649}
5650RELATE [<case key>] TO [<foreign key>]
5651) AS [<nested table>]
5652with:
5653SHAPE {
5654 OPENQUERY([Adventure Works DW],'SELECT OrderNumber
5655 FROM vAssocSeqOrders ORDER BY OrderNumber')}
5656APPEND
5657(
5658 {OPENQUERY([Adventure Works DW],'SELECT OrderNumber, Model 
5659188
5660FROM
5661 dbo.vAssocSeqLineItems ORDER BY OrderNumber, Model')
5662}
5663RELATE OrderNumber to OrderNumber
5664) AS [Products]
5665The source query references the data source defined in the sample
5666project. It uses this data source to access the vAssocSeqLineItems and
5667vAssocSeqOrders views. These views contain the source data that will be used to
5668train the mining model. If you have not created this project or these views, see
5669Basic Data Mining Tutorial.
5670Within the SHAPE command, you will use OPENQUERY to define two queries.
5671The first query defines the parent table, and the second query defines the nested
5672table. The two tables are related using the OrderNumber column, which exists in
5673both tables.
5674The complete statement should now be as follows:
5675INSERT INTO MINING STRUCTURE [Market Basket]
5676(
5677 [OrderNumber],[Products] (SKIP, [Model])
5678)
5679SHAPE {
5680 OPENQUERY([Adventure Works DW],'SELECT OrderNumber
5681 FROM vAssocSeqOrders ORDER BY OrderNumber')}
5682APPEND
5683(
5684 {OPENQUERY([Adventure Works DW],'SELECT OrderNumber, Model
5685FROM
5686 dbo.vAssocSeqLineItems ORDER BY OrderNumber, Model')
5687}
5688RELATE OrderNumber to OrderNumber
5689) AS [Products]
56906. On the File menu, click Save DMXQuery1.dmx As.
56917. In the Save As dialog box, browse to the appropriate folder, and name the file
5692Process Market Basket.dmx.
56938. On the toolbar, click the Execute button.
5694After the query has finished running, you can view the patterns and itemsets that were
5695found, view associations, or filter by itemset, probability, or importance. To view this 
5696189
5697information, in SQL Server Management Studio, right-click the name of the data model,
5698and then click Browse.
5699In the next lesson, you will create several predictions based on the mining models that
5700you added to the Market Basket structure.
5701Lesson 4: Executing Market Basket Predictions
5702In this lesson, you will use the DMX SELECT statement to create predictions based on the
5703association models you created in Lesson 2: Adding Mining Models to the Market Basket
5704Mining Structure. A prediction query is created by using the DMX SELECT statement and
5705adding a PREDICTION JOIN clause. For more information about the syntax of a
5706prediction join, see SELECT FROM <model> PREDICTION JOIN (DMX).
5707The SELECT FROM <model> PREDICTION JOIN form of the SELECT statement
5708contains three parts:
5709• A list of the mining model columns and prediction functions that are returned in the
5710result set. This list can also contain input columns from the source data.
5711• A source query that defines the data that is being used to create a prediction. For
5712example, if you are creating many predictions in a batch, the source query could
5713retrieve a list of customers.
5714• A mapping between the mining model columns and the source data. If the columns
5715names match, you can use the NATURAL PREDICTION JOIN syntax and omit the
5716column mappings.
5717You can enhance the query by using prediction functions. Prediction functions provide
5718additional information, such as the probability of a prediction occurring, or the support
5719for a prediction in the training dataset. For more information about prediction functions,
5720see Functions (DMX).
5721You can also use the prediction query builder in SQL Server Data Tools (SSDT) to create
5722prediction queries. For more information, see Using the Prediction Query Builder to
5723Create DMX Prediction Queries.
5724Singleton PREDICTION JOIN Statement
5725The first step is to create a singleton query, by using the SELECT FROM <model>
5726PREDICTION JOIN syntax and supplying a single set of values as input. The following is
5727a generic example of the singleton statement:
5728SELECT <select list>
5729 FROM [<mining model>]
5730[NATURAL] PREDICTION JOIN
5731(SELECT '<value>' AS [<column>], 
5732190
5733 (SELECT 'value' AS [<nested column>] UNION
5734 SELECT 'value' AS [<nested column>] ...)
5735 AS [<nested table>])
5736AS [<input alias>]
5737The first line of the code defines the columns from the mining model that the query
5738returns, and specifies the name of the mining model used to generate the prediction:
5739SELECT <select list> FROM [<mining model>]
5740The next line of the code indicates the operation to perform. Because you will specify
5741values for each of the columns and type the column names exactly so as to match the
5742model, you can use the NATURAL PREDICTION JOIN syntax. However, if the column
5743names were different, you would have to specify mappings between the columns in the
5744model and the columns in the new data by adding an ON clause.
5745[NATURAL] PREDICTION JOIN
5746The next lines of the code define the products in the shopping cart that will be used to
5747predict additional products that a customer will add:
5748(SELECT '<value>' AS [<column>],
5749 (SELECT 'value' AS [<nested column>] UNION
5750 SELECT 'value' AS [<nested column>] ...)
5751 AS [<nested table>])
5752Lesson Tasks
5753You will perform the following tasks in this lesson:
5754• Create a query that predicts what other items a customer will likely purchase, based
5755on items already existing in their shopping cart. You will create this query by using
5756the mining model with the default MINIMUM_PROBABILITY.
5757• Create a query that predicts what other items a customer will likely purchase based
5758on items already existing in their shopping cart. This query is based on a different
5759model, in which MINIMUM_PROBABILITY has been set to 0.01. Because the default
5760value for MINIMUM_PROBABILITY in association models is 0.3, the query on this
5761model should return more possible items than the query on the default model.
5762Create a Prediction by Using a Model with the Default
5763MINIMUM_PROBABILITY
57641. In Object Explorer, right-click the instance of Analysis Services, point to New
5765Query, and then click DMX to open the Query Editor.
57662. Copy the generic example of the PREDICTION JOIN statement into the blank
5767query.
5768To create an association query
5769191
57703. Replace the following:
5771<select list>
5772with:
5773PREDICT([Default
5774Association].[Products],INCLUDE_STATISTICS,3)
5775You could just include the column name [Products], but by using the Predict
5776function, you can limit the number of products that are returned by the algorithm
5777to three. You can also use INCLUDE_STATISTICS, which returns the support,
5778probability, and adjusted probability for each product. These statistics help you
5779rate the accuracy of the prediction.
57804. Replace the following:
5781[<mining model>]
5782with:
5783[Default Association]
57845. Replace the following:
5785(SELECT '<value>' AS [<column>],
5786 (SELECT 'value' AS [<nested column>] UNION
5787 SELECT 'value' AS [<nested column>] ...)
5788 AS [<nested table>])
5789with:
5790(SELECT (SELECT 'Mountain Bottle Cage' AS [Model]
5791 UNION SELECT 'Mountain Tire Tube' AS [Model]
5792 UNION SELECT 'Mountain-200' AS [Model]) AS [Products]) AS t
5793This statement uses the UNION statement to specify three products that must be
5794included in the shopping cart together with the predicted products. The Model
5795column in the SELECT statement corresponds to the model column that is
5796contained in the nested products table.
5797The complete statement should now be as follows:
5798SELECT
5799 PREDICT([Default
5800Association].[Products],INCLUDE_STATISTICS,3)
5801From
5802 [Default Association]
5803NATURAL PREDICTION JOIN
5804(SELECT (SELECT 'Mountain Bottle Cage' AS [Model]
5805 UNION SELECT 'Mountain Tire Tube' AS [Model]
5806192
5807 UNION SELECT 'Mountain-200' AS [Model]) AS [Products]) AS t
58086. On the File menu, click Save DMXQuery1.dmx As.
58097. In the Save As dialog box, browse to the appropriate folder, and name the file
5810Association Prediction.dmx.
58118. On the toolbar, click the Execute button.
5812The query returns a table that contains three products: HL Mountain Tire, Fender
5813Set - Mountain, and ML Mountain Tire. The table lists these returned products in
5814order of probability. The returned product that is most likely to be included in the
5815same shopping cart as the three products specified in the query appears at the
5816top of the table. The two products that follow are the next most likely to be
5817included in the shopping cart. The table also contains statistics describing the
5818accuracy of the prediction.
5819Create a Prediction by Using a Model with a MINIMUM_PROBABILITY of
58200.01
58211. In Object Explorer, right-click the instance of Analysis Services, point to New
5822Query, and then click DMX to open the Query Editor.
58232. Copy the generic example of the PREDICTION JOIN statement into the blank
5824query.
58253. Replace the following:
5826<select list>
5827with:
5828PREDICT([Modified
5829Association].[Products],INCLUDE_STATISTICS,3)
58304. Replace the following:
5831[<mining model>]
5832with:
5833[Modified Association]
58345. Replace the following:
5835(SELECT '<value>' AS [<column>],
5836 (SELECT 'value' AS [<nested column>] UNION
5837 SELECT 'value' AS [<nested column>] ...)
5838 AS [<nested table>])
5839with:
5840(SELECT (SELECT 'Mountain Bottle Cage' AS [Model]
5841To create an association query
5842 UNION SELECT 'Mountain Tire Tube' AS [Model]
5843 UNION SELECT 'Mountain-200' AS [Model]) AS [Products]) AS t
5844This statement uses the UNION statement to specify three products that must be
5845included in the shopping cart together with the predicted products. The [Model]
5846column in the SELECT statement corresponds to the column in the nested
5847products table.
5848The complete statement should now be as follows:
5849SELECT
5850 PREDICT([Modified
5851Association].[Products],INCLUDE_STATISTICS,3)
5852From
5853 [Modified Association]
5854NATURAL PREDICTION JOIN
5855(SELECT (SELECT 'Mountain Bottle Cage' AS [Model]
5856 UNION SELECT 'Mountain Tire Tube' AS [Model]
5857 UNION SELECT 'Mountain-200' AS [Model]) AS [Products]) AS t
58586. On the File menu, click Save DMXQuery1.dmx As.
58597. In the Save As dialog box, browse to the appropriate folder, and name the file
5860Modified Association Prediction.dmx.
58618. On the toolbar, click the Execute button.
5862The query returns a table that contains three products: HL Mountain Tire, Water
5863Bottle, and Fender Set - Mountain. The table lists these products in order of
5864probability. The product that appears at the top of the table is the product that is
5865most likely to be included in the same shopping cart as the three products
5866specified in the query. The remaining products are the next most likely to be
5867included in the shopping cart. The table also contains statistics that describe the
5868accuracy of the prediction.
5869You can see from the results of this query that the value of the
5870MINIMUM_PROBABILITY parameter affects the results returned by the query.
5871This is the last step in the Market Basket tutorial. You now have a set of models that you
5872can use to predict the products that customers might purchase at the same time.
5873194
5874Time Series Prediction DMX Tutorial
5875In this tutorial, you will learn how to create a time series mining structure, create three
5876custom time series mining models, and then make predictions by using those models.
5877The mining models are based on the data contained in the sample database, which
5878stores data for the fictitious company Adventure Works Cycles. Adventure Works Cycles
5879is a large, multinational manufacturing company.
5880Tutorial Scenario
5881Adventure Works Cycles has decided to use data mining to generate sales projections.
5882They have already built some regional forecasting models; for more information, see
5883Lesson 2: Building a Forecasting Scenario (Intermediate Data Mining Tutorial). However,
5884the Sales Department needs to be able to periodically update the data mining model
5885with new sales data. They also want to customize the models to provide different
5886projections.
5887Microsoft SQL Server Analysis Services provides several tools that can be used to
5888accomplish this task:
5889• The Data Mining Extensions (DMX) query language
5890• The Microsoft Time Series Algorithm
5891• Query Editor in SQL Server Management Studio
5892The Microsoft Time Series algorithm creates models that can be used for prediction of
5893time-related data. Data Mining Extensions (DMX) is a query language provided by
5894Analysis Services that you can use to create mining models and prediction queries.
5895What You Will Learn
5896This tutorial assumes that you are already familiar with the objects that Analysis Services
5897uses to create mining models. If you have not previously created a mining structure or
5898mining model by using DMX, see Bike Buyer DMX Tutorial.
5899This tutorial is divided into the following lessons:
5900Creating a Time Series Mining Structure
5901In this lesson, you will learn how to use the CREATE MINING MODEL statement to add
5902a new forecasting model and a related mining model.
5903Adding Mining Models to the Time Series Mining Structure
5904In this lesson, you will learn how to use the ALTER MINING STRUCTURE statement to
5905add new mining models to the time series structure. You will also learn how to
5906customize the algorithm used for analyzing a time series.
5907Lesson 3: Processing the Time Series Structure and Models
5908In this lesson, you will learn how to train the models by using the INSERT INTO
5909statement and populating the structure with data from the database.
5910195
5911Creating Time Series Predictions Using DMX
5912In this lesson, you will learn how to create time series predictions.
5913Extending the Time Series Model
5914In this lesson, you will learn how to use the EXTEND_MODEL_CASES parameter to
5915update the model with new data when you make predictions.
5916Requirements
5917Before doing this tutorial, make sure that the following are installed:
5918• Microsoft SQL Server
5919• Microsoft SQL Server Analysis Services
5920• The database
5921By default, the sample databases are not installed, to enhance security. To install the
5922official sample databases for Microsoft SQL Server, go to
5923http://www.CodePlex.com/MSFTDBProdSamples or on the Microsoft SQL Server Samples
5924and Community Projects home page in the section Microsoft SQL Server Product
5925Samples. Click Databases, then click the Releases tab and select the databases that you
5926want.
5927Lesson 1: Creating a Time Series Mining Model and Mining
5928Structure
5929In this lesson, you will create a mining model that allows you to predict values over time,
5930based on historical data. When you create the model, the underlying structure will be
5931generated automatically and can be used as the basis for additional mining models.
5932This lesson assumes that you are familiar with forecasting models and with the
5933requirements of the Microsoft Time Series algorithm. For more information, see
5934Microsoft Time Series Algorithm (Analysis Services - Data Mining).
5935CREATE MINING MODEL Statement
5936In order to create a mining model directly and automatically generate the underlying
5937mining structure, you use the CREATE MINING MODEL (DMX) statement. The code in the
5938statement can be broken into the following parts:
5939• Naming the model
5940196
5941• Defining the time stamp
5942• Defining the optional series key column
5943• Defining the predictable attribute or attributes
5944The following is a generic example of the CREATE MINING MODEL statement:
5945CREATE MINING MODEL [<Mining Structure Name>]
5946(
5947 <key columns>,
5948 <predictable attribute columns>
5949)
5950USING <algorithm name>([parameter list])
5951WITH DRILLTHROUGH
5952The first line of the code defines the name of the mining model:
5953CREATE MINING MODEL [Mining Model Name]
5954Analysis Services automatically generates a name for the underlying structure, by
5955appending "_structure" to the model name, which ensures that the structure name is
5956unique from the model name. For information about naming an object in DMX, see
5957Identifiers (DMX).
5958The next line of the code defines the key column for the mining model, which in the case
5959of a time series model uniquely identifies a time step in the source data. The time step is
5960identified with the KEY TIME keywords after the column name and data types. If the
5961time series model has a separate series key, it is identified by using the KEY keyword.
5962<key columns>
5963The next line of the code is used to define the columns in the model that will be
5964predicted. You can have multiple predictable attributes in a single mining model. When
5965there are multiple predictable attributes, the Microsoft Time Series algorithm generates a
5966separate analysis for each series:
5967<predictable attribute columns>
5968Lesson Tasks
5969You will perform the following tasks in this lesson:
5970• Create a new blank query
5971• Alter the query to create the mining model
5972• Execute the query
5973Creating the Query
5974The first step is to connect to an instance of Analysis Services and create a new DMX
5975query in SQL Server Management Studio.
5976To create a new DMX query in SQL Server Management Studio
5977197
59781. Open SQL Server Management Studio.
59792. In the Connect to Server dialog box, for Server type, select Analysis Services. In
5980Server name, type LocalHost, or the name of the instance of Analysis Services
5981that you want to connect to for this lesson. Click Connect.
59823. In Object Explorer, right-click the instance of Analysis Services, point to New
5983Query, and then click DMX.
5984Query Editor opens and contains a new, blank query.
5985Altering the Query
5986The next step is to modify the CREATE MINING MODEL statement to create the mining
5987model used for forecasting, together with its underlying mining structure.
59881. In Query Editor, copy the generic example of the CREATE MINING MODEL
5989statement into the blank query.
59902. Replace the following:
5991[mining model name]
5992with:
5993[Forecasting_MIXED]
59943. Replace the following:
5995<key columns>
5996with:
5997[Reporting Date] DATE KEY TIME,
5998[Model Region] TEXT KEY
5999The TIME KEY keyword indicates that the ReportingDate column contains the
6000time step values used to order the values. Time steps can be dates and times,
6001integers, or any ordered data type, so long as the values are unique and the data
6002is sorted.
6003The TEXT and KEY keywords indicate that the ModelRegion column contains an
6004additional series key. You can have only one series key, and the values in the
6005column must be distinct.
60064. Replace the following:
6007< predictable attribute columns> )
6008with:
6009 [Quantity] LONG CONTINUOUS PREDICT,
6010 [Amount] DOUBLE CONTINUOUS PREDICT
6011 )
6012To customize the CREATE MINING MODEL statement
6013198
60145. Replace the following:
6015USING <algorithm name>([parameter list])
6016WITH DRILLTHROUGH
6017with:
6018USING Microsoft_Time_Series(AUTO_DETECT_PERIODICITY = 0.8,
6019FORECAST_METHOD = 'MIXED')
6020WITH DRILLTHROUGH
6021The algorithm parameter, AUTO_DETECT_PERIODICITY = 0.8, indicates that you
6022want the algorithm to detect cycles in the data. Setting this value closer to 1
6023favors the discovery of many patterns but can slow processing.
6024The algorithm parameter, FORECAST_METHOD, indicates whether you want the
6025data to be analyzed using ARTXP, ARIMA, or a mixture of both.
6026The keyword, WITH DRILLTHROUGH, specify that you want to be able to view
6027detailed statistics in the source data after the model is complete. You must add
6028this clause if you want to browse the model by using the Microsoft Time Series
6029Viewer. It is not required for prediction.
6030The complete statement should now be as follows:
6031CREATE MINING MODEL [Forecasting_MIXED]
6032 (
6033 [Reporting Date] DATE KEY TIME,
6034 [Model Region] TEXT KEY,
6035 [Quantity] LONG CONTINUOUS PREDICT,
6036 [Amount] DOUBLE CONTINUOUS PREDICT
6037 )
6038USING Microsoft_Time_Series (AUTO_DETECT_PERIODICITY = 0.8,
6039FORECAST_METHOD = 'MIXED')
6040WITH DRILLTHROUGH
60416. On the File menu, click Save DMXQuery1.dmx As.
60427. In the Save As dialog box, browse to the appropriate folder, and name the file
6043Forecasting_MIXED.dmx.
6044Executing the Query
6045The final step is to execute the query. After a query is created and saved, it needs to be
6046executed to create the mining model and its mining structure on the server. For more
6047information about executing queries in Query Editor, see SQL Server Management Studio
6048Transact SQL Query.
6049199
6050• In Query Editor, on the toolbar, click Execute.
6051The status of the query is displayed in the Messages tab at the bottom of Query
6052Editor after the statement finishes executing. Messages should display:
6053Executing the query
6054Execution complete
6055A new structure named Forecasting_MIXED_Structure now exists on the server,
6056together with the related mining model Forecasting_MIXED.
6057In the next lesson, you will add a mining model to the Forecasting_MIXED mining
6058structure that you just created.
6059Next Lesson
6060Adding Mining Models to the Time Series Mining Structure
6061See Also
6062Mining Model Content for Time Series Models (Analysis Services - Data Mining)
6063Microsoft Time Series Algorithm Technical Reference (Analysis Services - Data Mining)
6064Lesson 2: Adding Mining Models to the Time Series Mining
6065Structure
6066In this lesson, you will add a new mining model to the mining structure that you just
6067created in Creating a Time Series Mining Structure.
6068ALTER MINING STRUCTURE Statement
6069In order to add a new mining model to an existing mining structure, you use the ALTER
6070MINING STRUCTURE (DMX) statement. The code in the statement can be broken into the
6071following parts:
6072• Identifying the mining structure
6073• Naming the mining model
6074• Defining the key column
6075• Defining the predictable columns
6076• Specifying the algorithm and any parameter changes
6077The following is a generic example of the ALTER MINING STRUCTURE statement:
6078ALTER MINING STRUCTURE [<mining structure name>]
6079ADD MINING MODEL [<mining model name>]
6080 ([<key columns>],
6081 <mining model columns>
6082 )
6083To execute the query
6084200
6085USING <algorithm name>([<algorithm parameters>])
6086[WITH DRILLTHROUGH]
6087The first line of the code identifies the existing mining structure to which the mining
6088models will be added:
6089ALTER MINING STRUCTURE [<mining structure name>]
6090The next line of the code names the mining model that will be added to the mining
6091structure:
6092ADD MINING MODEL [<mining model name>]
6093For information about naming an object in DMX, see Identifiers (DMX).
6094The next lines of the code define columns from the mining structure that will be used by
6095the mining model:
6096[<key columns>],
6097<mining model columns>
6098You can only use columns that already exist in the mining structure, and the first column
6099in the list must be the key column from the mining structure.
6100The next lines of the code defines the mining algorithm that generates the mining model
6101and the algorithm parameters that you can set on the algorithm, and specify whether
6102you can drill down from the mining model into view detailed data in the training cases:
6103USING <algorithm name>([<algorithm parameters>])
6104WITH DRILLTHROUGH
6105For more information about the algorithm parameters that you can adjust, see Microsoft
6106Time Series Algorithm Technical Reference (Analysis Services - Data Mining).
6107You can specify that a column in the mining model be used for prediction by using the
6108following syntax:
6109<mining model column> PREDICT
6110Lesson Tasks
6111You will perform the following tasks in this lesson:
6112• Add a new time series mining model to the structure.
6113• Change the algorithm parameters to use a different method of analysis and
6114prediction
6115Adding an ARIMA Time Series Model to the Structure
6116The first step is to add a new forecasting mining model to the existing structure. By
6117default, the Microsoft Time Series algorithm creates time series mining models by using
6118two algorithms, ARIMA and ARTXP, and blending the results. However, you can specify a
6119single algorithm to use, or you can specify the exact blend of algorithms. In this step, you
6120will add a new model that uses only the ARIMA algorithm. This algorithm is optimized
6121for long-term prediction.
6122201
61231. In Object Explorer, right-click the instance of Analysis Services, point to New
6124Query, and then click DMX to open Query Editor and a new, blank query.
61252. Copy the generic example of the ALTER MINING STRUCTURE statement into the
6126blank query.
61273. Replace the following:
6128<mining structure name>
6129with:
6130[Forecasting_MIXED_Structure]
61314. Replace the following:
6132<mining model name>
6133with:
6134Forecasting_ARIMA
61355. Replace the following:
6136<key columns>,
6137with:
6138[ReportingDate],
6139[ModelRegion]
6140Note that you do not need to repeat any of the date type or content type
6141information that you provided in the CREATE MINING MODEL statement,
6142because this information is already stored in the mining structure.
61436. Replace the following:
6144<mining model columns>
6145with:
6146([Quantity] PREDICT,
6147[Amount] PREDICT
6148)
61497. Replace the following:
6150USING <algorithm name>([<algorithm parameters>])
6151[WITH DRILLTHROUGH]
6152with:
6153USING Microsoft_Time_Series (AUTO_DETECT_PERIODICITY = .08,
6154FORECAST_METHOD = 'ARIMA')
6155WITH DRILLTHROUGH
6156To add an ARIMA time series mining model
6157202
6158The resulting statement should now be as follows:
6159ALTER MINING STRUCTURE [Forecasting_MIXED_Structure]
6160ADD MINING MODEL [Forecasting_ARIMA]
6161 (
6162 ([ReportingDate],
6163 [ModelRegion],
6164 ([Quantity] PREDICT,
6165 [Amount] PREDICT
6166 )
6167USING Microsoft_Time_Series (AUTO_DETECT_PERIODICITY = .08,
6168FORECAST_METHOD = 'ARIMA')
6169WITH DRILLTHROUGH
61708. On the File menu, click Save DMXQuery1.dmx As.
61719. In the Save As dialog box, browse to the appropriate folder, and name the file
6172Forecasting_ARIMA.dmx.
617310. On the toolbar, click the Execute button.
6174Adding an ARTXP Time Series Model to the Structure
6175The ARTXP algorithm was the default time series algorithm in SQL Server 2005 and is
6176optimized for short-term prediction. To compare predictions by using all three time
6177series algorithms, you will add one more model that is based on the ARTXP algorithm.
61781. Copy the following code into a blank query window.
6179Note that you do not need to change anything except the name of the new
6180mining model, and the value of the FORECAST_METHOD parameter.
6181ALTER MINING STRUCTURE [Forecasting_MIXED_Structure]
6182ADD MINING MODEL [Forecasting_ARTXP]
6183 (
6184 ([ReportingDate],
6185 [ModelRegion],
6186 ([Quantity] PREDICT,
6187 [Amount] PREDICT
6188 )
6189USING Microsoft_Time_Series (AUTO_DETECT_PERIODICITY = .08,
6190FORECAST_METHOD = 'ARTXP')
6191To add an ARTXP time series mining model
6192203
6193WITH DRILLTHROUGH
61942. On the File menu, click Save DMXQuery1.dmx As.
61953. In the Save As dialog box, browse to the appropriate folder, and name the file
6196Forecasting_ARTXP.dmx.
61974. On the toolbar, click the Execute button.
6198In the next lesson, you will process all of the models and the mining structure.
6199Lesson 3: Processing the Time Series Structure and Models
6200In this lesson, you will use the INSERT INTO statement to process the time series mining
6201structures and mining models that you created.
6202When you process a mining structure, Analysis Services reads the source data and builds
6203the structures that support mining models. You always have to process a mining model
6204and structure when you first create it. If you specify the mining structure when using
6205INSERT INTO, the statement processes the mining structure and all its associated mining
6206models.
6207When you add a mining model to a mining structure that has already been processed,
6208you can use the INSERT INTO MINING MODEL statement to process just the new
6209mining model by using the existing data.
6210For more information about processing mining models, see Processing Data Mining
6211Objects.
6212INSERT INTO Statement
6213In order to train the time series mining structure and all its associated mining models,
6214use the INSERT INTO (DMX) statement. The code in the statement can be broken into
6215the following parts.
6216• Identifying the mining structure
6217• Listing the columns in the mining structure
6218• Defining the training data
6219The following is a generic example of the INSERT INTO statement:
6220INSERT INTO MINING STRUCTURE [<mining structure name>]
6221(
6222 <mining structure columns>
6223)
6224204
6225OPENQUERY (<source data definition>)
6226The first line of the code identifies the mining structure that you will train:
6227INSERT INTO MINING STRUCTURE [<mining structure name>]
6228The next lines of the code specify the columns that are defined by the mining structure.
6229You must list each column in the mining structure, and each column must map to a
6230column contained within the source query data.
6231(
6232 <mining structure columns>
6233)
6234The final lines of the code define the data that will be used to train the mining structure.
6235OPENQUERY (<source data definition>)
6236In this lesson, you use OPENQUERY to define the source data. For more information
6237about other methods of defining a query on the source data, see <source data query>.
6238Lesson Tasks
6239You will perform the following task in this lesson:
6240• Process the mining structure Forecasting_MIXED_Structure
6241• Process the related mining models Forecasting_MIXED, Forecasting_ARIMA, and
6242Forecasting_ARTXP
6243Processing the Time Series Mining Structure
62441. In Object Explorer, right-click the instance of Analysis Services, point to New
6245Query, and then click DMX.
6246Query Editor opens and contains a new, blank query.
62472. Copy the generic example of the INSERT INTO statement into the blank query.
62483. Replace the following:
6249[<mining structure>]
6250with:
6251Forecasting_MIXED_Structure
62524. Replace the following:
6253 <mining structure columns>
6254with:
6255[ReportingDate],
6256[ModelRegion]
62575. Replace the following:
6258To process the mining structure and related mining models by using INSERT
6259INTO
6260OPENQUERY(<source data definition>)
6261with:
6262OPENQUERY([Adventure Works DW 2008R2],'SELECT
6263[ReportingDate], [ModelRegion], [Quantity], [Amount]
6264FROM vTimeSeries ORDER BY [ReportingDate]')
6265The source query references the data source defined in the
6266IntermediateTutorial sample project. It uses this data source to access the view
6267vTimeSeries. This view contains the source data that will be used to train the
6268mining model. If you are not familiar with this project or this views, see Lesson 2:
6269Building a Forecasting Scenario (Intermediate Data Mining Tutorial).
6270The complete statement should now be as follows:
6271INSERT INTO MINING STRUCTURE [Forecasting_MIXED_Structure]
6272(
6273 [ReportingDate],[ModelRegion],[Quantity],[Amount])
6274)
6275OPENQUERY(
6276[Adventure Works DW 2008R2],
6277'SELECT [ReportingDate],[ModelRegion],[Quantity],[Amount]
6278FROM vTimeSeries ORDER BY [ReportingDate]'
6279)
62806. On the File menu, click Save DMXQuery1.dmx As.
62817. In the Save As dialog box, browse to the appropriate folder, and name the file
6282ProcessForecastingAll.dmx.
62838. On the toolbar, click the Execute button.
6284After the query has finished running, you can create predictions by using the processed
6285mining models. In the next lesson, you will create several predictions based on the
6286mining models that you created.
6287206
6288Lesson 4: Creating Time Series Predictions Using DMX
6289In this lesson and the following lesson, you will use Data Mining Extensions (DMX) to
6290create different types of predictions based on the time series models that you created in
6291Lesson 1: Creating a Time Series Mining Model and Mining Structure and Lesson 2:
6292Adding Mining Models to the Time Series Mining Structure.
6293With a time series model, you have many options for making predictions:
6294• Use the existing patterns and data in the mining model
6295• Use the existing patterns in the mining model but supply new data
6296• Add new data to the model or update the model.
6297The syntax for making these prediction types is summarized below:
6298Default time series prediction
6299Use PredictTimeSeries (DMX) to return the specified number of predictions from
6300the trained mining model.
6301For example, see PredictTimeSeries (DMX) or Querying a Time Series Model
6302(Analysis Services - Data Mining).
6303EXTEND_MODEL_CASES
6304Use PredictTimeSeries (DMX) with the EXTEND_MODEL_CASES argument to add
6305new data, extend the series, and create predictions based on the updated mining model.
6306This tutorial contains an example of how to use EXTEND_MODEL_CASES.
6307REPLACE_MODEL_CASES
6308Use PredictTimeSeries (DMX) with the REPLACE_MODEL_CASES argument to
6309replace the original data with a new data series, and then create predictions based on
6310applying the patterns in the mining model to the new data series.
6311For an example of how to use REPLACE_MODEL_CASES, see Lesson 2: Building a
6312Forecasting Scenario (Intermediate Data Mining Tutorial).
6313Lesson Tasks
6314You will perform the following tasks in this lesson:
6315• Create a query to get the default predictions based on existing data.
6316In the following lesson you will perform the following related tasks:
6317• Create a query to supply new data and get updated predictions.
6318In addition to creating queries manually by using DMX, you can also create predictions
6319by using the prediction query builder in SQL Server Data Tools (SSDT). For more
6320information, see Using the Prediction Query Builder to Create DMX Prediction Queries or
6321Mining Model Prediction Tab: How-to Topics.
6322Simple Time Series Prediction Query
6323The first step is to use the SELECT FROM statement together with the
6324PredictTimeSeries function to create time series predictions. Time series models 
6325207
6326support a simplified syntax for creating predictions: you do not need to supply any
6327inputs, but only have to specify the number of predictions to create. The following is a
6328generic example of the statement you will use:
6329SELECT <select list>
6330FROM [<mining model name>]
6331WHERE [<criteria>]
6332The select list can contain columns from the model, such as the name of the product line
6333that you are creating the predictions for, or prediction functions, such as Lag (DMX) or
6334PredictTimeSeries (DMX), which are specifically for time series mining models.
63351. In Object Explorer, right-click the instance of Analysis Services, point to New
6336Query, and then click DMX.
6337Query Editor opens and contains a new, blank query.
63382. Copy the generic example of the statement into the blank query.
63393. Replace the following:
6340<select list>
6341with:
6342[Forecasting_MIXED].[ModelRegion],
6343PredictTimeSeries([Forecasting_MIXED].[Quantity],6) AS
6344PredictQty,
6345PredictTimeSeries ([Forecasting_MIXED].[Amount],6) AS
6346PredictAmt
6347The first line retrieves a value from the mining model that identifies the series.
6348The second and third lines use the PredictTimeSeries function. Each line predicts
6349a different attribute, [Quantity] or [Amount]. The numbers after the names of
6350the predictable attributes specify the number of time steps to predict.
6351The AS clause is used to provide a name for the column that is returned by each
6352prediction function. If you do not supply an alias, by default both columns are
6353returned with the label, Expression.
63544. Replace the following:
6355[<mining model>]
6356with:
6357[Forecasting_MIXED]
63585. Replace the following:
6359WHERE [criteria>]
6360To create a simple time series prediction query
6361208
6362with:
6363WHERE [ModelRegion] = 'M200 Europe' OR
6364[ModelRegion] = 'M200 Pacific'
6365The complete statement should now be as follows:
6366SELECT
6367[Forecasting_MIXED].[ModelRegion],
6368PredictTimeSeries([Forecasting_MIXED].[Quantity],6) AS
6369PredictQty,
6370PredictTimeSeries ([Forecasting_MIXED].[Amount],6) AS
6371PredictAmt
6372FROM
6373[Forecasting_MIXED]
6374WHERE [ModelRegion] = 'M200 Europe' OR
6375[ModelRegion] = 'M200 Pacific'
63766. On the File menu, click Save DMXQuery1.dmx As.
63777. In the Save As dialog box, browse to the appropriate folder, and name the file
6378SimpleTimeSeriesPrediction.dmx.
63798. On the toolbar, click the Execute button.
6380The query returns 6 predictions for each of the two combinations of product and
6381region that are specified in the WHERE clause.
6382In the next lesson, you will create a query that supplies new data to the model, and
6383compare the results of that prediction with the one you just created.
6384Lesson 5: Extending the Time Series Model
6385In SQL Server 2012 Enterprise, you can add new data to a time series model and
6386automatically incorporate the new data into the model. You add new data to a time
6387series mining model in one of two ways:
6388• Use a PREDICTION JOIN to join data in an external source to the training data.
6389• Use a singleton prediction query to provide data one slice at a time. 
6390209
6391For example, assume that you trained the mining model on existing sales data some
6392months ago. When you get new sales, you might want to update the sales predictions to
6393incorporate the new data. You can do this in one step, by supplying the new sales figures
6394as input data and generating new predictions based on the composite data set.
6395Making Predictions with EXTEND_MODEL_CASES
6396The following are generic examples of a time series prediction using
6397EXTEND_MODEL_CASES. The first example enables you to specify the number of
6398predictions starting from the last time step of the original model:
6399SELECT [<model columns>,] PredictTimeSeries(<table column reference>,
6400n, EXTEND_MODEL_CASES)
6401FROM <mining model>
6402PREDICTION JOIN <source query>
6403[WHERE <criteria>]
6404The second example enables you to specify the time step where predictions should start,
6405and where they should end. This option is important when you extend the model cases
6406because, by default, the time steps used for prediction queries always start at the end of
6407the original series.
6408SELECT [<model columns>,] PredictTimeSeries(<table column reference>,
6409n-start, n-end, EXTEND_MODEL_CASES)
6410FROM <mining model>
6411PREDICTION JOIN <source query>
6412[WHERE <criteria>}
6413In this tutorial, you will create both kinds of queries.
64141. In Object Explorer, right-click the instance of Analysis Services, point to New
6415Query, and then click DMX.
6416Query Editor opens and contains a new, blank query.
64172. Copy the generic example of the singleton statement into the blank query.
64183. Replace the following:
6419SELECT [<model columns>,] PredictTimeSeries(<table column
6420reference>, n, EXTEND_MODEL_CASES)
6421with:
6422SELECT [Model Region],
6423PredictTimeSeries([Quantity],6, EXTEND_MODEL_CASES) AS
6424PredictQty
6425The first line retrieves a value from the model that identifies the series.
6426To create a singleton prediction query on a time series model
6427210
6428The second line contains the prediction function, which gets 6 predictions for
6429Quantity. An alias, PredictQty, is assigned to the prediction result column to
6430make it easier to understand the results.
64314. Replace the following:
6432FROM <mining model>
6433with:
6434FROM [Forecasting_MIXED]
64355. Replace the following:
6436PREDICTION JOIN <source query>
6437with:
6438NATURAL PREDICTION JOIN
6439(
6440 SELECT 1 AS [Reporting Date],
6441 '10' AS [Quantity],
6442 'M200 Europe' AS [Model Region]
6443 UNION SELECT
6444 2 AS [Reporting Date],
6445 15 AS [Quantity]),
6446 'M200 Europe' AS [Model Region]
6447) AS t
64486. Replace the following:
6449[WHERE <criteria>]
6450with:
6451WHERE [ModelRegion] = 'M200 Europe' OR
6452[ModelRegion] = 'M200 Pacific'
6453The complete statement should now be as follows:
6454SELECT [Model Region],
6455PredictTimeSeries([Quantity],6, EXTEND_MODEL_CASES) AS
6456PredictQty
6457FROM
6458 [Forecasting_MIXED]
6459NATURAL PREDICTION JOIN
6460 SELECT 1 AS [ReportingDate],
6461 '10' AS [Quantity],
6462211
6463 'M200 Europe' AS [ModelRegion]
6464UNION SELECT
6465 2 AS [ReportingDate],
6466 15 AS [Quantity]),
6467 'M200 Europe' AS [ModelRegion]
6468) AS t
6469WHERE [ModelRegion] = 'M200 Europe' OR
6470[ModelRegion] = 'M200 Pacific'
64717. On the File menu, click Save DMXQuery1.dmx As.
64728. In the Save As dialog box, browse to the appropriate folder, and name the file
6473Singleton_TimeSeries_Query.dmx.
64749. On the toolbar, click the Execute button.
6475The query returns predictions of sales quantity for the M200 bicycle in the Europe
6476and Pacific regions.
6477Understanding Prediction Start with EXTEND_MODEL_CASES
6478Now that you have created predictions based on the original model, and with new data,
6479you can compare the results to see how updating the sales data affects the predictions.
6480Before you do so, review the code that you just created, and notice the following:
6481• You supplied new data for only the Europe region.
6482• You supplied only two months' worth of new data.
6483The following table shows how the new values supplied for M200 Europe affect
6484predictions. You did not provide any new data for the M200 product in the Pacific
6485region, but this series is presented for comparison:
6486Product and Region Existing model (PredictTimeSeries) Model with updated sales data
6487(PredictTimeSeries with
6488EXTEND_MODEL_CASES)
6489M200 Europe
6490M200
6491Europe
64927/25/2008
649312:00:00 AM
649477
6495M200
6496Europe
64978/25/2008
649812:00:00 AM
649964
6500M200
6501Europe
65029/25/2008
650312:00:00 AM
650459
6505M200 10/25/2008 56
6506M200
6507Europe
65087/25/2008
650912:00:00 AM
651010
6511M200
6512Europe
65138/25/2008
651412:00:00 AM
651515
6516M200
6517Europe
65189/25/2008
651912:00:00 AM
652072
6521M200 10/25/2008 69
6522212
6523Product and Region Existing model (PredictTimeSeries) Model with updated sales data
6524(PredictTimeSeries with
6525EXTEND_MODEL_CASES)
6526Europe 12:00:00 AM
6527M200
6528Europe
652911/25/2008
653012:00:00 AM
653156
6532M200
6533Europe
653412/25/2008
653512:00:00 AM
653674
6537Europe 12:00:00 AM
6538M200
6539Europe
654011/25/2008
654112:00:00 AM
654268
6543M200
6544Europe
654512/25/2008
654612:00:00 AM
654789
6548M200 Pacific
6549M200
6550Pacific
65517/25/2008
655212:00:00 AM
655341
6554M200
6555Pacific
65568/25/2008
655712:00:00 AM
655844
6559M200
6560Pacific
65619/25/2008
656212:00:00 AM
656338
6564M200
6565Pacific
656610/25/2008
656712:00:00 AM
656841
6569M200
6570Pacific
657111/25/2008
657212:00:00 AM
657336
6574M200
6575Pacific
657612/25/2008
657712:00:00 AM
657839
6579M200
6580Pacific
65817/25/2008
658212:00:00 AM
658341
6584M200
6585Pacific
65868/25/2008
658712:00:00 AM
658844
6589M200
6590Pacific
65919/25/2008
659212:00:00 AM
659338
6594M200
6595Pacific
659610/25/2008
659712:00:00 AM
659841
6599M200
6600Pacific
660111/25/2008
660212:00:00 AM
660336
6604M200
6605Pacific
660612/25/2008
660712:00:00 AM
660839
6609From these results, you can see two things:
6610• The first two predictions for the M200 Europe series are exactly the same as the new
6611data you supplied. By design, Analysis Services returns the actual new data points
6612instead of making a prediction. That is because when you extend the model cases,
6613the time steps used for prediction queries always start at the end of the original
6614series. Therefore, if you add two new data points, the first two predictions returned
6615overlap with the new data. 
6616213
6617• After all the new data points are used up, Analysis Services makes predictions based
6618on the updated model. Therefore, starting in September 2005, you can see the
6619difference between predictions for M200 Europe from the original model, in the lefthand
6620column, and the model that uses EXTEND_MODEL_CASES, in the right-hand
6621column. The predictions are different because the model has been updated with the
6622new data.
6623Using Start and End Time Steps to Control Predictions
6624When you extend a model, the new data is always attached to the end of the series.
6625However, for the purpose of prediction, the time slices used for prediction queries start
6626at the end of the original series. If you want to obtain only the new predictions when you
6627add the new data, you must specify the starting point as a number of time slices. For
6628example, if you are adding two new data points and want to make four new predictions,
6629you would do the following:
6630• Create a PREDICTION JOIN on a time series model, and specify two months of new
6631data.
6632• Request predictions for four time slices, where the starting point is 3, and the ending
6633point is time slice 6.
6634In other words, if your new data contains time slices, and you request predictions for
6635time steps 1 through , the predictions will coincide with the same period as the new data.
6636To get new predictions for a time periods not covered by your data, you must either start
6637predictions at the time slice after the new data series, or make sure that you request
6638additional time slices.
6639You cannot make historical predictions when you add new data.
6640The following example shows the DMX statement that lets you get only the new
6641predictions for the two series in the previous example.
6642SELECT [Model Region],
6643PredictTimeSeries([Quantity],3,6, EXTEND_MODEL_CASES) AS PredictQty
6644FROM
6645 [Forecasting_MIXED]
6646NATURAL PREDICTION JOIN
6647 SELECT 1 AS [ReportingDate],
6648 '10' AS [Quantity],
6649 'M200 Europe' AS [ModelRegion]
6650UNION SELECT
6651 2 AS [ReportingDate],
6652 15 AS [Quantity]),
6653Note
6654214
6655 'M200 Europe' AS [ModelRegion]
6656) AS t
6657WHERE [ModelRegion] = 'M200 Europe'
6658The prediction results start at time slice 3, which is after the 2 months of new data that
6659you supplied.
6660Product and Region Model with updated data (PredictTimeSeries with
6661EXTEND_MODEL_CASES)
6662M200 Europe
6663M200
6664Europe
66659/25/2008
666612:00:00 AM
666772
6668M200
6669Europe
667010/25/2008
667112:00:00 AM
667269
6673M200
6674Europe
667511/25/2008
667612:00:00 AM
667768
6678M200
6679Europe
668012/25/2008
668112:00:00 AM
668289