Click here to Skip to main content
Click here to Skip to main content

Market Basket Analysis in SSAS 2005+

, 28 Feb 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
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:

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:

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:

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:

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

However, this MDX returns the following result set:

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.

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).

Next, create a connection to the Nothwind database:

Next, create the data source view:

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

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

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

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:

SELECT * FROM [Order Details]

The Data Source View should look like:

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

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

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

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)

Share

About the Author

Igor Krupitsky
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 Pinmemberhshi772-Aug-13 10:11 
AnswerRe: Can you see the purchase pattern for over two products PinmemberIgor Krupitsky16-Dec-13 19:02 
QuestionI am sorry but I did not see any data mining stuffs in this article?? Pinmemberhshi772-Aug-13 10:08 
Questionwhat is the exact software that is needed?????????/ PinmemberMember 851424224-Dec-11 7:20 
hello,
 
i successfully installed the software that is given in this article, but i am unable to have that analysis services project template. May be i have installed the wrong version or there must be some other problem..
 
I am doing this on vista sp1. If anyone can help me, please go ahead..
 
if possible do reply with the exact link to download the software..
AnswerRe: what is the exact software that is needed?????????/ PinmemberIgor Krupitsky2-Apr-12 20:08 
Generalsome other articles on SSAS Market Basket Analysis PinmemberSam Kane22-Mar-11 9:32 
GeneralVery helpful article Pinmemberfilip115013-Jul-10 6:14 
GeneralRe: Very helpful article PinmemberIgor Krupitsky1-Nov-10 11:04 
GeneralThis article is not enought to MarketBasket Analysis PinmemberMember 14225145-Jan-10 4:12 
GeneralVery good tutorial PinmemberTae-Sung20-Nov-09 6:08 
GeneralRe: Very good tutorial PinmemberMember 390648314-Jun-10 20:36 
Generalhello Pinmemberahmadian77713-Apr-09 23:36 
GeneralRe: hello PinmemberGuennadi Vanine29-Aug-09 22:05 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.141022.2 | Last Updated 28 Feb 2009
Article Copyright 2009 by Igor Krupitsky
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid