[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