a****b
发帖数: 489 | 1 Any thoughts/concerns/best practices about two slowly chaning dimensions
have FK relationship?
Thanks |
e****7
发帖数: 4387 | 2 好久没来了，
you can have a bridge(fact, a factless fact) table with SK for the said dims
if it's the
relationship that you are interested in. otherwise your other fact tables
should already have the relationship baked in.
问题比较模糊，如果能举实例，或许有比较确切的方案。
【在 a****b 的大作中提到】
: Any thoughts/concerns/best practices about two slowly chaning dimensions
: have FK relationship?
: Thanks
|
a****b
发帖数: 489 | 3 For example, I have two dimensions, Dim_Emp and Dim_Emp_Account. Assuming,
there is only a one-to-many relationship between Dim_Emp and Dim_Emp_Account
, meaning one account belongs to one emplyee; and one employee could has
several account. We COULD PUT Dim_Emp_Key in Dim_Emp_Acc. But for the design
, because
Dim_Emp is a conformed dimension, we CANNOT merge Dim_Emp into Dim_Emp_Acct(
denormalization). Dim_Emp is an SCD because of address, Dim_Emp_Account is
an SCD too because of Banker or some other attributes.
What would be the impact if there is a FK relationship between the above
dimensions? What could be the best practice to haNdle the above situation? |
e****7
发帖数: 4387 | 4 there should be a dimAccount table as well right?
this actually fits into what I wrote earlier. the true relationship between
EMP and account should probably live in the fact table which records
transactions between EMP and accounts. so factPaycheck or something like
that.
the relationship alone between account and employees is probably less useful
unless there is a transaction between them.
another way to look at this is what information is the business looking for
from their point of view. are they looking for the relationship itself or
the transaction between them.
hope this makes sense.
Account
design
Acct(
【在 a****b 的大作中提到】
: For example, I have two dimensions, Dim_Emp and Dim_Emp_Account. Assuming,
: there is only a one-to-many relationship between Dim_Emp and Dim_Emp_Account
: , meaning one account belongs to one emplyee; and one employee could has
: several account. We COULD PUT Dim_Emp_Key in Dim_Emp_Acc. But for the design
: , because
: Dim_Emp is a conformed dimension, we CANNOT merge Dim_Emp into Dim_Emp_Acct(
: denormalization). Dim_Emp is an SCD because of address, Dim_Emp_Account is
: an SCD too because of Banker or some other attributes.
: What would be the impact if there is a FK relationship between the above
: dimensions? What could be the best practice to haNdle the above situation?
|
b****k
发帖数: 23 | 5 你可以看看我的博客 数据仓库系列 - 缓慢渐变维度 (Slowly Changing Dimension)
常见的三种类型及原型设计 http://www.cnblogs.com/biwork/p/3363749.html
微软BI 之SSIS 系列 - 数据仓库中实现 Slowly Changing Dimension 缓慢渐变维度的
三种方式
http://www.cnblogs.com/biwork/p/3371338.html
应该可以帮助到你 |
s*********t
发帖数: 296 | 6 通常的做法是象eagle7说的：两个dimension table(dimEmp & dimAccount)，关联通过
fact table实现。
如果确实需要关联dimEmp & dimAccount，可以在dimEmp & dimAccount中间建一个
bridge table。一对多的关系下，也有人在dimEmp里加dimAccount的logical key 作为
FK。
between
useful
for
【在 e****7 的大作中提到】
: there should be a dimAccount table as well right?
: this actually fits into what I wrote earlier. the true relationship between
: EMP and account should probably live in the fact table which records
: transactions between EMP and accounts. so factPaycheck or something like
: that.
: the relationship alone between account and employees is probably less useful
: unless there is a transaction between them.
: another way to look at this is what information is the business looking for
: from their point of view. are they looking for the relationship itself or
: the transaction between them.
|
a******g
发帖数: 725 | 7 slowly chaning dimensions只能用在简单的东西上，就是点鼠标。
垃圾货， 实用性太差。 复杂的都是CODE 解决。 |
e****7
发帖数: 4387 | 8 不会吧，我开始做BI的时候没少花功夫，白费了？
【在 a******g 的大作中提到】
: slowly chaning dimensions只能用在简单的东西上，就是点鼠标。
: 垃圾货， 实用性太差。 复杂的都是CODE 解决。
|
