sql - 如何从初始大表创建星型模式？

Question

我目前有一个包含 5000 万行的表。

Column  Data Type
Text1   nvarchar(60)
Text2   nvarchar(115)
Text3   nvarchar(100)
Text4   nvarchar(50)
Text5   nvarchar(17)
Year    INT
Age_Group1  float
Age_Group2  float
Age_Group3  float
Age_Group4  float
Age_Group5  float
Age_Group6  float
Age_Group7  float
Age_Group8  float
Age_Group9  float
Age_Group10 float
Age_Group11 float
Age_Group12 float
Age_Group13 float
Age_Group14 float
Age_Group15 float
Age_Group16 float
Age_Group17 float
Age_Group18 float
Age_Group19 float
Age_Group20 float
Age_Group21 float

这些数据将使用直接查询方法提取到 PowerBI 中，因此我想确保数据以最佳方式存储。考虑到此表中文本的大小和数量，我想我应该为每个文本字段创建一个维度表？

我想到的脚本是:

select Text1 , row_number()  OVER (         
         ORDER BY Text1 
           )  as Text1_ID   
        into Text1_DIM      
        from (  
        select distinct Text1   
        from dbo.my_table   
            ) x ;

我想我应该对每个文本字段执行此操作，然后使用以下内容创建一个新的汇总事实表:

select 
Text1_ID,
Text2_ID,
Text3_ID,
Text4_ID,
Text5_ID,
Year,
Age_Group1,
Age_Group2,
Age_Group3,
Age_Group4,
Age_Group5,
Age_Group6,
Age_Group7,
Age_Group8,
Age_Group9,
Age_Group10,
Age_Group11,
Age_Group12,
Age_Group13,
Age_Group14,
Age_Group15,
Age_Group16,
Age_Group17,
Age_Group18,
Age_Group19,
Age_Group20,
Age_Group21,
into My_Table_Fact
from My_Table y
join Text1 x1 on y.Text1 = x1.Text1
join Text2 x2 on y.Text2 = x1.Text2
join Text3 x3 on y.Text3 = x1.Text3
join Text4 x4 on y.Text4 = x1.Text4
join Text5 x5 on y.Text5 = x1.Text5

在 PowerBI 中，我将提取事实表和维度表。

我想知道这是否是从大表创建星型模式的正确方法以及这是否是最佳方法？

Answer 1

如果您有一个包含 5000 万行的表，由字符串作为键，那么您的方法显然是存储该表的更有效方法 - 假设前五个字段有大量重复值。

而不是存储类似 (60*2 + 2 + 115*2 + 2 + 100*2 + 2 + 50*2 + 2 + 17*2 + 2) = 694 字节的内容。整数列有 20 个字节——因此有相当大的空间节省空间。

至于创建表本身，我建议使用 identity()用于此目的的函数:

select identity(int) as text1_id, Text1 
into Text1_DIM      
from (select distinct Text1   
      from dbo.my_table t 
     ) t
order by text1;

您还可以将所有值放入一个表中:

select identity(int) as text_id, Text
into Text1_DIM      
from (select distinct v.Text 
      from dbo.my_table t cross apply
           (values (text1), (text2), (text3), (text4), (text5)) v(text)
     ) t
order by text;

唯一需要注意的是，这在引入表格时有效。但是，如果更新表并重新导入表，则维度上的值可能会发生变化。如果它们仅与一个事实表一起使用，这实际上并没有什么区别。