BMO Financial Group Case Study - Information Resource ...

gruesomebugscuffleSoftware and s/w Development

Nov 25, 2013 (3 years and 10 months ago)

175 views

Workload Management

BMO Financial Group Case Study


IRMAC, January 2008


Sorina Faur , Database Development Manager

BMO Information Governance & Quality

BMO Information Management

1


Corporate and presenter background


WLM case study


System profile


Business goals


WLM configuration


Other points

Agenda

BMO Information Governance & Quality

BMO Information Management

2

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

Corporate Overview: BMO Financial Group

BMO Information Governance & Quality

BMO Information Management

3


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

Information Management Group at BMO Financial Group

BMO Information Governance & Quality

BMO Information Management

4


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

Presenter Background

BMO Information Governance & Quality

BMO Information Management

5


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 Environment

BMO Information Governance & Quality

BMO Information Management

6

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

BMO Information Governance & Quality

BMO Information Management

7



“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

System Profile

BMO Information Governance & Quality

BMO Information Management

8


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


Business Goals

BMO Information Governance & Quality

BMO Information Management

9


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)

WLM Implementation

BMO Information Governance & Quality

BMO Information Management

10


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

Other Points