在Power BI中映射数据库架构

Question

我在youtube上看到一段视频，描述了如何使用Microsoft的AdventureWorks数据库在Power BI中轻松映射数据库架构。现在，我正尝试使用另一个数据库来复制该示例。问题是，我的许多列都有类似的内容，但不同的列名带有前缀，如pk_或fk_，这取决于它们所在的表。这导致以下查询失败：

SELECT
c.TABLE_NAME
,c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
(SELECT
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY COLUMN_NAME
HAVING COUNT(*) > 1
) dupes
ON dupes.COLUMN_NAME = c.COLUMN_NAME

有人知道是否可以模糊匹配列名或考虑不同的前缀来实现这一点吗？同样的问题也被直接问到了youtube OP。它也可以在reddit.com上找到，但这个问题仍然没有答案。

我正试图了解一些更高级的Power BI功能，同时学习一些急需的SQL，我认为这将是一个很酷的起点，所以非常感谢任何帮助！

Answer 1

如果您想显示表之间的关系，那么在两个表之间使用公共列名不是最好的主意。

例如：

CREATE TABLE tab(id INT PRIMARY KEY, name INT);
CREATE TABLE tab2(id2 INT PRIMARY KEY, name INT);
-- completely unrelated tables
SELECT
c.TABLE_NAME
,c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
(SELECT
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY COLUMN_NAME
HAVING COUNT(*) > 1
) dupes
ON dupes.COLUMN_NAME = c.COLUMN_NAME

+-------------+-------------+
| TABLE_NAME  | COLUMN_NAME |
+-------------+-------------+
| tab         | name        |
| tab2        | name        |
+-------------+-------------+

db<>小提琴演示

---

我建议使用适当的元数据视图，即sys.foreign_key_columns:

SELECT [table] = tab1.name,
[column] =  col1.name,
[referenced_table] = tab2.name,
[referenced_column] = col2.name
FROM sys.foreign_key_columns fkc
JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id
JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id
JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id
JOIN sys.columns col1 ON col1.column_id = parent_column_id 
AND col1.object_id = tab1.object_id
JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id
JOIN sys.columns col2 ON col2.column_id = referenced_column_id 
AND col2.object_id = tab2.object_id;

db<>小提琴demo2

然后你需要在PowerBI中选择合适的可视化方法。