Hi Gurus ,

My customer is planning to purchase business objects and i am asked a
question about queries which includes external data sources like
excel. The custoemr wants to join two tables which reside in different
data sources, one is an excel file and the other one is an oracle
table. Is it possible to do this join by using business objects . If
so , how can i learn the details of the operation .I wonder whether
the operation is done at client side , at server side of the business
object or at the target database server.

Imagine that my excel file has 300,000 records and the oracle table
has 10 million rows , if i join them at client site , the performance
will be suffer a lot , wont it?

Kind Regards ,
hope

"utkanbir" <hopehope_123 (AT) yahoo (DOT) com> wrote

I don't think Business Objects can do this directly; you will probably have
to do the join in Oracle or some other server-based RDBMS that can do
heterogeneous joins. Business Objects simply processes the results of SQL
queries, but it doesn't have a real database of its own. It can join several
results sets from the same database but I don't think it can join data from
multiple databases in one microcube.

Incidentally, Excel files can't have more than 64k rows, so you certainly
won't have to join 300k rows from Excel.

Hi,

As Nigel said, probably the best way to deal with this would be to
have Oracle join the two tables 'behind the scenes', with Business
Objects then retrieving all of the data directly from Oracle.

From Oracle 8i, you can create database links to data sources that
have ODBC drivers (such as Access, SQL Server, and MS Excel) by using
the bundled Heterogenious Services / Generic Connectivity feature, the
details of which are described in
http://download-west.oracle.com/docs...n.htm#HETER007

If you've got Oracle 9i, you might also want to consider saving your
Excel worksheet as a CSV file, and then registering it as an 'External
Table'. The External Table feature allows you to define a database
table that maps on to a flat file datasource, using SQL*Loader
transparently in the background to retrieve the data. External Tables
are 'read only' and cannot be indexed, but they're a quick and easy
way to make flat files available as tables, so that you can SELECT and
JOIN to them without having to learn SQL*Loader syntax. An overview of
External Tables can be found at
http://otn.oracle.com/products/oracl...ily/may24.html

Hope this helps

Mark Rittman
http://www.rittman.net



"Nigel Pendse" <nigelp.nospam (AT) compuserve (DOT) com> wrote

Hi;
Just for general info. Cognos can do what you are asking. They call it "Hot
Files", I believe, and enables the join of a local file with the larger
database.

-Rich P.

"Mark Rittman" <mrittman (AT) plusconsultancy (DOT) co.uk> wrote

tm#HETER007

For that matter, Cognos ReportNet can do this directly and at the server.
You would need the ODBC driver for Excel tables...

"Richard Price" <mmarques (AT) optonline (DOT) net> wrote

Actually, yes it can be done. We have Business Objects (6.1) where I work
and one of our users does exactly that.

The user is a 'zabo' user - that is, he uses the client version for '3-tier'
access. You can define multiple data providers per report. He directs one
data provider to the appropriate universe (Oracle), and the second data
provider to an Excel file on his hard drive. The two get linked together on
the client using their common key.

Now, I'm not sure if this can be done for 'webi' users - those that access
report via http. I seem to recall that I had difficulty exporting some
local 'connections' to the repository because they weren't 'secure' or
something.

In any case, if you really are talking about linking thousands and thousands
of rows this way, you should consider some ETL process to get this
information into your database.

A good source for Business Objects information is BOB:
http://www.forumtopics.com/busobj/about.php

Good luck!

Rob Innes
Data Services Manager
MILA, Inc.


"utkanbir" <hopehope_123 (AT) yahoo (DOT) com> wrote