I have written a Sparx repository query to obtain "data element lineage". I have a source model in XML and the target is imported from Oracle. We have mapped the source XSDelement(s) to the destination database columns in Sparx using
connector_type = 'Information Flow' as the base class
and a custom stereotype = 'ETLMapping'.
Below is the query/view I am using.
[Near the top of the query you will see a literal package name, 'Workday ETL Mapping'. This package defines the scope for the query; it contains the several diagrams which have the ETL source/target mappings defined.]
The Sparx view definition script follows...
USE [SparxProjects]
GO
/****** Object: View [dbo].[vSparx_Workday_ETL_Source_Target_Object_Element_Mapping] Script Date: 11/13/2024 2:26:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[vSparx_Workday_ETL_Source_Target_Object_Element_Mapping]
as
With cte_workday_etl_package_diagram as
(select distinct
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'
)
, cte_workday_etl_package_diagram_object as
(select distinct
pd.Package_ID
,do.Diagram_ID
,do.Object_ID
,o.Name Object_Name
,o.Object_Type
,o.Stereotype Object_Stereotype
,pd.Package_Name
,pd.Diagram_Name
from t_diagramobjects do
inner join cte_workday_etl_package_diagram pd
on (pd.Diagram_ID = do.Diagram_ID)
inner join t_object o
on (o.Object_ID = do.Object_ID)
where
(o.Object_Type = 'Class'
and o.Stereotype in ('XSDComplexType', 'Table')
)
)
, cte_workday_etl_diagram_object_element as
(select distinct
pdo.Object_ID ETL_Object_ID
,pdo.Object_Name ETL_Object_Name
,attr.Name ETL_Element_Name
,attr.ea_guid ETL_Element_guid --will be used to join connector end info
from cte_workday_etl_package_diagram_object pdo
inner join t_attribute attr on (attr.Object_ID = pdo.Object_ID)
)
, cte_conn_end_guids as
(select conn.connector_id
,conn.StyleEx
,SUBSTRING(conn.StyleEx
,CHARINDEX('{' ,SUBSTRING(conn.StyleEx,1,100)) --start
,(CHARINDEX('}', SUBSTRING(conn.StyleEx,1,100)) --right_curly_bracket
- CHARINDEX('{',SUBSTRING(conn.StyleEx,1,100)) --first left_curly_bracket
+ 1 ) --length
) "first_guid_value"
,SUBSTRING(
conn.StyleEx --expression
,CHARINDEX(';' , SUBSTRING(conn.StyleEx,1,100)) + 6 --start of guid substring
,len(conn.StyleEx) - CHARINDEX(';' , SUBSTRING(conn.StyleEx,1,100)) - 7 --length of substring
) "second_guid_value"
,case
when (SUBSTRING(conn.StyleEx , 1, 2 ) = 'LF'
and SUBSTRING(conn.StyleEx , 3, 1 ) = 'S')
then 'START_CONN_GUID'
when (SUBSTRING(conn.StyleEx , 1, 2 ) = 'LF'
and SUBSTRING(conn.StyleEx , 3, 1 ) = 'E')
then 'END_CONN_GUID'
else null
end "FirstConnEndDirection"
,case
when (SUBSTRING(conn.StyleEx
,CHARINDEX(';' ,conn.StyleEx,1 ) + 1
, 2
) = 'LF'
and
(SUBSTRING(conn.StyleEx
,CHARINDEX(';' ,conn.StyleEx,1) + 3
, 1
) = 'E'
)
)
then 'END_CONN_GUID'
when (SUBSTRING(conn.StyleEx
,CHARINDEX(';' ,conn.StyleEx,1 ) + 1
, 2
) = 'LF'
and
(SUBSTRING(conn.StyleEx
,CHARINDEX(';' ,conn.StyleEx,1) + 3
, 1
) = 'S'
)
)
then 'START_CONN_GUID'
else null
end "SecondConnEndDirection"
from dbo.t_connector conn
where conn.StyleEx is not null
and conn.Connector_Type = 'InformationFlow'
and conn.Stereotype = 'ETLMapping'
and conn.Start_Object_ID in (select pdo.object_id
from cte_workday_etl_package_diagram_object pdo)
and conn.End_Object_ID in (select pdo.object_id
from cte_workday_etl_package_diagram_object pdo)
)
, cte_start_conn_elements as
( select conn.connector_id
,sattr.Name Start_Element_Name
,sattr.Type Start_Element_Type
,sattr.Stereotype Start_Element_Stereotype
,sattr.ea_guid Start_Element_guid
,sattr.ID Start_Element_ID
,sattr.Object_ID Start_Element_Object_ID
,sattr.Notes Start_Element_Notes
from cte_conn_end_guids conn
inner join t_attribute sattr on (sattr.ea_guid = conn.first_guid_value
and conn.FirstConnEndDirection = 'START_CONN_GUID'
)
UNION
select conn2.connector_id
,eattr.Name Start_Element_Name
,eattr.Type Start_Element_Type
,eattr.Stereotype Start_Element_Stereotype
,eattr.ea_guid Start_Element_guid
,eattr.ID Start_Element_ID
,eattr.Object_ID Start_Element_Object_ID
,eattr.Notes Start_Element_Notes
from cte_conn_end_guids conn2
inner join t_attribute eattr on (eattr.ea_guid = conn2.second_guid_value
and conn2.SecondConnEndDirection = 'START_CONN_GUID'
)
)
, cte_end_conn_elements as
( select conn.connector_id
,eattr.Name End_Element_Name
,eattr.Type End_Element_Type
,eattr.Stereotype End_Element_Stereotype
,eattr.ea_guid End_Element_guid
,eattr.ID End_Element_ID
,eattr.Object_ID End_Element_Object_ID
,eattr.Notes End_Element_Notes
from cte_conn_end_guids conn
inner join t_attribute eattr on (eattr.ea_guid = conn.first_guid_value
and conn.FirstConnEndDirection = 'END_CONN_GUID'
)
UNION
select conn2.connector_id
,eattr.Name End_Element_Name
,eattr.Type End_Element_Type
,eattr.Stereotype End_Element_Stereotype
,eattr.ea_guid End_Element_guid
,eattr.ID End_Element_ID
,eattr.Object_ID End_Element_Object_ID
,eattr.Notes End_Element_Notes
from cte_conn_end_guids conn2
inner join t_attribute eattr on (eattr.ea_guid = conn2.second_guid_value
and conn2.SecondConnEndDirection = 'END_CONN_GUID'
)
)
, cte_workday_etl_connector_objects_elements as
(select
spdo.Diagram_ID
,spdo.Diagram_Name
,spdo.Package_ID
,spdo.Package_Name
,seconn.Connector_ID
,spdo.Object_Name Start_Object_Name
,spdo.Object_Type Start_Object_Type
,spdo.Object_Stereotype Start_Object_Stereotype
,seconn.Start_Element_Name
,seconn.Start_Element_Object_ID
,seconn.Start_Element_Type
,seconn.Start_Element_Stereotype
,seconn.Start_Element_guid
,seconn.Start_Element_ID
,seconn.Start_Element_Notes
,epdo.Object_Name End_Object_Name
,epdo.Object_Type End_Object_Type
,epdo.Object_Stereotype End_Object_Stereotype
,eeconn.End_Element_Name
,eeconn.End_Element_Object_ID
,eeconn.End_Element_Type
,eeconn.End_Element_Stereotype
,eeconn.End_Element_guid
,eeconn.End_Element_ID
,eeconn.End_Element_Notes
from cte_start_conn_elements seconn
inner join cte_end_conn_elements eeconn
on (seconn.Connector_ID = eeconn.Connector_ID)
inner join cte_workday_etl_package_diagram_object spdo
on (spdo.Object_ID = seconn.Start_Element_Object_ID)
inner join cte_workday_etl_package_diagram_object epdo
on (epdo.Object_ID = eeconn.End_Element_Object_ID)
)
select distinct
s_t_element_mapping.Diagram_ID
,s_t_element_mapping.Diagram_Name
,s_t_element_mapping.Package_ID
,s_t_element_mapping.Package_Name
,s_t_element_mapping.Connector_ID
,s_t_element_mapping.Start_Object_Name
,s_t_element_mapping.Start_Object_Type
,s_t_element_mapping.Start_Object_Stereotype
,s_t_element_mapping.Start_Element_Name
,s_t_element_mapping.Start_Element_Object_ID
,s_t_element_mapping.Start_Element_Type
,s_t_element_mapping.Start_Element_Stereotype
,s_t_element_mapping.Start_Element_guid
,s_t_element_mapping.Start_Element_ID
,s_t_element_mapping.Start_Element_Notes
,s_t_element_mapping.End_Object_Name
,s_t_element_mapping.End_Object_Type
,s_t_element_mapping.End_Object_Stereotype
,s_t_element_mapping.End_Element_Name
,s_t_element_mapping.End_Element_Object_ID
,s_t_element_mapping.End_Element_Type
,s_t_element_mapping.End_Element_Stereotype
,s_t_element_mapping.End_Element_guid
,s_t_element_mapping.End_Element_ID
,s_t_element_mapping.End_Element_Notes
from cte_workday_etl_connector_objects_elements s_t_element_mapping
GO
Thanks to Geert Bellekens at https://sparxsystems.com/forums and https://stackoverflow.com/users/3379653/qwerty-so for their helpful remarks and suggestions.
Reply here with any improvement suggestions or error discoveries. Sorry about the formatting, it parsers fine in SQL.
Best,
CCW
