Basket analysis & Association Mining

I'm looking for suggestions on the right design approach in relation to a problem that resembles Basket analysis. The data to be analyzed is a dimension Attribute_DIM and contains an ID, Attribute and Attribute_Value. Some examples of the data are :

ID Attribute Attribute_Value

1 Color Black

1 Movie Men in Black

1 Book Of Human Bondage

2 Color White

2 Movie Men in Black

2 Book Grapes of Wrath

We need to be able to analyze multiple selections of the dimension. For example,

Men In Black

Grapes Of Wrath Of Human Bondage

Men In Black Black 1 1

White 1 0

I have had some success using the Association Algorithm Mining Model. I think It is an overkill since I only need descriptive and no predictive analysis.

I'm looking for some ideas on the right approach to this problem. Ideally, we need to present the data in a cube and have the possibility to perform member analysis of the dimension.

I have looked at several articles (including http://msdn2.microsoft.com/en-us/library/aa902637(sql.80).aspx and http://www.aspnetpro.net/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp). I'm not convinced those are the solutions and would appreciate any insight into this problem.

Thank you,

Anna.

You might try an OLAP cube with a many to many dimension - described here: http://msdn2.microsoft.com/en-us/library/ms170463.aspx. There's also a short book dedicated to the feature by Marco Russo (http://www.lulu.com/content/812235)

|||

So, if I apply your solution, I would have two dimensions from the same source and establish a many-to-many relationship between the two, right? Wouldn't this limit the analysis to only two dimensions; i.e., if I needed to analyze 3 attributes and how the basket looks in that case; I need to be able to analyze an open-ended number of attributes on separate axes.

This article comes closest to describing my problem: http://msdn2.microsoft.com/en-us/library/aa902637(sql.80).aspx (Analysis Services: DISTINCT COUNT, Basket Analysis, and Solving the Multiple Selection of Members Problem). The article is based on SQL Server 2000. I would like to know if there is a simpler and different approach with SQL Server 2005.

|||Actually, in response to your original question, Association Rules is generally used for descriptive analysis, not predictive analysis. It is a rather recent innovation that has allowed AR to be used for predictive purposes. I think your best bet is to use AR.|||Thank you for the suggestion. I will go ahead with the idea of using a Mining model with Association rules.

Basket Analysis

Hi everyone,

Who knows how to write a MDX to do basket analysis based on cube?

Thanks

Hello! I think that this is a task for the distinct count aggregation method on the measure you have choosen,

You have also the option to work with data mining in SSAS2005 for this analytic topic.

HTH

Thomas Ivarsson

|||

Incase if you use Predictive Modelling algorithm in datamining for Market Basket Analysis, Use DMX to achieve this.

Thanks

Subhash Subramanyam

|||

Sacha has just done a really good blog on this:

http://blogs.adatis.co.uk/blogs/sachatomey/archive/2007/08/22/basket-analysis-using-analysis-services-2005.aspx

HTH

Tim

Basket Analysis

Hi,
Does anyone know of any good examples of cube construction
for basket analysis, I can't seem to find much of anything
on this particular type of analysis.
Ta
Paul
You might find some interesting material here:
http://www.ralphkimball.com/html/articles.html
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:389501c4aeec$a2c634b0$a601280a@.phx.gbl...
> Hi,
> Does anyone know of any good examples of cube construction
> for basket analysis, I can't seem to find much of anything
> on this particular type of analysis.
> Ta
> Paul

Basket Analysis

Hi,
Does anyone know of any good examples of cube construction
for basket analysis, I can't seem to find much of anything
on this particular type of analysis.
Ta
PaulYou might find some interesting material here:
http://www.ralphkimball.com/html/articles.html
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:389501c4aeec$a2c634b0$a601280a@.phx.gbl...
> Hi,
> Does anyone know of any good examples of cube construction
> for basket analysis, I can't seem to find much of anything
> on this particular type of analysis.
> Ta
> Paul

Basket Analysis

Hi everyone,

Who knows how to write a MDX to do basket analysis based on cube?

Thanks

Hello! I think that this is a task for the distinct count aggregation method on the measure you have choosen,

You have also the option to work with data mining in SSAS2005 for this analytic topic.

HTH

Thomas Ivarsson

|||

Incase if you use Predictive Modelling algorithm in datamining for Market Basket Analysis, Use DMX to achieve this.

Thanks

Subhash Subramanyam

|||

Sacha has just done a really good blog on this:

http://blogs.adatis.co.uk/blogs/sachatomey/archive/2007/08/22/basket-analysis-using-analysis-services-2005.aspx

HTH

Tim

Basic Reporting Services / Analysis Services report - flattened rowset problem

I am just getting started with Reporting Services 2000 and I need some help PLEASE! My source is MSAS 2000 cube. This whole flattened rowset is confusing me. Insight will be HUGELY appreciated.

I have a MSAS hierarchy that looks like this:
WORLD
-North America
USA
Canada
Mexico
-Europe
UK
Germany
etc...

I use the following MDX to get my dataset
Select [Amount] on Columns, [World].AllMembers on ROWS From Cube

I get the following Reporting Services dataset with THREE columns:
(nothing) 5000
North America 3000
North America USA 1000
North America Canada 1000
North America Mexico 1000
Europe 2000
Europe UK 1000
Europe Germany 1000

What I want is a report that looks like this -- (Two columns)
DESIRED REPORT
Location Amount
+ North America 3000
+ Europe 2000
World 5000

When the user Clicks the + icon , The report should expand. Like below:
DESIRED REPORT AFTER DRILLDOWN
Location Amount
USA 1000
Canada 1000
Mexico 1000
+ North America 3000
+ Europe 2000
World 5000

This seems like it should be the most basic report possible for a cube with a hierarchy. However, it seems that no matter what I try, I get variations of the following:
Three columns:
Continent Country Amount
+ blank here!
+ North America (nothing!!!)
+ Europe (nothing!!!)

When a + icon is clicked I get (still no AMOUNTS!!!):
Continent Country Amount
+ blank here!
- North America (nothing!!!)
+USA (nothing!!!)
+Canada (nothing!!!)
+Mexico (nothing!!!)
+ Europe (nothing!!!)

and another clicks on USA for example yields:
Continent Country Amount
+ blank here!
- North America (nothing!!!)
-USA (nothing!!!)
1000
+Canada (nothing!!!)
+Mexico (nothing!!!)
+ Europe (nothing!!!)

The desired report is a simple report. It seems like a good candidate for a wizard. However, using the wizard, I get results such as the one above. The example above uses a wizard stepped format with subtotals and drilldown enabled.

Moving away from the wizard... If I build the report without the wizard some report rows have no titles and/or no amounts and/or amounts are repeated multiple times down the rows. The only thing I can think of is that I need to IIF statements in every row to hide these rows which seems inefficient. I think I must be doing something wrong.

Do I need to change my MDX in some way to eliminate rows with Blanks in the Continent and Country columns? Or is there something simple I can do in the report definition to make it behave differentlty?

Can someone get me started please?

This MSDN paper discusses how to work with flattened rowsets in Reporting Services, and includes some sample drilldown reports that should point you in the right direction. Basically, you can set filtering in your groups to select the right rows for a given group, or have the MDX query only return data from the lowest level:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp?frame=true

>>

Integrating Analysis Services with Reporting Services

Sean Boon
Microsoft Corporation

June 2004

Applies to:
Microsoft SQL Server 2000

Summary: Create a compelling solution for your customer that defines and manages great-looking Analysis Services reports, and quickly answers analytical questions to improve traditional reporting scenarios. (33 printed pages)

...

Building "Drill-Down" Reports against OLAP Data

Up until this point, all of the data contained within the example reports has belonged to only one level from each dimension. For example, if you ask the question, "What are unit sales by promotion media type?" even though the user might be given the option to select any available time period, the resulting dataset will only contain data from a single level of each dimension. The included level of the time dimension might change each time the report is executed, but for each report execution only one level of the time dimension will be referenced. What happens when you need to add interactivity to the report by allowing the users to "drill-down" on members in the table?

...

>>

|||

I am having the same problem. When I run my MDX query in designer I do not get the "All" member for any of my dimensions. Displaying that member is a requirement for all of my reports.

I did review the above mentioned white paper as well as download the sample reports, however none of those reports uses the "ALL" member.

Any suggections?

Here is my simple code:

Thanks in advance !!

SELECT { [Measures].[Responses], [Measures].[Responders], [Measures].[Trans Amount] } ON COLUMNS,
{ { { [Providers].Members} * { [Product].Members } * { [Channel].Members } } }
Dimension Properties Member_Unique_Name ON ROWS
FROM [Programmatic_Balance_Build]

|||

The problem with returning an "All" member in a flattened rowset is discussed elsewhere in the above paper:

>>

Another behavior of the flattening algorithm is that the "[(ALL)]" level is not included in the dataset. This has a couple of implications. The first is that if you want to include data from the "All" member of a dimension, you'll need to create a calculated member to represent this member. This can be accomplished in a couple of different ways. The first method would be to create a calculated member on the Measures dimension and for the definition of the calculated member refer to the current member's name or unique name. There are several examples of this method represented in later sections of this whitepaper.

Note The "All" level of a dimension is not included in the field set that is returned to Reporting Services.

The second implication of the "All" level not being represented in the dataset is that calculated members, usually defined without a parent member, will need to change so that they do have a parent member. This only applies in cases where the calculated member does not belong to the Measures dimension. In many cases, when calculated members are defined on a non-Measures dimension, the parent member property is left blank. This can be changed in the calculated member dialog box as shown below

>>

|||

Thank you Deepak -

This whitepaper opened up a lot of concepts for RS and AS. It was just what I needed. Thank you for pointing me in this direction.

I still don't like some of the integration issues between RS and AS . But I now understand the behaviors and work with them. And most importantly, still deliver the reports.

Basic Reporting Services / Analysis Server report - flattened rowset problem

I am just getting started with Reporting Services 2000 and I need some
help PLEASE! My source is MSAS 2000 cube. This whole flattened rowset
is confusing me. Insight will be HUGELY appreciated.
I have a MSAS hierarchy that looks like this:
WORLD
-North America
--USA
--Canada
--Mexico
-Europe
--UK
--Germany
--etc...
I use the following MDX to get my dataset
Select [Amount] on Columns, [World].AllMembers on ROWS From Cube
I get the following Reporting Services dataset with THREE columns:
(nothing) 5000
North America 3000
North America USA 1000
North America Canada 1000
North America Mexico 1000
Europe 2000
Europe UK 1000
Europe Germany 1000
What I want is a report that looks like this -- (Two columns)
DESIRED REPORT
Location Amount
+ North America 3000
+ Europe 2000
World 5000
When the user Clicks the + icon , The report should expand. Like
below:
DESIRED REPORT AFTER DRILLDOWN
Location Amount
USA 1000
Canada 1000
Mexico 1000
+ North America 3000
+ Europe 2000
World 5000
This seems like it should be the most basic report possible for a cube
with a hierarchy. However, it seems that no matter what I try, I get
variations of the following:
Three columns:
Continent Country Amount
+ blank here!
+ North America (nothing!!!)
+ Europe (nothing!!!)
When a + icon is clicked I get (still no AMOUNTS!!!):
Continent Country Amount
+ blank here!
- North America (nothing!!!)
+USA (nothing!!!)
+Canada (nothing!!!)
+Mexico (nothing!!!)
+ Europe (nothing!!!)
and another clicks on USA for example yields:
Continent Country Amount
+ blank here!
- North America (nothing!!!)
-USA (nothing!!!)
1000
+Canada (nothing!!!)
+Mexico (nothing!!!)
+ Europe (nothing!!!)
The desired report is a simple report. It seems like a good candidate
for a wizard. However, using the wizard, I get results such as the one
above. The example above uses a wizard stepped format with subtotals
and drilldown enabled.
Moving away from the wizard... If I build the report without the wizard
some report rows have no titles and/or no amounts and/or amounts are
repeated multiple times down the rows. The only thing I can think of
is that I need to IIF statements in every row to hide these rows which
seems inefficient. I think I must be doing something wrong.
Do I need to change my MDX in some way to eliminate rows with Blanks in
the Continent and Country columns? Or is there something simple I can
do in the report definition to make it behave differentlty?
Can someone get me started please?Joel,
The reason you're getting the blank row is because of the 'total' rows
in your dataset (ie North America <blank> 3000). The alternative would
be to remove this total row (via modifying your MDX) and letting the
report total for the continent by doing a SUM on the Amount col.
Check the visibility property of the Amount textbox to make sure it is
toggled by the [+] Continent textbox.
Also, to hide duplicates, there is a property on a Textbox which will
hide repeated values.
Hopefully that will get you started.
Andy Potter|||Thank you for your response -
For your first suggestion, do you mean to create a row in the report
with formula USA+Canada+Mexica and a second row with formula
UK+Germany? This isn't really my hierarchy, in reality, the dimension
has 7 levels and hundreds of thousands of members so manual totals are
not an option.
Or do you mean to suppress the rows with a blank via my MDX? If so,
any ideas on how to formulate this?
I tried the toggle for the amount text box and it has no effect. the
Hide Repeated on the Amount text box has no effect either. I tried
hiding repeats for amount conditioned on Continent and Country.|||Thank you for your response -
For your first suggestion, do you mean to create a row in the report
with formula USA+Canada+Mexica and a second row with formula
UK+Germany? This isn't really my hierarchy, in reality, the dimension
has 7 levels and hundreds of thousands of members so manual totals are
not an option.
Or do you mean to suppress the rows with a blank via my MDX? If so,
any ideas on how to formulate this?
I tried the toggle for the amount text box and it has no effect. the
Hide Repeated on the Amount text box has no effect either. I tried
hiding repeats for amount conditioned on Continent and Country.|||Thank you for your response -
For your first suggestion, do you mean to create a row in the report
with formula USA+Canada+Mexica and a second row with formula
UK+Germany? This isn't really my hierarchy, in reality, the dimension
has 7 levels and hundreds of thousands of members so manual totals are
not an option.
Or do you mean to suppress the rows with a blank via my MDX? If so,
any ideas on how to formulate this?
I tried the toggle for the amount text box and it has no effect. the
Hide Repeated on the Amount text box has no effect either. I tried
hiding repeats for amount conditioned on Continent and Country.