Stacy White wrote:
> I'm working on a system to report on large amounts of transactional data
> (I'm expecting at least a few hundred million transactions in a couple
> years).
>
> On other projects I've been getting good performance by aggregating the data
> at multiple levels (totals by product, by supplier, by retailer, by
> division, etc), and grabbing data from the smallest table/materialized-view
> that can satisfy the query.
>
> But on this project, our requirements include reporting on the total number
> of unique customers for a user-selectable subset of the data. So, for
> instance, the end-users should be able to run a report that gives them
> (among other things) the total number of unique customers in sales region A
> that ordered product B over a user-definable date range.
>
> If I were only showing total transactional information, I might build a
> table or materialized view keyed on product #/region #/day, which included
> fields for total_revenue/total_order_count, etc. We've been able to build
> our reports in real time on other systems by summarizing data like this.
>
> But I can't add a 'unique_customers' field, and 'SELECT
> SUM(unique_customers)' since that would count some customers twice.
>
> I'm having trouble coming up with a cunning solution to the problem, and I'm
> starting to think that a relational database might not be the right
> approach.
>
> Any suggestions?
What you are describing is not relational ... it is OLAP. If you
have 9i use the built-in OLAP capabilities.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Sun Feb 22 2004 - 15:10:36 CST
