August 18, 2009 at 4:16 pm
Hi there everyone,
My employer has purchased an ERP with an off the shelf database of over four thousand tables. Even the vendor doesnt have a diagram of it. (So Scary)
I tried the diagram tool in management studio against my development database and the process slowly grinded to a halt after processing about 500 tables.
Visio can't even get close to this thing without barfing.
Please! Is there "ANYTHING" out there that we could purchase with the power to diagram so many tables and their relationships? Even if someone could contact me offline would be greatly appreciated.
If the database cannot be diagramed is there a query that I can use to at least Identify which tables have data and which tables do not?
:crazy:
Sincerely,
Chris
(Public Schools DBA in Maryland)
SQL version: SQL2005 SP2
32-bit
8GB Server mem
AWE: ON
August 18, 2009 at 5:13 pm
It's pricy, but ER Studio handles our (legacy) monster DB fine.
August 18, 2009 at 5:24 pm
Try this one. I had to use this open source on of my projects.
August 19, 2009 at 5:20 am
Checking on both now.
I'll keep you posted.
And THANKS for you suggestions!!!
Keep em coming!!
Chris
August 20, 2009 at 7:54 am
I'm reviewing ERD programs now, and I like ModelRight 3.5. I have not tried that many tables, but it is so granular you can filter and bring in sets of tables at a time (if need to). http://www.modelright.com
I haven't reviewed SQL Power's Power Architect yet, but it is free! http://www.sqlpower.ca
Others are
DeZign from http://www.datanamic.com
Database Visual Architect from http://www.visual-paradigm.com
Aqua Data Studio from http://www.aquafold.com
Anybody have any known problems with any of these? I would like to know before purchasing one, thanks!
[font="Courier New"]____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog[/font]
August 20, 2009 at 8:29 am
If that is one of the big name ERP systems, there is a good chance there are no FK relationships defined between the tables. Since they have to support so many DB platforms, they often prefer to enforce relationships logically in the application.
You can use the script on the link below to see if there are any FK relationships defined.
Find Table Reference Levels
August 20, 2009 at 10:52 am
I have had good luck with Sybase's PowerDesigner. Below is a link to the evaluation version.
http://response.sybase.com/forms/PowerDesigner15Arch
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 20, 2009 at 11:18 am
I have used ERStudio for the better part of a decade. IIRC you can get (or used to could anyway) sql server specific licensing that is MUCH cheaper than the full blown kitchen-sink costs.
Gotta ask though - is it really going to do you any good to have an ERD with 4k tables on it? Especially for an ERP system that you are almost certainly forbidden to alter in any way?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2009 at 11:35 am
Michael Valentine Jones (8/20/2009)
If that is one of the big name ERP systems, there is a good chance there are no FK relationships defined between the tables. Since they have to support so many DB platforms, they often prefer to enforce relationships logically in the application.You can use the script on the link below to see if there are any FK relationships defined.
Find Table Reference Levels
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957
I am agree with Michael Valentine Jones . If your ERPdoes not contains FK, no tool will be able to do reverse engenering
August 24, 2009 at 6:04 am
Pam Brisjar (8/18/2009)
It's pricy, but ER Studio handles our (legacy) monster DB fine.
Pam,
Thanks,ER Studio is looking somewhat promising.
I was able to create a data model but it's so darn big that takes several minuets to drill into it.
If you have any tips on creating sub-models I love to hear them.
But in general, thanks for your help in pointing me in this direction in the first place! 🙂
August 24, 2009 at 5:33 pm
Chris,
I know you can do it but am not 100% positive as to how. I know it involves creating sub-collections of the objects but that's about it. I've been way off doing other things so haven't played around with it for several months.
August 24, 2009 at 6:05 pm
calico (8/21/2009)
If your ERPdoes not contains FK, no tool will be able to do reverse engenering
Actually, this not true. Even without FK's there are techniques to discern the relationships between tables and most of them can be automated to one degree or another.
I know that I have documented/reverse-engineered several large schemas without the benefit of FK's and although it s very laborious and difficult it certainly can be done. I only know for sure of one instance of one these techniques being incoporated into an automated tool (ErWin, almost 10 years ago), but there is no reason that they could not all be 80-90% automated and I have often thought of doing this myself.
Now doing it without any PK's (which I have seen a couple of times), that's a nightmare.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2009 at 7:20 am
RBarryYoung (8/24/2009)
calico (8/21/2009)
If your ERPdoes not contains FK, no tool will be able to do reverse engeneringActually, this not true. Even without FK's there are techniques to discern the relationships between tables and most of them can be automated to one degree or another.
I know that I have documented/reverse-engineered several large schemas without the benefit of FK's and although it s very laborious and difficult it certainly can be done. I only know for sure of one instance of one these techniques being incoporated into an automated tool (ErWin, almost 10 years ago), but there is no reason that they could not all be 80-90% automated and I have often thought of doing this myself.
Now doing it without any PK's (which I have seen a couple of times), that's a nightmare.
This is correct. ERStudio has options for 'inferring' FK relationships by indexes and names. It can also attempt to infer PKs. Oh, and it also has it's own macro language so you can automate/program virtually every aspect of the application as well. This can be an immense timesaver, especially if you need to do sweeping changes to a massive ERD.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 25, 2009 at 9:30 am
TheSQLGuru (8/25/2009)
RBarryYoung (8/24/2009)
calico (8/21/2009)
If your ERPdoes not contains FK, no tool will be able to do reverse engeneringActually, this not true. Even without FK's there are techniques to discern the relationships between tables and most of them can be automated to one degree or another.
I know that I have documented/reverse-engineered several large schemas without the benefit of FK's and although it s very laborious and difficult it certainly can be done. I only know for sure of one instance of one these techniques being incoporated into an automated tool (ErWin, almost 10 years ago), but there is no reason that they could not all be 80-90% automated and I have often thought of doing this myself.
Now doing it without any PK's (which I have seen a couple of times), that's a nightmare.
This is correct. ERStudio has options for 'inferring' FK relationships by indexes and names. It can also attempt to infer PKs. Oh, and it also has it's own macro language so you can automate/program virtually every aspect of the application as well. This can be an immense timesaver, especially if you need to do sweeping changes to a massive ERD.
Yup, some really great tools. But *very* expensive as I recall ($10k+ license even 10 years ago). That's why I hardly ever get to use them. 🙁
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2009 at 9:52 am
Yea ERWin is too expensive. The programs I listed in my previous post are around $500.
[font="Courier New"]____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog[/font]
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply