Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

«February 2018»

Developing an End to End Data Mining Report

  • 24 January 2010
  • Author: DevinKnight
  • Number of views: 15794

Data mining is a great feature in Analysis Services that is used for predictive analytics.  For example, think about Amazon as a great example of data mining.  Any time you buy a book off Amazon they give you suggestions of other books you may like based on the book you just selected.  It is likely that the data mining model used here is Association Rules, which is used to find relationships between the book chosen and others that bought the same book.

Generally, when you think Analysis Services you think of designing a cube but data mining actually does not require a cube to be built to create a mining structure.  The purpose of this article is not to teach you so much about data mining but to teach you how you can build a Reporting Services report using a data mining dataset.  I will write a future article to help in understanding the basics of data mining.  However, to follow the example used in this article you will not need to even know how to use Analysis Services. 

Requirements to Follow the Example

To follow the examples in this article download the appropriate version of the Adventure Works sample database for you from  When you download this sample database it will also give you a sample Analysis Services project that you can deploy so you will have the sample data mining structure that I show in this example.  Navigate to the C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project folder if you are using SQL Server 2008.  If you are using SQL Server 2005 there will be a comparable folder structure in the C:\Program Files\Microsoft SQL Server\90 folder.  Select either the enterprise or standard edition folder depending on the edition of SQL Server you have.  Then open the Adventure Works.sln file and deploy the solution, which includes the data mining structure used to follow this article.

Creating the Report

·         Open Business Intelligence Development Studio (BIDS) and create a new Reporting Server Project.

·         Create a New Analysis Services Shared Data Source to Adventure Works DW 2008.

·         Create a new report using the Report Wizard.

·         Use the Analysis Services data source that you just created.

·         Select the Query Builder to have the wizard write a DMX (Data Mining Extensions) query for you.

·         By default the Query Builder will write an MDX query for you when an Analysis Services data source is selected.  To create a DMX query select the Command Type DMX button.  When the wizard asks you to confirm the switch click Yes.


·         Hit Select Model then expand the Targeted Mailing mining structure and choose the TM Decision Tree mining model that was included with the sample Adventure Works project.  Click OK after selecting the model.


·         Now hit Select Case Table to pick a dataset that you want to retrieve a prediction for.  In this case the mining model we have picked will tell us if someone is likely to buy a bike.  For this example pick the view called vTargetMail then hit OK.


·         The vTargetMail view actually already has the answer of whether someone will buy a bike or not but for the purposes of this example it will work.  Delete the mapping between BikeBuyer from the Mining Model and the Case Table so the mining model only uses the attributes like commute distance and number of cars owned to predict if a customer is a likely bike buyer.


·         Add six columns to the dataset FirstName, LastName, Phone, EmailAddress and two other columns that will use DMX to return a prediction.  The first one will return back the prediction of 1 if someone predicted to be a bike buyer and 0 if someone predicted to not be a bike buyer.  The second will return back the probability that the customer will be a bike buyer.  Hit OK once your screen matches the image.


Notice the last two columns added that are using Prediction Functions to return back results by using a data mining query.  The Criteria/Argument property shows the formulas used.

·         Notice the DMX query that the Query Builder created for you then click Next.

·         Select Tabular for the report type then click Next.

·         Add all the fields to the Detail level of the report then click Next.


·         Leave the default Slate style selected then click Next.

·         Name the report Data Mining Report and check Preview report then click Finish.


This will produce a report of potential customers and the probability of them buying a bike.  You could do any kind of formatting now in Reporting Services like changing the probability to a percentage or even changing the 1 or 0 under Bike Buyer to represent something else like Yes or No.

Categories: Machine Learning
Rate this article:
No rating


Other posts by DevinKnight

Please login or register to post comments.