Importing directly into an EA repository

[Modesto Vega]

Let me just start by saying that this sounds like complete heresy to me but I am going to ask about it. Other than the usual mechanisms, CSV imports or Excel macros, has anybody considered or even tried importing directly into the database tables of an EA repository?

The reason for such a heretical question is that the usual mechanisms, quoted above, are proving to be too "slow" to keep up with release cycles of a relatively large solution.

Hopefully nobody starts screaming "heresy".   

[Uffe]

An unbeliever! Persecute - kill the heretic!

Ahem.

Well I think it's a tricky proposition, because the database schema is not simple (as in each type of entity is stored in a single table) and there is no publicly available documentation. In other words, exactly what EA does in the database when you create an element and set its properties is unknown, and therefore hard to replicate.

For bulk imports/exports I always go to the API and write something, usually in C#, to do the heavy lifting. Maybe Paolo Cantoni has some more insight, I know he's done some fairly massive jobs in this area.

/Uffe

[qwerty]

I have done so (of course, it's me) for various reference tables (for pictures since the EA import was/is broken). For elements I usually take the API (e.g. when I created my own XSD import). I would not re-program an XMI-import, though.

q.

[Modesto Vega]

Thanks Uffe and Paolo. My preference is the API but this is a large job, very heavy lifting. Cannot use C# because getting the relevant tools installed on my machine will take a long time due to internal red tape.

I do understand the database schema relatively well, including some nuances. Any technical details on both routes database and C# will be welcome.

Currently using some custom code largely based on Geert samples.

IMHO, the import is still broken because only elements can be imported; it is not possible to import attributes or operations

[Uffe]

Hi again,


If C# isn't an option, C++ works and so does Java -- but only for applications which drive themselves and access the EA repository from outside. The alternative to that is an Add-In, where EA is in the driver's seat, but that part of the API ("Add-In Model") does not support Java.

If you haven't done a lot of EA API coding, Geert's blog is probably the best place to start. There are also some simple examples included in the help.


/Uffe

PS  Oh, and qwerty isn't Paolo.

[Modesto Vega]

Hi again,


If C# isn't an option, C++ works and so does Java -- but only for applications which drive themselves and access the EA repository from outside. The alternative to that is an Add-In, where EA is in the driver's seat, but that part of the API ("Add-In Model") does not support Java.

If you haven't done a lot of EA API coding, Geert's blog is probably the best place to start. There are also some simple examples included in the help.


/Uffe

PS  Oh, and qwerty isn't Paolo.

That will be too easy, work has to be made more difficult. I will have the same problem with C++ and Java. I am restricted to Excel and SQL.

P.S.: Yep, Paolo is not qwerty but qwerty words some of his messages sounding as if he is pretending to be Paolo; there are both quite unique.

[qwerty]

One could say that Paolo and me are birds of on feather in some way....

I don't know what you are trying to import, but you should use the API first. Only if that does not work, think about the tables. Direct fiddling with tables is not really recommended. It's just that you can't avoid it in certain cirumstances.

q.

[Geert Bellekens]

I've done pretty large imports using the API and Excel's VBA (so no red tape issues) and the performance is quite OK.
I don't know what quantities you are talking about, but with some tweaking I think i got to up to a few thousand elements in a matter of minutes.

Geert

[Modesto Vega]

About 10,000 elements organised in different packages and 120,000 attributes. This is to start with. They are organised into 3 packages with various sub-packages. There is also a need to identify changes once the baseline is contracted.

[Modesto Vega]

Let's explore the API option for a minute, just a few randon questions:

1) How do I set the language?
2) How do I set the version?
3) If some of those objects are database tables, how do I set the tablesapce and owner
4) Can I return the GUID back to the spreadhseet?
5) How can I add 2 Tags to each element and attribute (Updated, Changed)

[qwerty]

Hmm, extending this to an interactive API training would probably go too far.

q.

[Uffe]

Hi again,


Well there's plenty of stuff out there to get you started on the API. Always remember that whenever you change an object you must call Update() on it, otherwise the changes aren't stored in the database.

1) How do I set the language?

Set Element.GenType (string).

2) How do I set the version?

Set Element.Version (string).

3) If some of those objects are database tables, how do I set the tablesapce and owner

Those are TaggedValue objects, stored in Element.TaggedValues, which is a Collection. Find the one with the right Name and set its Value (string).

4) Can I return the GUID back to the spreadhseet?

The normal way of syncing between Excel and EA is to include the GUID in a CSV export. If you don't want to do that you can probably do something using the Excel API to update its contents directly. The element GUID is stored in Element.ElementGUID.

5) How can I add 2 Tags to each element and attribute (Updated, Changed)

Element already has properties Element.Created and Element.Modified. For Attribute, add them as tagged values using Collection.AddNew() on the Attribute.TaggedValues collection. You can do that with an Element as well, if the regular properties don't work the way you want them to.


/Uffe

[Modesto Vega]

Thanks for the replies, much appreciated.

Please keep in mind the issue is the volumes, about 10,000 elements organised into different packages and 120,000 attributes.

4) Can I return the GUID back to the spreadhseet?

The normal way of syncing between Excel and EA is to include the GUID in a CSV export. If you don't want to do that you can probably do something using the Excel API to update its contents directly. The element GUID is stored in Element.ElementGUID.

Fully aware of this but as you know, you cannot import attributes using CSV file, the only way to import attributes is through Excel. Please correct me if I am wrong. Attributes are very important for the type of work I do, as important as elements.

5) How can I add 2 Tags to each element and attribute (Updated, Changed)

Element already has properties Element.Created and Element.Modified. For Attribute, add them as tagged values using Collection.AddNew() on the Attribute.TaggedValues collection. You can do that with an Element as well, if the regular properties don't work the way you want them to.

Also, fully aware of this. But what I am trying to do is finding an easy way to identify which elements and atributes have being or going to be updated each time the repository is refreshed using an import routine.

[Geert Bellekens]

Have you looked at the Excel Importer shared on my website?

There's a also a section that exports tagged values. You could do something similar for attributes so you can import and export them using this tool. (currently only import has been implemented)

Geert

[Modesto Vega]

Have you looked at the Excel Importer shared on my website?

There's a also a section that exports tagged values. You could do something similar for attributes so you can import and export them using this tool. (currently only import has been implemented)

Geert

I have seen your Excel importer, it would be fair to say the I am using a modified version of it. The main performance bottleneck appears to be with the function that gets an element by name, this is because it has to loop through the entire elements collection, it performs well on small packages but there are 2 very big packages and the performance is not so good.

I have been considering running the imports in 2 steps: 1) create "shell" elements (without attributes) returning the GUID and, then, 2) add the attributes. This assumes that that there is an API to find an attribute by GUID and have not done my homework regarding this.

Once again the problem is establishing the baseline.