Series Overview

I’ve been working a lot with Sparx Enterprise Architect and ArchiMate over the past few years, and although I am a big fan of formalized modelling using ArchiMate I’ve found myself frustrated with some of the limitations of the Sparx EA tool.

Over a small series of posts I’m intending to share a few of the tricks I’ve developed over time to address these frustrations.  These posts are fairly technical in nature, and address the usage of Sparx EA as an architecture tool, as opposed to the practice of defining the architecture itself.  The other posts in this series are below;

• Sparx EA: Matrix Viewpoints Across Multiple Relationships
• Sparx EA: Embedding Diagrams within an Enterprise Wiki
• Sparx EA: Displaying Tagged Values on Diagrams
• Sparx EA: Preview Panel for Element Rollovers in HTML Exports
• Sparx EA: Using PowerShell to script queries against the model
• Sparx EA: Bulk Updates using PowerShell

Why Use PowerShell?

One of the challenges I’ve faced with Sparx EA 13.5 is the limitations of the scripting languages available to perform bulk operations on the model.

• The JavaScript implementation has an issue with invoking COM objects such as MSXML, so interacting with the XML responses from Repository.SQLQuery is challenging.
• VMScript and JScript no longer support debugging, due to the discontinuation of the Microsoft Script Debugger

To work around these issues, I decided to move scripting outside of Sparx EA and produce scripts that execute directly against the model. Initially I had intended to build this as a set of .NET console scripts, after a bit of experimentation the solution I eventually settled on was to use PowerShell scripts utilising the Sparx EA .NET client library, as using a scripting language has provided a lot more flexibility than a compiled console application.

Note that although in a lot of cases it’s possible to update the underlying database directly, I’ve opted to perform all operations via Sparx EA Object Model libraries in order to ensure the model remains in a supported state.

Connecting to the Model

The following script fragment initiates a connection directly to the model repository.  Note that the OpenFile command can take more than 10 seconds to connect, so it is worth storing the $repository variable for reuse across your PowerShell session.

add-type -path “C:\Program Files (x86)\Sparx Systems\ea\Interop.EA.dll”
$repository = New-Object EA.RepositoryClass
$repository.OpenFile(“connection string”)

I have been testing this using a connection string for SQL Server, however I’m fairly confident the same commands will work against an EAP file.  

Basic Model Queries

Most bulk operations against the Sparx EA model will start with querying for a set of elements (or connectors) to change. Using a SQL command to retrieve the element set is much more efficient than using the Repository class to iterate through elements within a package, as a specific filter can be applied to narrow down the result set.

Define the Model Query

The following script fragment defines a simple SQL query to search for all elements containing “Salesforce” in the name.   

$searchString = “Salesforce”
$sql = “SELECT o.Object_ID, o.Stereotype, o.Name as ElementName
             FROM t_object o
             WHERE o.Name LIKE ‘%$($searchString)%’ “

Execute the Model Query

The following fragment will execute the SQL statement against the model repository.

# Execute the search. The response is returned as an XML document
[xml]$response = $repository.SQLQuery($sql)
$nodes = $response.SelectNodes(“//*[local-name()=’Row’]”)

The key to note here is the PowerShell casting shortcut [xml], which will automatically convert the response string from the SQLQuery command into a .NET System.Xml.XmlDocument. This allows the use of all methods on the XmlDocument class, so the SelectNodes method can be used to execute a simple XPath returning each element row.

Creating a PowerShell Array

The following script fragment demonstrates how to loop through the XML nodes and create an array of PowerShell objects.  Although it is possible to query the XmlDocument directly, a lot of operations on the result set can be easier if an array of PowerShell elements is created first. 

$elementArray = @()
foreach ($node in $nodes)
{
    $element = New-Object PSObject -Property @{
        ElementID = $node.Object_ID
        ElementStereotype = $node.Stereotype
        ElementName = removeNewLine($node.ElementName)
    }
    $elementArray += $record
}

This allows more flexibility for further operations to be performed using native PowerShell features, rather than relying on complex XPath operations.  The statement below demonstrates the ability to further filter the result set (using the Where-Object command, and specify which properties to output.

$elementArray | Where-Object {$_.ElementStereotype -eq “ArchiMate_ApplicationComponent”} | Select-Object ElementID, ElementName

ElementID     ElementName
2142188216   Salesforce Sales Cloud
2142188217   Salesforce Marketing Cloud
2142188221   Salesforce Service Cloud

Next Steps

With the ability to query a set of elements (or connectors) from the model, the next step is being able to script the bulk update we set out to perform. In my next post I’ll share a couple of example update scripts I’ve found useful as a way to make day-to-day modelling within Sparx EA less time consuming.