Definitions can be imported into the schema designer using a simple JSON format. This supports the import of fields, entities and entity mappings.
The importer's support for mapping is limited to entity and field renames, for example to create synonyms for resolving parent-child links. Other mapping functionality is not currently supported through the importer.
JSON structure
Conceptually, the JSON contains tables of data:
- A field table that provides field definitions.
- An entity table that provides entity definitions.
- An entity_field table that specifies the fields in an entity.
- An entity_mapping table that specifies entity mappings.
- An entity_mapping_field table that specifies fields in an entity mapping, e.g. for column renames. (Note that this is not the same as field mapping, which is not currently supported.)
- A source_system table that specifies source systems.
- A source_system_entity table that specifies entities in a source system.
The import structure is not the same as the Source definition JSON because it allows field definitions to be reused. It is also a non-nested structure, to make it easier to produce from other systems.
Each table is held as an array of objects.
The high-level structure of the JSON is:
{
"entity": [
{entity object},
... more entities
],
"field": [
{field object},
... more fields
],
"entity_field": [
{entity field object},
... more entity fields
],
"entity_mapping": [
{entity mapping object},
... more entity mappings
],
"entity_mapping_field": [
{entity mapping field object},
... more entity mapping fields
],
"source_system": [
{source system object},
... more source systems
],
"source_system_entity" [
{source system entity object},
... more source sytem entities
]
}The object properties are described below.
entity
| name | Name of the entity. This must be unique within the import. |
| description | A description for the entity. May contain HTML markup. |
| reference | A reference for the entity, to be used as the table name. Optional. If not present, a reference will be built from the name. |
| data_store | The reference to the datastore. This ties up with configuration of the data hub to direct data to the correct database. |
| unique | Indicates that each set of input message contains unique records (i.e. facts). |
| unique_identifier | When unique is true, the name of the field that holds the identifier of a batch of unique records. |
| unique_sequence | When unique is true, the name of the field that holds the identifier or the record sequence number within the batch. |
field
| name | Name of the field. This must be unique within the import. |
| description | A description for the entity. May contain HTML markup. |
| reference | A reference for the field, to be used as the column name. Optional. If not present, a reference will be built from the name. |
| type | The field type. Must be one of the data types supported by in the schema designer. |
| length | For text fields, maximum length of the text field. |
| scale | For decimal fields, the total number of digits, including those after the decimal point. |
| precision | For decimal fields, the number of digits after the decimal point. |
| link_entity | For link fields, the name of the link entity. |
| autocreate | For link fields, whether link parent autocreation is required. |
| child_entity | For children fields, the name of the child entity. |
| parent_identifier | For children fields, the name of the field on the child that identifies the parent entity. |
| child_sequence | For children fields, the name of the field on the child that holds the child sequence. |
entity_field
| entity | Name of the entity. |
| field | Name of the field. |
| reference | Alternative reference to use for this field within this entity, rather than using the reference from the field itself. |
| key | Set to true to indicate that the field forms part of the logical key of the entity. |
entity_mapping
| name | Name of the entity mapping. |
| description | Description of the entity mapping. |
| entity | Name of the target entity. |
| source_reference |
Reference for the source entity (conceptually, the reference for the entity in the incoming data).
|
entity_mapping_field
| entity_mapping | Name of the entity mapping |
| field | Name of the field. |
| source_reference | Reference of the field in the source. |
| key | Set to true to indicate that the field forms part of the logical key of the mapped entity. |
source_system
| name | Name of the source system. |
| description | Description of the source system. |
| reference | Reference for the source system. |
source_system_entity
| source_system | Name of the source system |
| entity | Name of the entity |
| reference | Reference of the entity in the source (overrides that in the entity itself). |
Excel format
You can also import using a Microsoft Excel spreadsheet. This supports Excel 2007-onwards spreadsheets with a .xlsx extension, not earlier Excel versions with a .xls extension.
This follows the same pattern as the JSON imports. Have sheets for Entity, Field and Entity Field. Each of these should have the first row containing column headings that match the property names, and then rows of data.
Download blank import spreadsheet
The key field should contain the boolean TRUE or the string "true". "yes" or "#" will not be recognised.
SQL Power Architect import
You can use the importer to import entity and field definitions from a database design in SQL Power Architect, by uploading the .architect file. See Import from SQL Power Architect for more details.
Running the import
To run the import, go to Settings / Import definitions. Use the button to create a new import. Upload a file, provide a name for the import, select the definition library into which you want to import definitions, and indicate whether you intend to overwrite existing imports. Click on Next to start the import.
This will create an import node. Follow the green buttons through generate, validate and confirm to perform the import.