sql - 在 Power BI 中映射数据库架构

Question

我在 youtube 上看到一个视频，描述 How to Easily Map Your Database Schema in Power BI使用AdventureWorks来自微软的数据库。现在我尝试使用另一个数据库复制该示例。问题是我的许多列都有类似的内容，但列名不同，带有前缀，例如 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

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

例如:

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<>fiddle demo

<小时/>

我建议使用正确的元数据 View ，即 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<>fiddle demo2

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