数量建立模型,关系和互相
分类:pc28.am神测网

在分析数据时,不可能总是对单个数据表进行分析,有时需要把多个数据表导入到PowerBI中,通过多个表中的数据及其关系来执行一些复杂的数据分析任务,因此,为准确计算分析的结果,需要在数据建模中,创建数据表之间的关系。在PowerBI中,关系(Relationship)是指数据表之间的基数(Cardinality)和交叉筛选方向(Cross Filter Direction)。

PowerBI 使用 内存的列式数据库 VertiPaq,用于对已发布的数据集进行数据压缩和快速处理,能够使PowerBI报表执行脱机访问,面向列的处理,高度优化对1:N关系的处理性能。关系是数据分析的基础,正因为数据之间存在关系,分析数据才有了意义。PowerBI支持的关系(Reliationship)有1:N(称作一对多的关系)和1:1两种,PowerBI不支持多对多的关系,在设计PowerBI时,通常把1:1的关系合并成一张表,因为任何一个关系都会降低查询性能。通常意义上,所谓的PowerBI的关系通常是指一对多的关系,关系(1:N)的构成:两端是查找表(Lookup,维度表Dimension Table)和事实表(Fact,数据表Data Table),其中查找表处于关系的“1”端,而事实表处于关系的“N”端,维度表中建立关系的列的值是唯一的,事实表中建立关系的列的值可以有重复值。

 

在PowerBI Desktop的关系(Relationship)视图中,通过实线/虚线表示物理关系(Physical Relationship),实线的两端是1和*号,表示关系的两端,这种实线表示的关系处于活跃状态,虚线是不活跃的关系。虚拟关系(Virtual Relationship)是通过DAX表达式(例如,通过FILTER函数)创建的关系,一般是用在度量值中,用于交互查询。PowerBI的关系,实际上是按照特定的属性对另一端进行切片,通常是按照1端的属性,对N端进行切片和聚合分析。

我的PowerBI开发系列的文章目录:PowerBI开发

PowerBI的关系和关系型数据库的外键相似,外键是通过两个表(事实表和维度表)之间的数据列创建的,例如,事实表(Fact)的数据列c_f引用维度表(Dimension)的数据列c_d,前提是:c_d列的值是唯一的,c_f的值必须是c_d列中的值,而c_f列的值允许重复。在PowerBI中,允许c_f列引用不存在于c_d列中的值,这种情况下,PowerBI自动向维度表的c_d列中添加空值(BLANK(),该空值可以通过Slicer查看),用于引用不满足外键关系的列值,称作回写空值。

 

一,单向交叉方向

基数(Cardinality)

当关系的“Cross Filter Direction”属性设置为单向的箭头,即把Cross Filter Direction设置为Single时,箭头由查找表指向事实表,一旦关系创建成功,查找表用于对事实表进行过滤,按照查找表对事实表进行切片(聚合查询)。

基数关系类似于关系表的外键引用,都是通过两个数据表之间的单个数据列进行关联,该数据列叫做查找列,两个数据表之间的基数关系是1:1,或者1:N,或者N:1,基数关系表示的含义是:

这种传统的数据模型和数据仓库的星型模型相同,特点是:维度表包含属性,事实表包含度量(measure),按照维度表的属性对事实表的度量进行切片/聚合查询。

  • 多对一 (N:1):这是最常见的默认类型。这意味着一个表中的列可具有一个值的多个实例,而另一个相关表(常称为查找表)仅具有一个值的一个实例。
  • 一对一 (1:1):这意味着一个表中的列仅具有特定值的一个实例,而另一个相关表也是如此。

 图片 1

例如,TableA和TableB之间的基数关系是1:N,那么TableA是TableB的查找表,TableB叫做引用表,在查找表中,查找列的值是唯一的,不允许存在重复值,而在引用表中,查找列的值不唯一。

二,双向交叉过滤

在PowerBI中,有时,引用表会引用查找表中不存在的数据,默认情况下,PowerBI会自动在查找表中增加一个查找值Blank,所有不存在于查找表中的值,都映射到Blank。

当关系的“Cross Filter Direction”属性设置为双向的箭头,即把Cross Filter Direction设置为Both时,为了实现数据的过滤,逻辑上可以认为,PowerBI把这两个表展开成一个大表。

交叉筛选方向(Cross Filter Direction)

图片 2

筛选方向是筛选的流向,表示一个筛选条件对其他相关表进行过滤,例如,TableA对TableB过滤,其筛选方向可以是双向,或单向:

双向交叉过滤方向会导致有些关系处于不活跃状态(inactive),当一个维度(lookup)表和多个事实表有关系时,避免使用Both方向,这样可能会导致部分关系失效,处于不活跃状态。单向过滤(single-driectional filtering)PowerBI的默认设置,而双向过滤(bi-directional filtering)是一个不好的设置,因为通过事实表对查找表进行过滤由一定的性能消耗。

  • 双向:默认方向,这意味着为进行筛选,两个表均被视为是同一个表,这非常适用于其周围具有多个查找表的单个表。
  • 单向:这意味着一个表只能对另外一个表进行筛选,而不能反向过滤。

三,关系的传递

一,双向筛选关系

在星型结构中,中间是一个引用表,周围是多个查找表,引用表和查找表之间的筛选关系是双向的,如下所示:

图片 3

通常情况下,双向筛选用于星型结构,是默认的方向,但是,双向筛选不太适合以下关系图中的模式,在该模式中,筛选方向形成一个循环,对于此类关系模式,双向筛选会创建一组语义不明的关系,例如,求取 TableX 中某个字段的总和,如果选择按照 TableY 中的某个字段进行筛选,则不清楚筛选器应该如何流动,是通过顶部表,还是底部表进行流动? 

图片 4

如果双向筛选导致数据关系的多义性,那么,可以导入表格两次(第二次使用其他名称)以消除循环。 这会产生类似于星型架构的关系模式,借助星型架构,所有关系均可设置为“双向”。

在PowerBI中,关系是可以传递的,这就意味着,过滤条件是可以传递的。把Filter看作是流水,箭头的指向是由上游指向下游(查找表处于上游,而数据表处于下游),Filter由查找表流向数据表。一般情况下,按照查找表对数据表进行过滤,Filter由查找表流向数据表,再流向其他关联的数据表;如果把交叉过滤的方向设置双向过滤,那么PowerBI可以按照数据表对查找表进行过滤,也就是说,过滤(Filter)由数据表逆流到查找表。双向交叉过滤使得查找表被过滤和切片,并能对查找表执行聚合查询。

二,创建间接关系

在PowerBI报表的关系中,直接关系是指关系的两个表直接接触,间接关系是指通过中间表建立关系的两个数据表,间接关系关联的两个数据表不直接接触,如下图,数据表Students和StudentCourse之间的关系是直接关系,数据表Course和StudentCourse之间的关系是直接关系,而数据表Students和Courses之间通过StudentCourse建立间接关系。间接关系通过一系列有直接关系的数据表,能够实现数据的交互,这是PowerBI自动实现的,为创建复杂的数据模型提供了支持,但是,在数据建模中使用间接关系时,务必谨慎,PowerBI对Filter选项的全选和不选的处理是有区别的。

图片 5

1,使用以下脚本创建具有多层关系的数据表

脚本创建了四个表,分别是用于表示学生,课程,学生选课,学生演讲,学生和课程之间的关系是1:N,学生和演讲活动之间的关系是1:N

图片 6图片 7

create table dbo.Courses
(
CourseID int not null primary key clustered,
CourseName varchar(32) not null
)

create table dbo.Students
(
StudentID int not null primary key clustered,
StudentName varchar(64) not null
)

create table dbo.StudentCourse
(
    StudentID int not null,
    CourseID int not null,
    constraint PK__StudentCourse primary key clustered(StudentID,CourseID)
)

create table dbo.StudentSpeaker
(
    StudentID int not null,
    EventID int not null
)

insert into dbo.Courses(CourseID,CourseName)
values(1,'English'),(2,'Chinese')

insert into dbo.Students(StudentID,StudentName)
values(1,'stu_a'),(2,'stu_b'),(3,'stu_c'),(4,'stu_d')

insert into dbo.StudentCourse(StudentID,CourseID)
values(1,1),(2,1),(3,2)

insert into dbo.StudentSpeaker(StudentID,EventID)
values(1,101),(4,102)

View Code

图片 8

2,在Relationships视图中,创建表之间的关系

基数关系(Cardinality)根据数据之间的关系创建,筛选方向根据过滤的逻辑设置。默认情况下,PowerBI会自动检查(AutoDetect)数据之间的关系,根据检查的结果(列名和列值的唯一性)自动创建关系,在Relationships视图中,关系是一条有方向的折线,折线的两端是数字,表示基数(Cardinality)关系,折线中间的有向箭头表示筛选方向(Direction)。

PowerBI不会智能到尽善尽美,用户需要根据数据内在的关系对PowerBI自动创建的关系进行修正,或者,例如,把数据表Students和StudentCourse之间的关系修改为:1:N和双向筛选,双击关系(折线),弹出编辑关系(Edit Relationship)的窗体:

图片 9

在每个表下方面板中,会显示列名和示例数据。基数(Cardinality)关系是Many to one,其表达式是:*:1,表达式左边的表位于上面,右边的表位于下面,用于建立关系的数据列是灰色选中状态。交叉筛选方向(Cross filter direction)选择Both,勾选“Make this relationship active”,点击OK,完成关系的创建,如下图,点击关系(折线),用于建立关系的数据列处于选中状态。

图片 10

 图片 11

三,利用间接关系实现业务需求

报表需要实现的业务需求是:根据课程(Course)统计作为演讲者(Speaker)的学生数量

在做报表时,必须熟悉数据和数据之间的关系,在数据表StudentCourse中,共有3个学生选课,学号分别是1、2和3,存在不选课的学生,而在数据表StudentSpeaker中,只有学号1的学生满足条件,因此,根据课程(Course)统计作为演讲者(Speaker)的学生数量的结果应该是:

  • 选修English的学生数量是0;
  • 修改Chinese的学生数量是1;
  • 对所有课程做统计,学生数量是选修English和选修Chinese的数量之和,1(=0 1);

1,设置课程Filter

数据表Course是查找表,由于StudentCourse中的课程(CourseID)都存在于Course表中,所有,Slicer图表中不存在Blank选项。

图片 12

2,添加Card图表,显示统计数量

在Page中添加Card图表(Visualizations),在图表的Fields属性中,选择数据表SutdentSpeaker的StudentID字段,属性值自动变成:聚合函数 of 字段值。

 图片 13

3,设置聚合函数

由于一个学生,可能在多个活动(Event)中担当演讲者(Speaker),因此,必须对StudentID进行去重,在图表的Fields属性值“Count of StudentID” 中右击,选择聚合函数选择Count(Distinct)

图片 14

4,设置图表的显示属性

切换到“刷子”Icon,禁用Category lable,启用Title,修改Title Text、Font color,Alignment和Text Size,

图片 15

5,分析报表数据

课程选择Chinese,数量是Blank

图片 16

课程选择English,数量是1

图片 17

选择所有课程,数量是1

图片 18

6,清洗数据

默认情况下,图表不选择任何课程(Course),数量是2,这个结果在逻辑上是“错误”的,对于没有选择任何选项的Filter,PowerBI不会做任何筛选关联。

图片 19

导致错误的原因是由于数据表StudentSpeaker出现脏数据,没有选修任何课程的学生(本例是学号为4的学生)出现在StudentSpeaker数据表中,要修正查询的结果,必须清洗脏数据。

关系的传递有一个副作用,就是Filter的全选和不选有很大的不同:不选包含Blank值,而全选不包含Blank值。

四,编辑交互行为

选择不同的CourseName,度量值Speaker#自动根据Filter做相应的数据过滤,重新统计数据,这种过滤的流向是单向的,由数据关系中的交叉过滤方向(Cross Filter Direction)决定,PowerBI允许在不修改关系的情况下,编辑Filter和度量值的交互行为,使报表中的不同图表(Visiualization)选择性地响应或不响应过滤条件(Filter)。

1,选择Filter,切换到Format菜单,选择“Edit Interactions”

图片 20

2,编辑交互行为

默认情况下,Card图表的Filter图片 21是选中,将其切换到禁止图片 22,这样,选择Course过滤器中的任何一个选项,都不会影响Card图表显示的数据值。

图片 23

在关系的传递时,数据行的缺失会导致下游数据出现空值(BLANK),我使用如下的关系图演示,注意关系的类型和指向:

五,在数据建模中,要遵守一定的设计原则

在数据建模中,不仅需要属性业务需求,而且需要熟悉数据及其关系,遵守一定的设计原则,能够避免出现一些显而易见的错误:

  • 要根据业务需求,设计报表的过滤条件(Filer)和度量值;
  • 过滤器是数据建模的出发点,根据过滤条件和数据之间内在的关系设计数据模型;
  • 根据数据之间内在的关系,加载数据,保证数据表中不出现脏数据。

 

参考文档:

Power BI 文档

在 Power BI Desktop 中创建和管理关系

图片 24

导入示例的数据,各个表的数据如下图所示:

图片 25

把CourseID作为Filter(Slice可视化控件),下游数据(Card可视化控件,Count(Distinct EventID)会出现Blank,这是因为存在StudentID=4的数据行没有选择对应的CourseID。

图片 26

不选择任何Filter时,“Count of EventID”的值是2,包含Blank对应的EventID

图片 27

当选择CourseID=1时,“Count of EventID”的值是1:

图片 28

当选择所有Filter时,“Count of EventID”的值是1:

图片 29

四,关系的设计

把数据模型设计成维度表和事实表,维度表和事实表之间的关系是1:N,交叉过滤方向由维度表指向事实表,避免使用Both交叉方向。

由于PowerBI不支持“多对多”关系类型,在处理这种数据时,通常有两种方式:

  1. 删除关系:把"多对多"的数据合并到一个表中
  2. 把"多对多"的关系转换成两个"一对多"的关系:新建一个维度表,该维度表只包含单列的唯一值,连接原“多对多”的两个表

 

参考文档:

Power BI Desktop New Feature: Bi-Directional Relationships!

Why Is My Relationship Inactive in Power BI Desktop?

 

本文由pc28.am发布于pc28.am神测网,转载请注明出处:数量建立模型,关系和互相

上一篇:10不能够访谈移动硬盘提醒参数错误怎么做,wi 下一篇:没有了
猜你喜欢
热门排行
精彩图文