Product Basket Problem

Hi,
I have to build a Poduct basket mining mode

Hi,

I have to build a Product basket mining mode such that when a customer selects

a product the model should be able to recommend him/her some more products.

I have a customer, product transaction table.

I have come across many typical examples where the output is

as follows

CustomerRecommended Products

Cust1a, b, c…….

Cust2e, b,

f……….

But I want the output of the model to be as follows

ProductRecommendedProducts

AB, C, D…….

BE, D, A…….

CD, A, F……

How should my mining model structure and the prediction

query look like?

Your model would look the same - all you would really want to do is to change how you query the model.

Essentially, the question you are asking is "if a customer bought product 'A', what other products might they buy". This is the same as "If customer 1 has product A in their basket, what products should I recommend." Therefore you need a query like this:

SELECT 'A' as [Product], Predict(Products,5) as [Recommended Products]
FROM MyRecommendationModel
NATURAL PREDICTION JOIN
(SELECT (SELECT 'A' as Product) AS Products) AS t

If you wanted to do this for all individual products, you would have to create a shaped input query from your product table. For example

SELECT t.Product, Predict(Products, 5) FROM [My Recommendation Model]
PREDICTION JOIN
SHAPE
{ OPENQUERY(MyDataSource],'SELECT Product FROM Products ORDER BY Product') }
APPEND
( {OPENQUERY([MyDataSource],'SELECT Product FROM Products ORDER BY Product'}
RELATE [Product] TO [Product])
AS [Products]
AS [t]
ON [My Recommendation Model].Products.Product=t.Products.Product

Processing Mining Models & Structures via SSIS

Still new to DM and SSIS...anyand all help is greatly appreciated!

In SSIS they say that you can use the Analysis Services Processing Task to process a mining model/mining structure, however, I do not see where you can give it a relational table to work off of. I know that I can use a data flow to do this but I wanted to go a different route if I could to process my models as I don't really necessarily need the data flow as what I am tring to do is pretty simple.

That brings me to a more general question, what is the best method for training your models using SSIS? I am building a new model everytime the package runs using some variables and the DDL task, running a query on it, and destroying it at the end of the package but I am having logistical problems training it outside of the data flow. I tried using the DM Query task but it requires that you output a result set and I am not sure if I can use it to create and process models.

I would think that they would just give you a DMX task similar to the SQL task but that does not seem to be the case. Also, when I browse the AS objects via the processing task I can only see the mining structures and not the mining models.

Am I just missing something here?

Cheers,

Dan Meyers

If you just want to process the mining models/structure, you should use the Analysis Services processing task. If you wnat more flexibility, you can use the Analysis Services Execute DDL task (you can do pretty much anything there). There is a feature called "out of line bindings" that you can specify if you want to proecess against different data than the model was bound to in the DSV - I'm not sure of the documentation level on this feature though.

You are right that you can't use the DM Query task for the reason you stated - you can use the DDL task as in this tip http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1443.aspx to process using DMX, or execute any other non-rowset returning DMX statement.

On the point of what happens during the data flow execution, the model/structure is only created at design time. The processing happens at run time. This means that if you wanted to run this package against another server, you would first have to create the model on that server. The easiest method may be to use SSMS to script the structure to create and then use the DDL task again

HTH

-Jamie

|||

Interesting...I did not realize that you could use the DDL task to execute DMX statments. Makes perfect sense since it all gets broken down to XMLA anyway. This is exactly the answer I was looking for. You are like the Yoda of SQL DM. Once again, thanks for the help.

-Dan Meyers

|||

Jamie,

I have another more question on this...Do I have to build a new DSV for my mining model or can I just use the same one that I am using for my OLAP cubes?

|||You can use the same DSV - the DSV is simply a collection of tables/named queries that don't necessarily have to be related

Process a Mining Model Programmatically

I have tried to process a data mining model that I have produce programmatically. I use the following code for processing

Private Sub BtProcess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtProcess.Click

' Now some different styles of processing

'Process mining structure first - don't process the models though

' ms.Process(ProcessType.ProcessStructure)

ms.Process(ProcessType.ProcessStructure)

DeclareBox.Text = "Process Data Mining Structure"

' Process the mining model only (because the structure has been processed above

' We could have said ProcessDefault without processing the structure first, though

mm.Process(ProcessType.ProcessDefault)

DeclareBox.Text = "Process Data Mining Model"

End Sub

I use the following code for data mining model and is correctly designed

Private Sub CreateMiningModel()

Dim mc As MiningModelColumn

mm = ms.MiningModels.Add(CbDatabase.Text & " miningmodelName") ', Utils.GetSyntacticallyValidID(miningmodelName, Type.GetType(MiningModel)))

mm.Algorithm = "Microsoft_Decision_Trees"

mm.AlgorithmParameters.Add("COMPLEXITY_PENALTY", 0.3)

mc = New MiningModelColumn("SUMCODE", "SUMCODE")

mc.SourceColumnID = ms.Columns("SUMCODE").ID

mc.Usage = "Key"

mm.Columns.Add(mc)

mc = New MiningModelColumn("STAFF_YES", "STAFF_YES")

mc.SourceColumnID = ms.Columns("STAFF_YES").ID

mc.Usage = "Input"

mm.Columns.Add(mc)

mc = New MiningModelColumn("D_G_OZODIS_IPER", "D_G_OZODIS_IPER")

mc.SourceColumnID = ms.Columns("D_G_OZODIS_IPER").ID

mc.Usage = "PredictOnly"

mm.Columns.Add(mc)

and I received the following error message. Any Ideas? What is wrong with this? Thank you in advance

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>test27</DatabaseID>
<MiningStructureID>test27 MiningStructureName</MiningStructureID>
<MiningModelID>test27 miningmodelName</MiningModelID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Mining Structure 'test27 MiningStructureName' completed successfully.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Processing Dimension 'test27 MiningStructureName ~MC-SUMCODE' failed.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Processing Dimension Attribute '(All)' completed successfully.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Processing Dimension Attribute 'OZOS NUM MANY' failed.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[VISIT].[OZOS_NUM_MANY] AS [VISITOZOS_NUM_MANY0_0]
FROM [VISIT] AS [VISIT]
Error Messages 1
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
Processing Dimension Attribute 'STAFF_YES' failed. 1 rows have been read.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
SQL queries 1
SELECT [VISIT].[STAFF_YES] AS [VISITSTAFF_YES0_0]
FROM [VISIT] AS [VISIT]
Error Messages 1
OLE DB error: OLE DB or ODBC error: Requested conversion is not supported.. Errors in the OLAP storage engine: An error occurred while the 'STAFF_YES' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Processing Dimension Attribute 'D_G_OZODIS_IPER' completed successfully.
Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00
Errors and Warnings from Response
OLE DB error: OLE DB or ODBC error: Requested conversion is not supported..
Errors in the OLAP storage engine: An error occurred while the 'STAFF_YES' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'test27 MiningStructureName ~MC-SUMCODE', Name of 'test27 MiningStructureName ~MC-SUMCODE' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'OZOS NUM MANY' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.
Errors in the high-level relational engine. The database operation was cancelled because of an earlier failure.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'test27 MiningStructureName ~MC-SUMCODE', Name of 'test27 MiningStructureName ~MC-SUMCODE' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'D_G_OZODIS_IPER' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed.

Manolis

There seems to be an error converting the STAFF_YES values to the mining structure column type. What is the data type (in the database) for this column and what is the Mining Structure column type for this column?

|||

I thing that you are correct

In the database STAFF_YES is of type bit and in the mining structure is a text. I tried to convert the type in the mining structure to bit but I did not find such a type. How can I do that programmatically? Do I have to change anything to the database? I hope not

Thank you in advance

|||

I tried this code

mc = New MiningModelColumn("STAFF_YES", STAFF_YES, Type.GetType(MiningModelColumn)))

I received an error in MiningModelColumn, what is the problem with MiningModelColumn. Is there any other code?

Thank you in advance

Manolis

|||

Bit should work fine when represented (in the mining structure) as Boolean.

Can you try to change the structure column type to boolean and then retrain your model? The data type is specific to the mining structure column (not the mining model column)

|||

Thank you very much your advice worked perfectly.

My problem now is to get the type programmatically and not type OleDbType.Boolean by me

Can I try the following code?

Seems that does not work?

What do I have to use for the ScalarMiningStructureColumn

smsc = New ScalarMiningStructureColumn("D_G_OZODIS_IPER") , Utils.GetSyntacticallyValidID("VISIT"), Type.GetType(ScalarMiningStructureColumn)))

smsc.IsKey = False

smsc.Content = "Discrete"

smsc.KeyColumns.Add("VISIT", "D_G_OZODIS_IPER", OleDbType.Boolean)

ms.Columns.Add(smsc)

Thank you in advance

Manolis