Data

Page tree



Back to Knowledge Base

MDM 3.0 Cloud Migration - Changes and Workarounds

The MDM team will be utilizing Cloud capabilities to address current on-premise challenges in the goal of providing modern, complete, conformed, and timely data to business users, resulting in the MDM 3.0 Cloud Migration. Because of this migration, the MDM data within the CDR has changed. Please see the changes below, as well as workarounds for SAS Viya Users for specific use cases. 

If you are interested in walk-through of this information, please watch the January 27, 2022 Communications Call. 

Workaround Use CaseWorkaround Details
SAS Workaround for INT to BIGINT
  • BIGINT is known to be incompatible with SAS. MDM Surrogate keys(sk) defined as BIGINT and are generated with 19 digits whereas SAS can hold only 15.
  • Manual workaround
    • Convert BIGINT column to char to ensure no value is lost. Ex. select cast(sk_col1 as char(19)) from mdm_table1
SAS Workaround for STRING
  • Conversion issues can occur when querying data from CDR Hive with SAS Viya.

–Hive STRING columns default to 32767 when Hadoop data is read into SAS. This can be unnecessarily large for short STRING columns and might lead to performance issues

–It can also truncate Hadoop STRING columns that are greater than 32767

–Additional reference can be found here: 48815 - Issues with various data types occur when you convert data from Hive to SAS® with SAS/ACCESS® Interface to Hadoop

  • Remediation has been applied in all CDR managed dataset

–New table attribute added SET TBLPROPERTIES (‘SASFMT: sk_col1'='char(max_length)’) during table publish process.

  • Other manual workaround when encounter performance issue by casting the column to appropriate length

–Ex: SELECT CAST(SUBSTR(sk_col1 , 1, max_length) AS CHAR(max_length)) AS casted_sk_col1 FROM mdm_table1

TopicDetails
MDM 3.0 Cloud Migration
  • The MDM team will be utilizing Cloud capabilities to address current on-premise challenges in the goal of providing modern, complete, conformed, and timely data to business users.
  • Cloud migration is planned in phases:
    • Phase I – Provider subject area – Went live on Jan 14th, 2022
    • Phase II – POR and NCBP subject area – Planned go-live end of Quarter 2, 2022
  • Impact to Consumers – Current users will continue to receive certain products as per their integration method but will eventually need to source MDM data via the EDL by end of 2022.
MDM 3.0 Provider Data Products Refresh Frequency
  • We are planning to improve the frequency with which source-system data is refreshed in MDM. Please note MDM is still assessing the performance, the below table is the planned schedule and will be in effect after we do few weeks of runs and optimization:

Changes to Lean Index Structure 
  • Lean Index Structure - MDD_MDM_PRVDR_ID_IDX and MDD_MDM_PRVDR_ID_IDX_HSTRY:
    • MDM_PRVDR_ENT_ID - Entity ID column data type change from BIGINT to String - The Entity ID will allow alphanumeric and "|“ values.
    • For example: NEA assigns the EID as one of the source provider ID, typically it will be NPPES as it is the superset of Providers. In certain cases, the EID could be assigned with source provider ID from PECOS or T-MSIS.
    • The EID could be in the format of

–T-MSIS - TMSISI_VT|123456|1234567890 or

–PECOS – PECOSI_8765432161|I20051014000123 or

–NPPES – NPPESI_876492840485909

  • Lean Index Structure - MDD_MDM_PRVDR_ID_IDX and MDD_MDM_PRVDR_ID_IDX_HSTRY:
    • HLD_BACK_NPI_IND column (Hold back NPI for T-MSIS Organizational Providers) If an NPI is associated with more than five SSPIDs, it will not be used for matching.
    • This column gives an indicator whether the NPI was used or not for matching.
    • This rule is applied only to Organizational providers.
  • Lean Index Structure - MDD_MDM_PRVDR_ID_IDX and MDD_MDM_PRVDR_ID_IDX_HSTRY:
    • STATE_RPTD_NPI - State Reported NPI column will be removed, and this information is available in MDD_PRVDR_DM_LGCY_IDENT_HSTRY table under PRVDR_LGCY_ID column.
    • HRVST_NPI_IND - This flag is set to "Y" if the NPI is not reported by the state and MDM has harvested the NPI from the Medicaid claims.
    • The above two columns are only applicable to T-MSIS.

PECOS Source Related Changes
  • CPI requested that MDM source PECOS data from the PECOS UGE and custom PECOS Data Mart views created by the PECOS 2.0 Team. As a result, the following columns in MDM will have a "NULL" value for the PECOS source until the availability date below:

  • CPI requested that MDM source PECOS data from the PECOS UGE and custom PECOS Data Mart views created by the PECOS 2.0 Team. As a result, the following columns in MDM will have a "NULL" value for the PECOS source until the availability date below:

  • The following columns will not be populated in MDM 3.0. However, these columns will continue to appear in the data products.

  • The following columns are new columns and will be populated in MDM 3.0:

  • Since MDM will source PECOS data from the PECOS UGE and custom PECOS Data Mart views created by the PECOS 2.0 Team. Any new change requests submitted to MDM for PECOS data will be dependent on the PECOS 2.0 Team’s prioritization and life cycle. 
  • MDM is working on the following CRs and will provide information in the future on the targeted release dates:

T-MSIS Related Changes
  • Medicaid Organizational Providers Matching Improvements – Currently, in MDM 2.0, we ingest data for all T-MSIS Organizational providers; however, only 13 states (AK, DC, FL, KY, NY, OH, SD, VA, VI, VT, WI, WV and WY), have been ID resolved.
    • The data quality reported by other states not listed above were introducing false positive and false negative matches.
    • We have been monitoring the data quality and now believe we can match Organization Providers for most of the states. A list of any states not included for Organization Provider matching will be provided in January 2022. 
  • MDM can match ALL Medicaid Individual Providers from all states except Iowa and Oregon.
  • Harvesting NPIs - At times, the states do not report NPIs for providers. Two plausible reasons:
    • The state does not have the NPI for a Provider
    • The provider is an Atypical provider.
  • When this occurs, MDM scans the Medicaid claims and harvested the NPI. Currently, we are doing this for a finite set of states. Going forward, NPI harvesting will be done, when required, for all states. 
Data Type Change
  • The data type of the surrogate key is changing from INT to BIGINT.
  • This change affects the corresponding FK columns too. FK columns would be automatically modified with the PK Column change.

  • The data type of the surrogate key is changing from INT to BIGINT.
  • This change affects the corresponding FK columns too. FK columns would be automatically modified with the PK Column change.

  • All Character data types (Char, Varchar) have been modified to STRING in all Tables.
  • Other Data Type change (BIGINT to STRING) change.

  • 'Timestamp to Date' Data type changes for DOB and DOD.

***See workarounds in other tab 

NPI Table Changes
  • Existing NPI views (listed below) would be converted to the following NPI Tables (removed _VW from the table name).

  • Required Audit columns have been added to the above tables:

  • The following columns have been removed from the table.

  • The following table name have been changed.

NPICS Related Changes
  • Retired the NPICS crosswalk table in MDM. Users can get NPICS data from the MDD_NPI_LGCY_ID table.
  • MDD_MDM_PRVDR_ID_IDX - NPICS data will not be reflected in the Provide Master Index.
Known Issues Announcement

MDM 3.0 system is live with the following known issues:




  • No labels