Discussion Locked

Please give me your advice, feedback, anything! Thank you in advance!

0 helpful
9 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Over the years I have had the pleasure of working with several IT departments. I have so much respect for the amazing things they do and believe that they rarely get the credit or recognition they deserve.
Unfortunately, my new job has an IT department that is the absolute worst. They refuse to help with anything, don't care, and play ping pong most of the day in the breakroom.
I work for a small company that has been doing all of their reporting through a reporting program called Discoverer and through time-consuming Excel files using vlookups at least two time each for report. The information to Discoverer comes from Oracle.

I thought it would be better to get the information from Oracle through ODBC and use Access. Is this a good idea or bad? Let me know your thoughts.

I did manage to get the ODBC connection to the Oracle tables so that I can "Get External Data" into Access and start creating reports (hopefully ones that are automatically e-mail out by Outlook using an Access macro) but their are thousands of Oracle tables and I don't know what to do now. Any thoughts on how I should proceed, what I should do, or maybe what classes I should take?

Anyone's and everyone's help and input is greatly appreciated. I worry about this so much I am now losing sleep. I am trying to do research on my own, but I don't seem to be going in the right direction or any direction at all for that matter.

My sincere appreciation for your assistance.
Gratefully,
Keith
keithrostalski@hotmail.com

9 replies

by tony hopkinson

what you need is the database layout. A diagram of what data is stored where and how it's linked to others.
A bit of basic SQL training might be useful though. You might disturb someone's ping pong game if you execute a query that locks up the database for 2 hours.

Please tell me you don't have write access to the data.

by keithrostalski

No I don't have write access to the data. Can you imagine? Oh my God, I could destroy everything. I will definitely look into SQL training and will not do anything in production until I make sure it runs successfully in the test environment. Tony, Thanks so much for your reply. I sincerely appreciate your time and advice. Take care and thanks again!!!
Keith

by tony hopkinson

But remember just because something works in test doesn't mean it's right.

e.g.

Select * from Customers, Orders
works just fine but did you really want a table with every order attached to every customer a 1000 of each will give you a 1,000,000 'answers'.

Select * From Customers Where CustomerID in (Select CustomerId From Orders).
Will give you all the customers with orders which is a lot more useful than the above, but the inner query checking for an order for that customer will execute for every customer in the table, which is a tad inefficient and in some possible setups very expensive.

Select Customers.* From Customers,Orders
Where Customers.CustomerId = Orders.CustomerID

is much better.

SQL is very small language, but there are many ways of doing what you want, pick the wrong one and guys with bats could be paying you a visit.
Though from the sound of it some time in the office might be well spent.

by howdy4499

Additionally, you'll want to get some basic relational database knowledge under your belt. You're going to need that so that you understand how those many tables link up with each other.

by jeffrob78

Hi Keith,
One of the things you need to do is set up an account with Oracle Metalink.(http://metalink.oracle.com/) It has all manner of information, including access to what they call eTRM, electronic Technical Reference Manuals. You'll find relationship diagrams and table lookups that will show you primary and foreign key information for making joins.
http://etrm.oracle.com/license/MustLogin.html
Also, depending on what version of Oracle Applications you're using, in the APPS schema there are a number of Views (pre-written SQL code) that may help you get what you need with the complicated joins already made for you. Particularly look at the PMIBV and PMIFV views if you have them.

by keithrostalski

Just wanted to tell everyone who posted a reply that I really appreciate your time and guidence. Thanks for helping a stranger!

Keith

by dwong

. Seems you are using Oracle E-Business suite. It is difficult to use 'link table' funtion to link "thousands of Oracle tables" into MS Access. Even you have done that, you will encounter two show stopers:

1. In order to get result, you have to join many tables together. It is deadly slow when you join Oracle table in MS Access environment.
2. MS Access may treat Oracle number field as 'String'. It is hard to deal with this.

Discoverer is one of the good choice. But using Excel vlookups is not right. Discoverer can perform vlookups function much faster, better and stable. Get Discoverer training on 'Hierarchies' and 'Item Classes' topics, you will know how to do those.

Jrobinson is right. You have to extract Oracle E-business suite data from their build in database views under apps schema. Oracle E-Business TRM (Technical reference manual) has all the information about these views. If you still couldn't understand those things ask a help from Oracle tech support or http://www.oaug.org

Crystal report, Cognos, Microsoft SQL Server Reporting services also can extract Oracle data out and generate reports.

by jshee42

Ok. Both are mighty reporting tools. In my experience Access is able to retrieve the information in a more timely fashion. I've seen Discoverer reports run for 15 minutes and then run the same report in Access and get the same information in less than a minute. For speed, go with Access.

However, when you are looking at the main set of tables in any Oracle tables, you might not be able to find what you need. I believe that Access is restricted to retrieving 10000 tables at one time. This may seem like a lot, but it didn't help me in one situation where I couldn't see the table I needed at all. I guess there were 10001 tables this time. This is where Discoverer has MS by the cajones.

I guess it's all a matter of preference. I like access also because you can pull from multiple databases as well as multiple types of databases at the same time (and I'm more familiar with it). I created a database that pulls information from an Oracle DB and inputs into an SQL DB.

One other thing about Access is that when you create ODBC connections (using the MS ODBC driver) you cannot write to the tables. Mostly a good thing.

Most of our report writers use Discoverer, but only for control of the reports. With the fantastic security of Access, it's easier to modify reports and get bogus data.

that's my $.02

Hope this helps with your issues.

This discussion is closed to new replies.