Data Warehousing & Business Intelligence at BMW Financial Services

1. Data Warehousing & Business Intelligence at BMW Financial Services Where We Are & How We Got Here

2. Presentation Goals • Share the what’s, how’s, and why’s 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 isn’t nimble enough (business perception) • Some shadow IT in the business

8. What Makes BMW FS Unique • Deeply entrenched static reporting paradigm • Business on it’s own when it came to reporting • Data wasn’t 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 “what’s” come before the “how’s”. • Deliver value every step of the way.

11. 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 What We’ve Achieved (’08)

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 it’s 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 BA’s. • 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 it’s hard to find best practices. Feels like an area of opportunity for the data management profession. • Detailed requirements don’t guarantee a successful project. But we can’t be successful without it. • Review of our approach:

18. Lessons Learned • Chunk and iterate projects whenever possible. Note: We’re 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 “what’s” before getting into the “how’s”. In other words define the business questions & problems before defining the solution. • Quality is key to keeping the end user’s confidence.

19. - Architecture, Infrastructure -

20. Where We Started (2003) • Organization was luke-warm to a grand DW/BI implementation. • “Why can’t 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 ODS…yet.

23. Current DW Architecture EDB (leases, loans) APPRO (credit apps) NA (vehicle sales) Bank (CC apps & contracts) AuctionNet (luxury auction data) VCS (extended service) Siebel (DRM data) Canada (vehicle sales) Safeguard (gap coverage) Excel (custom rollups & groupings) Davox (Dialer data) Sources Staging Extract/Transform/Load Warehouse Layer Enterprise Data Warehouse Extract/Transform/Load M M M M D D D D D D D D D HSOB FIN FCST Risk VPSO CAM LEA FOA CAN VLE New Biz IB Credit Risk Mart Layer HIST cube FOA 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) APPRO (credit apps) NA (vehicle sales) Bank (CC apps & contracts) AuctionNet (luxury auction data) VCS (extended service) Siebel (DRM data) Canada (vehicle sales) Safeguard (gap coverage) Excel (custom rollups & groupings) 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 M D D D FCST CSOB IB HSOB Mart Layer cubes cubes SL’s SL’s 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. Simple table to track cutoff dates for the various fiscal periods. 1st Half of Solution – Calendar Table

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 we’re 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. • Didn’t 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 can’t 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 Strategic Reporting Ad Hoc Querying Dimensional Analysis Scorecards & Dashboards Forecasting Predictive Analytics 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

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 we’re 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 doesn’t see value in an ODS. Looking at logical data integration tools in the Sypherlink / Altosoft category. • Don’t 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 haven’t 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. Team Structure Team Lead (1) Architect (1) ETL Developers (4) BI Developers (1) Business Analysts (2) Database Architect (1) Database Developer (1) Infrastructure (< 0.5) End User Access Services (0.5) DW/BI Team Staffing • 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: End User Services 25% Prod Support 27% Release 8% Enhancements 28% Admin/Other 14%

45. - Strategy -

46. Where To? • Confident we have a solid DW/BI foundation. • Gap: some parts of the business aren’t leveraging it, or don’t 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 Executives committed. Resources & timetable for building broad capabilities. Organization has some interest in analytics 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 3 ML4 ML5 ML 1 ML 2 • 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 • Reference Architecture Practiced and Enforced • Data Consistent Across Stores • Method to Incorporate Emerging Technology Tech. • Effective Metrics/Levers & Consistent Process Turn Value into Project Opportunities • Understand Major Levers & KPI’s at a Departmental Level • Defined Consistent Measures • Executive Support and Understanding of Levers & KPI’s • 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 • IIG Factors Used to Forecast Projections and Ensure Data Quality • Full Commitment to Service Offering and Training • Consistent Use of Backup/Disaster Plans & SLA’s • Funding Allocated Based on Projections • Strategy Developed for End User Services and Training • Enterprise Level Strategy (IS Only) for Backup/Disaster Plans Implemented • Proactive, Repeatable, & Reusable Process for End User Services and Training • Shared Accountability (via SLA’s) with Vendors • Enterprise Level Strategy (IS & Business) for Backup/Disaster Plans Implemented • Limited Set of End User Services • Inconsistent Backup and Disaster Recovery Plans • Inconsistent Use of SLA’s 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