The model data exchanged by an Excel^® connection is organized into mappings. A connection can have multiple mappings, each specified in its diagram. 

On this page:

• Mappings
• Mapping types
• Examples of connections

Mappings

A mapping defines which and how a single Excel worksheet represents model objects and properties in Enterprise Studio. A mapping consists of the following components:

• Sheets, columns, rows, and cells of an Excel worksheet, represented by a table.
• Model object types and their properties from Enterprise Studio, represented by metaobjects.
• Model relation types and their properties from Enterprise Studio, represented by metarelations.
• Relations between the Enterprise Studio model elements and the Excel worksheet.

Mapping types

Two types of mappings can be used in an Excel connection: mappings with a regular table and mappings with a relation cross-reference table. Which mapping you use depends on the type of table in Excel for the data.

Mapping with a regular table

If the data in the Excel worksheet is (import) or should be (export) organized in a regular table with columns to represent model information, use this mapping. A typical mapping consists of a table with multiple columns and one metaobject or metarelation with one or more properties linked via mapping relations to each table column.

General structure of an Excel mapping with a regular table

Mapping with a relation cross-reference table

If the data in the Excel worksheet is (import) or should be (export) organized in a relation cross-reference table showing relations between rows and columns, use this mapping type. A typical mapping consists of a relation cross-reference table, two metaobjects representing the table row and table column data, and a metarelation representing the relations between them. The meta-elements and the table are linked via mapping relations.

General structure of an Excel mapping with a relation cross-reference table

Examples of connections

Excel connection for an ArchiMate model

In the following example, a connection has been created to exchange information from an ArchiMate^® model between Enterprise Studio and Excel about application components, their business value, and their data flows (relationships with data objects).

Connection

The following figure shows the "IT" connection with mappings "Applications" and "Data flows". "Applications" is a mapping with a regular table, "Data flows" is a mapping with a relation cross-reference table.

Example of an ArchiMate Excel connection with two mappings

Mapping 1 (with regular table)

The figure below shows the "Applications" mapping between Enterprise Studio application components and the columns of the Excel worksheet "Applications".

Excel mapping with the mapping table "Applications"

The metaobject "Application component" represents objects of type "Application component" in ArchiMate, including some properties of these objects (Business value and ID). The table "Applications" represents a worksheet in an Excel file, including columns "A", "B", and "C", The dashed lines (relations) model the actual mapping between the elements.

The figure below shows the results of an export to Excel. The columns "Application component", "Business value" and "ID" show the associated information of all the application components.

Excel workbook "Applications" with a regular table

Mapping 2 (with relation cross-reference table)

The figure below shows the "Data flows" mapping for a relation cross-reference table. The mapping specifies a matrix of access relations between application components and data objects.

Excel mapping with the "Data flows" relation cross-reference table

The application component of some access relation is represented by its row, and the data object by its column.

The figure below shows the export result to Excel. The cross signs X in the cells represent an access relation between a specific application component and a data object.

Excel workbook "Dataflows" with a relation cross-reference table

Excel connection for an ERD model

In the following example, a connection has been created to exchange information between an ERD model in Enterprise Studio and Excel about entities and their attributes, the attributes' types, and whether they are primary keys.

Connection

The following figure shows the "Webshop" connection with mapping "Entities and Attributes". It is a mapping with a regular table.

Example of an ERD Excel connection with one mapping

Mapping

The figure below shows the "Entities and Attributes" mapping between ERD entities and attributes in Enterprise Studio and the columns of Excel worksheet "Entities and Attributes".

Excel mapping with the mapping table "Entities and Attributes"

The metaobject "Entity" on the left represents the entities in the ERD model, the metaobject "Attribute" represents the entities' attributes, including properties of these entities (attribute type and primary key). The metaobject "Entity" on the right represents attribute basic types that have been defined in the ERD model, as is shown in the figure below.

Mapping model context of the entity representing attribute types, and the attribute types defined in the ERD model

The table "Entities and Attributes" represents a worksheet in an Excel file, including columns "A", "B", "C", and "D". The actual mapping between the elements is modeled by the dashed lines (relations).

The figure below shows the results of an export to Excel. The column "Association" shows the association relations, the columns "From class", "To class", "From role", "To role", and "from end.aggregation" show the associated information of the relations.

Excel workbook "Entities and Attributes" with a regular table

Excel connection for a UML model

In the following example, a connection has been created to exchange information between a UML^® model in Enterprise Studio and Excel about classes, the association relations between them, and their relation roles.

Connection

The following figure shows the "Insurance" connection with mapping "Associations". It is a mapping with a regular table.

Example of a UML Excel connection with one mapping

Mapping

The figure below shows the "Associations" mapping between UML classes in Enterprise Studio and the columns of Excel worksheet "Associations".

Excel mapping with the mapping table "Associations"

Two metaobjects "Class" represent the classes at both ends of the association relations ("from" classes and "to" classes) in the UML model. The metarelation "Association" represents the association relation between these classes, including their "from" role and "to" role, and the association type of the "from" end of the relation. The table "Associations" represents a worksheet in an Excel file, including columns "A" to "F". The actual mapping between the elements is modeled by the dashed lines (relations).

The figure below shows the results of an export to Excel. The column "Association" shows the association relations, the columns "From class", "To class", "From role", "To role", and "from end.aggregation" show the associated information of the relations.

Excel workbook "Associations" with a regular table