Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server / SQL Server 2008

Market Basket Analysis in SSAS 2005+

Rate me:
Please Sign up or sign in to vote.
4.78/5 (6 votes)
28 Feb 2009CPOL3 min read 95.5K   1.4K   32   13
This article explains how to do Market Basket Analysis in SSAS 2005 (Microsoft SQL Analysis Services).

Introduction

This article explains how to do Market Basket Analysis in SSAS 2005 (Microsoft SQL Analysis Services). Market Basket Analysis answers questions of this kind: "How many customers who bought product A also bought product B?" This article assumes some prior knowledge of SSAS and MDX. Here is what we want the result set to look like:

Image 1

The following example will use the Northwind sample database. If you don’t have this database installed on your server, please use this file: Northwind.sql. The Northwind database has the following Entity Relationship Diagram:

Image 2

To understand the problem, let us suppose we have a Sales cube with a Product dimension.

Initially, I thought the Market Basket Analysis would be easy to create by placing the Product dimension on both rows and columns. Here is the MDX:

SQL
SELECT 
    [Products].[Products].Members on Rows,
    [Products].[Products].Members on Columns
    FROM [Sales]

However, the statement above will return an error:

The Products hierarchy already appears in the Axis0 axis. 

The next apparent solution to this problem is to add a duplicate cube dimension (Products 2) to the Sales cube. Here is the MDX:

SQL
SELECT 
    [Products].[Products].Members on Rows,
    [Products 2].[Products].Members on Columns
    FROM [Sales]

However, this MDX returns the following result set:

Image 3

You can see that the data is populated only diagonally. The problem is that you see that data only in intersection of Product A and Product A, or Product B and Product B. This is not what we want. This problem is caused by the way we linked two identical dimensions to the same measure group.

Image 4

What we want is to see is Product A and Product B purchased together (have by the same order ID). This can be accomplished by doing the following:

  1. Creating a duplicate measure group for the Sales data.
  2. Linking two Sales measure groups by an Order dimension.
  3. Linking first Sales measure groups to first Product dimension.
  4. Linking second Sales measure groups to second Product dimension.
  5. Hiding measures in the second measure group.
  6. Linking second Product dimension to the first Sales measure group using a Many-to-Many relationship type.

So, let us begin by creating a project in BIDS (SQL Server Business Intelligence Development Studio).

Image 5

Next, create a connection to the Nothwind database:

Image 6

Next, create the data source view:

Image 7

Next, create the Product dimension by using the Products and Categories tables. The key for the dimension will be ProductID.

Image 8

Image 9

Image 10

Image 11

Next, create the Orders dimension by using the Orders Table. The key for the dimension will be OrderID.

Image 12

Now, create the Sales cube. Uncheck the Auto-Build.

Image 13

Image 14

Image 15

Image 16

To create a duplicate measure group, you will need to create a duplicate Sales table in the Data Source View. We have to do this because SSAS does no allow you to create two measure groups for one table. Open the Data Source View; right click on en empty area and choose Add New Named Query. The query should be:

SQL
SELECT * FROM [Order Details]

Image 17

The Data Source View should look like:

Image 18

Under the Cube structur, tab, right click on the cube and choose to add a new measure group.

Image 19

Under the Dimension Usage tab, right click to add a new cube dimension. Choose Products. Rename the cube dimension to “Products 2”.

Image 20

Link the second product dimension to the first Sales measure group using a Many-to-Many relationship type.

Image 21

Deployment

If you are too lazy to follow the steps above, you can restore the OLAP database from this backup file: Northwind.abf. Make sure to update the connection and password to the NorthWind database.

Alternatively, you can restore the OLAP database from the XMLA script file: Northwind.xmla. Make sure to update the connection and password to the Northwind database and to process the database.

Points of interest

Next, you might want to try a multi-product analysis. This kind of analysis would answer questions like “How many customers who buy product A and B also buy product C?” As you might have guessed, the solution to this problem is the same. You will need to create a new Product cube Dimension and another duplicate of the Order Details measure group.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States United States
Igor is a business intelligence consultant working in Tampa, Florida. He has a BS in Finance from University of South Carolina and Masters in Information Management System from University of South Florida. He also has following professional certifications: MCSD, MCDBA, MCAD.

Comments and Discussions

 
QuestionCan you see the purchase pattern for over two products Pin
hshi772-Aug-13 10:11
hshi772-Aug-13 10:11 
AnswerRe: Can you see the purchase pattern for over two products Pin
Igor Krupitsky16-Dec-13 19:02
mvaIgor Krupitsky16-Dec-13 19:02 
QuestionI am sorry but I did not see any data mining stuffs in this article?? Pin
hshi772-Aug-13 10:08
hshi772-Aug-13 10:08 
Questionwhat is the exact software that is needed?????????/ Pin
Member 851424224-Dec-11 7:20
Member 851424224-Dec-11 7:20 
AnswerRe: what is the exact software that is needed?????????/ Pin
Igor Krupitsky2-Apr-12 20:08
mvaIgor Krupitsky2-Apr-12 20:08 
Generalsome other articles on SSAS Market Basket Analysis Pin
Sam Kane22-Mar-11 9:32
Sam Kane22-Mar-11 9:32 
GeneralVery helpful article Pin
filip115013-Jul-10 6:14
filip115013-Jul-10 6:14 
GeneralRe: Very helpful article Pin
Igor Krupitsky1-Nov-10 11:04
mvaIgor Krupitsky1-Nov-10 11:04 
GeneralThis article is not enought to MarketBasket Analysis Pin
PedroCGD5-Jan-10 4:12
PedroCGD5-Jan-10 4:12 
GeneralVery good tutorial Pin
Tae-Sung20-Nov-09 6:08
Tae-Sung20-Nov-09 6:08 
GeneralRe: Very good tutorial Pin
Member 390648314-Jun-10 20:36
Member 390648314-Jun-10 20:36 
Generalhello Pin
ahmadian77713-Apr-09 23:36
ahmadian77713-Apr-09 23:36 
GeneralRe: hello Pin
Guennady Vanin29-Aug-09 22:05
Guennady Vanin29-Aug-09 22:05 
ahmadian777 wrote:
please send sample data mining for me (sql server 2005)


How many gigabytes do you want?

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.