Data Mining Problem: Is that possible to predict Many Many Columns?

Hello,

Can someone please assist?
I have no problem using the provided Algorithms (NaiveBayes, Decision Tree, etc) from SQL Server 2005 Data Mining. For example: If I want to predict whether the customers want to buy bike from the following data, then I use Age, Salary, Gender as input/attribute/feature selection and BuyBike column as "Predict" column.

Table
Age Salary Gender BuyBike

However, say that I have 10,000 types of bikes to predict. How to do that?
Age Salary Gender BuyBike1 BuyBike2 BuyBike3 ...... BuyBike10000

Are there any online resources discussing this issue? I am desperately try to solve this problem. Please assist!

Mary

You can create a nested table. Based on data above, the model would look like:

(

[CustKey] KEY,

[Age] DOUBLE CONTINUOUS,

[Gender] TEXT DISCRETE,

[BikeModels] TABLE PREDICT

(

[Model] TEXT KEY

)

)

Then query the model (NB, DT etc) with a prediction statement like below:

SELECT Predict( BikeModels[, 5]) FROM Model

If you use the optional ",5" it will return the top 5 most likely predictions

More details:

http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1090.aspx (details on the nested table concept)

http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1061.aspx (impact of nested table on the model attributes)

http://msdn2.microsoft.com/en-us/library/ms132190.aspx (documentation for the DMX Predict function)

|||

Hello,

The provided solution (see the previous message) gave me the following error:

Query(2, 25) Parse: The syntax for '[,5]' is incorrect.

Please assist!

Mary

|||

Perhaps this is the right solution:

SELECT Predict( [BikeModels], 5) FROM Model

instead of

SELECT Predict( BikeModels[, 5]) FROM Model

Mary

|||

By [, 5] I meant that ", 5" is optional.

You can use either

SELECT Predict(BikeModels) FROM Model -- for all predictions

or

SELECT Predict(BikeModels, 5) FROM Model --for top 5 predictions

Sorry, I should have made it clear

Data Mining Model Viewer of Decision Tree out of memory error

We've successfully processed a large decision tree model in SQL Server 2005. When I try to view the tree in the mining model viewer, I get the following error:

TITLE: Microsoft Visual Studio

The tree graph cannot be created because of the following error:

'Exception of type 'System.OutOfMemoryException' was thrown.'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Viewers.SR&EvtID=ErrorCreateGraphFailed&LinkId=20476

The link provides no other documentaiton on the error.

We're using 64-bit SQL on a Dell Workstation running XP-64 with 16GB of memory. From my view of things we aren't close to running out of memory. Since the model processed and the error occurs when viewing the model, is this a problem with Visual Studio and nont necessarily Anlaysis Services?

Thanks in advance.

Nick

Hello, Nick
As you said, it seems a viewer problem and not a server problem.
There is a simple way to find out: start the Profiler tool (All Programs\SQL Server 2005\Performance Tools\SQL Server Profiler) and connect to the Analysis Services instance.

Then, try to browse the model. If the error is coming from the server, you should see it in the trace.

If possible, please save the trace file and post it (or send it to me directly,
bogdanc AT microsoft DOT com), so that we can trace the problem

thanks|||

Bogdan,

Thanks for looking into this. The model errored I don't see any errors in the trace but I'm not sure. I'm very familiar profiler in term of SQL Server but Analysis Services. I'll send the trace directly to your email.

Nick

|||It looks like the tree has a really large number of nodes (around 34 000) and the viewer has troubles handling that many nodes and runs out of memory. We will definitely try to address this issue in future versions.
Meanwhile, here are a few suggestions:
- Increase the COMPLEXITY_PENALTY or the MINIMUM_SUPPORT parameters of the mining model. These would effectively reduce the complexity of the tree, bringing it to a more manageable size.
- Use the "Generic Content Tree Viewer" sample. It can be downloaded from here http://www.sqlserverdatamining.com/DMCommunity/_Downloads/1348.aspx
It allows interactive navigation of the model content and it provides access to all the information inside the tree viewer
- Use the Data Mining add-in for Visio -- it allows rendering of only the top levels of a tree