Transcript FSA Data Warehouse - Advanced DataTools

NBC Datamart
A Case Study of
the National Business Center
Who is the NBC?
• An agency of the Department of the Interior
• Provides administrative services and systems to
the rest of the federal government
• Selected as one of four e-Payroll providers
• Named as one of five HR Line of Business
Centers of Excellence
• Named as one of four Financial Management
Line of Business Centers of Excellence.
2
NBC – Turning Data into
Information
• Provide all FPPS users with on-line access to
their HR and Payroll data.
• Improve data quality and integrity by
– reducing data entry through auxiliary systems and
interfaces with the authoritative source.
– Integrating related data from multiple sources.
• Improve data security through reapplication of
security from the authoritative source of the
data.
3
NBC Datamart:
4+ years of production
• 1998 - Independent assessment recommended NBC
create a reporting system for the
Federal Personnel/Payroll System (FPPS)
• 1999 - Project formalized as FPPS Datamart
– Team established and trained
– Goals, including initial list of FPPS file to be
included established.
– Technology researched and established
• 2000 – First production environment
• 2001 – New project manager hired
• 2002 – All initial files implemented
• 2002 - Present – Continued growth
4
What was the original Business
Problem?
• FPPS did not provide a modern
reporting environment
• Users did not have access to an
analytical environment
• Users could not export data
• Users could not format reports for
presentation
5
Objectives of the FPPS
Datamart
• Provide a state-of-the-art reporting
environment
• Original file listing
– Combined File (HR and Pay)
– Combined History
– Transaction (SF-50 Actions)
6
Who are the primary Users?
• FPPS Agencies including
–
–
–
–
–
–
–
–
Social Security Administration
Department of the Interior
NASA
Department of Education
National Science Foundation
Department of Transportation
Securities and Exchange Commission
Equal Employment Opportunity Commision
• FPPS Usage expanded due to e-Payroll
initiative
– NBC services 37 agencies
– paying over 300,000 employees
7
NBC Datamart
FPPS
FFS
Agency
Oracle Data Store
•Multiple
Production, Stage,
Test and
Development
databases
Brio/Hyperion
Agency Systems
Bank of
America
Auxiliary
Systems
8
Data Management Architecture
• Flat File Feed
– Extract files loaded automatically
– Good alternative for data not automatically
maintained within source system or for crossing
security lines
• I-Way Gateway
– Real-time read of ADABAS (FPPS environment)
• Treehouse
– Periodical update using ADABAS backup files
• Direct Oracle Client Read
– Used for extracts
9
NBC Datamart – Auxiliary
Systems
• Build upon data in Datamart
• Automate niche business process not serviced
by larger NBC product.
• Reuse NBC Datamart infrastructure.
• Reuse Security of authoritative source.
• Examples
– SEC Position Control System
– NSF Intergovernmental Personnel Act System
10
Customer Service
• Level 0
– Extensive information on Web Page
•
•
•
•
How to get training
Table descriptions
Newsletters
Computed Items
– Web-Based Training
• Level 1 and 2
– Helpdesk
– Periodic DM User Group Meetings
– Site Visits
11
12
13
NBC – Three Years Later
• Scope expanded enough to change name of
product, FPPS Datamart -> NBC Datamart, now
includes, along with FPPS data,
–
–
–
–
–
–
Agency Managed data
Agency Historical HR/Pay data
Casual Hire data
Federal Financial data
Auxiliary System data
DOI Charge Card data
• Usage expanded by 496% of users and 477% of
queries
14
Query Environment
Brio 6.6.4 and HPS8
•Current environment
•Brio 6.6.4
•Three load balanced servers
•Known maintenance requirements
•Future environment
•Hyperion Performance Suite 8
•Currently in pre-production testing
15
Future – HPS8
Issues
• Internal
– New implementation of SSL
– Dual Maintenance of queries while supporting Both 6
&8
– Making time for administrators to learn HPS8
• HPS8
– Publisher role
– Organizational Administrator
16
Known Weaknesses
• Current audience are report writers
– People who have a good idea of the information they
are seeking and how to get it
– Support these folks extremely well
– Problem? These folks do not pay the bills
• No Web Services
– No ability to expose reports as web services
– Problem? SOA is the future architecture within NBC
17
Desired Behavior
• Create environment for Managers
– Looking forward to View Manager and Dashboards
– Need a way to expose macro level data without
undermining security at the micro level
• Web Services
– Need to be able to expose this data and the known
presentation ability through callable web services.
18
The Future is Challenging
• We need to complete move to HPS8
–
–
–
–
Publisher role
Organizational Administrator
View Manager
Dashboards
• We need to figure out how we fit in an SOA
world
19
NBC Datamart Contacts
• www.nbc.gov
• Select Datamart
• Manager – Martin J. Quinlan
– [email protected]
– 303-969-7228
20