Typical problems when reporting off of OLTP system?

general discussion
1

I am wondering what the typical problems are when reporting off the OLTP system as it relates to:

a. Universe Design
b. Report writing
c. Report performance

Also, what can/should be done to mitigate these problems?

As always - thanks for your time!

ciodice :us: (BOB member since 2005-04-05)

2

Hi,
You will face the problem in designing the report .
Because you will not get correct value ,since the values are getting updates on every transactions.It better to build a datawarehouse or datamart

Rajasekar :india: (BOB member since 2004-07-16)

3

I have done a lot of universe development with Oracle Financials. In terms of universe development, the biggest problem is how to handle outer joins. For example, invoices may be matched to purchase orders or may not. Purchase orders may have associated invoices or may not. With the version or Oracle that Oracle Financials runs on, outer joins can only run one way.

In terms of report performance, the biggest issue is that a badly written report that goes against the production database can cause the whole system to grind to a halt. The chance of that happening is dependent on the number of processors, temp space, universe design, how much training report developers are given, etc. You are better off to have a copy of the Producton instance for reporting, but lots of places don’t want that overhead.

Dennis W. Disney :us: (BOB member since 2003-09-17)

4

Also, don’t use views - use base tables. Oracle Financials has lots of views, but the views can significantly slow performance.

Dennis W. Disney :us: (BOB member since 2003-09-17)

5

Thanks for all your input.

The transactional system is actually a custom designed system – not oracle financials or based on oracle financials.

We have about 400 tables in the DB. We will have approximately 150 core users for the transactional system. Most likely 75 core users accessing canned reports and 10 users (max) doing ad hoc reporting.

A certain times of the month (i.e. end of month) we expect the # of concurrent users to be as high as 50.

I would appreciate any additional input on what problems I can expect and how I might mitigate them…

thanks again!

ciodice :us: (BOB member since 2005-04-05)