I have a particularly lengthy query that I'm trying to speed up.

I haven't got a clue how/if I can set this up as a cube, can someone advise?


I have a table as follows:
PrimaryKey CustomerID Date Attribute1
Attribute2 Attribute3
1 1 1/1/2003 1
1 1
2 1 2/2/2003 1
2 3
3 2 1/1/2003 1
3 4
3 2 3/3/2003 1
3 4


For example, I will query this table by supplying a date range and will
return the sum of Attribute1. I will only select the most recent record for
a CustomerID to go to the aggregation.

How can I build in this date range selection taking the most recent records
in my cube?

Cheers

Not sure I completely understand your question but maybe you could use a SQL
view to create your cube off of.

--
Ray Higdon MCSE, MCDBA, CCNA
---
"Anthony Shorrock" <ashorrock (AT) SPAM (DOT) ME.NOT.hotmail.com> wrote

Thanks for taking the time to explain.

I have transaxtional data which I would like to query.

For example,

key id date attribute1 attribute2
1 1 1/1/2001 1 1
2 1 1/2/2001 2 1
3 1 1/3/2001 3 1
4 2 1/1/2001 2 2
5 2 1/2/2001 3 2
6 2 1/3/2001 3 2

where
key = primary key
id = customer number
date = transaction date
attribute1 = sales band or whatever.
attribute2 = customer score or whatever

I have millions of these rows.

What I need to do is query this data by given a data range for the
transactions and also their sales band.

I must only take the most recent customer record. So, for the six row
example above I should only ever pull back two records.

I don't know how I can design a cube on this "take most recent record"
basis.

Cheers


"Ray Higdon" <rayhigdon (AT) higdonconsulting (DOT) com> wrote

"Anthony Shorrock" <ashorrock (AT) SPAM (DOT) ME.NOT.hotmail.com> wrote

Just wondered why you were using a cube for this? Cubes aren't normally
used for querying individual transactions. Why not just use SQL?

True, but I have millions of rows and sql isn't going to give me the answers
I need - realtime. I have a web based report thing hanging off this. I am
viewing a count of transactions rather than the transactions themselves.

I can get near instantaneous results by querying a cube - ideal! However, I
don't know how to work around the "most recent transaction" issue.

Cheers

"Nigel Pendse" <nigelp.nospam (AT) olapreport (DOT) com> wrote

Counting dynamically-selected transactions isn't what OLAP is about. OLAP is
(mainly) used for rapidly retrieving aggregated data, some of which was
pre-aggregated. With the right indexing, relational processing probably is
the right answer for you.

Nigel Pendse
http://www.olapreport.com

"Anthony Shorrock" <ashorrock (AT) SPAM (DOT) ME.NOT.hotmail.com> wrote

I agree with Nigel, when I'm working with a customer and they start
getting a return on their cubes, they want to port all reports to a cube
adn that just isn't the answer. For specific transactional reports like
the one you are looking for, using SQL to either query the OLTP store or
a secondary storage place where you are copying the data to would be
your best bet.


Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Assuming that you can set up a relational staging area to facilitate the
query process, can this approach work:

- Copy new transactions to the staging area, adding a column for count,
which is +1 for new transactions.

- When copying a new transaction, first check for the previous newest
transaction of that customer, and add a fake transaction with -1 for
count, and -attribute for each attribute. This will cause reversal of
data associated with the previous transaction during cube incremental
update. The incremental cube update can be based on a time window, so
that the latest transactions are added to the cube.

- Overnight, or over the weekend, the staging area can be purged of old
transactions, and the cube reprocessed.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!