Transcript Anforderungsmanagement - Opal

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
Mod_plsql
http
http
Sql*net
http
Sql*net
http
Clients
Mod_plsql
AbiT - Platform
The solution – Technical architecture
Hardware Load Balancer
Oracle RAC 10.2
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
Mod_plsql
http
http
Sql*net
http
Sql*net
http
Clients
Mod_plsql
AbiT - Plattform
The solution – technical infrastructure
Hardware Load Balancer
Geo-Komponenten
Application Server
Oracle RAC 10.2
http
http
ODBC /
Sql*net
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)
(Key Account Manager)
Project ShopDB
The solution – Workflow
Standort auf
Suchliste?
2:NEIN
S&A10_STANDORT_ABSTIMMEN
Standort abstimmen
(Standortplaner)
17
1:JA
P&E0B_ANSTOSS_UNTERLAGEN_TPP
Anstoss zur Erstellung der
Prüfungsunterlagen (Partner)
(Standortplaner)
3
P&E1_EINHOLUNG_SICHTPRUEF
UNG
Einholung Sichtprüfung
(Standortmanager)
4
5
P&E3_STANDORTEXPOSE
Standortexpose erstellen
(Standortplaner)
14
P&E4B_ABSATZPROGNOSE_TPP
Absatzprognose erstellen
(ZVS)
6
9
8
P&E2_EINHOLUNG_MACHBARKEI
T
Einholung Machbarkeitsstudie
(Standortmanager)
7
P&E4C_ABSATZPROGNOSE_TPP
Einholung partnerspez. Kosten und
Absatzprognose
(Key Account Manager)
15
P&E4D_PROGNOSEN_KONSOLIDIEREN_TP
P
Zusammenführung Absatzprognosen
(Key Account Manager)
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: [email protected]
ODTUG Kaleidoscope 2008
Dietmar Aust
Page 27