Query to retrieve Entity List from ER_Diagram

Follow

Velur

December 12, 2016 14:43

Hi all

I was running a query to retrieve entity list from a ER diagram (display area)  under a subject area. As per my knowledge the hierarchy is Model has many subject areas and each subject area has many ER Diagrams . I tried the below query to list  super types for a ER Diagram under a  subject area  

SELECT distinct
 ed.name,s.name,
            tran (LEnP.Name) 'Parent Entity Name',
            tran (LEnP.User_Formatted_Physical_Name) 'Parent Table Name',
            tran (LEnC.Name) 'Child Entity Name',
            tran (LEnC.User_Formatted_Physical_Name) 'Child Table Name',
            tran (LRel.Type) 'Relationship Type'
            FROM
Relationship LRel
            INNER JOIN Entity LEnP
            ON LRel.Parent_Entity_Ref = LEnP.Id@
            INNER JOIN Entity LEnC
            ON LRel.Child_Entity_Ref = LEnC.Id@
    INNER JOIN M0.USER_ATTACHED_OBJECTS_REF saf
 ON LEnP.id@=saf.value@
  INNER JOIN M0.Subject_Area s
ON               s.id@=saf.id@
INNER JOIN ER_DIAGRAM ed
on s.id@=ed.owner@
            where tran(LRel.TYPE) = 'Subtype'  and s.name ='Visualization' and ed.name='ER_Diagram_14818'
            ORDER BY 1,3

 

It shows list of subtypes even if  no entity is populated in ER_Diagram_14818 ( The subject area has list of entities populated to other ER Diagrams e.g. Logical/Physical shown in the picture  above ). The Question is how to get the list of entities displayed under an ER_Diagram  

Can you help

Ani

1

• 

  Michael Silves December 13, 2016 18:18

  Ari,

  Be careful. Subtype relationships always link to a Subtype Symbol and an entity.  The parent entity of the Subtype Symbol is the supertype and the child entities of the Subtype Symbol are the subtypes. 

  In your query you navigate from Parent Entity to Subject Area. This will automatically include all the entities you have selected so far. When you go from Subject Area to ER Diagram all the entities you have selected so far will still be included in the result set. You need to go from Parent Entity to ER Diagram to Subject Area to get the subset you want.

  Disclaimer: I do not have ERwin and can't test anything. I also don't have metadata for ER Diagram so the following query is undoubtedly wrong but it might provide insight for a different approach.

  select subject_area.Name 'Subject Area'
           ,er_diagram.Name 'ER Diagram'
           ,supertype_entity.Name 'Supertype Entity Name'
           ,supertype_entity.User_Formatted_Physical_Name 'Supertype Table Name'
           ,subtype_entity.Name 'Subtype Entity Name'
           ,subtype_entity.User_Formatted_Physical_Name 'Subtype Table Name'

  from M0.Subject_Area

  join ER_Diagram
    on subject_area.Id@ = er_diagram.Owner@
  and er_diagram.Name = 'ER_Diagram_14818'

  join M0.Referenced_Entities_Ref
    on er_diagram.Id@ = referenced_entities_ref.Id@

  join M0.Subtype_Symbol
    on referenced_entities_ref.Value@ = subtype_symbol.Id@

  join M0.Relationship parent_relationship
    on subtype_symbol.Id@ = parent_relationship.Child_Entity_Ref

  join M0.Entity supertype_entity
    on parent_relationship.Parent_Entity_Ref = supertype_entity.Id@

  join M0.Relationship child_relationship
    on subtype_symbol.Id@ = child_relationship.Parent_Entity_Ref

  join M0.Entity subtype_entity
    on child_relationship.Child_Entity_Ref = subtype_entity.Id@

  where subject_area.Name = 'Visualization'

  order by supertype_entity.Name
               ,subtype_entity.Name
   

  If this doesn't work try replacing M0.Referenced_Entities_Ref with M0.User_Attached_Objects_Ref. 

  Hope this helps,

  --mls

  Note: the Relationship Navigation subject area in the attached metamodel illustrates how it worked a few years back.

  http://datamorph.com/MetaModelRdb/MlsMetamodel.htm 

  0

  Comment actions Permalink
• 

  Velur December 14, 2016 04:36

  mls

  Thanks I tried the first query, not rows retrieved and tried  replacing M0.Referenced_Entities_Ref with M0.User_Attached_Objects_Ref without luck.

  I will do some R&D and try to get it working

  Thanks a lot for providing me the meta model

  Ani

   

  0

  Comment actions Permalink
• 

  Michael Silves December 15, 2016 00:44

  Ani,

  You are certainly welcome for the meta model link. 

  You might try altering your original query to include ER Diagram in the filter sequence:

  original query snippet:
  
    INNER JOIN M0.Subject_Area s 
  ON               s.id@=saf.id@
  INNER JOIN ER_DIAGRAM ed
  on s.id@=ed.owner@

  change to:

   INNER JOIN ER_DIAGRAM ed
  ON               ed.id@=saf.id@
  INNER JOIN M0.Subject_Area s
  on s.id@=ed.owner@

  If this doesn't work it means that both M0.User_Attached_Objects_Ref and M0.Referenced_Entities_Ref only connect objects to subject areas and there are one or more other vector tables to specify objects in er diagrams. 

  I used to have a query to find vector tables and their referents. This might be it:

  select Name
           ,tran(Participating_Object_Ref)  'Vector Attribute Owner (Id@)'
           ,tran(Referenced_Type_Ref)    'Referenced type (Value@)'
  from M1.Property_Type
  join M1.Association_Type
    on Id@ =Participating_Property_Ref
  where Tag_Is_Reference = 'T'
    and tran(Data_Type) = 'Short Id Vector'
  order by Name
               ,tran(Participating_Object_Ref)
  
  

  If this actually works it should give you a list of objects with vector attributes. Look for er diagram somewhere in the list of Vector Attributes Owner. 

  --mls

   

   

   

  Column

   

  0

  Comment actions Permalink

Please sign in to leave a comment.