J.9_Attachment_09.pdf

Business Application Solutions (BAS) Project Data Modeling and Data Definition Approach

BUSINESS APPLICATION SOLUTIONS (BAS)

PROJECT

DATA MODELING AND DATA DEFINITION APPROACH

May 2019

Version 0.5

Department of Commerce (DoC)

Office of the CFO/ASA

CONTENTS

Contents

Table of Figures

1 Functional Approach Overview

1.1 Background

1.2 Approach Overview

1.2.1 Goals

1.2.2 Strategy

1.2.3 Outcomes and Deliverables

1.2.4 Summary

1.3 Preparation Steps

1.4 Collaboration and Functional Approach

1.4.1 Working Session Preparations

1.4.2 Collaborate with The Vendor

1.4.3 Working Sessions with Bureau Representatives

2 Technical and Architecture Approach Overview

2.1 Approach Overview

2.2 Data Architecture Framework

2.3 Definition of the Canonical Data Model

2.4 Technical Data Modeling and Mapping Approach

2.4.1 Analysis

2.4.2 Create FMS BAS Canonical Data Model (BAS CADM)

2.4.3 Create Enterprise Data Warehouse Conceptual Data Model (EDW CODM)

2.4.4 Align BAS and EDW Data Models

2.4.5 Perform Data Mapping – Data Sources to EDW

2.5 Technical Data Definition Approach

2.5.1 Analysis

2.5.2 Create data standards based on analysis results

2.5.3 Enterprise Data Warehouse (EDW) Dimension Values

3 Assumptions

TABLE OF FIGURES

Figure 1. Data Modeling and Definitions Approach Overview

Figure 3: Hub and Spoke vs. Point to Point Data Mappings

Figure 5: Sample Canonical Model for Vendor

1 FUNCTIONAL APPROACH OVERVIEW

1.1 BACKGROUND

The Data Modeling and Data Definition activities at the Department of Commerce eventually resulted in an Enterprise Data Warehouse Architecture Diagram, full Data Dictionary, and Canonical Data Model describing the overall data standardization and data values at the Department of Commerce. This work was completed in 2015 and will serve as the foundation on which the BAS solution Enterprise Data Warehouse will be built. This document is for historical purposes only as it describes activities that were already completed.

Upon contract award, the Department of Commerce will provide the outcomes and deliverables from these data modeling activities to the chosen BAS vendor.

1.2 APPROACH OVERVIEW

1.2.1 GOALS

The goals for the data modeling and definition effort were to provide a canonical data model, a set of standard data definitions and values, and the DOC BAS data architecture for Office of Financial Management (OFM) that would:

1) Support the business process for financial management;

2) Support the financial (both operational and managerial) reporting process; and,

3) Support managerial, consolidated, and enterprise reporting and analytics (both current and future requirements). This includes reporting on concepts beyond BAS such as operational performance metrics, Human Resources, etc.

1.2.2 STRATEGY

In creating the Data Model as well as defining the Data Standards, the BAS Team referenced and leveraged the following resources to achieve our goals.

1) Industry standards – Leveraged Federal Financial Management Models and Standards, leveraged Federal Enterprise Architecture frameworks.

2) Collaboration with the bureaus – Worked with the core bureaus at DOC, the vendor data team, and other key entities to define the data standards.

a) Leveraged bureau resources that were assigned to the BAS Team on temporary detail to gain in depth knowledge and to prepare for working session meetings.

b) Facilitated working session meetings with the Bureau Representatives and the vendor team for knowledge sharing, requirements gathering, and solution brainstorming.

c) Created data models and standards in collaboration with the assigned resources, based on information gathered during the working sessions.

d) Conducted review and validation sessions with the bureau representatives and the vendor after artifacts had been completed.

e) Revised artifacts and reviewed with the bureau representatives and the vendor as the project progresses.

3) DOC Historical Artifacts – Leveraged any existing DOC Models and Standards, both at the department level and throughout the different bureaus. Identified new DOC Models and Standards by looking at:

a) The inventory of the current state system data models, dictionaries, and definitions documentation.

b) The data definition for the future state BAS solution

c) The fill gaps between the current solutions and the desired future state capabilities.

4) DOC Implementation – Throughout implementation, the team will align the Modeling and Standards definition process with the BAS Implementation process, and identified standards, flushed out during:

a) Requirements Definition Process.

b) Creation of future BAS system solutions processes

1.2.3 OUTCOMES AND DELIVERABLES

1. The BAS Team developed the formal BAS Canonical Data Model (CADM) for the Department of Commerce Office of Financial Management. The final BAS CADM consistently described data architectures related to the BAS Implementation and uniformly described the BAS data artifacts so that these data models and data definitions can be shared across all bureaus. The BAS CADM will be referenced by the other tasks of the implementation phase, such as data conversion and interface definition to facilitate consistent data definition throughout the implementation phase. The BAS CADM contains information on:

Data Description ‐ meaning and the structure of the data that is needed;

Data Context ‐ the context of the data needed to meet its shared mission business needs;

and, Data Sharing ‐ common capabilities to enable information to be accessed and exchanged1

The BAS CADM also contained a list of data definitions and standards for critical data concepts, such as the Accounting Classification Code Structure (ACCS) / Common Government‐wide Accounting Classification (CGAC) and other common data dimensions. These standards and definitions will be housed in a Metadata Repository for ease of access, maintenance, and publication for the BAS Team and consuming entities of this information. Once the data model and data standards were defined, the concepts were be mapped from source system to the canonical model to the target system.

2. In addition to the BAS CADM, the BAS Team also delivered a document identifying the list of data concepts beyond those modeled in outcome 1 above that are required for reporting or analytical purposes.

3. The CADM and the reporting data concepts was then be used to create an Enterprise Data Warehouse (EDW) Conceptual Data Model (CODM) for the DOC data warehouse. All data standards and mappings defined in the CADM were extended to include data warehouse concepts that are beyond the BAS Implementation effort where necessary. The EDW CODM is the data foundation for

1 This task is interdependent with the Interfaces requirements definition effort.

the data warehouse and should be tightly aligned with the functional requirements for the EDW.

This deliverable also contains a high‐level mapping between the source systems and the EDW

CODM.

1.2.4 SUMMARY

The following diagram summarizes the data modeling approach. The next sections describe each step in the approach in more detail.

FIGURE 1. DATA MODELING AND DEFINITIONS APPROACH OVERVIEW

1.3 PREPARATION STEPS

The following steps were accomplished in preparation for starting the Data Modeling and Data Definition task:

1) Inventory of Current Systems and Operational Reports

a) Acquired access2 to the databases for the Current Systems

b) Acquired data models of the databases for the Current Systems

c) Acquired commonly used reports for each of the Current Systems

2) Inventory of Current Data Warehouse Solutions and Managerial/Enterprise Reports

a) Acquired access to the databases for the Data Warehouses

b) Acquired data models for the Data Warehouses

c) Acquired commonly used reports for each of the Data Warehouses

2 All system access requests are “Read‐only”

d) Acquired existing data standards at DOC (standard business object classes, etc.)

3) Identified inventory of any existing Financial Management related Data Reference Models at DOC

4) Assessed the future to be solution Data Structures and Reporting Capabilities at a high level (data models and system reporting capabilities)

1.4 COLLABORATION AND FUNCTIONAL APPROACH

1.4.1 WORKING SESSION PREPARATIONS

The BAS Team leveraged the bureau representatives who are assigned temporary detail to the Office of Financial Management (OFM) office and worked with them to:

Gain additional background knowledge in preparation for conducting the working sessions

Initiate collaboration with the vendor implementation team to be involved with implementation planning on the vendor side

Design, create, and validate meeting agendas and artifacts for the working sessions with the bureau representatives

In order to prepare for the working sessions with the bureau representatives, the BAS Team worked with the bureau assignees to achieve the following:

1) Reviewed the data modeling approach and schedule

2) Held knowledge sharing sessions with bureau assignees

a) Discussed current system implementation

i) Commerce Business System (CBS)

ii) C. Suite

iii) Sunflower, etc.

b) Discussed Reporting Requirements

i) Standard application reports

ii) Custom/Ad hoc reports

c) Discussed current data warehouse implementation

3) Held communications and meetings with the vendor to gain knowledge on their current data structures

a) ACCS is the first priority

b) Other key reference data concepts

4) Held planning sessions with bureau assignees to prepare for the working sessions with bureau representatives

a) Identified working session schedules and attendees

b) Prepared and sent out any work ahead items prior to the working sessions

c) Reviewed and refined data modeling working session goals and agendas

d) Reviewed and refined reporting data concepts working session goals and agendas

e) Reviewed data warehouse working session goals and agendas

1.4.2 COLLABORATE WITH THE VENDOR

The BAS Team, including the bureau assignees, collaborated with the Vendor Team during the data definition phase to understand how the ERP solution would be configured and implemented and how the data was being defined at the vendor. Collaboration activities with the vendor included:

1) Discovered how the ACCS s defined and what the components represent conceptually.

2) Discovered how other key reference data is configured.

3) Collaborated to determine to how best map DOC data definitions to the vendor configuration. This occurred throughout the entire Data Model, Standards, and Mapping tasks.

1.4.3 WORKING SESSIONS WITH BUREAU REPRESENTATIVES

After the appropriate preparation sessions with the bureau assignees, the BAS Team held working sessions with the bureau representatives across the different subject areas to discuss the data requirements for each subject areas. The working sessions consisted of two series:

1) First Series – Initial discussions

a) Knowledge sharing and validation of data requirements

b) Refinement of bureau data requirements

c) Identified the variances in data requirements and usage between the different bureaus

d) Discussed reporting data concepts and requirements

e) Discussed how data will map from current systems to future solutions

2) Second Series – Validation and Reporting / EDW Discussions

a) Validated and refined draft data model, standards, and data mappings

b) Discussed enterprise data warehousing requirements

Working sessions will held to cover each of the following subject areas separately in the below suggested groupings:

1) Core Financials

a) General Ledger (GL), Fund Balance

b) Budget

c) Accounts Receivable (AR), Cost (includes reimbursable agreements)

d) Accounts Payable (AP)

2) Acquisitions

3) Personal Property

4) Real Property

5) Fleet

6) Reporting and Data Warehouse

The BAS Team and the bureau assignees worked together to drive the direction of the working sessions and to resolve any requirement conflicts and differences between the bureaus.

2 TECHNICAL AND ARCHITECTURE APPROACH OVERVIEW

2.1 APPROACH OVERVIEW

In order to create the data model and standards at DOC, the BAS Team utilized federal standards, frameworks, and industry best practices as much as possible while still taking into account DOC’s specific needs. The goal was to balance and reuse definitions and frameworks without compromising the custom needs of DOC whenever possible.

2.2 DATA ARCHITECTURE FRAMEWORK

The BAS Team evaluated how to leverage the DATA REFERENCE MODEL (DRM) defined in the FEDERAL ENTERPRISE ARCHITECTURE (FEA) as a framework in performing the data modeling and data definition task for the BAS implementation planning phase. Usage of the Federal Enterprise Architecture Data Reference Model is discussed in OMB M06‐02. More information on the Data Reference Model and the Federal Enterprise Architecture can be found at the following locations:

http://www.whitehouse.gov/sites/default/files/omb/assets/egov_docs/DRM_2_0_Final.pdf http://www.whitehouse.gov/omb/e‐gov/fea/

2.3 DEFINITION OF THE CANONICAL DATA MODEL

The recommended approach for defining the standard data model for the Department of Commerce was to follow the best practice of defining a canonical model. A canonical data model is a model in the simplest form possible based on a standard, common view within a given context. The goal of the Canonical Model was to provide a dictionary of reusable common objects and definitions at an enterprise or business‐domain level that is not tied to or dependent upon a specific technical solution.

The advantages of having a canonical data model include:

Allows developers and business users to define data concepts in terms of an enterprise’s business domain rather than a specific package implementation.

Can be used to help translate and map data between two different implementations.

Provides a data baseline for an enterprise to use for integration specifications, data conversion, or other types of data interchange.

Avoids point to point data mappings between applications which lead to exponential growth in maintaining and administrating data exchanges between applications as data interchange requirements increase or data definitions are updated.

FIGURE 2: HUB AND SPOKE VS. POINT TO POINT DATA MAPPINGS

Defining a canonical model required more work initially, but this work was a valuable investment, as it will lessen future implementation complexity, reduce maintenance efforts, and improve solution quality overall. The canonical data model will serve as the baseline for every data interchange or conversion effort so that the addition of an application to the data interchange process affects only one mapping instead of potentially N number of mappings in the traditional point‐to‐point model.

With respect to the planning phase of the BAS implementation, the goal was to create a data structure and definition that will be common across all three bureaus. This canonical structure will then be used to map the data definitions from the three bureaus’ instances to the vendor solution.

This DOC BAS canonical model is now the official data definition at DOC and will be used in a variety of situations for the BAS modernization effort:

1) To standardize data structures across the different bureaus.

2) To map data between source and target systems for data conversion, such as from the different instances of CBS to the target vendor solution for example.

3) As the baseline for interface definition, especially from the perspective of BAS solutions.

4) To facilitate and assist in technical change management discussions and identifying the differences between the current state and future state solutions.

2.3.1.1 EXAMPLE

Given the concept of a vendor, the BAS Team defined a canonical model of a vendor record that was based on how BAS interacts with, manages, and processes vendors.

FIGURE 3: SAMPLE CANONICAL MODEL FOR VENDOR

In this example, the vendor is defined as having:

The following identifying attributes o Vendor Name o DUNS Number o Tax ID Number o Employer ID Number

Zero, one, or more than one address associated to each vendor of various types

Only zero or one Point of Contact name associated to each vendor

One optional field of free form Notes associated to each vendor

Other classification attributes of address type, status code, and payment method are associated to each vendor. These attributes are controlled list of values.

This definition was not based on any instance of CBS or commercial financial system, but rather on how DOC utilized this data. This model can now be mapped to the actual technical solutions of an implementation.

2.4 TECHNICAL DATA MODELING AND MAPPING APPROACH

The BAS Team executed the following technical approach to develop both the BAS Canonical Data Model (BAS CADM) and the Enterprise Data Warehouse Conceptual Data Model (EDW CODM).

2.4.1 ANALYSIS

Obtained background information, functional knowledge, and technical specifications insights required to perform the data modeling process and to understand reporting requirements.

1) Analyzed current state solutions

2) Profiled data to identify core data concepts across three functional areas and across all the affected bureaus

3) Collaborated with bureau reps to gain high level knowledge of current state solutions

4) Reporting Requirements Definition

a) Gathered Operational Reporting Requirements

b) Gathered Enterprise and Analytical Reporting Requirements

c) Gap Analysis – Identified unfulfilled or manual reporting requirements in the current state that should be included in the desired future state

5) Analyzed current state data warehouses and other data source systems beyond BAS solution

6) Profiled data to identify high level core enterprise data concepts for the Data Warehouse3

2.4.2 CREATE FMS BAS CANONICAL DATA MODEL (BAS CADM)

As knowledge was gained from the Analysis task, modeling the CADM commenced.

1) Created high level canonical data model based on industry and DOC standards and analyzed the results

a) Created CADM – high level conceptual data model broken down by Subject Areas

3 Should be done in conjunction with Data Warehouse Requirements Definition effort

i) Core Financials

ii) Acquisition

iii) Property

(1) Personal

(2) Real

(3) Fleet

iv) Common Concepts (Cross Subject Areas)

b) Reviewed with key stakeholders and SMEs and revised BAS CADM, as appropriate Performed

Data Mapping – Source to Canonical to Target

As the CADM was defined, mapped the fields between the current state source solution and the future state target solution.

1) Created mappings for the three instances of CBS to the Canonical Data Model (CADM) for Core Financials Concepts, consisting of a baseline mapping and three delta sections. Created mappings from Canonical data model to the future financial solution.

2) Created mappings for C.Suite to the CADM for Acquisition Concepts and then created the mappings from CADM to the future acquisitions solution.

3) Created mappings for Sunflower to the CADM for Personal Property and then created mappings from CADM to the future property solution.

4) Created mappings for Real Property Management System (RPMS) to the Canonical data model for Real Property. Created mappings from CADM to the future property solution.

5) Created mappings for Sunflower to Canonical data model (if different than Personal mapping) for Fleet Property. Created mappings from CADM to the future property solution.

2.4.3 CREATE ENTERPRISE DATA WAREHOUSE CONCEPTUAL DATA MODEL (EDW CODM)

As canonical data structures were defined as part of the CADM modeling effort, we assessed whether they should be included in the EDW CODM and began the modeling of the CODM.

1) Extracted concepts from CADM to be included in EDW data model.

2) Identified core enterprise data concepts across three functional areas and across all bureaus affected that may not have been identified during the subject areas’ analysis.

3) Expanded the scope of the CODM to include concepts beyond the BAS effort:

a) Grants

b) Travel

c) HR data beyond Payment Files and Employee Files

d) Operational Performance Metrics

e) Hyperion

4) Participated in EDW requirements definition effort

5) Enterprise and Analytical Reporting Requirements were defined

6) Created a Conceptual Data Model for the EDW.

7) Reviewed with key stakeholders and SMEs and revise BAS CADM, as appropriate.

2.4.4 ALIGN BAS AND EDW DATA MODELS

As the modeling progressed, reviewed and aligned the CADM and CODM (as necessary) to make ensure alignment and reflect the same concepts in the same way.

1) Assessed BAS CADM and EDW CODM against data models existing for the Bureaus and current DWs.

2) Mapped and assessed deltas and gaps of the data concepts.

3) Refined and adjusted BAS CADM and EDW CODM as future state implementation effort progressed.

2.4.5 PERFORM DATA MAPPING – DATA SOURCES TO EDW

As the CODM was defined, mapped the fields between the source systems and the CODM.

1) Created mappings for the TBD financial solution to the EDW CODM.

2) Created mappings for the TBD acquisitions solution to the EDW CODM.

3) Created mappings for the TBD personal, fleet, and real property solution to the EDW CODM.

4) Created mappings for the TBD Fleet solution to the EDW CODM.

5) Created mappings for the DOC Grants solution to the EDW CODM.

6) Created mappings for eTravel and other TBD sources of travel data to the EDW CODM.

7) Created mappings for NFC and/or HRConnect to the EDW CODM.

8) Created mappings for Operational Performance Metrics data concepts to the EDW CODM.

9) Created mappings for current Data Warehouse data concepts that had to be converted to the EDW

CODM.

2.5 TECHNICAL DATA DEFINITION APPROACH

The BAS Team executed the following technical approach for defining the data definitions and standard values for DOC.

2.5.1 ANALYSIS

Obtained background information, functional knowledge, and data insights required to drive the data modeling process and to understand reporting requirements.

1) Reviewed the Data Models for all three instances of CBS.

2) Reviewed Current state Financial Reporting for bureaus.

3) Performed data analysis and data profiling of the CBS instances.

4) Collaborated with bureau reps to get high level context of data usage of the current state

5) Identified key reference data and dimensions.

6) Performed data mapping of reference values across three CBS instances.

7) Analyzed Core Financials concepts within Data Warehouse; adjusted standards and mappings as necessary.

2.5.2 CREATE DATA STANDARDS BASED ON ANALYSIS RESULTS

As knowledge was gained from the Analysis task, begin documented standard reference data values within the BAS CADM.

1) Created Data Standards Definitions and referenced data value mappings broken down by Subject Areas

a) Core Financials

i) The ACCS and CGAC were the highest priorities to document and maintain in the standards definitions and mappings

b) Acquisitions

c) Property

(1) Personal

(2) Real

(3) Fleet

d) Common Values and Dimensions (Cross Subject Areas)

2) Created Data Standards Definitions and referenced data value mappings for Subject Areas beyond the BAS effort

a) Grants

b) Travel

c) HR/Payroll

d) Operational Performance Metrics

e) Hyperion

3) Reviewed with key stakeholders and SMEs and revised, if applicable

4) Finalized Data Standards Definitions

2.5.3 ENTERPRISE DATA WAREHOUSE (EDW) DIMENSION VALUES

As standard reference data values were defined for the CADM, assessed whether they should be included in the EDW CODM and defined them for the CODM as well. The standards were uniformly applied for the most part, but differed slightly to accommodate enterprise wide reporting and trend analysis.

1) Extracted concepts from CADM data standards to be included in EDW data standards.

2) Identified additional core enterprise data concepts values across three functional areas and across all affected bureaus that had not been identified during the subject areas analysis.

3) Participated in EDW requirements definition effort.

4) Analyzed and profiled Enterprise and Analytical Reporting Dimensions.

5) Updated Data Standards to reflect EDW requirements/values.

3 ASSUMPTIONS

The approach outlined in this document was created with the understanding that the following assumptions are valid.

There were three main subject areas that were in scope for this phase of the data definition and data reference modeling effort as prioritized by the BAS Team:

Core Financials

Acquisitions

Property o Real o Personal o Fleet

For the data warehouse conceptual data modeling effort, the following subject areas were covered in addition to the ones listed above for the data reference model:

Grants

Travel

HR data beyond Payment Files and Employee Files

Operational Performance Metrics

Current State systems in scope for this effort included:

CBS (all three instances)

C.Suite

Sunflower

RPMS

National Oceanic and Atmospheric Administration (NOAA) Data Warehouse

National Institute of Standards and Technology (NIST) Data Warehouse

Census Data Warehouse

Grants Online, GMIS, and other TBD sources of grants data eTravel and other TBD sources of travel data

NFC

HRConnect

TBD sources for operational performance metrics

Hyperion