Data Warehousing

1
Data Warehousing Business Intelligence at BMW
Financial Services

• Where We Are How We Got Here

2
Presentation Goals

• Share the whats, hows, and whys of DW and BI
  at BMWFS
• Give others an opportunity to learn from our
  experience
• Hear your ideas and opinions
• Share, share, share

3
Topics

• Company Overview
• Business Drivers
• Delivery Process
• Architecture, Infrastructure
• Data Modeling Challenges
• Tools
• Data Governance Data Quality
• Organization
• Strategy

4
- BMW FS Overview -
5
Company Overview

• About BMW Group Financial Services
• Established in the U.S. in 93 to support sales
  and marketing efforts of BMW of North America.
• Offer wide range of leasing, retail and
  commercial financing and banking products
  tailored to meet the needs of BMW customers
• Offer financing to BMW dealers to expand
  dealership capabilities and enhance operations.
• Expanded into others markets and countries.
  Continue to evolve beyond a captive finance unit.
  For example we offer finance products for
  non-BMW customers and P2P.
• 3 locations (OH, NJ, UT)
• 1,000 headcount (associates contract/temp)
• 700,000 active accounts.
• Halloween is a very big deal
• DW/BI program started in 2003

6
Our Business

• Provide attractive financing products to dealers
  and customers
• Help the Sales company move cars off the lots
• Generate profit and revenue for Financial
  Services
• Key Measures
• Customer sat
• Residuals
• Bookings
• Profitability
• Delinquency
• Penetration

7
What We Have in Common

• Many disparate data sources
• Rapidly changing business needs
• Impact from current economic conditions
• IT isnt nimble enough (business perception)
• Some shadow IT in the business

8
What Makes BMW FS Unique

• Deeply entrenched static reporting paradigm
• Business on its own when it came to reporting
• Data wasnt being leveraged to its fullest, but
  business results were still healthy and strong
• Strict technology blueprint (Microsoft, Intel)
• Tactical funding model. We are not a build it
  and they will come organization.
• Rigorous release process
• Majority of IT resources are contract /
  consultant.
• Transitioning from a nimble medium-sized company
  to a less flexible, large corporation.

9
- Business Drivers -
10
Problems We Set Out To Solve (03)

• Give our business access to the data it needs for
  analysis non-operational reporting.
• Data must be reliable, integrated, historical,
  1-day latent.
• Deliver support appropriate BI tools.
• Collect, maintain, and deliver critical business
  metadata.
• Approval to help the business when they have
  questions.
• Demonstrate how the right data, delivered at the
  right time, to the right people, having the right
  data analysis skills, can significantly move the
  needle on business results.
• Business is used to approaching IT with a
  solution in mind. Create a culture where the
  whats come before the hows.
• Deliver value every step of the way.

11
What Weve Achieved (08)

• Business Processes We Support
• Financial Reporting
• Account Profitability
• Operations
• Collections
• Lease End
• Sales Marketing
• Vehicle Logistics
• Dealer Bonus Program
• Front Office (used by Sales force and Dealer
  channel)
• Customer Retention / Loyalty
• Risk
• Credit Risk
• Residual Risk
• 200 total users

• Services We Provide
• Answer questions and resolve issues with
  supported query, analysis, reporting tools
• help the business find data in EDW and Bengal
  (operational reporting database)
• Recommend best tool(s) for analysis reporting
  needs
• Optimization tuning
• Share tool and data analysis tips, techniques,
  best practices

12
- Delivery -
13
Delivery Process
Projects
Final IT estimate
Business go / no-go decision
IT reserves capacity, project scheduled for
future release
Initial request w/high level scope
Initial IT estimate
Business go / no-go decision
Maintenance
IT reserves capacity, tickets scheduled for a
future release
Ticket w/ high level scope
IT Estimate
Business owners prioritize tickets for next
release

• Heavy emphasis on delivering projects on time and
  on schedule
• IT capacity and budgets very transparent
• Lot of process around managing capacity (supply)
  and business requests (demand)
• If demand supply, business may need to
  prioritize
• IT capacity takes into consideration production
  support admin / overhead
• DW/BI team delivers 30 projects and 100
  maintenance requests / year.

14
SDLC

• Have one, but it only addressed transaction
  processing solutions.
• DW/BI team defined process collateral for data
  solutions. Matured over time. Integrated with
  core SDLC in 07.
• Analysis Reporting Requirements
• ETL, BI, Data Model Design
• Estimating Model
• Business Rule Validation, Source Data Quality
  Verification

15
Releases

• All platforms follow a common release schedule.
  Very efficient.
• DW/BI platform tied to these dates for the simple
  reason that we have to react to changes in our
  sources.
• However its not easy to knit iterative BI
  development into this schedule.
• Key users may not be available at the right times
• Harder to deploy changes off cycle
• Harder to manage DW/BI capacity budget

16
Testing

• 1. Unit
• Responsibility of each developer
• Verify individual code components using low
  volume, sample data
• 2A. System
• Full volume production data
• Test cases with expected results. Verified by
  Build Team and BAs.
• Also used for performance testing
• 2B. Regression
• Full volume production data loaded separately
  through old and new ETL process
• SQL used to concatenate columns mechanically
  compare row images
• Focus on a few high risk DW fact tables each
  release
• High effort to build regression scripts but very
  re-usable efficience
• 3. UAT
• Full volume production data
• Test cases with expected results. Verified by
  the business.
• Goal is 0 defects, we often come close
• DW/BI platform goes beyond the IS standard for
  testing. Higher up-front effort but worth the
  effort.

17
Business Requirements

• Super critical...but its hard to find best
  practices. Feels like an area of opportunity for
  the data management profession.
• Detailed requirements dont guarantee a
  successful project. But we cant be successful
  without it.
• Review of our approach

18
Lessons Learned

• Chunk and iterate projects whenever possible.
  Note Were still trying to figure out the best
  way to marry iterative development to a fixed
  release schedule.
• Good requirements have value. They can (and
  should) evolve during the delivery process, but a
  baseline is important.
• Start with whats before getting into the
  hows. In other words define the business
  questions problems before defining the
  solution.
• Quality is key to keeping the end users
  confidence.

19
- Architecture, Infrastructure -
20
Where We Started (2003)

• Organization was luke-warm to a grand DW/BI
  implementation.
• Why cant you put all the data in one big
  table?
• Big bang approach did not fit tactical funding
  model or culture.
• Majority of business was getting data from an
  un-architected near real-time reporting database.
• DW/BI Team made a conscious decision
• Start small, deliver business value quickly and
  frequently
• Grow organically, but make sure every step is on
  the path to an enterprise solution.

21
Evolution

• 2003 Daily reporting database to support Dealer
  Bonus Program.
• 2004 Separate monthly analysis reporting
  database for Risk.
• 2005 Databases consolidated into EDW with
  first architected relational marts for Front
  Office Reporting.
• 2006 Higher value solutions ex. Customer
  Retention Loyalty. Additional relational marts
  deployed.
• 2007 First enterprise launch of true BI for
  Front Office Analytics. Additional relational
  marts, first cubes and semantic layers deployed.
• 2008 More demand for BI. Also significant focus
  on hardware upgrade.
• 2009 Commitment for mission critical BI
  initiatives (ex. Collections / Delinquency
  Analysis, Pricing Analytics).

Increasing business value
22
Design Basics

• Data Warehouse
• Not star schema, closer to 3NF
• Snapshot history
• Updated nightly
• Data Marts
• Relational marts are star schema, deviating
  if/when it makes sense
• Semantic layers cubes are also marts
• Nightly batch window a major challenge
• No architected ODSyet.

23
Current DW Architecture
EDB (leases, loans)
Canada (vehicle sales)
VCS (extended service)
AuctionNet (luxury auction data)
Bank (CC apps contracts)
NA (vehicle sales)
APPRO (credit apps)
Safeguard (gap coverage)
Excel (custom rollups groupings)
Siebel (DRM data)
Davox (Dialer data)
Sources
Staging
Extract/Transform/Load
Warehouse Layer
Enterprise Data Warehouse
Extract/Transform/Load
D
D
D
D
D
M
D
M
M
D
M
D
D
HSOB
CAN
New Biz
IB
VLE
CAM
FIN
VPSO
FOA
LEA
Risk
FCST
Credit Risk
Mart Layer
FOA cube
HIST cube
SL
SL
SL
Access Layer
controlled access for Infobahn, DRM, Ghostfill
ad hoc querying, analysis, standard reporting
24
Target DW Architecture
EDB (leases, loans)
Canada (vehicle sales)
VCS (extended service)
AuctionNet (luxury auction data)
Bank (CC apps contracts)
NA (vehicle sales)
APPRO (credit apps)
Safeguard (gap coverage)
Excel (custom rollups groupings)
Siebel (DRM data)
Davox (Dialer data)
Sources

• Consolidate, consolidate!
• Easier to answer more complex, higher value
  business questions
• Less dependency on IT
• Less data redundancy, more efficient
• Low cost to get here

Staging
Extract/Transform/Load
Warehouse Layer
Enterprise Data Warehouse
Extract/Transform/Load
D
M
D
D
IB
CSOB
FCST
HSOB
Mart Layer




cubes
cubes
SLs
SLs
controlled access for Infobahn, DRM, Ghostfill
Access Layer
ad hoc querying, analysis, standard reporting
25
Lessons Learned

• Not enough emphasis on mart usability.
• Inconsistent design approaches (normalized,
  denormalized, star schemas, etc.)
• Some structures hard to query
• Having a good foundation (EDW) makes it easy to
  evolve adapt the marts.

26
- Data Data Modeling Challenges -
27
The Dead Zone

• Had our fair share of unpleasant data surprises.
• Original requirement from the business
• Need Total FS Accounts as of PM, MTD, YTD
• Need Total NA Sales as of PM, MTD, YTD
• During testing we discovered
• FS and NA have different fiscal calendars for
  internal reporting tracking.
• Uncovered another 5 distinct fiscal calendars
• Start/end dates for some fiscal periods change
  over time.
• Some measures combine metrics associated with
  different fiscal periods.

28
Headache Time

• Had to
• Figure out which measures are associated with
  each fiscal calendar
• Design a process that tracked start/end dates for
  each distinct fiscal period
• Allowed updates to the calendar
• Some measures combine metrics associated with
  different fiscal periods.
• Formula FS Penetration FS Contracts / NA Sales
• FS Contracts are measures through the last
  calendar day of the month
• NA Sales are measured into the first week of the
  following month
• So what is FS Penetration on November 2nd?

29
1st Half of Solution Calendar Table

• Simple table to track cutoff dates for the
  various fiscal periods.

30
2nd Half of Solution Elegant ETL

• Logic to stop accumulating measures used in
  multiple fiscal periods, until the end of the
  last period.

FS month end is 10/31 NA month end is 11/3
frozen thru 11/3
31
Lessons Learned

• Not easy to find all the landmines via a typical
  source data assessment.
• More detailed requirements and business rule
  modeling may have caught it.
• Business SME was already part of the team! At
  the end of the day were dependent on analysts
  asking the right questions of the business, and
  the business offering the right information at
  the right time.

32
- Tools -
33
BI Toolset

• At one time Crystal Reports was the only
  supported tool, hence it became entrenched. It
  was the solution to every problem.
• When it was time to add BI capabilities, we
  evaluated several products/platforms.
• Didnt make sense to spend months months on a
  super evaluation. Vendors technology
  changing too rapidly.
• Goal was to make an informed selection and get
  started, not find the perfect BI platform.
• B.O. was a logical choice
• Synergy with our Crystal platform
• Strengths aligned with current near future
  needs
• Web Intelligence and Voyager have been deployed
• Dashboard pilot to get smart
• Universes still cant span databases.ugh.

34
Anticipating Demand
Silo solutions in the biz (unsupported)
Expected in the next 6-18 months need to get
smart here
added via current DW/BI program
Complex
Level of Complexity
Average
Where we started (pre- DW/BI)
Simple
Data Mining
Operational Reporting
Predictive Analytics
Strategic Reporting
Forecasting
Scorecards Dashboards
Ad Hoc Querying
Dimensional Analysis
Functional Needs
35
Other Tools / Technology

• Metadata
• Metacenter by Data Advantage Group
• Currently not integrated with ETL or BI toolsets.
• Using it to deliver highest value business
  metadata.
• ETL
• Informatica. Upgraded from v7 to v8 in Q2.
• Database
• SQL Server 2005
• Hardware
• Quad-core servers running Windows 2003 EE

36
(No Transcript)
37
Lessons Learned

• Generally happy with our technology choices but
  there is constant pressure to drive down cost.
• For products licensed by CPU, there may be
  different interpretations of how terms translate
  them to multi-core processors. Make it clear
  during your negotiations.
• Bundled products (ex. SSIS) and open source
  offerings are maturing. For cost reasons were
  keeping an eye on them.
• A light metadata implementation can be a good
  place to start.
• We have a large, unmet need for access to
  operational data for near real-time analysis.
  Business doesnt see value in an ODS. Looking at
  logical data integration tools in the Sypherlink
  / Altosoft category.
• Dont try to shut down silo solutions or rogue
  tools i.e. no empire building. They exist because
  they meet a need. Focus on delivering value,
  marketing accomplishments, and being a trusted
  partner. As others see value there will be less
  resistance.

38
- Data Governance Quality -
39
Governance

• Not mature in this area. No formal process, but
  still effective.
• We know who the subject matter experts are
• Rely on this group to define the single version
  of the truth (business rules, definitions, etc.)
• No challenge we havent been able to resolve,
  easily
• Gap coordinating OLTP changes before they impact
  downstream systems, including EDW.
• No automated way to do this today.
• Relies on people process. Misses occur.

40
Data Quality

• Also not mature in this area but, again, still
  effective.
• Most systems are new and internally developed.
  Data quality is generally good.
• EDW has some rudimentary data quality checks. We
  know more is needed.
• Philosophy is not to cleanse data on the way into
  the EDW. If data is bad, fix the source.

41
Lessons Learned

• Governance is important, but we see it being
  critical when more areas of the enterprise are
  sharing data

42
- Organization -
43
DW/BI Team Staffing

• Team Structure
• Team Lead (1)
• Architect (1)
• ETL Developers (4)
• BI Developers (1)
• Business Analysts (2)

• Database Architect (1)
• Database Developer (1)
• Infrastructure (
• End User Access Services (0.5)

• Technical skills are important, but the key to a
  successful team is finding people that know when
  and how to collaborate.

44
A Day In The Life

• Building, testing, implementing new data and
  functionality (project tickets)
• Defining, designing, estimating new requests
• Production support / break fix
• End User services and support
• Strategic activities (reference architecture,
  technology evaluations, etc.). Not enough time
  for this!
• Project / maintenance split is about 50 / 50.
  Typical distribution of maintenance activities

Prod Support 27
End User Services 25
Release 8
Admin/Other 14
Enhancements 28
45
- Strategy -
46
Where To?

• Confident we have a solid DW/BI foundation.
• Gap some parts of the business arent leveraging
  it, or dont see the benefit of going beyond
  basic reporting. Opportunity here.
• It is time to help the organization mature into a
  data driven enterprise. Mostly organizational
  and political, not much technical.

47
Changing Hearts Minds
Level 3 Analytical Aspirations
Level 1 Analytically Impaired
Organization has some interest in analytics
Executives committed. Resources timetable for
building broad capabilities.
Top Mngmt Support
Yes
Level 4 Analytical Company
No
To-Be State
Enterprise-wide analytic capability in
development. Corporate priority for execs.
Level 2 Localized Analytics
Current State
Functional areas tackle local needs
Level 5 Analytical Competitor
Top Mngmt Support
Yes
Organization routinely reaps benefits of
enterprise-wide capability. Ongoing support
renewal.
No
Terminal stage. Analytics not part of culture
from Competing on Analytics The New Science of
Winning by Thomas Davenport
48
Capability By Maturity Level
To-Be State
Current State
ML 2
ML 3
ML4
ML 1
ML5

• SDLC Updated with BI Processes, Tools and
  Templates
• Best Practice Driven Model Defined
• Partial Implementation of SDLC in Projects

• Full Implementation of SDLC in Projects
• Incorporate Appropriate Tools and Organization
  Support

• Ongoing Measurement of SDLC Effectiveness
• SDLC Process, Tools and Methodologies Refined

• SDLC Exists but Does not take BI Into Account

Solution Delivery (SDLC)

• Reference Architecture Does Not Exist
• No Data Consistency Across Stores

• Reference Architecture Established
• Best Practices to Model Data Defined
• Tools Selected and Reused

• Method to Incorporate Emerging Technology

• Reference Architecture Practiced and Enforced
• Data Consistent Across Stores

Tech.

• Effective Metrics/Levers Consistent Process
  Turn Value into Project Opportunities

• Understand Major Levers KPIs at a Departmental
  Level
• Defined Consistent Measures

• Executive Support and Understanding of Levers
  KPIs

• Metrics/Levers in Place to Drive Consistent
  Enterprise Processes

Business Goals Drivers

• No Dedicated Change Management Team
• Resources Lack Analytical Skills
• Partial Sponsorship

• Enterprise-Wide Sponsorship
• New Roles, Positions, Development Plans
  Compensation Models Defined
• Communication Plan Developed

• Change Management Team Staffed
• Resources Deep in BI/Analytical Skills
• Communication Plan Implemented

• Communication extends to External FS
  Divisions/Partners
• Optimized Organization, Processes and Roles
  Measured and Refined

Org. Culture

• Proactive, Repeatable, Reusable Process for End
  User Services and Training
• Shared Accountability (via SLAs) with Vendors
• Enterprise Level Strategy (IS Business) for
  Backup/Disaster Plans Implemented

• IIG Factors Used to Forecast Projections and
  Ensure Data Quality
• Full Commitment to Service Offering and Training
• Consistent Use of Backup/Disaster Plans SLAs

• Funding Allocated Based on Projections
• Strategy Developed for End User Services and
  Training
• Enterprise Level Strategy (IS Only) for
  Backup/Disaster Plans Implemented

• Limited Set of End User Services
• Inconsistent Backup and Disaster Recovery Plans
• Inconsistent Use of SLAs

Services Support
49
Other Unmet Challenges

• Master Data Management
• Dealer Number
• Customer Number
• ODS or other near real-time solution

50
Wrap Up

• Questions? Thank you for listening.
• A person who never made a mistake never tried
  anything new. - Albert Einstein
• I'm sorry this presentation is so long, but I did
  not have time to make it shorter.- Mark Twain

51
About Eric Juttner

• Information Delivery Team Lead (DW/BI platform)
  in the IS Department at BMW FS.
• DW/BI consultant and project manager at IBM
  Global Services / Business Consulting Services
  from 1996 2003. Also a member of the team that
  defined the IGS worldwide DW/BI methodology and
  SDLC.
• Started I/T career and was introduced to data
  management and data warehousing at Aetna Life in
  Casualty in the mid-1980's.
• Experiences include leading many large data
  warehouse / data integration / business
  intelligence projects in Banking and Finance,
  K-12 Education, Retail, State Local Government.
  
• Originally from Connecticut, Eric moved to Ohio
  in 1996 and lives in Lewis Center with his wife
  and 3 children.