Approach and conventions to design the existing Healthcare data model for Dataverse
Before you dive into the Maker portal and build your tables, review the approach and conventions used in designing the existing Healthcare data model for Dataverse.
In this exercise, you'll:
Review the Fast Healthcare Interoperability Resources (FHIR) specification that drives the Healthcare data model for Dataverse.
Learn how to interpret the specification details.
Identify common data elements and how they translate to Dataverse.
Review some of the built-in tables and conventions of the data model.
This step is important to ensure that you follow conventions, plan for interoperability, and avoid rework during development.
FHIR Specification and Resources
The HL7 FHIR specification is a standard for healthcare data exchange, published by HL7®. This specification outlines FHIR resources that represent healthcare data that can be exchanged. More specifically, you reference a Domain Resource which is derived from Resource when building new Dataverse tables in the data model. The FHIR specification for Resource and Domain Resource states:
A resource is an entity that:
Has a known identity (a URL) by which it can be addressed.
Identifies itself as one of the types of resource defined in this specification.
Contains a set of structured data items as described by the definition of the resource type.
Has an identified version that changes when the contents of the resource change.
A domain resource is a resource that:
Has a human-readable XHTML representation of the content of the resource. For more information, see Human Narrative in resources.
Can contain extra related resources inside the resource. For more information, see Contained Resources.
Can have extra extensions and modifierExtensions and the defined data. For more information, see Extensibility.
The FHIR specification includes multiple data formats for a FHIR resource, but our focus is on the FHIR version. The Healthcare data model for Dataverse includes versions of FHIR resources represented in a relational database format.
The full FHIR specification can be found at https://hl7.org/fhir/.
JSON versus Relational Data Model
The FHIR specification provides a JSON schema for each FHIR resource, and our data ingestion tooling is designed to read and process FHIR JSON documents, or bundles. You need to translate a document-based model to a relational model, and this relational model must conform to standards that make it intuitive to read. The model should also support tooling to both ingest inbound FHIR data and produce the same FHIR structure for outbound writeback operations.
The conventions for tables and field creation also enable making the transformation configurable. You can provide configurable transformation rules via the Entity and Attribute maps. These rules provide flexibility to customers, partners, and ISV to extend the data model and use Microsoft Dataverse healthcare APIs for data synchronization.
Read the FHIR specification
The FHIR specification contains a large amount of information, defining the FHIR resources, their components, sample data, and a full API specification. The FHIR JSON format is a document centric specification, meaning that an entire FHIR resource record is contained in a single hierarchical JSON structure. The Dataverse foundation is a relational data store, meaning that it provides tables and relationships between them.
Understanding this specification is key to designing the representative Dataverse tables as mapping FHIR resources to Dataverse tables is relatively straightforward. A FHIR resource can be mapped to an existing Dataverse table, like FHIR Patient to Dataverse Contact. A new Dataverse table can be created to represent a given FHIR resource, such as Encounter to msemr_Encounter.
But mapping the fields between FHIR data types and Dataverse column types and knowing when to create expansion tables is the key.
The FHIR specification provides a detailed specification for each FHIR resource, and the conventions for each can be found in Logical table.
FHIR Resource structure and cardinality
The following table provides an overview of the specification used for each resource. This table describes the various structures that compose a FHIR resource. You can refer to the full details in the specification, but some details are worth mentioning as follows.
Resource Name - name of the FHIR resource. Used as the name of the corresponding Dataverse Table.
nameA - standard element of a FHIR resource. 1..1 indicates the field is mandatory. 0..1 would indicate that the field is Optional.
nameB[x] - element has one of several different types. For example, you often see valueBoolean (Boolean), valueString (string), valueQuantity (Quantity). This means that when a FHIR resource is created, the element might be either valueBoolean OR valueString OR valueQuantity. The approach for the rest of the data model is to create a field for each of these possibilities, each of the corresponding type.
nameC - element representing a collection of child values. 1..* indicates that at least one value is required, while 0..* means that a related value is optional. This type of FHIR element translates to an Expansion element.
Field type mapping conventions
Each element in the FHIR resource structure can be one of the data types.
You can find complete information on each of these resource structures in the documentation. For now, the following items are common to building the relational Dataverse representation of a FHIR resource.
Simple / primitive types - single elements with a primitive value. For example, Boolean, integer, date, decimal, or string. These data types map to the Dataverse data types. However, exceptions can be made for primitive types like time.
Complex types - elements of a data type which describes an element that has other elements such as period, CodeableConcept, or Identifier. Each of these complex types has its own definition and can be composed of primitive types, or other complex types. When the cardinality is 0..1 or 1..1, these elements typically result in more than one field to capture the data of the complex type.
Reference Types - element that contains a reference to another resource. This element usually comes in the form of the resource type and referenced identifier. In Dataverse, these typically translate to a Lookup field. The specification indicates the type of FHIR resource being referenced. In some cases, a FHIR element can reference more than one type of resource for a single field. For example, Claim.provider is defined as: Reference(Practitioner | PractitionerRole | Organization). This means that when the FHIR message is received, it's a reference to a FHIR resource of type Practitioner OR PractitionerRole OR Organization. This translates to a polymorphic or multi-table lookups in Dataverse. Some refences are defined as Reference(Any) in the FHIR specification, meaning that a reference to any FHIR resource is accepted. In these instances, our recommendation is to review sample data for commonly used references and define your polymorphic lookups with those FHIR references. If extra references are required, they can be updated in later releases.
These three data types represent the major scenarios you encounter when extending the data model.
FHIR JSON Elements to Dataverse Columns
Following is a general mapping of FHIR data types to Dataverse data types. This list is based on commonly used data types found in the Healthcare data model for Dataverse.
Primitive types
| FHIR element type | Dataverse | Notes |
|---|---|---|
| string | string | A FHIR string can be up to 1 MB, but standard Dataverse conventions should apply based on the reasonable expectations for the real data. |
| boolean | Yes/No | This is a string value in FHIR as true/false. |
| decimal | Number/Decimal | A decimal supports a range of values between -100,000,000,000 and 100,000,000,000. |
| integer | Number/Whole Number | The supported values should map exactly between the two systems. |
| uri | String | String with no formatting as it isn't a URL. |
| url | String | String formatted as a URL for display and validation. |
| date | DateTime | DateTime set to Date Only |
| dateTime | DateTime | DateTime set to Date and Time |
| time | String | String because Dataverse doesn't include built-in support for Time only. A Date Time format can be used, but custom code would be required for display, and the Dataverse Healthcare APIs won't correctly import the value. |
| code | Choice | Mapping for Choices (formerly Option Sets) is supported by the Dataverse Healthcare APIs. The definition for the code provides the possible values for the Choices in Dataverse. |
| id | String | String of at least 64 characters. |
The full list of HL7 FHIR primitive types can be found at Primitive types.
Complex types
| FHIR element type | Dataverse | Notes |
|---|---|---|
| period | Date Time fields start time, and end time | Consists of a start and end primitive type, so a field for each value. |
| coding | Multiple fields to capture individual items | The following are common in the current data model: • System as uri • version as string • code as code • display - string |
| Quantity | Multiple fields to capture individual items | The following are common in the current data model: • value - decimal • comparator - code • unit - string • system - uri • code – code A common variation is SimpleQuantity - in this case you can exclude the comparator field. |
| ratio | Multiple fields to capture individual items | Consists of a numerator and denominator Quantity type, and requires a set of fields for each value. |
| range | Quantity Fields to capture high and low parts the range | Consists of a high and low Quantity type and requires a set of fields for each value. |
| CodeableConcept | Codeable Concept lookup | Codeable Concept is an existing Dataverse table that you can reference in the new tables. |
| Identifier | Identifier lookup | Identifier is an existing Dataverse table that you can reference in the new tables. |
| Annotation | Annotation lookup | Annotation is an existing Dataverse table that you can reference in the new tables. |
| Attachment | Attachment lookup | Attachment is an existing Dataverse table that you can reference in the new tables. |
The full list of HL7 FHIR complex types can be found at Complex types.
Table Naming Conventions
When you name the new Dataverse tables, the convention for the current data model is to start with the resource name defined. The FHIR resource naming convention includes capitalization. The current data model follows the convention of using the FHIR name and adding spaces between capitalized words for the label. For example, for the FHIR resource RiskAssessment, the table name becomes msemr_RiskAssessment with Risk Assessment as the label.
Individual fields follow a similar convention however the FHIR resource fields are camel case in the FHIR specification.
Common Fields for FHIR Resource Tables
For a table that represents a FHIR resource, some common fields are required to support integration with external FHIR services. These key fields link a record in Dataverse back to a record in an external system like Azure Health Data Services FHIR service.
For each root level table, the following fields should be included:
| Column Name/Label | Data Type | Notes |
|---|---|---|
| AzureFHIRID/Azure FHIR ID | String, 64 characters | Attribute used to store the GUID of the object in Azure FHIR. |
| AzureFHIRLastUpdatedOn/ Azure FHIR Last Updated On | DateTime, Date, and Time | Attribute used to store the date and time that the object was last synced with Azure FHIR. |
| AzureFHIRVersion/Azure FHIR Version | String, 50 characters | Attribute used to store the version of the object in Azure FHIR. |
These fields are used in later exercises, specifically when creating Entity and Attribute maps.
FHIR Resources and Expansion Tables
FHIR is a document centric specification, meaning that a single FHIR resource can contain arrays of sub elements. For example, Encounter includes a statusHistory element.
The element is an array of sub elements of type code and Period, both complex types. When an encounter is set, the FHIR resource can contain multiple pairs of status and period combinations, and there's no simple way of accommodating this using the standard fields in Dataverse.
To accommodate this list of child values, you create an expansion table, or a new table with a reference back to the parent. The naming convention for expansion tables is to start with the root level table and combine with child object name to form the expansion table name. In this example, you would have the Encounter table, and an expansion table that would be named Encounter Status History.
This table includes a reference back to the parent Encounter table and then the statusCode, or Choice, field, and a periodStart and periodEnd Date Time fields.
Shared multi-table Dataverse lookups
Many FHIR resources contain common structures across FHIR resources. For example, many FHIR resources contain an array of identifiers, such as Patient, Claim, Location, and Explanation of Benefits.
Instead of a duplicating a set of expansion tables for each FHIR resource, you're provided a common Identifier table that contains the required fields based on the Identifier data type. This table is then linked back to the related tables using a multi-table lookup named Parent Resource.
When creating a new table that requires the array of identifier structure, you update the existing Identifier table Parent Resource multi-table lookup to include a reference to your new table. The same approach applies to Appointment and Annotation reference types.
Next
In the next exercise, you'll apply these best practices in building a new set of tables for a selected FHIR resource, beginning with a design, and a simple diagram.