Transcript Infromation into Action: Deliverying value from governance

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
BMO Information Management
Governance & Quality
1
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
BMO Information Management
Governance & Quality
2
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
BMO Information Management
Governance & Quality
3
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
Governance & Quality
4
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 Information Management
Governance & Quality
5
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
BMO Information Management
Governance & Quality
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
6
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
BMO Information Management
Governance & Quality
7
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
BMO Information Management
Governance & Quality
8
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)
BMO Information Management
Governance & Quality
9
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 predefined 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
BMO Information Management
Governance & Quality
10