SQL Server Extended Properties in PowerDesigner model

[missyprice]

I have a SQL Server database I am reverse engineering to a physical model. Several of the tables in my database have extended properties for various details about the table. For example, "Source Table" & "SSIS Package". My extended properties are not showing up anywhere in my generated physical model. Looking at the DBMS Properties for SQL Server I can see references to extended_properties but it is assuming the name of the extended property to be "MS_Description". My extended properties will always have names other than "MS_Description". Is it possible to customize PowerDesigner such that I could add a new tab under Table properties to maintain extended properties? Ideally, using name/value pairs from this custom tab, PowerDesigner would include the appropriate sp_addextendedproperty statements as part of the table create/alter statement.

[mynynachau]

Thank you for visiting SAP Community to get answers to your questions. Since you're new in asking questions here, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers (if you haven't already), as it provides tips for preparing questions that draw responses from our members.

Should you wish, you can revise your question by selecting Actions, then Edit (although once someone answers your question, you'll lose the ability to edit the question -- but if that happens, you can leave more details in a comment).

Finally, if you're hoping to connect with readers, please consider adding a picture to your profile. Here's how you do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS . By personalizing your profile with a photo of you, you encourage readers to respond.

Best regards

Mynyna

SAP Community moderator

[Ondrej_Divis]

There are two levels of your problem. One is working with MS SQL extended properties in your model, second is how to reverse-engineer these values from DB or generate these values to DB.

First level is quite easy to solve, you just need to define Extended Attributes with the same names as extended properties in MS SQL are. So you need to create extended attributes for metaclass Table. In order to do that, follow the steps below:

1. Make a copy of your MS SQL definition file (NEVER modify the original file!!). Tools - Resources - DBMS... - Create new (second icon from left) and in ''Copy from" field choose your desired DBMS file. Then you will be prompted to save it into new filename with xdb extension. Save it among other xdb files in your PD installation (if your company policy allows you to save files to ProgramFiles subfolders).
2. After you save the file, the DBMS editor should open up. If it doesn`t, go to Tools - Resources - DBMS... and doubleclick your newly created definition file.
3. In DBMS editor, go to Profile\Table\Extended Attributes section and create your new extended attributes as you need (SSIS Package and Source Table for start) and define appropriate datatypes for them. I suppose String will be just fine.
4. That`s it. You`re done. Now you can work with these properties in your model. (There is some more fine tuning like showing these attributes on particular tab in Table properties, but it is just cosmetics).

Second level is more complex task.

1. In your DBMS editor, you need to go to section Script\Objects\Table and explore items starting with "Sql" prefix. You`ll be interested most probably in SqlListQuery and SqlAttrQuery. These queries are playing main part in reverse engineering tables from the DB. So you need to modify them to include the values of your extended properties and hand them over to variables in PD, which are responsible for creating these objects during reverse engineering. Something similar we did for the "Project" extended property in our MS SQL2016. See attached screenshot below. This is more complex task, because you need to decide/find out, which SqlXXX items you need to modify.
2. If you need to generate these values from model into DB, you will have to modify the generation templates too. Script\Objects\Table\Create should be the starting item for you. It is the initial template used for generating proper DDL for CREATE TABLE statement and it is calling other templates. Here you will need to identify all the templates for modification and put your ext.properties there. The templates use proprietary language GTL (Generation Template Language), so it is essential to get familiar with it a little bit. But don`t worry, the syntax is not very complex.

I hope this will help you proceed further. In case you need more help, don`t hesitate to ask. I`ve been creating extensions/modifications for PD for 15 years and trust me, there is no better CASE tool you can choose for implementing modifications like yours.

Regards,

Ondrej

P.S. Next time it would be better if you enter version of MS SQL and your exact version of PD too. Usually it helps others to give better answers.

[missyprice]

Thanks Ondrej! I will give this a try. I am using using PowerDesigner v16.6. My SQL Server database is 2019, but the most current SQL Server DBMS I have for PD is 2014 so I'm using that.