Re: Is OLAP essentially for Large Enterprises

Dear Punnet,

I usually use materialized views to solve this kind of requirements (summary, aggregation). A materialized view (or mv.) is a mix of a table and a view in the sense that a table stores data, a view stores definition (SQL), and a mv. sotres both. When the base table is modified, the mv. needs "refreshing". With mv., you can achieve the performance goal at the expense of extra disk space. IMHO, it is almost always justifiable for doing this, because disks have been becoming much cheaper these days. Also the performance of queries with multi-level sub-queries become more "manageable" when intermittent mvs. are used.

Some level of flexbility can be retained. A simple example: a sales transaction database has product, customer, and time dimensions, under product there are independent sub-dimensions brands and categories. Instead of creating two mvs. for brand and category respectively, you can create a mv at the product level, this view can be used for querying product, brand, and category aggregations. However, you cannot use it for product + time, customer, customer + time, or time only aggregations, you need more mvs. OLAP is meant to provide aggregations of any intersections of dimensions in this respect.

That said, there are some situations when mvs are considered: 1.you need an OLAP tool, but cannot afford yet (mvs can satifiy all querying needs except that you cannot drill around easily) 2.you have an OLAP tool, but it has limitations (semi-additive and non-additive roll-ups, per-dimension roll-ups, distinct count, number of rows of source data)
3.you have an OLTP database and want to do aggregations in it

Please refer to Oracle 8i Data Warehousing Guide for more: http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76994/toc.htm

HTH,
Dino

On Mon, 29 Oct 2001 00:41:17 +0530, "Puneet Agarwal" <NOSPAM__puneetagarwal_at_india.com> wrote:

>Do you mean that Materialized vews which are based on aggregate tables are
>also optimized. Please elaborate further or refer me a resource where I can
>find more information. I could not find much regarding optimization of
>aggregate based view in SQL Server books online.
>
>Thanks
>"Umachandar Jayachandran" <umachandar_at_yahoo.com> wrote in message
>news:Ot#wSA#XBHA.2184_at_tkmsftngp05...
>> No. Not from within the RDBMS itself. Whereas if you use indexed or
>> materialized views, this is possible.
>>
>> --
>> Umachandar Jayachandran
>> SQL Resources at http://www.umachandar.com/resources.htm
>> ( Please reply only to newsgroup. )
>>
>>
>
Received on Mon Oct 29 2001 - 00:29:47 CST