How to import Reference Joins with different column names

[Former Member]

Hi, there,

My apologies if this has been asked before, but I searched in the forum and couldn't find an answer.  I'm trying to reverse-engineer a PDM by extracting metadata from the Oracle data dictionary and importing it into PowerDesigner 16.5 (SP03) using the Excel import extension.  At the moment, I am operating with some basic objects, and below are the tabs I've created:

• Table
• Table.Key
• Table.Column
• Table.Index
• Table.Index.Index Column
• Reference
• Reference.Reference Join

Everything seems to work correctly, except for one reference join that fails to import properly.  I've narrowed down the case to the following:  If the "Child Table Column" name is different from the "Parent Table Column," then the import function will create a new column object (with some made up name) instead of using the designated child one.

On the other hand, as long as the parent and child columns are homonyms, everything works without issue.

Here's a representative example:

Table:

Owner	Name
OWNER1	MASTER
OWNER1	DETAIL

Table.Key:

Parent	Name	Constraint Name	Primary
MASTER	PK_MASTER	PK_MASTER	True
DETAIL	PK_DETAIL	PK_DETAIL	True

Table.Column:

Parent	Name	Data Type	Mandatory	Primary
MASTER	ID	INTEGER	True	True
MASTER	ParentID	INTEGER	False	False
MASTER	StartDate	DATE	False	False
DETAIL	ID	INTEGER	True	True
DETAIL	Name	VARCHAR2(50)	True	False
DETAIL	Description	VARCHAR2(1000)	False	False

Reference:

Name	Code	Parent Table	Child Table	Foreign Key Constraint Name
FK_DETAIL_MASTER	FK_DETAIL_MASTER	MASTER	DETAIL	FK_DETAIL_MASTER
FK_MASTER_MASTER	FK_MASTER_MASTER	MASTER	MASTER	FK_MASTER_MASTER

Reference.Reference Join:

Parent	Parent Table Column	Child Table Column
FK_DETAIL_MASTER	MASTER.ID	DETAIL.ID
FK_MASTER_MASTER	MASTER.ID	MASTER.ParentID

I've highlighted above the offending object.  In this case, the reference is to the same table (self-referential), but I've tried it to a different table.  It always fails if the Child Table Column name is different from the Parent Table Column name.

Is there a way to make PowerDesigner correctly link the reference in such situations, or am I doing something wrong?

     Best regards,

     - j.

[Former Member · Accepted solution]

And the plot thickens...

I see the exact same effect when the Reference utilizes an AK (unique constraint) on the Parent Table, rather than the PK.  In such cases, it replaces the Join Columns on the Child Table with new "fake" instances (which are copies of the AK columns), while duplicating their counterparts on the Parent Table.

In all both cases, I am able to correct the error manually after import, but it seems that there should be some way of properly defining the dependencies on the spreadsheet to avoid the problem.

Anyone has a thought?

     Regards,
     - j.

[Former Member · Accepted solution]

Hi James,

It appears that I am running into the same issue with the exact same message; even though my PK/FK reference is not a self-reference as in the example that you gave.  Just as yours, the import replaced my original column(s) with the name inherited from the parent PK.

You indicated that you had a work around using some form of duplicate Excel (with a different name).  I tried it and it did not revert the names back to their original form.  Instead, it added a new column with the old name.  Now the tables are more messed up.

Would you care to share your work around?

Thanks.

[former_member185317 · Accepted solution]

The Excel import is based purely on name matching either on the name or code attribute, depending on what you set on the Excel Import::Options. The Excel import is not meant to be a substitute for the DBMS reverse engineering capabilities which deal with these types of complexities.

Is there a reason not to just use the DBMS reverse engineering method vs. going through Excel? 

Cheers,

-Matt C.

[Former Member · Accepted solution]

Hi, Matt,

Thanks for responding.  The reason I am trying to use the Excel import is two-fold:  first, we're trying to create a streamlined process to reverse-engineer existing databases, and we believe that separating the extraction of metadata from the import process itself (via standardized queries to the data dictionary) will allow us to lower the barriers of participation, rather than having to train everybody on how to use Power-Designer to reverse-engineer their database.

Second, and more importantly, it is our experience that in practice, the built-in DBMS Reverse Engineering mechanism in PowerDesigner is flakey, buggy, and not very reliable.  For instance, in Oracle tables that have interval partitions, the process takes much too long, and sometimes fails altogether by attempting to retrieve every single partition instance rather than the metadata to describe the intervals.  When this happens, being an all-or-nothing affair, the process needs to be re-started and there is no real way to work around it.

Using the Excel Import extension, we hoped to control precisely the objects to be imported.

All that said, I understand that the Excel import is using purely name matching, but as I described in my previous posts, there seem to be some rather naïve assumptions made on references; namely expecting that the FK is associated specifically with the PK (not AK), and that both parent and child share the same name.

Is there a way to correct this, or am I doing something wrong?

     Regards,

     - j.

[c_baker · Accepted solution]

If the DBMS reverse-engineering process is 'flakey' then you really should be working with support on what is happening.  You can also examine the queries in the .xdb file to determine what is happening and if it might be related to security in your system, etc.  Sometimes customers do lock down system tables in the DBMS that might be causing the issue.

That being said, the real reason I would not suggest using Excel is more to do with the fact that you will be missing so much metadata that PD could capture automatically.  The creation of an Excel sheet is subject to errors, typos, etc.

In one recent situation, I had such files provided and was somehow expected to understand that the 'CODE' on one sheet was the 'NAME' on the other - with absolutely no way to actually connect the 2 during the Excel import process! (Turns out the files actually came from PD! - go figure)

Also, to capture the many dimensions of metadata required for the various DBMS objects might require many sheets and the creation/customization of the various 'Excel Import' objects to match.  In the end this might be your biggest headache.

It would honestly be easer to create 'settings' for the PD 'reverse-engineering' to make sure you only capture what you want, and then run it once.  This might help in avoiding some of the issues with reverse-engineering from the DBMS you have identified.

If you still want to avoid direct reverse-engineering from the DBMS, consider instead generating the DDL from the database instead and reverse engineer into PD ('Using Script Files').   This would avoid the performance issues you have identified, uses the GTL in the .xdb (instead of the Sqlxxxx sections) and would still capture much more metadata than you might be able to put into an Excel spreadsheet.

You could still generate separate scripts (e.g. first table spaces DDL from Oracle EM, then table DDL, etc) and define them in the correct order in the dialog.  (When doing this, I have found it sometimes easier then to correct the GTL in the .xdb if there are some import errors, instead of trying to solve the SQL reverse engineering queries).

In the end, if you want to avoid direct reverse-engineering, I would suggest the DDL route over Excel files.

HTH

Chris

[Former Member · Accepted solution]

Hi, Chris,

I encountered several issues with reverse engineering from DDL as well, specifically with the grammar parsing.  It seems that the DDL parser is very picky on how the DDLs must be generated, to the point of failing -- or worse, misinterpreting! -- when encountering some very valid and fairly common script blocks.

Not only that, it required too much manual work in order to curate the immense list of scripts for each database in their correct order.

Trust me, I did not choose this route lightly.

After much effort, I've managed to prepare queries that acquire the necessary metadata of interest, in the proper order and language supported by the PD Metamodel and the Excel Import extension.  That indeed incurred some rather nasty headaches, but that's done.

I understand that most of my troubles may be related to lack of experience mucking about with excruciatingly complicated innards of PowerDesigner; but honestly, at this point I do not have much inclination in starting from scratch and learning an entirely new programming model in order get my work done.

In the end, I have a rather stable and robust mechanism to import most of everything of interest for our purpose, including ADTs, Tables, Constraints, Keys, Partitions, Indices, References, Physical Options, etc.  The only kinks left are the two problems I have reported above.  Perhaps in the future we'll improve the process by addressing it as you suggested; but at the moment, I'm more interested in resolving the specific questions I asked.

I do appreciate your assistance and attention.

     Best regards,

     - j.

[former_member185317 · Accepted solution]

Hi James,

I know PowerDesigner has made some improvements on the Oracle partition front, can I ask what version you are using?

The DDL parser within PowerDesigner is pretty easy to manipulate, especially if you have done some of the work you explain above. Since you have a consistent script from a specific tool that is formatting, have you thought about just modifying the DDL structure in the PD DBMS Definition file. I have many customers who have extended the base definition to meet their needs from reverse or generation and just call it DBMS <COMPANY NAME/APP> Version. Plus, now this can be stored in the Library and downloaded to the user desktop upon connection to the Repository.

But on you original question, when importing the Reference object, I see you are defining the Parent table and Child table. I recommend trying to also define the parent key and child key?

The more explicit you are in the definition, the better the outcome in my experience.

HTH,

-Matt C.

[Former Member · Accepted solution]

Hi, Matt,

Thanks for following up.  I am using PD 16.5 (SP03).  Unfortunately, our enterprise is committed to this version, so it may be a while before we can upgrade.

As for modifying the DDL structure, indeed I considered it, but I thought it would be too daunting a task to figure out how to deal with PD extensions and all those things.  Personally, I thought it would be quite simple to just prepare queries to the Oracle data dictionary, populate them in an Excel spreadsheet, et voila.  Little did I know that dealing with the Excel Import extension was going to be such a nightmare.

Looking back, it would have been more profitable to have spent all that effort into just hacking away the DDL structure or the reverse-engineering module.  Hindsight is an insufferable mistress.

But on you original question, when importing the Reference object, I see you are defining the Parent table and Child table. I recommend trying to also define the parent key and child key?

I only use the columns described on the top post, namely

• Reference:
  • Name
  • Code
  • Foreign Key Constraint Name
  • Parent Table
  • Child Table
  • Parent Key
• Reference.Reference Joins
  • Parent
  • Parent Table Column
  • Child Table Column

The problem I've discovered is that, while the Metamodel describes several available attributes for each object, not all of them can be mapped via the Excel Import interface.  In fact, exploring the available attributes for the above objects in the "attribute/object mapping" pop-up window during import, I can't see anything referreing to children keys.  That said, I'll give it a go just in case.

Thanks again,

     - j.

Message was edited by: James Pujals Whoops! Submitted prematurely! Sorry.

[former_member200945 · Accepted solution]

Hi James,

A possible workaround is using script to deal with these failed reference creation.

This need three steps

1) Find out all these references which has different column name in the excel file.

    Move them to a new excel file.

2) The remaining content should be good.  Use Excel Import extension to import them.

3) Write script to read the new excel file and create reference.

The code sample can be found at

http://scn.sap.com/thread/3782845

[former_member185317 · Accepted solution]

Hi James,

I understand, been there done that in the past, even before the Excel Import function, when we used to build our own Excel vbs importers. But did you try the Parent and Child column that I mentioned?

Cheers,

-Matt C.

[Former Member · Accepted solution]

Hi, Matt,

I have not tried it yet, but will certainly do soon and report back.  Thanks again for the feedback.

     Regards,

     - j.

[GeorgeMcGeachie · Accepted solution]

Hello James

I've had a similar issue before with relationships, where the relationship joined on the AK not the PK. I got around this by :

• in the relationship sheet, added the name of the Parent Identifier
• added a sheet for relationship joins, specifying the parent and child attributes. It only contains the exceptions, of course

[Former Member · Accepted solution]

Hi, George,

Thanks for the tip.  Can you give an example of what you mean by adding the "Parent Identifier" to the relationship sheet?

     Regards,

     - j.

[GeorgeMcGeachie · Accepted solution]

Sorry, my answer was over-simplified. Here's what I did to import a a different relationship join:

In a sheet of relationships, tell PD to use a specific identifier in a relationship join

Name	Entity 1	Entity 2	Parent Identifier
relationship name	entity 1 name	entity 1 name	entity 1 name.<identifier name>

In a separate sheet, provide details for the relationship join:

Parent	Parent Attribute	Child Attribute
relationship name	This is an AK att	child entity name.child attribute name

Obviously, the parent attribute must be present in the named 'parent identifier', or who knows what will happen

[Former Member]

I also made some Excel importing of references, and did't have any issue with Child Table Column different from Parent table column name, but have some other issues. So have few ideas:

1) the log you proveded says:

[Log] Error: Could not set attribute ParentTableColumn for Reference Join 'FK_MASTER_MASTER.' with value 'Column 'MASTER.ID'' (Cannot set value 'Column 'MASTER.ID'' for attribute Parent Table Column of Reference Join 'FK_MASTER_MASTER.': Parent Table Column cannot be null.) 

So could you plese check, is the issue stays the same if you set the ID columns as NOT NULL ?

2) It very importans to write Table names and Column names in excel file the same case as they are in you model (because Excel importer works in a case-sensitive way, and case misstyping lead to some kind of undefinded behavior). So in my experince I had SOMETABLE in model, and trying to import reference to this table from Excel where it was called as SomeTable. This lead to warnings/errors of excel importer, looks like it could not have found the SOMETABLE cause tryed to find SomeTable. Then it creates new table and called it SomeTable2, which have no columns at all, so reference created but wihout joins.

Unfortunatly Excel-importer behivior is always case-sensitive despite the seting Tools / Model Options / Model Settings / All Objects Name/Code case sensitive which was disabled in my case (for MS SQL Server 2005)

3)  I also have some suspisions that it could be a specific behavoir because of settins: Tools / Model Options / Model Settings / Reference / "Change parent allowed" or "Auto-migrate columns". And "Default link on creation" beter to setup as User-defined.

In my case they are: Change parent allowed" = YES, Auto-migrate columns = NO -- and import of table-self-references works fine.

[Former Member]

I enabled "debug" mode in the import script and include below a relevant excerpt of the resulting log.  Like #19 points to the culprit.

...
Log] Importing data from table Reference.Reference Join
[Dbg]     Link class detected for Reference Join
[Dbg]     Table has at least one mandatory column: Parent
[Dbg]    Next line of table Reference.Reference Join is 2
[Dbg]     Reading values for line 2, 3 column(s) found
[Dbg]   Common option CreateIfNotExist is common one: True
[Dbg]   Common option RefAttribute is common one: Code
[Dbg]     Retrieving parent of Reference.ReferenceJoin value=FK_MASTER_MASTER
[Dbg]     Filling class key dictionary for class Reference attribute Code Physical Data Model 'PhysicalDataModel_1'
[Dbg]      1 object(s) found
[Dbg]     Retrieving reference object with key=Code value=FK_MASTER_MASTER
[Dbg]     Retrieving local reference object with reference=Code qualified value=MASTER.ParentID
[Dbg]     Retrieving local reference object with reference=Code qualified value=MASTER.ID
[Dbg]     Creating new sub-object Reference Join
[Dbg] SubObject False?
[Dbg]     Retrieving local reference object with reference=Code qualified value=MASTER.ID
[Dbg]      Commit cell data (obj) Parent Table Column = Column 'MASTER.ID'
[Log] Error: Could not set attribute ParentTableColumn for Reference Join 'FK_MASTER_MASTER.' with value 'Column 'MASTER.ID'' (Cannot set value 'Column 'MASTER.ID'' for attribute Parent Table Column of Reference Join 'FK_MASTER_MASTER.': Parent Table Column cannot be null.)
[Dbg] SubObject False?
[Dbg]     Retrieving local reference object with reference=Code qualified value=MASTER.ParentID
[Dbg]      Commit cell data (obj) Child Table Column = Column 'MASTER.ParentID'
[Dbg]     Imported object: Reference Join 'FK_MASTER_MASTER.'
[Dbg] Missing destination extremity for Reference Join 'FK_MASTER_MASTER.'
[Log] Error: Reference Join 'FK_MASTER_MASTER.' will not be imported due to missing link extremity
[Log]   no object created or updated
...

It seems that PowerDesigner is attempting to look for a key (PK?) on the Child Table Column and failing.  Consequently, it creates a new column on the Child Table with an auto-generated name composited from the Parent Table Column, sets it as the target of the reference, and removes the original column (ParentID).  The ultimate effect is that the target column is replaced with a new object derived from the source one, and in the process mangling all other Keys, Indices, Constraints, etc. that referred to it.

A work-around we have discovered is to re-affirm the Table Columns by duplicating the Table.Column tab in the Excel worksheet.  This seems to return the Child Table Column to its original name and definition, and repair all other object references.

However, for this to work, the duplicate tab must be given a different and unique name (Excel won't accept duplicate tab names), which then requires manual intervention during the import in order to map it to the correct object.

This is highly annoying, to say the least, especially since we are looking to automate this process.  Not to mention that the duplicate tab feels unclean, being too much of hack to be part of a reliable process.

Does anybody know of a proper solution or can offer additional suggestions?

     Regards,

     - j.