Information of Sequence clustering

I’m a college student currently studying 10th semester in the Universidad de los Andes, Colombia and I’m working on a data mining project. I need to use the cluster sequence approach; therefore I need to completely understand how it works. In order to understand it, I need to know which inputs it uses, how the algorithm works and which type of outputs does the approach throw. Do you have any idea where I can find this type of information? and examples?

Any help would be appreciated.

Thank-you for your time.

The typical use of the sequence clustering algoithm is to cluster clickstream data i.e. group the navigation patterns of website users.

You can find the algorithm details in this Microsoft Research paper that the implementation is based on: ftp://ftp.research.microsoft.com/pub/tr/tr-2000-18.pdf.

The book "Data Mining with SQL Server 2005" also has a chapter on the sequence clustering algorithm and examples of its usage.

|||

Thanks for the information, it was useful.

I have another two questions about this algorithm.

1) in witch problems the algorithm could be used, the book that you recommended me, use the example of the clicks of a web page, but in witch others environments could be used the sequence clustering?

2) In the sequences of the model it’s possible that the states could be a set of elements, for example, if I want to use the algorithm in a medical set of data, one state of the sequence could be two or more diagnostics.

|||

The algorithm can be used whereever there is a discrete sequence of events. In general, accuracy of the algorithm degrades if there are too many possible sequence states - over 70 or so.

The algorithm considers any descrete state as distinct and will not provide a "this or that" state. However it could end up with a result such as

A->C 40%

A->B 40%

A->D 20%

C->D 100%

B->D 100%

This result says that there are three paths to D, A->D, A->C->D, and A->B->D with varying probabilities.

If you want to explicitly equate two diagnositics, e.g. B and C are functionally identical, you should prepare your data that way and combine the states. You can do so using a calculated column in the data source view. The "Using SQL Server Data Mining" chapter in the Data Mining with SQL Server 2005 book provides an example of this.

Information of Sequence clustering

I’m a college student currently studying 10th semester in the Universidad de los Andes, Colombia and I’m working on a data mining project. I need to use the cluster sequence approach; therefore I need to completely understand how it works. In order to understand it, I need to know which inputs it uses, how the algorithm works and which type of outputs does the approach throw. Do you have any idea where I can find this type of information? and examples?

Any help would be appreciated.

Thank-you for your time.

The typical use of the sequence clustering algoithm is to cluster clickstream data i.e. group the navigation patterns of website users.

You can find the algorithm details in this Microsoft Research paper that the implementation is based on: ftp://ftp.research.microsoft.com/pub/tr/tr-2000-18.pdf.

The book "Data Mining with SQL Server 2005" also has a chapter on the sequence clustering algorithm and examples of its usage.

|||

Thanks for the information, it was useful.

I have another two questions about this algorithm.

1) in witch problems the algorithm could be used, the book that you recommended me, use the example of the clicks of a web page, but in witch others environments could be used the sequence clustering?

2) In the sequences of the model it’s possible that the states could be a set of elements, for example, if I want to use the algorithm in a medical set of data, one state of the sequence could be two or more diagnostics.

|||

The algorithm can be used whereever there is a discrete sequence of events. In general, accuracy of the algorithm degrades if there are too many possible sequence states - over 70 or so.

The algorithm considers any descrete state as distinct and will not provide a "this or that" state. However it could end up with a result such as

A->C 40%

A->B 40%

A->D 20%

C->D 100%

B->D 100%

This result says that there are three paths to D, A->D, A->C->D, and A->B->D with varying probabilities.

If you want to explicitly equate two diagnositics, e.g. B and C are functionally identical, you should prepare your data that way and combine the states. You can do so using a calculated column in the data source view. The "Using SQL Server Data Mining" chapter in the Data Mining with SQL Server 2005 book provides an example of this.

sql

Information of Sequence clustering

I’m a college student currently studying 10th semester in the Universidad de los Andes, Colombia and I’m working on a data mining project. I need to use the cluster sequence approach; therefore I need to completely understand how it works. In order to understand it, I need to know which inputs it uses, how the algorithm works and which type of outputs does the approach throw. Do you have any idea where I can find this type of information? and examples?

Any help would be appreciated.

Thank-you for your time.

The typical use of the sequence clustering algoithm is to cluster clickstream data i.e. group the navigation patterns of website users.

You can find the algorithm details in this Microsoft Research paper that the implementation is based on: ftp://ftp.research.microsoft.com/pub/tr/tr-2000-18.pdf.

The book "Data Mining with SQL Server 2005" also has a chapter on the sequence clustering algorithm and examples of its usage.

|||

Thanks for the information, it was useful.

I have another two questions about this algorithm.

1) in witch problems the algorithm could be used, the book that you recommended me, use the example of the clicks of a web page, but in witch others environments could be used the sequence clustering?

2) In the sequences of the model it’s possible that the states could be a set of elements, for example, if I want to use the algorithm in a medical set of data, one state of the sequence could be two or more diagnostics.

|||

The algorithm can be used whereever there is a discrete sequence of events. In general, accuracy of the algorithm degrades if there are too many possible sequence states - over 70 or so.

The algorithm considers any descrete state as distinct and will not provide a "this or that" state. However it could end up with a result such as

A->C 40%

A->B 40%

A->D 20%

C->D 100%

B->D 100%

This result says that there are three paths to D, A->D, A->C->D, and A->B->D with varying probabilities.

If you want to explicitly equate two diagnositics, e.g. B and C are functionally identical, you should prepare your data that way and combine the states. You can do so using a calculated column in the data source view. The "Using SQL Server Data Mining" chapter in the Data Mining with SQL Server 2005 book provides an example of this.

Infinite Loop while cube processing in SSAS 2005 SP2

Hi,

We build the cube, quite large one processing 4 million records of data daily. It was working fine in SSAS 2005 Service Pack 1. But recently Service Pack 2 for SSAS came out, in the releases notes it said SP2 solves some of the issues we had on the drillthrough. So, after applying SP2 on the production server, running on 32 bit Windows 2003 SP2, the ETL process is stuck. It stuck while processing the cube, on large dimension.

I was wondering whether microsoft released hotfix for this issue or anyone else had this problem.

I note again, that whole thing was working fine under SSAS 2005 SP1 environment, but i believe in SP2 there is some flaw that leads to infinite loop while processing the cube.

Regards,

Bakhodir Makhamadov

Hello,

Short after releasing sp2 they came out again with an update of SP2 !?

Recreate all your partitions and redesign de storage designs, maybe this will help too !?sql

Infinite Loop while cube processing in SSAS 2005 SP2

Hi,

We build the cube, quite large one processing 4 million records of data daily. It was working fine in SSAS 2005 Service Pack 1. But recently Service Pack 2 for SSAS came out, in the releases notes it said SP2 solves some of the issues we had on the drillthrough. So, after applying SP2 on the production server, running on 32 bit Windows 2003 SP2, the ETL process is stuck. It stuck while processing the cube, on large dimension.

I was wondering whether microsoft released hotfix for this issue or anyone else had this problem.

I note again, that whole thing was working fine under SSAS 2005 SP1 environment, but i believe in SP2 there is some flaw that leads to infinite loop while processing the cube.

Regards,

Bakhodir Makhamadov

Hello,

Short after releasing sp2 they came out again with an update of SP2 !? Recreate all your partitions and redesign de storage designs, maybe this will help too !?

Inexplicable Invalid Syntax error

I created a DB in Access.. then I converted it to work with SQL Server. I have several tables and queries that are working just fine with it.

However, the other day I made a few changes to one of the tables ( Order table), and it seems no matter what kind of query I do with it, it always returns this error:

Invalid syntax near the keyword 'Order'

The query is IDENTICAL to about 10 others like it.. but no matter what kind of query I do to this Order table.. it always returns the same error. I've stepped through it, looked at the query builder, and there's nothing wrong with the format. Here is what the code looks like..

strSQL = "SELECT OrderId FROM Order WHERE SessionId = '"
+ "testfudgeddata" + "' ";
cm.CommandText = strSQL;
dr = cm.ExecuteReader();

while( dr.Read() )
{
orderId = dr.GetInt32(0);
}

It always fails when ExecuteReader() gets called.. with the same error. The syntax is exactly the same as many other queries.. and I even typed in 'testfudgeddata' into the db directly. I am thinking this problem might be related to some proxy-type information that I need to clean out due to the change of the name of this table since it was created in Access.. anyone ever see anything like this?

BrentORDER is a reserved word in SQL Server. change the table name (!)|||Try placing the table name in square brackets.|||AAAAK!

It seems every time I ask for help, I figure out the problem right after. I ran Query Analyzer and was a little confused why my Order table text was blue.. and the other tables were black.

Order is KEYWORD!! DUH!!

Anyways, it's a little funny I guess.. hope you can all get a good laugh from my misery.. heh|||Thanks for the reply guys.. looks like all 3 of us posted at exactly the same time. ;)sql

Indicator non aggregate

Hi everybody,

I'm working on SQL Server 2005 & Reporting services to create a Projet model to deploy it on the server.

To explain me, i 'll recreate simply my problem on the the database "AdventureWorksDW".

I woudl like to see information on my product dimension.

In a matrix tab, i put "Product name" in row value and "customer name" on column.

I would naturally know the "Sales Amount" for my product by customers.

But, as i had not create aggregate, i can't put my numerique value on the cells indicators. It just allowed me to put the SUM(SALES_AMOUNT) by exemple, but it's not what i want.

How can i put my numerique value in this case ?

Regards,

Erwan

Toulouse, France

Have a column called count on the fact table.

Have the value 1 in the column

Sum the column

|||

The probleme is that i dont want the sum of my value, in just want the pure value.

It is possible ?

Erwan

|||

The whole point of a matrix is that it aggregates the data.

Otherwise it would be a table.

In analysis services, you can drillthrough to the underlying data in the fact table. You would need to post on that forum to find out how.

|||

it's ok, i had just execute my request to blind total.

Thanks adolf

sql

Indexing a selection query

Hey,

I've started a new topic for this, as the old was is a different problem, lol.

This script is working now, however it says the unitIndex is 287 for all units.

What I wanted the unitIndex to be is, 1 for the unit with highest tsr, and (if 30 units) 30 for the unit with lowest tsr.

Why is it making the UnitIndex 287, and how can I fix it? lol

(I have marked the line where the unitIndex is

PROCEDURE units_display (@.startIndex INT, @.endIndex INT)
declare @.maxrts bigint;
select @.maxrts = (select Distinct max((((wins)*((wins*100)/((games+1)*4))))) from unit);
if @.maxrts = 0
set @.maxrts = 1;
declare @.maxValue bigint;
set @.maxValue=2500
select *
from (select *, (select unit from UnitTypes where UnitTypes.unitid = unit.UnitType) as unitTypeName, ((@.maxValue*((wins)*((wins*100)/((games+1)*4))))/@.maxrts ) as trs2 from Unit) as Unit1
inner join
(select Unit3.trs, count(*) as UnitIndex --< here is UnitIndex
from
(select ((@.maxValue*((wins)*((wins*100)/((games+1)*4))))/@.maxrts ) as trs from Unit) as unit3
inner join
(select ((@.maxValue*((wins)*((wins*100)/((games+1)*4))))/@.maxrts ) as trs3 from Unit) as Unit2 on Unit3.trs <= Unit2.trs3
group by Unit3.trs) as UnitIndexes
on Unit1.trs2 = UnitIndexes.trs
where UnitIndexes.UnitIndex between @.StartIndex and @.EndIndex
order by Unit1.trs2 DESC

ThanksIt seems like you are making this more complicated than it should be. What are the table definitions and what are you trying to do? Is tsr a calculated field?

To order the records and determine a unit index for them, I would create a subquery in the select clause that counts all records that have a greater tsr.|||Hey,

yea, if I can simplify this, that's be awesome.

I am only looking at one table, I want to select all the fields from it, but relevant fields are:

UNIT
unitid int pk
Name vc(50)
games int
wins int
unittype int (fk referencing UnitTypes, unitid)

UNITTYPES
unitid int PK
unit varchar(20)

tsr is a column I am working out on the fly, it isn't stored in the database. perhaps a better method would be to create a view first with tsr in it, then run the reorder etc after, however I am unsure how views work, and as of yet, I havn't had time to look into them to determine if they are what I should use.

Any help simplifying this would be awesome, lol.

I have solved the issue I had with the script below, however if you can should me however, it is just a repair on the code as it is there, lol

I'll post the repair in here if you'd like to work from that. Basically though, once I determine the column tsr, I then want to determine the 'ranks' of each unit. So basically, units with the same tsr score, get the worse rank, ie, the third and fourth highest scores both get rank 4 (rank = unitindex form the script below). once that is done, I then was to assign a position variable to each row.

I am doing the same thing with a similar query, which a few people are discussing with me in another post. From what they have said, I will be looking at creating a table, with the columns i desire, plus am incrementing 'position' column.

I will then use something like

insert into @.tempTable (the select statement which returns a reordered table with the tsr value).

Thanks
-Ashleigh

-Ashleigh

Indexes with Server Cursor

I am working with an application that uses a server cursor on SQL 2000. Whenever the program tries to search for a record, the search takes forever. I was hoping I could just search for the record within the entire dataset without doing a sql select command. It almost seems like the server cursor isn't using the indexes I have setup. Does anyone know if the server cursor will take advantage of the indexes?You may need to recompile the stored proc if you created the index after the fact. Or if the index has been around for a while you may need to run a DBCC REINDEX. If the index is out of date or bad SQL Server will automatically quit using it.

Make sure you are not overriding SQL with a HINT.

If this doesn't help can you post the CREATE TABLE and a sample of the SELECT. Maybe you don't have the WHERE clause covered by the INDEX and SQL thinks it can do better without it.|||As referred make sure to run DBCC checks to fine tune the performance. Also runn PROFILER during this operation and see where it lacks, submit the trace to INDEX TUNING WIZARD for any recommendation on indexes that may help.|||You may want to reconsider using a cursor -- there may be better ways to obtain the data you are seeking.|||Unfortuntely I don't know of any other way, the main obstacle with this program is that I need to have all the data there for the user to see. Currently I do have the searches returning subsets with a sql statement, but I would very much like to be able to use a locate command instead.

I tried reindexing but that didn't work either. Thanks for all your replies, any other suggestions? I'm stumped :)

Originally posted by MattR
You may want to reconsider using a cursor -- there may be better ways to obtain the data you are seeking.|||Try using QUERY Execution plan in QA using the query.
BTW what is the SQL version & SP level and how do you connect from application to database?

Indexes stop working

We have about 10 sites using SQL Server 2000 SP1 or 3 or 3a, across two servers. The publisher/distributor performs transactional replication on three databases to the 'secondary' server.

Nearly all of the sites, at one time or another have experienced the indices on one of the tables simply stop working. Its always the same indices on a table that can have between 70,000 and a 300, 000 rows.

Before I start posting schema or index details anyone come across this before?Only once. Actually, now that I think about it, not even once. Do you have Auto-Stats set on for these databases? Or does UPDATE STATISTICS tablename resolve the problem?|||Yeah, that's happened to me a couple of times. Well two or three times, anyway. Or once. Maybe once. No, not even once. What the heck do you mean "stopped working", anyway? Are they not showing up in the mornings, or are your indexes just sitting in their cubes staring at the screensaver?|||auto update statistics was on so switched this off and ran update statistics again manually to no avail. The table in question is as follows:

CREATE TABLE [dbo].[rawdata] (
[evtime] [datetime] NOT NULL ,
[type] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[srcid] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[rawinfo] [varchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL ,
[seqno_yr] [int] NOT NULL ,
[seqno] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE INDEX [IX_rawdata] ON [dbo].[rawdata]([type], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_1] ON [dbo].[rawdata]([srcid], [evtime]) ON [PRIMARY]
GO
CREATE INDEX [IX_rawdata_2] ON [dbo].[rawdata]([evtime]) ON [PRIMARY]
GO

The problem, when it starts happening, means the following query stops returning data. Using different criteria by matching exactly rather than on a range in evtime works proving the data is there.

select * from rawdata where evtime between '2004-11-01 00:00:00.000' and '2004-11-02 00:00:00.000'

I am not sure whether transactional replication has anything to do with the cause. Because this data is only needed occasionally, we can get away with dropping the subscription, dropping the publication then dropping and recreating the indexes. We then publish and create a subscription to the data. It lasts for about 3 or 4 days then it goes again.

There are no maintenance plans set for this database which is possibly the problem. Running DBCC SHOWCONTIG never completes - it just sits there.

Help!|||You will want to leave Auto-stats on.

How many rows in the table? And how long has DBCC Showcontig gone for, before you give up on it? Have you run DBCC CHECKTABLE on this table?

Indexes not working after converting to 2000

I detached a SQL 7 DB, copied it to a server running SQL
2000 and attached it.
The indexes don't appear to be working. I've reindexed
the tables and executed a simple query that should
display the data in date order, but it's displayed in a
random order.
The same query on the original DB in SQL 7 shows the data
in date order.
Anyone know what's going on?
There's only one index. It's a nonclustered index on two
fields. Name+date.
Thanks,
DonThis is a multi-part message in MIME format.
--=_NextPart_000_02C4_01C3B29B.F1DDDD30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Two things:
1. Order is not guaranteed, unless you include an ORDER BY in your
SELECT.
2. You should update statistics on all user tables (preferably WITH
FULLSCAN) when upgrading a database from 7.0 to 2000.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Don" <ddachner@.hotmail.com> wrote in message
news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
I detached a SQL 7 DB, copied it to a server running SQL
2000 and attached it.
The indexes don't appear to be working. I've reindexed
the tables and executed a simple query that should
display the data in date order, but it's displayed in a
random order.
The same query on the original DB in SQL 7 shows the data
in date order.
Anyone know what's going on?
There's only one index. It's a nonclustered index on two
fields. Name+date.
Thanks,
Don
--=_NextPart_000_02C4_01C3B29B.F1DDDD30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Two things:
1. Order is not =guaranteed, unless you include an ORDER BY in your SELECT.
2. You should update =statistics on all user tables (preferably WITH FULLSCAN) when upgrading a database =from 7.0 to 2000.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Don" wrote =in message news:047601c3b2c4$e3=e7cfa0$a301280a@.phx.gbl...I detached a SQL 7 DB, copied it to a server running SQL 2000 and =attached it.The indexes don't appear to be working. I've reindexed the =tables and executed a simple query that should display the data in date order, =but it's displayed in a random order. The same query on the original DB =in SQL 7 shows the data in date order.Anyone know what's going =on?There's only one index. It's a nonclustered index on two fields. Name+date.Thanks,Don

---

--=_NextPart_000_02C4_01C3B29B.F1DDDD30--|||Hi Don
The only queries that 'should' display data in a particular order are
queries that contain ORDER BY. If you were formerly getting data back in a
preferred order, you were just lucky. It was never documented or supported
that any query, using any index, would return data in any speficic order
without using ORDER BY.
OTOH, having a good index can help speed up the order by, and possibly make
a sort unnecessary, but the ORDER BY must be there in order to guarantee the
order of the rows in the result set.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <ddachner@.hotmail.com> wrote in message
news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
> I detached a SQL 7 DB, copied it to a server running SQL
> 2000 and attached it.
> The indexes don't appear to be working. I've reindexed
> the tables and executed a simple query that should
> display the data in date order, but it's displayed in a
> random order.
> The same query on the original DB in SQL 7 shows the data
> in date order.
> Anyone know what's going on?
> There's only one index. It's a nonclustered index on two
> fields. Name+date.
> Thanks,
> Don
>|||Thanks for you help. I didn't realize I was just being
lucky :-)
Don
>--Original Message--
>Hi Don
>The only queries that 'should' display data in a
particular order are
>queries that contain ORDER BY. If you were formerly
getting data back in a
>preferred order, you were just lucky. It was never
documented or supported
>that any query, using any index, would return data in
any speficic order
>without using ORDER BY.
>OTOH, having a good index can help speed up the order
by, and possibly make
>a sort unnecessary, but the ORDER BY must be there in
order to guarantee the
>order of the rows in the result set.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <ddachner@.hotmail.com> wrote in message
>news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
>> I detached a SQL 7 DB, copied it to a server running
SQL
>> 2000 and attached it.
>> The indexes don't appear to be working. I've reindexed
>> the tables and executed a simple query that should
>> display the data in date order, but it's displayed in a
>> random order.
>> The same query on the original DB in SQL 7 shows the
data
>> in date order.
>> Anyone know what's going on?
>> There's only one index. It's a nonclustered index on
two
>> fields. Name+date.
>> Thanks,
>> Don
>
>.
>|||I didn't know that about the ORDER BY.
Thanks for your help. I'll do the Scan update statistic.
Don
>--Original Message--
>Two things:
>1. Order is not guaranteed, unless you include an
ORDER BY in your
>SELECT.
>2. You should update statistics on all user tables
(preferably WITH
>FULLSCAN) when upgrading a database from 7.0 to 2000.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Don" <ddachner@.hotmail.com> wrote in message
>news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
>I detached a SQL 7 DB, copied it to a server running SQL
>2000 and attached it.
>The indexes don't appear to be working. I've reindexed
>the tables and executed a simple query that should
>display the data in date order, but it's displayed in a
>random order.
>The same query on the original DB in SQL 7 shows the data
>in date order.
>Anyone know what's going on?
>There's only one index. It's a nonclustered index on two
>fields. Name+date.
>Thanks,
>Don
>

Indexed Views

What have been experiences out there with indexed views? How are they
working for you? What method do you use to maintain them? What is
recommended for scenarios where there is a large # of updates to the
base table ... are there any issues with rollback tables filling up?
Thanks for your help!
JimLarry
http://www.sql-server-performance.com/indexed_views.asp
"Larry" <nospam@.nospam.com> wrote in message
news:OhVvIX#7DHA.2560@.TK2MSFTNGP09.phx.gbl...
> What have been experiences out there with indexed views? How are they
> working for you? What method do you use to maintain them? What is
> recommended for scenarios where there is a large # of updates to the
> base table ... are there any issues with rollback tables filling up?
> Thanks for your help!
> Jim
>