1
General Board / Joining t_connector with t_object, etc.
« on: October 30, 2024, 06:30:12 am »
I am writing a repository query to obtain "data element lineage". I have one model in XML and another imported from Oracle. I have mapped the source/target relationships in Sparx using connector_type = 'Information Flow' as the base class and a custom stereotype = 'ETLMapping'.
We have mapped the source XSDelement(s) to the destination database columns.
(An oversimplification) It seems like when I select the connector_id, the XSDelement name (as an attribute of the XSDelement), and the database column as an attribute of the table), I am getting too many rows returned.
I have seen that an attribute object_id is equal to the object_id of the parent, and that the start_object_id and End_Object_id of the connectors are equal to the source and destination object_id(s).
Am I missing something?
Thanks in advance.
CCW
Continuing...
Here's the query as I described.
When the last two commented lines are added back into the query, the result set goes from 70 rows to 127598 rows.
We have mapped the source XSDelement(s) to the destination database columns.
(An oversimplification) It seems like when I select the connector_id, the XSDelement name (as an attribute of the XSDelement), and the database column as an attribute of the table), I am getting too many rows returned.
I have seen that an attribute object_id is equal to the object_id of the parent, and that the start_object_id and End_Object_id of the connectors are equal to the source and destination object_id(s).
Am I missing something?
Thanks in advance.
CCW
Continuing...
Here's the query as I described.
Code: [Select]
[size=8pt]
With cte_workday_etl_package_diagram as
(select
p.Package_ID
,p.Name Package_Name
,d.Diagram_ID
,d.Name Diagram_Name
from t_diagram d
inner join t_package p on (d.Package_ID = p.Package_ID )
where p.Name = 'Workday ETL Mapping'
) --returns 4 rows
, cte_workday_etl_diagramobject as
(select distinct
do.Object_ID
,etldo.Object_Type
,etldo.Stereotype
from t_diagramobjects do
inner join t_object etldo on (etldo.Object_ID = do.Object_ID)
where do.Diagram_ID in
(select pd.Diagram_ID
from cte_workday_etl_package_diagram pd
)
and
(etldo.Object_Type = 'Class'
and etldo.Stereotype in ('XSDComplexType', 'Table')
)
) --13 rows, source and destination containers intermingled.
, cte_workday_ETLMapping_connector as
( select distinct
conn.Connector_ID
, conn.Start_Object_ID
, conn.End_Object_ID
from t_connector conn
where
conn.Start_Object_ID in
(select Object_ID
from cte_workday_etl_diagramobject)
and
conn.End_Object_ID in
(select Object_ID
from cte_workday_etl_diagramobject)
and conn.Connector_Type = 'InformationFlow'
and conn.Stereotype = 'ETLMapping'
) --70 rows- sounds okay
select * from cte_workday_ETLMapping_connector etlconn2
inner join t_object so on (so.Object_ID = etlconn2.Start_Object_ID)
inner join t_object eo on (eo.Object_ID = etlconn2.End_Object_ID)
-- -- -- -- -- -- -- -- TESTED OK ABOVE HERE -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- 70 rows returned. -- -- -- -- -- -- -- -- -- -- -- --
--inner join t_attribute sattr on (sattr.Object_ID = so.Object_ID )
--inner join t_attribute eattr on (eattr.Object_ID = eo.Object_ID )
;
When the last two commented lines are added back into the query, the result set goes from 70 rows to 127598 rows.