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

Processor model

I have a server with 4 dual core processors.
If I install SQL Server 2005 Express edition on it (which is free), I do not
have to disable other 3 processors because by default express edition will
use at most 1 processor?
But if I use SQL Server 2005 Workgroup (or Standard) edition with one
processor license, I would have to disable other 3 processors at the bios
level to be legal? If I don't disable it, will it use all 4 processors even
though I only bought license for one processor?
Help would be appreciated.
JJ,
I could be wrong, but I believe you cannot get around licensing by
restricting the number of processers. I believe if you buy a license for one
processor, then you cannot use it on any server with more than one physical
processor.
-- Bill
"JJ" <nospam@.nospam.com> wrote in message
news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>I have a server with 4 dual core processors.
> If I install SQL Server 2005 Express edition on it (which is free), I do
> not have to disable other 3 processors because by default express edition
> will use at most 1 processor?
> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
> processor license, I would have to disable other 3 processors at the bios
> level to be legal? If I don't disable it, will it use all 4 processors
> even though I only bought license for one processor?
> Help would be appreciated.
>
|||> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
> processor license, I would have to disable other 3 processors at the bios
> level to be legal?
Yes.

> If I don't disable it, will it use all 4 processors even though I only
> bought license for one processor?
SQL Server will use the lesser of either the number of supported or
installed processors. Workgroup supports up to 2 CPUs and Standard up to 4.
However you will be violating the license agreement unless you have
processor licenses for each processor socket available to the OS. See
http://www.microsoft.com/sql/howtobuy/faq.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"JJ" <nospam@.nospam.com> wrote in message
news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>I have a server with 4 dual core processors.
> If I install SQL Server 2005 Express edition on it (which is free), I do
> not have to disable other 3 processors because by default express edition
> will use at most 1 processor?
> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
> processor license, I would have to disable other 3 processors at the bios
> level to be legal? If I don't disable it, will it use all 4 processors
> even though I only bought license for one processor?
> Help would be appreciated.
>
|||But if I installed express 2005, then no need to disable processors, right?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4E406247-920D-4708-AE2F-3D8F816A0E75@.microsoft.com...
> Yes.
>
> SQL Server will use the lesser of either the number of supported or
> installed processors. Workgroup supports up to 2 CPUs and Standard up to
> 4. However you will be violating the license agreement unless you have
> processor licenses for each processor socket available to the OS. See
> http://www.microsoft.com/sql/howtobuy/faq.mspx
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JJ" <nospam@.nospam.com> wrote in message
> news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>
|||> But if I installed express 2005, then no need to disable processors,
> right?
Correct. SQL Server Express is free so the processing licensing model does
not apply,
Hope this helps.
Dan Guzman
SQL Server MVP
"JJ" <nospam@.nospam.com> wrote in message
news:OZo$JxqTHHA.5060@.TK2MSFTNGP06.phx.gbl...
> But if I installed express 2005, then no need to disable processors,
> right?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:4E406247-920D-4708-AE2F-3D8F816A0E75@.microsoft.com...
>
sql

Processor model

I have a server with 4 dual core processors.
If I install SQL Server 2005 Express edition on it (which is free), I do not
have to disable other 3 processors because by default express edition will
use at most 1 processor?
But if I use SQL Server 2005 Workgroup (or Standard) edition with one
processor license, I would have to disable other 3 processors at the bios
level to be legal? If I don't disable it, will it use all 4 processors even
though I only bought license for one processor?
Help would be appreciated.JJ,
I could be wrong, but I believe you cannot get around licensing by
restricting the number of processers. I believe if you buy a license for one
processor, then you cannot use it on any server with more than one physical
processor.
-- Bill
"JJ" <nospam@.nospam.com> wrote in message
news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>I have a server with 4 dual core processors.
> If I install SQL Server 2005 Express edition on it (which is free), I do
> not have to disable other 3 processors because by default express edition
> will use at most 1 processor?
> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
> processor license, I would have to disable other 3 processors at the bios
> level to be legal? If I don't disable it, will it use all 4 processors
> even though I only bought license for one processor?
> Help would be appreciated.
>|||> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
> processor license, I would have to disable other 3 processors at the bios
> level to be legal?
Yes.

> If I don't disable it, will it use all 4 processors even though I only
> bought license for one processor?
SQL Server will use the lesser of either the number of supported or
installed processors. Workgroup supports up to 2 CPUs and Standard up to 4.
However you will be violating the license agreement unless you have
processor licenses for each processor socket available to the OS. See
http://www.microsoft.com/sql/howtobuy/faq.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"JJ" <nospam@.nospam.com> wrote in message
news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>I have a server with 4 dual core processors.
> If I install SQL Server 2005 Express edition on it (which is free), I do
> not have to disable other 3 processors because by default express edition
> will use at most 1 processor?
> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
> processor license, I would have to disable other 3 processors at the bios
> level to be legal? If I don't disable it, will it use all 4 processors
> even though I only bought license for one processor?
> Help would be appreciated.
>|||But if I installed express 2005, then no need to disable processors, right?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4E406247-920D-4708-AE2F-3D8F816A0E75@.microsoft.com...
> Yes.
>
> SQL Server will use the lesser of either the number of supported or
> installed processors. Workgroup supports up to 2 CPUs and Standard up to
> 4. However you will be violating the license agreement unless you have
> processor licenses for each processor socket available to the OS. See
> http://www.microsoft.com/sql/howtobuy/faq.mspx
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JJ" <nospam@.nospam.com> wrote in message
> news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>|||> But if I installed express 2005, then no need to disable processors,
> right?
Correct. SQL Server Express is free so the processing licensing model does
not apply,
Hope this helps.
Dan Guzman
SQL Server MVP
"JJ" <nospam@.nospam.com> wrote in message
news:OZo$JxqTHHA.5060@.TK2MSFTNGP06.phx.gbl...
> But if I installed express 2005, then no need to disable processors,
> right?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:4E406247-920D-4708-AE2F-3D8F816A0E75@.microsoft.com...
>

Processor model

I have a server with 4 dual core processors.
If I install SQL Server 2005 Express edition on it (which is free), I do not
have to disable other 3 processors because by default express edition will
use at most 1 processor?
But if I use SQL Server 2005 Workgroup (or Standard) edition with one
processor license, I would have to disable other 3 processors at the bios
level to be legal? If I don't disable it, will it use all 4 processors even
though I only bought license for one processor?
Help would be appreciated.JJ,
I could be wrong, but I believe you cannot get around licensing by
restricting the number of processers. I believe if you buy a license for one
processor, then you cannot use it on any server with more than one physical
processor.
-- Bill
"JJ" <nospam@.nospam.com> wrote in message
news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>I have a server with 4 dual core processors.
> If I install SQL Server 2005 Express edition on it (which is free), I do
> not have to disable other 3 processors because by default express edition
> will use at most 1 processor?
> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
> processor license, I would have to disable other 3 processors at the bios
> level to be legal? If I don't disable it, will it use all 4 processors
> even though I only bought license for one processor?
> Help would be appreciated.
>|||> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
> processor license, I would have to disable other 3 processors at the bios
> level to be legal?
Yes.
> If I don't disable it, will it use all 4 processors even though I only
> bought license for one processor?
SQL Server will use the lesser of either the number of supported or
installed processors. Workgroup supports up to 2 CPUs and Standard up to 4.
However you will be violating the license agreement unless you have
processor licenses for each processor socket available to the OS. See
http://www.microsoft.com/sql/howtobuy/faq.mspx
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JJ" <nospam@.nospam.com> wrote in message
news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>I have a server with 4 dual core processors.
> If I install SQL Server 2005 Express edition on it (which is free), I do
> not have to disable other 3 processors because by default express edition
> will use at most 1 processor?
> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
> processor license, I would have to disable other 3 processors at the bios
> level to be legal? If I don't disable it, will it use all 4 processors
> even though I only bought license for one processor?
> Help would be appreciated.
>|||But if I installed express 2005, then no need to disable processors, right?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4E406247-920D-4708-AE2F-3D8F816A0E75@.microsoft.com...
>> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
>> processor license, I would have to disable other 3 processors at the bios
>> level to be legal?
> Yes.
>> If I don't disable it, will it use all 4 processors even though I only
>> bought license for one processor?
> SQL Server will use the lesser of either the number of supported or
> installed processors. Workgroup supports up to 2 CPUs and Standard up to
> 4. However you will be violating the license agreement unless you have
> processor licenses for each processor socket available to the OS. See
> http://www.microsoft.com/sql/howtobuy/faq.mspx
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JJ" <nospam@.nospam.com> wrote in message
> news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>>I have a server with 4 dual core processors.
>> If I install SQL Server 2005 Express edition on it (which is free), I do
>> not have to disable other 3 processors because by default express edition
>> will use at most 1 processor?
>> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
>> processor license, I would have to disable other 3 processors at the bios
>> level to be legal? If I don't disable it, will it use all 4 processors
>> even though I only bought license for one processor?
>> Help would be appreciated.
>|||> But if I installed express 2005, then no need to disable processors,
> right?
Correct. SQL Server Express is free so the processing licensing model does
not apply,
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JJ" <nospam@.nospam.com> wrote in message
news:OZo$JxqTHHA.5060@.TK2MSFTNGP06.phx.gbl...
> But if I installed express 2005, then no need to disable processors,
> right?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:4E406247-920D-4708-AE2F-3D8F816A0E75@.microsoft.com...
>> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
>> processor license, I would have to disable other 3 processors at the
>> bios level to be legal?
>> Yes.
>> If I don't disable it, will it use all 4 processors even though I only
>> bought license for one processor?
>> SQL Server will use the lesser of either the number of supported or
>> installed processors. Workgroup supports up to 2 CPUs and Standard up to
>> 4. However you will be violating the license agreement unless you have
>> processor licenses for each processor socket available to the OS. See
>> http://www.microsoft.com/sql/howtobuy/faq.mspx
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "JJ" <nospam@.nospam.com> wrote in message
>> news:%23FeNFl8SHHA.920@.TK2MSFTNGP05.phx.gbl...
>>I have a server with 4 dual core processors.
>> If I install SQL Server 2005 Express edition on it (which is free), I do
>> not have to disable other 3 processors because by default express
>> edition will use at most 1 processor?
>> But if I use SQL Server 2005 Workgroup (or Standard) edition with one
>> processor license, I would have to disable other 3 processors at the
>> bios level to be legal? If I don't disable it, will it use all 4
>> processors even though I only bought license for one processor?
>> Help would be appreciated.
>>
>

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