Exercise - Design Dataverse Tables from FHIR Resource
In this exercise, you design a new set of Dataverse tables that represent the ServiceRequest Fast Healthcare Interoperability Resources (FHIR) resource and build the root level table for Service Request. You review the ServiceRequest specification and create an entity relationship diagram using the guidelines and approach in the previous overview exercise. This first step is to convert the FHIR document centric model into root tables, expansion tables, mapping field data types, and defining the relationships between tables.
Note
When you complete these exercises, it won't result in a full representation of each Service Request data element. Instead, you'll focus on key elements and steps required to extend the Healthcare data model for Dataverse.
Entity Relationship Diagram
Now that you reviewed the conventions, you can design the proposed data model in the form of a simplified entity relationship diagram. Because you aren't designing a full database schema, you won't be designing a formal database diagram that you might build for SQL server tables. Instead, you're building Dataverse tables that provide a façade over the underlying data store. The diagram shows the new tables, how they relate to each other, how they relate to existing tables, and key data elements that need special attention.
An example of a simplified entity relationship diagram displaying a subset of tables for Encounter from the Healthcare data model for Dataverse looks as follows.
This diagram displays the Encounter root table and expansion tables, and several key relationships to other tables in the data model.
We recommend you communicate the proposed data model with your stakeholders, review the design, and discuss the possible issues to save rework during development.
ServiceRequest FHIR resource specification
The FHIR resource specification for the ServiceRequest that you use for the design is at the following link. The specification includes background on the ServiceRequest, intended usage, and some examples on usage.
To build our Dataverse tables, you focus on section 12.14.4 Resource Content. This section provides the schema for the ServiceRequest data elements that you translate into fields and expansion tables. The previous exercise provided guidance on how the FHIR specification is structured and how you can use it to build Dataverse tables.
The first element of the diagram is the Service Request table as it sits at the root and derives from the DomainResource type.
ServiceRequest Dataverse Table
The root table in Dataverse represents the ServiceRequest FHIR resource. To begin with, you identify the table standard and Lookup columns. For this step, you focus on the elements in the specification with cardinality 0..1 and 1..1, meaning you're looking at FHIR resource elements that don't contain an array of child elements.
Standard Columns
Standard columns consist of Primitive and Complex types and map to common data types in Dataverse.
Because this column represents the root level FHIR resource, you need to include the three common fields that enable integration via the Azure Health Data Services.
| Schema Name | Display Name | Data Type | Description |
|---|---|---|---|
| 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 | Date and Time 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. |
Based on the specification, you can add the following fields to represent the ServiceRequest columns:
| Schema Name | Display Name | Data Type | Description |
|---|---|---|---|
| status (required) | Status | Choice values: draft, active, on-hold, revoked, completed, entered-in-error, unknown |
Status code for the Service Request. |
| intent (required) | Intent | Choice values: proposal, plan, directive, order, original-order, reflex-order, filler-order, instance-order, option |
Intent code for the Service Request. |
| Priority | Priority | Choice values: routine, urgent, asap, stat |
Priority code for the Service Request. |
| doNotPerform | Do Not Perform | Yes/No | True if service/procedure shouldn't be performed. |
| authoredOn | Authored On | Date and Time Date and Time |
Date request signed. |
| patientInstruction | Patient Instruction | String multi-line, 2,000 characters |
Patient or consumer-oriented instructions. |
Note on the column definitions:
The actual Schema Names for both tables and columns in Dataverse include a Publisher specific prefix. This information is covered in the next exercise when you build the ServiceRequest table in the Power Platform maker portal.
The status and intent fields are tagged as (required) because the FHIR specification indicates a cardinality of 1..1.
The ServiceRequest diagram is as follows:
The extra fields you can create for ServiceRequest include fields having one of several different values, that is, formatted as nameB[x] in the FHIR specification, and are composed of complex types, or types composed of more than one primitive types. As outlined in the previous exercise Complex Types section, these Types result in multiple table columns to capture data elements from the FHIR resource.
The following table represents extra columns that capture complex types in the FHIR specification for ServiceRequest:
| Schema Name | Display Name | Data Type | Description |
|---|---|---|---|
| quantity[x]: Service amount. One of these | |||
| quantityQuantity | Quantity – expand into the following fields | ||
| quantityQuantityValue | Quantity Value | Decimal | Numerical value (with implicit precision) |
| quantityQuantityComparator | Quantity Comparator | Choice values: <, <=, >=, > | How the actual value compares to the given value |
| quantityQuantityUnit | Quantity Unit | String 50 characters. | Unit representation |
| quantityQuantityUri | Quantity Uri | String 500 characters. | System that defines coded unit form |
| quantityQuantityCode | Quantity Code | String 50 characters. | Coded form of the unit |
| quantityRatio | Ratio – expand into the following fields | ||
| quantityRatioNumeratorValue | Quantity Ratio Numerator Value | Decimal | Numerical value (with implicit precision) |
| quantityRatioNumeratorComparator | Quantity Ratio Numerator Comparator | Choice values: <, <=, >=, > | How the actual value compares to the given value |
| quantityRatioNumeratorUnit | Quantity Ratio Numerator Unit | String 50 characters. | Unit representation |
| quantityRatioNumeratorUri | Quantity Ratio Numerator Uri | String 500 characters. | System that defines coded unit form |
| quantityRatioNumeratorCode | Quantity Ratio Numerator Code | String 50 characters. | Coded form of the unit |
| quantityRatioDenominatorValue | Quantity Ratio Denominator Value | Decimal | Numerical value (with implicit precision) |
| quantityRatioDenominatorComparator | Quantity Ratio Denominator Comparator | Choice values: <, <=, >=, > | How the actual value compares to the given value |
| quantityRatioDenominatorUnit | Quantity Ratio Denominator Unit | String 50 characters. | Unit representation |
| quantityRatioDenominatorUri | Quantity Ratio Denominator Uri | String 500 characters. | System that defines coded unit form |
| quantityRatioDenominatorCode | Quantity Ratio Denominator Code | String 50 characters. | Coded form of the unit |
| quantityRange | Range – expand into the following fields | ||
| quantityRangeHighValue | Quantity Range High Value | Decimal | Numerical value (with implicit precision) |
| quantityRangeHighUnit | Quantity Range High Unit | String 50 characters. | Unit representation |
| quantityRangeHighUri | Quantity Range High Uri | String 500 characters. | System that defines coded unit form |
| quantityRangeHighCode | Quantity Range High Code | String 50 characters. | Coded form of the unit |
| quantityRangeLowValue | Quantity Range Low Value | Decimal | Numerical value (with implicit precision) |
| quantityRangeLowUnit | Quantity Range Low Unit | String 50 characters. | Unit representation |
| quantityRangeLowUri | Quantity Range Low Uri | String 500 characters. | System that defines coded unit form |
| quantityRangeLowCode | Quantity Range Low Code | String 50 characters. | Coded form of the unit |
| asNeeded[x]: Preconditions for service. One of these | |||
| asNeededBoolean | As Needed Boolean | Yes/No | |
| see following table second asNeeded component |
Notes on the column definitions:
The single FHIR resource element quantity is expanded to 23 Dataverse columns because of the combination of complex types. You have limited control over how many data elements to implement, but this selection of fields allows you to support those defined in the FHIR specification. This highlights the complexity of the FHIR specification and creating a relational data model to capture FHIR data. This isn't the only approach to the data model, but these conventions allow interoperability with the Dataverse Healthcare APIs.
The Schema Names follow a naming convention like the root and expansion tables, but a single use of Quantity in the Display Name makes it a bit more readable. Naming for both is open to your discretion, but this example follows some conventions used by the Microsoft team.
The string lengths for Quantity Unit, Uri, and Code are dependent on the expected values for a ServiceRequest. Sample messages can be found for each FHIR resource under the Examples tab.
The code column data type for Quantity code values is dependent on the system value and the specification doesn't define a set list of options. Because system values might vary widely, providing a Choice with a large number of items isn't practical, so it's mapped to a string. If your solution has a known, fixed set of values, you could use a Choice data type.
The updated ServiceRequest diagram is as follows:
Lookup Columns
FHIR resource elements of type Reference with cardinality 0..1 and 1..1 can be mapped to Dataverse Many-to-one relationships, or Lookup fields on our ServiceRequest table. These Lookup references define the first set of relationships between ServiceRequest and other tables, some of which aren't yet be implanted in the Healthcare data model for Dataverse.
The following table represents extra columns that capture complex types in the FHIR specification for ServiceRequest:
| Schema Name | Display Name | Data Type | Description |
|---|---|---|---|
| asNeeded[x]: Preconditions for service. One of these | |||
| asNeededCodeableConcept | As Needed Codeable Concept | Lookup, single CodeableConcept | As needed SNOMED code |
| code | Code | Lookup, single CodeableConcept | What is being requested/ordered |
| subject | Subject | Lookup, multi-table Device, Group, Location, Patient | Individual or Entity the service is ordered for |
| encounter | Encounter | Lookup, single Encounter | Encounter in which the request was created |
| performerType | Performer Type | Lookup, single CodeableConcept | Performer role |
Notes on the column definitions:
You included the references to the following tables in your design:
- CodeableConcept - msemr_codeableconcept
- Encounter - msemr_encounter
- Device - msemr_device
- Group - msemr_group
- Location - msemr_location
- Patient - contact
Subject is defined as a multi-table Lookup. You find existing tables in the Healthcare data model for Dataverse with multiple fields for Subject and named Subject (Patient), Subject (Group), or similar. These tables were created before multi-table lookups were made available in Dataverse.
Creating multi-table lookups is outlined in the following sections.
The updated ServiceRequest diagram includes the referenced tables and new Lookup fields:
The diagram displays the root tables, relations to existing tables, and existing relationships between tables like Contact (Patient) and Codeable Concept. You can extend this type of diagram to include many more tables if needed and can be used to plan around existing tables within Healthcare data model for Dataverse.
Next
Now that the design is set for the root level table, the next exercise will begin the process for creating the corresponding Dataverse tables, their columns, and relationships. You use the previous table definitions to create a solution and start building the ServiceRequest tables.