1	Real-World Reporting in the Enterprise: A Case Study Bill Bartlett Software Analytics, Inc.
2	Introduction Will review Party City’s “Intranet & Enterprise Reporting” system Oracle9i Application Server (9iAS) Portal, Reports Server, custom portlets Network and infrastructure 24x7 operation In production since September 2001 Used by corporate, stores and vendors
3	Agenda Overview Business & Technical Needs Solution Infrastructure Custom Application Monitoring Lessons Learned Future
4	Level-Set Audience Background DBMS: 8i or 9i? Using 9iAS? Using 9iAS Release 1 or Release 2? Using Portal? Using Reports Server? Master/slave or single server?
5	Party City Corporation America’s largest party goods chain Today: > 470 Stores (235 Company-owned stores, 240 franchise stores in United States and Puerto Rico) Expected annual growth: 25-30 corporate stores, 25+ franchise stores Emphasis on technology to promote efficiency
6	Software Analytics, Inc. Full-service Solution Provider
7	Business Needs Secure Also: granular, flexible, dynamic security Scalable Robust / Reliable Access to real-time data Reports: On demand, scheduled, event-based Easy to use / Minimal training required Easy distribution of company information
8	Technical Issues Database size & activity Currently using same databases for OLTP and reports Database used for price lookups in stores, so cannot impact database performance [much] Need to access multiple databases Limited WAN bandwidth to stores
9	Infrastructure - Database Databases: Oracle 8i (8.1.7.1.0) 64 bit AIX version 750 GB primary database on a 12 CPU RS/6000 with 24 GB RAM
10	Infrastructure – 9iAS 9iAS Release 1, Reports 6i (for now…) Production environment: 6 server master/slave report server cluster 2 node master report server cluster (Windows 2000 Advanced Server + Oracle Fail Safe) 4 report slave servers 4 server web farm, load balanced via content switch (Windows 2000 Servers) Redundant & distributed connections QA environment identical to production
11	Infrastructure: Redundant Network Connections
12	Store Environment – Physical Architecture
13	Store Environment – Application Execution
14	Application Overview Web application Currently 9iAS Release 1 Originally 9iAS v1.0.2.1 / Portal 3.0.8 / Reports6i patch 4 Several interim updates Now 9iAS v1.0.2.2.2 / Portal 3.0.9 / Reports6i patch 11 9iAS Release 2 in 1Q2003 ? Need Content Area API’s
15	Custom Application 9iAS components: Portal, SSO, Reports 98% custom code (9iAS PDK / JPDK) Portlet providers: both database and web PL/SQL procedures & packages PSP (PL/SQL Pages) Java: JSP, Servlets, EJB HTML, DHTML, JavaScript, XML, XMLHTTP
16	Application Walkthrough
17	Application: Opening Page
18	Application: Opening Page – Logged In
19	Application: Report Parameter Screen
20	Application: More Complex Parameter Screen
21	Application: Report Output to Screen
22	Application: Report Queue (Manager’s View)
23	Application: Additional Tabs
24	Application: Report Results (Manager’s View)
25	Application: System Administration: Reports
26	Application: System Administration: User Admin
27	Application: System Administration: Helpdesk View
28	Application: Monitoring Section
29	Monitoring Tools: WhatsUp Gold (www.ipswitch.com) Compaq Insight Manager CA Unicenter Oracle Enterprise Manager Custom application pages Screens show different information based on security Primarily JSP / servlets / EJB wrapped around Windows Resource Kit utilities or HttpURLConnection’s Accessed both from inside application and from other monitoring tools and sites
30	Monitoring Page from Inside Application
31	Monitoring Page from Central Monitoring Site
32	Lessons Learned 9iAS Overall Reports Report Server Apache Environment Disaster Recovery
33	Lessons: 9iAS Overall API provides TREMENDOUS amount of flexibility, but you’ll need to go exploring “API spelunking” Documented API’s Semi-documented API’s (OK to use?) Private API’s: YGYBYF (use only as a last resort) JPDK doesn’t have everything yet; may need to call the PL/SQL API’s
34	Lessons: Reports Extract all data up front (via functions) Do NOT go back to database on each line Line-by-line can crush your DBMS Difficult to detect Database sessions appear to be inactive Report servers show low activity Rapidly refresh database session information to see tiny but frequently repeated activity Use packet sniffer (Network Monitor, Sniffer, etc.) to monitor the network
35	Lessons: Reports Load test each report If you don’t do this, your users will ! Assume the worst-case scenario: All available report engines will simultaneously run this report with its worst-possible criteria 17 Pentium III-based report engines can easily overwhelm a 12 CPU, 64 bit RS/6000 However, report server will ensure that all reports in the queue do eventually get processed
36	Lessons: Report Server Upgrade to Reports Patch 11 Security & scalability fixes added post Patch 10, especially in integration of Reports & Content Areas Determine the proper number of report engines Too few: “run immediately” reports will fail Too many: Can overwhelm the database server Changing number of report engines may require restarting the report cluster
37	Lessons: Report Server Need multiple ways to look at report queue Might want users or helpdesk to look at queue May be times when one method is inaccessible Queue Manager application is the best way RWCGI60 output is almost as good RW_SERVER_QUEUE is not always current (but it’s good enough for most users) Worst case: Can use the Report Server log file
38	Lessons: Report Server RepServer.ORA: traceoption=portalpush\|all Useful when diagnosing Reports / Portal interaction problems Don’t kill a report from the database until it has first been killed from the report server Otherwise this will crash the engine that was running the report, sometimes in bad ways
39	Lessons: Report Server Monitor for cluster failovers If slave cannot start properly, the master will detect the problem, and fail the cluster over. The new master will attempt to restart, fail, and fail back to the original node, and so on. Not a problem if slave is offline, only if online but unable to start report service False alerts are sometimes generated if an engine on the master crashes
40	Lessons: Apache Upgrade to 9iAS v1.0.2.2.2 ! Increase all “timeout” and “retry” settings JServ.conf: ApJServRetryAttempts 30, ApJServVMTimeout 20 zone.properties: servlet.page.initArgs=requesttime=60 Change JServ port to 8008 JServ.conf: ApJServDefaultPort 8008 jserv.properties: port=8008 Monitor servlet service; restart web server if this goes down Much better in v1.0.2.2.2
41	Lessons: Environment Have identical environments in development, QA and production Same report server configuration: cluster/single Same load balancing method Similar database size & content If only single report server in development, cannot test clustering, Fail Safe, failover, report job preservation, etc.
42	Lessons: Disaster Recovery With proper preparation, this is easy Document all cluster details (especially disk signatures) Use dumpcfg.exe to get the disk signature Clustering solutions for lab “Poor Man’s Clustering” www.SoftwareAnalytics.com for details
43	Tools TracePlus Web Detective (www.sstinc.com) Sniffer (www.nai.com) Or Windows Network Monitor VMWare GSX Server (www.vmware.com) DBArtisan (www.embarcadero.com) Or any favorite client tool that can rapidly refresh session information Sysinternals tools (www.sysinternals.com) Windows Resource Kit
44	Future 9iAS Release 2 Portal 9.0.2 Reports 9i Forms 9i ? Expand use of content areas (WebDAV, workflow, greatly expanded content) Additional “Application” portlets Quick Entry Department Inventory, QE Purchase Order OEM !
45	Benefits In production since 9/2001 Has become “business-critical” 1400 users 116 reports available (in several variations) 190,000 Reports run in the past 11 months Report size: 1 – 71,000 pages
46	Benefits Vendor access to reports On-line Training Documentation Centralized document management Reduce hard-to-find network-stored documents Eliminating MS Exchange Folder storage Eliminating daily & weekly emails
47	Summary
48	Contact Info / Questions Bill Bartlett bbartlett@SoftwareAnalytics.com Software Analytics, Inc. http://www.SoftwareAnalytics.com