310 likes | 465 Views
Implementing Reporting & Analysis at Chicago Mercantile Exchange Hyperion User Group of Michigan Conference September 22, 2006. Agenda. Introduction Challenges Solution Results. Introduction. Introduction: Representative Companies. Chicago Mercantile Exchange (CME)
E N D
Implementing Reporting & Analysis at Chicago Mercantile ExchangeHyperion User Group of Michigan Conference September 22, 2006
Agenda • Introduction • Challenges • Solution • Results
Introduction:Representative Companies • Chicago Mercantile Exchange (CME) • Founded in 1898; Public in 2002 • World’s largest Futures Exchange • Over 1B contracts traded annually • Nearly $3 trillion traded daily (notional) • “Hedgers” and “Speculators” • Palladium Consulting • Leading professional services firm focused exclusively on helping clients successfully execute strategy • Merger of Balanced Scorecard Collaborative, Painted Word and ThinkFast Consulting • Unparalleled set of integrated services; including consulting, research, technology and education • Helping the world’s largest organizations achieve significant breakthrough results
Introduction:Speakers • Andy Banas • CME; Associate Director – Data Warehouse & Telecom Systems • Paul Palicki • Palladium Group; Midwest Region Director • Tao Zuo • CME; Technology Specialist
Introduction:Presentation Objectives • Describe the approach we took at CME to build our new “InfoSource” system • Enterprise Data Warehouse • Division & Key Department Performance Management • Discuss challenges, solution & results • Particular focus on major Essbase system components • Interactive / ask questions / try to take some of our experience back to your company
Challenges:Traditional / Old CME Stand-Alone Approach to Info Development • Unprecedented data growth • Online storage grew from 5TB to 250 TB over last five years • Although many projects wanted the same data – • Separate efforts by various IT, business & consulting groups • Different technologies and approaches • Duplication of effort; no information sharing; conflicting data, all custom design with limited functionality • Inefficient & expensive • “Data Rich, Information Poor”
Challenges:Skepticism & Biases • Skepticism • Nearly all trade publications quote Enterprise Data Warehouse Project failure rates of 50% - 75% • CME had made multiple high-profile unsuccessful attempts over the years prior to InfoSource • Business, IT and Senior Management were all skeptical • “Another set of interviews I have to participate in” • “Should we really fund this type of project again?” • Biases • Main internal customers had significant prior experience with different BI tools
Solution:CME “InfoSource” System • InfoSource “Platform” enables business people to: • Develop and share reports & analysis • Department Folders, Production Reports, Ad-Hoc Queries, Dimensional Analysis • Identify Critical Performance Metrics specific to all Divisions & Departments, and track their performance against various baselines • Division & Key Department Dashboards
Solution:Major Activities CME ActivityMonth Delivered • Designated Business Champions & appointed IT Architect Jan ‘05 • Proposed concept and H/L approach; secured preliminary approval Feb • Assembled key team members (IT & Bus; employee & consultant) Mar • Conducted interviews with “Business Liaisons” Mar • Completed Internal Assessment & Enterprise Requirements doc. Apr • Selected BI tools May • Delivered Requirements, Proof of Concept (POC), PhasedDeployment Plan, Security Plan, and Project Costs to SeniorManagement; secured approval to proceed May • Deployed Infrastructure Jul • Deployed Platform with 3 “30-day” quick hit projects;key Power Users online – 6 months from concept proposal! Aug • Created Manuals, started Classroom Training Sep • Delivered significant InfoSource EOY release Dec
Solution:Requirements - Process • Developed Interview Template • Interviewed all Business Liaisons • 28 individuals across every major CME business group • Some required multiple interviews • Basic goal was to understand information, reporting and analysis needs across CME • Discussions focused on their groups’ • Role at CME • Organizational goals • Information, reporting & analysis environment • Current vs. desired
Solution:Requirements – General Findings • Business desires a “Single View” of data across CME • Business requires better information access, and reporting & analysis capabilities • Business demands more self-reliance • Significant opportunity for increased efficiencies • Majority of time is spent securing, not analyzing data • Majority of groups are analyzing the same data • All going back to the same core systems, but using a multitude of different extracts
Functional Need dramatically better visibility into 17 specific functional areas e.g. incentive programs, product analysis, revenue analysis, competitive exchange volumes Customer Need to better understand our customers 360° View of the customer Membership Profiling Customer Configurations Solution:Requirements – By Category
Production Reporting “Canned” report scheduling, running & viewing Ad-hoc Analysis & Reporting “On-Demand” Analysis & Reporting Dimensional Analysis Pre-built, pre-processed data cubes Excel interface, to allow users to work in a familiar environment Write back, to allocate and/or forecast data that can be saved and shared within a working group Enterprise Integration Wrapping it all together – dashboards, security, metadata and alerts; e.g. Executive & department dashboards, to track Key Performance Indicator performance against various goals Thresholds & Alerting, for proactive KPI monitoring ETL Tool for data integration Solution:Requirements – By Category Tools
Solution:Requirements – Summary Summary Identified Enterprise Information Needs Representatives 28 Business Liaisons across all 10 CME Divisions 1-4 rounds with each Liaison Verified notes after each interview Conducted Interviews 120 page Requirements document 350+ specific business requirements Consolidated Requirements • Mapped all Requirements into 33 logical groupings within 4 major categories: • General (5) • BI Tool (8) • Functional (17) • Customer (3) Analyzed Requirements Developed Phased Deployment Recommendation 5 phases over the next 18 months 1-2 development lines running concurrently Satisfies major elements of Enterprise requirements (80/20 rule) Developed Supporting Infrastructure Plan Hardware, Software, Database, Roles, etc.
Solution:Major Essbase Components • Primary and MOST DEMANDING internal customer was the Finance Division • System requirements: • Excel interface • Write-back capabilities • Slice & dice query results on-demand (pre-processed) • Functional requirements: • Analysis, forecasting, reporting & budgeting • Capabilities NOW!
Solution:Major Essbase Components – PeopleSoft Financial Cube • Used for P&L Analysis, Budgeting and Forecasting • Cube data sources: • PeopleSoft Financial • Headcount files from Organizational Development • Device count reports from Information Products Management (IPM) • Cube contents: • Monthly revenue, expense and capital expenditure data by account, department and project • Statistical data such as Volume (number of trades), Headcount, IPM device count
Solution:Major Essbase Components – Exchange Fee System (EFS) Cube • Used for Rate Per Contract and other Fee related analysis. • Data is sourced from EFS and PeopleSoft Financial and includes: • Clearing fee detail at the product level by month • Totals for all other revenue accounts that roll up into the clearing and transaction fees category of the financial statements
DVT Data Flow Diagram Reporting Microsoft Excel Add-In OLAP Essbase DVT Cube ETL SQL Server DTS RDBMS STATS Database Solution:Major Essbase Components – Daily Volume Tracker (DVT) Cube • Used for OLAP analysis of Trade Volume Data • Use Essbase to replicate all calculations and metrics prepared by Accounting via manual spreadsheets (e.g., ADV %Change From Same Month Last Yr; % Of Total Volume By Product Groups Excl TRAKRS)
Results • Single, company-wide platform for: • Report development • Canned reports and ad-hoc analysis • Report distribution • Dimensional analysis • Performance Management Dashboards • Metadata and security • Business excited, engaged, more self-reliant & more efficient • Requests that took days now take minutes • Can easily analyze situations which were previously impossible • Users have replaced multiple systems with InfoSource • Management armed with much more information when making strategic decisions, visiting clients, etc. • Department level KPI identification, focus & performance management
Results “Budget completed in record time!” “Helps us identify and monitor our dept’s critical KPI’s against multiple benchmarks, and understand where we can do better” “Data retrievals cut from days to minutes” “Allows me, as a business user, to obtain instantaneous results without relying on IT” “Indispensable in helping us identify our customers” “Directly contributed to dramatic efficiency improvements; cut days off our monthly processing” “Helps our marketing efforts along with any activity that requires us to analyze where our customers are coming from, who they are, and what type of business they do with us” “Brings critical CME and industry data right to my desktop in easily constructed queries and reports” “I’ve never seen the business so excited about a project” (CME CEO)
Results CME very quickly becoming much more efficient and “Information Rich”
Results 2006 ComputerWorld Finalist for “Best BI Project of the Year”
Cube Viewer Common Security Central Repository Enterprise Meta Data Enterprise Integration • Dashboards • Alerts Production Reporting • Schedule, View & Run “Canned” Reports Ad-hoc Analysis & Reporting • Universes, Classes& Objects Dimensional Analysis • Slice & Dice • Write-back Appendix:Architecture CME User Access Route Backend Data Flow Essbase InfoSource “Single View” Database Data Cube Hyperion Excel Add-in Fees Trades Customers Financials Pricing … CME Data Sources
Appendix:Information Access Data Sources Data Cleansing, Aggregation & Storage Information Access Fees Group specific critical KPI snapshots Dashboards • Management Team • 10 CME Divisions • Multiple Departments • Specialty others Customers InfoSource Database Trades Universes • Trade Vol & Rev • Incentive Programs • PS EPM • Compliance (SOX) • Specialty others Pricing CME Analyst Other Sources Fixed format reports Ad-hoc reports Detailed analysis Data Cubes PS EPM Financials Budgeting Financial Reporting Strategic Plan Others Pre-processed dimensional analysis “Write-back” CME InfoSource “Single View of Data”
Appendix:Support Structure Accounting & Finance P&S Marketing & PR Operations CORE Team: • Create, Maintain & Enhance InfoSource Platform • System Administration & Governance • Add New Data Sources • Develop Dashboards • User Training, 2nd Level Support, Support QA Clearing Technology Legal & Market Reg DIVISION ADVISORS: • Design Dashboards & Data Cubes • Direct Activities of their Divisions’ Power Team • Collect Division Requirements & Forward to Core Team, Support QA Corp Dev OD DIVISION POWER TEAM: • Become InfoSource Tool & Data Experts • Create & Share Reports w/i their Division • Proliferate knowledge w/i their Division • 1st Level User Support w/i their Division, Support QA DATA SPOURCE OWNERS: • Subject Matter Expert (SMEs) / Data Support • Define Security, Support QA INFOSOURCE USERS • Access InfoSource Information(reports, dashboards, etc.) • Create Reports