Timed data in an ArchiMate® model can be imported in Enterprise Studio and exported using an Excel connection. Timed data can be, for example, life cycle stages with dates linked to them, or attributes and metrics with date-dependent values. An example of using timed values is importing life cycle data by defining a date for each possible life cycle stage, or by defining a life cycle stage for a selection of dates.
Life cycle view containing applications with timed life cycle stages and timed maintenance costs
An Excel connection for import or export of timed data has a mapping with a regular table with one or more columns for timed data and possibly columns for non-timed attributes or metrics. To be able to import or export timed data, the columns with timed data need to have a time-value mapping defined, or an additional TIME column needs to be added to the mapping table to represent the dates. Both solutions can be used.
If you are using an ADOdb connection, you can only use the solution with the TIME column.
On this page:
Because of the association between values of attributes and metrics and their associated time stamp (date), the mappings in the Excel connection are called time-value mappings. Two types of time-value mappings are available for the Excel import and export:
Date per value: With this mapping type a date is defined per value in the import or export. In the Excel worksheet the column heading is extended with an additional row that defines the value. Subsequent rows define the date associated with this value for the object represented by that row.
Example of export results when using value mapping "date per value"
Value per date: With this mapping type a value is defined per date in the import or export. For the Excel worksheet it means that the column heading is extended with an additional row that defines the date. Subsequent rows define the value associated with this date for the object represented by that row.
Example of export results when using value mapping "value per date"
Instead of setting a time-value mapping type in columns with timed data you can choose to add an additional TIME column to the table. The TIME column represents timestamps (dates). The timestamp in the TIME column applies to the columns with timed data. These columns only contain a value in case for the corresponding metric or attribute a value is defined for that specific timestamp.
When using a TIME column, multiple rows are used, one row for each possible timestamp at which one or more metric or attribute values are defined. This is shown in the following figure.
Example of export results when using a TIME column
To show the use of timed data in an Excel connection, an example connection will be created for exporting the dates of the applications' life cycle stages, and the maintenance costs of the applications on defined dates. First a basic connection is created, then configuring the connection with both solutions is described.
Creating a basic connection
- Create a connection with a mapping with a regular table. Make sure to set the model context for the connection in the connection settings.
- Open the mapping, and add the needed table column(s) and meta-element(s) to the mapping. Also add one or more timed attributes or metrics.
A metaobject for Application components is added with attribute "Life cycle stage" and metric "Maintenance costs", both have timed data. The table has a total of three columns to represent the application components, the life cycle stages, and the maintenance costs in the Excel worksheet.
- Connect the meta-element(s), attribute(s) and/or metric(s) to the mapping table columns, and set the population column
The Application component column is set as the population column.
- Select the table column with the timed attribute or metric, and click
Both the timed attribute and the timed metric column are set as column with timed data. to set the column as a column with timed data. Alternatively you can activate Timed data in the column's properties window. Do this for each table column with timed data.
The basic connection is now ready. Perform one of the following two procedures to further configure the connection for exporting timed data.
Configuring the connection with time-value mappings
The result of exporting data with the example connection using time-value mappings would be following:
- In the properties window of each timed data column, in Time-value mapping, select the desired type of time-value mapping.
For the life cycle stage column the time-value mapping is set to "Define date per value", and to "Define value per date" for the maintenance costs column.
- Optional: If a time-value mapping is set to "Define value per date", the mapping relation between the timed attribute or metric and the table column has additional properties that can be set for export:
Export time-value mappings for all current dates: If activated, timed values for all associated dates will be exported. If you do not want that, deactivate the property, and manually define the dates for which the timed values should be exported in Time domain.
Basically, the connection is now ready for export. However, if you are going to use the connection for import, there are a few more settings. Continue with the following steps.
Import only: In the table properties window, set the following:
Activate Column heading names must match. If the names of the mapping table columns differ from the names of the associated columns in your the Excel worksheet, rename them in order to make them match the column names in the Excel worksheet.
Deactivate Column numbers must match.
By default the above settings on table level are derived from the settings set on connection level. If the settings on table level differ from those on connection level, the settings on table level will override.
- Optional, import only: If a table column with timed data represents money values, then text and number values to be imported from Excel can be converted to money values. To do this, activate Convert imported text/number to money values in the column's properties window, and select the desired Currency, if needed.
Configuring the connection with a TIME column
The result of exporting data with the example connection using a TIME column would be following:
Add an additional column to the mapping table, and set it as the TIME column by clickingin the column.
There is no prescribed position for the TIME column in the mapping table, but placing it at the most right can be a good practice. After exporting date, the column will also be positioned on the right in your Excel table. If you are going to use the connection for import, make sure the position of the TIME column matches its position in your Excel worksheet.
The result is a connection with a TIME column:
Basically, the connection is now ready for export. However, if you are going to use the connection for import, there are few more optional settings. Continue with the following steps.
- Optional, import only: If date values in the Excel worksheet have a text format instead of a date format, then they can be converted to date values when imported. To do this, activate Convert imported text to date values in the TIME column's properties window, and select the desired Date format.
- Optional, import only: If a table column with timed data represents money values, then text and number values from the Excel worksheet can be converted to money values when imported. To do this, activate Convert imported text/number to money values in the column's properties window, and select the desired Currency, if needed.
ArchiMate® is a registered trademark of The Open Group.