Displaying items by tag: sql

If you want to analyze your EA model with SQL, you should consider using LINQ to SQL. Write the query once for all your EA database types. Use the combination of SQL like syntax, modern .net languages like C#, VB, F# and the compiler to find your typos.
 
The main benefits are:
 
  • Combine SQL like query language with the power of C#, VB, or F#
  • Database independent
  • IntelliSense, AutoComplete
  • Let the compiler find your typos
Approaches
 
AddInSimple shows you:
  • How to leverage LINQ to SQL in your Add-In (Example SimpleAddIn of open source hoTools)
  • LINQPad, the off-the-shelf interactive Scratchpad
In both cases: Before you reap the power of LINQ to SQL, you have to invest some time learning.
 
 
 
Example with LINQPad:
 
The following example shows the result of a little query against the EAExample model to see the types of Requirements.

You find standard SQL somehow old, limited, difficult to learn and has a lot of dialects? You think a Compiler can find a lot of your typos, Then give LINQ to SQL a try. You have two possibilities:

  • LINQPad  as an interactive query and debugging tool, database independent, C#, F#, VB
  • Develop your Add-In with LINQ to SQL

The main advantages of LINQ for SQL are:

  • Database independent
  • Combines the benefits of SQL like and Code (C#, F#, VB)
  • IntelliSense, Autocomplete, let the Compiler find your typos
  • Powerful Debug as you know from C# & Co.
  • LINQ provides a rich Eco-System (SQL, XML to name a few)

The LINQPad:

Ready to use Scratchpad to run, debug and visualize your Find and Debug for all your EA Databases. Show the code for the above grid:



IntelliSense, Autocomplete:

Let the Compiler find your typos.



Export to Excel:

 

Steps with LINQPad:

  • Create a new connection to your EA Database
  • Assign the connection to the query at hand

 

Look inside:

  • Combine queries and Code
  • EA Databases supported
  • Arbitrary complex queries without the limitations of SQL
  • Edit, Debug, Visualize
  • C#, F#, VB, LINQ (XML, SQL,..)

Add-In with LINQ to SQL:

You find two examples to tinker with in AddInSimple (part of hoTools). In essence:

  • Determine Database and connection string
  • Run LINQ query to DataTable
  • Output DataTable to EA Model Search
  • Enjoy

The results:

Start with AddinSimple and EAExample.eap.

 

 

Control Code:

The Query:

Summary:

LINQ to SQL is the solution for complex queries:

  • Interactive query tool LINPad
  • In your Add-In to easily handle complex queries database independent
  • Database independent
  • IntelliSense and AutoComplete
  • Compiler verifies against EA Database (no typos, type safe)
  • Comprehensive debug features

From viewpoint EA user:

  • Find in EA - everything without SQL limitations
  • Verify your EA models
  • Make metrics of your EA models

Write it once and use it for all your EA databases - regardless of location or type. Just connect your query with the new Database.

References:

 

This and more you can see at https://github.com/Helmut-Ortmann/EnterpriseArchitect_hoTools/wiki/LinqToSql.

 

I appreciate your experiences and feedback!

 

Kind regards,

 

Helmut

 

 

Published in Community Resources
Tagged under
Sunday, 17 January 2016 16:42

Script move/delete items from Search Result

Delete or move elements to package which are found by Searches (SQL, Query Builder, Standard Searches). These vbscripts allow you to easily handle found elements and to develop your own vbscripts to manipulate found things. In effect these scripts use bookmarked or elements copied to clipboard. It's an extension fo Geert Bellekens VBScripting Library.  

Abstract:

  • Use SQL Searches to move or delete found elements, diagrams or packages copied to Clipboard
  • Use Bookmarked elements from e.g. Standard / QueryBuilder Searches to move or delete elements
  • Handle bookmarked or result of SQL searches with your individual script

These scripts are using Geert Bellekens Script Framework (powerful!, Open Source)

  • See also there for updates, folder ho

 

Standard Search / Bookmark:

  • Set bookmarks
    • e.g. by Standard Search
  • If move: Select target package in ProjectBrowser 
  • Run Script:
    • hoModelSearch, DeleteBookmarkedItems or
    • hoProjectBrowser, MoveBookmarkedToSelectedPackage

 

Standard Search / Clipboard:

  • Run SQL Search 
    • each row shall contain GUID of the items to handle
  • Copy wanted rows to clipboard (eg: CTRL+A, CTRL+C)
  • If move: Select target package in ProjectBrowser 
  • Run Script:
    • hoModelSearch, DeleteClipmarkedItems or
    • hoProjectBrowser, MoveClipmarkedToSelectedPackage

 

In Action: Delete item found by SQL Search or Query Builder Search

  • Run SQL Search with element GUID as result / Run Query Builder Search
  • Copy to Clipboard (SQL Search / Bookmark (Query Builder Search)
  • Run Delete ClipboardedItems /  Delete BookmarkedItems

Delete clipboarded items from a SQL search

 

Move items found by SQL Search to package

  • Copy found elements to Clipboard
  • Select Package, Right Click, Scripts,...

 

Move bookmarked items
(
bookmarked in eg. Diagram or found by Standard Search / Query Builder Search) to selected package)

 

  • Run Standard Search or Query Builder Search
  • Bookmark wanted elements
  • Select Package, Right Click, Scripts,...
  • It works 

Install

  • Project DataManagement, Import ReferenceData, Select File (Script.xml)
  • Select 'Automation Scripts'
  • Import
  • See the scripts for details:
    • ho (reusable groups to extend functionality according to your needs)
    • hoModelSearch Group of type ModelSearch
      • DeleteBookmarkedItems
      • DeleteClipboardedItems
    • hoProjectBrowser Group of type ProjectBrowser
      • DeleteBookmarkedItems
      • DeleteClipboardedItems

 

Bookmarks (see also EA help):

  • Search Window: Query Builder Results,  Right Click, Bookmark Element,
  • Diagram: Select elements, Shift + Space
  • Diagram: Select elements, Edit, Bookmark selected,
Published in Community Resources
Find Real Orphans

Enterprise Architect comes standard with an Find Orphans search, which is supposed to give you a list of all elements that are no longer used in the model, and thus better be removed from the model altogether.

Find Orphans

The problem with this search is that it will only report elements that are not shown on a diagram; which should not be the only criterion to determine if an element is an orphan or not. Not being on a diagram is just fine. I have lots of elements in my model that are not on a diagram, that doesn’t mean they are not used anymore.

If you are going to delete all elements reported in the standard orphans search you might do serious damage to your model.

So in order to aid with the maintenance of a large model I wrote the following SQL Search.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
select distinct o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name, o.Stereotype,
package.name as PackageName ,package_p1.name as PackageLevel1,package_p2.name as PackageLevel2 ,package_p3.name as PackageLevel3
from ((((((((((((((((( t_object o
left join t_diagramobjects dob on dob.[Object_ID] = o.[Object_ID])
left join t_object inst on inst.[Classifier] = o.[Object_ID])
left join t_objectproperties otv on otv.VALUE = o.[ea_guid])
left join t_connectortag ctv on ctv.VALUE = o.[ea_guid])
left join t_attributetag atv on atv.VALUE = o.[ea_guid])
left join t_operationtag optv on optv.VALUE = o.[ea_guid])
left join t_object u on u.Pdata1 = o.ea_guid)
left join t_connector cs on cs.Start_Object_ID = o.Object_ID)
left join t_connector ce on ce.End_Object_ID = o.Object_ID)
left join t_attribute att on att.Classifier like o.Object_ID)
left join t_operation op on op.Classifier like o.Object_ID)
left join t_operationparams opp on opp.Classifier like o.Object_ID)
left join t_object owned on owned.ParentID = o.Object_ID)
inner join t_package package on o.package_id = package.package_id)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
where
o.Package_ID in (#Branch#)
and (o.name is null or o.Name like '#WC#<Search Term>#WC#')
and o.Object_Type not in ('Package')
and (o.Stereotype is null or o.Stereotype not in ('model document'))
and dob.Object_ID is null
and inst.Object_ID is null
and otv.VALUE is null
and ctv.Value is null
and atv.Value is null
and optv.Value is null
and u.Object_ID is null
and cs.Start_Object_ID is null
and ce.End_Object_ID is null
and att.Classifier is null
and op.Classifier is null
and opp.Classifier is null
and owned.Object_ID is null

This search will look for all elements that

  • Are located in the currently selected package branch
  • Are not a package or a model document  (section in a virtual document)
  • Are not used on a diagram
  • Are not used as the Classifier of an instance
  • Are not referenced by any tagged value
  • Are not used as the source or target of any relation
  • Are not used as the type of an attribute
  • Are not used as the return type of an operation
  • Are not used as the type of a parameter
  • Do not own any other elements

Elements that show up in this search are most probably real orphans that clutter your model and can be safely deleted.

Of course, before you delete anything from the model, make sure you have a backup!

Find Real OrphansIn order to use this search create a new search of type SQL and paste the above query into the query builder field.

If you find anything that should be added to this search please let me know.

Related

Published in Community Resources

Designing architecture

Designing solution architecture is about making best possible choices in context of current requirements and constraints. Behind every architecture decision there is some context and every architect should document the reasons of important decisions that are made. This article will focus on how to document architectural decisions in the context of project requirements in Sparx Enterprise Architect.

Value of architecture documentation

As an architect one needs to document important issues, possible solutions and reasons for choosing one of the solutions. There are various reasons to document decisions in projects lifecycle:

  • Reasoning that stood behind a choice will blur with time so the architect will not be able to precisely discuss or defend the choices or evaluate if the decisions made are still valid

  • Architect may need to describe reasons for his decisions for various reasons like some other architect joining the team or client (or consultancy company hired by client) having some questions about the design

  • Requirements and constraints may change in time and decisions may have to be reviewed. Knowledge about full context behind decisions made is fundamental in review them.

  • Architect may choose to leave project or for some other reason other architect may have to take over the responsibilities of the architect who originally made the decisions

  • Building database of architectural patterns and decision will allow the company to learn with every project, instead of relying on memory of individuals

Software architecture is also the dominant factor contributing to poor software architecture quality metrics of system. Bad architecture choices lead to costly development and maintenance and poor extensibility. They also have a tremendous impact on other software architecture quality metrics of system as reliability, scalability or testability. SEI has done research in this area, see A Field Study of Technical Debt [1]. Documenting architectural decisions is one of TOGAF's principles [2].

What to document

Deciding on what to document requires experience. Documentation must be lean and contain enough information to be helpful. A very good analysis is available in Sustainable Architectural Design Decisions [3] article, you can also find many blog posts or discussions on this subject on forums and social media portals. One thing to remember is that software architecture documentation is not a replacement for low level design documentation (if such is provided in project) or documenting code. You can also read about what to document in Documenting Software Architectures: Views and Beyond [4].

Deciding on what the documentation should contain also depends on the project. As discussed in Sustainable Architectural Design Decisions [3] formal documentation with many fields will in many cases result in gradual degradation of quality of documentation and even questioning the business value of documenting software architecture at all. A minimalistic and lean documentation may contain:

  • Issue name – a short name to identify issue in human readable form

  • Issue statement – description of the issue in textual form

  • Context (this includes requirements) of the issue – context should include functional requirements, non-functional requirements and other factors that have impact on decision.

  • Descriptions of alternative solutions - description in textual form

  • Reasons for choosing one solution - description in textual form

  • Status of decision – one of a few states

  • Date of last modification or evaluation

Depending on specific context or requirements other information may be included of course.

Where to keep documentation

Decisions can be kept in various places like:

  • Files based on some templates

  • Excel documents

  • Case tools

Using case tool that is used to model requirements ha an advantage of keeping all data together and allowing to do searches on data easily. One of the important aspects of documenting software architecture decisions discussed in Sustainable Architectural Design Decisions [3] is keeping decisions with requirements.

Sparx Systems Enterprise Architect is an example of a CASE tool that allows architects to document the software architecture design decisions with links to the requirements. Sparx Systems Enterprise Architect is often used to manage requirements, design the solutions architecture and to convey design to development teams. Using Sparx Enterprise Architect makes it possible to keep requirements, architectural issues log and other design artifact (like state machines, process models, service models) in one place and to create connections between issues and requirements. This allows us to partially or fully automate process of selecting issues that need to be reviewed. Additional benefit of using Sparx Enterprise Architect is the document generation features of Enterprise Architect. It is possible to keep all requirements management, analysis and design data in Enterprise Architect and to generate documentation straight from EA. Other artifact like notes made on meetings that have impact on design may also be included as linked documents or as a simple link to version control system.

Using Sparx Enterprise Architect and UML Profile

To efficiently store architectural decisions in Enterprise Architect one should create a profile. It is possible to create an architectural decisions log without a profile, using one of extension mechanisms of UML like stereotypes or tagged values. Using profile gives us a better separation between model elements and allows to search for some data in the model more easily. Custom document templates for linked documents can also be used to store architectural decisions - details on how to do this can be found in Enterprise Architect documentation [9]. Searching these documents would be more difficult as it would require to build custom application or a plugin. Using UML profile gives use more searching options.

Creating UML profiles is described well in Enterprise Architect documentation [5] so there is no need to repeat this information. This article will focus only on main steps of creating UML profile in EA.

In order to create a custom UML profile in Enterprise Architect you create a Profile element in the model (see [5] for details). Then you add required meta classes and stereotypes to the Profile element.

Architecture decision log profile should contain five stereotypes:

  • Architectural issues log – this stereotype extends package

  • Architectural issue – this stereotype extends package

  • Architectural context – this stereotype extends artifact

  • Architectural decision - this stereotype extends artifact

  • Architectural alternative solution - this stereotype extends artifact

Architectural issues log and Architectural issue stereotypes extend package in order to be containers for other elements. Architectural issues log will contain many Architectural issues.

Each issues will have it's own packages in order to lessen the maintenance cost for Enterprise Architect model in the context of document generation. If we would keep them in one package then ordering the elements would be problematic, especially if later some new alternative decisions would be added.

Architectural issue will store description of the problem and a short title (we wont to keep the log simple). Architectural issue will also group architectural issue context, decision and alternative solutions. Each context must be connected via a trace relation to the requirements that engendered this issue.

Architectural context element will specify known constraints (both technical and non-technical) that limit possible solution space.

Architectural decision will contain reasoning on why the solution was chosen. This should be a short text as every alternative solution should contain advantages and disadvantages of a given solution that will give a good insight on each solution. This model allows to work with alternative solutions more easily, each solution has its dedicated element so they do not get mixed up making them harder to read.

Each Architectural issue will contain a number of Architectural alternative solution. Each solution alternative is independent solution option.

Architectural issues log is a package that will group various architectural issues.

This profile will contain necessary fields to convey information discussed in previous chapter:

  • Issue name – the Architectural issue element name (Enterprise Architect provided it)

  • Issue statement – the description of Architectural issue element

  • Context (this includes requirements) of the issue – Architectural context element will specify known constraints (both technical and non-technical) in it's description.

  • Descriptions (free text) of alternative solutions – the description of separate Architectural alternative solution elements

  • Reasons for choosing one solution (free text) - the reasons will be described in Architectural decision element's description

  • Status of decision – the status field of Architectural decision element

  • Date of last modification or evaluation – this will be the date on Architectural decision element

All the elements are shown on the diagram below:

Custom UML profile

In order to be used the profile must be first exported from Enterprise Architect as a profile and then imported as a resource. To export the profile right click the profile's package element and select Advances and then Save Package as UML Profile (see image below):

Saving custom profile to a file

To import the profile right click UML Profile In Resources view and then choos Import Profile:

Importing custom profile to a project

After doing this a new category of tools will be available. Image below shows an example of a small decision log created using this profile:

Architectural decisions log

Architectural decisions log

Custom model search

When we have the profile in place we can create a custom search that will allow us to find the decision for which requirements have changed. Both elements have last modification date so it is easy to track down issues that need to be reviewed.

We can do it in several ways:

  • Building a custom application that will use Enterprise Architect automation interface [6] to search repository – this requires a custom application to be build prior to executing any searches. Application would not be integrated with Enterprise Architect.

  • Search the Enterprise Architect database – using database level searches may be problematic as there may be differences between Access and some RDMBS' SQL executed to search the model for issues to review. Additionally if this solution will be chosen then search would not be integrated with Enterprise Architect.

  • Create Enterprise Architect plug-in - similar but not the same to building custom application. Plugin would be integrated with Enterprise Architect and thus more convenient to use.

  • Do a custom model search using Enterprise Architect extension features – the only thing required is to build a query and let Enterprise Architect handle query execution.

Last solution is quite simple and thanks to the fact that it uses Enterprise Architect extension features user will work with Enterprise Architect only to manage Architecture Decisions Log.

Such custom model search can be created in two ways:

  • With query designer

  • With SQL queries

Second option is more powerful but a little bit more difficult. There is a very good description on how to create such query on Bellekens blog [7]. In our case we will search for objects with type “Architecture issue” and choose those for which the requirements changed. The query that does this search can be found below:

select decision.ea_guid as NodeGUID, decision.name as Name

from (((( t_object req -- start with requirements

-- go to dependency connector

inner join t_connector requc on requc.End_Object_ID = req.Object_ID)

-- get Architectural decision context

inner join t_object ctx on requc.Start_Object_ID = ctx.Object_ID)

-- go to dependency connector for decisions

inner join t_connector decon on decon.End_Object_ID = ctx.Object_ID)

-- get Architectural decision

inner join t_object decision on decon.Start_Object_ID = decision.Object_ID)

where ctx.Object_Type = 'Artifact' and ctx.Stereotype = 'Architectural context'

and decision.Object_Type = 'Artifact' and decision.Stereotype = 'Architectural decision'

and decision.ModifiedDate < req.ModifiedDate

Description of columns and tables used:

  • t_object - this is a table that stores information about elements in model

    • ea_guid - UUID in Enterprise Architect model

    • name - name of element

    • Object_ID - object identifier in Enterprise Architect repository

    • Object_Type - type of object (class, artifact, interface, etc.)

    • Stereotype - stereotype of this object

    • ModifiedDate - date of modification

  • t_connector - this is table stores elements about relations (also elements in model)

    • End_Object_ID - id of object at the end of relation

    • Start_Object_ID - id of object at the start of relation

Using this simple query we can quickly validate our model and see what needs to be reviewed.

In order to extend the query by adding some columns not covered in this example than be sure to take a look at Read Inside Enterprise Architect [8] site.

Conclusions

Keeping project documentation together is important for maintenance and architectural solution library. Creating such library must not be too much burden so it will not become an unwanted task. With Enterprise Architect and custom profile creating such portfolio is easy. Keeping all project information in one place makes it possible not only to maintain decisions by checking if they are valid or learn on past decisions and reuse them. It also allows to build cohesive and comprehensive project documentation. Thanks to Enterprise Architect powerful reporting features we can generate documentation for the projects straight out of Enterprise Architect. Architecture decisions are part of projects documentation, a very important part so they need to be included. No more copy & paste of diagrams and their descriptions.

Links

[1] http://blog.sei.cmu.edu/post.cfm/field-study-technical-debt-208

[2] http://pubs.opengroup.org/architecture/togaf9-doc/arch/chap23.html#tag_23_05

[3] http://www.infoq.com/articles/sustainable-architectural-design-decisions

[4] http://resources.sei.cmu.edu/library/asset-view.cfm?assetid=30386

[5] http://www.sparxsystems.com/enterprise_architect_user_guide/10/extending_uml_models/workingwithprofiles.html

[6] http://www.sparxsystems.com/enterprise_architect_user_guide/10/automation_and_scripting/automation_interface.html

[7] http://bellekens.com/2011/01/14/harvesting-the-power-of-eas-sql-searches/

[8] https://leanpub.com/InsideEA/read#search-builder

[9] http://www.sparxsystems.com/enterprise_architect_user_guide/10/modeling_basics/linked_document_templates.html

Published in White Papers
Tagged under

Creating Charts in Enterprise Architect to Aid Decision Making

Date: September 2014

Mr Scott Hebbard, Communications Manager at Sparx Systems, will show how Charts in Enterprise Architect can help you manage projects, communicate strategy, identify trends, track KPI's and make informed decisions.

In this webinar you will learn how to:

  • Place Charts and Graphs on diagrams, group them to display an interactive project dashboard and include them in generated reports
  • Source data from the model, from specific Packages within the model, or from a custom SQL query that extracts the information from the model
  • Create burn down charts and time series graphs that are automatically updated by Enterprise Architect on a regular basis.

 

Register Here ...

Published in News