Time Series processing pattern (not data mining)?

26 Nov 2005 12:33 PM

Martin

Hi,

Is there a pattern for processing data presented as a time series?

(Typically, the type of pattern called for when processing financial
transactions).

My current design is SQL based and looks something like this

1) Have different event types stored in separate tables (with associated
time)
2) Have a master event table where all event types are represented in one
table (with foreign keys to each event detail table)
3) Have various triggers on the master event table to process diferent event
types as they "occur".

Solution should be able to handle back dating of events, so it is necessary
to be able to roll back the processing of events, adopt the back dated event
and then process forward again.

Can anyone comment / point me design papers on this class of problem? Can't
see anything in Microsoft's Patterns and Practices at the moment.

Thanks in advance.

Martin

27 Nov 2005 4:07 PM

ML

Please post DDL and some sample data.

In the meantime... What is the contect in which these events occur (i.e. are
they related to a real-life entity)? Can events occur at the same time, and
specific circumstances require only one (or more) of them be rolled back? Do
events occur in patterns - are there any relationships between the events?

ML

27 Nov 2005 4:57 PM

Martin

Well I'm really looking at patterns rather than specific instances, but
let's work with this ddl

create table trade(tradeId varchar(50),type varchar(10),volume
decimal(18,5),[date] datetime,code varchar(5),clientId varchar(10))
create table valuation(priceId varchar(50),[date] datetime,price
decimal(18,5),code varchar(5))
create table fee([date] datetime)
create table timeline(masterEventId int,eventType varchar(10),eventId
varchar(50))
create table portfolio (clientId varchar(10),code varchar(5),volume
decimal(18,5),startdate datetime,enddate datetime)

code would identify the fund or share being traded/valued
trade.type is buy/sell
fee records the point at which fees are due

The order of different events that have the same datetime would be
determined by the event type, so let's say valuation of a fund would occur
before trades at the same datetime.

I've assumed the granularity of valuation and trade is a day, so I am not
looking at time within a particular day (I know that's not particularly
realistic).

I would expect triggers to exist on the timeline table which then modify
other tables (eg portfolio)

Thanks
Martin

Show quote

"ML" <M*@discussions.microsoft.com> wrote in message
news:C8615173-59BF-4824-BFF5-CC7B4EF998BB@microsoft.com...
> Please post DDL and some sample data.
>
> In the meantime... What is the contect in which these events occur (i.e.
> are
> they related to a real-life entity)? Can events occur at the same time,
> and
> specific circumstances require only one (or more) of them be rolled back?
> Do
> events occur in patterns - are there any relationships between the events?
>
>
> ML

28 Nov 2005 9:54 AM

ML

I think the fee table is missing a column.

Maybe you should consider using a calendar table for that - this way you can
adjust time granularity as appropriate.

If the order of events is fixed by a rule, add a sorting column to the
events table - which I also think is missing from the DDL you posted.

It's also really difficult (virtually impossible) to uderstand the
relationships between the entities in this setup, since no keys are present.

I personally think you've rushed into table creation before making a logical
model with some proof of concept. So...

....identify entities,
identify relationships,
identify business rules.

Don't start designing tables before you fully understand the issue.

As far as I see it, you need something like this:

1) Entities: events (a list of supported events with a natural order),
timeline (actual event instances), clients;

2) Relationships:
events occur in the context of a client;
only one type of event should occur for a client at any given time - if more
events do occur at the same time, the event order is in effect;
one of the attributes of an event is the fee;
a transaction is completed after all events have occured (<-- this is a
guess).

Of course more questions remain. Which you can find the answers to.

ML

28 Nov 2005 12:40 PM

Martin

I just made up the tables in answer to the group.

I emphasise I am interested in the design pattern, not the specific example.

Show quote

"ML" <M*@discussions.microsoft.com> wrote in message
news:78A542E4-720D-4156-8931-20D2C51251C3@microsoft.com...
>I think the fee table is missing a column.
>
> Maybe you should consider using a calendar table for that - this way you
> can
> adjust time granularity as appropriate.
>
> If the order of events is fixed by a rule, add a sorting column to the
> events table - which I also think is missing from the DDL you posted.
>
> It's also really difficult (virtually impossible) to uderstand the
> relationships between the entities in this setup, since no keys are
> present.
>
> I personally think you've rushed into table creation before making a
> logical
> model with some proof of concept. So...
>
> ...identify entities,
> identify relationships,
> identify business rules.
>
> Don't start designing tables before you fully understand the issue.
>
> As far as I see it, you need something like this:
>
> 1) Entities: events (a list of supported events with a natural order),
> timeline (actual event instances), clients;
>
> 2) Relationships:
> events occur in the context of a client;
> only one type of event should occur for a client at any given time - if
> more
> events do occur at the same time, the event order is in effect;
> one of the attributes of an event is the fee;
> a transaction is completed after all events have occured (<-- this is a
> guess).
>
> Of course more questions remain. Which you can find the answers to.
>
>
> ML