Connect Microsoft Access To Business Objects

[cew657]

I have a Microsoft Access 2000 database which contains a table of loan numbers I want to use in Business Objects. How would I go about linking this table of loan numbers to the tables in Business Objects? I want to add information from the tables in Business Objects to the list of loan numbers that are in my Access Database.

I don't have access to the SDK and probably never will since I am only a user and our IT department will not allow users access to anything like this.

Does anybody have any suggestions on how to do this or can point my in the right direction?

Thanks.

cew657

 

[blom0344]

Business Objects tables do not exist in the sense that you expect. BO uses underlying databases like Access to fetch data from by using SQL. The dataset that is fetched is a 'cube', technically more or less a flatfile with data.

The whole process is meant as a read-only action. If you want to update data in Access by using Business Objects as tool you are playing a different ball-game.

It may be more efficient to directly update the access tables from within the Access database using update queries.

I suppose the data that you fetch with BO comes from another database?

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[cew657]

Business Objects is used to query data from our mortgage servicing system and contains a lot of tables we can pull essential mortgage information from. It is driven by what they call Informent. It's essentially a huge oracle database that is updated nightly which prevents us from using Business Objects until at least mid morning each day because the update cycle has not completed, which is another story. :-(

They have restricted us to a 10,000 row limit in Business Objects in order to maintain the performance of the queries and even have restricted the number of users that have access to Business Objects. So what I do is set up my report in Business Objects and run it to make sure the data is what I want, even though the results stop at 10,000 records.

Then in Microsoft Access, I set up a Pass Through query and copy the Business Objects SQL statement into this query. I then have to set up another query with the Pass Through query as my source linked to my table I am pulling information for. By doing it this way, the performance of the queries is really bad. I usually wait 45 minutes to 1 1/2 hours for results, which I would like to get away from and have more decent run times, like in Business Objects.

So, I thought if I could directly tap into my Business Objects report, I would use my table of loan numbers and link it to Business Objects to see if I could improve run times.

I thought it could be possible to use VBA in Microsoft Access to establish a connection to Business Objects and set up a SQL statement using both the tables in Microsoft Access and Business Objects to get my results but am not sure how this can be done, if at all.

Thanks for your help.

cew657

 

[blom0344]

A few comments on your situation:

1. There are no 'tables' in Business Objects. The resultset that Business Objects uses is a cube. If this is approachable with VBA is doubtful.

2. Business Objects relies on SQL as well, so a direct SQL statement from Access using a Pass-through query should be as fast as a business objects query.

BUT!

I suspect that the universes that are used have possibly been tuned with the ORACLE optimizer statements that can have enormous effect on query speed.

So, with your pass-through query it can be usefull to test the ORACLE optimize statements (HINTS) like:

/*+FIRST_ROWS*/
/*+ALL_ROWS*/

Start with the first one , which is meant for OLTP processing and can be of great impact if statistics are kept up to date. Personally I have used this to speed up queries with 6-10 fold speed.

Example:

Select /*+FIRST_ROWS*/
field1,field2
from table where ........

But you should probably take it up with a DBA or developer to check and test things. I am not really up to date with ORACLE, so there may be other tuning aspects involved...


Ties Blom
Information analyst
tbl@shimano-eu.com

 

[cew657]

Thanks for you assistance. This will give me a good start. I am going to mess around with it and see what I can do with the response times.

Thanks again.

cew657