PL 008 TRA RAW DATA LAKE.docx

DOCX document 2 MB Posted

Attached to
BABYNET IDEA PART C
Solicitation number
5400018456
Issued by
Richland County, South Carolina

About this file

This is a technical reference architecture document prepared by the South Carolina Department of Health and Human Services (SCDHHS) Enterprise Services that describes the Raw Data Lake (RDL) component of the Medicaid Enterprise System (MES). The document defines the RDL as a NoSQL database solution designed to ingest, store, and manage raw data from multiple disparate sources including member management systems, eligibility systems, claims processing systems, document management systems, and financial systems. The RDL ingests data in any native format—including structured relational database records, semi-structured data such as CSV and XML files, and binary formats like PDFs and images—without requiring data to conform to a predefined schema. The architecture employs commodity cluster computing for massively scalable, low-cost data storage and retention of all raw data in its original form, with metadata attached to track source, timestamp, and processing information for audit and compliance purposes.

The ingestion process follows a standardized workflow consisting of authentication and authorization through enterprise identity management, data access via file transfer or direct database query, data loading through integration hub services (ETL processes or web services), and metadata attachment. The RDL is implemented using MarkLogic as the NoSQL database platform, with file transfer managed by Oracle MFT, ETL processing handled by Oracle Data Integrator, and scheduling managed through Enterprise Scheduling Services (ESS). High availability and disaster recovery are achieved through a shared-nothing architecture with data replication across commodity servers, with service level agreements formalizing recovery time objectives (RTO) and recovery point objectives (RPO). The system includes comprehensive audit controls for all Create, Read, Update, and Delete operations; process logging for compliance documentation; exception handling per enterprise error-handling guidelines; and a monitoring dashboard to track performance metrics and alert operations staff to variances outside established performance baselines.

View the file

Other files

File Type Posted
ATTM 007 CERTIFICATION FOR CONTRACTS GRANTS LOANS AND COOPERATIV.docx DOCX document
ATTM 013 EXPERIENCE TABLE.docx DOCX document
ATTM 010 QUESTIONS TEMPLATE.docx DOCX document
ATTM 006 ORGANIZATIONAL CONFLICT OF INTEREST DISCLOSURE.docx DOCX document
PL 006 TRA MANAGED FILE TRANSFER.docx DOCX document
Award Extension.pdf PDF
PL 002 TRA INTEGRATION HUB.docx DOCX document
ATTM 008 BABYNET PRICING TABLES EXAMPLE.xls XLS spreadsheet
ATTM 009 BABYNET PRICING TABLES BLANK.xls XLS spreadsheet
PL 003 TRA ENTERPRISE SERVICE BUS.docx DOCX document
ATTM 001 IMPORTANT NOTICE - NONRESIDENTS ONLY.docx DOCX document
ATTM 014 LIST OF INCLUDED SYSTEM REPORTS.docx DOCX document
ATTM 004 DISCLOSURE OF OWNERSHIP.docx DOCX document
ATTM 011 CDML.docx DOCX document
PL 004 TRA WEB SERVICES.docx DOCX document
ATTM 002 OFFERORS CHECKLIST.docx DOCX document
ATTM 003 CERTIFICATION REGARDING DEBARMENT SUSPENSION INELIGIBIL.docx DOCX document
PL 007 TRA ENTERPRISE DATA SERVICES.docx DOCX document
PL 011 OandM MANUAL TEMPLATE.pdf PDF
ATTM 012 SERVICE LEVEL AGREEMENTS (SLA).docx DOCX document
Solicitation.pdf PDF
PL 005 TRA EXTRACT TRANSFORM LOAD.docx DOCX document
Award Notice.pdf PDF
Amendment #1.pdf PDF
ATTM 015 SPSAQ (REVISED).docx DOCX document
PL 009 TRA OPERATIONAL DATA STORE.docx DOCX document
PL 001 ELECTRONIC DATA INTERCHANGE (EDI).pdf PDF
Amendment #2.pdf PDF
PL 010 IDEA PART C DATA COLLECTION REGULATIONS.docx DOCX document
ATTM 005 HIPAA BAA.docx DOCX document
ATTM 016 Qestions and Answers.xlsx XLSX spreadsheet
Show all 31

On GovTribe

Work with this file

  • Download the original file
  • Contacts named in this file
  • Similar government files
  • Ask GovTribe AI about this file

Text version

TRA05.02 – Raw data lake (RDL)

TRA05.02 – Raw Data Lake (RDL) Version 0.9 7/10/2017Prepared by:

South Carolina Department of Health and Human Services (SCDHHS) Enterprise Services (ES)

Table of Contents

1. Introduction6
2. Benefits of the RDL to the MES7
3. Architecture to Ingest Data9
4. Data Ingestion11
4.1. Data Ingestion Issues / Challenges11
4.2. Ingestion Process12
4.2.1. Process Steps13
4.2.2. Requirements Common to the RDL Ingestion Process Steps17
4.2.3. Operational Considerations19
4.3. Metadata with Common Information21
5. Use Cases23
5.1. Initial Loads23
5.2. Incremental Loads25
6. Tools and Technologies26
7. Governance27
8. Appendices27
8.1. Acronyms27
8.2. Glossary29
8.3. Referenced documents30

Table of Figures

Figure 1. The raw data lake (RDL) in the MES6
Figure 2. RDL architecture9
Figure 3. Loading data records and documents9
Figure 4. Relational database table stored in RDL in JSON format10
Figure 5. Ingestion process12
Figure 5. RDL authentication architecture14
Figure 7. RDL load process15
Figure 8. Initial load use case24
Figure 9. Incremental load use case25

Table of Tables

Table 1. Benefits of the RDL7
Table 2. Descriptions of ingestion patterns15
Table 3. Ingestion patterns by data source capabilities and payload16
Table 4. Process log data elements17
Table 5. Initial load use case steps24
Table 6. Incremental load use case steps26

Table of Code Examples

Code example 1. Schema Reference21
Code example 2. Metadata format22
Code example 3. Example JSON document with metadata22

Table of Standards TRA05.02-S1. The RDL ingests any available data in its native format.

TRA05.02-S2. The RDL can load the dataset as a whole as well as interpret and load individual records if possible.

TRA05.02-S3. The RDL loads raw data into collections of related content.

TRA05.02-S4. The RDL defines data collections according to its business or technical purpose.

TRA05.02-S5. The RDL retains raw data in its original form and does not make updates to the raw data record content.

TRA05.02-S6. Access to the RDL conforms to the TRA01.02 - Access control and identity management supplement.

TRA05.02-S7. The role of the user defines access to the RDL.

TRA05.02-S8. Data is loaded into the RDL by an integration hub service.

TRA05.02-S9. The RDL includes audit/control records for each operation (Create, Read, Update, and Delete).

TRA05.02-S10. The NoSQL platform handles exceptions per MES error-handling guidelines.

TRA05.02-S11. A high-availability NoSQL platform (with defined SLAs for recovery processing) implements the RDL.

TRA05.02-S12. The NoSQL platform ensures RDL performance within the defined SLA.

TRA05.02-S13. The NoSQL platform offers RDL integration with MES technologies and tools.

TRA05.02-S14. The NoSQL platform provides a tool to monitor the RDL.

SCDHHS expressly restricts the distribution of this document to include only those SCDHHS staff, SCDHHS processing environment contractors, or any entity given explicit access to this document with SCDHHS executive or management approval.

Intended audience The SCDHHS Technical Reference Architecture (TRA) audience includes SCDHHS staff, trading partners, and third-party vendors who will be implementing integrating, managing, or operating the SCDHHS Medicaid Enterprise System (MES). The SCDHHS TRA provides the reference model by which technologies and components of the SCDHHS MES will be measured for development and implementation.

Introduction The Medicaid Enterprise System (MES) allows the South Carolina Department of Health and Human Services (SCDHHS) to establish a single, integrated, view of members, providers, claims, and encounters. The MES derives its data from disparate sources, such as trading partners, other state agencies (e.g., the South Carolina Department of Justice), and systems of record (SOR) including the:

· Member management system

· Eligibility system

· Claims processing system

· Document management system

· Financial system Data flows from the sources, through the MES, and out to the data consumers. Figure 1. The raw data lake (RDL) in the MES illustrates the flow of data through the enterprise data management pipeline (EDMP). The data enters the Enterprise Data Services (EDS) at the raw data lake (RDL) (bolded in the figure).Figure 1. The raw data lake (RDL) in the MES

The RDL:

Ingests any data in any format Captures metadata from the source Permanently stores a copy of all data Interprets data and stores in records if possible; otherwise, stores as loaded Instead of forcing data integration first, as in a traditional data warehouse, the EDS parses (materializes) the data later. That is, the RDL stores the data as ingested, and the operational data store (ODS) contains the materialized data—the specific data views and structures that support the business need for real-time analytics, discovery and ideation, data models, and data marts. The EDS can build a single view of the client over time, adding demographic data, then eligibility data, and then, claims and encounters data.

A NoSQL[footnoteRef:1] database implements the RDL using commodity cluster computing techniques for massively scalable, low-cost data storage. [1: NoSQL is the acronym for Not Only Structured Query Language.]

This supplement describes the RDL and the processes to ingest the data into the RDL. The TRA05–Enterprise Data Services (EDS) Framework and associated supplements describe the data architecture, data modeling, and the EDS processes to transport, parse, cleanse, and prepare the data for analytics. The TRA05.03 Operational Data Store (ODS) supplement describes materializing the data, information use, and sharing. The TRA04 – Integration Hub and associated supplements describe the processes to access the data sources and to transport the data to the RDL, including the enterprise service bus (ESB), web services, managed file transfers (MFT), and extract-transform-load (ETL) processes.

Benefits of the RDL to the MES NoSQL databases handle high data volumes, provide extreme performance, and maintain an integrated view of the information in the database.

Table 1. Benefits of the RDL identifies other key benefits of using the NoSQL RDL in the MES.

Table 1. Benefits of the RDL

Feature
Benefit
Not tied to a single structure
Relational databases, such as the member management database, represent and store data in a structure (tables with columns and rows). The non-relational RDL does not have a fixed schema for the data format, which allows the RDL to load any data in any format, and interpret the data later.
Serves as integration point
The RDL associates related data, and the materialized view integrates related data from the disparate sources. A request for all information on a citizen returns integrated results from all SORs, highlighting discrepancies, such as the different home addresses.
Accepts data in any format
Collects data from disparate sources, without formatting or processing the data to fit into a specified structure, thereby:

· Easing the burden on trading partners.

· Minimizing data integrity risk as the RDL stores data in its entirety and without any changes to its structure.

Single source for all raw MES data and all modifications to that data over time
The RDL:

· Provides audit trail for data sources and updates.

· Supports repeated access to the original data and its modifications.

· Provides input for data scientists who identify data-driven innovations to raise health outcomes and minimize Medicaid costs because Medicaid typically provides care for high-cost, high-utilization populations facing an array of social, economic, and environmental factors that have an outsized impact on the constituents’ health and on public budgets.

Horizontal scalability across commodity technology
By design, the NoSQL database spreads data across an arbitrary number of servers, and automatically balances data and query loads across servers, independent of the composition of the server pool. Therefore, the RDL can easily:

· Scale horizontally, and support data replication across commodity technology (instead of scaling up, requiring specialized servers and software).

· Ensure high availability and disaster recovery.

Timeliness of data
The EDS can stream information directly into the RDL, providing an almost real-time view of the data.

Architecture to Ingest Data The RDL ingests any data in any format, and permanently stores a copy of all data it ingests. The RDL stores the data in collections of related data, adding updates to the data as new records in the appropriate collection. This section describes the RDL architecture as illustrated in Figure 2. RDL architecture.Figure 2. RDL architecture

TRA05.02-S1. The RDL ingests any available data in its native format.

The RDL ingests raw data from multiple sources, each of which may have a different native schema. The RDL ingests any data in any format, including:

Structured data from relational databases (rows and columns) Semi-structured data, such as comma-separated values (CSV), logs, eXtensible Markup Language (XML), and JavaScript Object Notation (JSON) Binary data, such as Portable Document Format (PDF) files, images, audio, and video TRA05.02-S2. The RDL can load the dataset as a whole as well as interpret and load individual records if possible.Figure 3. Loading data records and documents

The integration hub service that transports/loads the data attempts to match the data source/content to an RDL source schema. If the service determines the source schema, the service interprets the data and loads the data to the RDL in JSON records in the appropriate collection. Otherwise, the service loads the data in a document (in the appropriate collection) in as-is format, and attaches the default metadata—the source and the date/time stamp. Figure 3. Loading data records and documents illustrates this concept. For example, the ETL service can interpret the claims records in an 837 and load each claim as a record in the 837 claims collection. The ETL can also load the file as a document in the 837-file collection. Reasons for loading the file as a document include:

· The file layout is not available. Once the layout becomes available, and content mapping completes, the file records may be loaded into the appropriate collection of records.

· Requirement or agreement to load both the file as a document and as interpreted records.

See Section 4.3. Metadata with common information for more information about metadata and Section 4.2.1.3. Load data and Section 4.2.1.4. Attach metadata for more information about loading the data and attaching metadata.

Figure 4. Relational database table stored in RDL in JSON format shows the storage of a relational database table in JSON format in the RDL. The JSON notation preserves the schema of the source by using the same column names and table names.Figure 4. Relational database table stored in RDL in JSON format

When a dataset cannot be interpreted (e.g. a data file with no declaration of fields), the RDL may store the whole dataset as-is so that the individual records can be interpreted later when metadata is available. In addition, this scenario may be applicable to a specific business reason, such as maintaining 837 claim files as received.

The RDL may store both the dataset and the JSON records in situations that require retention of an exact copy of the transmitted data or when the data format is unavailable during ingest.

TRA05.02-S3. The RDL loads raw data into collections of related content.

The implementation defines the content, format, metadata, and destination collection for the data integration process. The integration hub determines which collection to load the data into based on the content and source of the data.

For example, load changes to the provider enrollment table to the collection containing JSON records from that table. Load Electronic Data Interchange (EDI) files with 837 claims to the collection containing 837 claims.

TRA05.02-S4. The RDL defines data collections according to its business or technical purpose.

The business needs determine the collections established in the RDL as part of the setting up a data integration process. See the TRA05.07 – NoSQL data modeling supplement for more information about collections and collection naming conventions. See Section 7. Governance for information about setting up the data integration process.

Data Ingestion Data ingestion is the process of pushing/pulling (transporting) the data from its source into the RDL and adding metadata to the new document(s).

This section describes the following:

Data ingestion issues / challenges Ingestion process Raw data format Metadata Data Ingestion Issues / Challenges Issues/challenges related to the RDL include:

Large data volumes—Initial data loads require significantly more time, processor, and network resources than the subsequent incremental loads. The initial load schedule to allow for loading the large data volume without negatively affecting performance on the source systems and network.

Indexing—Indexes support efficient execution of queries. Without search-style indexing a query scans every document in a collection to select the documents that match the query statement. Indexing limits the number of documents to inspect. By design, most queries will execute against the ODS, not against the RDL. The NoSQL default indexes will support most queries against the RDL. When a query expects to search a large collection, create and apply a custom index to support that query. Where indexing often increases data-load time, it dramatically improves retrieval time.

Data integrity—the RDL is the original source for all EDS information sharing; therefore, the routine transport/load processes require:

· Monitoring to ensure that the pipeline does not drop data.

· Keep an un-edited original copy of raw. Raw data provides an audit trail.

Data security and access control—the RDL contains a superset of SCDHHS member and provider information, including Protected Health Information (PHI). Protecting the RDL from unauthorized access when the data is at rest in the RDL and in transit in the EDMP is critical.

Infrastructure scaling—the volume of MES data requires an easily scaled infrastructure to ensure acceptable performance loading and accessing (downstream EDS activities) data.

TRA05.02-S5. The RDL retains raw data in its original form and does not make updates to the raw data record content.

Ingestion Process Once set up, ingestion becomes a routine operation of steps illustrated in Figure 5. Ingestion process. The steps include:Figure 5. Ingestion process

Authenticating/authorizing the access to the RDL Accessing and transporting (push/pull) the data from the source to the RDL Loading the data into the RDL Attaching metadata to the loaded data The project delivery framework ensures that when setting up a new data integration process, the appropriate data sharing agreements, security controls, etc., are in place and the configuration is in accordance with the TRA. See Section 7. Governance for more information about the project delivery framework.

The standard for ingesting data into the RDL includes the following structure and considerations:

Process steps:

· Authenticate and authorize

· Access data

· Load data

· Attach metadata Requirements common to the RDL ingestion process steps include:

· Process logging

· Exception handling

· Security The RDL operational considerations include:

· High availability

· Scheduling

· Monitoring

· Reporting Process Steps All data requests across the MES are passed to the integration hub for CRUD (create, read, update, and delete) functions. The integration hub completely encapsulates access to the EDS framework and exposes data access and data processing services by means of file exchanges, web services, and direct database access to source systems.

Authenticate and Authorize Access to the RDL utilizes the enterprise identity management framework. Please refer to the TRA01.02 - Access Control and Identity Management supplement document for further information on identity management and access control.

TRA05.02-S6. Access to the RDL conforms to the TRA01.02 - Access control and identity management supplement.

Figure 5. RDL authentication architecture illustrates the architecture of the SCDHHS single-sign-on (SSO) implementation for the RDL.

In the SCDHHS SSO configuration, an enterprise-wide identity provider maintains user credentials to all applications. Figure 5. RDL authentication architecture

TRA05.02-S7. The role of the user defines access to the RDL.

The RDL runs on an application server that also connects to the identify provider. The identity provider refers to the enterprise active directory to validate the username and password of the user trying to access the RDL. If the credentials are correct, the user can access content based on the user configuration in the RDL application.

Access Data Depending on the configuration of the data integration process, one of the following mechanisms accesses the data from the source and transports it to the RDL:

· The data source extracts/saves the data to a file and places the file in a mutually accessible location.

· An integration hub service extracts the data from the database source as payload, a file, or file location.

For example, a trading partner creates an EDI health care claim (837) file and places it in a File Transfer Protocol (FTP) server, where the integration hub service can transport and load the data to the RDL. The TRA04 – Integration Hub supplement provides guidelines for selecting the service to access/transport data.

Load Data The MES only allows access to the EDS framework through the integration hub. Integration hub services that access the RDL includes ETL processes and web services.

TRA05.02-S8. Data is loaded into the RDL by an integration hub service.

The integration hub service transporting the data loads the data directly into the RDL. For instance, if an ETL process collects data from a file, the same ETL process inserts the data into the RDL.

The RDL loads data as provided. When the RDL loads encrypted or redacted data, the resultant records in the RDL persists as encrypted or redacted.

The MES supports multiple patterns for inserting source data into the RDL. The patterns are determined when setting up the data integration process. Patterns for inserting data include:

· A—ETL loading data from file

· B—ETL loading data from database query

· C—ESB loading data in web service message Figure 7. RDL load process illustrates patterns for loading the data.

Figure 7. RDL load process Table 2. Descriptions of ingestion patterns describes the patterns illustrated in Figure 7. RDL load process.

Table 2. Descriptions of ingestion patterns

Pattern
Step
Description
File transfer and load
A1
The data source places a file on a mutually accessible FTP server. File types include: XML, CSV, JSON, proprietary file format (PFF), EDI, etc.
A2
The presence of the file in the FTP server triggers the managed file transfer (MFT) service to initiate the extract, transform, and load (ETL) service.
A3
ETL examines the file source and content to determine the target RDL collection and schema. If the ETL can interpret the file, it loads the data as JSON records in the RDL collection; otherwise, the ETL service loads the file as-is in a document/record in the RDL collection.
Direct database query and load
B1
The ETL process queries the database to extract data from tables.
B2
The source system sends the data as payload in the response message.
B3
ETL loads the data to the RDL collection as JSON records.
Web service interface
C1
The ESB exposes a web service the portal invokes to send the data in the message. Formats include: XML, JSON, Health Information Exchange (HIE), National Information Exchange Model (NIEM), EDI, etc.
C2
The enterprise service bus (ESB) determines the target collection based on data source and content and loads the data to the RDL collection as a file/document or as JSON records.

Table 3. Ingestion patterns by data source capabilities and payload provides guidance on selecting the pattern based on the data source capabilities and the payload.

Table 3. Ingestion patterns by data source capabilities and payload

Pattern
Data source capabilities
Payload
File transfer and load
Extracts data to file
Files of any size and format, including initial data loads
Direct database query and load
Provides service account to query for data
Low volume loads in near-real-time scenarios
User input through portal
Invokes a web service to submit data
Low volume updates in near-real-time scenarios

Attach Metadata The integration hub service that transports and loads the data also attaches metadata to each record it inserts into the RDL. See Section 4.3. Metadata with common information for more information about metadata.

The RDL contains metadata for the data sources, enabling it to interpret datasets and map individual data elements. To expedite inserting data, the services retain this schema collection in cache.

When inserting data, the loading service matches the data source/content to the collection of source schemas to determine what metadata to apply to the interpreted data loaded to the RDL in JSON records. When the source schema is not present, the loading service cannot interpret the data, and the service loads the data in a document in as-is format, and attaches the default metadata—the source and the date/time stamp. See Figure 3. Loading data records and documents in Section 3. Architecture to ingest data.

Requirements Common to the RDL Ingestion Process Steps The RDL does more than store raw data. It provides a rich set of built-in functionality that improves the overall control, security, and reliability of the NoSQL database. This section describes the NoSQL application functionality.

Process Logging Process logs help investigate and troubleshoot potential failures and errors in enterprise data interfaces. In the event of a question about data integrity or error in the data load, the operations staff can utilize log files to identify the point of failure. Log data must be both comprehensive and easy to analyze.

The logs also demonstrate regulatory compliance for safe handling of data by documenting critical information about files, jobs, and data as well as user activity. Table 4. Process log data elements lists required RDL process log data elements.

Table 4. Process log data elements

Job and file data elements
User activity data elements

File name Source location Target location Transfer initiating system (source name) Initiating procedure name Initiation time Completion time Size of the file Transfer status Usernames Log-in time Session length Transfers initiated Folders accessed Files read/updated/deleted

TRA05.02-S9. The RDL includes audit/control records for each operation (Create, Read, Update, and Delete).

The Office of Information Assurance (OIA) identifies the audit/control requirements of the RDL to provide evidence of the following:

· Adherence to regulatory requirements on an ongoing basis

· Audit log non-repudiation

· Audit log retention

· Controlled access to audit logs Exception Handling The MES provides a uniform enterprise-wide exception-handling mechanism with a repository of error codes and descriptions.

TRA05.02-S10. The NoSQL platform handles exceptions per MES error-handling guidelines.

In the event of any failure in a RDL process, the NoSQL platform, which implements the RDL, handles the exception according to the enterprise error-handling guidelines. The goal of the error-handling procedure is to move the error-causing dataset to a dedicated folder and to send notification(s) to the source system with the information such as (in the case of the dataset being a file):

File name File size File format File encoding Target location Reason for failure

· Error code

· Error description Reasons for an RDL load failure may include:

The source schema is not available The source record fails data validation The target record fails consistency validation Security A major goal of the RDL is to ensure safety of sensitive and proprietary data at rest and in transit. Section 4.2.1.1. Authenticate and authorize describes the key aspect of RDL application security—authentication.

The integration hub supplements describe standard methods of secure data transmission that allow the RDL to trust the content in the files and to process them directly without additional validations or manual interventions.

The TRA01.02 – Access Control and Identity Management supplement also describes security requirements.

Operational Considerations Data lakes can easily contain hundreds of nodes, making the tools that monitor, control, and resize the data lake a critical resource. With these tools, administrators can decrease or increase the size of the data lake while finding and addressing issues such as bad hard drives, node failures, and stopped services. The tools also make it easy to add new services, ensure compatible tool versions, and upgrade the data lake. The RDL provides core administrative functionality, such as configuration, administration, monitoring and reporting.

High Availability / Disaster Recovery (HA/DR) The RDL achieves high availability (HA) and disaster recovery (DR) by using a shared-nothing architecture[footnoteRef:2] that allows for high-performance scaling and redundancy for failover. [2: A distributed computing architecture in which each node is independent and self-sufficient, and there is no single point of contention across the system. More specifically, none of the nodes share memory or disk storage.]

TRA05.02-S11. A high-availability NoSQL platform (with defined SLAs for recovery processing) implements the RDL.

The service level agreement (SLA[footnoteRef:3]) created when setting up each data integration process formalizes the availability objectives and requirements, for example, the monthly uptime percentage. The RDL ensures processing continuity within the cluster, which protects against hardware, network equipment, and power failures. If a failure occurs, the redundant component automatically picks up the workload, ensuring HA and data integrity. [3: An official commitment between a service provider and the constituent, agreeing on aspects of the service, for instance, quality, availability, responsibilities.]

DR protects against the total data center failure (e.g., in a natural disaster). RDL database replication ensures database restoration in the event of a disaster. The service level agreement (SLA) created when setting up each data integration process formalizes the recovery time objectives (RTO) and recovery point objectives (RPO). RPO measures the amount of data that can be lost to a disaster. RTO is the measure of how long the RDL can be offline during a disaster; that is, the amount of time to bring the standby RDL online.

Performance The RDL platform size and configuration ensure performance that meets the performance benchmarks formalized in the SLA created when setting up each data integration process.

TRA05.02-S12. The NoSQL platform ensures RDL performance within the defined SLA.

Performance measurements include:

Ingestion rate
File sizes
File upload times
Concurrent users

Search response times.

Automation The NoSQL platform upon which the RDL is implemented integrates with other tools and technologies used in the MES. For instance, the NoSQL platform offers functions, drivers, and adapters to connect to the ESB and ETL technologies TRA05.02-S13. The NoSQL platform offers RDL integration with MES technologies and tools.

Naming Conventions Naming conventions apply to collections and documents processed by the RDL. See the TRA05.07 – NoSQL Data Modeling supplement for more information about naming conventions for RDL objects.

Monitoring A monitoring dashboard provides views and tools to keep track of day-to-day operations and to troubleshoot performance issues and application errors and failures.

TRA05.02-S14. The NoSQL platform provides a tool to monitor the RDL.

The monitoring tool establishes performance baselines—normal patterns of performance. When operations fall outside of the baselines, the RDL application alerts/notifies Enterprise Services of the variance. For example, monitoring the data load time when loading the initial data probably falls outside the normal time to process incremental loads of the same database table.

Additional monitoring metrics of interest include considerations about the adequacy of the RDL resources, such as the following:

· Cache sizes

· Host machine processing utilization

· Disk space for forest data and merges

· Disk space for log files

· Memory for the range indexes

· Swap space and memory space

· Configuration and organization of hosts in the cluster

· Resource utilization by applications, such as content processing framework (CPF), replication, and point-in-time recovery Metadata with Common Information Metadata is data about data and provides context for source and target datasets.

The RDL collection containing all source schemas and the associated source record names uses a format similar to the sample format in Code example 1. Schema Reference.

Code example 1. Schema Reference “source”: “MEDS”, “recordName”: “L9211”, “version”: “1”, “schema”: {

“NUM_NP_PRE”: “”,

“NUM_PROV”: “”,

“CDE_TRANSID”: 0,

“DTE_XCTN_START”: “”,

“NUM_KEY_DUP”: 1,

“DTE_XCTN_END”: “”,

“DTE_XCTN_END_NULL_I”: “”,

“STR_TRDG_PARTNER_ID”: “”

“source”: “MMIS”, “recordName”: “L9211”, “version”: “2”, “schema”: {

“NUM_NP_PRE”: “”,

“NUM_PROV”: “”,

“DTE_XCTN_END”: “”,

“STR_TRDG_PARTNER_ID”: “”

Code example 2. Metadata format metadata: { // Embedded document for Metadata source: // Identifies where the record came from, such as MEDS.

Timestamp: // Records inserted timestamp from the application.

Datatype: // MEMBER, ENCOUNTER, CLAIM, ELIGIBILITY, etc.

recordName:// Name to track back to the source, e.g., table name.

version: // Record version number “1” by default recordMetadata: {//Optional. Record-specific metadata, e.g., source primary key sourceRecordId: // Optional. Record-specific.

.... // Optional other record-specific fields to tie to the source.

processInfo: { processName: // Process Name processId: // Process ID purpose: // Purpose applicationType: // ODI, OSB or SOA ...... // Flat record name key-value pairs.

Code example 3. Example JSON document with metadata provides an example of the metadata for a JSON document.

Code example 3. Example JSON document with metadata

“NUM_NP_PRE”: “”,

“NUM_PROV”: “AW0141”,

“CDE_TRANSID”: 835,

“DTE_XCTN_START”: “2016-03-28”,

“NUM_KEY_DUP”: 1,

“DTE_XCTN_END”: “2026-03-28”,

“DTE_XCTN_END_NULL_I”: “”,

“STR_TRDG_PARTNER_ID”: “H99914SC4”,

“recordMetadata”: { “sourceRecordId”: “MMIS-L9211-E782342” “metadata”: { //Metadata for L9211 record “source”: “MMIS”, “timestamp”: “2016-09-13 15:09:20”, “atatype”: “PROVIDER”, “recordName”: “L9211”, “version”: “1” “processInfo”: { “processName”: “ProviderManualLoad”, “processId”: “PROV-1”, “purpose”: “Inserting MMIS Provider rawdata into NoSQL”, “applicationType”: “ODI” Use Cases This section illustrates and details the initial and incremental loads processes. The initial load occurs once, the first time the (entire) database loads to the RDL. The incremental loads occur periodically (e.g., daily, weekly, monthly, etc.) to load data added to the source database since the previous (initial or incremental) load and data changed in the source database since the previous load.

Section 4. Data ingestion provides additional information about these use cases.

Initial Loads Data analysts, system architects, business users, etc. determine the data from sources that needs to be ingested into the RDL, the collection(s) that will hold that data in the RDL, and the pipelines to access/transport/load the data to the RDL. The source systems either grant READ access to their database or extract the data into files and place them in a mutually accessible location. Choices for transferring/loading the data include extracted files (MFT and ETL) or REST payloads.

Typically, in the initial load of data into the RDL, the entire (or the full set of) data residing in the source is ingested into the RDL. Figure 7. Initial load use case depicts two patterns for the initial loading of data from source databases.

Figure 8. Initial load use case Table 5. Initial load use case steps describes each step.

Table 5. Initial load use case steps

Pattern
Step(s)
Description
ETL loading data from file
A1 & A4
The source system:

1. Extracts each database table into a file

2. Places the files in the FTP server embedded in the integration hub or in a server accessible by the integration hub MFT service.

A2 & A5
The MFT service moves each file to the ETL service.
A3 & A6
The ETL service:

1. Reads each file.

2. Interprets the data.

3. Loads each record into the appropriate collection as a JSON record.

4. Attaches metadata to each record.

ETL loading data from database query
B1 & B4
The ETL service queries the contents of a table in the database.
B2 & B5
The response contains the table content.
B3 & B6
The ETL service:

1. Interprets the data.

2. Loads each record into the appropriate collection as a JSON record.

3. Attaches metadata to each record.

Incremental Loads Before loading each incremental load, the data changed/added since the last load needs to be determined. Then, the appropriate integration hub component loads the data into the RDL. Figure 8. Incremental load use case illustrates the incremental load. The steps are the same as detailed in Table 5. Initial load use case steps.

Strategies to determine the delta/incremental subset include:

· The source system extracts the new/changed data using time stamps on CREATE and LAST_UPDATED fields.

· The source system exposes the entire set of data and the ETL service extracts the new/changed data subset using time stamps on CREATE and LAST_UPDATED fields.

· If the source system does not have timestamps in the database, the ETL service reads all of the data (in the file or web service payload) and uses process intensive ETL functions to extract the differential data by comparing the latest set with the previous set of data.

Figure 9. Incremental load use case Table 6. Incremental load use case steps describes each step depicted in Figure 8. Incremental load use case.

Table 6. Incremental load use case steps

Pattern
Step(s)
Description
ETL loading data from file
A1 & A4
The source system:

1. Extracts incremental or all data from each database table into a file

2. Places the files in the FTP server embedded in the integration hub or in a server accessible by the integration hub MFT service.

A2 & A5
The MFT service moves each file to the ETL service.
A3 & A6
The ETL service:

1. Reads each file.

2. Interprets the differential data.

3. Loads each record into the appropriate collection as a JSON record.

4. Attaches metadata to each record.

ETL loading data from database query
B1 & B4
The ETL service queries a table for the differential data or full data.
B2 & B5
The response contains the table data.
B3 & B6
The ETL service:

1. Determines the differential data in the table, view or query.

2. Interprets the differential data.

3. Loads each record into the appropriate collection as a JSON record

4. Attaches metadata to each record.

Tools and Technologies

Purpose
Technology
Multi-model NoSQL database to store, manage, and search JSON and XML documents.
MarkLogic
Authentication
Liferay Identity Manager
File transfer
Oracle MFT
ETL
Oracle Data Integrator
Execution Scheduling
ESS (Enterprise Scheduling Services)

Governance The TRA08 – Architecture Governance supplement governs the RDL architecture, ingestion process, and data format.

The TRA05.06 NoSQL Data Modeling supplement governs the RDL data model, including:

Naming conventions Data modeling Integration hub or EDS processes transport the data in accordance with the processes governing the MES components and IT services each component provides. (See the TRA09 – Project Delivery Framework supplement for information about the MES governance.) The project delivery framework ensures that the appropriate data sharing agreements, security controls, etc., are in place for a new pipeline and that the transport and ingestion is configured in accordance with the TRA.

Appendices Acronyms

Acronym
Meaning
API
Application programming interface
CPF
Content processing framework
CRUD
Create, read, update, delete
CSV
Comma-separated values
DR
Disaster recovery
EDI
Electronic Data Interchange
EDMP
Enterprise data management pipeline
EDS
Enterprise Data Services
ES
Enterprise Services
ESB
Enterprise service bus
ETL
Extract, transform, and load
FTP
File Transfer Protocol
HA
High availability
HIE
Health Information Exchange
HTTP
Hypertext Transfer Protocol
JSON
JavaScript Object Notation
MES
Medicaid Enterprise System
MFT
Managed file transfer
NIEM
National Information Exchange Model
NoSQL
Not Only Structured Query Language
ODS
Operational data store
OIA
Office of Information Assurance
OOTB
Out-of-the-box
PDF
Portable Document Format
PFF
Proprietary file format
PHI
Protected Health Information
RDL
Raw data lake
REST
Representational state transfer
RPO
Recovery point objective
RTO
Recovery time objective
SCDHHS
South Carolina Department of Health and Human Services
SLA
Service level agreement
SME
Subject matter expert
SOA
Service-oriented architecture
SOR
System of record
SSO
Single sign-on
TRA
Technical reference architecture
XML
eXtensible Markup Language

Glossary

Term
Definition
Service level agreement
An official commitment between a service provider and the constituent, agreeing on aspects of the service, for instance, quality, availability, responsibilities.
Shared-nothing architecture
A distributed computing architecture in which each node is independent and self-sufficient, and there is no single point of contention across the system. More specifically, none of the nodes share memory or disk storage.

Referenced documents

Referenced document title
Document location and/or URL
TRA01 – Technical Reference Architecture
Overview of the TRA and Enterprise Architecture Diagram. Defines the SCDHHS strategy, including reference to the governance model.
TRA01.02 – Access Control and Identity Management
Identity management across portal, database and service assets. Use of service accounts for component-to-component calls.
TRA04 – Integration Hub
Overview of the integration hub, the ESB, enterprise services, logging, error response handling, architecture, publishing, discovery, etc.
TRA05 – Enterprise Data Services (EDS)
Overview of the data architecture and data-flows throughout the system. The mechanism for processing, consuming and federating operational data.
TRA05.07 – NoSQL Data Modeling
Standards and patterns to follow when modeling NoSQL document databases, collections and RDF tuples (triples).
TRA08 – Architecture Governance
The governance model to be followed should any of the TRA components require to be changed
TRA09 – Project Delivery Framework
Overview of ITIL, the Project Lifecycle and the SDLC. References on where to find it.

3/29/2017 SCDHHS - Confidential Page 13 of 15 image1.png image2.png image3.png image4.png image5.png image6.png image7.png image8.png image9.png image10.png image11.png image12.png image13.png image14.png image15.png image16.png image17.png image18.png image19.png

File details come from the government source that posted it. Updated .