110 likes | 385 Views
Workload Management BMO Financial Group Case Study IRMAC, January 2008 Sorina Faur , Database Development Manager. Agenda. Corporate and presenter background WLM case study System profile Business goals WLM configuration Other points. Corporate Overview: BMO Financial Group.
E N D
Workload Management BMO Financial Group Case Study IRMAC, January 2008 Sorina Faur , Database Development Manager
Agenda • Corporate and presenter background • WLM case study • System profile • Business goals • WLM configuration • Other points
Corporate Overview: BMO Financial Group Founded in 1817 as Bank of Montreal, Canada’s first bank: • BMO Financial Group is a highly diversified financial services provider. • BMOFG offers clients a broad range of personal, commercial, corporate and institutional financial services across Canada and in the United States through BMO Bank of Montreal, BMO Nesbitt Burns, Harris Nesbitt and our Chicago-based subsidiary, Harris Bank. • Total assets of $306 Billion at January 31, 2006 • More than 33,000 employees
Information Management Group at BMO Financial Group • Award Winning Center of Competence for the Data Warehousing and Business Intelligence Solutions in BMO Financial Group • Develop & manage core data warehouse and data marts environment • 10+ years of experience • Manage Business Intelligence competency across our various Bank businesses • Provide and ensure information governance and quality standards are in place across the enterprise • 150+ staff, 100+ contractors
Presenter Background • Database Development Manager in Information Management Group. The team of 12 DBAs, provides support for Data Warehouse and Datamart environments varying from 100 GB to 10 TB of data for BMO Financial Group. • Area of expertise includes performance tuning and database design of large databases for BI applications. • IBM Certified Advanced Database Administrator • IBM Certified Solution Designer – DB2 Business Intelligence
BMO Information Management Environment • 1 Data Warehouse • 20+ Data Marts • 40+ source system feeds • Supporting multiple lines of businesses • Canada and US • Customer profitability, Marketing, Campaign management • Finance Profitability & Reporting • Risk Management & Basel • Fraud Detection & Anti-Money Laundering • Statutory Reporting
BMO IM Technical Production Environment • BCU – Balance Configuration Unit • 8 data nodes (60 TB disk) • 2 admin nodes • Servers • 6 P690 (16CPU to 32CPU) • 3 P595 (16CPU to 48CPU) • Disk Subsystems • IBM ( total 100 TB) • EMC • DBMSs • DB2 ESE PDF V8.2, 9.1 • Oracle • SQL Server • Data Integration… • Ab Initio for ETL • Informatica for ETL • IBM Intelligent Miner Scoring • Peoplesoft • Promod for DB2 • Trillium • Information Access • Microstrategy, SAS • MS Access, MS Excel, MS Reporting Services • Web Focus, Cognos • UNICA, Crystal Reports • Metadata • CA Metadata Repository
System Profile • “NEWDB” database on new BCU platform • Next generation data warehouse platform • Consolidates legacy stand-alone data marts • Addresses emerging business needs • Business details • Customer Environment Insight include the following business units: • Customer profitability analysts • Customer segmentation and modeling analysts • Marketing and campaign management staff • Technology details • DB2 LUW 9.5, including new WLM capability • IBM BCU platform • 64+8 CPU, 60 TB storage
Business Goals • 1. Allocate limited resources among multiple business units • 2. Change resource allocation on a schedule according to day of month • 3. Protect against extremely high cost inefficient queries • 4. Prevent excessive concurrency of large queries from monopolizing the system
WLM Implementation • WLM in DB2 9.5 • Part of the database engine • Define work categories by application, user group, user ID • Define execution environments and route work categories to its execution environments (DB2 service classes and AIX service classes) • Control and monitor execution environments via thresholds. • BMO WLM configuration • Work categories (with different WLM controls) • One category per business unit (so can control each one separately) • Controls per work category: • Set a target number of CPU shares per group (for Goal 1) • Increase CPU share for a business unit on days when it has high priority reports (for Goal 2) • Database-wide controls: • Set a threshold to stop requests that exceed a very high estimated cost (for Goal 3) • Set a concurrency limit on large queries (for Goal 4)
Other Points • Strong relationship between WLM and Monitoring • Business not aware of resource consumption by different dimensions: • Time hierarchy • Application and application hierarchy including ad-hoc vs pre-defined work, data mining tools vs reporting • User and user hierarchy including roll up by business unit • Workload complexity • Need to understand resource usage patterns • Collect and analyze data for optimization opportunities • Collect and analyze data for capacity planning and forecasting