1 / 35

Anil Kumar Sharma Director Reserve Bank of India Mumbai anilksharma@rbi

Regulators Experience in DW and BI Implementation and Usage. Anil Kumar Sharma Director Reserve Bank of India Mumbai anilksharma@rbi.org.in. Agenda. RBI’s Experience in Building a DW Business Intelligence Implementation Usage. RBI's Experience in Building DW. Objectives of RBI’s DW.

donaldclark
Download Presentation

Anil Kumar Sharma Director Reserve Bank of India Mumbai anilksharma@rbi

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Regulators Experience in DW and BI Implementation and Usage Anil Kumar Sharma Director Reserve Bank of India Mumbai anilksharma@rbi.org.in

  2. Agenda • RBI’s Experience in Building a DW • Business Intelligence Implementation • Usage

  3. RBI's Experience in Building DW

  4. Objectives of RBI’s DW • Web enabled Decision Support System • To develop an integrated repository of current and historical data • Workflow automation • Business Intelligence tools

  5. The Approach • Prepared an Approach Paper • Subject Orientation • Survey of Users • Validation of Subjects • Discussing with Users • Seminar with Experts • Followed an enterprise wide architectural approach with the philosophy – design the big picture but build small and iteratively

  6. Design Process for the Data Warehouse Stage 1 Analysis Survey of existing data processing systems Survey of Use of Data • Input form, data definition, structure, contents, source, frequency, integrity norms, storage platform etc. • Regular requirements, frequency and nature of ad-hoc queries, time horizon and scope of queries across data systems Stage 2 Design Architecture of CDBMS Hardware and Software Specification • Data model,subjects, facts, dimensions, duration,granularity, schema design, relationships. • Architecture, hardware platform, OS, DBMS, metadata, OLAP tools, security issues. Stage 3 Implementation Data Cleaning and Loading Software Specification • Cleaning, encoding, conversion, structuring summarisation, movement to common platform • Data extraction tools, metadata maintenance, data access control, graphical user interface for the CDBMS

  7. Subject Areas- Approach for Identification • Complex and Iterative procedure • A mix of top-down and bottom up approach • Top-down approach • Goal of the organization identified • Departments are mapped to individual goals • Each goal is taken as a possible subject area • Bottom up approach • Identify Business Process Analyses during the requirement survey • Cluster BPAs based on their homogeneity in terms business focus, analytical perspective and information requirement • Identify subject areas based on such clusters of BPAs.

  8. Inter- Dependence between Subject Areas

  9. Firewall Users Source Systems RDBMS RDBMS Star Schema S1 Web Server Enterprise Data Warehouse S2 Central Repository Data Staging Area LDAP S3 Transformation /Integration by ETL Tool Metadata Repository Architecture of RBI’s DW

  10. RBIK1_4 (HP-UX 11i) - RBIK1_2 (HP-UX11i) - RBIK2_2 (HP-UX11i) - Cluster 4 CPU, 4 GB RAM 2 CPU, 4 GB RAM 2 CPU, 4 GB RAM Source Data Oracle 9.2.0.4 + RAC Central Repository Informatica PowerCenter Enterprise Data Warehouse 7.1.1 Oracle 9.2 Client Informatica Repository Feedback Application LDAP Repository BO-Repository Apache Apache BO 6.5.2 & WebI 6.5 BO 6.5.2 & WebI 6.5 5.1.18 5.1.1.8 Apache 2.2 CDBMSDNS (W2K) Apache 2.2 - DBIE 1 CPU, 2.25 GB RAM Tomcat 5.5 Tomcat 5.5 Application DNS Server SunOne LDAP 5.2 SunOne LDAP 5.2 CDBMSI (W2K) Oracle 4 CPU, 4 GB RAM Oracle 9.2 Client 9.2 Client CDBMSW1 (HP - UX 11i) CDBMSW2 (HP- UX 11i - Internet Portal Server 2 CPU, 4 GB RAM 2 CPU, 4 GB RAM CISCO Firewall Client PC MS Internet Explorer (Browser) (Windows based) Front End S/W LDAP ETL Tool Oracle Client Source Data in Common Storage Legend: Authentication Path Repository Access Data Flow Web Server

  11. DW - Information Flow • Extraction from source systems to staging area as per extract specifications– Pull or Push • Historical Data • Incremental Data • Transformation and Cleansing • Populating CR as per extract to CR mapping specifications • CR to EDW • Data and analysis from EDW and Datamarts to users’ desktop through web server & firewall

  12. Data Volumes Present CDBMS Oracle size are as follows: Development Oracle DB = 196 GB Production Oracle DB = 192 GB

  13. Data flows • Apart from local LAN networks, INFINET is used as communication backbone for EDW and the same is used for transferring data from source systems to Staging area as well as for the users to access the data in the warehouse. • Data frequency include daily, weekly, fortnightly, monthly, quarterly, and annual depending on data series. • Extract programs have been written to extract the data from source systems and the data is received as e-mail attachment. • Data flow automation has been carried out using Informatica, ETL tool, for extracting and loading data from some of the RDBMS based source systems.

  14. Connectivity to Other platform • Data available in DW can be easily exported to MS-EXCEL spreadsheets which can then be used up by the analyst for time series analysis using various statistical analysis tools.

  15. Challenges in Implementation of DW • Users Acceptance • Inertia • Training the Users

  16. Challenges in Implementation of DW • Ever Evolving • Costly • Needs Continuous support from Top Management

  17. Developments in RBI’s DW • Internet Deployment of RBI’s DW • Become International • Become a Gateway of exchanging data with International Agencies

  18. Internet Deployment of RBI’s DW • To facilitate access of RBI’s DW to public • Publishable part RBI’s DW released to public w.e.f. November 1, 2004 • Called “Database on IndianEconomy”

  19. DBIE – The Organization • The data in DBIE is organized as per subject area and frequency. • The subject areas as on today are - Financial Sector, Real Sector, Financial Markets, External Sector, Public Finance and Corporate Finance • The frequencies are – Daily, Weekly, Fortnightly, Monthly, Quarterly and Annual

  20. DBIE – The Interface • DBIE can be accessed in 3 different ways -Report, Data Query and Data Definitions • Report and Data Query can be viewed through subject or through frequency • Data can be reached with the help of data definitions, called meta data • 2-way navigation between data and meta data is possible • Selected data can be downloaded

  21. DBIE – The Scope • There are 182 readymade canned reports • There are 53 Data query (by frequency) reports • There are 72 Data query (by subject) reports

  22. DBIE – The Coverage

  23. DBIE – The Path • Log on to RBI website rbi.org.in • Select the option Database • You can view a selected list of databases and also a hyperlink with label as Database on Indian Economy • Click on the hyperlink to go to DBIE

  24. DBIE – The Endeavor • To provide user-friendly interface • To maintain the data up to date • To ensure data quality to the extent possible • Quality improves as there are more users and more feedback • DBIE is getting more than 7000 hits a month

  25. Users Oracle 9iAS Secured Web Server IDRBT, Hyderabad CDBMS Internet RBI public website Web Server Servlets J2EE BO WebI Server Users ORACLE Firewall Technical Architecture of DBIE Secured Website IDRBT Hyderabad DW

  26. RBI’s DW become International DW • RBI joins BIS Databank w.e.f. February 2006 • Data exchanged between RBI and BIS Databank through its DW • 59 Data series are supplied to BIS Data Bank at present • More than 70 countries data now available to RBI’s Researchers for cross-country analysis

  27. BI Implementation

  28. Approach for BI Implementation • Best of Breed Approach • Integration/Compatibility Issues • End-to-End Solution • Not fully Matured

  29. Approach for BI Implementation • Tool-Based DW • High Capital Investment • Low Maintenance • Customised DW • Low Capital Investment • High Maintenance

  30. Approach for BI Implementation • Multiple Vendor • Not an issue in End-to-End solution of DW • Integration issue become a problem in Best of Breed • Single Vendor • Must for Best of Breed Implementation • Integration issues taken care off by System Integrator

  31. Database Maintenance Issues • Single Database for both OLAP & MOLAP • Separate Database for OLAP & MOLAP

  32. Usage of RBI's DW Internal External

  33. Internal Usage • Users in RBI are using the data for Forecasting, Inflation Analysis, RBI’s Balance sheet and Profit and loss analysis, Receipt and Payments data in State and Central Government account, Section -42 Provisional Data etc. • Cross Country Analysis • Sharing data across Departments

  34. External Usage • Dissemination of Data to Public through DBIE • Dissemination of Metadata • More than 1,90,000 users have accessed DBIE site

  35. Thank You Database on Indian Economy Link www.rbi.org.in RBI Website: Database on Indian Economy

More Related