Author Topic: How to transform a logical data model to a physical one  (Read 13062 times)

ducatiross

  • EA User
  • **
  • Posts: 114
  • Karma: +1/-0
    • View Profile
How to transform a logical data model to a physical one
« on: October 17, 2020, 04:12:19 am »
OK, please don't shoot me down in flames, but I've spent all afternoon trying to find out how to do this, searched the help files, searched here and failed. I am probably being a complete dimwit, but please tell me how I can do this ?

The help systems says it can be done using built in functions :-
https://sparxsystems.com/enterprise_architect_user_guide/15.0/model_domains/ddltransformation.html

But I cannot found out actually how to do the transformation !

Please put me out of my misery ! :'(

Thank you in advance.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 13256
  • Karma: +554/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: How to transform a logical data model to a physical one
« Reply #1 on: October 17, 2020, 04:17:44 am »
ddl transformation is to go from physical model to DDL code (to create the database)

I'm not sure if there is a standard LDM to Physical Data Model, but you can certainly write one yourself.

This might be the closes match: https://sparxsystems.com/enterprise_architect_user_guide/15.0/model_domains/erd_to_data_modeling.html

Geert

ducatiross

  • EA User
  • **
  • Posts: 114
  • Karma: +1/-0
    • View Profile
Re: How to transform a logical data model to a physical one
« Reply #2 on: October 22, 2020, 01:01:51 am »
Thanks for the suggestion Geert.

I eventually found out how to do it. As my logical model was done as Classes representing the entities, the standard 'ERD to Data Model' transformation wouldn't work. After much digging around, and help from a colleague, I used the 'Tables' transformation and this did what I expected - took the Platform Independent logical model classes and converted these into new DB-specific entities (SQL Server in my case) and created links back from the generated objects to the source logical model objects.

But my word, it shouldn't have taken so long to find out how to do this - if only the help files went from 'you can do transformations' to actually showing you what you had to do to perform it, that would save so much time !

Hopefully this thread will help someone else in the future.

pvickers

  • EA User
  • **
  • Posts: 44
  • Karma: +7/-0
    • View Profile
Re: How to transform a logical data model to a physical one
« Reply #3 on: October 22, 2020, 02:53:26 am »
I agree the transformation process is challenging to figure out...and it varies across versions of EA.   I have been down that very same path.  Once you find your way through the EA interface and the related documentation it gets easier.

There are actually two transformations (I'm currently referencing EA version 15.1) that are very similar: "DDL" and "Tables".
Currently the only difference between these two transformations appears to be that the DDL template has code to deal with foreign keys.

To see the low level details of what occurs when you perform a transformation, you can reference the transformation code...this is located in a transformation template.

Go to the "Design" tab, "Tools", "Transform" and select "Transform Templates".

Now select the "DDL" option on the "Language" drop-down.
This will show you the actual transformation code.

You can also use this feature to modify/create customized transformations.

Here is a link to the current documentation:
https://www.sparxsystems.com/enterprise_architect_user_guide/15.2/model_domains/transformationtemplates.html

Regards,
Perry

ducatiross

  • EA User
  • **
  • Posts: 114
  • Karma: +1/-0
    • View Profile
Re: How to transform a logical data model to a physical one
« Reply #4 on: December 08, 2020, 01:52:23 am »
Thanks Perry, I'll have a go at this.

Now we are experiencing lost foreign key information on the transformed physical model ! No idea what is causing this.

Regards,

Matthew

Sunshine

  • EA Practitioner
  • ***
  • Posts: 1309
  • Karma: +120/-10
  • Its the results that count
    • View Profile
Re: How to transform a logical data model to a physical one
« Reply #5 on: December 08, 2020, 07:32:48 am »
There is a diagram on this page that illustrates the Transformations.
https://www.sparxsystems.com/enterprise_architect_user_guide/15.2/model_domains/mdastyletransforms.html


The specific DDL transformation is described here
https://www.sparxsystems.com/resources/mda/ddl_transformation.html

The important thing to remember is that a Platform Independent Model (PIM) can be transformed to a Platform Dependent Model (PDM) where the PDM can be C#, Java, DDL etc. So whilst a PIM is similar to a Logical Data Model its not quite the same.  The DDL Transform translates the PIM into a PDM for physical data model so adds primary and foreign key. You then need to generate the DDL code from the DDL model.


So the steps are something like this
1. Create PIM (Logical Model without Keys)
2. Tranform to PDM (Physical Model) using DDL transform
3. Generated Script(DDL) or Code(C#, Java, etc)

So in the PIM there is no foreign key as its its only added during the transformation to Physical Data Model and not to the C# or Java models.

These concepts are all explained if you search for Model Driven Development. Remember that Spark EA is started off as a UML tool in the Object Oriented(OO) world. OO modelling evolved from Data Modelling to be more advanced and include behavior. The transforms are designed for the OO world and data modelling is just a subset of that.

« Last Edit: December 08, 2020, 07:41:20 am by Sunshine »
Happy to help
:)

ducatiross

  • EA User
  • **
  • Posts: 114
  • Karma: +1/-0
    • View Profile
Re: How to transform a logical data model to a physical one
« Reply #6 on: February 20, 2021, 01:50:24 am »
Hi Sunshine,

Sorry it's taken me so long to reply to your helpful post. I very much appreciated it. We have now managed to do the transformation to the PDM, but we are still experiencing issues with 'orphaned' relationships - those that appear on one side of the connection but where the other is lost. We also cannot see these relationships in the model using any of the standard stuff (relationship matrices, relationship window etc) but they appear when we generate DDL from the PDM .

The DDL also contains duplicate foreign key constraints that we can find no way of locating in the model or understand how they got there.

Has anyone else experienced this ? (Maybe I should start a new thread)

Matthew

Arun8006

  • EA Novice
  • *
  • Posts: 8
  • Karma: +1/-0
    • View Profile
Re: How to transform a logical data model to a physical one
« Reply #7 on: January 10, 2024, 03:49:31 pm »
Thanks for the suggestion Geert.

I eventually found out how to do it. As my logical model was done as Classes representing the entities, the standard 'ERD to Data Model' transformation wouldn't work. After much digging around, and help from a colleague, I used the 'Tables' transformation and this did what I expected - took the Platform Independent logical model classes and converted these into new DB-specific entities (SQL Server in my case) and created links back from the generated objects to the source logical model objects.

But my word, it shouldn't have taken so long to find out how to do this - if only the help files went from 'you can do transformations' to actually showing you what you had to do to perform it, that would save so much time !

Hopefully this thread will help someone else in the future.

Hi Ducatiross,
I have a UML class diagram model, I now want to create a physical data model (DB being SQL Server). Could you point me to some resources on how you managed to create this. I wanted the physical model tables pointing back to the logical data model classes like you mentioned. Thanks in advance.

Regards,
Arun

ducatiross

  • EA User
  • **
  • Posts: 114
  • Karma: +1/-0
    • View Profile
Re: How to transform a logical data model to a physical one
« Reply #8 on: January 10, 2024, 06:20:56 pm »
Hi Arun,

The simplest way I have found is this :
1. Set the database you want to use via Settings/Settings/Database Datatypes and choose your preferred DB from the Product Name dropdown
2. In the model browser, highlight/select the package where your class diagrams are that you want to transform
3. Click Design/Transform/Apply Transformation
4. A list of classes from the package should appear. Deselect any you don't want to be transformed (also click 'Include Child Packages' if you want sub-packages to be transformed as well
5.  On the right of that dialog is the Transformation window. Scroll down and tick 'Tables'
6. Click 'Do Transform' and the transformation will run, creating a new diagram and new physical objects linked to the logical ones you selected originally.

Done !

Hope that helps.

Matthew

Arun8006

  • EA Novice
  • *
  • Posts: 8
  • Karma: +1/-0
    • View Profile
Re: How to transform a logical data model to a physical one
« Reply #9 on: January 12, 2024, 05:06:01 pm »
Thanks Matthew, for the steps.
I have followed the same steps and I see a problem. My models(diagrams) are organised in various packages and my models use classes from other packages. Thus, when I transform I don't see those classes and thus the tables are not generated. Any work around that you could suggest?

And one more issue that I face is, how do I inherit the attributes in tables. What I mean is, I have class A and Class B, class B is inherited from Class A. When I transform, I get Table A and Table B, but Table B does not have the attributes from Table A, I thought that Table B will include all the attributes from Table A and will also contain its own attributes. Any setting that I need to turn On or Off?

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 13256
  • Karma: +554/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: How to transform a logical data model to a physical one
« Reply #10 on: January 12, 2024, 09:02:00 pm »
Thanks Matthew, for the steps.
I have followed the same steps and I see a problem. My models(diagrams) are organised in various packages and my models use classes from other packages. Thus, when I transform I don't see those classes and thus the tables are not generated. Any work around that you could suggest?

And one more issue that I face is, how do I inherit the attributes in tables. What I mean is, I have class A and Class B, class B is inherited from Class A. When I transform, I get Table A and Table B, but Table B does not have the attributes from Table A, I thought that Table B will include all the attributes from Table A and will also contain its own attributes. Any setting that I need to turn On or Off?

Make sure you have all the classes you need in a single package tree and generate from the top level package.

The issue of the inheritance is typically a human decision. There are multiple strategies on how to translate a generalization into a relational table structure.
Which strategy you choose depends on a lot of factors, some of which are not known by EA (like how many records you expect, of how often this table will be queried)

The transformation can give you a head start, but I'm afraid there will always be work to be done to really finish it.

Geert

Arun8006

  • EA Novice
  • *
  • Posts: 8
  • Karma: +1/-0
    • View Profile
Re: How to transform a logical data model to a physical one
« Reply #11 on: January 18, 2024, 09:42:22 am »
Thank you, Geert. Appreciate your response.