Messages

1

General Board / Re: Joining t_connector with t_object, etc.

« on: November 14, 2024, 03:09:33 am »

Thanks much, Geert!  Here is the solution query for my situation, retrieving attribute level detail for the two ends of a connector.

Code: [Select]

[color=blue][size=10pt]
[font=courier]
USE [SparxProjects]
GO
/****** Object:  View [dbo].[vSparx_Workday_ETL_Source_Target_Object_Element_Mapping]    Script Date: 11/13/2024 10:09:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create  or alter  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
[/color][/size][/font]


2

General Board / Re: Joining t_connector with t_object, etc.

« on: November 02, 2024, 05:21:45 am »

…Not seeing it yet. still searching in documentation...Do you know of a more specific answer to my question?

Thank you,
CCW

3

General Board / Re: Joining t_connector with t_object, etc.

« on: November 01, 2024, 08:00:42 am »

Geert,
This is the real question, I think.  How to retrieve from the Sparx repository using SQL the features at the two ends of a connector.
It is obvious visually in a diagram, and the containing objects have their Object_ID foreign keys in the connector, but how is the feature level connection stored in the repository database?

1.  I just had a thought.  What does Sparx do when you right-click on an "association" in a diagram and select "Link to Element Feature"?

2. I do not see any consistent way that the SourceRole and DestRole columns in the t_connector table are used.   


Any explanation from anyone is appreciated!
CCW

4

General Board / Re: Joining t_connector with t_object, etc.

« on: October 31, 2024, 12:23:58 am »

Thank you, Geert.  But I think there might be another problem.

I am relying on t_attribute.Object_ID to identify the child XSDelement/Column where
the child (sattr.Object_ID = the parent so.Object_ID) and that would indeed give duplicates.

What about using t_attribute.ea_guid to "uniqueify" the children within a parent? ...or possibly t_attribute.ID?

I am not seeing how to code the join between the connector and the "child" XSDelement/Column so as to get distinct combination of source attribute / connector / destination attribute.

CCW

5

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.

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.

6

Automation Interface, Add-Ins and Tools / How does the EA repository represent a view column?

« on: May 10, 2024, 04:33:23 am »

Trying to create a matrix mapping database view columns to database table columns.
How does the EA repository represent a view column?  What is the join condition for navigating from the view column to the parent view object? 

I see that I have created connectors from table columns to XSDElements, but I'm stuck trying to connect table columns to view columns.

Thank you,
CCW

7

Suggestions and Requests / Re: Improved support for View columns

« on: May 10, 2024, 04:16:17 am »

Agree!

8

General Board / How to prevent Sparx from inserting HTML markup codes in text (e.g., Notes)?

« on: April 20, 2024, 03:37:17 am »

I retrieve object properties like column length, etc., and especially Notes from SQL Server repository for a user walkthrough. The Notes contain the definitions obtained from the users.  But I get things like this "<font color="#424242">The medication given to the patient</font>."
How can I set/reset all fonts (typeface, size, color, etc.) for all objects in the entire model? It's a bit tedious to manually change these for every "offending" piece of text in the thousands of columns in the model!

I feel like I've tried all of the options in the EA UI.

Thank you in advance,
CCW

9

General Board / Re: EA - SQL to show hierarchy of packages?

« on: April 10, 2024, 12:34:42 am »

Thanks!

10

General Board / Re: EA - SQL to show hierarchy of packages?

« on: April 06, 2024, 05:35:19 am »

Thanks.  And does t_object.ParentID for a column get you to the containing table's object_id?

And then the table's t_object.package_id get you to the containing package's package_id?

11

General Board / EA - SQL to show hierarchy of packages?

« on: April 06, 2024, 02:29:11 am »

Hi -  Can someone share a SQL (SQL Server) query to show the hierarchy of packages in a "project"?  A query to run against the Sparx repository.
Thank you,
CCW

12

Suggestions and Requests / Re: Database builder - differences report

« on: March 07, 2023, 08:52:28 am »

Hi Geert, et al., We, too could very much use the ability to export (to Excel, for example) the differences as well as the chosen resolution actions.

If the capability already exists, where do I find it??

Thank you,
CCW

13

Automation Interface, Add-Ins and Tools / Where is MDG XML for JSON located?

« on: November 11, 2022, 05:47:16 am »

We have been using Sparx EA for a few years - ver 15.1 currently.  Some users have EA installed on their local workstations.  Other users run EA that is installed on a shared Citrix machine.
I am having a problem where a project model created on a Citrix box and using the EA-JSON add-in cannot be opened on a non-Citrix user's workstation running EA.
Trying to import an MDG technology (resource), I do not have a folder/URL containing the xml for EA-JSON.  I believe our access to the registered users' section of your site has expired.
Thank you,
CCW

14

General Board / Re: Where to find NOTES for an object in the repository?

« on: September 15, 2021, 05:33:03 pm »

Of course!  How did I miss that!?

Thanks again,
  CCW

15

General Board / Re: Where to find NOTES for an object in the repository?

« on: September 15, 2021, 10:22:31 am »

I found a "Find in Project" query builder window, but I cannot see that it exposes the generated SQL underlying the search, e.g., in the SQL Scratch Pad tab. Missing something, wrong place to look, something else?