Staying Highly Available using SAS Grid Manager The

Staying Highly Available using SAS® Grid Manager: The Performance Management Application at Commerzbank AG Thomas Braun and Miriam Schipper, Commerzbank AG, Frankfurt, Germany Cheryl Doninger, SAS Institute Inc. , Cary, NC Jochen Kirsten, SAS Institute Inc. , Heidelberg, Germany Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Staying Highly Available using SAS® Grid Manager The Performance Management Application at Commerzbank AG Seattle, April 13, 2010 PC-RB Business Management and Vertrieb Seattle April 2010

Agenda 1. Commerzbank AG – key facts Page 2 2. Integrated sales performance management required Page 6 3. Commerzbanks ”Performance Manager” as solution Page 10 PC-RB Business Management and Vertrieb Seattle April 2010 3

Milestones of corporate history Commerzbank headquarters in Frankfurt 1870: Commerz- and Disconto-Bank founded in Hamburg 1971: Transformation of New York representative office into a branch – being the first German bank to do so 1990: Opening of the first branch in east Germany following the reunification 1995: comdirect bank AG - Germany‘s leading online broker - established 2003: Increase of stake in BRE Bank, Poland, to more than 70 % 2004: Acquisition of the branch network of Schmidt. Bank (70 branches, 29 self service centres) 2005: Acquisition of Eurohypo making Commerzbank Germany‘s second largest bank 2008: No. 1 in Affluent Customers, market share approx. 15% (source: Source: FMDS 2008, TSN Infratest) 2009: Acquisition of Dresdner Bank – integration is well on track PC-RB Business Management and Vertrieb Seattle April 2010 4

Integration well on track Joint management structure Joint marketing activities First joint products Joint customer advisory council Stable governance at Head Office Yellow gradually integrated Tangible integration for customers Customers have a strong voice Joint employee events Legal merger Committee negotiations concluded Allianz Banking spun off Cultural interaction Merger on May 11, 2009 Works Council agreement reached Allianz Bank independent since June Call Centers merged Consequent Divestments Head offices merged Branches merged from 2009 Commerz Direktservice Gmb. H First EU requirements met Employees in key functions First joint branches PC-RB Business Management and Vertrieb Seattle April 2010 5

Private Customers Commerzbank’s Strategic Position Profitability Drivers Strategic Goals PC-RB Business Management and Vertrieb Seattle April 2010 § Leading retail bank in Germany with 11 million private customers: Comprehensive nationwide branch network of 1, 200 branches for Commerzbank and Dresdner Bank altogether No. 1 Retail Bank No. 1 Online Broker No. 1 Private Wealth Manager No. 3 in Retail Credit § Sustainable profit generation and value added for investors by means of synergy effects: Optimized branch network Stable revenue basis: focus on client base & high-end product mix as well as benefit from strong sales partnership with Allianz Group Further reduced cost base - lean headquarter organization and efficient back office processes Risk-return-orientation in loan business / credit portfolio management § Successful integration of Dresdner Bank to create the No. 1 bank for sophisticated private customers § Thereby above-average participation in long-term growth trends (e. g. demographics, development of savings ratio) 6

Agenda 1. Commerzbank AG – key facts Page 2 2. Integrated sales performance management required Page 6 3. Commerzbanks ”Performance Manager” as solution Page 10 PC-RB Business Management and Vertrieb Seattle April 2010 7

Branch network & customer management model Branch network Commerzbank AG in Germany as of 2010/01/01 Complexity added through need for: Private- & Business Customers 1, 695 Locations 1) main branches 18 173 regional branches § Provision of identical sales performance information at all levels § Depiction of different branch types including “Branch of the Future”, “Branch of the Future Plus” and b&p advisory centers § Integration of Dresdner Bank branches in new distribution channels § Depiction of separate customer market segments § Consideration of different relationship management models (generalists & specialists) 1, 504 branches 2) § Allocation of users based on special superior & agent-rules § Dynamic accommodation of changes to branch network – data reorganisation 1) Dresdner Bank branches incl. 2) advisory centres incl. PC-RB Business Management and Vertrieb Seattle April 2010 8

Integrated performance management of complete product offering Requirements § - Goal Complete and uniform depiction of all sales management relevant information PC-RB Business Management and Vertrieb Seattle April 2010 Depiction of the strategic product groupings Wealth management Insurance Investments Credit & loan products Payment instruments (incl. current accounts) § High flexibility allowing inclusion of new products § Depiction of both new- and existing business § Availability of revenue-, volume- und profitability information § High breath and depth of available information with simultaneous focus on Key Performance Indicators 9

Immediate translation of information into performance management Availability § § § Daily information: following day 8 am Weekly information: following Monday 8 am Monthly information: 2 working days after provision of raw data § Heterogeneous addressees § Differentiated Performance Management focus § Complex rights management -Functions -Level of Information Performance § Users: soon >10, 000 § Peak processing periods § Soon 4 terabytes SAN storage § Read- and write capabilities § 30, 000 individual queries processed every Monday § Intuitive usability § Supportive analysis process § Depiction managementprocess Processing Usability PC-RB Business Management and Vertrieb Seattle April 2010 10

Agenda 1. Commerzbank AG – key facts Page 2 2. Integrated sales performance management required Page 6 3. Commerzbanks ”Performance Manager” as solution Page 10 PC-RB Business Management and Vertrieb Seattle April 2010 11

Third stage of extension prepares for integration of Dresdner Bank 2009 / 2010 § New requirements with integration of Dresdner Bank: - Maintain same online performance despite twice as many users § Extension of existing published KPIs - Keep processing time the same and creation of new ones despite twice as much data § Implementation of daily investments § Build-up of data history: Flexibility of sales report SAS data model allows processing of § Shift of Servers’ operating system Dresdner Bank data from Windows to UNIX®; smooth § Innovative and scalable Architecture: transition due to SAS platform - Change to QFS file system, which independence allows independent data access § Proof of Scalability: Implementation of from multiple servers Unix more than doubles performance - Independent process optimization § Change to ZFS file system (zone and control by QFS concept) - Load balancing through SAS® Grid § Consolidation of application server, Manager and BIG IP aggregation server and SPD server - Parallelization of processing into one SUN server unit § Further reduction of processing time to § Reduction of processing time to 20 7 hours 1) 2008 2007 § Rollout of Performance Manger as application for integrated sales performance management § Integrated presentation of new and existing business § Analysis of consultant type, region, consumer segment and ratios of customers to full-time staff possible § Processing time: 60 hours 1) § SAS Business Intelligence Award 2007 1) Processing time simulated on current data volume PC-RB Business Management and Vertrieb Seattle April 2010 12

Reporting in ”Performance Manager” Daily Sales Support Standard Reporting Monthly Revenues Sales support High-level reports - Investments - Sales performance (weekly) - Activities - Appointments - Sales performance (monthly) - Asset-levels & customer numbers - Revenue Detail Reports - Product details & drivers Ranking - Overview branch network Managementcommentary Deal overview Basic reports - Itemization of weekly sales performance for consultants - High breadth and depth - Time series analysis Sales performance management Revenue management Advanced Analysis Options Emphasis Weekly Activities Activity management PC-RB Business Management and Vertrieb Seattle April 2010 13

Overview of the new SAS-platform Architectural principles Asynchronous mirrored design in two locations – Disaster management – Guaranteed availability Layered model with explicit separation of functions – Efficient maintenance – Simple expansion and upgrading Zone concept – In event of failure automatic switch to another function – Maintenance on ”live“ system possible QFS – Regulates server access to data – Multiple servers can operate at the same time in the database SAS® Grid Manager with 4 SPD Servers – Optimizes parallel processing – Improved I/O performance PC-RB Business Management and Vertrieb Seattle April 2010 14

SAS Grid Computing Architecture Central File Server for: § Job Deployment Directories § Source and Target Data § SAS Executables Grid Client SAS® Management Console Grid Manager and Schedule Manager Platform. TM Grid Management Service SAS® Data SAS® Enterprise Miner Integration Studio SAS® Enterprise Guide Platform LSF Other SAS Clients Platform LSF § Base SAS § SAS CONNECT® § SAS Grid Server § SAS Data Step Batch Server § SAS SPD Server Grid Node 1 Grid Node n Platform Process Mgr. TM SAS Program Platform. TM LSF SAS® Metadata Server Platform LSF § Base SAS § SAS CONNECT® § SAS Workspace Server § SAS Grid Server § SAS Data Step Grid Control Batch Server § SAS SPD Server Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Candidates for High Availability n SAS Metadata Server n SAS Object Spawner n SAS OLAP Server n SAS/Share Server n Platform Process Manager n Platform Grid Management Service n Web application tier components n Any other critical services including 3 rd party (JBOSS) Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Controlling Grid Resources With Queues n LSF queue: a grid-wide holding place for jobs n Implementation of job scheduling/control policies n Various policies can be defined on queues: • priority and preemption • job slots and slot pools • dispatch and run windows • hosts and host groups • users and user groups Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Grid-Enabling Legacy SAS Code Using the SAS Code Analyzer SAS Job STEP 1 STEP 2 SAS Job Instrumented for PC 1 PC 2 PC 3 STEP 1 STEP 2 STEP 3 STEP 4 Execution on the grid STEP 3 STEP 4 PROC SCAPROC; RECORD ‘record. txt‘ GRID ‘Grid. Count. txt‘ ; Run; Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Grid-Enabling Legacy SAS Code Wrapping the legacy source code using SCAPROC proc scaproc; record "c: tempcomments_out. txt" /* Code Analyzer comments */ grid "c: tempgrid_out. txt" /* Grid exploiting code */ resource "SASApp"; /* SAS server*/ run; proc summary data=wk_data. a; var x; output out=new 1 mean=mx; run; proc summary data=wk_data. a; var y; output out=new 2 mean=my; run; proc scaproc; write; run; /* Closes the results files */ Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Grid-Enabling Legacy SAS Code Initializing the grid infrastructure and signon /*--------------------------------------*/ /* There are 2 tasks in this job. */ /* 2 of these tasks can be RSUBMITed. */ /* These 2 tasks used 15 units of time. */ /* The longest task took 15 units of time, 100% of total time. */ /*--------------------------------------*/ %let SCAGRID_SESSIONS=2; /*--------------------------------------*/ /* Enable grid service */ /*--------------------------------------*/ %let rc=%sysfunc(grdsvc_enable(_all_, resource=SASApp)); /*--------------------------------------*/ /* This macro starts up the connect sessions */ /*--------------------------------------*/ %macro scagrid_sessions(count); %do i = 1 %to &count; signon sess&i connectwait=no; %put Session started on grid node %sysfunc(grdsvc_getname(sess&i)); %end; %mend scagrid_sessions; /*--------------------------------------*/ /* Start up our connect sessions. */ /*--------------------------------------*/ %scagrid_sessions(&SCAGRID_SESSIONS); Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Grid-Enabling Legacy SAS Code Executing the tasks /*--------------------------------------*/ /* TASK 1 run in rsubmit */ /*--------------------------------------*/ /* I/O Activity */ /*--------------------------------------*/ /* DATASET OUTPUT SEQ created in task 1 WORK. NEW 1. DATA */ /*--------------------------------------*/ /* Symbol activity */ /*--------------------------------------*/ /* SYMBOL GET task: 0 used by subsequent task: no Name: SYS_IOUSEEE */ /*--------------------------------------*/ /* ELAPSED 0 */ /*---------------------------------------------------------------------------*/ /* Get an available session with the scagrid_gs function */ /*--------------------------------------*/ proc scaproc; getsession 1 "sess"; run; %put sess=&sess; /*--------------------------------------*/ /* rsubmit for task 1 */ /*--------------------------------------*/ rsubmit &sess sysrputsync=yes cmacvar=scagrid_task_1; proc summary data=wk_data. a; var x; output out=new 1 mean=mx; run; endrsubmit; Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Grid-Enabling Legacy SAS Code Signing off /*--------------------------------------*/ /* This macro issues waitfors and signoffs for our sessions. */ /*--------------------------------------*/ %macro scagrid_waitfors(count); %do i = 1 %to &count; waitfor sess&i; signoff sess&i; %end; %mend scagrid_waitfors; /*--------------------------------------*/ /* Wait for and sign off all sessions. */ /*--------------------------------------*/ %scagrid_waitfors(&SCAGRID_SESSIONS); /*--------------------------------------*/ /* Termination for our SCAGRID functions. */ /*--------------------------------------*/ proc scaproc; shutdown; run; /*--------------------------------------*/ /* All done. */ /*--------------------------------------*/ Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

SAS Command-Line Grid Submission Utility n Standalone utility n User can submit and forget (no need to remain connected to process job) n User can view job status and output while job is running n Can kill submitted jobs n Allows for SAS checkpoint/restart capability n Uses SAS Grid Manager metadata for centralized control n Requires shared file system between client and grid Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

SAS Checkpoint/Restart Capability n SAS stores Job information • Last execution of DATA and PROC steps DATA step • Checkpoint catalog n When a Job is terminates prematurely • Start in Restart mode • SAS reads checkpoint catalog • Starts where the last successful step ended PROC step DATA step Checkpoint Library ü Xxxxxxxxx ü Xxxxx ü xxxxx PROC step • WORK tables can be stored and reused PROC step • Globale statements and macro’s will be reexecuted DATA step Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Data Storage is Key n Shared file system n Throughput across the grid • 30 -50 MB/sec/core (~1. 8 – 3 GB/sec with 64 cores) n Scalable • Handle increasing load gracefully • Readily enlargeable as data volumes grow n Locality of data • input/output files • temporary files • external data access Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

SPD Server Grid Deployment n Multiple SMP hosts, one name and data server each n Shared access to data on SAN + Client access can scale across multiple hosts + Shared read access to same tables + Shared write access to disparate tables + Highly available and transparent to client + Grid routes data acess requests transparently to the least used node - More complex to deploy Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

SPD Server Grid Deployment n What can be shared • Server executables (binaries) − Benefit grows as number of grid nodes grows − Single location for version/patch mgmt • Data domains • User database • Data domains definition file • Server parameter definition file (unless different parms are required per node) n What cannot be shared • Log and audit files • Startup scripts Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

SPD Server Cluster Table Parallel On-the-Side Loading Clustered Table New Months Jan 2005 ETL 1 Feb 2005 ETL 2 Mar 2005 ETL 3 Apr 2005 ETL 4 Aug 2004 May 2005 ETL 5 Dec 2004 Jun 2005 ETL 6 Jan 2003 Feb 2003 Mar 2003 Apr 2003 May 2003 Jun 2003 Jul 2003 Aug 2003 Sep 2003 Oct 2003 Nov 2003 Dec 2003 Jan 2004 Feb 2004 Mar 2004 Apr 2004 May 2004 Jun 2004 Jul 2004 Sep 2004 Oct 2004 Nov 2004 Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

SPD Server Cluster Table Snap in New Data in Seconds Original Clustered Table New Months Jan 2003 Feb 2003 Mar 2003 Apr 2003 Jan 2005 May 2003 Jun 2003 Jul 2003 Aug 2003 Feb 2005 Sep 2003 Oct 2003 Nov 2003 Dec 2003 Mar 2005 Snap-in Jan 2004 Feb 2004 Mar 2004 Apr 2005 May 2004 Jun 2004 Jul 2004 Aug 2004 May 2005 Sep 2004 Oct 2004 Nov 2004 Dec 2004 Jun 2005 Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Cluster Table Loading Scalability n 2: 24 n 2: 09 n 1: 55 n 1: 40 n 1: 26 n 1: 12 n. Load time [h: min] n 0: 57 n 1 x 100 GB n 2 x 50 GB n 4 x 25 GB n 8 x 12. 5 GB n 0: 43 n 0: 28 n 0: 14 n 0: 00 Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Cluster Table Reading Cluster_Year x Table_Q 1 Table_Q 2 Jan Jan . . Mar Mar … Apr Apr . . Jun Jun x x Table_Q 3 Table_Q 4 Jul Jul Oct . . . . Sep Sep Dec select. . . where date Dec in(‚Apr‘, ‚May‘, ‚Jun‘); Oct Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

As a Result. . . Run time Regular partitioned table Dynamic cluster table Number of member tables hit by query Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Materialized Views n Technically a temporary table n Materializes at view creation time n Speeds up view access drastically n Re-computes on next access when underlying data changes n Best for multi-user queries with N-way joins n Solid state disk placement being researched Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Data Compression n At libname assignment or data set level (COMPRESS=YES) n Run-length encoding (RLE) algorithm n Compression rate data-dependent (best for character variables) n Disk footprint and I/O bandwidth n Needs additional CPU-cycles both ways Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Customer Success Commerzbank AG Soluti on Challeng es - Doubling of data volume due to merger - Same or better execution times - Terabytes of data - 1000 s of users - 10000 s queries/reports - Separate IT centers for disaster recovery - SAS/Connect (MP Connect) - SAS Batch jobs (ETL) - SAS SPD Server (standard deployment) • SAS Grid Manager • SAS SPD Server (grid deployment ) Resul ts Commodity hardware high performance scalable, resilient, dynamic management of grid infrastructure SAS/Grid 4 Opteron-based grid nodes (4 quad-core CPUs each) 4 TB Disk Storage in SAN (mirrored at remote IT center) Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Customer Success Financial Industry Challeng es Saturated SAS environment had to be addressed SMP Server 36 Dual Core CPUs 144 GB RAM <Disk storage extra> <Internal bus connectivity> $2. 4 Million Soluti on • SAS Grid Manager Resul ts Commodity hardware high performance scalable, resilient SAS/Grid 36 Dual Core CPUs 144 GB RAM 7 TB SAS Work Disk Storage Infiniband Connectivity $212, 000 Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Customer Success Banking Industry Soluti on Challeng es • High performance • SAS Grid Manager processing • at the data layer • at the compute layer • SAS SPD Server Resul ts • Scoring Jobs • 100% of data in 2 hours and 30 minutes • Scoring Jobs • 10% of data in 8 hours Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Jochen Kirsten SAS Institute Inc. Heidelberg, Germany Email: Jochen. Kirsten@sas. com Copyright © 2010, SAS Institute Inc. All rights reserved. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.