PL 017.pdf

TRA05.03 Operational Data Store (ODS)

Version 0.5 12/10/2019

Prepared by:

South Carolina Department of Health and Human Services (SCDHHS)

Enterprise Services (ES)

PL 017

12/10/2019 SCDHHS - Confidential Page 2 of 3

Table of Contents

1. Introduction

2. Key Benefits

3. ODS in the MES Architecture

4. Data Materialization

4.1. Design

4.1.1. Schema Design

4.1.2. Metadata Mapping

4.1.3. Augmentation

4.2. Data Mastering

4.2.1. Improving Data Quality

4.2.2. Matching

4.2.3. Merging

4.3. Indexing

4.4. Data Load Patterns

5. Use Cases

5.1. Initial Materialization

5.2. Re-Materializing because New Functionality Added to Source System

5.3. Re-Materializing with New Trading Partner Data

6. Appendices

6.1. Acronyms

6.2. Referenced Documents

Table of Figures Figure 1. The operational data store (ODS) in the MES

Figure 2. ODS data architecture https://schhs.sharepoint.com/teams/INTHub/ES/Standards/TRA05.03%20Operational%20Data%20Store%20(ODS).docx#_Toc487721892 https://schhs.sharepoint.com/teams/INTHub/ES/Standards/TRA05.03%20Operational%20Data%20Store%20(ODS).docx#_Toc487721893

12/10/2019 SCDHHS - Confidential Page 3 of 4

Figure 3. ODS data materialization process

Figure 4. ODS business entity schema mapping

Figure 5. Example of initial materialization

Figure 6. Example of re-materializing with new trading partner data

Table of Tables Table 1. ODS key benefits

Table 2: Example for mastering data

Table 3. Aggregated data example

Table 4. Mastered data example

Table 5. Example of two matching records

Table 6. Matching rules based on fuzzy logic matching score

Table 7. Sample matching rules

Table 8. Sample survivorship rules

Table 9. Types of the universal indexes

Table of Standards TRA05.03-S1. The RDL serves as data source and audit trail for materialized data in the ODS

TRA05.03-S2. The ODS will materialize only the data needed for operations

TRA05.03-S3. The ODS will create a new record only if an existing record (based on pre-determined identifiers) do not already exist

TRA05.03-S4. The ODS will be the single integration point across the Medicaid Enterprise

TRA05.03-S5. Data materialization changes will follow the project delivery framework

TRA05.03-S6. Data element names will be persistent and uniquely used

TRA05.03-S7. Every data element will be related to at least one business entity schema

TRA05.03-S8. The ODS will use the RDL metadata repository to identify the RDL data

TRA05.03-S9. When changing the ODS schema, existing materialized data will be augmented. 12 https://schhs.sharepoint.com/teams/INTHub/ES/Standards/TRA05.03%20Operational%20Data%20Store%20(ODS).docx#_Toc487721894 https://schhs.sharepoint.com/teams/INTHub/ES/Standards/TRA05.03%20Operational%20Data%20Store%20(ODS).docx#_Toc487721896 https://schhs.sharepoint.com/teams/INTHub/ES/Standards/TRA05.03%20Operational%20Data%20Store%20(ODS).docx#_Toc487721897

12/10/2019 SCDHHS - Confidential Page 4 of 5

TRA05.03-S10. Data ownership and merging rules will be established for every automated merge process

TRA05.03-S11. The ODS will leverage indexes for increased data retrieval performance

12/10/2019 SCDHHS - Confidential Page 5 of 6

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.

12/10/2019 SCDHHS - Confidential Page 6 of 7

1. Introduction The South Carolina Department of Health and Human Services (SCDHHS) operational data store (ODS) provides the most comprehensive view of data within the Medicaid Enterprise System (MES) and offers a single view of business-critical entities, such as members, providers, claims, etc. For example, the ODS aggregates member data collected from component systems, such as the member eligibility system, member management systems, and trading partner systems. An example of an external trading partner is the managed care organization (MCO). The MES then provides the aggregated member information in the ODS to constituents through the SCDHHS portal, and to MES component systems and trading partners through the integration hub.

MES component systems and trading partner systems support Medicaid operations with transactional databases updated by business operations. SCDHHS recognizes some of these transactional databases as the authoritative data source—the system of record (SOR)—for a given data element or information set; for example, the Medicaid Information Management System (MMIS) is the SOR for SCDHHS Medicaid provider master data.

As explained in the TRA05 – Enterprise Data Services (EDS) and the TRA05.02 – Raw Data Lake (RDL) supplements, integration hub services first load MES source data to the RDL. Then, the ODS aggregates the RDL data into comprehensive views of members, providers, claims, outcomes, etc. Figure 1.

The operational data store (ODS) in the MES positions the MES and the ODS, a highly scalable NoSQL1 database.

The process to materialize the MES data includes steps to consolidate, cleanse, match, de-duplicate, and merge the data in the ODS. Data quality and master data management routines handle data inconsistences, such as differing values for the same data. Business rules determine

1 NoSQL is the acronym for Not Only Structured Query Language.

Figure 1. The operational data store (ODS) in the MES

12/10/2019 SCDHHS - Confidential Page 7 of 8 how to resolve the conflict. The EDS shares updates to the consolidated ODS records with the MES components and trading partners (including the SORs). Thus, the ODS is the MES source of truth (SOT), providing more comprehensive and more reliable information than an individual

SOR.

This supplement describes the ODS, its architecture, and the processes to achieve data quality through materialization. The TRA05–Enterprise Data Services (EDS) Framework supplement provides more information about the EDS framework and the MES data flow. The TRA05.02 – Raw Data Lake (RDL) supplement describes the RDL, its architecture, and the processes to ingest data into the RDL.

2. Key Benefits The ODS offers multiple benefits, including the benefits outlined in Table 1. ODS key benefits.

Table 1. ODS key benefits

Features Benefits

Consolidates and materializes data from the MES component systems and trading partners at regular intervals, which ensures that the ODS holds complete and near real-time MES information.

Provides a SOT for constituents using the SCDHHS portal and MES component systems and trading partner systems.

Generates analytics reports and periodically populates data marts designed to serve the business intelligence and reporting needs of a particular line of business.

Simplifies the data extraction complexity for analytical reporting. Data can be extracted from a single, consolidated, data source instead of several disparate systems.

Integrates and synchronizes the data across the MES component systems and trading partner systems.

Centralized data replaces the need for point-to-point connections and costly maintenance of individual system-to-system interfaces.

Data cleansing increases data accuracy across the enterprise.

Facilitates merging volumes of structured and unstructured content into a coherent, single view.

Provides the ability to extract relevant data from both structured (database, XML, JSON, etc.) and unstructured (PDF, XLS, etc.)

sources.

12/10/2019 SCDHHS - Confidential Page 8 of 9

Features Benefits

Provides an audit trail, documenting an activity performed on a record

Provides accountability – who, when, where and how a document was accessed or updated.

Helps resolve conflicts in data quality.

Helps reconstruct what happened before and during an event.

Provides tools and workflows to perform the data improvement activities both manually and automatically.

Cleanses data collected from disparate sources and resolves data quality issues.

Data cleansing can be coordinated during the materialization process or as a separate, independent, process.

Create, retrieve, update, and delete (CRUD) operations on documents and metadata

Search documents, semantic graphs and query lexicon values, using query formats, including:

• String query

• Structured query

• Combined query

• Query by Example (QBE)

• SPARQL2 query.

Apply transformations to document contents and search results

Extract large volumes of data from the ODS, transform the data, and load the data into an MES component system or trading partner system

Supports the architecture outlined in the TRA02 – Shared Services Service-Oriented Architecture (SOA) supplement, which includes a description of the benefits of shared services applicable to the ODS.

2 SPARQL is a recursive acronym for SPARQL Protocol and RDF Query Language. RDF is an acronym for Resource Description Framework.

12/10/2019 SCDHHS - Confidential Page 9 of 10

3. ODS in the MES Architecture The RDL contains all MES data in its native, raw, format. The ODS contains consolidated and materialized data for its intended business purpose.

Data in the ODS contains a single and consolidated view of business entities including (but not limited

to) the following typical Medicaid data:

• Members

• Providers

• Claims

• Encounters

• Eligibility history

• Enrollment history

As illustrated in Figure 2. ODS data architecture, the ODS member data is materialized from several sources of raw member data in the RDL.

The ODS serves as the primary data source to the integration hub for data delivery to trading partners, data marts and the SCDHHS portal.

TRA05.03-S1. The RDL serves as data source and audit trail for materialized data in the ODS.

The raw data in the RDL serves as the source for data to be materialized into the ODS. Since materialized records reference its sources, it also serves as audit trail.

TRA05.03-S2. The ODS will materialize only the data needed for operations.

Although the RDL contains all MES data, the ODS materializes only the data needed for business and analytical operations. This strategy allows for progressive transformation and phased data conversion.

TRA05.03-S3. The ODS will create a new record only if an existing record (based on pre-determined identifiers) do not already exist.

When adding a record, the ODS checks to see if a record with pre-determined identifiers exists.

If the record already exists, the ODS will update the record. If none exists, a new record will be created.

Figure 2. ODS data architecture

12/10/2019 SCDHHS - Confidential Page 10 of 11

If the ODS The ODS

Contains an existing document

Updates the referenced document according to the materialization merging standards described in Section 4.2.3.

Merging.

Does not contain an existing document

Creates a newly materialized record to the ODS.

TRA05.03-S4. The ODS will be the single integration point across the Medicaid Enterprise.

The ODS serves as the central integration point across data systems across the Medicaid Enterprise. Point-to-point integrations are no longer needed and highly discouraged.

4. Data Materialization Data materialization converts the data from its raw data format into a common consolidated structure and creates master documents. The data transformation process involves some preparation before steady state operations can materialize data. Figure 3. ODS data materialization process illustrates the process that involves:

• Designing a common schema (Section 4.1.1. Schema Design) and mapping metadata to the common schema (Section 4.1.2.

Metadata Mapping).

• Loading the initial data (Section 4.4. Data Load Patterns), which includes mastering the data (Section 4.2. Data Mastering) and indexing the data (Section 4.3. Indexing).

• Steady state steps for incremental data loads (Section 4.4. Data Load Patterns), including data mastering and indexing.

• Augmentation to improve the materialization, data quality, and/or schema (Section 4.1.3. Augmentation).

TRA05.03-S5. Data materialization changes will follow the project delivery framework.

Setting up the data materialization and all schema changes follow the formal process explained in the TRA09 – Project Delivery Framework supplement.

Figure 3. ODS data materialization process

12/10/2019 SCDHHS - Confidential Page 11 of 12

4.1. Design

The target schema needs to be defined and mapped so that the ODS can load the data. These preparatory steps occur when initially setting up the ODS, or when augmenting the ODS.

4.1.1. Schema Design

One of the most important and desirable aspects of a NoSQL database, in terms of design scalability, is that different documents within a collection can have different schemas;

whereas, in relational databases all rows contain the exact same number of column fields. This feature allows for the RDL to adapt to source structures very easily.

However, in order for the ODS to server the operational requirements of the MES, a common taxonomy for the ODS is important. During the schema design phase, the initial data structure for each document type is established based on its intended purpose. This structure can easily be changed, but will need to be communicated so that the data consumers can adjust accordingly.

TRA05.03-S6. Data element names will be persistent and uniquely used.

The names of data elements in a schema will not be updated unless the name change is included in an ODS augmentation. Although schema changes are very simple in NoSQL databases, the downstream data consumers will need to be notified to ensure that systems operations are not disrupted.

TRA05.03-S7. Every data element will be related to at least one business entity schema.

Each data element stored in the ODS is related to one are more business entities schemas.

4.1.2. Metadata Mapping

TRA05.03-S8. The ODS will use the RDL metadata repository to identify the RDL data.

The ODS uses the RDL metadata repository to identify the data collection and data elements in the RDL data records. During the design stage, the ODS development team uses the RDL metadata as a reference to map the RDL data needed to the ODS schema. Figure 4. ODS business entity schema mapping shows an example of mapping the ODS member data element (presented in first column) to three MES component systems data elements.

12/10/2019 SCDHHS - Confidential Page 12 of 13

Figure 4. ODS business entity schema mapping

4.1.3. Augmentation

Augmentation facilitates changing the schema or materialization process and improves data quantity and quality. The augmentation occurs when:

• Preparing to load the data into ODS for the first time, for example, loading to the member entity the names, identification, and contact information from the member eligibility and member management systems.

• Adding data elements to business entities, for instance, adding the veteran status from raw data in the RDL to the member entity.

• A change in a source (MES component or trading partner system) generates new business entity data or changes the data, thus requiring modifications to the ODS schema.

• Changing the materialization process to enhance ODS data quality or to improve the materialization process.

• Integrating data from an additional system that requires changes to the ODS schema, for instance, adding the data from an MCO to the RDL and then the ODS.

TRA05.03-S9. When changing the ODS schema, existing materialized data will be augmented.

For any ODS schema change, ODS data stored using the old schema are augmented to include element changes (new elements and removed elements). Augmentation includes an initial load and subsequent incremental loads that incorporate the schema changes.

CommonView Member Structure as on MM-DD-YYYY MEDS CURAM MMIS memberId: "", MEDSMEMBER.MemberId PersonDoc.CONCERNROLEID Member.INDIVIDUAL firstName: "", MEDSMEMBER.mbrNamFirst PersonDoc.firstforename Member.FIRSTNAME middleName:"", MEDSMEMBER.mbrNamMiddle PersonDoc.OTHERFORENAME Member.MIDDLENAME lastName: "", MEDSMEMBER.mbrNamLast PersonDoc.surname Member.LASTNAME gender: "M", MEDSMEMBER.mbrCdeSex PersonDoc.gender Member.SEX dateOfBirth: "", MEDSMEMBER.mbrDteBirth PersonDoc.dateofbirth Member.DATE_OF_BIRTH

12/10/2019 SCDHHS - Confidential Page 13 of 14

4.2. Data Mastering

When the ODS aggregates the dispersed source data, the collected data will include some redundancies and may reveal data discrepancies as well as missing data elements. The goal of mastering the data is to resolve the duplications, discrepancies, and incompletions to create a trusted single version of truth.

To get from source data to mastered data involves:

• Cleansing the data (Section 4.2.1. Improving Data Quality)

• Recognizing when two or more records correspond, thus de-duplicating the data (Section 4.2.2. Matching)

• Merging matching records into a single record (Section 4.2.3. Merging)

Table 2: Example for mastering data, Table 3. Aggregated data example, and Table 4. Mastered data example illustrate the concept of mastering data using two records from three source systems. Table 2: Example for mastering data shows the data as it is present in each of the three sources.

Table 2: Example for mastering data

Source 1

Member Id First Name Last Name DOB SSN Addr1 Addr2 City State Zip

ABC222 Neil Armstrong 1/1/1950 123456789 123 Wail st unit99 New York NY 77777

ABC333 Yuri Gagarin 1/1/1955 111222333 123 Broad st Charlotte NC 28888

Source 2

Id FNAME LNAME DOB SSN ADDRESS ADDRESS_CONT CITY STATE ZIP

1211 Neil Armstrong 1/1/1950 123 Wail st unit99 New York NY 77777

1222 Valentina Tereshkova 6/6/1955 666677778

Source 3

Member_ID First Name Last Name DOB SSN Addr1 Addr2 City State Zip

MEM789 Yuri Gagarin 1/1/1955 111222333 123 Broad st #98 Charlotte NC 28888

MEM799 Valentina Tereshkova 6/6/1955 666677778 2 Bakers street unit2 London MA 98765

12/10/2019 SCDHHS - Confidential Page 14 of 15

Table 3. Aggregated data example shows the aggregated data with duplicates/redundancies.

Table 3. Aggregated data example

Member Id First Name Last Name DOB SSN Addr1 Addr2 City State Zip

ABC222 Neil Armstrong 1/1/1950 123456789 123 Wail st unit99 New York NY 77777

ABC333 Yuri Gagarin 1/1/1955 111222333 123 Broad st Charlotte NC 28888

1211 Neil Armstrong 1/1/1950 123 Wail st unit99 New York NY 77777

1222 Valentina Tereshkova 6/6/1955 666677778

MEM789 Yuri Gagarin 1/1/1955 111222333 123 Broad st #98 Charlotte NC 28888

MEM799 Valentina Tereshkova 6/6/1955 666677778 2 Bakers street unit2 London MA 98765

Table 4. Mastered data example shows the mastered data with no duplicates.

Table 4. Mastered data example

Member Id First Name Last Name DOB SSN Addr1 Addr2 City State Zip

ODS001 Neil Armstrong 1/1/1950 123456789 123 Wail st unit99 New York NY 77777

ODS002 Yuri Gagarin 1/1/1955 111222333 123 Broad st #98 Charlotte NC 28888

ODS003 Valentina Tereshkova 6/6/1955 666677778 2 Bakers street unit2 London MA 98765

4.2.1. Improving Data Quality

When materializing data, the ODS may encounter data quality issues such as data inconsistency, data accuracy, and data completeness. (See the TRA05 – Enterprise Data Services (EDS) supplement for additional information about data quality issues.)

Data cleansing standardizes document information, and includes:

• Correcting data elements to align with external regulatory standards, for example, ensuring that the ODS stores street address, city, state, and postal code information in a uniform and consistent format, as mandated by United States postal requirements.

• Using consistent date formats for all date related data.

• Ensuring the ODS follows a common naming standard for all alike entities and data elements, for example, storing provider and member information in a uniform and consistent format.

• Resolving or flagging data redundancies. For example, in Table 3. Aggregated data example, two records in the consolidated data table refer to the same person, Mr. Neil Armstrong. The ODS consolidates these two records into a single record.

12/10/2019 SCDHHS - Confidential Page 15 of 16

• Validating address information against United States Postal Service (USPS), Google or other address validation services. For example, Table 4. Mastered data example includes two variants for street, which should be ST, the USPS postal addressing standard abbreviation for street.

The ODS provides routines that enforce document validation against data governance policies and procedures. These routines allow the database administrators to configure and enforce data governance rules, and to flag non-conforming documents. Examples of data governance rules include:

• Proper document structure

• Correct data types

• Allowed data ranges

• Mandatory fields

4.2.2. Matching

Matching is the process of recognizing that two or more records in the database correspond to the same physical entity instance. Such matching records are essentially duplicate records that occurred either due to erroneous data entry or due to combining data from multiple sources.

However, matching the records is not as straight forward as identifying records with field values. Table 3. Example of two matching records provides an example of two matching records.

Table 5. Example of two matching records

System Id Title Name Address 1 Address 2 Post code Post code

897889 Mr John Deer 1A Spiral Dr Charlotte Apt 1A 28888

AX-RTG1 J Deer Spiral Drive Charlotte 1A

The records in Table 5. Example of two matching records have different values in every database field; however, many similarities exist between the records. The ODS provides fuzzy logic matching capabilities that identify matching records and flag them for further action.

Based on how similar the field values are with each other, the ODS fuzzy logic provides a certain percentage matching score that can be used to come to a matching decision. For example:

• 100% = Equal

• 80%-99% = Close Match

• 0-79% = No Match

12/10/2019 SCDHHS - Confidential Page 16 of 17

The ODS allows business analysts to configure records-matching rules that determine which records present the same entity and which do not. Matching rules offer a way to interpret comparison results according to their business significance. Any number of ordered rules can be configured in order to interpret the comparison results. Each rule can result in one of three decisions based on overall matching percentage score as illustrated in Table 6. Matching rules based on fuzzy logic matching score. All scores in the table are for illustrative purposes only.

Table 6. Matching rules based on fuzzy logic matching score

Decision Outcome Example

Match Merge Records Records with fuzzy logic matching percentage more than 90%

Review Manual review required to confirm or deny the match

Records with fuzzy logic matching percentage more that 60% and less than 90%

No Match No Action Records with fuzzy logic matching percentage less than 60%

Given the above methodology and configurability for identifying matching record, this matching logic can be triggered in batch or real-time mode. In batch mode, the ODS automatically matches a large number of records, and marks some of them for review. In real-time mode, the matching logic looks for potential matches in the database, and in the event of a match, marks it as match.

4.2.3. Merging

The ODS merges the matching records to form a single record. The ODS resolves data conflicts using list-of-survivorship rules, such as the record originating from the member management system always takes precedence, records with latest timestamp takes precedence, etc.

However, ODS does not merge records that require resolution by a manual intervention.

TRA05.03-S10. Data ownership and merging rules will be established for every automated merge process.

As described in Section 4.2.2. Matching, the record mapping process generates a matching score for each pair of records. A threshold matching score is configured to decide whether to merge the records. For instance, if the threshold matching score is 90, the ODS performs an

12/10/2019 SCDHHS - Confidential Page 17 of 18 automated merge for all record pairs with matching score greater than 90. Table 7. Sample matching rules lists the ODS actions based on the matching score.

Table 7. Sample matching rules

Threshold matching score

Conflicting data elements present?

Action

Meets No Merge automatically to form a single master record

Meets Yes Merge without solving conflicts. Flag for review.

Does not meet No

Do not merge. Flag for review if score is significantly high.

Does not meet NA

Do not merge

Table 8. Sample survivorship rules illustrates the source of truth (survivorship rules) for the data elements during the materialization and merge processes.

Table 8. Sample survivorship rules

Element Source of truth

Social security number Social Security Card Cúram evidence

Date of birth Birth certificate Cúram evidence DSS data feed

First name Capture all known names in order of data submission

4.3. Indexing

Indexes support the efficient execution of queries in the ODS. Without indexes, the ODS performs a collection scan; that is, the ODS scans every document in a collection to select

12/10/2019 SCDHHS - Confidential Page 18 of 19 documents that match the query statement. If an appropriate index exists for a query, the ODS database can use the index to limit the number of documents it must inspect. Advantages of indexing include:

• Doing comparison operations

• Quick retrieval

• Fast aggregation

• Creating a lexicon for quick access to the documents and collections

However, adding a new index requires additional disk space usage and increases document loading time.

By default, the ODS builds a set of indexes designed to yield the fast query performance in general usage scenarios. The ODS index is optimized to allow text, structure, and value searches to be combined into a single process. Table 9. Types of the universal indexes describes ODS Index types.

Table 9. Types of the universal indexes

Type of index Description

Word index Word indexing enables simple single word searches. The ODS word indexing also supports an inverted index, which inverts the relationship between the word and document.

Phrase index Phrase indexing enables two-word phrase searches. When a document is materialized, the ODS creates all possible two-word indexes.

12/10/2019 SCDHHS - Confidential Page 19 of 20

Type of index Description

Relationship index

Each eXtensible Markup Language (XML) element or JavaScript Object Notation (JSON) property is used as an index for searching.

Value index Value indexing maps strings with a value using a hashing technique.

Instead of storing a long string, the ODS hashes element text to a succinct integer. Regardless of the element name and text string length, the string is only a small entry value in the index.

Range index Range indexing enables searches based on inequalities, for example, Policy amount > 100.00.

Lexicons Lexicons allow quick access to the document and collection Uniform Resource Identifiers (URI) in the database. The ODS can create a quick reference for lists of unique words or values. Type of lexicons include:

Word lexicon

Value lexicon

Value co-occurrences lexicon

Geospatial lexicon

Range lexicon

URI lexicon

Collection lexicon

Triple index All triples are indexed to enable quick reference.

TRA05.03-S11. The ODS will leverage indexes for increased data retrieval performance.

The ODS also uses metadata indexing that enables quick search of collections, directories, and security rules. Metadata indexes include:

• Collection indexes – Each document is tagged as belonging to any number of collections.

Collection names are indexed and can be used for search.

• Directory indexes – Similar to collections index; however, directory indexes are hierarchical and non-overlapping.

12/10/2019 SCDHHS - Confidential Page 20 of 21

• Security indexes – User role-based document restriction is enabled using security index.

• Properties indexes – Document properties are also used for searching.

4.4. Data Load Patterns

As discussed in supplements such as TRA05.02 – Raw Data Lake (RDL) and TRA04.05 – Extract, Transform, and Load (ETL), data loads classifications include initial loads and incremental loads.

An initial load of data in the ODS occurs when loading a large amount of data into the ODS for the first time. An incremental load contains data added or changed in the RDL since the previous data load to the ODS. Typically, initial data loads occur when:

• The ODS is set up for the first time.

• A large amount of data in the RDL needs to re materialized.

• Introducing data from a component system database or trading partner to the MES, for example, after a trading partner completes the partnership agreements and performs the initial data load into the RDL.

5. Use Cases This section illustrates the application of the standards within the context of the MES.

The use cases in this section present some data in tabular format for clarity.

The tables do not mean that the data is stored in relational format.

12/10/2019 SCDHHS - Confidential Page 21 of 22

5.1. Initial Materialization

The initial load of data into the ODS comprises establishing a materialized record set for the first time. For instance, the first time that member data or provider data is consolidated across RDL sources, an initial load will be performed.

Matching data records from three different sources in the RDL are materialized into one document in the ODS. The steps to materialize the records include:

1. Identify the source collections that contain member data.

2. Design the materialized record layout based on its business purpose.

3. Select the records from the RDL and group based on the threshold matching score. For John Doe, all information in all the data elements matches except the address data elements.

Source 1

Id Fname Lname DOB SSN Address_Line1 Address_Line2 City State Zip

XYZ111 John Doe 1/1/1966 112233445 123 Main st unit 4 columbia SC 33333

Source 2

Id First_Name Last_Name DATEOFBIRTH SOCIALSECURITY Address City State Zip

NSL111 John Doe 1/1/1966 112233445 123 Main st columbia SC 33333

Source 3

Member Id First.Name Last.Name DOB SSN Addr1 Addr2 City State Zip

ABC111 John Doe 1/1/1966 112233445 123 Main st unit 4 Columbia SC 33333

Figure 5. Example of initial materialization

12/10/2019 SCDHHS - Confidential Page 22 of 23

4. The ODS resolves the address data element conflict using the survivorship rule that the record with latest timestamp takes precedence. In this example, the metadata for the record from the third source had the latest timestamp.

Aggregated data

Member Id FirstName LastName DOB SSN Addr1 Addr2 City State Zip

ABC111 John Doe 1/1/1966 112233445 123 Main st unit 4 Columbia SC 33333

5. The ODS improves the data quality by correcting the data elements to ensure that the ODS stores street address, city, state, and ZIP information in a uniform and consistent format, as mandated by United States postal requirements.

Cleansed data

Member Id FirstName LastName DOB SSN Addr1 Addr2 City State Zip

ABC111 John Doe 1/1/1966 112233445 123 Main ST Unit 4 Columbia SC 29201

6. The ODS stores the final mastered data record.

5.2. Re-Materializing because New Functionality Added to Source System

When a source system adds new functionality, the new functionality generates a new set of data, which requires modifications to the existing ODS schema. The re-materialization process includes following steps:

• Re-map the metadata with new ODS schema structure.

• Determine the portion of data records affected by the change.

• Update survivorship rules according to the new schema structure.

• Run materialization process on only the records affected by the schema structure change.

• Update incremental load materialization scripts to incorporate new data elements.

This use case continues the example from Section 5.1. Initial Materialization. Adding a county data element to the member schema affects only the member records already added to the

12/10/2019 SCDHHS - Confidential Page 23 of 24

ODS. The ODS collects county information for each address from the postal service data. The following example shows the added county.

5.3. Re-Materializing with New Trading Partner Data

When SCDHHS and a new trading partner sign data sharing agreements, the new trading partner data is loaded into the ODS using the following steps:

• Map the new trading partner metadata to the ODS schema structure.

• Determine data collections and records affected by the new trading partner data.

• Update the surviving rules according to the new business priorities.

• Run the materialization process on only the collections/records that need to be charged.

6. Appendices

6.1. Acronyms

Acronym Meaning

CRUD Create, read, update, delete

ODS data

Member Id First Name Last Name DOB SSN Addr1 Addr2 County City State Zip

ABC111 John Doe 1/1/1966 112233445 123 Main ST Unit 4 Richland Columbia SC 29201

Figure 6. Example of re-materializing with new trading partner data

12/10/2019 SCDHHS - Confidential Page 24 of 25

Acronym Meaning

DOB Date of birth

EDS Enterprise Data Services

ES Enterprise Services

ETL Extract, transform, and load

JSON JavaScript Object Notation

MCO Managed care organization

MEDS Medicaid Eligibility Determination System

MES Medicaid Enterprise System

MFT Managed file transfer

MMIS Medicaid Management Information System

NoSQL Not Only Structured Query Language

ODS Operational data store

QBE Query by example

RDF Resource Description Framework

RDL Raw data lake

SCDHHS South Carolina Department of Health and Human Services

SOA Service-oriented architecture

SOR System of record

SOT Source of truth

SPARQL Simple Protocol and RDF Query Language

SSN Social Security Number

12/10/2019 SCDHHS - Confidential Page 25 of 25

Acronym Meaning

TRA Technical reference architecture

URI Uniform Resource Identifier

USPS United States Postal Service

XML eXtensible Markup Language

6.2. Referenced Documents

Referenced document title Document location and/or URL

TRA04.05 – Extract, Transform and Load

(ETL)

Standards on ETL processing, ETL integration in the ESB and expectations on data that leave the DMZ.

TRA05 – Enterprise Data Services (EDS) Overview of the data architecture and how data is expected to flow through the system.

How data is used and then fed back to the source systems.

TRA05.02 – Raw Data Lake (RDL) Overview of the Raw Data Lake (RDL) and how the MES ingests and processes raw data.

TRA09 – Project Delivery Framework Overview of ITIL, the Project Lifecycle and the SDLC. References on where to find it.