Creating a New Transformation Specification for FHIR SQL Builder

Adding a Primitive Element to the Schema

Select a primitive element to open a panel that allows you to add the element to the schema. For primitives, such as strings or numbers, you can do the following:

• Click Show Histogram to view a histogram of the unique values in the repository.

• Edit the name of the column that will appear in the table by editing the Column name field.

• Select the Index check box to add an index on this column in the table.

  For information regarding which elements you may want to add an index to, see “What to Index” in the SQL optimiztion documentation.

• Select the Store a copy check box to store this as a column in your set of locally-stored columns. This is most useful for data in collections.

Click Add To Projection to add the element to the Projection, including adding it to an index and/or the local copy depending which checkboxes you selected.

The Currently Selected Items table on the edit page shows the current state of the schema. It has columns the following columns:

• Table – The name of the table the added column is for. You can add elements from multiple different resources to a schema in a Transformation Specification; theses elements will appear in the tables as specified by the values in this column.

• Column – The name of the column that will appear in the table.

• Type – The datatype of the contents of the column.

• Index – Indicates whether an index will be created for the column.

• Store a copy – Indicates whether the column will be added to the local copy.

• Actions – Provides buttons you can use to edit or delete the specific element.

  • Edit – Opens the panel that you used to add the element to the schema, enabling you to edit your preferences for this column, including setting filters, in the schema.

  • Delete – Deletes the column specified by the from the schema.

You can add as many elements to the schema as needed. Click Done to return to the home page, where you will find the new specification listed.

Adding Data from Collections to the Schema

Some elements have collections associated with them. Selecting a collection will bring up a selection panel where all the elements at the bottom node of the collection are listed in rows. An example, using the Identifier collection of a Patient, is below. Note that this particular collection has both a object and primitive elements within it.

Click the check boxes next to the primitive elements you would like to add to the projection. When adding data from a collection in this manner, the Builder will automatically create a subtable for this data and it will not be included in the table of the parent resource. The name of the table is identified by the Subtable name field, which you can edit. The rows of this table correspond to individual entries of the collection and the columns are the elements of the collection you have specified, plus some special columns that help identify what resource they came from and where in the collection they appear.

However, it is sometimes desirable to have data from a collection stored within a table representing the resource it came from and not in a subtable. To do so, you should navigate to a primitive element you would like to add by clicking through the collection and selecting Add to Projection. However, this action will automatically populate the column with the value of the first element of the collection. As this element is likely arbitrary, it is not recommended to leave the column as such. Instead, you should use the Filter option.

The Filter option allows you to choose specific data from a collection to include in the projection. From any leaf in the resource’s tree, you are able to click on a preceding node in the full path, except for the initial resource you are drawing from (for example, the Patient resource name) or the leaf itself (for example, the terminal display node). In the example below, the filter has been created by clicking on coding; it is usually best practice to set a filter on the deepest node of the resource tree. You can set conditions to determine which fields to show in the projection. The filter is set to include the display element of the item in the collection where the code element equals “SS.”

The table projected by this transformation will show the display element of the coding objects that had a code of “SS.” However, if the filter was set on code elements equal to “MR,” the table will show the display element of the coding objects that had a code of “MR.”

You can use the filter option with different operations, not just equals. Additional operations include greater than, less than or equal to, greater than or equal to, exists, like regex, resolves to type, and more. You should configure a filter that fits your needs.

You can also add multiple filters for a particular element to finely tune which data appears in your table; however, multiple filters cannot be set on the same tier of a path. Additionally, you cannot set a filter on elements of a subtable; instead, you must set a filter on the subtable itself, which will then include only the elements that meet the filtered condition.

Note that in some cases, a collection may not include an object that matches the filter you have specified. In these cases, the rows that do not contain such elements will not have any data in that column.

Adding an Object to the Schema

To add an element of an object associated with the resource, click through the object’s elements until you get to a primitive element as described in “Adding a Primitive Element.”

You are allowed to set a filter on objects, as you can with collections. In this case, the filter is most useful as a method to remove any values from the table that do not match a certain criteria. In the example below, the filter is used to show the start element of a CarePlan when the end element is January 31, 1969. In the resultant CarePlan table, rows that did not end on that date will not have any data in the start column.

Viewing Transformation Specifications

On the main FHIR SQL Builder page, you will see your Transformation Specifications listed under the header. Columns in the listing provide the following information:

• Name – The name of this specification.

• Analysis – The analysis used to create this specification.

• Description – The description of the transformation specification as defined when it was initially created.

• Last Modified – The date and time the specification was last modified.

• Actions – Provides buttons you can use to manage the specification.

  • Edit – Opens the Edit Transformation Specification page.

  • Copy – Opens the Copy Transformation Specification dialog box to create a new specification identical to this one. For more information, see “Copying a Transformation Specification.”

  • Export – Downloads a JSON file of the transformation specification that can be shared and imported. For more information, see “Exporting a Transformation Specification.”

  • Delete – Deletes the transformation specification. A transformation specification cannot be deleted if a projection depends on it.