Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

   

Oracle Business Intelligence, OLAP and BI training and consulting tips . . .

Click here for more
Oracle News Headlines

 


More Information On Oracle 10g OLAP Compressed Composites
March 22, 2005
Mark Rittman

A few weeks ago I posted an article on this site about the new Compressed Composites feature in Oracle 10g OLAP. Basically, compressed composites give you the opportunity, for certain types of sparse cubes, to dramatically reduce the size of the cube and the time it takes to perform aggregation. Although most people aren't really aware of this feature (it wasn't particularly well documented or highlighted when Oracle 10g came out) it's potentially one of the most useful features of the Oracle 10g OLAP Option, and anecdotal evidence suggested that build time could be brought down by up to a factor of eight.

I was particularly interested therefore to see a posting by Scott Powell on the OTN OLAP Forum, that gave a real world example of compressed composites in action, and the performance improvements that he encountered:

"Ok, just got finished testing compressed composites, and in all my life with IRI Express / Oracle Express / Oracle OLAP, I've NEVER seen a better feature added!!!!!

The details of the results are below, but the quick summary is that a full aggregation went from 166 minutes down to ONE MINUTE!!! (ok, one minute 12 seconds, so sue me!) Also, the size of the fully aggregated db went from approx 3.7 Gb to 148 Mb!!!

I'm Giddy!!! Note that I did validate the total numbers to make sure the aggregation actually worked (because it solved so quickly I didn't believe it...)

Scott

Cube info:

5 dimensions
dates dim (14 leaf values, 5 levels)
line of bus dim (162 leaf values, 4 levels)
compliance rating dim (23 leaf values, 3 levels)
instruments dim (171 leaf values, 3 levels)
ownership dim (69,771 leaf values, 6 levels, 2 separate hierarchies)

The composites were defined with the dates dimension being DENSE, all others SPARSE:
<dim_dates <dim_ownership dim_lob dim_instruments dim_compliance>>

There were 190,676 total tuples after the data load and before aggregation.

SKIP-LEVEL aggregation, normal composite:
Agg time: 36 minutes 50 seconds
Tuples after agg: 1,520,377
Approx DB size: 1.3 Gb

FULL aggregation, normal composite:
Agg time: 2 hours, 46 minutes
Tuples after agg: 4,650,470
Approx DB size: 3.65 Gb

FULL aggregation, COMPRESSED composite:
Agg time: 1 minute 12 seconds!!!
Tuples after agg: 4,650,470 (assumed, can't measure like you can a normal composite)
Approx DB size: 148 Mb "

Scott and I have discussed the OLAP Option before so I dropped him a line to get a bit more information. According to Scott,

"The performance increase I saw was absolutely fantastic - seeing 2 hour 45 minute solves go down to just a minute or two. One note is that the number of "singles" for my example my be higher than average. I am analyzing investment securities, and some of the dimensions are very "demographic" in nature (i.e. security type, security rating, etc. doesn't vary for a given low level security, so this "forces" many singles. This would also be true in customer demographic applications, etc.)"

Scott also subsequently posted a couple of other threads on compressed composites, the first of which discussed aggregations using compressed composites (basically, it appears that only full aggregations using SUM are currently supported) and a second posting that summarised the current limitations (and workarounds) with compressed composites, together with any workarounds that might be needed:

"Ok, per my previous post, I'm completely in love with compressed composites. However, it does seem like there are some limitations with using them. Can someone who has played with these more than I please confirm the following (and let me know if there are workarounds, and/or if additional functionality will be added in future)?

1. Compressed composites must be FULLY aggregated, you can not do skip-level, partial aggregations

Workaround for #1 - none needed for me, speed performance was so dramatic that my 166 minute full aggregation went down to 1 minute, 12 seconds and the database size was a fraction of the normal size (150 Mb vs. almost 4 Gb)

2. Compressed composites can only do SUM functions, no others

Workaround for #2 - Keep any non-additve dimensions DENSE and out of the composite. This works great for me, where our major non-additive dimension is TIME (we do ending balance type calcs, not sums across time), which was already our dense dim. I should be able to solve the dense TIME dimension using a separate time-only aggmap, and then aggregate the sparse dims using a SUM only aggmap. Note - if you have many non-additive dims, this isn't going to work well

3. Compressed composites can only be used as the base for a single variable? (at least when I try to dimension a second variable by them I get an error message)

Workaround for #3 - either build a separate compressed composite for each variable, or define a "measure" dimension and have a single variable store all of your different values. In this case, and per workaround #2, you will probably have to define the new measure dimension as being DENSE (because you won't want to aggregate across measures)

4. You can not define a variable as having both a normal composite and a compressed composite?

Workaround for #4 - not really needed, this was just my attempt to make one composite (the compressed one) have all of the SUM only dimensions in it, and another composite (a normal b-tree composite) have all of the non-additive aggregation dims in it. Oracle seems to think this will not work, but they were not 100% sure and I have not tested.

Any other ideas / workarounds / knowledge of what the future will bring for compressed composites would be greatly appreciated - this is a HUGE step forward for Oracle OLAP."

to which Chris Chiappa replied:

"For #2, you can also play games with the new Aggregate "dataflow" (FROM/FROMVAR) capabilities in 10.1 as well - you can layer a completely dynamic variable on top of an aggregated CC and using dynamic aggregate to perform some of the operations you wouldn't be able to in the base variable.

#3 is unlikely to go away any time soon, but since you need separate composites to take full advantage of the new multi-writer stuff in 10.1 anyhow, sharing composites is likely to become much less common going forward."

For anyone looking for more information on compressed composites, check out my original posting and the OLAP DML Reference R1 (10.1) section on compressed composites. Thanks again to Scott for allowing me to reprint his postings.

 


 

   
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational