Oracle DB to MS Access

sf_woo

Active Member
Dear List,

Our OW database is on Oracle HP UX and we would like to copy a selected list
of tables to a file server (NT) - maybe deployment server into the MS Access
format. From the Access database, we can then use the Cognos product to
query it. This is to off-load our reporting from the main database.

This process will have to a day-end kind of job - hopefully automated.

How do I go about doing it ? And MS Access would have no. of record
limitations...etc ?? Are there any better options ?


Rgds

Sook Fun
 

Kevin_Stone

Member
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C04A4D.BAE13052
Content-Type: text/plain;
charset="iso-8859-1"

First, you need to create an ODBC to your oracle database on whichever pc
you are using (Your Oracle DBA can help you with this). Then you can link
the tables from MS Access. Be warned, based on your table selection,
whatever you run can be INCREDIBLY slow. I'm not familiar with Cognos, but
unless it's capable of automating these reports, it might be in your best
interest to just use Access to create what you need. You would be able to
use "pass-through" queries to substantially increase the speed at which
these would run, and would have one less interface to interface which would
cause another substantial slow-down. Is this to avoid creating JDEdwards
reports? You might be better off getting an Access programmer in there to
create them.
 

achimirel

Member
Hi Sook Fun,
1 - Why do you need Access for Cognos; can't you connect Cognos straight to Oracle (through ODBC)?
2 - Yes, Kevin has a point; Access is slow, however using pass-through queries it will get faster, faster than using Cognos on top of Access but not necessarily faster than a regular UBE.
3 - Why do you want to off-load your main database; it is Oracle! It's meant to be LOADED; I mean, you can't compare a shopping cart (Access)with a huge tractor trailer (Oracle), can you?
4 - A better option could be using the RDA (or have a developer doing it); you may look into load balancing, too.
Regards,
Adrian

B7321 SP12.2, Oracle 805 on RS/6000, Citrix
 

Hal_McGee

Active Member
Re: Oracle DB to MS Access

Sook Fun,
I currently use Access to import data from both Oracle 7.x and 8.x databases
as well as DB2 on the AS/400. Here are my experiences and recommendations:

1. The Oracle ODBC driver always requires q password, unlike the DB2 for
AS/400, therefore we had to do an extract by scheduling a job on the HP box to
files on the HP server, then I use ftp on the NT Server to transfer the data
from the HP box to the NT server. Once the data is on the NT server, I use the
file import on a macro in Access.
2. You have to install Oracle on the NT server for the ODBC link to work. With
the above method, you do not need to install Oracle on the NT server. If you do
install Oracle on the NT server, you will need to create a Role and User that
has read only permission to the Oracle tables you want from the Unix server.
This will keep you from using ODBC to update the data on JDE. Use this User for
accessing the Oracle database from within Access. As stated in other messages,
the Access database will treat the Oracle ODBC link as a native table and you
don't necessarily need to transfer the data to Access unless you are doing
compares. However, the best method for downloading data from Oracle is to use
make table or append table queries.
3. As far as size limitations in Access, I have worked with files up to 2 gig
which is the limit in Access. I have a BoM file of 5,000,000 records and Item
Master of 900,000 records and worked successfully with them. You must however,
compact your database often because once it hits the two gig limit, you get very
strange error messages and it will not compact. Also, I recommend you keep free
space on the hard drive of at least twice the size of your largest Access
database so that Access can compact easily.

Good Luck
Hal McGee
B/E Aerospace
Seating Products Group
Group Engineering
Winston-Salem, North Carolina, USA
 

sf_woo

Active Member
FW: Oracle DB to MS Access

Hi Adrian / Kevin,

Right now Cognos is connected direct to Oracle. We experience extreme
performance issue running Impromptu reports and somehow when a complicated
Impromptu report is processing, even the SOP
suffers - key in 1 line item, wait for hour glass...etc.

We are trying to avoid the RDA as it would mean dependence on MIS personnel
whereas Impromptu is a very user-friendly tool to use even just to pull data
out for checking / investigation. We are also trying to move all
operations-related Impromptu / daily reports to the OW tools. Considering
using Impromptu for ad-hoc purposes too.

During the oracle performance tuning class, there was a topic on tuning the
DB and it not easy having OLTP and so-called DSS on the same database. It's
not cheap having to maintain another instance of oracle & mirroring
complications, etc...

I'm looking at other areas of bandwidth management and this is just an area
of concern.

Can I have more details on the load-balancing that you mentioned ?

Thanks & rgds
Sook Fun
 

MSiebenschuh

Well Known Member
We use a product called FRX for reporting and even thought when we bought it
2 years ago it was recommended by JDE we have noticed they don't play well
together. Our DBA has found that FRX does a bad job of record locking thus
causing OW to wait. You might want to investigate this path. A little tuning
and we seem to have alleviated most of the problem.

Mark Siebenschuh
 

Kevin_Stone

Member
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C04B19.31CE05E8
Content-Type: text/plain;
charset="iso-8859-1"

I am not familiar with Cognos or the Ad-Hoc Query solutions they have. But
if you are having speed problems using the application, you could have a
number of different causes, and putting Access in the middle of it will
compound the problem.

My questions are:

What kind of machine is Cognos on? A server or desktop?
Does Cognos link or import the tables (or use Oracle SQL to grab the data?)?
What tables are you pulling out of Oracle?
How many records do you have in those tables?

Kevin
 

achimirel

Member
Re: FW: Oracle DB to MS Access

Sure,
Load balancing means mapping business functions to be executed on the Enterprise Server/Workstation in order to improve performance. This is what we did:
For Sales Order Entry (P4210): B4200310, B4200690, B4201070, B4201280, B7600720, B7600730, B76A0250, B7600750, B7600790
For Shipment Confirmation (P4205): N4200790, B4200810
For Backorder Release (P42117): N4200860, B4200820, B4201260, B4200370. So far so good.
My guess is your Cognos is having the extreme performance issue because it has not been fine tuned. Did you call their support? Try again and be more decisive, something like "Either you guys fix my problem or I'll hire some big shots JDE Developers!" Good luck.

[yellow]Adrian Chimirel[/yellow][wink]
B7321 SP12.2, Oracle 805 on RS/6000, Citrix, Win95 & NT
 

sf_woo

Active Member
We run Cognos on desktops and it's using SQL statements to grap data, direct
access to the DB.

Basically the sales order header, detail, Address book, Inventory
master.....SOP related tables. Cognos uses 'joins' to link all these tables
up. If I understand correctly when there's a filter the program fetches all
records joined and then only apply the filter so it makes no difference
whether you are just selecting one order and a range of orders- it takes
about the same amount of time with maybe just some slight difference of time
in formatting it on the screen.

This is unlike the OW tool, I hope - 'fetch next' where there is no
redundancy.

I'm trying to find out if there is also a user group / list like this one
for Cognos or ways to fine-tune if possible SQL statements used. Not too
sure it's worth all the effort or just use ERW for reporting.

Thanks.
Sook Fun


---Original Message-----
From: Kevin Stone [mailto:[email protected]]
Sent: Friday, November 10, 2000 9:24 PM
To: [email protected]
Subject: RE: Oracle DB to MS Access ~~755:854


This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C04B19.31CE05E8
Content-Type: text/plain;
charset="iso-8859-1"

I am not familiar with Cognos or the Ad-Hoc Query solutions they have. But
if you are having speed problems using the application, you could have a
number of different causes, and putting Access in the middle of it will
compound the problem.

My questions are:

What kind of machine is Cognos on? A server or desktop?
Does Cognos link or import the tables (or use Oracle SQL to grab the data?)?
What tables are you pulling out of Oracle?
How many records do you have in those tables?

Kevin






--------------------------
To view this thread, visit the JDEList forum at:
http://198.144.193.139/cgi-bin/wwwthreads/showflat.pl?Cat=0&Board=OW&Number=
854
*************************************************************
This is the JDEList One World / XE Mailing List.
Archives and information on how to SUBSCRIBE, and
UNSUBSCRIBE can be found at http://www.JDELIST.com
*************************************************************
 

vap

Active Member
I can only add my 2 cents worth to discussion. I never used Cognos (because
I like another report writer a LOT better), but I did some performance
tuning for our reports, which were generated by a Visual Basic application,
using MS Jet from AS/400 data (don't flame me, we had business reasons).
First thing is that ODBC + MS Jet performance over DB/400 s*cks. It is just
slightly better over Oracle. I started digging in the Internet and found a
web site, which helped me to a great extent (I name it in the end, 'cause I
don't want to disturb those of you who mind advertising in the list).

As a side story it (the site) mentioned improving Crystal Reports report
performance from 16 to 2 hours by transferring data from AS/400 to Access
DB, and then running reports locally. It also mentioned that Crystal Reports
is specifically designed to take advantage of Access databases and runs a
lot faster. Story with Cognos may be the same or similar.

Also, a different thought - if you join a lot of tables, Cognos may perform
client side joins, and then it'll be pulling a lot of data (or the other way
around - if you join the whole address record to each line in sales order -
performance will suffer simply because it'll pull an address record for each
order line).

Sorry if this was an off-topic,
Vladimir Ponomarev
PS: I'm not affiliated with this site, I just like it. If you don't care
about my opinion - don't read it.
PPS: Site is called http://www.sqlthing.com/, story is at page
http://www.sqlthing.com/as400_reports.htm. If you use AS/400 and you are not
an absolute guru like Richard Jackson, it maybe worthwhile to have a look at
the site, it has some user-friendly information, and this SqlThing (a tool
for running SQL queries against AS/400 data) simply rocks. If I were still
working with AS/400, I'd be using it every day.
PPPS: Report writer is Business Objects. It may be not the most cost
efficient solution around, but it's THE best.
 

sf_woo

Active Member
RE: FW: Oracle DB to MS Access

Hi Adrian,

I just got hold of the documentation by JDE - 'DISTRIBUTED LOGIC - OCM
MAPPING CONSIDERATIONS',
revised Sept 1999. I noticed that for N4200790 there are 2 dependencies -
B3800070 & N3800050 for ship confirmation and they are not listed on the
list you sent earlier.

Have you done any mappings to the server for the inventory module ?

Thanks !

Rgds

Sook Fun
 

Adrian_Chimirel

Legendary Poster
Re: RE: FW: Oracle DB to MS Access

Sook Fun,
Sorry for the delay; I just came back from FormScape training - cool!
The load balancing info (dated May 27, 1999) I've sent to you was done before I joined this company. It may have been revised; I advise you to use the JDE document, test the new mapping thoroughly (in DEV) and, if you are happy with the results, consider promoting to CRP/PROD. Good luck!
Adrian Chimirel

B732.1 SP12.2, Oracle 805 on RS/6000, Citrix, Win95&NT
 

sf_woo

Active Member
RE: RE: FW: Oracle DB to MS Access

Adrian,

How do I ensure that the bsfn mapped to ES is used and not the local one ?
Will it tell in the local jededebug.log or I've got to switch the one on the
ES ? In the current debug log, it says 'Calling business function....' .I'm
about to attempt it in CRP.

Thanks.
Sook Fun
 
Top