什么工具可以从 SQL Server 分层数据生成图表？

Question

是否有一个工具可以与 SQL Server 一起使用，从分层数据模型生成树状图？

我正在处理一个大型的地理层次结构，并且希望将其可视化。

这是一个例子。

我有一个 NodeHierarchy 表，用于存储节点之间的层次关系。表中的每一行代表一个节点。除一个节点外，每个节点都有一个父节点。没有父节点的节点是层次结构的根。

以下是我创建表格的方法：

CREATE DATABASE HierarchyTest;
GO

USE HierarchyTest;
GO

CREATE TABLE NodeHierarchy (
  PK_NodeID INT NOT NULL
    CONSTRAINT PK_NodeHierarchy PRIMARY KEY,
  FK_ParentNodeID INT NULL
    CONSTRAINT FK_NodeHierarchy_NodeHierarchy FOREIGN KEY
      REFERENCES NodeHierarchy(PK_NodeID),
  Name NVARCHAR(255) NOT NULL
);

我有一个苏格兰城市和场馆的层次结构示例。苏格兰是等级制度的根源。苏格兰的后裔是城市和场地。在这个层次结构中，父级“包含”一个子级，因此我们说“巴罗兰在格拉斯哥，格拉斯哥在苏格兰”。

此语句使用示例数据填充 NodeHierachy 表：

INSERT INTO NodeHierarchy(PK_NodeID, FK_ParentNodeID, Name)
VALUES
  (1, NULL, N'Scotland'),
  (2, 1, N'Glasgow'),
  (3, 1, N'Edinburgh'),
  (4, 1, N'St Andrews'),
  (5, 2, N'The Barrowlands'),
  (6, 2, N'The Cathouse'),
  (7, 2, N'Carling Academy'),
  (8, 2, N'SECC'),
  (9, 2, N'King Tut''s Wah-Wah Hut'),
  (10, 3, N'Henry''s Cellar Bar'),
  (11, 3, N'The Bongo Club'),
  (12, 3, N'Sneaky Pete''s'),
  (13, 3, N'The Picture House'),
  (14, 3, N'Potterrow'),
  (15, 4, N'Aikman''s'),
  (16, 4, N'The Union'),
  (17, 4, N'Castle Sands');

SELECT * FROM NodeHierarchy; 的输出：

PK_NodeID   FK_ParentNodeID Name
----------- --------------- ---------------------------------
1           NULL            Scotland
2           1               Glasgow
3           1               Edinburgh
4           1               St Andrews
5           2               The Barrowlands
6           2               The Cathouse
7           2               Carling Academy
8           2               SECC
9           2               King Tut's Wah-Wah Hut
10          3               Henry's Cellar Bar
11          3               The Bongo Club
12          3               Sneaky Pete's
13          3               The Picture House
14          3               Potterrow
15          4               Aikman's
16          4               The Union
17          4               Castle Sands

(17 row(s) affected)

In Freemind 我画了这个等效图：

什么工具可以以最少的手动操作为我完成此任务？

---

编辑：最初我说我想可视化层次结构的“全部或部分”。此处发布的解决方案无条件地可视化整个层次结构。这对于小型示例层次结构来说很好，但对于较大的示例层次结构，仅可视化其中的一部分可能更有用。

因为我没有具体说明“部分”的含义，所以我已将其从问题中删除。我在 另一个问题。

Answer 1

我研究了 Cade Roux 答案中的线索，并使用 GraphViz 开发了一个解决方案。

要了解 GraphViz，首先我阅读了这篇介绍性文章 和 命令行调用文档。从本文中的示例代码列表成功生成图表后，我对使用自己的数据充满信心。

正如 Cade 所建议的，学习 GraphViz 的 DOT 语言的最佳方法是自己编写它。我研究了本文的示例（清单 1、2 和 6），然后提出了这个 venues.gv 来描述我自己的数据：

digraph Venues
{ 
  N1[label = "Scotland"];
  N2[label = "Glasgow"];
  N3[label = "Edinburgh"];
  N4[label = "St Andrews"];
  N5[label = "The Barrowlands"];
  N6[label = "The Cathouse"];
  N7[label = "Carling Academy"];
  N8[label = "SECC"];
  N9[label = "King Tut's Wah-Wah Hut"];
  N10[label = "Henry's Cellar Bar"];
  N11[label = "The Bongo Club"];
  N12[label = "Sneaky Pete's"];
  N13[label = "The Picture House"];
  N14[label = "Potterrow"];
  N15[label = "Aikman's"];
  N16[label = "The Union"];
  N17[label = "Castle Sands"];

  N1 -> N2;
  N1 -> N3;
  N1 -> N4;
  N2 -> N5;
  N2 -> N6;
  N2 -> N7;
  N2 -> N8;
  N2 -> N9;
  N3 -> N10;
  N3 -> N11;
  N3 -> N12;
  N3 -> N13;
  N3 -> N14;
  N4 -> N15;
  N4 -> N16;
  N4 -> N17;
}

我将其提供给 circo，只是GraphViz 中众多图形绘制命令之一，并获得了令人满意的输出：

circo -Tpngvenues.gv -ovenues.png 的输出：

GraphViz 文件由两个块构成。一个块声明每个节点的标签，另一个块声明图的边。

为了提供每个块的数据，我创建了一个 NodeHierarchy 视图。

该视图提供了用于声明节点标签的数据：

CREATE VIEW NodeLabels (
  Node,
  Label
)
AS
SELECT
   PK_NodeID AS Node,
   Name AS Label
FROM
  NodeHierarchy;

该视图提供了用于声明节点之间的边的数据：

CREATE VIEW Edges (
  Parent,
  Child
)
AS
SELECT
  FK_ParentNodeID AS Parent,
  PK_NodeID AS Child
FROM NodeHierarchy
WHERE FK_ParentNodeID IS NOT NULL;

这个名为 generate-graph.ps1 的 Powershell 脚本从视图中选择数据，将其转换为 GraphViz输入，并将其传送到 circo 来生成完整层次结构的可视化，如上所示：

"digraph Venues {" + (
  Invoke-Sqlcmd -Query "SELECT * FROM HierarchyTest.dbo.NodeLabels" | 
  ForEach-Object {"N" + $_.Node + "[label = """ + $_.Label + """];"}
) + (
  Invoke-Sqlcmd -Query "SELECT * FROM HierarchyTest.dbo.Edges" |
  ForEach-Object {"N" + $_.Parent + " -> N" + $_.Child + ";"}
) +
"}" | circo -Tpng -o venues.png

脚本必须在 sqlps 中运行，而不是在 powershell 中运行> 以便Invoke-Sqlcmd cmdlet 可用。 sqlps的默认工作目录是SQLSERVER，所以我通过sqlps运行脚本时必须指定驱动器。

这是我用来生成类似上面的图表的命令：

sqlps C:.\generate-graph.ps1

这会在 C 工作目录中输出一个名为 venues.png 的文件。

这个 Powershell 解决方案感觉有点不优雅，但这确实满足了我的需要。更有经验的 Powershell 程序员也许能够想出更干净的东西。

Answer 2

通过 GraphViz 导出并运行它 - 您甚至不必生成层次结构（只需导出节点和边) - 只需根据您的 NodeID 列分配唯一的节点名称，并在边缘中使用相同的节点名称。

如果你想要一些交互式的东西，微软有一个自动图形布局库，它可以从 .NET 使用。

这是GraphViz 简介。

您要做的是通过使用如下脚本导出 SQL 来输出 DOT 文件： https://data.stackexchange.com/stackoverflow/q/109885/ 它将通过 GraphViz 运行并生成您的图片。

DOT 语法相对简单 - 您可以先手动编写它，然后从 SQL 生成它，然后将其简单地粘贴到文件或其他东西（如 .NET 或 PowerShell）中，后者读取 SQL 集并生成文件。

您可以使用 SSIS 实现自动化。我制作了一个包，写出 DOT 文件并在其上运行 graphviz，并每天保存我们系统的 graphiacl 快照。