Modelling Reference Data in PowerDesigner

[former_member649155]

I have a requirement where in I want to model as well as store the reference data in the model, so that when the DDL is executed the reference tables are populated with the data. For example, I want to store data like, Province code or policy status in the PDM itself. I have defined the table to hold such data. The question I have now is what is best option to enter and store the data in the data model. May be I am not looking at the right place and any pointer would be helpful.

[Ondrej_Divis]

One more question, what is the number of data records you need to keep? Is it tens, hundreds or thousands of row records?

[former_member649155]

As things stand now, one such table holds reference data for the look up. Currently has 300+ records. I have 3 such tables in the model. They have typical structures has code, value, date etc.

Thanks for your help. Currently I have them as SQL Insert under 'post' script.

[Ondrej_Divis]

Using Script tab in table properties is quite good approach to solve this (in case you don`t mind writing INSERT statement for each record row). What is the ideal solution you are looking for? Do you want a possibility to take raw data from CSV file and let PD generate appropriate INSERT statements? Or do you want to manage these data directly in PD model and then generate INSERT statements together with DDL script? Do you need PD to do some automated (or Check Model based) checks, which will verify numbers of table columns with number of columns in your data? What is the final goal you need to solve with this? Which functionality you require (would appreciate) from PD? After having these answers I might be able to propose you best solution PD can offer.

[former_member649155]

Thank you very much!. Actually, your idea of using raw data from CSV addresses my requirements. The Developers are maintaining a CSV file which was the input my INSERT commands ( I was manually entering in the Script tab though). Reading directly from the CSV and inserting into the table will address my immediate requirement to maintain the reference data. Assuming I can bundle this CSV in the PD model itself.

Having said that I would like to learn about the second approach purely from educational purposes. But the first one will be one I would opt for now.

[Ondrej_Divis]

You`re welcome. Explaining rules how to extend PD metamodel exceeds scope of this network, so if you`re interested in getting more knowledge and best practices for using full potential of PowerDesigner, you can find me on LinkedIn. Here`d be great if you just mark the answer that helped you most as correct answer:)

Ondra

[GeorgeMcGeachie]

I run a members-only Slack group for PowerDesigner users - www.mypowerdesigner.slack.com - if you want to join, send an email to concierge@powerdesigner.training. All I need is your name and email address.

[Ondrej_Divis]

Second option would be to create Extended Composition probably on Table level and then accordingly modify DDL generation templates in your DBMS file (or you could put it into separate Extension too). Each member of that Extended Composition will represent one record in that table. This will give you more freedom/possibilities in case Testa Data Profiles won`t be sufficient for you. For example if you need 5 different values for Column_1 and match them with 5 values in Column_2 in one exact way, Test Data Profiles won`t be enough. They just simply generate test data. They were not meant to populate enumeration tables, so they might not have all the functions necessary.

But I definitely agree with George, Test Data Profiles should be option No.1 to explore, since it requires no customization. In case Test Data won`t do the job, you can get back here.

Regards,

Ondrej

[GeorgeMcGeachie]

I think the simplest method is to create Test Data profiles - each could hold the list of values for a clumn. Attach each profile to the relevant columns, then use "Generate Test Data" on the Datbase menu to generate the script to populate the tables; make sure you only generate the necessary number of rows.

It would also be possible to do this using a model generation in an extension or the DB definition file - it could generate the required SQL for specific tables.