Relational database data explorer / visualization?

Question

Is there a tool that can let one browse relational data as a graph of connected nodes?

For example, i'm faced with trying to cleanse some anomolous data. i can start with two offending rows. In this particular example, the TransactionID should, by business rules, be unique to the table, but i find a transaction that violates that rule:

SELECT * FROM LCTTrans
WHERE TransactionID = 1075048

LCTID      TransactionID
=========  =============
4358       1075048
4359       1075048

2 row(s) affected

But really what i want to begin to hunt down all the related data, to try to see which is right. So this hypothetical software would start by showing me these two rows:

Next, i want to see that transaction that is linked into this table:

Now that transaction points to an MAL, so show me that:

Now lets add those two LCTs, that the transaction is "on". A transaction can be on only one LCT, yet this one is pointing to two:

Okay computer, both of those LCTs point to an MAL and the transaction that created them, show me those:

Those last two transactions, they also point at an MAL, and they themselves point to an LCT, show me those:

Okay, now are there any entries in LCTTrans that point to LCTs 4358 or 4359?...

And so on, and so on.

Now i did all this manually, running single selects, copying and pasting uniqueidentifier keys and converting them into friendly id numbers so i could easily see the relationships.

Is there software that can do this?

_SELECT_ is the best database explorer/visualization tool, write a stored procedure to implement your logic to display "offending rows" — KM., Feb 03 '10 at 18:33
@KM: Finding offending rows isn't the problem. It's figuring out what the values should be. This requires querying for all related rows, seeing what things link together property, and which ones are wrong. It's easy for a human to see when "*all the dots are connected*", but it's not easy to write an algorithm, since logical data inconsistiences can be in different spots, with varying degrees of "wrongness". — Ian Boyd, Feb 03 '10 at 19:17
@Lucas B: i have considered writing one. The only real stumbling block would be a node-graph-arrow drawing and layout engine. It's the equivalent of Enterprise Manager's relationship diagrams, except i would be showing data that comes from column values, rather than column names. Does anyone know of a node-arrow layout engine? That draws the fancy curved lines and arrows and organizes everything to minimize the number of crossed arrows? — Ian Boyd, Feb 03 '10 at 19:20
First, kudos on the idea. I need something like that myself. Second, if you would like to do it yourself, I would do it in Java based on Eclipse. You will find a very simple to use graphing engine called Zest which is perfect for the job. — zvikico, Feb 11 '10 at 06:03
First, awesome drawings! I needed this today and searched to see if someone has made it. That's exactly as I conceived it. I don't know if it does curved lines, but 'dot' is a graph description language that can be visualized with other tools: http://en.wikipedia.org/wiki/DOT_(graph_description_language) — Swoogan, Feb 01 '14 at 00:03

Matt Whitfield · Accepted Answer · 2013-02-27T20:52:39.527

15

Ok, well I liked this idea so much that I've written it.

It's not released yet, but when it is it will be free.

Edit

Ok, it's now released. Free relational database exploring goodness @ http://www.atlantis-interactive.co.uk/products/datasurf/default.aspx

enter image description here

Edit

Although initially free, this is now part of Pragmatic Works' DBA xPress package.

edited Feb 27 '13 at 20:52

answered Jul 06 '10 at 16:05

Matt Whitfield

6,436
3
29
44

1

i finally got around to looking at it. It's very impressive for two months of "Oh, this might be cool" work. Needs a way to define my own "foreign keys" (since not all relationships have a FK in place, e.g. FK to a view). The other feature was showing each row in the surf, but only a single representative "name" (e.g. Person.Firstname, Person.MiddleInitial, Person.Lastname). But the UI, force directed/Horizontal Heirarchy, is all very fluid and animated. And so much work is done for me that it gets up and running fast. – Ian Boyd Dec 09 '11 at 19:07
I'm glad you got the chance to give it a spin :) I did wonder about showing the rows in the surf graph - but it's Winforms based and I didn't want to kill the performance of it - and I also wondered how it would actually work if you ended up with 100s of rows... But I get the 'can I define my own keys' one quite a bit. Starting a new job soon which hopefully should give me some time to look at it more... – Matt Whitfield Dec 18 '11 at 21:09

score 3 · Answer 2 · answered Dec 09 '11 at 17:43

DBeauty is a powerful data browser (similar to Matt Whitfield's excellent DataSurf but more powerful). It is Java based, so you need to download the JDBC driver. I've found this tool invaluable in quickly navigating data (I fell in love with Microsoft's Quadrant before they killed it off and have been looking for a replacement ever since).

score 1 · Answer 3 · answered Feb 20 '13 at 14:19

1

Old but good and free DB subsetting tool Jailer should be able to answer the question. http://jailer.sourceforge.net/

answered Feb 20 '13 at 14:19

Petr H.

11
1

score 1 · Answer 4 · answered Jun 18 '13 at 08:26

1

Yes, i would advice you to look into DbSchema, it's a neet database management tool that will help you.

answered Jun 18 '13 at 08:26

user2143407

55
7

score 0 · Answer 5 · answered Jun 26 '13 at 21:20

0

Try This tool - it is in russian, but interface is comprehensive http://sourceforge.net/projects/basescan/. Navigation in base is through drag and drop.

answered Jun 26 '13 at 21:20

user2525885

1

score 0 · Answer 6 · answered Feb 03 '10 at 18:32

I can think of a few for relational data (RDF, Topic Map and conceptual graph browsers), but none off-hand for SQL. You could try and translate your queries to a relational language the browsers understand. You also might be able to build something on top of skyrails. Most of the visualisations I've tagged on delicious are for graph or relational data, but again tend to be schema free rather than SQL.

score 0 · Answer 7 · answered Feb 10 '10 at 19:03

Basically you write a dedup tool where you show both records onthe screen side by side with the ability to pick the record you wan to keep but to check individual data from the other record to keep as well. Since deduping is very differnt from database to database and highly dependant on the specific table structure and business rules you have (as well as knowledge about which things must be looked at for the type of deduping you are doing as they typically only show the most important relationship tables on screen), I have never seen one that wasn't built in house.

But if you want a quick look at all the data write a query that left joins to all the child tables and shows all the fields for both transactionids. Then read through your results.

More importantly, how did you end up with a dup if you hav ea business rule that requires the transactionid to be uninique. Did you forget that all of these types of rules must be enfoced through the datbase and not the application? Why was there no unique index on that field?

i could write a tool that can be used to explore these two duplicates - but that doesn't help me in other situations. Situations, say, where i'm investigating a terrorist, finding everything he did. Or perhaps exploring a possible relationship between two customers. — Ian Boyd, Feb 12 '10 at 02:20

score 0 · Answer 8 · answered Jun 05 '10 at 19:48

I've looked for open source software that can do this sort of link analysis, without much success. If you have enough of a budget to go proprietary, you might consider talking to Palantir Technologies, Centrifuge Systems, i2, etc. about analytics platforms and visualization technologies.

Relational database data explorer / visualization?

8 Answers8