Transcript BankSys - Natural Group

End-to-End Solution for MIS/BI & Automated Data
Flow to RBI
BI for Banks
MIS, BI & Similar Projects done by the same team
Client
Bank of India
Project
Comprehensive closing returns
The Himachal Pradesh State
Cooperative Bank Ltd
MIS Implementation and Support
Raichur DCC Bank
MIS Implementation and Support
Corporation Bank
ADF and XBRL
Project Objectives
To create Central data repository (CDR) adhering to the framework outlined by RBI for automated
data flow of returns to RBI.
The CDR should be populated with clean and valid data and should synchronize financial and nonfinancial data from various source systems of the bank.
Eliminate multiple data version by creating single version of data at any point of time
Provide integrated system with existing Source systems (CBS, Treasury, CIS, HRM, LAPS, etc) and
other applications.
The CDR enable the bank to meet requirements for:
• Decision support system
• Internal MIS and analytical reports, charts, graphs, etc.
• Reporting to external agencies like Ministry of Commerce, SEBI and other regulators
Returns to be submitted in XBRL formats as per taxonomy published by RBI
To have Data and Systems readiness for any future Data-Warehousing Project
How BI for Banks ADF meets Bank’s Objectives
1
1

This solution is structured in four layers as outlined in the RBI
framework to meet all the activities and tasks prescribed for
implementing ADF system. It has ready data models key Features being
the extensible Data Model, System for Governance, External Data
Capture functionality

The solution has data validating algorithms built in ETL and
gives reports related to quality of data.
The solution can be easily integrated with data quality tools
and provides for strong data governance.
Creating CDR
2
OBJECTIVES
2

Data quality
3
4
Data integration from
various source
systems deployed in
the bank.
3


4
Reporting,
dashboards and
conversion to XBRL
format.


It has ETL routines and mapping from source systems like
Finacle, Treasury, Central Accounts department, etc.
Data management platform consists of rich set of
transformation for a variety of task, Integration Analytics and
Data Quality Transformation
We have created 400+ reports in a public sector bank. Hence
the data mapping to the reports are already available.
The reporting interface component of the proposed solution
allows to create reports and dashboard with drag and drop
facility with a strong metadata management component.
Features of the Solution
Software is web based, menu driven and user friendly.
Access is through user login.
User defined access control available. The access can be configured on need to
know basis.
Audit trails provided for each login, logout, and Addition/deletion/modification by
individual users.
Uploading of data in electronic form as well as data entry mode available.
Provision of Data Governance by using feature such as Maker / Checker
.
Software compiles statements of sundry deposits, sundry credits, and suspense
debits.
Is able to configure the amount in Thousands, Lakhs, and Crores
Features of the Solution
Supports multiple currencies.
Compiles statements relating to Asset Classification, Allocation of Advances &
Unrealized Interest.
Has provisions to accommodate increase in no. of branches ,zones etc and also
various other fields which may come in future.
Uploading of data any number of times permitted at various stages like pre-audit,
post audit and post MOC.
Is able to configure the amount in Thousands, Lakhs, and Crores
Scalable, Extensible data model cutting across various Banking
domain
Logical & Functional Architecture
Data Management
CBS & other source systems to Stage Database
• All the Required data from CBS and other systems (Source tables) Extracted to the
Stage Database. Configurable frequency to ensure returns periodicity.
Stage Database to CDR
• Other set of ETL packages use this Staged Database for transformation, mapping and
cleaning various data elements and loading into CDR from where the reports &
returns are generated.
Legacy system data
• In case any data required in the CDR is existing in any legacy system which does not
have RDBMS then the data that is collected in Flat files and transfer to CDR
database.
Gap data
• Data not available in any system is fetched through data entry programs. Which
provides for maker checker facility.
Data Integration
Data Extraction from Disparate Operational Source Data
• Data Adaptors
• Custom Data Maps and Transformation Rules
• Sources – Data bases, Excel, Flat Files, etc.
Consolidation of Data
• Data Validation
• Data Standardization
• Generation of Data Sets for Analysis & Predictive Modeling
Historical Data Population through ETL
Auto-scheduled ETL
Audit Trails
Data Cleansing
Data Profiling – to detect defect levels in variable values
Deployment of Data Profiling functions for incremental data
Development of Business Rules for transformation / standardization
Deployment of Business Rules in ETL
Customer ID creation
Standardization of categorical variable values
Conversion (Pin Code to City Name)
Missing value population with Business Rules
Creation of Bins for functions like frequency distribution (e.g. Age)
Data Quality
Profiling
Develop a complete assessment of the scope and nature of data quality issues in CBS
and other source databases along with existing MIS database of the Bank.
Create an inventory of data assets.
Inspect bank’s CBS and other Source data bases for errors, inconsistencies,
redundancies and incomplete information in the data.
Build the foundation for future data management initiatives to provide clean, consistent,
effective and efficient data.
Quality
Plan and prioritize data correction initiatives.
Identify and resolve problematic data.
Standardize, normalize and transform data before loading into bank’s CDR.
Validate and improve the overall accuracy of data.
Reporting and Alerting
Empower users
• Power View
• Highly visual design
experience
• Rich metadata-driven
interactivity
• Presentation-ready
at all times
Increase Proactive
Intelligence
• End User Alerting
Increase efficiency
• Enabled as Shared
• Defined from within
operational or ad-hoc
reports
Service
• Built-in scale-out for
RS Service Apps
• Intuitive Alert rules
• Cross-farm reporting
• Self-managed Alerts
• Integrated backup &
• XLS/Word 2007/2010
Managed Self Service BI
–
12
recovery, ULS
logging, PowerShell
etc.
Corporate BI
Proactive Intelligence
Self Services Alerting capability makes it trivial for any end user while viewing a
report to define rules and thresholds on data and be alerted when they are
reached. This transforms a traditional pull mode activity to a push mode activity
greatly increasing Information Workers productivity.
What XBRL Requires…
Standardized data
Clean and validated data
Timely data
Capturing the granular data
Removing the redundancy in data collected and stored
Automated data processing
RBI’s XBRL Initiative
Key driver for XBRL adoption in India
Member of XBRL India jurisdiction
Started XBRLisation with RCA II in 2008
Migrate to XBRL solution for all returns in phased manner
XBRL via ADF
Returns currently covered
• Form A, GPB, Financial Reporting
• Form VIII, Form X
Solution Overview:
MIS/BI & Automated Data Flow
Infrastructure Deployment
Hardware Architecture
17
Brief on Security Architecture

The application has a three tier architecture. (Web/App/DB)

Each of the layer Web/App/DB has been divided into zones separated by firewall.

The web layer is in DMZ which will be first layer for all users access.

The App/DB has been put into the MZ separately.

The application level security is through user access levels which is configurable as
per the banks ISSP.

Security features of application:
•
Automatic logout of idle user after specified time.
•
Minimum password length of 8 characters available.
•
User lock after predefined unsuccessful login attempts.
•
Force password change after predefined periods.
•
System does not accept user id as password.
•
Password is not same as old password.
18
Some of the key reports generated
#
Name of the Output form
1
Form A Special
2
Form B
3
Form B Special
4
Form VIII
5
Form IX
6
Form IX Special
7
Press Communique data
8
Statistical Tables relating to Banks in (Table No. 34)
9
Working Group on Monetary Aggregates (WGMA)
10
Advances to Priority Sector by all SCBs (Final)
11
Advances to Priority Sector by all SCBs (Adhoc -Half yearly)
12
Special Return I (As on Last Reporting Friday of June)
13
Special Return II (As on Last Reporting Friday of June)
14
Special Return III
15
Advance under DRI Scheme
16
Credit Facility to SC/ST
17
Credit Assistance to Minorities
19
Case Study: Himachal State Cooperative Bank

Some of the Reports available
• Weekly Flash Data Report
• BSR Statements.
• Industry wise advances.
• Sector wise advances.
• Facility wise advances.
• Position of Priority Sector Advances and Disbursement.

100+ reports are deployed which cover reports of different departments like Credit, Risk
Management, Priority Sector Lending, Statistics, Retail Banking and International
Banking Division etc.

Dashboards are made available

Statutory and regulatory reports as per RBI format are generated.
Case Study: ADF Solution

203 ADF Reports Available for Reporting to RBI on defined frequency

Predefined templates available for data extraction.

Advances & Reminder Mail Alerts for Data upload, Report Due Data, Report Approval,
Report Submission.

XBRL Report Generation Integrated available for RBI Reports.

User Management available to define access rights to user as designation and
department integrated with bank employee data.

Report Scheduling and Auto Loading provided for report generation as on due date or
on receipt of data.

Dashboards are made available
Names of some Reports Rendered through ADF
Financial inclusion - number of no frill accounts opened
Form – VIII (SLR)
State-wise ATMs data
Area-wise ATMs data
Cards data
Pre-paid cards
Vostro list
List of offices/branches maintaining (Rupee and ACU Dollar) accounts of nonresident banks and list of Vostro accounts
Credit flow to micro, small and medium enterprise
Statement on the position of balancing of books
Form-A Provisional (Sec.42)
Form-A Final (Sec.42)
Form-X – statement of assets and liabilities
BSR1, BSR 2, BSR 4 & BSR 7
Opening / closing of branches
Sample Reports
Samples – What-If Analysis
Sample – Dashboards
CASA DASHBOARD
Employee Data – Creation / Edit / Upload
Data Upload – Screens with Maker – Checker Concept
Data Upload – Screens with Maker – Checker Concept
Data Upload – Screens with Maker – Checker Concept
Email Alerts – Data Upload / Reports
RBI Reports
RBI Reports – Version Wise Report Generation
RBI Reports – Report Details if Data Pending for Date
RBI Reports - Report Generation
Solution Overview:
MIS/BI & Automated Data Flow
Proposed Implementation and Methodology
Proposition to Customer
Accelerated
Roll-out
Thoughtful
Solution Design
Robust
Governance
• Requirement validation as
applicable
• Minimum Customization
• Parallel builds
synchronizing in to single
Testing phase
• Core Key team members
from domain and
technology are ready to
be deployed
• Governance Layer can be
leveraged
• Focus on current
application needs
• Alignment to Enterprise
standards of SBI as
applicable\required.
• Design and development
follow standard SDLC
processes .
 Bank/ NSCS key stakeholder
participation in project
governance
 Proven BI Governance
mechanism at all levels –
Country, Region, Group
Proven
Relationships &
Techniques
 Delivery excellence across
multiple projects over
many years
 Strong relationship with
SAS as a partner
 Real-time synchronization
between Onsite-Offsite
teams
Commitment to Bank for project Success
Successful Solution Implementation
Critical Success Factors which we will deliver against
Solution fit to
requirements
Conforms to existing
Performance measures
In-time Remediation
38
No cost & Schedule spillover
Objective
Planning
Initiation / Deliverables
Phases
Manage
Build Execute
Go-Live/Operate
Operate
Transfer
• Due Diligence
• Risk management
• Expectation
management
• Ensure governance
• Scope, issue, resource
management
• Design , Install ,
customize , integrate and
deploy Solution
• Provide operational
support
• Optimize
• Solution is live and
support model is in
place
• Project kick-off
• Establish PMO
• Project planning and
tracking
• Budget tracking
• Requirement gathering
• Infrastructure design
• UAT,PDC,HA,DR
• Install Solution
• Parameterization and
customization
• SIT and UAT
• Pilot Rollout, Parallel run
• Initial Training
• Data migration, QA, UAT
• Production
Migration/cut-over
• Phased Rollouts: web
based, thin clients,
messing hub integration
• Define SOP for
Integrations, On
boarding
• Training
• Verification and
signoff.
• SRS, traceability matrix
• Process Documents
• High /Low level design
39
• Infrastructure
plan
• Customized Solution
• QA Results
• QA,UAT, production
deployments
• Training schedule
• User manuals
• Pilot/ HARD Launch
Activities
• GAP / risk /
dependency
identification
Deliverables
Transfer
Initiation
• Verification of
scope/requirements
• Monthly steering
Committee meetings
• Weekly check point
meeting
• Scope management
• Verification of existing
solution
stack/environment
• Planning: QA,
Integration, migration
,Rollout
• Project charter/Plan
• Roles Responsibilities
matrix
• Frozen Project scope
• Risk management
plan
• Project calendar
• Resource calendar
• Gap analysis
• Updated project plan
• Status reports
• Issue tracking status
• Budgets
• Change management Log
• Rollout plan, release
management
• QA plan
• Setup Support:
Helpdesk, L2, L3
• Application
monitoring
• Build knowledge
repository
• Tier 3 support
• Problem/issue
management
• Root cause analysis
• Change management
• Production updates if
any
• Lights are on, SLA
tracking
• Admin/user guide
• On boarding SOP
• Change controls if any
• System
documentation
• AMC contract
• Help desk
• Application
Support
Key Success factors
Collaborative approach and transparency
Strong
Governance
Strong
Program/Project
management
Proactive Risk
identification
and mitigation
Mature process framework based on CMMi 5 and ITIL…
Roles and Responsibilities - Representative
Project Phases
NSCSPL
Bank
Analysis Of Requirements
a) Capture Business Requirements
b) Source System Study
c) Gap Data analysis
a) Provide Business Requirements
b) Access to data sources
c) Sign-off the BRD
Design Phase
a)
b)
c)
d)
a) Data in structured format
b) Sign off the design document
Construct Phase
a) Provides gap data entry programs
b) Configure ETL
c) Configure rules engine for DQ and
transformations
d) Build Reports
e) Configure alerts and authorizations levels
Internal Testing
a) Report wise testing
b) End to end testing
UAT
a)
b)
c)
d)
Trainings and Go-live
a) Impart training to core team
b) Training to users and other stakeholders
c) Port the solution on production
environment
Configure Data Model
Configure Solution
Design ETL & Business Rules
Design Gap data screens
Initial training to testing team
Replicate Test cases
Record Issues
Address Issues
a)
b)
c)
d)
Access to Source System for integration
Ensure Gap data is entered and authorized
ISSP guidelines for configurations of user levels
Provide hierarchy for sending alerts
a)
b)
c)
d)
Constitute test team
Provide Test cases
Conduct UAT and notify issues
Sign off UAT
a)
b)
c)
d)
Nominate personnel for trainings
Nominate the pilot branches/departments
Sign offs on training
Go-live sign off
Thank You!
Q&A