To illustrate the procedure to create a basic Excel connection, the following example is used.
You want to exchange model information between Enterprise Studio and Excel about application components, their business value, and their data flows (relationships with data objects).
To realize the information exchange, the following connection with two mapping tables will be created:
- Add an Excel connection as basis for the mappings.
- Define two mappings, one with a regular table for the application components and their business value, and one with a relation cross-reference table for the relationship between the application components and data objects.
- Define the Excel worksheets and their columns, rows and cells by configuring the mapping tables.
- Define the model data on the Enterprise Studio side by adding metaobjects and their properties, and metarelations.
- Map the model data from the tool side to the Excel worksheet by creating relations between the model elements and the mapping tables.
There is no prescribed order in which the table and the meta-elements of a mapping should be configured to create the mapping. The preferred order may depend on what you want to use the connection for.
If you use it for import, you may prefer to start with the table, since you have an Excel file as starting point. If you want to export, you may prefer to start with the model data from the tool side by adding the meta-elements.
On this page:
Adding a connection
To add a new Excel connection to your Connection model, follow these steps:
- In the Create pane, click the Excel connection
, click in the open connection view, and type the name of the new connection. Select the connection object, and then click
to set the connection properties. Options with a check mark are activated, options with a cross are deactivated. Click the check mark or cross to change the status.
- When you are ready, close the settings window
Defining mappings
After the connection has been created, one or more mappings must be defined. One mapping represents one Excel worksheet.
For the example connection, two mappings are defined. One with a regular table, and one with a relation cross-reference table.
- In the connection view, select the Excel connection object, and then click:
to add a mapping with a regular table
to add a mapping with a relation cross-reference table Name the mapping. It will also be the name of the table in the mapping.
If you are using the connection for export, this name will be the name of the worksheet in Excel. Take note that Excel has a few limitations with respect to naming worksheets.
If you are going to use the connection to import data, make sure the name of the mapping/table matches the name of the Excel worksheet you want to import from!
For the example connection, the result is a connection with two mappings:
Configuring tables
Next step is configuring the tables to correctly represent the Excel worksheet you want import data from or export data to. Do this for each mapping you have added.
For the example connection, both tables are configured, the regular table and the relation cross-reference table.
- Open a table by double-clicking the mapping. A new diagram opens containing an empty mapping table. Depending on the chosen mapping, it is a regular table or a relation cross-reference table.
Regular table only: Determine the number of needed columns for the Excel worksheet, add the additional columns and name them.
If you are going to use the connection for import, you can quickly add the columns by importing them from the Excel file instead of adding and naming them manually. See Importing table column headings from Excel.
To add columns manually, select a table column, and then click the plus sign on the left or right side to add a column to the left or the right.
The total number of columns must equal the number of relations you want to create between the model data and the mapping table. In the end each table column must have a mapping relation attached.- Name the table columns. The names represent the column headings in the Excel worksheet. To name the columns, you have the following possibilities:
Name automatically: In that case do nothing yet; when you further on in this procedure create relations between the meta-elements and table columns that have not been named yet, the column automatically gets the name of the linked meta-element.
Name manually: Select the column, press F2 and type the name.
- Regular table only: By default the first table column is set as the population column. The population column is the column that defines the population for an export, the objects in this column will be taken as a starting point for the export. If you want to set another column as the population column, select that column, and then click
Optional: If the first column and row of the table in the Excel worksheet starts (for import) or must start (for export) in another than the first column or row, then specify the start column and row number for the table. Do this for each table, if needed.
Select the table title, click , and set the following properties.
- When you are ready, close the properties window.
For the example connection, the result of configuring the tables is a regular table with three columns, and a relation cross-reference table:
Adding metaobjects and metarelations
Next step is adding metaobjects and metarelations to define the model data from Enterprise Studio that must be mapped to the Excel worksheet. In case of a regular table, add one metaobject or metarelation. In case of a relation cross-reference table, add two metaobjects and a metarelation.
After adding the meta-elements, properties may need to be added to the meta-elements if the mapping tables contain defined properties.
In case of the example connection, one metaobject including properties is added to the regular mapping table, and two metaobjects and a metarelation are added to the relation cross-reference mapping table.
Additionally, properties are added to the metaobject for the regular mapping table.
Adding a meta-element
To add a metaobject or metarelation to a mapping, follow these steps:
- In the model browser, select a model object or relation of the type you want to add to the mapping, and drag it onto the diagram. It can be any object or relation.
A metaobject or metarelation is placed on the diagram. It has the name of the element type of the selected element. In the figure below a metaobject representing application component is added by dragging an application component.
Alternatively you can add a meta-element by using the Create pane or the quick-create pop-up window. - Only for metaobjects, optional: By default, any children of an object of the selected object type will automatically be included in an import or export. If you do not want any child elements to be included, do as follows:
- Click the object type, and then click the
- On the General tab next to Include children, click the check mark
- Click the object type, and then click the
Adding meta-element properties
If one or more columns in the table represent object or relation properties, these properties also need to be added to the metaobject or metarelation in order to be mapped to the table columns. These properties can be an attribute, metric, tag, and metadata.
To add a property to a meta-element, do as follows:
Select the meta-element, and then click the control of the type of property you want to add. If needed, add multiple properties.
attribute
Add an attribute. In the selection window that appears, select an attribute, and click OK. Example:
metric
Add a metric. Metrics are used to describe numerical properties of objects. In contrast to attributes metrics can be added to a model, whereas attributes have to be introduced in the tool configuration.
In the selection window that appears, select a metric from the model package, and click OK. Example:
metadata
Add metadata. Two types of metadata are available: ID and type name of the element. Metadata can be exported and can (only) be used for matching during import. This is because type name and ID cannot be changed for existing objects. For more information about using metadata as property, see Using metadata as meta-element property.
In the metadata selection window, select the desired metadata type, and click OK. Example:
tag
Add a tagged value. A tagged value represents some data that is linked (tagged) to an object. Tagged values are meant for advanced usage such as complex viewpoints (scripts) or complex import scenarios, but can also be used for properties that are not of interest to the end-user, but which are used for matching existing elements from earlier imports or exports.
Tagged values should typically not be used to model properties of an object, metrics and attributes are used for this. More information about using tagged values can be found in the BiZZdesign Scripting Reference.
After clicking the control, type the name of the tag, and click OK. Example:
For the example connection, adding metaobjects and metarelations and adding properties to them has resulted in the following:
Regular table
Relation cross-reference table
Creating relations between the mapping elements
Now, relations must be created between the model data and table in the diagram for the actual mapping of the data. The mapping relation is used for this. Each column in a regular mapping table must have a mapping relation attached that connects the column with a metaobject, metarelation, or its properties (attribute, metric, tag, or metadata).
The link relation is used for connecting metarelations and metaobjects. Which relations you need to create, depends on your mapping. In the end, each column or cell in the mapping table must have a relation attached.
For the example connection, mapping relations between the regular mapping table and the metaobject and its properties (attribute and metadata) are created, and mapping relations between the relation cross-reference table and the metaobjects and metarelation.
Link relations are created between the metaobjects and the metarelation in the mapping with the relation cross-reference table.
Creating a mapping relation
In a mapping with a regular table, a meta-element is linked to the table column representing the element type, any meta-element properties are linked to the columns representing the respective properties. In a mapping with a relation cross-reference table, the metaobjects (or their property, if set) are linked to the end cells of the table representing the column and the row. The metarelation is linked to the central cell representing the cross-reference.
If you have not named the table columns yet, they will automatically be named the moment you map them to a meta-element or property. They will get the name of the meta-element or the property they have been mapped to.
In case you draw a mapping relation to a column that already has a mapping relation, a new column is added to the left or right of the column, depending on where you attach the relation to the column (left or right from the center).
To create a mapping relation, do as follows:
- Select the meta-element or a property in the meta-element, and draw a mapping relation column or cell in the mapping table has a relation attached.
Regular table
Relation cross-reference table to the table column or cell representing the element type or property. Do this for every element and property present in the mapping until each
Relation lines are automatically laid out optimally when connecting the components. That is default behavior for modeling a mapping. In case a relation does not have the optimal layout, use the lay out the mapping relations.
control on a selected relation toCreating a link relation
To create a link relation, do as follows:
- Select the metaobject that must be linked to the "from" connector (endpoint) of the metarelation, and draw a link relation
For the example connection, the result of creating the relations looks as follows:
Regular table
Relation cross-reference table
Your mapping may not look as organized as the example above; the relations and connectors are probably positioned differently. In order to make your mapping look more organized, you can reposition one or more relations in the mapping by dragging them, and relocate the "from" and "to" connectors of the metarelation.
Specifying mapping options for import and export
Once mapping relations have been created, their settings for import and/or export must be specified. For each mapping relation in your connection mapping(s), specify the mapping options that are relevant to your use of the connection. Depending on what you are going to use the connection for, set the options for import or export, or for both.
Theavailability of an option in a mapping relation and its default setting may depend on the mapping table in which the relation is created (a regular or relation cross-reference table), and the component the mapping relation is attached to (metaobject, metarelation, or property).
Import and export options of a mapping relation for a metaobject in a regular table
To specify the properties for a mapping relation, follow these steps:
- Select a mapping relation, and then click
Click the Import tab, or the Export tab to specify the settings.
Import:
Import and export
Export
- When you are ready, close the settings window.
Your Excel connection is now completed.