Synchronize model with ERWIN

[Hans Olthof]

Hello,
I have an enterprise reference model which is mastered in ERWIN.
I can import that model fine.

However, since this model is updated and released periodically, I need a capability to update the already imported model, rather than delete/re-import.
This is because a substantial number of relationships between this model and other element (Services, Applications, Business functions etc) have been created.

I've tried a number of different ways, but I am not having any luck.

ERWIN has a diff capability, but seems unable to put that out in xml/xmi format I thought of comparing a diff-file to sparx.

The model is non-trivial(~4000 model elements), so manually updating is not feasible.

I have this feeling that hacking some code to process the ERWIN xml file is the only way to go.

Has anyone got any hints?
With regards,
Hans

[MMA]

If the model is maintained by Enterprise Architect, then the "Database Compare" can pickup the difference from the DBMS and your model and then give you the options like:

• (right==>left)use DBMS schema to update your model;

• (left==>right)use you model to update DBMS schema via generation of Create/Alter DDLs and run in the execution engine in an executable order without losing any data.

If you don't need the model in ERWIN, you could use EA to import from ODBC that is configured to your existing database. Otherwise, here is my suggestion to migrate the existing model in ERWIN to EA via a temp-empty-bridging database:

Now you have:
    A. model in ERWIN
    B. the existing database with data

1. Create a new database "C", same type as "B"
2. use ERWIN's model "A" to create schema in the database "C".
3. use EA to import from "C" (Import DB Schema from ODBC), then you will get model in EA "D".

Now you have:
    C. the "bridging database"
    D. model in EA

"D" is exactly the same as "A", then you can use EA's "Compare Database" to compare "D" and "B", apply actions... (left=>right or right=>left)

You can discard the bridging database "C" afterwards.

Note: the process from "A" to "D" via bridging "C" is a once-off operation.

Hope that helps
M

[Hans Olthof]

Hi,
I'm doing a bump on this thread in the hope that there are some solutions.
I went quiet on the topic for a bit, but now the issue has to be sorted out in my company.
To reprise:

• I have an enterprise conceptual data model (not a physical database schema) that is maintained in CA Erwin.
• This Erwin model goes through a monthly update where adds/changes/deletes are applied to the Erwin baseline. This means that the Erwin internal reference (GUID) remains constant between releases.
• My problem is that I can't find a way to apply the updated Erwin model to the sparx imported model.
• I can't simply re-import the model and 'blow away' the existing one since I've now got thousands of relationships between the conceptual data model and other modelling elements (applications, ESB Services, Business services etc) that I cannot loose, or manually re-establish.

Since the model is conceptual, it cannot be represented as a physical database (thanks for yyour suggestion MMA) and even so, I would have the same referential issue between Sparx and Erwin regarding a unique key.
The issue I am facing is that I can't get the GUID to be the primary reference between Sparx and Erwin.
When I do an import, even with the 'strip GUID' unchecked, Sparx generates it's own GUIDs. Now I've lost my only consistent key between Sparx an Erwin.
I do notice that it generates a tagged value 'rose_uuid' with the input Erwin xmi.uuid - which at least gets the value into sparx, but only usable with custom code.
I have tried all sorts of exports from Erwin (including it's built-in Sparx export) and run into the same problem.

Is there a way to force Sparx to use the GUID (xmi.uuid) in the input xmi file. My hope is that if Sparx can be forced to use the import xmi.uuit as it's GUID then I can use baseline/package control diff/merge to manage the Erwin model updates.

If that does not work, then the only way forward that I can see is a custom script which maintains a mapping table of Sparx GUID<=>Erwin xmi.uuid. That's going to be really ugly and present a maintainability problem for my company.

with regards,
Hans

[Paolo F Cantoni]

Hi Hans,

Firstly, is the "Conceptual" structure in EA a mirror of the structure in ERWIN?  That is, there are no more features/properties/tagged values etc in EA that are not in ERWIN that you need to keep.  If not, you're on a "hiding to nothing" without extensive coding.

Assuming it is, are you able to import the exported ERWIN model into EA without stripping guids? Say into another repository for the present.  If that imports OK, can you check that adding a link out to a non-ERWIN object is preserved following another import from ERWIN?  That is, that existing external linkages are preserved?

I assume that the name of the object in the EA structure is identical to the name in the ERWIN structure.  If not, see: hiding to nothing above.

If so, then you can create a one-time mapping "behind the covers" to map the name and the ERWIN GUID and the EA GUID (after importing into the main repository - BUT into a different branch).

Then with an SQL Query, you can replace the EA GUID with the ERWIN GUID in the t_object table and now you have the direct mapping.  NOTE: there are a couple of tables where this needs to be done, but it's a one-time cost.  (See qwerty - Thomas Kilian's - eBook: "Inside EA" for guidance)

If you think this will help, then ask more questions till you're satisfied you've got it.

Try it out on a test repository and then you're good to go!

HTH,
Paolo

[Hans Olthof]

Hi Paolo,
Many thanks for the quick and detailed response.
I tried seeing if there was a song called "Hiding to Nowhere" so I could cue it as a backing track, but that search turned up nothing but reference to a line in a Muse song. Now that's a hiding to nowhere 

In any case:
The model in Sparx is a mirror of the ERWIN model. The Erwin model is the canonical change-controlled model, and Sparx must contain that and no more (other than the links to other sparx modelling elements such as applications, Business Functions, services etc).
I have not found a way of importing the Erwin model into sparx while retaining the Erwin GUIDs as the Sparx GUID. As far as I can tell, it is the GUID that drives the package control diff/merge capability. I was pinning my hopes on that capability to drive the updates from Erwin into Sparx.
The name in Sparx is identical to that in the Erwin model. But alas, the Erwin model has changed entity names as well, so the entity Name is not a reliable reference.
Given that the sparx import does put the inbound GUID into a tagged value the mapping is there (that's one thing that is going right).

All in all, I think you have confirmed that I am heading into custom coding territory (and I really don't like coding to xml, mainly because I have not done a lot of it). I'm going to investigate the Erwin csv export and see if that is a more amenable approach.
The SQL bit is the easy one (I've got Thomas' book) - I've done lots of sql to the sparx database. It's the navigating the xmi model which is non-trivial.

Anyway, thanks again Paolo.
hans

[Paolo F Cantoni]

Hi Hans,

Are you able to import the ERWIN XML into Sparx at all??  It's been many years since I tried ERWIN<->Sparx interchange (including trying some commercially available bridges).

I think I'm misunderstanding something.  Does EA provide its own GUID when you import ERWIN with strip GUIDs disabled?  If so, does it get the guid from the XML file or generate a new one?

Paolo

[Rodrigo Nascimento]

Hi Hans,

I had the same problem with my current client. There are few things that we redesigned in terms of processes and tools to deal with this kind of issue.

First, we've identified that all the Sparx EA models' consumers were related to integration (SOA, API...) or business process management. Therefore, an 'objected oriented' view of the information would make more sense.

We then decided to use the MDA approach to derive the XSD models. The challenge came when we had to import the Erwin models as our Platform Independent Model, because it is not a straight forward exercise to translate relational model to class (hierarchical) models. After trying few options, we decided to create an add-in that could consume the reports generated by the Enterprise Data Modelling team from Erwin, and producing a report that could inform the differences between Erwin and Sparx. The add-in also gives you the capability to choose what classes/entities and attributes to merge.

In my view, the main advantage of this approach is that you have better separation of concerns and traceability from the Enterprise Model down to the physical XSD models.

Hope it helps...

[Glassboy]

Hi Paolo,
Many thanks for the quick and detailed response.
I tried seeing if there was a song called "Hiding to Nowhere" so I could cue it as a backing track, but that search turned up nothing but reference to a line in a Muse song. Now that's a hiding to nowhere 

Is "hiding to nowhere" German idiom?  The more familiar English is "hiding to nothing", but the experts do note that nowhere has started to appear http://www.worldwidewords.org/qa/qa-hid1.htm.  Hiding, in this sense means beating, and I guess you're feeling beaten by this issue 

[Paolo F Cantoni]

Hi Paolo,
Many thanks for the quick and detailed response.
I tried seeing if there was a song called "Hiding to Nowhere" so I could cue it as a backing track, but that search turned up nothing but reference to a line in a Muse song. Now that's a hiding to nowhere 

Is "hiding to nowhere" German idiom?  The more familiar English is "hiding to nothing", but the experts do note that nowhere has started to appear http://www.worldwidewords.org/qa/qa-hid1.htm.  Hiding, in this sense means beating, and I guess you're feeling beaten by this issue 

I think "nowhere" was my bad...  Typing on the the bus and trying to get spell check while bouncing around...  I meant "nothing" - the usual form.

Paolo

[Hans Olthof]

Hi Paolo,

hmm I'd always heard it as "hiding to nowhere".
But for those who prefer a "hiding to nothing" => https://www.youtube.com/watch?v=P35nX4ULTBs 

In any case, I am feeling a little defeated (but as the ancient expression goes: "man with compiler can always fix problem"). I'm not sure if the problem lies with Erwin putting out xml that Sparx can't work with fully (not sure of that, since Sparx brings in all the entities, subject areas and relationships fine) or if the problem is that Sparx just always generates it's own GUIDs and the 'strip guid' field is a placebo.

I'm still looking at the permutations of Erwin export. One of them actually puts the Erwin GUID into a field. This will let me keep the reconciliation - I've already got a python swiss-army-knife code to do various things, and this is doable.

regards,
Hans

[Eve]

... or if the problem is that Sparx just always generates it's own GUIDs and the 'strip guid' field is a placebo.

The strip guids option only applies to Enterprise Architect generated XMI. For an import from any other tool EA always generates guids to ensure that they match our conventions etc.

[Paolo F Cantoni]

... or if the problem is that Sparx just always generates it's own GUIDs and the 'strip guid' field is a placebo.

The strip guids option only applies to Enterprise Architect generated XMI. For an import from any other tool EA always generates guids to ensure that they match our conventions etc.

Qh yes... As I've observed... "All GUIDs are equal, but Sparx GUIDs are more equal than others..."   

Paolo

[Eve]

All guids are most certainly not equal.

1. A 128 bit guid has more value than a 64 bit guid but less than a 256 bit guid.
2. A guid with 6 fixed bits (eg. to denote the source) supports more values than one with 64 fixed bits used for the source, but supports less sources and is more likely to have a source collision.
3. A guid generated with a poor random number generator is a lot less useful than one generated by a good one.

None of those are really relevant to this scenario though.

4. A guid in an unknown format, can't be reliably used or converted to a known format.

If it has a known (or self-describing) length, it can be used for lookups (as we do) but we don't maintain that lookup beyond the life of a single import. The other option is to use an algorithm to generate a non-random guid out of the existing one. I have toyed with this, but it's complicated because a lot of XMI uses simple ids instead of guids.

[Paolo F Cantoni]

Thanks for that Simon,  most useful.

I was, of course, being a bit facetious.  But it elicited a useful, to me, response.

Paolo

[Geert Bellekens]

For those who need to google "facetious" like me:

adjective
1. not meant to be taken seriously or literally: a facetious remark.
2. amusing; humorous.
3. lacking serious intent; concerned with something nonessential, amusing, or frivolous:

Geert