"Mastering Unreasonable Deadlines with Oracle APEX at the German"
Mastering Unreasonable Deadlines with Oracle APEX at the German Telecom Shops Dietmar Aust / Opal Consulting 15.06.2008 Agenda Introducing Opal Consulting The customer Project AbiT The problem Choosing APEX The solution Project ShopDB Lessons learned ODTUG Kaleidoscope 2008 Dietmar Aust Page 1 Introducing Opal Consulting Dipl.-Inform. Dietmar Aust, Freelance Consultant Master's Degree in Computer Science (MSCS) 1997-2000: Consultant at Oracle Deutschland GmbH / Düsseldorf Focus areas: PL/SQL, Oracle Reports, Oracle Forms, Oracle Portal, PLSQL Web Toolkit (OWA) Seit 09/2000: Freelance Consultant Focus areas: Oracle Portal, Oracle Reports, Oracle Application Express, Oracle Express Edition Activities http://daust.blogspot.com/ http://forums.oracle.com/forums/forum.jspa?forumID=137 (APEX) http://forums.oracle.com/forums/forum.jspa?forumID=251 (XE) ODTUG Kaleidoscope 2008 Dietmar Aust Page 2 T-Punkt Vertriebsgesellschaft mbH ODTUG Kaleidoscope 2008 Dietmar Aust Page 3 T-Punkt Vertriebsgesellschaft mbH 1986: The first shops are opened May 2004 : Founded as a subsidiary of the Deutsche Telekom The T-Punkt Vertriebsgesellschaft is the only stationary distribution channel for the Deutsche Telekom in Germany Handling of 60 million private customers and over 2.5 million business customers Around 800 shops servicing customers on over 90,000 qm sales area. More than 6000 employees ODTUG Kaleidoscope 2008 Dietmar Aust Page 4 Project AbiT The problem – Background information The salaries of the employees have a fixed and a variable component, based on their sales success Direct usage of the order management systems of the coroprate divisions (T-Com, T-Mobile) The TPG gets compensated only for successful orders (credit rating, technical issues), but their employees not. The gross sales revenue is relevant (agreement with labor union), but only the net revenue is transferred by the divisions No realtime measuring of actual sales possible (no current figures for strategic decisions) Workarounds Usage of tally sheets Usage of barcode scanners (40, 68, 90 scan codes) for counting the sales ODTUG Kaleidoscope 2008 Dietmar Aust Page 5 Project AbiT The problem – Requirements Management decision Agreement with labor union Time for design, implementation and pilot phase: 6 weeks! Severe financial impact in case of failure (130% compensation for all employees) Requirements Build an order entry system for around 5.000 employees Highly available (7 days per week, 07:00 – 22:00) Simplify the process of counting the gross sales (variable compensation) − Audit proof Enable real time monitoring of actual sales Document discounted hardware sales (bundle security) − Compliance with state regulations Four interfaces with existing systems (products, user accounts, shop information, actual sales) Changing requirements until very late in the project ODTUG Kaleidoscope 2008 Dietmar Aust Page 6 Project AbiT Choosing APEX Why was APEX chosen? They didn‘t know about APEX J2EE was the preferred platform to implement web based solutions The deadline was too tight, the usual contractors couldn‘t deliver in that timeframe Opal Consulting was chosen based on a recommendation Thus: no technology discussion, just „do it“ ODTUG Kaleidoscope 2008 Dietmar Aust Page 7 Project AbiT The solution – Technical architecture Mod_plsql Sql*net http AbiT - Platform http http Sql*net http Clients Oracle RAC 10.2 Mod_plsql Hardware Load Balancer Application Server Application Server (2): Fujitsu Siemens RX 300 S2, 2 HT CPUs, 8GB RAM, Linux RH AS4 Oracle Internet Application Server 10.1.3 Database (2 node RAC): Sun Fire V490 Server, 2 Dual Core CPUs, 8GB RAM, Solaris 10 Oracle 10.2, APEX 2.2.1 ODTUG Kaleidoscope 2008 Dietmar Aust Page 8 Project AbiT The solution - Performance and usage Usage 3.500 users active per day ~ 25.000 orders per day 400.000 hits per day Utilization Application Server: − >95% idle − #http Sessions: 30 Database Server − 30% (70% with only one node running) − Sessions: 25-35 concurrent (per node) − Dedicated, no Shared Server (MTS) ODTUG Kaleidoscope 2008 Dietmar Aust Page 9 Project AbiT The solution Live demo ODTUG Kaleidoscope 2008 Dietmar Aust Page 10 Project AbiT Next steps It was planned as a temporary solution from the start Now online since 10/2006 The application will replaced by other systems in several steps Direct interfaces will be established to transfer the required information directly from the divisions => the employees will have to enter the order only once ODTUG Kaleidoscope 2008 Dietmar Aust Page 11 Project ShopDB The problem Deutsche Telekom long term goals Expansion strategy of new shops crucial Missing the expansion target in 2006 Number of newly opened shops too low Analysis showed major deficits No clear definition of tasks and responsibilites Lack of transparency, current state of processes Lack of communication, some tasks performed in parallel by different teams Time to open a shop too long No central information system, wrong addresses New rollout process was designed by external consultants ODTUG Kaleidoscope 2008 Dietmar Aust Page 12 Project ShopDB Requirements Implement a central shop database as a single „source of truth“ Consolidation and unification of different datasources Data consistency Implement the new rollout process, based on a workflow engine Clear responsibilites, deadlines per task Read- / write access to be controlled via user roles Automatic validation (geocoding) of each address to eliminate typos Visualization of maps (shops and market information) Purchasing power, age distribution, competition Process monitoring and historicization of modified data ODTUG Kaleidoscope 2008 Dietmar Aust Page 13 Project ShopDB Challenges The project plan was extremely ambitious Project kick off in April 2007 (customer analyzes requirements) First involvement in the middle of April First prototype in May Going live on 13.06.2007 (Version 0.6) Going live on 10.08.2007 (Version 1.0) Existing MS Access applications Couldn‘t migrate everything in one step => stepwise migration The application was changed until late in the project (new columns/data) Minimize downtime of the MS Access application The data model had to be redesigned Many details of the requirements were unclear => extremely iterative approach with weekly prototypes Show stuff early Get them involved ODTUG Kaleidoscope 2008 Dietmar Aust Page 14 Project ShopDB Choosing APEX APEX was already established through the AbiT project Short development cycles Performant application platform No additional complex setup needed, just install another application on the same infrastructure Project team Two developers One project manager ODTUG Kaleidoscope 2008 Dietmar Aust Page 15 Project ShopDB The solution – technical infrastructure Mod_plsql Sql*net http AbiT - Plattform http http Sql*net http Clients Oracle RAC 10.2 Mod_plsql Hardware Load Balancer http ODBC / Application Server http Sql*net Geo-Komponenten Map visualization Geocoding MS Access Clients Geo / Application Server ODTUG Kaleidoscope 2008 Dietmar Aust Page 16 Project ShopDB The solution – MS Access migration Problems: Who builds these applications? − MS Access power users, no true developers Column names >30 chars and special characters used Direct migration of the data model not sensible Changing the data and data model until very late in the project Short downtime => Solution: Normalizing the table and column names with a VBA script Database link from Oracle to MS Access (any ODBC data source) ETL process to migration the data from MS Access to Oracle Layer of views in Oracle Linking the views via ODBC and creating views in MS Access ODTUG Kaleidoscope 2008 Dietmar Aust Page 17 S&A9B_OUTLETVORSCHLAG_AUFNEHMEN_TPP Outletvorschlag aufnehmen (Partner) Project ShopDB (Key Account Manager) The solution – Workflow S&A10_STANDORT_ABSTIMMEN Standort auf 2:NEIN Standort abstimmen Suchliste? (Standortplaner) 17 1:JA P&E0B_ANSTOSS_UNTERLAGEN_TPP Anstoss zur Erstellung der Prüfungsunterlagen (Partner) (Standortplaner) 3 4 5 14 P&E1_EINHOLUNG_SICHTPRUEF P&E4C_ABSATZPROGNOSE_TPP P&E3_STANDORTEXPOSE P&E4B_ABSATZPROGNOSE_TPP UNG Einholung partnerspez. Kosten und Standortexpose erstellen Absatzprognose erstellen Einholung Sichtprüfung Absatzprognose (Standortplaner) (ZVS) (Standortmanager) (Key Account Manager) 6 9 15 8 P&E2_EINHOLUNG_MACHBARKEI P&E4D_PROGNOSEN_KONSOLIDIEREN_TP T P Einholung Machbarkeitsstudie Zusammenführung Absatzprognosen (Standortmanager) (Key Account Manager) 7 16 P&E5_SAMMLUNG_BEWERTUNG Sammlung Daten und Bewertung Absatzprognose (Standortplaner) 10 P&E6_BC_BEWERTUNG_ABSATZ Erstellung BC und qual. Bewertung Absatzprognose (Controlling) 11 P&E7_GF_VORLAGE Erstellung GF-Vorlage (Standortplaner) 12 P&E8_EINREICHUNG_GF_VORLAGE Einreichung GF-Vorlage in Umlaufverfahren (Standortplaner) 13 P&E9_FEEDBACK_GF_GO Feedback GF go (Standortplaner) ODTUG Kaleidoscope 2008 Dietmar Aust Page 18 Project ShopDB The solution – Workflow Open Source pl/sql Workflow Engine http://plflow.sourceforge.net/ XPDL 1.0 compliant, no import/export of processes Creation of processes via table inserts No UI, just backend framework Easy to integrate and stable Process visualization possible using Graphviz ODTUG Kaleidoscope 2008 Dietmar Aust Page 19 Project ShopDB The solution – Workflow Role based tasklist Email notifications New tasks Overdue tasks Embedded links to the task / shop ODTUG Kaleidoscope 2008 Dietmar Aust Page 20 Project ShopDB The solution – Workflow Status information ODTUG Kaleidoscope 2008 Dietmar Aust Page 21 Project ShopDB The solution – Geocoding of addresses Goals Unambiguous spelling of addresses − No more typos! Usage of coordinates − Make sure a certain distance between shops Software JCoder Business (infas Geodaten) ODTUG Kaleidoscope 2008 Dietmar Aust Page 22 Project ShopDB The solution – Workflow Visualization Addresses / shops Layers of information Software MapSuite (infoware) Market data (infas Geodaten) ODTUG Kaleidoscope 2008 Dietmar Aust Page 23 Project ShopDB The solution Live demo ODTUG Kaleidoscope 2008 Dietmar Aust Page 24 Project ShopDB Next steps Implement more processes to support the full lifecylcle of a shop More processes after the decision to open the shop Implement remaining functionality of the MS Access application Establish the ShopDB as the leading system for all shop relevant information in the TPG Tenacy agreements Monitoring the costs Cutting down costs Reclaim accounting differences from the service provider Current release went production on 12.06.2008 ODTUG Kaleidoscope 2008 Dietmar Aust Page 25 Lessons learned Major benefits of APEX Leverage existing Oracle skills !!! Developers can become productive very fast No steep learning curve Good separation from business logic and the UI (themes and templates) Rapidly incorporate design changes, very flexible Changed requirements until late in the project Excellent tool for RAD or agile development Strongly iterative approach (weekly prototyping) delivers very high customer satisfaction Get them involved! Oracle heterogenous services are cool! ODTUG Kaleidoscope 2008 Dietmar Aust Page 26 Q&A Contact: Opal-Consulting Dietmar Aust Web: http://www.opal-consulting.de Blog: http://daust.blogspot.com/ E-Mail: firstname.lastname@example.org ODTUG Kaleidoscope 2008 Dietmar Aust Page 27