Entity Relationship Diagram (The Quest Continues)

The quest temporarily delayed. I'm working on other, hotter projects...

In answer to the question of what it will include: Don't know yet. I
have it drilling up and down, and, if I'm not careful, it tries to like
everything to everything else or gets caught in loops "WrkCenter to
JCDept to WrkCenter". I am working on a list of tables to terminate on,
such as customers, etc. I am also looking at mini-trees, so I can
quickly graft on a set of tables that should always be linked together.
The solution seems to be using some pretty complex rules to make sure
the original trace does not loop indefinitely then follow with multiple
"cleaning" passes to get rid of redundant or meaningless links.

Another possibility is to build parts of the linkage manually then turn
an expansion routine loose on it.

Unfortunately, even with all the data I have at my disposal, it's pretty
hard to decide which links to follow and which to ignore without a lot
of manual intervention.

"Dammit Jim, I'm a mathematician, not a DB Admin!"

Greg Krumrey
Information Manager
Patriot Machine, Inc.
Voice: 636-940-1776 x 130
fax: 636-940-8933
email me: mailto:gkrumrey@...
visit us at: http://www.patriot-machine.com



[Non-text portions of this message have been removed]
In my quest for the Entity Relationship Diagram, I am attempting to
automate the process as much as possible.
Here's what I've down so far:
+ Read in table index data from the Vantage report
+ Located all primary unique indices, and then searched all other tables
for the same set of fields. If found, this is saved as a Potential
Upward Link.
+ Redundant duplicate links removed (If Table A links to B using an
index of 2 fields, and B links to A using only 1, discard link from B to
A).
Here's my next steps:
+ Redundant duplicate branch removal (Pruning): If table B uplinks to A
and A downlinks to C and D, check for C & D downlinked from B, and, if
the number of fields from C to B is greater then those from C to A,
delete link between C to A.
+ Once the branches are trimmed, look at resulting data.

Does anyone see a better way to do this? I've checked out parent tables,
and other relationships listed within the reports generated by Vantage
and they either leave off links I know are there or seem to link
everything to everything.

"Dammit Jim, I'm a mathematician, not a DB Admin!"

Greg Krumrey
Information Manager
Patriot Machine, Inc.
Voice: 636-940-1776 x 130
fax: 636-940-8933
email me: mailto:gkrumrey@...
visit us at: http://www.patriot-machine.com



[Non-text portions of this message have been removed]
Forgive me for being ignorant but what are the advantages of doing this over
the data dictionary viewer?

Patrick

-----Original Message-----
From: Greg Krumrey [mailto:gkrumrey@...]
Sent: Friday, June 29, 2001 10:58 AM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Entity Relationship Diagram (The Quest Continues)


In my quest for the Entity Relationship Diagram, I am attempting to
automate the process as much as possible.
Here's what I've down so far:
+ Read in table index data from the Vantage report
+ Located all primary unique indices, and then searched all other tables
for the same set of fields. If found, this is saved as a Potential
Upward Link.
+ Redundant duplicate links removed (If Table A links to B using an
index of 2 fields, and B links to A using only 1, discard link from B to
A).
Here's my next steps:
+ Redundant duplicate branch removal (Pruning): If table B uplinks to A
and A downlinks to C and D, check for C & D downlinked from B, and, if
the number of fields from C to B is greater then those from C to A,
delete link between C to A.
+ Once the branches are trimmed, look at resulting data.

Does anyone see a better way to do this? I've checked out parent tables,
and other relationships listed within the reports generated by Vantage
and they either leave off links I know are there or seem to link
everything to everything.

"Dammit Jim, I'm a mathematician, not a DB Admin!"

Greg Krumrey
Information Manager
Patriot Machine, Inc.
Voice: 636-940-1776 x 130
fax: 636-940-8933
email me: mailto:gkrumrey@...
visit us at: http://www.patriot-machine.com



[Non-text portions of this message have been removed]


To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please go to:
http://groups.yahoo.com/group/vantage/files/. Note: You must have already
linked your email address to a yahoo id to enable access.

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
What does a ERD gain me? The biggest thing is to know how to get from
Table A to Table D, especially if I don't know which tables are in
between. It also can graphically illustrate one-to-one and one-to-many
relationships (although this will a little harder to trace) and looks
really cool on the cube wall. Also the data dictionary is not consistent
or complete enough to show the full structure. With this visual aid, I
can create reports in record time, without it, I can still create the
reports, but it takes longer and I'm not sure if my links are valid.

The last benefit is for the bosses and co-workers: To show them just how
complicated Vantage really is and to show to employees how the data they
enter is used and how a failure to properly clock labor hours can result
in incorrect quoting, scheduling and even shipping.

Greg Krumrey
Information Manager
Patriot Machine, Inc.
Voice: 636-940-1776 x 130
fax: 636-940-8933
email me: mailto:gkrumrey@...
visit us at: http://www.patriot-machine.com


Reference:
Forgive me for being ignorant but what are the advantages of doing this
over the data dictionary viewer?

Patrick


[Non-text portions of this message have been removed]
Greg,

I've been using the Data Dictionary religiously for years (even before they
came out with a browser version and you had to print a new one each time you
upgraded) and I haven't noticed a lack of completeness or consistency. Can
you elaborate? If there are problems with it, I'd sure like to know what
they are.

Thanks,

Jon Hellebuyck
Stremel Manufacturing
-----Original Message-----
From:
sentto-20369-22119-994166009-jhellebuyck=stremel.com@...
[mailto:sentto-20369-22119-994166009-jhellebuyck=stremel.com@...
.com]On Behalf Of Greg Krumrey
Sent: Tuesday, July 03, 2001 8:13 AM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Entity Relationship Diagram (The Quest Continues)


What does a ERD gain me? The biggest thing is to know how to get from
Table A to Table D, especially if I don't know which tables are in
between. It also can graphically illustrate one-to-one and one-to-many
relationships (although this will a little harder to trace) and looks
really cool on the cube wall. Also the data dictionary is not consistent
or complete enough to show the full structure. With this visual aid, I
can create reports in record time, without it, I can still create the
reports, but it takes longer and I'm not sure if my links are valid.

The last benefit is for the bosses and co-workers: To show them just how
complicated Vantage really is and to show to employees how the data they
enter is used and how a failure to properly clock labor hours can result
in incorrect quoting, scheduling and even shipping.

Greg Krumrey
Information Manager
Patriot Machine, Inc.
Voice: 636-940-1776 x 130
fax: 636-940-8933
email me: mailto:gkrumrey@...
visit us at: http://www.patriot-machine.com


Reference:
Forgive me for being ignorant but what are the advantages of doing this
over the data dictionary viewer?

Patrick


[Non-text portions of this message have been removed]


To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please go to:
http://groups.yahoo.com/group/vantage/files/. Note: You must have already
linked your email address to a yahoo id to enable access.

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
There are obvious joins which are not in the data dictionary such as:

Customer Table to FOB Table
Company to Company
DefaultFOB to FOB

Will the ERD give us a list of all of these some how?

Patrick Winter
sSc Specialty Screw Corporation
Vantage 5.00.317, Progress 9.1a


-----Original Message-----
From: Jon Hellebuyck [mailto:jhellebuyck@...]
Sent: Tuesday, July 03, 2001 8:28 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Entity Relationship Diagram (The Quest Continues)


Greg,

I've been using the Data Dictionary religiously for years (even before they
came out with a browser version and you had to print a new one each time you
upgraded) and I haven't noticed a lack of completeness or consistency. Can
you elaborate? If there are problems with it, I'd sure like to know what
they are.

Thanks,

Jon Hellebuyck
Stremel Manufacturing
-----Original Message-----
From:
sentto-20369-22119-994166009-jhellebuyck=stremel.com@...
[mailto:sentto-20369-22119-994166009-jhellebuyck=stremel.com@...
.com]On Behalf Of Greg Krumrey
Sent: Tuesday, July 03, 2001 8:13 AM
To: 'vantage@yahoogroups.com'
Subject: [Vantage] Entity Relationship Diagram (The Quest Continues)


What does a ERD gain me? The biggest thing is to know how to get from
Table A to Table D, especially if I don't know which tables are in
between. It also can graphically illustrate one-to-one and one-to-many
relationships (although this will a little harder to trace) and looks
really cool on the cube wall. Also the data dictionary is not consistent
or complete enough to show the full structure. With this visual aid, I
can create reports in record time, without it, I can still create the
reports, but it takes longer and I'm not sure if my links are valid.

The last benefit is for the bosses and co-workers: To show them just how
complicated Vantage really is and to show to employees how the data they
enter is used and how a failure to properly clock labor hours can result
in incorrect quoting, scheduling and even shipping.

Greg Krumrey
Information Manager
Patriot Machine, Inc.
Voice: 636-940-1776 x 130
fax: 636-940-8933
email me: mailto:gkrumrey@...
visit us at: http://www.patriot-machine.com


Reference:
Forgive me for being ignorant but what are the advantages of doing this
over the data dictionary viewer?

Patrick


[Non-text portions of this message have been removed]


To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please go to:
http://groups.yahoo.com/group/vantage/files/. Note: You must have already
linked your email address to a yahoo id to enable access.

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]


To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please go to:
http://groups.yahoo.com/group/vantage/files/. Note: You must have already
linked your email address to a yahoo id to enable access.

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Another problem I have found with the data dictionary is when a field is
spelled differently in two tables. A good example of this is joining the
EmpBasic table to the LaborHed table. In EmpBasic, the Employee ID field is
called "EmpID", in LaborHed it is called "EmployeeNum". Since these two
fields do not have the same name, they are not recognized as joins in the
data dictionary, when in fact, these two tables can easily and correctly be
joined by these fields. There are other instances of this as well which
makes the data dictionary incomplete or inaccurate.



-----Original Message-----
There are obvious joins which are not in the data dictionary such as:

Customer Table to FOB Table
Company to Company
DefaultFOB to FOB