[thelist] RE: SQL Case/Decode
Luther, Ron
ron.luther at hp.com
Tue Jul 27 16:25:23 CDT 2004
Joshua Olson noted:
>>It's not uncommon to represent data two ways--one for each of the
>>different applications of the data. One application of the data is
>>reporting, and the other is transactions.
Hi Joshua!
Sorry. Actually I was thinking more about different OLAP representations
of the data. Lets say that in 'year 1' a request comes in for a report
showing 'orders' by geography (region/country/sales district/whatever)
and is aggressively and optimally designed to do _exactly_ that. In
'year 2' a request comes in for a report showing 'orders' by product
(product line / product division / subsidiary / whatever) and is
aggressively and optimally designed to do _exactly_ that. You join the
team in 'year 3' facing a request for a report showing 'orders' by
product by geography and scratch your head wondering why your schema
has two 'orders' tables - neither one of which you can use for your
project.
It's easy to look back now and say "Oh, design 'x' would have been
better from the get-go." It's tougher to 'forward proof' a design.
>>OLAP (Online Analytical Processing) and OLTP (Online Transaction
>>Processing)
Ever hear the one about the guy who threw an index on the OLTP in
order to speed up his reporting? ;-)
>>I've seen some massive systems built for publishing data from the OLTP
>>system to the OLAP system for reporting. Usually, I see this in batch
>>processes.
Yup - got some of those. Got some 'real-time' OLTP outbounds as well.
!Joyful
>>Then, reports can be run easily against the new form of the data.
Well, there are days when I'll argue about how 'easily' that occurs.
;-)
>>Is there any way that you can at least pull the data
>>into something you CAN control for reporting purposes?
Nope. Not on this job. I can do some 'proof of concept' things on
the side in 'non-standard' technologies ... but not production work.
>>But, you can ALSO do this, without the view:
>>SELECT derived.*
>>FROM
>>(
>> SELECT blah
>> FROM foo
>> WHERE bar = 123
>>) derived
Wooot! Most excellent Joshua! I will definitely play with that.
[Hmmm ... couldn't find a syntax like that quickly in my 'Oracle (The
Complete Reference)' book ... I try a few ideas and talk to a few
Oracle wizards.] But thanks for the heads up!
>>Morale of the story is that you don't have to have access to views
>>to apply work done in a view to a "regular" query.
Neat - didn't know that.
Thanks!
RonL.
More information about the thelist
mailing list