Advice needed - MSDE vs. Access

Hi, I currently have a application that is being used throughout the
country at different customers. Currently it is a VB6 application accessing
DBF/CDX standalone tables via ODBC. We are developing the 'next
generation' of the program in VB.NET and will do data access via OLEDB.
Here is the issue: Stand alone Visual Foxpro DBF tables do not return
'primary key' information, so we lose a lot of potential functionality by
not being able to set relations in a Dataset. Therefore, we are looking
for a new database to host the application. The application DB requirements
are basic INSERT, UPDATE, DELETE. No data replication, or anything fancy.
No "DB security" required. In reality, the application is coded so that it
will actually run on a SQL server or Oracle enterprise DB, too. But
customers that choose that option have an IT dept with appropriate
expertise, so I'm not worried about them.
Here's the problem... most of our customers are in small shops and are
barely computer literate. We need something REALLY simple. With the DBF
files it couldn't be simpler... install the program and then run it. No
real maintenance (except backup of course) is needed. I'm looking at
either MS Access or MSDE as a replacement database. Given that my
customers are the types that use the CD drawer as a cup holder, I'm
concerned that MSDE might be too much for them, and that Access might be
easier for them. Right now this is just my initial thought, as I'm not
familiar with MSDE. Before I get too far down the road, I wanted to tap
some expertise for advise.
So, the application will be coded to the 'lowest common denominator' but
will able to run on SQL server and ORACLE also. The question is, given the
small shops I need to cater to, what should that 'lowest common denominator'
be? Access or MSDE?
Any advice graceously accepted... Thanks.
John
hi John,
JohnR wrote:
>...
> So, the application will be coded to the 'lowest common denominator'
> but will able to run on SQL server and ORACLE also. The question is,
> given the small shops I need to cater to, what should that 'lowest
> common denominator' be? Access or MSDE?
>
difficult question, as the 2 engines can not be compared... ok they are both
database engines, but very different.... from my point of view I'd go with
MSDE as it can be easily scaled to full blown SQL Server editions with no
harm at all... and as you already know, JET database engine is in
maintenance and no additional features will be provided for it..
on the other side, MSDE, and soon SQLExpress, could require some more
maintenace (you can include in you application, for quite all administrative
tasks)... but they are another level of db engines, comparable with Oracle,
where the JET engine is not...
for SQL Server connections I'd go for the SQLClient name space versus the
OLEDB conterpart asi it provides better and targeted support for the SQL
Server worls, where the OLEDB provider is quite generic... but this is
another story..
more... SQLExpress will support easy setup and deployment for your database,
as long as XCopy support...
http://msdn.microsoft.com/library/de...seoverview.asp
provides additional info..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

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

AdventureWorks DB question

Hi, I have been looking at the Adventure works DB diagram and I am confused about the relationship that employees, customers and individuals has with contact i.e. a 1-1, 1-many or many-many relationship. Also why is it broken down like that.

Thanks,

Nadim.

Hi Nadim,

AdventureWorksDB is designed to show a complex data schema in a nomalized structure. AdventureWorks is probably a bit overcomplex if you're just learning about normalization, but once you understand the concepts, it becomes more clear.

Note: If you are new to normalization, you should search for a few topics about it to get the basics. You might also find the Northwind or Pubs sample databases to be a bit more understandable. You can download either from http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en. They are SQL 2000 samples, but they will still work in 2005.

On to the description...

If you look at the fields in each of the tables, you'll see that the Contact table contains the base information about a person, e.g. first name, last name, phone, etc. The Contact table is linked to both the Employee and Individual tables. Both Employees and Individuals are types of people that have special properties in addition to the base person information stored in the Contact table. So an Employee has a ManagerID and LoginID, but an Individual doesn't. Because of the relationship between Contact and Employee, there is no need to store the first name, last name, etc. in the Employee table, it is retrieved through a join.

If I wanted to get a list of Employee names, with Titles and Hire Dates, I'd need to write a query that uses both the Contact and Employee table, as follows:

SELECT C.FirstName, C.LastName, E.Title, E.HireDate
FROM Person.Contact C INNER JOIN HumanResources.Employee E
ON C.ContactID = E.ContactID

The relationship with Individual is similar, only it extends through Individual onto Customer. Each Customer has an Individual associated with it, and that Individual, is a person who has Contact information. I'll be honest, I don't really know why they split Customer and Individual, it could have something to do with the way they wanted to use the data being stored.

Hopefully that brings a little light to the subject.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

Advance TSQL question

Hi,
I would like to find customers (same id) with different addresses:
Id Address
-- --
1 123 ABC
2 456 DEF
1 789 GHI
2 456 DEF
3 000 XYZ
As above example: my result would be Id=1.
Thanks in advanced,
Culamselect id, count(id) cnt from (select distinct id, address from tablename)
group by id having count(id) > 1
"culam" wrote:

> Hi,
> I would like to find customers (same id) with different addresses:
> Id Address
> -- --
> 1 123 ABC
> 2 456 DEF
> 1 789 GHI
> 2 456 DEF
> 3 000 XYZ
> As above example: my result would be Id=1.
> Thanks in advanced,
> Culam|||On Fri, 3 Feb 2006 03:34:15 -0800, culam wrote:

>Hi,
>I would like to find customers (same id) with different addresses:
>Id Address
>-- --
>1 123 ABC
>2 456 DEF
>1 789 GHI
>2 456 DEF
>3 000 XYZ
>As above example: my result would be Id=1.
>Thanks in advanced,
>Culam
Hi Culam,
Try:
SELECT Id
FROM YourTable
GROUP BY Id
HAVING MIN(Address) <> MAX(Address)
Hugo Kornelis, SQL Server MVP