Microsoft Excel Macros

After having the information exported from project plans to an Excel file, the next step is to manipulate that same data through the use of Excel macros, so the information in the template exported from MS-Projectcan be uniformed to be compliant with the templates that can be imported from Atlas. To be able to reach that unification, one needs to know what are the templates supported by Atlas, that is, the templates that contain all the information that Atlas needs to import so that the architectural representations can be reproduced.

As we already described, they consist in distinct files that correspond to each of the classes present in the metamodel adopted by the organization. An example of this file is in Fig.3.8, where the class and the properties shown match with the application component type of artefact.

Hereupon, we will have to transfer information from the taxonomy file (Fig.3.13) to the artefact tem-plates (Fig.3.8), in order to integrate the two technological components used in this dissertation. The taxonomy file will be exported through the ’Export’ macro (see section3.4.3) and the artefact templates through the method already explained (see Fig.3.9and Fig.3.10). Once we have all the useful infor-mation, the process of propagation can begin. This process will occur in a separate Excel file originated for this purpose, from now on called as ’workbook’, which will be where all the necessary macros will be located, and where the data will be transmitted and uniformed to comply with the right templates. To

accomplish the data spread six macros were created:

• GetTaxonomy - Copies the taxonomy file into the first sheet of the workbook (sheet1).

• GetSheets - Copies all the files located in the same folder as the workbook into the following sheets of the workbook (sheet!=1), by alphabetical order. Those files must correspond to the artefact templates extracted from Atlas in the beginning of the process (Fig.3.8).

• UpdateData - Propagates all the data (dates and impacts) from sheet 1, which corresponds to the taxonomy file descendant fromMS-Project(project plan), to the other sheets of the workbook, which correspond to the artefact templates supported by Atlas.

• ReverseData - Propagates all the data (dates and impacts) from every sheet of the workbook different than the first one, which corresponds to the artefact templates supported by Atlas, to sheet 1, that corresponds to the taxonomy file.

• SaveSheets - Saves all the changes made to the templates of the artefacts, i.e., to every sheet of workbook different than the first one, in the source files from where they were exported.

• SaveTaxonomy - Saves all the modifications made to sheet 1, that is, to the taxonomy file, in the source file from where it was exported.

In order to complement the previous macros, and to facilitate the process of updating the information, the RunMacros macro was also created. It is composed by two sub-macros that are responsible for the integration betweenMS-Projectand Atlas, in both sides, which means that one is concerned with keep-ing the information from the architectural models updated based on the information descendant from project plans (ProjectAtlas1), and the other is concerned with the other way around, that is, in keeping the project plans information updated based on the information descendant from the architectural repre-sentations (AtlasProject2). Those two sub-macros take advantage of the other six macros to be able to correctly update the information, as we can see below:

• ProjectAtlas1 - GetSheets + GetTaxonomy + UpdateData + SaveSheets, in this specific order.

• AtlasProject2 - GetSheets + GetTaxonomy + ReverseData + SaveTaxonomy, in this specific or-der.

The first sub-macro presented allows to update the information in the directionMS-Project− Atlas, that is, updating the architectural models based on the information coming from the project plans. It runs the macros GetSheets and GetTaxonomy to get both artefact templates and the important part of the project plan (taxonomy file) into the workbook, after they have been respectively exported from Atlas andMS-Project. Then, this sub-macro propagates the necessary information from the taxonomy file into

the respective artefact templates (sheets different than the first one of the workbook), with the help of UpdateData macro, and finally saves the latter, since the information was propagated in this direction and the taxonomy file has not changed. Having the artefact templates properly updated, the next step is to transfer this information to Atlas, so the blueprint queries can be executed and, consequently, the blueprint drawn. For this matter, a similar approach to the one for the exportation of the artefact templates must be done. Each artefact class has to be uploaded, so they must be sent to a compressed folder in order to be able to upload all the classes at once.

First, one must go to the ’Data Explorer’ menu and click on the icon symbolizing the upload. It is in the upper right corner highlighted in orange since it is being hovered, as we can see in Fig.3.14. Then, the import mode must be chosen from a predefined list, and it must be equal to ’destructive update’, as shown in Fig.3.15. This mode refers that the full content (properties and references) of data instances existing both in the repository of Atlas and in the file is set as in the file. Data instances existing only in the file are created with the contents as in the file, and data instances existing only in the repository remain unchanged. Finally, the ’Select files’ button must be chosen (Fig.3.16) so the project manager can pick the zipped folder with all the artefact templates updated, in order to upload them to Atlas. Based on this data, the enterprise architecture can be kept up-to-date with minimal effort.

Figure 3.14: How to import artefact templates: step 1.

Additionally, and in contrast, we have the second sub-macro that allows to update the information in the opposite direction, from Atlas toMS-Project. This means that project plans will be updated based on the information descendant from the architectural representations, because these may be modified and improved by the cartographer after consulting and analysing the blueprint. In a similar way to the example above, GetSheets and GetTaxonomy will be run in order to get the taxonomy file and the artefact templates into the workbook. After that, the ReverseData will be responsible for propagating the information changed in the architectural models and that have impact in the project management

Figure 3.15: How to import artefact templates: step 2.

Figure 3.16: How to import artefact templates: step 3.

domain, that is, in project plans, by updating the respective fields in the taxonomy file. Finally, this taxonomy file is saved into its original location, since this will be the only file being updated when running this sub-macro. After having this last file updated and saved, it is only needed to use the ’Import’ macro in Microsoft Project, in order to transmit the updated information to the project management tool. This way, the project manager will not have much effort in keeping project plans up-to-date on the basis of the models from the architecture.