MS Access 2013 and EAP files

[Paolo F Cantoni]

Thanks, all. As you can see I am new to the forum and didn't realize you had answered. I turned on notifications for this topic but didn't check my spam folder!

Paolo you answered my questions exactly as intended. I definitely do not know what I'm doing at this point. But the project lead now wants to move the file to a central database for multiple users. For regulatory reasons, they also want to continue to access the tables via MS Access. Paolo that sounds like exactly what you've been doing, is that right?

I have Inside EA and read it!

Bill

Yes Bill, that's what I'm doing.  But the way I've done it, I can switch to another repository type (such as .eap) at the press of a button.

Feel free to ask questions.

Paolo

[bholtzman]

Paolo,
Ah, this is great news for me. We have not committed to anything so your advice and experience will be of great value.

We're currently evaluating our options. What we want is to track custom metadata associated with classes, attributes, and everything we enter into EA. We need multi-user concurrent access from several different physical locations and networks. So here are the basic questions:

1. Can we do what we need inside of EA?
2. If not, what is the best client? Probably all of us would prefer a web-based tool for manipulating the metadata, but I think I mentioned that for regulatory reasons we need to use MS Access. Why have you chosen that client, your own familiarity with it?
3. What is the best configuration and database for concurrent access? Of course, "Deployment of Enterprise Architect" is a great resource for this. But there's no substitute for someone else's practical experience! We're leaning towards a MySQL database and still considering how to connect to it. We're also unclear as to how concurrent access will work. Is there any kind of locking mechanism?
4. Seems like we need to use the Cloud service option to connect to the database. Would you agree? Do you have any experience with that?

Scott Hebbard suggested we stick with EA and use tagged values. But the process of accessing the tagged values window for every single item is inefficient with respect to creating an Access form that contains every metadata item we want to track and a drop down of every EA item we want to include (from t_attribute, t_object, etc.). Also, having to type in every column name into the Tag field would really be labor-intensive. Or is there a way to populate the drop down? I looked at every single table in the database and did not see "EAExtended::CRUD" or the other two. Even if all of this worked you'd still have to get the values out of EA and I guess that'd be along these lines:
http://www.sparxsystems.com/enterprise_architect_user_guide/10/reporting/tagged_value_sections.html

Someone else suggested using the Glossary feature but it wasn't clear this was workable.

Thanks so much.

Bill

[Geert Bellekens]

Bill,

I really don't see why you would want to build another client in MS Access to enter data into Enterprise Architect.

If you really think you can't do it with the standard tagged values screens (which have improved greatly the last few releases, look at the BPMN 2.0 tags as an example) there still EAForms which will allow you to build your own user interface to enter details.

And if that is not sufficient you can even add your own windows to Enteprise Architect by building an add-in. At leas you would only have one place to enter the data.

Geert

[bholtzman]

Hi Geert,
I looked at tagged values but from what I could tell they would be lots of work. We have 20 or so fields we want to associate with an attribute. I would have to create a tagged value for all 20 for each attribute, typing in the name of the tag each time and then typing in the value. I'd rather use my Access form that links to that attribute and has all of the fields with drop-downs and possibly some auto-fill fields as well. (Can't seem to figure out how to attach an image to this.)

I haven't looked at EA forms but will.

As far as an Add-in, could I build something similar to the what I've described (a multi-field form keyed on rows in t_attributes and t_objects) that way?

I've watched several of your videos on youtube...great! I just saw the one on EA add ins in one minute but the EA Matic item is not in my Extensions drop down in EA 12. Thanks!

Is it possible that my version of EA does not have either EA Matic or EA forms? We own Corporate.

Bill

[Geert Bellekens]

Tagged values are usually set in a set of stereotypes which are part of a profile.
Selecting an element of a specific stereotype automatically comes with the set of tagged values.

This is the standard UML compliant way of extending standard UML or UML Profiles.

For reference look at the way this was used in the BPMN2.0 MDG

EA-Matic and EA Forms are add-ins for Enterprise Architect you'll have to install separately.
EA-Matic is free and open source. EA Forms is commercial.

Geert

[bholtzman]

Paolo,
Three more questions:

1. What's your preferred database to connect to? We are leaning towards MySQL. Do you have experience with that? Any specific issues or tips?
2. Once you've connected the Access front end to the relational database, can you then create tables and such? Or do you need to create the database structures separately?
3. How do you manage security on the side of the Access app? Do you specify locking level during schema construction?

Thanks!

Bill

[Geert Bellekens]

In terms of performance we've seen the best results with MySQL and SQL Server, so MySQL is definitely a good choice. Just look carefully at the instructions with regards to settings and drivers.

For security there the's the built-in security feature. Most often used in the "Require user lock to edit" mode. Also have a look at the auditing feature if you want to be able to track who changed what.

I would strongly advice against changing anything to the database structure. It will make things like project transfer, upgrades, etc.. a much harder task.

Geert

[qwerty]

Re 2. You need to create the tables first (as admin). Once setup you can transfer an existing repository (or a plain EAP) vie Project/Data Management/Project Transfer. The users need a Grant for R/W to all tables, but not CREATE TABLE.

q.

[bholtzman]

Geert and Thomas,
Thanks very much. Good to know MySQL will work well.

My other questions were not about EA, but about the configuration Paolo uses where a MS Access front end connects to a database (not EA). The Access app also has read links to the EA database. Then we connect the t_attribute and t_object tables to custom tables in the non-EA database and track all kinds of metadata using the Access front end. This enables us a lot of flexibility in terms of the types and amount of custom metadata we track, and infinite control over reports. We can give users direct access to the up-to-date and custom data dictionary without the need for them to use EA.

So I'm not talking about changing the EA database structure at all, not even the slightest change. What I was talking about was manipulating tables in the non-EA database from MS Access.

I think Paolo has been doing exactly what I'm talking about for years.

The fundamental issue here, and the reason we want to move to this configuration, is that EA Forms, EA Matic and MDG Technologies do not enable any user-driven changes to the EA tables. So we're at the mercy of the EA table structure for tracking this custom metadata. From everything I can tell, the custom metadata is stored in the tagged values tables (t_attributetag, t_objectproperties, t_connectortag) and the tags are stored as plain text. What we're looking for is the ability to use lookup tables for entry of custom data, so that the user chooses from drop downs and other widgets and the data entry is simplified. We also see an advantage in automating the data entry with auto-fill features and such by using MS Access this way.

Do you see what I am getting at?

Bill

[qwerty]

Yes. I think it is okay to do so, as long as you remember that this is a custom database and has restrictions regarding XMI/VC. I don't know whether I would suggest to use such a DB, but I can see that the milage varies everywhere.

q.

[bholtzman]

Thanks, q! It gives me confidence to have your "blessing" on this. I realize you aren't guaranteeing anything, but just knowing you don't have any outright objection is very helpful. Thanks for your comments!

Bill

[Paolo F Cantoni]

Hi Bill and co,

I see you've been busy while I've been sleeping!

We use MS Access for a number of use cases:
Surgical intervention when things go wrong
Running scripts (MS Macros etc) to perform actions directly on EA Tables - including adding objects, relationships, attributes, operations and tags
Running scripts to check higher level validity or structure than that obtainable with the Integrity checker.

These are all administrative use cases - not for normal users.

We don't do data entry, per se, in MS Access.  But there's no reason not to.  But Geert is right, for normal users you should try to do everything from EA in some way.

We do have tables not in the EA Repository, some common to all repositories (usually metadata related) others specific to the target repository (as we change the target repository via a linked table manager, we automatically re-link these external tables to the requisite MDB.

Now, it took a few years to get all this additional framework in place - but now we have a significant amount of freedom to create our own modelling environment.

However, as the others have said, we try to use existing functionality within EA wherever possible - using predefined tagged values against metatypes a case in point.  In fact, sometimes we have been "ahead of the game" and when EA has caught up, we've retired our "extension" and switched to the new EA functionality.

We took the view that EA is not just a product, but a framework.  We build our own environment /configuration on top of that framework.  Don't be afraid of using MS Access, but don't see it as a panacea.

BTW we also interact with EA via MS Excel (we automatically synchronise formal "Catalogues" with the repository, for example).

My advice to you is to look at what you want to do, decide on a design and the put it up for comment on the forum.  Over the years, I've donethat where I wasn't sure if the idea was correct.

HTH,
Paolo

[Geert Bellekens]

Hi Bill,

You still haven't convinced me the MS-Access route is necessary.
With UML Profiles you can define tagged values in all kinds of datatypes such as enumeration (which show to the user as dropdowns) and even references to ther model elements (which the user can select).
You can do a lot with Enterprise Architect using UML Profiles, SQL Searches, Scripts and Add-ins. With that toolset I've been able to satisfy all of my clients requirements (some of which were really far fetched). Not once have I considered using MS Access for anything.

Geert

[bholtzman]

Hi guys,
Sorry for being offline. The conversation is really great.

Paolo,
I also feel like I have much more granular control and understanding of things using the Access option. Maybe it's just because I haven't been working with EA for years (just a few months actually) and don't have the "back of the hand" knowledge of it inside and out. It's certainly a powerful product with lots of capabilities, but as you say it's great to have freedom to create your own modeling environment.

We're curious how you capture "versions" of the data in your custom tables and how you match that up with each Baseline from EA. Did you build some custom tools for handling that?

It seems pretty bold to be running scripts to perform action directly on EA tables! Can you give some examples of this? I've already had cases where EA throws an error because it is creating an entry that violates a primary key, so I've taken steps to keep all of my activities confined to the tables I created, and not "touch" any native EA table except in "read only" mode. My sense is that this is necessary for me but not for you, because you've made the mistakes in the past and know what you're doing!

The flexibility of performing higher level and very customized validity checks is also appealing. I take it these are your arguments to Geert about why you think using Access is worthwhile?

Geert,
If I use UML Profiles would I also use EA Forms to provide the user with a way to track 20 pieces of metadata associated with a particular element, attribute, etc.? How would that data be stored, in t_attributetag, t_objectproperties, etc.? Having our custom field names stored in these tables as plain text doesn't seem efficient. Also, where are the enumerated tagged value datatypes be kept in the database? EA Forms does not seem to be that well supported. The web site doesn't seem to be up-to-date.

The difference between us is you have many, many years of working with EA and incorporating new capabilities. For us to get to anywhere near the same level of comfort might take a year. Meanwhile, Access is a familiar and comfortable tool and is probably used by 10,000 times as many people as EA. So if we build something in Access there are a lot more people available who can manage that component cheaply and proficiently. So the configuration is more manageable on the design side.

Cheers,
Bill

[Geert Bellekens]

Bill,

The way the data is stored should not be any of our concern, as long as it is managed properly by EA.

And I must say that EA does pretty good job managing all the metadata as it is.

As for the GUI part, have you seen how standard EA handles BPMN? There are tons of tagged values, and I think the GUI for managing these, out of the box, is not bad at all.

But as I mentioned, if you don't like the standard GUI it is possible to customize it with add-ins.

The advantage of using a UML profile is that this is a standard extension method for UML, and so your xmi exports will contain all your extended meta-data. That may become important once you start working with 3th parties, or you want to export your model to other tools.
Another issue to consider is document generation and html reports.
and probably some areas or concerns I forgot.

Anyway, once you "roll your own" and build your own meta-data extension using your own database or tables, your on your own for all of these areas. I'm pretty sure there will be a point in the future where you will regret that decision.

I would really investigate the standard out of the box extension mechanism before building your own.

Geert