Another T-SQL puzzle

Hi all,
Sorry if this has been asked before, I've had a very long day and I think my
brain has switched off...
I have a table that records transactions (changes) in another table, and I'm
interested in getting an audit trail (i.e. who did what) for specific state
changes.
So, given the following... (there are more columns, such as user id etc,
but these are irrelevant here)
CREATE TABLE [StateChanges] (
[m_id] [int] IDENTITY (1, 1) NOT NULL,
[ChangeDateTime] [datetime] NOT NULL CONSTRAINT
[DF_StateChanges_JournalDateTime] DEFAULT (GETDATE()),
[SensorID] [int] NOT NULL,
[State1] [varchar] (20) NOT NULL,
[State2] [varchar] (20) NOT NULL
) ON [PRIMARY]
GO
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 01:46:19.543', '1670', 'Pending', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 02:21:33.153', '1670', 'New', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 01:46:20.077', '1671', 'Pending', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 02:17:48.030', '1671', 'OK', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 03:32:20.450', '1672', 'Pending', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 03:32:20.483', '1672', 'New', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:37:35.390', '1673', 'OK', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:37:35.403', '1673', 'New', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:37:42.293', '1673', 'New', 'Requested')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:38:13.340', '1674', 'Pending', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:38:13.357', '1674', 'Manual', 'Neutral')
INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
VALUES ('2006-02-11 04:38:16.857', '1674', 'Manual', 'Requested')
If I'm looking for changes in State1 in each SensorID, then I'd want to get
the rows where State1 has changed from Pending to New, or from Pending to OK
etc. There are occasions where another state will change and State1 will
stay the same, in which case I only want the first occurence.
So for the above data, I'd be interested in rows 2, 4, 6, 8 and 11.
If anyone can help me here it would be greatly appreciated!!!
Cheers,
AlexThe key to this type of problem is a perfect sequence number. In your
example, for clarity I can cheat and use the identity value since you have
sorted the inserts by SensorIf and ChangeDateTime.
You just shift the data to check the previous value against the current
value. In SQL Server 2005, this is pretty easy to do
with orderedSet as
( --this set is used twice in the following query
select M_Id, ChangeDateTime, SensorID, State1, State2,
--this sets up an ordering number per group
row_number() over (partition by sensorId order by changeDateTime) as
ordering
from stateChanges
)
select *
from orderedSet as s
join orderedSet as s2
on s.sensorId = s2.sensorId
and s.ordering = s2.ordering + 1 --use this column instead of m_id
because it is safer
where s2.state1 <> s.state1
For 2000, you have to do the row number yourself, so it is more complex,
especially if your data set is far more complex.
select M_Id, ChangeDateTime, SensorID, State1, State2,
--this sets up an ordering number per group
(select count(*)
from stateChanges as s2
where s2.sensorId = stateChanges.sensorId
and s2.changeDateTime <= stateChanges.changeDateTime) as ordering
into #orderedSet
from stateChanges
select *
from #orderedSet as s
join #orderedSet as s2
on s.sensorId = s2.sensorId
and s.ordering = s2.ordering + 1 --use this column instead of m_id
because it is safer
where s2.state1 <> s.state1
I use a temp table because it is just so much cleaner to deal with with the
two references since temp views are not allowed. Hope this help :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Alex" <nospam@.hotmail.com> wrote in message
news:43ed6f20$0$5013$db0fefd9@.news.zen.co.uk...
> Hi all,
> Sorry if this has been asked before, I've had a very long day and I think
> my brain has switched off...
> I have a table that records transactions (changes) in another table, and
> I'm interested in getting an audit trail (i.e. who did what) for specific
> state changes.
> So, given the following... (there are more columns, such as user id etc,
> but these are irrelevant here)
> CREATE TABLE [StateChanges] (
> [m_id] [int] IDENTITY (1, 1) NOT NULL,
> [ChangeDateTime] [datetime] NOT NULL CONSTRAINT
> [DF_StateChanges_JournalDateTime] DEFAULT (GETDATE()),
> [SensorID] [int] NOT NULL,
> [State1] [varchar] (20) NOT NULL,
> [State2] [varchar] (20) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 01:46:19.543', '1670', 'Pending', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 02:21:33.153', '1670', 'New', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 01:46:20.077', '1671', 'Pending', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 02:17:48.030', '1671', 'OK', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 03:32:20.450', '1672', 'Pending', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 03:32:20.483', '1672', 'New', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:37:35.390', '1673', 'OK', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:37:35.403', '1673', 'New', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:37:42.293', '1673', 'New', 'Requested')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:38:13.340', '1674', 'Pending', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:38:13.357', '1674', 'Manual', 'Neutral')
> INSERT StateChanges (ChangeDateTime, SensorID, State1, State2)
> VALUES ('2006-02-11 04:38:16.857', '1674', 'Manual', 'Requested')
>
> If I'm looking for changes in State1 in each SensorID, then I'd want to
> get the rows where State1 has changed from Pending to New, or from Pending
> to OK etc. There are occasions where another state will change and State1
> will stay the same, in which case I only want the first occurence.
> So for the above data, I'd be interested in rows 2, 4, 6, 8 and 11.
> If anyone can help me here it would be greatly appreciated!!!
> Cheers,
> Alex
>

Analysis Services Processing

Hello all,

I do a daily full processing of a cube. If I want to capture the daily loads of what new records were inserted into a table what would be my best strategy? Do I need to change my processing to incremental first?

Thanks, I will appreciate your insights.

Rok

You would need to do this in your relational source database. I am not aware of any "tagging" that SSAS does to indicate when something was loaded. And If you want to setup incremental processing, you need to be able to send SSAS the set of records that you want added to the cube. So you would need to figure out what is new before sending it to SSAS.

Analysis Services 2005 Design Challenge

Hi to all, I’m designing a SSAS 2005 cube based on an Oracle data source

We have a classical star schema with several millions of records into the fact table.

Now everything goes fine since dimensions are quite small (the greatest one has 100000 records), but users are asking me the possibility of view also more detailed data such order_number, client fiscal code, etc…and this data it has been stored into the fact table as a fact degenerate dimension.

Now I don’t want to load into the cube this degenerate dimension because it contains as I said too many records.

I was thinking to drill-through functionality (achieved setting the storage mode of the degenerate dimension to ROLAP) but after several tests it seems very very slow (it launches queries that incredibly big grouping all the dimensions and all the fields of the fact table or other times launches several heavy queries catching all the distinct values of order_number, client fiscal code, etc…) and some times the client itself (OWC) hangs and I have to kill it.

Note that these tests are done on the development environment where the fact table contains more or less only 70000 records!

I tried also to query directly this degenerate dimension (without drill-through) but the results are the same.

Any suggestions?

Thanks.

Alberto

Try using MOLAP dimensions. They should scale pretty well in AS2005.

You should be able to handle several milion members with MOLAP dimensions.

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