E-Business Suite

Boy, you've got more guts on this than me.

Many clients I've been at, solve the problem of killing production from ANY report (not just Discoverer, but Oracle Reports, Oracle Forms as well for that matter) by having a reporting instance that's updated nightly, hourly ... or if your company's rich with cash - instantly.

Maybe post this on the Discoverer forum as well in technet, as there's only person I can think of - Marcus - who many times asks users why they're not using OWB for reporting.

Russ

Hi,

I would question why you would want to put your reports on a separate database for performance. We used to do that but with the latest database versions you can control the resources used by Discoverer so we don't bother any more. From 9.2 onwards you can use resource consumer groups to limit the Discoverer processes or if you are using RAC you can have a separate RAC instance for Discoverer. Both these techniques ensure that Discoverer reports do not have a performance impact on other users.

If you want to use a data warehouse (and there are good reasons other than performance for doing this) then you can use any data in Ebusiness database a source. You need to consider what data structures you want to use your data warehouse and what security model you want to use.

Hope that helps,
Rod West

Rod.

Thanks for the info on 'resource consumer groups'. Learned something new.

As for the data warehouse concept, I agree there's good reasons for it. But I've always found a DW project can become large, people intensive and costly very quickly. So one has to go into it with both eyes open.

Just my take.

Russ

But of course, I'm not saying it's bad to create a DW.

If it's a project that's been analyzed ahead of time, people chosen, etc. then if followed through to the end, can get you much more valuable answers with your data.

Thanks Rod for pointing to 'resource consumer groups'. We are developing reports in enterprise deployment of Discoverer with over 1500 user worldwide on Oracle Retail Modules. When the load testing was done the Network and DB CPU's were used up to 100%. We were looking at Datawarehouse or use a Mirror database updated nightly for reporting.I will give Resource consumer groups as a suggestion to my DBA's.

Thanks
Raj

Hi ,
Yes I have worked on building datawarehouse with source system as Oracle applications. We brought in data from GL_balances, journal lines, and the subledger details for drill through purpose. We brought in AR, AP, PO, WIP, INV, FA and OPM. We used OWB for the target mapping but all our source collection routines were custom designed and coded.


Let me know if you need more information.
Regards
Nilesh
http://www.AppsBI.com <<Application Business Intelligence

It is so amazing to get so many response. Thanks for those advises.

1. For OLTP user performance, resource group and one seperate RAC instance for reports are good advise to know. But actually I got performance complain from report users more than OLTP users. That's why I want to have a DW to reorganize data for analyst.

2. For Nilesh Jethwa
Could you please tell me how good the OWB Connector for 11i is? I am wondering if it is worth to buying it or not.

Hi,

Can somebody please let me know their experiences in using ebusiness suite as a data source particularly GL?

Thanks
Maruthi

Hi Maruthi
I've had no problems using E-Business Suite as the source for a DW. I've used both OWB and PL/SQL to run the code. I've generally put the DW on a separate box from the OLTP mainly because customers don't want to add more CPU / Memory to the OLTP box and typically the OLTP is stretched to the limit anyway.

Some enlightened companies don't have an issue with having the DW on the same database as the OLTP. This will work fine, particularly if you have one of the later Oracle databases that allows you to create partitions of differing block sizes. You might also follow up on Rod's suggestion of using resource consumer groups. Here' some good articles:
http://www.oracle.com/technology/deploy/availability/htdocs/rm_overview.html

http://www.oracle.com/technology/oramag/oracle/04-nov/o64tuning.html

http://www.dbasupport.com/oracle/ora9i/DRM.shtml

By the way, there is a misnomer in the BI world that a data warehouse has to be something huge with lots of special code. That used to be the definition but these days the definition that I like the most is the one coined by Ralph Kimball which states: A data warehouse is a copy of transaction data specifically structured for query and analysis. I think this is a simple but brilliant definition of a DW.

Note how the words large database, integration, time variant, subject-oriented, enterprise-wide and central repository are missing from this definition. To me those are the old definitions which do not do justice to the power of the new Oracle databases or what organizations really mean by having a DW.

Take a look here: http://www.intranetjournal.com/features/datawarehousing.html where you will see the difference between Kimball's and Inmon's definitions. I used to be in favor of Inmon's definition but now I think it is too clumsy.

Does this help?
Regards
Michael

Hi Michael,

Thanks so much for the response!!!

The documents you pointed out are very helpful and also I liked the article in intranetjournal.

I am also a fan of Inmon though in some cases have practiced Kimball's methodology due to project pressure, etc. I am still a fan of Inmon though. I think with some small changes (adapting to current requirement) Inmon's cif is always better from getting a enterprise view and avoid DWH silos. Also, I see these are two different schools of thought and should we adopt only one of them, I don't think so. Depending on the situation on ground we have take a call.

I don't think this is the right forum to discuss this but since the topic came, I thought I will put some of my views at a very high level,

Thanks,
Maruthi

Hi Maruthi
Thanks for replying. This forum, Business Intelligence, is exactly the right forum for discussing data warehousing and high level definitions.

There are so many different definitions of a data warehouse don't you find? In the main I agree with you and will pick and choose to suit the need, but really they're all an extension of Kimball. I hear so many people saying they have been told by a consultant they don't have a DW simply because they don't have a separate database, are not using a tool such as OWB, or are not using OLAP.

If someone has taken the time to extract data from a transactional system, then summarized it and made it available in a tool for users to query, this is a DW, a very simple data warehouse, but a DW nonetheless. If you consider a materialized view, it actually complies with Inmon's definition too because it is time variant, subject-oriented and not subject to change.

I think its fascinating how times change. Also, there is not mention of the word OLAP in any of the data warehouse definitions so please don't let anyone tell you that it must be OLAP to be a DW. This is just not true. Organizations have used relational structures for over 30 years in data warehouses and they work just fine. OLAP has its place, but its not a prerequisite.

Best wishes
Michael