BI中事实表和维度表的定义+具体SQL操作(转载+自己添加实验)

 

1个典型的例子是，把逻辑业务比作1个立方体，产品维、时间维、地点维分别作为不同的坐标轴，而坐标轴的交点就是1个具体的事实。也就是说事实表是多个维度表的1个交点。而维度表是分析事实的1个窗口。

首先介绍下数据库结构中的星型结构，该结构在位于结构中心的单个事实数据表中保护数据，其它维度数据存储在维度表中。每一个维度表与事实数据表直接相干，且通常通过1个键联接到事实数据表中。星型架构是数据仓库比较流向的1种架构。

事实表是数据仓库结构中的中央表，它包括联系事实与维度表的数字度量值和键。事实数据表包括描写业务（例如产品销售）内特定事件的数据。

维度表是维度属性的集合。是分析问题的1个窗口。是人们视察数据的特定角度，是斟酌问题时的1类属性，属性的集合构成1个维。

事实表中有外键。

①先建立维度表

②再建立事实表，建立事实表的时候从维度表中添加外键到事实表中

③如果后期维度表有增加，那么可以继续在事实表中添加外键。

###########################################下面是具体实验#######################################################################

 

create database test;

建表顺序	SQL语言	备注
客户维	create table client (     clientId int null,     name varchar(64) null,     age int null,     sex varchar(12) null,     occupation varchar(64) null,     column_6 int null );	clientId必须是primary key或者unique
时间维	create table time (    timeid int null,    year int null,    month int null,    week int null,    day int null );	timeid必须是primary key或者unique
地区维	create table region (     regionId int null,     region varchar(32) null,     city varchar(32) null );	regionId必须是primary key或者unique
事实表	create table fact (     payment varchar(64) null,     amount int null,     fact_clientid int null,     fact_timeid int null,     fact_regionid int null,     constraint fact_client_clientId_fk         foreign key (fact_clientid) references client (clientId)             on update cascade on delete cascade,     constraint fact_region_regionId_fk         foreign key (fact_regionid) references region (regionId)             on update cascade on delete cascade,     constraint fact_time_timeid_fk         foreign key (fact_timeid) references time (timeid)             on update cascade on delete cascade );	看起来有点复杂，用datagrip操作即可 注意设置外键前必须先建立这个column才可以，否则会无法顺利建表

 

插入数据的时候，先插入维度表，再插入事实表[2]

注意外键只是同步约束，而不是同步数据[3]

############################################################################################################################################v

关于外键中的集中设置[4]

CASCADE	父表delete、update的时候，子表会delete、update掉关联记录；
SET NULL	父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，所以注意在设计子表时外键不能设为not null；
RESTRICT	如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录；
NO ACTION	同 RESTRICT，也是首先先检查外键；

 

 

另外,datagrip中的外键操作详解:

 这里的references的意思是说:

事实表fact中的fact_timeid的内容必须被包含在维度表time的列timeid中，

否则事实表想要新插入数据就会因为外键约束导致插入失败。

################################################################################################################################################

下面是外键效果，当在事实表的外键列fact_clientid中增加维度表中不存在的数据6565时，就会报约束错误

 

 

记忆和理解:

维度表中的数据可以理解成是x轴y轴上的刻度值

事实表中的数据可以理解成是直角坐标系中的一个点.

 

Reference:

[1]Mysql外键设置中的CASCADE、NO ACTION、RESTRICT、SET NULL

[2]外键约束的表怎么插入数据

[3]为什么外键不更新？(why foreign key does'nt update?)

[4]MySQL - 外键约束的修改更新状态（CASCADE，RESTRICT，NO ACTION，SET NULL ）