Querying PowerDesigner Repository

[former_member719926]

Hello,

I would like to create a query to have, from my repository, all entity names and all model name of my conceptual models.

Thanks and regards.

[former_member330716]

HI, i also need to extract the list of all the models from Powerdesigner repository, someone knows ?

[Ondrej_Divis]

Well, you have all necessary information below. Just explore Repository metamodel.pdm mentioned in my comment and adjust your query accordingly. Otherwise specify your question more clearly.

[Ondrej_Divis · Accepted solution]

Hi,

PMOBJT contains objects, PMRLSH contains relations between objects. If you need to list your conceptual entities TOGETHER with the models they belong to, you have to join through the PMRLSH table. Inner join on PMOBJT and PMOBJT doesn`t solve the case.

1. BRNC contains ID inside one branch. Use it for restricting your output to a certain repository branch. For joining with PMRLSH you have to use OBJT column instead. OBJT is unique object id inside the whole repository.
2. PMRLSH can connect your conceptual entity to the model it belongs to. See my attached example. Record from PMRLSH connects Entity_3 (5766) to the ConceptualDataModel_1 (5764) it belongs to. PMRLSH is the table you need to join with.
3. No, it isn`t. Number of version of the model (or object) is stored in VRSN column. LVRS is "boolean" indicator telling you whether the row contains latest version of an object or not. So if you have 5 versions of CDM model and you changed Comment of Entity_3 in each of these versions, you will have 5 rows containing Entity_3 in PMOBJT table, but only one will have LVRS = 1. The other 4 will have LVRS = 0. See my attached example with two versions of Entity_3.

Regards,

Ondrej

[former_member719926]

Thank you for your help Ondrej.

1. And for the attributes, it's the same process but with another class?
2. Can we find the repository data model in the PowerDesigner documentation?
3. In our repository, we have two branches: DEV and PROD. For a model it's easy to find because DEV VRSN is different than PROD VRSN, so I can filter VERSN = X or Y, but if I have the same VERSN number for DEV and PROD environnement?

Thanks

[Ondrej_Divis]

Check the Repository Metamodel.pdm in your Examples folder in PD installation.

Ondrej

[former_member719926]

Thank you for your answer Ondrej,

I create this query to have the list of entities, but I don't understand how to use PMRLSH for my needs.

SELECT distinct ent.NAME
  FROM [dbo].[PMOBJT] ent INNER JOIN [dbo].[PMOBJT] mod
                             ON ent.BRNC = mod.BRNC
 WHERE ent.CLSS = 1125         --Class of entities
   AND ent.NAME LIKE 'FAP%'
   AND mod.NAME LIKE 'FAP%'
   AND mod.VRSN = 2
 ORDER BY NAME

Questions:

1. To filter a model with all entities I used BRNC because last week I'm not found PMRLSH, it's correct?
2. How can I use PMRLSH with PMOBJT?
3. I say: ...last version of each object by using LVRS = 1... but I is not LVRS = number_of_version_in_the_model?

Thanks

[Ondrej_Divis]

Hi Francesco,

query tables PMOBJT (contains all objects) and PMRLSH (contains relationships between objects). In PMOBJT table, you should be interested in NAME or CODE columns and of course OBJT column, which contains internal repository object IDs, which you can use to join through PMRLSH table to connect entities to their models. Don`t forget to restrict objects from PMOBJT table only to last version of each object by using LVRS = 1.

Good luck and let me know if you need more help.

Regards,

Ondrej Divis