Advice on Partitioning and Processing

I'd appreciate some advice on the following:

I'm building a cube which is based on data in a SQL 2000 DB. There are around 1.2 billion rows of fact data in 26 tables. Each table uses approx 6 Gig. There are 4 Dimensions, 3 dimensions have less than a 1000 members with the 4th having 65000. For convenience I am creating 26 Cube partitions aligned to the DB tables, each partition will have around 50 million rows. Does this sound like a reasonable proposal? The reason I ask is that I am seeing very poor performance whilst calling ProcessData. I have processed various numbers of partitions in parallel and cannot achieve the same times that I can get on the existing AS2000 rig using the Parallel Processing Utility.

I have tested the data read by writing a .net application to execute the same DB queries in parallel that the partitions use (achieving 200 million rows in 20 minutes) and there are no issues related to network, disk or the DB server.

Processing seems to start well but around a minute in the Network utilisation drops down to 10 percent and CPU utilisation to 30%. It then goes back up to 100% for a short period and then drops again. This is repeated continually until processing is finished. At current speeds it will take 26 hours to complete as opposed to 10 hours on AS2000. The box is a 64-bit 4way hyperthreaded with 32 GB of RAM. AS2005 is Enterprise with SP1.

Any suggestions would be appreciated as I'm running out of ideas!

Rob.

You should check yourself against best practices you would find the project REAL implemetation http://www.microsoft.com/sql/solutions/bi/projectreal.mspx

I would say the partition size is a bit big. You should try and partition some more for and split a single partition in two. But that doesnt look like a major problem.

Question for you: in AS2005 you processing partitions in parallel. How many? Is the pattern the same for any number of partitions?

What OLEDB provider you are using to read data from SQL Server?

The reading rates looks to be slow. See where the bottleneck is. In your test application, the fair comparison not only when you execute SQL query, but you also retrevie all the data returned by the query.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Advice needed - how to build skills as a MS database administrator

Would appreciate some advice for a small/mid company IT MCSE that is now
required to pick up MCDBA skills (with a training budget that limits me to
Barnes&Noble).
Our environment has an SQL 2000 on our ERP server; both will be upgraded in
November, where we will have SQL 2005.
I've scanned over free technet documentation, and subscribed to newsletters
- but could use a bit more direction.
I'd be interested in an SQL guru's advice on how I could ramp up my skill
quickly - to at least maintain an existing SQL database with an app server.
High quality books (they don't need to start at ground zero, I am a
proficient SQL writer already), and quality websites, would be greatly
appreciated. I'm looking for materials that will show me how to provide
proper maintenace and optimize performance of an SQL server.
Thanks in advance,
--
pbrill1You might find something useful here.
http://msdn2.microsoft.com/en-us/virtuallabs/aa740409.aspx
http://www.microsoft.com/learning/assessment/ind/windowsserver/default.mspx#EWD
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"pbrill1" wrote:
> Would appreciate some advice for a small/mid company IT MCSE that is now
> required to pick up MCDBA skills (with a training budget that limits me to
> Barnes&Noble).
> Our environment has an SQL 2000 on our ERP server; both will be upgraded
> in
> November, where we will have SQL 2005.
> I've scanned over free technet documentation, and subscribed to
> newsletters
> - but could use a bit more direction.
> I'd be interested in an SQL guru's advice on how I could ramp up my skill
> quickly - to at least maintain an existing SQL database with an app
> server.
> High quality books (they don't need to start at ground zero, I am a
> proficient SQL writer already), and quality websites, would be greatly
> appreciated. I'm looking for materials that will show me how to provide
> proper maintenace and optimize performance of an SQL server.
> Thanks in advance,
> --
> pbrill1|||Hello!
There are self-training kits you can buy. They are very good.
MCDBA certificate is for SQL Server 2000. There is a new certification path
for SQL Server 2005.
First you need to achieve MCTS: SQL Server 2005 certificate by sitting for
70-431 exam.
http://www.microsoft.com/learning/mcp/mcts/sql/default.mspx
And then you need to achieve MCITP: DBA certificate by sitting for 70-443
and 70-444 exams.
http://www.microsoft.com/learning/mcp/mcitp/dbadmin/default.mspx
You can find recommended sources by Microsoft from the links above to study
on these exams.
Ekrem Ã?nsoy
"pbrill1" <pbrill1@.discussions.microsoft.com> wrote in message
news:36FADB02-C067-42FC-84DD-9A36425B9997@.microsoft.com...
> Would appreciate some advice for a small/mid company IT MCSE that is now
> required to pick up MCDBA skills (with a training budget that limits me to
> Barnes&Noble).
> Our environment has an SQL 2000 on our ERP server; both will be upgraded
> in
> November, where we will have SQL 2005.
> I've scanned over free technet documentation, and subscribed to
> newsletters
> - but could use a bit more direction.
> I'd be interested in an SQL guru's advice on how I could ramp up my skill
> quickly - to at least maintain an existing SQL database with an app
> server.
> High quality books (they don't need to start at ground zero, I am a
> proficient SQL writer already), and quality websites, would be greatly
> appreciated. I'm looking for materials that will show me how to provide
> proper maintenace and optimize performance of an SQL server.
> Thanks in advance,
> --
> pbrill1|||Thank you for the information.
At present, I won't be looking to certify as an SQL DBA (on a personal note
- no training funding until well into 2008) - so I need to run up the
learning curve on a budget of $50 or less.
Microsoft books (hardcover or online) or good how-to websites/blogs that can
show me how to 1) set up a maintenance schedule for SQL 2005 (what to
maintain/how often), and 2) tips/tricks to provide overall performance
improvements (i.e., I don't intend to dive into complex coding to seek
performance improvements) - would be the type of resource that I'm looking
for. A Microsoft SQL 2005 "DBA Secrets" type reference, maybe?
Any advice on inexpensive (again $50 range) resources would be greatly
appreciated.
--
pbrill1
"Dave Patrick" wrote:
> You might find something useful here.
> http://msdn2.microsoft.com/en-us/virtuallabs/aa740409.aspx
> http://www.microsoft.com/learning/assessment/ind/windowsserver/default.mspx#EWD
>
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "pbrill1" wrote:
> > Would appreciate some advice for a small/mid company IT MCSE that is now
> > required to pick up MCDBA skills (with a training budget that limits me to
> > Barnes&Noble).
> >
> > Our environment has an SQL 2000 on our ERP server; both will be upgraded
> > in
> > November, where we will have SQL 2005.
> >
> > I've scanned over free technet documentation, and subscribed to
> > newsletters
> > - but could use a bit more direction.
> >
> > I'd be interested in an SQL guru's advice on how I could ramp up my skill
> > quickly - to at least maintain an existing SQL database with an app
> > server.
> > High quality books (they don't need to start at ground zero, I am a
> > proficient SQL writer already), and quality websites, would be greatly
> > appreciated. I'm looking for materials that will show me how to provide
> > proper maintenace and optimize performance of an SQL server.
> >
> > Thanks in advance,
> > --
> > pbrill1
>

Advice needed

I’d appreciate an advice about the following data mining scenario. A bank institution has customers and keeps tracks of the customer demographics data (e.g. income, age, etc). A customer could have purchased one or more products (checking, savings, CD, and other accounts). My task is to predict the likelihood for a customer to purchase a new product. For example, if the custom has checking and savings accounts, what’s the likelihood for the customer to purchase a CD account so we can offer this product do the customer.

My questions:

1. What data mining task (segmentation, classification, etc) does this requirement represent?

2. What algorithm(s) is best suited for this task?

3. For a given customer, will I be able to predict the buy probability for all products or do I need to query the mining model to predict one product at the time?

Shrek banks are great candidates to leverage product recommendations. You are on the right path. A few quick pointers to your questions:

1. It makes sense to first utilize MS Clustering see learn more about what customers have in common. This will help determine what key drivers (e.g. geography, demographic, socioeconomic, tenure, etc.) are more important that others in recommending products.

2. MS Decision Trees and Neural Nets will do a great job of identifying what "x" product to offer to "y" customer. Each alogirithm has its own benefits.

3. If you properly set up the model you can set the output to deliver a scored list of product recommendations for each customer record instead of one product at a time. Remember the point of a product recommendation model is to provide multiple products that a customer may be likely to sign up for (think Amazon.com they offer multiple recommendations not just one at a time). For example, the models we have developed for banks will predict a cross-sell product (Mortgage), an up-sell product (gold membership), an add-on (checking plus) to each scored customer record.

Hope this helps.

Jeff

Please visit Apollo Data Technologies http://apollodatatech.com/|||

Jeff,

Thank you so much. I will definately try out your recommendations. If you don't mind, I would appreciate some additional pointers. Our system captures the customer products (checking, savings, CD, etc), account balances, profitability factors, and lmited demographics data (address and age). A big concern I have is that that input parameters may not be sufficient for a good cross-selling prediction.

1. Based on your experience with the banking industry, what could be the most important factors that may influence the customer (individual or business) to buy a new product?

2. Are there any companies that could provide tha missing important inputs (demographics and financial) if our system and the bank cannot supply them? Any idea about how much such services can cost?

I appreciate your help.

|||Shrek - good questions...

1. Your first question on key drivers is the holy grail. The key drivers really vary based on many factors - is it a national or regional bank, full-service, strong online presence, how many branches, etc? What I can say confidently without knowing the above answers and looking at the data is that income level and geo/demo are often key drivers. The best way to accurately determine this is to build a clustering model utilizing the MS Clustering algorithm.

2. Yes - there are many companies that are more than happy to sell you geo/demographic and socioeconomic data (at the individual level, household, business) you can overlay ontop of your customer records to improve the richness of the data set. The price ranges based on how many records and overlay attributes you determine you need. We have built a service around identifying which 3rd party data will be the best predictor for identifying product recommendations (these vendors will provide a free sample for evaluation). As a heads up this service pays for itself b/c often times the 3rd party data sold is of poor quality; however, our process identifies the best data sets available and the minimal amount of data needed to purchase so you are not over-buying data that is of little use.

Feel free to contact me if you need some additional pointers info@.apollodatatech.com