Published on August 13, 2014
Information Excellence informationexcellence.wordpress.com Harvesting Information Excellence Information Excellence 2014 Jul Knowledge Share Session Ravindranath Shenoy, GENPACT BI solution implementation roadmap Hosted by
Ravindranath Shenoy: IEG Session 2014 Jul BI solution implementation roadmap The Business Case, Data Prep, Exploratory Analysis, Modeling, Deployment and Measurement cycle.
Ravindranath Shenoy 3 Ravindranath Shenoy is a seasoned business intelligence architect with strong experience in analytics and building analytics based solutions. He has contributed in different capacities ranging from BI Solution architect, Analyst and consultant. Ravi is currently the Senior Manager with GENPACT, leading solution conceptualization and delivery on its customer engagements. He has previously been with Hansa Cequity, Sapient, Nucleus Software building Analytical and BI Solutions and Roadmap. Ravi is also an avid historian with keen passion about Mughal History and Historical places. He is also a regular in the Marathons in the country. Ravindranath Shenoy Senior Manager GENPACT http://image.slidesharecdn.com/ravindranathshenoybisolutionimplementationroadmap2014julieg-140813100740-phpapp01/95/ravindranath-shenoy-bi-solution-i-mplementation-roadmap2014-julieg-3-638.jpg?cb=1407942494
Presentation Title Goes Here Date: April 2013 Implementing an end-to-end BI Solution http://image.slidesharecdn.com/ravindranathshenoybisolutionimplementationroadmap2014julieg-140813100740-phpapp01/95/ravindranath-shenoy-bi-solution-i-mplementation-roadmap2014-julieg-4-638.jpg?cb=1407942494
Approach to an end-to-end Business Intelligence Implementation 2 Business Analysis IT Analysis Solution Roadmap Solution Design Implement Testing & Deployment Source System Analysis Source System Analysis Requirement Analysis Requirement Analysis KPI & Metrics Definition KPI & Metrics Definition Technical Evaluation Technical Evaluation Understand Existing Architecture Understand Existing Architecture Study Existing BI Solution & Process Study Existing BI Solution & Process Study Existing DW & Solutions Study Existing DW & Solutions Prepare Gap Document Prepare Gap Document Draft Solution Roadmap Draft Solution Roadmap Logical & Physical Data Model Logical & Physical Data Model ETL DesignETL Design Report Visualization Design Report Visualization Design ETL BuildETL Build Build Metrics Build Metrics Build Reports & Dashboards Build Reports & Dashboards Layout Process Maps Layout Process Maps User Training User Training Conduct UAT Conduct UAT
3 Business AnalysisBusiness Analysis IT AnalysisIT Analysis Solution RoadmapSolution Roadmap Solution DesignSolution Design ImplementImplement Test & DeployTest & Deploy • Met IT team, understood current architecture and as-is landscape • Understood various data sources and ETL to refresh them • Understood data quality process currently being done and gaps therein Infrastructure • Circulated pre-work survey to gather preliminary information on reports for Reporting teams • Interviewed each reporting analyst in these teams to complete to collect information on the following for all standard reports • Key metrics reported • User of Report • Key Data sources used • Tools Used • No. of hours spent on creating the report • Frequency • Reconciliation / Manual Effort • Output format • Collected a copy of each report • Identified key metrics used in reports • Designed and circulated a user survey for user feedback on reports Reporting/Analytics • Metrics-Variables analysis and coming up with • Common variables • Ordering reports in the sequence of common variables used • Overlap of common variables and core variables initially shared during assessment • Overlap of common variables and score card variables • Solution roadmap • Detailed plan for the solution Outputs
4 Business AnalysisBusiness Analysis IT AnalysisIT Analysis Solution RoadmapSolution Roadmap Solution DesignSolution Design ImplementImplement Test & DeployTest & Deploy Select Criteria and required filters Manually Change Date in SAS / SQL Integrate numbers manual copy & paste Integrate numbers through SAS import or manual copy & paste Pre-Extraction Sample Validation Pre-Extraction Sample Validation Correction for end data Correction for discrepancy in back end data Calculation of Metrics Export to Excel and Refresh of Pivot Current Reporting Process a generalized view * Data Extraction Time (520 hours, 47%) SAS / SQL Queries BO Reports Manual Reports Generate BO Report ExportExport to Text Database Excel Cube Access Database Copy toCopy to Access Validation Reports Validation against other Reports Reconciliation Groups Reconciliation with other Groups Post Reporting Resolution Post Reporting Query Resolution Publish Report Manual Effort Time (581 hours, 53%) Manual effort spent on Sample Validation and Back end data correction 11 11 11 Effort is spent on Manually changing selection criteria or copy & pasting data for standard reports 22 22 22 22 22 Reconciliation, Validation Time (19%) spent on Reconciliation, Validation and Post Report Query Resolution can be freed up 33 33 33 32 hours of report generation time can be saved by automating report uploads 44 44 22 33 Eliminate through clean data Eliminate through automation Reduce through Clean data Eliminate through automation
5 Business AnalysisBusiness Analysis IT AnalysisIT Analysis Solution RoadmapSolution Roadmap Solution DesignSolution Design ImplementImplement Test & DeployTest & Deploy Ad-hoc analysis time Savings Ad Hoc Analysis Data Extraction Time Reconciliation& Validation 15% 269 Hours 18% 312 Hours Other Manual Effort Time 30% 520 Hours 659 Hours Capacity spent on Standard Reporting 1101 Hours Man-Hours,%ofCurrentCapacity This capacity creation can be achieved by Solving the data problem: Establishing a Cleaned up Single Reliable Source of Data and standard data definitions across departments Process Improvement: Improving the overall Reporting Process 15% 264 Hours 100% 37% 37% Ad Hoc Analysis Savings Data Extraction Savings Recon & Val. Savings Current Capacity (Effort utilized in Std. Reporting & Ad Hoc Analysis) 1760 Hours 100% 816 Hours 22% 390 Hours 8% 135 Hours 9% 156Hours Data Extraction Savings Recon. & Val. Savings Other Manual Effort Savings Effort utilized in Std. Reporting& Ad Hoc Analysis in FUTURE STATE Total of 945 man-hours or close to six analysts be freed up per month! Other Manual Effort Savings 46%
6 Fin 1 src2 src5 src6 src7 Operational Data Store (Oracle) DOZ SRV Historical Data (SQL Server) Data Warehouse (SQL Server) Risk BIU Finance GL Reports CAD SAS Datasets Access Cubes BO Reports Primary Data – “Source Systems” Intermediate Data Storage “Data Warehouse” Reporting Layer User built Custom Data Sets Users have built multiple “Custom” Data Sets over time Current Data Environment is not optimally designed to meet user requirements Users face ” multiple versions of truth’ in reporting Day 4 Day 3 src4 Day 8 To Day 14 Data Availability This is leading to significant delay in data processing Other ODS PROD SRV Excel Reports SAS Report Other Sources Business AnalysisBusiness Analysis IT AnalysisIT Analysis Solution RoadmapSolution Roadmap Solution DesignSolution Design ImplementImplement Test & DeployTest & Deploy
7 Clean Data Enhance Reporting • Create a “Single source” Data Mart with: • Clean and Reconcile Variables • Faster Data refresh process • Data dictionary • Consolidate Reports and improve Reporting Process • Migrate Reports to the new Data Mart Clean Data Enhance Reporting • Expand the “Single source” Data Mart as required • Migrate additional Reports as required • Create new reports to complete KPI coverage /Customer level Enable Data Analysis Enable Analytics Projects • Infuse Analytics Skill • Train and augment to create Analytics skilled capacity • Create Analytics Org Bank wide Analytics led Strategic Projects to drive Revenue Growth & Cost savings Executives have access to quick and accurate analysis to support Data Driven decision making Accurate & timely KPIs and the ability to create new & modify reports with changing business needs Step 1 Step 2 Step 1 Step 4 Step 3 Step 2 contd. Step 1 contd. • Clean Data and freed up capacity from Phase 1 will automatically enhance Data Analysis capability Well designed Data environment is in place supporting all MIS and Analytics requirements Business AnalysisBusiness Analysis IT AnalysisIT Analysis Solution RoadmapSolution Roadmap Solution DesignSolution Design ImplementImplement Test & DeployTest & Deploy
8 src1 src2 src3 src4 src5 Operational Data Store (Oracle) DOZ SRV Historical Data (SQL Server) Data Warehouse (SQL Server) Risk Sales/Acquisition Finance Daily Refresh Monthly Refresh GL Reports CAD SAS Datasets Access Cubes BO Reports Reporting Data Mart CURRENT STATE END STATE src1 src2 src3 src4 src5 Operational Data Store (Oracle) Daily Refresh Reporting Interface Daily/Monthly Refresh Risk BIU Finance CAD PRD SRV BIU Sales/Acquisition Excel Reports SAS Reports Business AnalysisBusiness Analysis IT AnalysisIT Analysis Solution RoadmapSolution Roadmap Solution DesignSolution Design ImplementImplement Test & DeployTest & Deploy
9 Security for role based access Operations and Monitoring (data reconciliation) ETL Flow Query Flow Peoplesoft Operational Environment Persistent Data Store Environment Business Intelligence & Analytics Environment Business Data Store (BDS) for Operational Reporting – 3 months Integration By CS subject areas Data Marts Layer - 2 years (Phase1) Staging layer LandingZone(forflatfiles) Raw Data From Source Data Warehouse Dimensional - 5 years eWFM IBS IVR ACD Slowly Changing Dimensions, Reference/Lookup And Fact Tables With Base Metrics This image cannot currently be displayed. • KPI’s • Scorecards • Dashboards SSISETLforloading&integration ODSSystems Integration Adapters SiyanDiza Qfinity DGVM Dialer eGain SSISBasicValidationchecksandErrorHandling Error Reporting Validated Source Data 360 CSR View Customer Interactions SSISETL(Transformation) ACD Master Data • Schema and Application Objects to connect to BDS/DW/DM BI Semantic Layer and Delivery Platform (Reports, Dashboards And Analytic Applications) • Standard/Adhoc Reporting • Self Service Reporting • Boardpack • Dashboards Reports & Analysis Integration Adhoc/Self Service Power Users Standard Report Consumers Analytical Users SSISETL(BusinessMappingRulesforderived measures,aggregatesandsnapshots) Aggregate tables and MSTR Cubes Data Warehouse Data Marts 9 Qmatic CDI (Phase 2) IBS Excel Business AnalysisBusiness Analysis IT AnalysisIT Analysis Solution RoadmapSolution Roadmap Solution DesignSolution Design ImplementImplement Test & DeployTest & Deploy
10 Business AnalysisBusiness Analysis IT AnalysisIT Analysis Solution RoadmapSolution Roadmap Solution DesignSolution Design ImplementImplement Test & DeployTest & Deploy Identify & Define Business MetricsIdentify & Define Business Metrics Identify & Study Data sources that feeds into these metrics Identify & Study Data sources that feeds into these metrics Profile the dataProfile the data Model Staging & DWModel Staging & DW Model DMModel DM Reports DesignReports Design
11 Business AnalysisBusiness Analysis IT AnalysisIT Analysis Solution RoadmapSolution Roadmap Solution DesignSolution Design ImplementImplement Test & DeployTest & Deploy Project PlanProject Plan Project TrackerProject Tracker Deployment DocumentDeployment Document
Thank you.. 12
Community Focused Volunteer Driven Knowledge Share Accelerated Learning Collective Excellence Distilled Knowledge Shared, Non Conflicting Goals Validation / Brainstorm platform Mentor, Guide, Coach Satisfied, Empowered Professional Richer Industry and Academia About Information Excellence Group Progress Information Excellence Towards an Enriched Profession, Business and Society
About Information Excellence Group Reach us at: blog: http://informationexcellence.wordpress.com/ presentations: http://www.slideshare.net/informationexcellence linked in: http://www.linkedin.com/groups/Information-Excellence-3893869 Facebook: http://www.facebook.com/pages/Information-excellence-group/171892096247159 Google+: https://plus.google.com/u/0/communities/102316155996060621595 twitter: #infoexcel email: email@example.com firstname.lastname@example.org Have you enriched yourself by contributing to the community Knowledge Share..
Research/ Dissertation on “How online selling has changed the marketing perspectiv...
مشروع قانون يتعلق بالقضاء على كل أشكال العنف ضد المرأة
This brief examines 2013 demographic data recently released by the U.S. Census Bur...
This presentation shows you the advantages and the importance of Big Data in these...
BI solution implementation roadmap (Ravindranath Shenoy) ... 05:15 PM to 06:30 PM BI solution implementation roadmap. ... Knowledge Share Sessions.
♦Responsible for architecting and implementing BI & analytics solutions ... ♦Was responsible for the design and implementation of ... Ravindranath Shenoy.
Consultant Datawarehouse Articles, experts, jobs, and more: get all the professional insights you need on LinkedIn
... (Apparel and Footwear Solution), ... (Requirement analysis, Design, Implementation), Product Maintenance, Custom Development, ... Ravindranath Nayak,
To connect with Vishnu, sign up for Facebook today. Sign Up Log In. Favorites. Music. Dreamland (Robert Plant album) Terrene
... (66) C-SCM (17) C-PC (8) PeopleSoft (16) Total 230 CES C-BI & KM (47) Solution ... Implementation of Solutions around ... RAVINDRANATH K.