Database Import/Reconcile

Home 

This feature allows specifications to be imported from existing ODBC database sources. Alternatively it can also be used to simply reconcile an MWB specification with one that exists in an ODBC data source or in fact, it may be used to do both i.e. import new segments and fields during the reconciliation process. Its main intended purpose is to afford a relatively quick method of MWB library creation from an existing database specification repository.

Most often, some preparatory work will have to be done on the database to produce a dataset that can be mapped to the record structure required by the MWB, but using tools like Microsoft Access make this a fairly straightforward and easy accomplished task. The required record structure is as follows: 

Database Field

Optionality

Comments

HL7 version

optional

If present this element will be used in the specification definition

Segment ID

required

This is the 3 chr segment identifier

Segment Description

optional

Full name of the segment

Field Name

required

The field name as it would appear in an HL7 spec

Field Sequence

optional

Position of field within segment

Length

optional

Field length

Data Type

required

HL7 Data type of the field

Optionality

optional

Optionality abbreviation should be one of the following: R,O,NS,RE,C,CE,B

Repeatability

optional

True or false; be sure to indicate how a True value is represented in your dataset (e.g. 1 or Y or T or TRUE etc)

Quantity Max

optional

Applicable if repeatability is true

Quantity Min

optional

Applicable if repeatability is true

Table

optional

HL7 table reference

Reference/Chapter

optional

May be any textual reference to be associated with a message element

Example Value

optional

 

Table 1
(MWB element attributes that may be mapped for import/reconcile process)

 The actual field names are not important, as the database fields will be mapped to MWB attribute fields. An example of how one of these tables might look is shown in table 2 below. This table was generated by an MS Access Make Table Query executed on a specification repository database.

 

interface

TLA

Name

api

Field_Name

Seq

Len

DT

Opt

Rep

Qty

Chp

H_TID

Version

6

BHS

Batch Header Segment

 

Batch Field Separator

001

1

ST

R

0

0

 

 

2.2

6

BHS

Batch Header Segment

 

Batch Encoding Characters

002

4

ST

R

0

0

 

 

2.2

6

BHS

Batch Header Segment

 

Batch Sending Application

003

15

ST

O

0

0

 

 

2.2

6

BHS

Batch Header Segment

 

Batch Sending Facility

004

20

ST

O

0

0

 

 

2.2

6

BHS

Batch Header Segment

 

Batch Receiving Application

005

15

ST

O

0

0

 

 

2.2

6

BHS

Batch Header Segment

 

Batch Receiving Facility

006

20

ST

O

0

0

 

 

2.2

6

BHS

Batch Header Segment

 

Batch Creation Date/Time

007

26

TS

O

0

0

 

 

2.2

6

BHS

Batch Header Segment

 

Batch Security

008

40

ST

O

0

0

 

 

2.2

6

BHS

Batch Header Segment

 

Batch Name/ID/Type

009

20

ST

O

0

0

 

 

2.2

6

BHS

Batch Header Segment

 

Batch Comment

010

80

ST

O

0

0

 

 

2.2

6

BHS

Batch Header Segment

 

Batch Control ID

011

20

ST

O

0

0

 

 

2.2

6

BHS

Batch Header Segment

 

Reference Batch Control ID

012

20

ST

O

0

0

 

 

2.2

6

BTS

Batch Trailer Segment

 

Batch Message Count

001

10

ST

O

0

0

 

0093

2.2

6

BTS

Batch Trailer Segment

 

Batch Comment

002

80

ST

O

0

0

 

0094

2.2

6

BTS

Batch Trailer Segment

 

Batch Totals

003

100

CM

O

-1

0

 

0095

2.2

Table 2
(illustrates portion of a database table used in import or reconciliation process)

 

Notice that the segment id (BHS or BTS in the example) is repeated for each constituent field of the segment. Notice too, that the attribute names in this table do not match those in table 1; they will be mapped to the MWB attribute names. The only attributes that a given database table must contain to execute this feature are the ones marked required in table 1 above. If you are planning to perform reconciliation only, you may only want to consider certain attributes. The attributes may be limited either in constructing the source table, or via the mapping in the MWB. If you are going to import a specification however, it only makes sense that you build your data source table to include as many of the element attributes as possible, otherwise you’ll have to manually provide attribute values after the importing process is completed. In either case the wisest approach is to construct the data table or stored procedure using as many attributes as are available. They may easily be masked out if necessary in the MWB mapping process.

 

This is a good time to point out that this process operates down to the field level only. It does not import component and sub-component element definitions from the database. Instead, it automatically generates and supplies the required sub-elements for a field based on the field’s data type and the currently active  MWB data type library. It is important then to ensure that the active MWB library corresponds to the HL7 version of the database specification that you are importing.

 

Procedure: 

Select Maint/Lib/Import Lib from ODBC on the main menu. This makes the DB Import tab visible in the MWB, which you should select. The process begins by selecting an ODBC data source. The MWB automatically provides a list of the available ODBC data sources advertised by your system. Select one from the drop-down list. Once you have selected an ODBC data source you may select a table or a stored procedure (query in MS Access) that is associated with the data source. Again, make your selection from the appropriate drop-down list (see figure 1 below).

 

Figure 1
(illustrates an ODBC Data source [Vista HL7] selected and a table dataset [segs AmbCare] about to be selected)

 

Once you’ve selected a dataset, the Database Fields list is populated. The objective now is to drag the database fields from the list over to their MWB counterparts on the left. As you drop the database fields onto the MWB message element attribute fields, you’ll notice some activity: the dropped database field will disappear from the database fields list; the MWB attribute field background will change to a green color indicating that a successful link has been established; and if the attribute has a value for the current database record it will be displayed in the MWB attribute field. See figure 2 below. Also, to see the database field name of a linked field, simply move the mouse over the green linked field. The database field name will be displayed in the status bar at the bottom of the window.

 

Figure 2
(illustrates 2 linked attributes and a third database field selected for linking)

 

If you mistakenly drop a database field on the wrong MWB field, you can correct the situation by dragging the MWB field attribute back over to the Database Fields List. This reverses the process, allowing you to make the proper mapping. As discussed above, certain fields must be mapped, but you may wish to leave others unmapped. Any unmapped fields will be left in their MWB default state for importing, or left in there original state if reconciling. As will be discussed below, you have other options to control the process.

 

When the mapping is complete, you will indicate whether you want to import or reconcile. The default is reconcile. A set of toggle buttons marked Select Activity are provided (see fig 2 above). They control which of the two action buttons are active. If you want to Import, click on the import radio button, which will cause the Import activity button to become active and the Reconcile button to be disabled. This toggle feature is provided as a safety against selecting the wrong activity button. This is necessary because as we’ll discuss below, this process may be accomplished incrementally and accidentally selecting the wrong activity may require that you start over again.

 

Figure 3
(Illustrating action and activity selection buttons)

 

Refer to figure 3 above. Notice the set of buttons underneath the Database Fields list in labeled Action. Select the Report only button (default) to perform a dry run of your selected activity. The result of this, is that a report will be generated showing you what changes would have occurred had you clicked the Update Spec button. When the activity is completed, you will automatically be switched to the Display/Reports tab to view the generated report. This feature allows you the opportunity to make adjustments to the attributes selected, their mapping, or in the case of reconciliation, to the object specification, before committing to the changes.

 

When you are satisfied with the report, click the Update Spec button, and click the appropriate activity button Import or Reconcile.

 

Importing

If you are importing, the result of the operation will be a report showing you exactly what was imported, and a specification displayed on the Message Definition tab. The Display/Reports tab will automatically be displayed when the process is completed. You may wish to print the report for later reference. Click on the Message Definition tab to view the imported specification.

 

Obviously, this is not a specification in the normal HL7 sense (see fig 4 below which illustrates the message structure from such a specification). It is really a list of segments and their constituent elements with attributes as defined in the originating database. Its real value is in the ability of this list of segments to be turned into an MWB library or to be added to an existing MWB library. That process is described in detail elsewhere, but simply put, you would save this specification as you would any other, you would then invoke the library create or library edit procedure, add this specification to the list of constituent specifications for the library, compile it and save it as a library.

 

Figure 4
(Illustrating the message structure resulting from a database import process) 

 

Reconciling 

The first requirement for reconciliation is to have a specification (referred to below as the object specification) loaded in the MWB. Once that is accomplished and the mapping completed as described above, you must decide on a reconciliation method. This process will take the attribute values from the database specification and modify the corresponding element attributes on the object specification that you have loaded, according to the modification method that you have selected.

 

Refer again to figure 3 and examine the Reconcilliation Method box. Three basic forms of reconciliation may be specified. The first is unconditional replacement. In this form, the database attribute value replaces its corresponding object specification attribute without any other consideration. In the second form, the object specification attribute value will be replaced only if the database-derived attribute has a value other than null or 0. So for example, if the object specification has a value of 26 for the length attribute, but the database has 0 for the length, the object specification value will not be changed. In the third form, the exchange will only occur if the object specification has a value of null or 0. If the object specification has any other value, the exchange will not occur.

 

Two other considerations may shape the reconciliation process. Again, referring to figure 3 above, you may elect to add new segments and/or new fields to the object specification. In regard to segments, if you select that option, any segment encountered in the database specification that is not found in the object specification will be added to the object specification. If you select the Add new fields… option, anytime the database-derived segment has a field not found in the object specification’s segment, it will be added to the segment in the object specification. The combination of these two features could be used quite handily to upgrade an older version object specification to a newer version database specification.

 

This is a good time to point out that the object specification need not be a standard HL7 message specification. Just as described above, the object specification may simply be a list of segments. As a matter of fact, there is a utility (maint/library/rebuild lib from segs) that will reproduce an MWB library in the form of a specification. You could use such a specification as the object of reconciliation. So for example, if you have a v 2.3.1 MWB library and v 2.4 in and ODBC database, you may want to create a specification from the 2.3.1 MWB library and reconcile it against the 2.4 database specification. The outcome of such an exercise would be a v2.4 specification that could be made into a v2.4 MWB library following the procedure outlined above.

 

Another aspect of the reconciliation process should be understood. Reconcilliation of any given segment takes place positionally. That is, the first field in the database segment is reconciled against the first field in the object specification’s corresponding segment. This is important to understand because if there is a discrepancy in the ordering of fields between the 2 segments, the object specifications field order will be changed to match the database specification. Such a situation may be evidenced by a large number of data type changes reported (which would also have a tendency to slow the process down quite a bit). Keep in mind too; that segments in the object specification that are not present in the database-derived specification will not be affected in this process.

 

As mentioned above, the reconciliation process may be executed incrementally if necessary. That is, for example you may perform a reconciliation on an object specification with a limited set of attributes selected, and then decide to add one or more other attributes and run the process again. Keep in mind however that there is no way to reverse any changes made in any given iteration of the process. If you find that you’ve made a mistake however, there is no real damage. You can always start the process over again by reloading the original object specification - provided that you have not saved the changes over the original object specification. It is recommended that whenever you perform a reconciliation that you either save a backup of the original object specification, or save the reconciled object specification under a different name.

 

Finally, when you have completed the importation or reconciliation, click the Close button, which removes the DB Import tab from the MWB.

 Home