Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Subramanian.

Asked: November 23, 2002 - 3:50 pm UTC

Last updated: February 04, 2008 - 3:26 pm UTC

Version: 9.2.0.1

Viewed 1000+ times

You Asked

Hi Tom,

We have recently purchased licenses for oracle 9i EE, 9iAS and 9iDS.
And we want to build a datamart, to pull multi-dimensional reports.

For ETL we have Data Warehouse Builder and ETL functions build in the database.

For data mining, do we need the licenses for OLAP option in the database or can we use the discoverer/BI beans?

We expect around 20 to 30 GB of data with around 300 users.
Can't we use star schema/materialized views/discoverer to generate multi-dimensional reports. What extra benefits would we get with OLAP option?

Thanks for taking this question.


and Tom said...

Depends on your definition of data mining!

There is pure, true data mining ala:
</code> http://docs.oracle.com/cd/B10501_01/datamine.920/a95961/toc.htm 
this is technology we integrated over the years from Thinking Machines -- their "Darwin" product.  This is true data mining, see
http://docs.oracle.com/cd/B10501_01/datamine.920/a95961/1concept.htm#918153 

for a quick overview.  It is probably not what you are talking about (as people who want to do "true data mining" are statisticians and are typically very specific as to their needs)....  This Data mining is an extra cost option

Then there is OLAP:
http://docs.oracle.com/cd/B10501_01/olap.920/a95295/toc.htm 

This is the ability to build multi-dimensional cubes and do some pretty cool things that would either be really truly hard or impossible in straight SQL.  See
http://docs.oracle.com/cd/B10501_01/olap.920/a95295/basics2.htm#1005586 
for a quick overview.  

This OLAP capability is an extra cost option.


Then, there is conventional data warehousing:
http://docs.oracle.com/cd/B10501_01/server.920/a96520/toc.htm <code>

this is what you bought. Yes, you have the totally powerful analytic functions, materialized views, star transformations, query rewrites -- a ton of stuff. You can use the tools in jdev such as the bi-beans to visualize or any number of tools such as discoverer (you might find that discoverer is the tool for you -- less code)

20-30 gig is smallish, you should be able to go to town with what you have -- depending on your needs. Generally, if you needed true OLAP of DM -- you would "know it".




Rating

  (7 ratings)

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

Comments

Subramanian, November 23, 2002 - 9:32 pm UTC


I need some document for Information about error message on warehouse builder 9i

Oracle User, June 17, 2003 - 4:15 am UTC

ERROR MESSAGE:
"VLD-1108 Operatoe SPLIT is not properly connected. "
Operator SPLIT requires more input or output connections in order to generate mapping code.

DETAIL:
I have 2 tables(A, B) and one function(C - Reconcile which returns either Y or N). Then Join all three and use "SPLIT" to separate into 3 groups of data set.


These are the assumptions in the SPLIT criteria
if Reconcile_result = 'Y' and Master.Product = 'A' and details.product = 'A' then ...

if Reconcile_result = 'Y' and Master.Product = 'B' and details.product = 'B' then ...

if Reconcile_result = 'Y' and Master.Product = 'C' and details.product = 'C' then ...


After the execution, it returns error message as above.

Thank you



Another Validate Error

OWB'User, June 17, 2003 - 11:38 pm UTC

Can we search info about error message in Oracle.com web?

I have another error like this :
VLD-1111 - The mapping cannot be generate due to operators requiring different code languages.
Detail :- For mapping code generation, the generated language needs to be the same for every operator in the mapping.
Check the mapping and remove conflicting components.



Tom Kyte
June 18, 2003 - 6:02 pm UTC

metalink.oracle.com -- the support website.

Oracle 9i2 features in Warehouse Builder

Sebastien De Clercq, February 27, 2004 - 9:41 am UTC

Hi Tom,

I am facing a VLD-1113 error but METALINK, "the support website", returned no hits !

This error is related to the use of a windowing aggregate functions : AVG(NBR_CLI) OVER (PARTITION BY REF_GROUP).

The error message is :
"VLD-1113: The output expression for AVG_PCL.OUTGRP1.AVG_NBR_CLI cannot contain an aggregation function. Mapping components do not allow the use of aggregation functions inside user-defined expressions except in the Aggregation operator".

BUT the 'Aggregation operator' does not allowed the use of OVER ( PARTITION BY ...).

Any idea ?

Many Thanks in advance,

Sebastien


Tom Kyte
February 27, 2004 - 10:10 am UTC

OWB does not currently support analytic functions.

Analytic Functions in OWB

Sebastien De Clercq, February 27, 2004 - 10:38 am UTC

Many Thanks for this disappointing, but good to know, response !

It is strange that my OWB 'user-defined expression' using PERCENTILE_CONT is validated by OWB but the generated code can't be compiled once deployed in the db.

Anyway, let 's hope OWB will support analytic functions ASAP !

Sebastien


How to Search "data" on an Oracle database

Vijay Ekambaram, September 06, 2006 - 8:21 am UTC

Like, we can get a tablename from user_source,
Can we get the tablenames where a particular data is stored?! I would imagine give a query like below, to get tablenames.

Select tablename from "Oracle system table" where data = "Vendor name"

Little background to this question:

We have a new application with front-end and database given with 100's of tables. Need to find tables where the data stored, if we keyin data on the front-end forms.
One way is to put the trace-on and it's time consuming.


Tom Kyte
September 06, 2006 - 3:27 pm UTC

you do understand how backwards this is?

no, there is no such concept.

Why do you "need to find where the data is stored", would you not already know that (given you built it?)...

and if you didn't build it, you sort of need to get the documentation from the vendor to understand it.

A reader, January 31, 2008 - 3:59 pm UTC

Can you let me know if there any articles about Oracle Data Integrator (ODI)?
Tom Kyte
February 04, 2008 - 3:26 pm UTC

sure http://www.google.com/search?q=Oracle+Data+Integrator+%28ODI%29
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.