Processing Decision Tree Example for "Data Mining with SQL Server 2005" Hangs

I purchased the "Data Mining with SQL Server 2005" book and I am attempting to follow along with the Movie Click database example. I have downloaded the MovieClick database and converted it to SQL Server 2005 RTM. I have successfully built the Movie Trees and Movie Bayes models, but I cannot train either model. I am able to process the structure of both models, but any attempt to train results in the the processing hanging without any result for hours. If I eliminate nested case tables to get down to one or two nested case tables everything processes correctly. I have 1 GB of memory available, but when I process msmdsrv is not doing anything. Any ideas?

Thanks
- Steve

 

Hello,

Could you please update the following configuration settings to the
specified values?
Use SQL Server management Studio, connect to Analysis Services 2005,,
right-click on the server and select Properties, then change the following
entries to the indicated values. You might need to switch the property
viewer to Advanced mode (there is a check-box labeled 'Advanced properties'
in the lower part of the box)

Configuration entry Default Value Change
To:
Thread Pool \process \MaxThreads 64 128
Thread Pool \process \ concurrency 2 -4
(minus 4)

Hope this helps,
--
--
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Please do not send email directly to this alias. It is for newsgroup
purposes only.

thanks,
bogdan

 

Bogdan,
Thanks for your quick reply. I made the changes you suggested, restarted AS and then tried to train the models. Unfortunately the result was the same as before. The processing progress monitor shows "Reading cases: 0 cases read." and just hangs.

- Steve

 

A quick question, how many processors do you have? Also, are they hyperthreaded, etc.?

Thanks

 

I have one Pentium M processor running at 1.3 GHz with no hyperthreading and 1 GB RAM. In other words I am running this on a laptop. I have no problem processing the Adventure Works DM models and as I stated previously I can process the example models if I remove most of the nested case references.

Thanks
- Steve

 

I have exactly the same issue. Initially the processing would hang at a very
early stage (Build Processing Schedule - Integer Data = 1526) without
hitting the SQL data at all. Having made the suggested changes to the Thread
Pool (which I don't think make any difference - it's the restart of AS I did
after changing the settings that helped it get further) it gets a bit
further but now hangs at the Training mining model stage and hangs SSMS.
After I kill SSMS, restart it and try to process again it hangs back at the
original stage (but it at least lets me stop it). If I then restart Analysis
Services it gets further again but hangs at the Training mining model stage.
Repeat ad nauseum.....

XP Pro SP2, SQL2005 DEV RTM, P4 3.2 HT enabled (same problem on my non HT
laptop though)

I can process the AW DB models with no issues at all and from the traces it
seems the SQL side of things is fine but it's getting hung up in AS for some
reason.

 

Hello, Steve, Jasper

Please try doing the changes I mentioned before plus this new one regarding
the Coordinator Execution Mode:

CoordinatorExecutionMode -4 -> -16
Thread Pool \process \MaxThreads 64 -> 128
Thread Pool \process \ concurrency 2 -> -4

As Jasper mentioned in his posting, these changes may require restarting the
server before becoming effective.

With the three changes above, I got the whole MovieSurvey database to
process on a single CPU machine.

Jasper, I hope this will also fix the problem you found. Please let us know
if it still does not work.


--
--
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Please do not send email directly to this alias. It is for newsgroup
purposes only.

thanks,
bogdan

 

Hi Bogdan,

Thanks - that worked

Although BOL has some limited details on some of the configuration options
available in AS2005 do you know if there is or will be a whitepaper
surrounding these? In AS2000 there was not a huge amount of "tweaking"
available on the server side but in AS2005 it seems there's a lot more. I'd
just like to understand a bit more about what the effect of these
configuration changes was.

 

I don't know yet of any white paper for the configuration options, I'll post
here as soon as I find out anything about this.

I can try to explain the changes I suggested.

The MovieSurvey mining models are having a rather large number of nested
tables (7). Aggregating the nested tables together with the main table to
produce cases for model training is a parallel operation, which involves a
number of parallel tasks proportional with the number of tables. Changing
the CoordinatorExecutionMode from -4 to -16 is actually allowing the server
to run simultaneously 16 concurrent tasks per each available CPU (or 16
overall, in the case of 1 CPU).

On the 1-cpu machines, the default of 4 concurrent tasks (-4 == 4 per CPU =
4 overall) was obviously too small.

--
--
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Please do not send email directly to this alias. It is for newsgroup
purposes only.

thanks,
bogdan

 

Bogdan,
Your solution worked for me as well. I share Jasper's desire for more information regarding AS 2005 configuration options. Thank you very much for your help. Now on to learning more about data mining in SQL Server 2005...

- Steve