Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: April 29, 2003 - 11:01 pm UTC

Last updated: August 05, 2022 - 11:11 am UTC

Version: 9.2.0.1

Viewed 10K+ times! This question is

You Asked

We are evaluating OLAP technology for implementing some analytics for our product. We are reviewing technologies from Oracle, Microsoft and other vendors.

Correct my if I am wrong, OLAP technology with Oracle looks like an early stage feature. I got this assumption when comparing the features, ease of use etc with other vendors. Even Microsoft also well in advance in that area. I couldn’t see much customer references for Oracle OLAP.

Please comment on the following issue as you have more expertise with the different technologies and industry?

What are your suggestions on choosing OLAP technology?
What are your suggestions on Oracle OLAP? Should we consider it? If yes, why?


and Tom said...

Umm, "early stage"???

How did you get that impression? We've been doing it longer then MS has had a relational database?


</code> http://www.oracle.com/ip/index.html?olap_home.html http://otn.oracle.com/products/bi/content.html <code>


Let me put it this way - 99.9999% of your OLAP needs are satisfied directly in Oracle using SQL with analytics, advanced group by and the DW features. Occassionally you need multi-dimensional cubes and for that we actaully put it in the database (not in a separate external server). Meaning -- you don't dump your data from a RDBMS into an OLAP database (with different security, management, tools, languages, etc etc etc). You keep it -- in the database. The same people that manage your RDBMS today -- manage you OLAP tomorrow -- without having to be trained on an entirely separate, different product.


I don't know how many times I've gone somewhere where the "analysts" dumped the data from Oracle into an MS OLAP tool to do 'advanced' queries (spending hours on the dump and load and dump and load and dump and load over and over and over). I sit down and do it in a single query -- "oh, i didn't know SQL could do that...." A waste of time and computer resources.




Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Oracle Express

A reader, April 30, 2003 - 9:28 am UTC

Wasnt Oracle Express the pre-9i OLAP engine?

Tom Kyte
April 30, 2003 - 9:53 am UTC

yes. and Oracle Express is the post 9i OLAP engine -- it is just that for the last 2 releases of Oracle -- express has been an embedded technology.

That and the analytics functions (8i) have been in there forever.

Add data mining (ala darwin) and you've got all of the analysis tools you could want.

Thanks

A reader, April 30, 2003 - 2:39 pm UTC

Thanks for your comments. I got that impression because I couldn’t see many customer instillations for Oracle Express/OLAP(9i). I guess that I was thinking wrong.

>>your OLAP needs are satisfied directly in Oracle using SQL with analytics, advanced >>group by and the DW features.

Could you please elaborate on the available Oracle features that are helpful for OLAP?

a) What do you mean by “SQL with analytics”, is it analytical functions that oracle provided?
b) What is ‘advance group by’ feature?
c) Could you please list the DW features that are useful in OLAP?

Sorry for bugging you.


Tom Kyte
April 30, 2003 - 7:13 pm UTC

guess it is a matter of perspective. I see people doing tiny things with MS olap stuff. I see tons more doing big things with our stuff. Guess it is all in where you are looking.

I can think of NO data warehousing feature that is not potentially useful in OLAP -- think about OLAP -- "Online Analytical" -- fast, quick responses -- DW -- tools to do big things fast.


some quick rollup and cubes:

scott@ORA920> select decode( grouping(deptno), 1, 'summed over dept'),
2 decode( grouping(ename), 1, 'summed over ename'),
3 deptno, ename, sum(sal)
4 from emp
5 group by rollup(deptno,ename)
6 /

DECODE(GROUPING( DECODE(GROUPING(E DEPTNO ENAME SUM(SAL)
---------------- ----------------- ---------- ---------- ----------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
summed over ename 10 8750
20 FORD 3000
20 ADAMS 1100
20 JONES 2975
20 SCOTT 3000
20 SMITH 800
summed over ename 20 10875
30 WARD 1250
30 ALLEN 1600
30 BLAKE 2850
30 JAMES 950
30 MARTIN 1250
30 TURNER 1500
summed over ename 30 9400
summed over dept summed over ename 29025

18 rows selected.

scott@ORA920> select decode( grouping(deptno), 1, 'summed over dept'),
2 decode( grouping(ename), 1, 'summed over ename'),
3 deptno, ename, sum(sal)
4 from emp
5 group by cube(deptno,ename)
6 /

DECODE(GROUPING( DECODE(GROUPING(E DEPTNO ENAME SUM(SAL)
---------------- ----------------- ---------- ---------- ----------
summed over dept summed over ename 29025
summed over dept FORD 3000
summed over dept KING 5000
summed over dept WARD 1250
summed over dept ADAMS 1100
summed over dept ALLEN 1600
summed over dept BLAKE 2850
summed over dept CLARK 2450
summed over dept JAMES 950
summed over dept JONES 2975
summed over dept SCOTT 3000
summed over dept SMITH 800
summed over dept MARTIN 1250
summed over dept MILLER 1300
summed over dept TURNER 1500
summed over ename 10 8750
10 KING 5000
10 CLARK 2450
10 MILLER 1300
summed over ename 20 10875
20 FORD 3000
20 ADAMS 1100
20 JONES 2975
20 SCOTT 3000
20 SMITH 800
summed over ename 30 9400
30 WARD 1250
30 ALLEN 1600
30 BLAKE 2850
30 JAMES 950
30 MARTIN 1250
30 TURNER 1500

32 rows selected.

scott@ORA920> select decode( grouping(deptno), 1, 'summed over dept'),
2 decode( grouping(ename), 1, 'summed over ename'),
3 deptno, ename, sum(sal)
4 from emp
5 group by grouping sets((deptno),(ename))
6 /

DECODE(GROUPING( DECODE(GROUPING(E DEPTNO ENAME SUM(SAL)
---------------- ----------------- ---------- ---------- ----------
summed over ename 10 8750
summed over ename 20 10875
summed over ename 30 9400
summed over dept ADAMS 1100
summed over dept ALLEN 1600
summed over dept BLAKE 2850
summed over dept CLARK 2450
summed over dept FORD 3000
summed over dept JAMES 950
summed over dept JONES 2975
summed over dept KING 5000
summed over dept MARTIN 1250
summed over dept MILLER 1300
summed over dept SCOTT 3000
summed over dept SMITH 800
summed over dept TURNER 1500
summed over dept WARD 1250

17 rows selected.


Analytics -- search for that word here. I use them LOTS. They are the coolest thing to happen to sql since the keyword select. see also

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/toc.htm 

and in particular:
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/analysis.htm#1020 <code>



Thanks again!

A reader, April 30, 2003 - 7:17 pm UTC


Perspective

Jon, April 30, 2003 - 10:35 pm UTC

You raise an interesting problem though... perspective.

What ever you may say about MS, they certainly do their marketing well. Many people have the impression that to do OLAP well, you need all these extra bits and pieces that MS supply. Oracle may have the features, the simplicity and the power to do all this stuff, but *really* needs to work on its Marketing...

A reader, May 14, 2003 - 8:45 pm UTC

I have Oracle 9.2.0.1 running on Windows. I have installed 9.2.0.3 database patch. I couldn't see analytic workspace manager tool? How to get this tool?

I will greatly appreciate if you can guide me with some links or steps.


Tom Kyte
May 14, 2003 - 11:54 pm UTC

that would be something to ask of support -sounds like an OEM thing but i've not used the "analytic workspace manager tool"

Definition - embedded technology

Doug, September 08, 2003 - 2:45 am UTC

Tom - can you elaborate why during the last two releases, Express is an "embedded" technology? It looks like a bunch of libraries, an app if you will that just gets run along side oracle and can talk to an Oracle database if it wants, or some other source. What makes it fit the definition of "embedded". Is there another type of component we would NOT consider embedded? Are OFA and OSA embedded as well?

Tom Kyte
September 08, 2003 - 11:08 am UTC

runs INSIDE oracle

do you start an express server?
do you move data out of Oracle?
is is a standalone product?

it is embedded firmly in the database. it cannot run without the database for it runs IN the database.

in the past, it was

o a standalone product
o in a separate server
o that tended to move data out of oracle

now, it is in Oracle just like a b*tree index is in oracle.

Express 6.3.4 vs embedded

Doug, September 08, 2003 - 11:36 pm UTC

Actually - the version we installed was 6.3.4, one of the non-embedded versions, hence my confusion.

I am hearing there is very little app support for the new embedded express.. yet. You think that's true? or no? There are high hopes for the new EPB?

OLAP & OFA

Stuart Buchan, October 16, 2003 - 5:56 am UTC

Great Info Tom,

we use OFA 6.3 with an express server database and we are considering the future upgrade possibilities.

I read the documents on OTN about OLAP within the database as you discussed here, and then the 11i 6.4 version of OFA - which continues to mention throughout the seperate express server / database etc.

If we installed OLAP from 9i etc, would we not be able to directly use the OFA GUI anymore to access the information - this is an important feature for the user community at the moment who are familiar with the tool. Would we have to develop a new GUI interface using Java/Web based tools ?

Cheers,

Stuart

Tom Kyte
October 16, 2003 - 10:36 am UTC

sorry -- i am just not familar with the apps side of the house (ofa being the Oracle Financial Analyzer). you'd be best off talking to your sales contact who can bring in a technical apps SC to plot your best course.

Upgrade of Express into 6.3.4. - and ... still as a separate ENV..?

Andre, May 16, 2005 - 8:03 am UTC

Hi Tom,

I have read very carefully this column on Oracle Express and OLAP issues.

My client Apps Group has been working on Oracle 7.3.3.6 and 8.1.7 and only NOW (m id May 2005) - they want to migrate into 9iR2.

Their operations span 29 Oracle WinNT and XP databases.

They've been using Express 6.3.0.1 on RDBMS 7.3.3.6.

They finally agreed to upgrade from 7.3.3.6 into 9iR2
and - Express from 6.3.0.1 into 6.3.4

- - HOWEVER...
They insist to run the new environment as TWO SEPARATE components - RDBMS and Express.
The former DBA who left believed that Oracle 9iR2 integration with OLAP is NOT in a working order.

I am engaged as a consultant for a short (2 months) term, until they get another DBA.

WHAT DOCUMENTATION or REFERENCES could be used /pointed to in order to convince this App Group to use Oracle 9iR2 with fully embedded OLAP features (Express sub-environment) or whatever...?

Is there a MAJOR improvement within Oracle 10.g that they should be using instead...?

FINAL POINT:
This large insurance company has chosen Oracle STANDARD and NOT Enterprise Edition.

Is this the possible limitation as far as OLAP is concerned?

Many thanks
Andre

Tom Kyte
May 16, 2005 - 8:11 am UTC

not sure I'm following the environment here. But in order to use the OLAP features of 9i/10g (10g would be the way to go for sure), they would need to license them (not standard edition)

Oracle Express - to upgrade or not to upgrade...?

Andre, May 17, 2005 - 4:12 am UTC

Thanks Tom,

Clarification:

(1) This company wishes to move from Oracle RDBMS 7.3.3.6
into -> 9.2.0.5 (10g is to big a step now)

(2) They have been using Oracle Express 6.3.0.1
and they want to upgrade it to 6.3.4

(3) They require Oracle Express being separate from RDBMS

+ + +

You said that Oracle Express is integrated into RDBMS as of 9i.

WHAT ARE YOUR RECOMMENDATION AS TO INTEGRATION
OF ORACLE EXPRESS WITHIN RDBMS

as opposed to:

maintaining 2 separate environments =
(a) 9.2.0.5 RDBMS
+
(b) 6.3.4 Express

Please advise - Thank you.

Regards,
Andrew



Tom Kyte
May 17, 2005 - 8:51 am UTC

1) that is sort of funny. They are going to go from 1995 technology to 2003 technology but technology released in 2004 is "too big a step". huh.


Well, the expression functionality (cubes and olap) are "in the database" yes.


I would not be considering 9.2 -- that is and will be my advice.

olap in built or not?

A reader, July 21, 2005 - 12:12 pm UTC

Tom,

How do I know whether my databae (9i and 10g) have olap engine? My qustion is pretty general and I hope my it is clear.

I did select * from v$version and I did not see OLAP coming up on the screen (like PL/SQL Release 9.2.0.6.0 - Production)

thanks,

Tom Kyte
July 21, 2005 - 4:48 pm UTC

[tkyte@desktop tkyte]$ sqlplus /

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Jul 21 16:41:38 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production



or select * from v$option;

Is OLAP CUBE necessary ?

Reader, January 22, 2006 - 7:33 am UTC

Option 1: One can always create summary tables after the DW loads and point the tools or reports to these tables.
OR
Option 2 :Create OLAP cubes ,store them in an oracle DB (using OLAP option) and allow tools and users to use them.

Which is a better approach ?
Why is one preferred over the other ?

We currently use option 1 but are forced to use by a section of the team to use option 2 .

We would feel obliged if you could help us in understanding this difference in approach?


Tom Kyte
January 22, 2006 - 9:39 am UTC

Nothing is ever necessary.

Nothing is ever always better than something else in all cases (else we would not have both).

No one single approach is preferred over every other approach in all cases (else we would not have more than one)



OLAP

reader, January 22, 2006 - 4:35 pm UTC

Can you point me to some white paper which will tell me the diff in the above mentioned 2 approaches where
one may like to create summary tables using analytical functions OR create OLAP cubes .

Thanks

Tom Kyte
January 22, 2006 - 5:03 pm UTC

I would check out the application developers guide for OLAP
</code> http://docs.oracle.com/docs/cd/B19306_01/olap.102/b14349/toc.htm <code>

You can achieve many similar things for thousands of problems using either technique. It is a matter of realizing that you have a toolbox, full of tools, that may make certain jobs easier. Knowledge of the tools is really the only way I see to know when to use them.

OLAP Choices

Jack Mulhollan, February 21, 2007 - 7:08 pm UTC

It appears that there are two possible approaches to OLAP in Oracle.

The first is to create a datawarehouse of regular relational tables, normally a star schema consisting of a long fact table and several short dimension or lookup tables. OLAP-type questions may then be answered using straight SQL. The queries may employ group by, extensions to group by (rollup, cube, grouping sets), analytic functions, the model clause, and so on. A number of built in database features may be used to make these queries fast: table compression (less i/o), partitioning (pruning), bitmap indexes (star transformation), materialized views and dimensions (query rewrite), etc. An interface could be built (albeit with substantial effort) to generate these queries, providing the ability to do point-and-click analysis (drill-down/roll-up, crosstab/pivot, etc.).

The second approach is not as clear to me, but involves something called analytic workspaces. It appears that relational tables are transformed into additional physical objects (cubes?) that reside within the database in analytic workspaces. These objects are really just relational tables that employ some type of multidimensional indexing. Some tools exist to build analytic workspaces: Analytic Workspace Manager, Warehouse Builder. Other tools exist to query them: Oracle BI Discoverer plus OLAP, Oracle BI Spreadsheet Add-In. One or another of these tools presumably provides the point-and-click capability described above, as well as wizards to facilitate reporting. It is not clear if it is possible to build analytic workspaces without using these tools. It does appear that a SQL interface exists (through a supplied PL/SQL package) that would allow one to query these objects with straight SQL.

Is that an accurate picture of the OLAP choices in Oracle? Is there any major piece I am missing? Would you care to elaborate on anything mentioned above? Obviously, the two approaches above are not necessarily mutually exclusive. However, due to my fuzzy understanding of the second approach, I am not catching exactly when and why it would be desirable or necessary. Can not all OLAP-type questions be answered efficiently using the first approach? Thanks.

Tom Kyte
February 22, 2007 - 8:33 am UTC

You can also use OLAP functions against relational tables, the cubes are built on the fly. The analytic workspaces allow you to create them once, sort of like a materialized view.

OLAP Cubes without AWS

Kenny, March 21, 2013 - 7:17 am UTC

Tom, is there a way we can create OLPA Cubes in 10g/11g R2 without using AWS, using plain simple SQL CREATE Statements? I thought it was posibber since OLPA Cubes are inside the database but couldn't find any documentation on it. All the Docs are pointing towards AWS..

Thanks

can you tell me detail about embedded or enterprise Oracle license?

A reader, July 03, 2013 - 10:07 am UTC

hi,tom:
i have a question about embedded or enterprise Oracle license?
the question is
"Hi Pengsai,



K. Suphot raised the questions about Oracle license again yesterday, he said there’s no conclusion from Huawei on this yet.



1-Will Huawei provide embedded or enterprise Oracle license?

2-AIS highlighted the Oracle Enterprise license they purchased will not cover the usage for BOS, if BOS requires to use enterprise license, please provide a quotation there.



Hi LQ,


Please help to conclude this from Huawei within this week, it will have huge impact to BOS project commercial discussion.


Thanks.



Regards,
"
Tom Kyte
July 16, 2013 - 12:24 pm UTC

I have no idea what you are trying to ask here.

it seems you have cut and pasted a small bit of an internal email from your company?

Model clause

Asim, August 05, 2022 - 3:54 am UTC


Up in this discussion, in year 2003 Tom said

.....Let me put it this way - 99.9999% of your OLAP needs are satisfied directly in Oracle using SQL with analytics, advanced group by and the DW features. Occassionally you need multi-dimensional cubes.....

Ok that was the era of 9i

My question is does that mean the MODEL clause and the MATCH_RECOGNIZE only address to 100 - 99.9999=
0.0001 % OLAP needs?




Chris Saxon
August 05, 2022 - 11:11 am UTC

I believe the best use for MODEL is spreadsheet-like calculations in SQL. i.e. where you have formulas that depend on values from other rows and columns.

This is a niche use case.

Again IMO, MATCH_RECOGNIZE is more valuable. There are several types of problems that you can technically solve with analytics, but are messy/hard to maintain. For example find all the times prices increased three days in a row followed by two days of decreases.

Then there are problems which there aren't general-case solutions using analytics; such as grouping together rows of equal frequency when the data has variable frequencies.

http://db-oriented.com/2022/07/30/pattern-matching-and-event-frequencies/
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.