data mining

hi what is a good book to learn data mining and data warehousing and olap
concepts?
thanks
ICHORichor wrote:
> hi what is a good book to learn data mining and data warehousing and olap
> concepts?
These are three different subjects, so I doubt there are many good
books that cover all three. For data mining, I would recommend any of
the following:
"Data Mining: Concepts and Techniques"
by Jiawei Han, Micheline Kamber
ISBN: 1558604898
"Data Mining : Practical Machine Learning Tools and Techniques"
by Ian H. Witten, Eibe Frank
ISBN: 0120884070, 1558605525
"Predictive Data Mining : A Practical Guide"
by Sholom M. Weiss, Nitin Indurkhya
ISBN: 1558604030
I'd suggest the following OLAP title:
"OLAP Solutions: Building Multidimensional Information Systems (Second
Edition)"
by Thomsen
ISBN: 0471400300
-Will Dwinnell
http://will.dwinnell.com

Data Mining

Hi,

I've come along way learning about databases and SQL. I can write basic queries now. Even some with subQueries. What I need to learn, is how to approach data mining. Can someone suggest the best path to follow, to learn how to accomplish data mining from a very large database?
I don't just need to produce reports of acquired data. I need to go in and grab data and look for patterns against known result sets. I hope that makes sense.

Thanks,
MilfredoWell, do you have a basic grounding in statistics?
TSQL lends itself fairly well to exception reporting, but unfortunately it does not have a rich function set for correlational studies. I've written my own code for simple linear regression, but I haven't attempted true multi-linear analysis.
SQL Server 2005 has the CLR toolkit that allows you to build custom aggregate functions, so it may prove more useful for data analysis and I wouldn't be surprised to see 3rd party developers marketing more advanced statistical functions. In the meantime, you will need to spin off subsets of data for further analysis is a stronger statistical tool, such as Excel or SAS.|||Thanks Blindman,

I'm looking into some third party software. There seems to be several things on the market that will alllow me to drill down into my data and do some analysis. For single user's the price isn't bad.

Milfredo|||If you are just looking at pivoting and drill-downs, a simple Excel pivot table linked to a SQL Server view might get you started.|||Thanks. I actually over simplified my project. I have a fairly extensive database, that I need to work with. I need to select certain data set and see what factors that I can add that will make it more predictive. I'm test driving a software called Purple Mineset at present.

Milfredo

Data integrity

Hi,
Does any one know how to apply/enhance the database's integrity.
During study, i learn the theory of how to make sure the data retrieve and
update is correct. But i dont know how to apply in database development.
For example: I have a table. 2 persons are accessing the table in the
following sequence:
Person A, access the table.
Person B, access the table.
Person A update new value (such as rental_fee)
Person B also update the rental_fee value.
Person A exit
Person B also exit
At this moment, record updated by person A has been overwrited by person B.
How to prevent it?
Thanks in advance.
Best regards,
GLOne method is to use optimistic concurrency. This technique checks to see
if the original value was modified by another user:

> Person A, access the table.
SELECT @.OldRentalFee = RentalFee
FROM RentalProperties
WHERE RentalPropertID = 1

> Person B, access the table.
SELECT @.OldRentalFee = RentalFee
FROM RentalProperties
WHERE RentalPropertID = 1

> Person A update new value (such as rental_fee)
UPDATE RentalProperties
SET RentalFee = @.NewRentalFee
WHERE RentalPropertID = 1 AND
RentalFee = @.OldRentalFee
IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Data was updated by another user', 16, 1)
END
--the above succeedes

> Person B also update the rental_fee value.
UPDATE RentalProperties
SET RentalFee = @.NewRentalFee
WHERE RentalPropertID = 1 AND
RentalFee = @.OldRentalFee
IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Data was updated by another user', 16, 1)
END
--the above raises an error
A common practice to use a rowversion data type (formally timestamp) for the
concurrency check. This simplifies concurrency checking because the value
is automatically changed by SQL Server whenever any row value changes and
you don't need special handling of NULLs.
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:E6002470-2152-41AC-AE2F-D33A1676CCFA@.microsoft.com...
> Hi,
> Does any one know how to apply/enhance the database's integrity.
> During study, i learn the theory of how to make sure the data retrieve and
> update is correct. But i dont know how to apply in database development.
> For example: I have a table. 2 persons are accessing the table in the
> following sequence:
> Person A, access the table.
> Person B, access the table.
> Person A update new value (such as rental_fee)
> Person B also update the rental_fee value.
> Person A exit
> Person B also exit
> At this moment, record updated by person A has been overwrited by person
> B.
> How to prevent it?
> Thanks in advance.
> Best regards,
> GL|||Thanks for ur help.
I have another question, do i need to configure the sqlserver 2000 in order
to do that?
In addition, creating a column (auto generate number) act as one of the
primary key is a good practice?
"Dan Guzman" wrote:

> One method is to use optimistic concurrency. This technique checks to see
> if the original value was modified by another user:
>
> SELECT @.OldRentalFee = RentalFee
> FROM RentalProperties
> WHERE RentalPropertID = 1
>
> SELECT @.OldRentalFee = RentalFee
> FROM RentalProperties
> WHERE RentalPropertID = 1
>
> UPDATE RentalProperties
> SET RentalFee = @.NewRentalFee
> WHERE RentalPropertID = 1 AND
> RentalFee = @.OldRentalFee
> IF @.@.ROWCOUNT = 0
> BEGIN
> RAISERROR('Data was updated by another user', 16, 1)
> END
> --the above succeedes
>
> UPDATE RentalProperties
> SET RentalFee = @.NewRentalFee
> WHERE RentalPropertID = 1 AND
> RentalFee = @.OldRentalFee
> IF @.@.ROWCOUNT = 0
> BEGIN
> RAISERROR('Data was updated by another user', 16, 1)
> END
> --the above raises an error
> A common practice to use a rowversion data type (formally timestamp) for t
he
> concurrency check. This simplifies concurrency checking because the value
> is automatically changed by SQL Server whenever any row value changes and
> you don't need special handling of NULLs.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Daniel" <Daniel@.discussions.microsoft.com> wrote in message
> news:E6002470-2152-41AC-AE2F-D33A1676CCFA@.microsoft.com...
>
>|||Dan's code will work straight out of the box. There is no extra SQL
Server config needed. Timestamp is a native SQL datatype, so you don't
need to reconfigure anything to use the timestamp for the row versioning
either.
With regards to using an identity column (auto generated sequential
integer) as a primary key for a table, there are various debates about
that. Many people would say that's often the best way to define primary
keys in real life, while others (the most vocal being one Joe Celko, who
has spent some time on the ANSI committee and published some material on
the subject) would disagree and advise you to use a natural key as your
primary key for the table (like a SSN or VIN or ActionType/ActionDate
combination for example).
I won't get into the whole debate (it's a rather raging topic) except to
say both sides of the argument can be justified. If you do create a
clustered index on an identity column, however, you can substantially
reduce pages splits (and therefore I/O) on inserts because all new data
will be located at the end of the index and no reordering within the
index will be necessary. This will create a "hotspot" in effect at the
end of the index. But there are many other factors to consider, not the
least of which is personal preference, when declaring one or more
columns to be the primary key of a table.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Daniel wrote:
>Thanks for ur help.
>I have another question, do i need to configure the sqlserver 2000 in order
>to do that?
>In addition, creating a column (auto generate number) act as one of the
>primary key is a good practice?
>
>"Dan Guzman" wrote:
>
>|||> I have another question, do i need to configure the sqlserver 2000 in
> order
> to do that?
There's no special SQL Server configuration needed since optimistic
concurrency is handled by the application.

> In addition, creating a column (auto generate number) act as one of the
> primary key is a good practice?
This subject is often discussed in this forum so you can fund many pros and
cons with a google search. It's a common practice to use a surrogate value,
such as an IDENTITY column, as the primary key. Whether or not this
practice is good depends on the specific situation. There are those who
believe religiously that one should always use natural keys and others who
always employ surrogate keys as a knee-jerk reaction. Personally, I
evaluate each situation individually.
Hope this helps.
Dan Guzman
SQL Server MVP|||ok. i got it..thanks for ur respond.
"Dan Guzman" wrote:

> There's no special SQL Server configuration needed since optimistic
> concurrency is handled by the application.
>
> This subject is often discussed in this forum so you can fund many pros an
d
> cons with a google search. It's a common practice to use a surrogate valu
e,
> such as an IDENTITY column, as the primary key. Whether or not this
> practice is good depends on the specific situation. There are those who
> believe religiously that one should always use natural keys and others who
> always employ surrogate keys as a knee-jerk reaction. Personally, I
> evaluate each situation individually.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
>

Data Flow Task question

Hi there. I'm trying to learn SSIS, please, help me. I have 2 questions:

1)
There are 2 databases on 2 different servers. I need to get data from Table1(database1) and put it to Table2(database2). But I have to insert rows, which ID is not exists in Table2. How Can I do necessary filter?

2)
In the OLE DB DataSource Component I have used SQL Command(it's simplified):

declare @.TmpTable TABLE (WorkCode int not null);

INSERT INTO @.TmpTable (WorkCode)
select WorkCode
from Table1

SELECT WorkCode
FROM @.TmpTable

SSIS Package works without any exception. But there is no any inserted record in destination table. If I try similar query without temporary table - it works good. Why?

1 - Create OLE DB source to 1st (source) database.
- Add a lookup transformation to select key from 2nd database on 2nd server. In that lookup join the key coming from the 1st database to the key in the 2nd
- Hook the error output (red arrow) from the lookup to an OLE DB destination which points to the 2nd database. This will insert records not found in the 2nd database.

2 - Try setting the RetainSameConnection property of the connection manager to true and see what happens.|||The first task works! Thanks! But the second is not. Any other ideas?
|||

Aliaksander Hmyrak wrote:

The first task works! Thanks! But the second is not. Any other ideas?

Good deal.

As for number 2, why are you using temp tables? 2 things - you can just write the query that inserts into the temp table as the source for the data flow. OR you could use yet another, initial, data flow to populate a SQL server table with the results you need in the 2nd data flow (the one you've already got written). After the two data flows, you could write an Execute SQL task in the control flow to truncate that "temporary" table.|||

At the start of your SQL statement add

Code Snippet

SET NOCOUNT ON

|||Thanks a lot !! Both methods work!