Data Warehousing & Business Intelligence at BMW Financial Services Where We Are & How We Got Here
Presentation Goals o o 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 October 22, 2008 pg 2
Topics o o o o o Company Overview Business Drivers Delivery Process Architecture, Infrastructure Data Modeling Challenges Tools Data Governance & Data Quality Organization Strategy October 22, 2008 pg 3
- BMW FS Overview - October 22, 2008 pg 4
Company Overview o About BMW Group Financial Services n n n n o 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 P 2 P. 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 October 22, 2008 pg 5
Our Business Provide attractive financing products to dealers and customers: n n o Help the Sales company move cars off the lots Generate profit and revenue for Financial Services Key Measures n n n Customer sat Residuals Bookings Profitability Delinquency Penetration October 22, 2008 pg 6
What We Have in Common o o o 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 October 22, 2008 pg 7
What Makes BMW FS Unique o o o o 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. October 22, 2008 pg 8
- Business Drivers - October 22, 2008 pg 9
Problems We Set Out To Solve (’ 03) o o o o 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. October 22, 2008 pg 10
What We’ve Achieved (’ 08) o Business Processes We Support n Financial Reporting o n Account Profitability Collections o Lease End n n Sales & Marketing Vehicle Logistics o Dealer Bonus Program o Front Office (used by Sales force and Dealer channel) o Customer Retention / Loyalty o n Services We Provide Operations o n o Risk n n n 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 Credit Risk o Residual Risk o n 200+ total users October 22, 2008 pg 11
- Delivery - October 22, 2008 pg 12
Delivery Process Projects Initial request w/high level scope Initial IT estimate Business go / no-go decision Final IT estimate Business go / no-go decision IT reserves capacity, project scheduled for future release Maintenance Ticket w/ high level scope • • • IT Estimate Business owners prioritize tickets for next release IT reserves capacity, tickets scheduled for a future 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. October 22, 2008 pg 13
SDLC o o 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. n n Analysis & Reporting Requirements ETL, BI, Data Model Design Estimating Model Business Rule Validation, Source Data Quality Verification October 22, 2008 pg 14
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 October 22, 2008 pg 15
Testing 1. Unit n n Responsibility of each developer Verify individual code components using low volume, sample data 2 A. System n n n Full volume production data Test cases with expected results. Verified by Build Team and BA’s. Also used for performance testing 2 B. Regression n n 3. 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 UAT n n n 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. October 22, 2008 pg 16 o
Business Requirements o o o 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: October 22, 2008 pg 17
Lessons Learned o o 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. October 22, 2008 pg 18
- Architecture, Infrastructure - October 22, 2008 pg 19
Where We Started (2003) o o o 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 unarchitected near real-time reporting database. DW/BI Team made a conscious decision: n n Start small, deliver business value quickly and frequently Grow organically, but make sure every step is on the path to an enterprise solution. October 22, 2008 pg 20
Evolution Increasing business value 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). October 22, 2008 pg 21
Design Basics o Data Warehouse n n n o Data Marts n n o o Not star schema, closer to 3 NF Snapshot history Updated nightly 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. October 22, 2008 pg 22
Sources Current DW Architecture APPRO (credit apps) EDB (leases, loans) NA (vehicle sales) Bank (CC apps & contracts) Auction. Net (luxury auction data) VCS (extended service) Siebel (DRM data) Davox (Dialer data) Canada (vehicle sales) Excel (custom rollups & groupings) Safeguard (gap coverage) Warehouse Layer Staging Extract/Transform/Load Enterprise Data Warehouse Extract/Transform/Load Mart Layer M FCST M Risk M M Credit Risk VPSO D FIN D CAM D LEA SL Access Layer D D FO A cub e ad hoc querying, analysis, standard reporting October 22, 2008 HSOB FOA SL D SL CAN D VLE D D New Biz IB HIS T cube controlled access for Infobahn, DRM, Ghostfill pg 23
Warehouse Layer Sources Target DW Architecture APPRO (credit apps) EDB (leases, loans) NA (vehicle sales) Bank (CC apps & contracts) Auction. Net (luxury auction data) 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 VCS (extended service) Siebel (DRM data) Davox (Dialer data) Canada (vehicle sales) Safeguard (gap coverage) Excel (custom rollups & groupings) Staging Extract/Transform/Load Enterprise Data Warehouse Extract/Transform/Load Mart Layer M D FCST CSOB Access Layer SL’s IB HSOB cube s ad hoc querying, analysis, standard reporting October 22, 2008 D D SL’s cube s controlled access for Infobahn, DRM, Ghostfill pg 24
Lessons Learned o Not enough emphasis on mart usability. n n o 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. October 22, 2008 pg 25
- Data & Data Modeling Challenges - October 22, 2008 pg 26
“The Dead Zone” o o Had our fair share of unpleasant data surprises. Original requirement from the business: n n o Need “Total FS Accounts as of PM, MTD, YTD” Need “Total NA Sales as of PM, MTD, YTD” During testing we discovered: n n 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. October 22, 2008 pg 27
Headache Time o Had to: n n 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. o o October 22, 2008 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 2 nd? pg 28
1 st Half of Solution – Calendar Table o Simple table to track cutoff dates for the various fiscal periods. October 22, 2008 pg 29
2 nd Half of Solution – Elegant ETL o 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 Calendar FS Fiscal period date 10/31/08 October FS contracts October 10 NA Fiscal Period October NA Sales October 100 Prior Month Penetration [Sept’s number] 11/1/08 Nov 10 October 110 [Sept’s number] 11/2/08 Nov 10 October 120 [Sept’s number] 11/3/08 Nov 10 November 120 10/120 = 8% October 22, 2008 frozen thru 11/3 pg 30
Lessons Learned o o o 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. October 22, 2008 pg 31
- Tools - October 22, 2008 pg 32
BI Toolset o o 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. n n o 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 n n n 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. October 22, 2008 pg 33
Average Simple Level of Complexity Complex Anticipating Demand added via current DW/BI program Expected in the next 6 -18 months need to “get smart here” Silo solutions in the biz (unsupported) Where we started (pre. DW/BI) Operational Reporting October 22, 2008 Strategic Reporting Ad Hoc Querying Dimensional Analysis Scorecards & Dashboards Functional Needs Forecasting Predictive Analytics Data Mining pg 34
Other Tools / Technology o Metadata n n n o ETL n o Informatica. Upgraded from v 7 to v 8 in Q 2. Database n o Metacenter by Data Advantage Group Currently not integrated with ETL or BI toolsets. Using it to deliver highest value business metadata. SQL Server 2005 Hardware n Quad-core servers running Windows 2003 EE October 22, 2008 pg 35
October 22, 2008 pg 36
Lessons Learned o o o 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 realtime 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. October 22, 2008 pg 37
- Data Governance & Quality - October 22, 2008 pg 38
Governance o Not mature in this area. No formal process, but still effective. n n n o 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. n n No automated way to do this today. Relies on people & process. Misses occur. October 22, 2008 pg 39
Data Quality o Also not mature in this area but, again, still effective. n n n 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. October 22, 2008 pg 40
Lessons Learned o Governance is important, but we see it being critical when more areas of the enterprise are sharing data October 22, 2008 pg 41
- Organization - October 22, 2008 pg 42
DW/BI Team Staffing o Team Structure n n n o Team Lead (1) Architect (1) ETL Developers (4) BI Developers (1) Business Analysts (2) n n Database Architect (1) Database Developer (1) Infrastructure (< 0. 5) 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. October 22, 2008 pg 43
A Day In The Life o o o 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 Prod Support Release 25% 27% 8% Enhancements Admin/Other 28% 14% October 22, 2008 pg 44
- Strategy - October 22, 2008 pg 45
Where To? o o o 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. October 22, 2008 pg 46
Changing Hearts & Minds Level 1 Analytically Impaired Organization has some interest in analytics Top Mngmt Support Level 4 Analytical Company Level 2 Localized Analytics Functional areas tackle local needs Top Mngmt Support Yes No October 22, 2008 Executives committed. Resources & timetable for building broad capabilities. Yes No Current State Level 3 Analytical Aspirations Terminal stage. Analytics not part of culture Enterprise-wide analytic capability in development. Corporate priority for execs. To-Be State Level 5 Analytical Competitor Organization routinely reaps benefits of enterprise-wide capability. Ongoing support & renewal. from “Competing on Analytics: pg 47 New The Science of Winning” by Thomas Davenport
Capability By Maturity Level To-Be State Current State ML 2 Solution Delivery (SDLC) ML 1 § SDLC Exists but Does not take BI Into Account Tech. Business Goals & Drivers Org. & Culture • Reference Architecture Does Not Exist No Data Consistency Across Stores • Understand Major Levers & KPI’s at a Departmental Level Defined Consistent Measures • No Dedicated Change Management Team Resources Lack Analytical Skills Partial Sponsorship • Limited Set of End User Services Inconsistent Backup and Disaster Recovery Plans Inconsistent Use of SLA’s • § § § § Services & Support • • § § § October 22, 2008 ML 4 ML 3 • • • SDLC Updated with BI Processes, Tools and Templates Best Practice Driven Model Defined Partial Implementation of SDLC in Projects Reference Architecture Established Best Practices to Model Data Defined Tools Selected and Reused Executive Support and Understanding of Levers & KPI’s Enterprise-Wide Sponsorship New Roles, Positions, Development Plans & Compensation Models Defined Communication Plan Developed 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 • ML 5 Full Implementation of SDLC in Projects Incorporate Appropriate Tools and Organization Support • • Ongoing Measurement of SDLC Effectiveness SDLC Process, Tools and Methodologies Refined Reference Architecture Practiced and Enforced Data Consistent Across Stores • Method to Incorporate Emerging Technology • Metrics/Levers in Place to Drive Consistent Enterprise Processes • Effective Metrics/Levers & Consistent Process Turn Value into Project Opportunities • • • 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 • 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 pg 48 Implemented
Other Unmet Challenges o Master Data Management n n o Dealer Number Customer Number ODS or other near real-time solution October 22, 2008 pg 49
Wrap Up o Questions? Thank you for listening. o A person who never made a mistake never tried anything new. - Albert Einstein o I'm sorry this presentation is so long, but I did not have time to make it shorter. - Mark Twain October 22, 2008 pg 50
About Eric Juttner o Information Delivery Team Lead (DW/BI platform) in the IS Department at BMW FS. o 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. o Started I/T career and was introduced to data management and data warehousing at Aetna Life in Casualty in the mid-1980's. o Experiences include leading many large data warehouse / data integration / business intelligence projects in Banking and Finance, K-12 Education, Retail, State & Local Government. o Originally from Connecticut, Eric moved to Ohio in 1996 and lives in Lewis Center with his wife and 3 children. October 22, 2008 pg 51
Скачать презентацию Data Warehousing Business Intelligence at BMW Financial
0625208c6855b5d080a422b7ee931952.ppt