models
Data integrated with Metl can be structured or unstructured data. When dealing with Structured data, the structured data can be defined by modeling the data. Metl Models allow you to describe that structured data. Models can either be relational or hierarchical in nature. All structured data must be modeled in Metl. For example, a delimited file may exist that looks as follows:
Smith,John,male,10/19/2000 Smith,Sally,female,5/4/2004 Smith,Sarah,female,2/1/2015
This file represents people including their last name, first name, gender and date of birth. In order to use this file in a structured way, you must model the data contents of the file. I.E. define an entity named something like Person, and then define attributes for that person (lastName, firstName, gender, dateOfBirth).
A model in Metl can either be a relational model or a hierarchical model. The model type needed depends directly on the structure of the data to be modeled. Data coming from or going to a relational database, most commonly utilizes a relational model. A relational model has tables, columns, primary keys, foreign keys, etc. A relational model in Metl is largely like a relational model in any relational database platform. Hierarchical models are traditionally used when the data you are modeling doesn’t have explicit relationships defined in the data, but instead has relationships that are implicitly defined based on nesting of entities. Hierarchical models are commonly used for xml and json structures which are used in service based architectures.
To create a new model, click on the "Models" folder in the navigation pane, and then click "New" - "Model", and then either "Relational" or "Hierarchical" as shown below. For this example, we will create a Relational Model as the contents of the file will be sent to a relational database.
Once the model has been created, double click the model in the navigation pane in order to edit the model. From the content editor pane, there are buttons for adding and entities and attributes as well as positing those attributes within the model. Highlighting an entity and clicking the "Add Attribute" button will add an attribute to the given entity.
Similarly when dealing with data in a relational database, there is an inherent model defined in the relational database itself. I.E. tables and columns equate to entities and attributes. Metl relational models for relational databases can be automatically imported from the database structure itself, or they can be maintained manually. In order to import a model from a relational database, click the "Import…" button. The following screen will be displayed which displays all database resources. Drilling into a database resource allows the user to multi-select one or more tables in order for those tables to be imported into the model.
If a relational model contains several entity and/or attributes you can use a comma separated file to import the model layout. In order to import a model from a file, click the "Import…" button, then select "Relational CSV File". The following screen will be displayed where you can click the button to choose your file. Then click "Import".
The CSV file must contain only 5 columns with no header row. The columns are as follows:
Entity Name |
Required *first row of each Entity |
This is the name of your Entity, it is only required on the first row for a given Entity. Subsequent rows (attributes) will assume the prior Entity if none is provided. |
Attribute Name |
Required |
This is the name of the Attribute |
Description |
Optional |
A description of the Attribute |
Data Type |
Optional |
This is the data type of the attribute. Note: if none is provided or if the value is not a valid type, a type of "OTHER" will be used. These can then be manually edited later. |
Is Primary Key |
Optional |
This defines whether the attribute should be treated as the PK of the Entity. Valid values are 'Y', 'Yes', 'X' or 1. Any other value entered will be ignored and the attribute will not be marked as a PK. |
ENTITY_1,Attribute_1,Description of Attribute 1,INTEGER,X ,Attribute_2,Description of Attribute 2,VARCHAR(20), ,Attribute_3,,CHAR, ENTITY_2,Attr1,,BIGINT, ENTITY_2,Attr2,,STRING, ENTITY_2,Attr3,Key Field,INTEGER,Y ENTITY_2,Attr4,,DECIMAL(9,2),
If a source file contains a delimited header row of column names you can use this to create a relational model with attributes of the column names from the file. In order to import a model from the header row in a file, click the "Import…" button, then select "Source File Header Row". The following screen will be displayed where you will enter the Entity Name, Delimiter (single character delimiter ie: , \ | \t …) and click the button to choose your file. Then click "Import".
The source file must have a header row that contains a delimited set of column names to use as attribute names. The attributes will be created as VARCHAR data types and none will be selected as PK and no descriptions will be added.