免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 4803 | 回复: 0

Factless Fact Tables(英文资料鉴赏) [复制链接]

论坛徽章:
4
金牛座
日期:2014-08-21 12:58:152015年辞旧岁徽章
日期:2015-03-03 16:54:152015亚冠之本尤德科
日期:2015-05-22 00:05:18数据库技术版块每日发帖之星
日期:2015-06-23 22:20:00
发表于 2008-12-02 21:28 |显示全部楼层
Kimball Design Tip #50: Factless Fact Tables? Sounds Like Jumbo Shrimp?
By Bob Becker

Factless fact tables appear to be an oxymoron, similar to jumbo shrimp. How can you have a fact
table that doesn’t have any facts? We’ve discussed the basics of factless fact tables several times in
our books and articles. In this design tip, we use a factless fact table to complement our slowly
changing dimension strategies.

As you probably recall, a factless fact table captures the many-to-many relationships between
dimensions, but contains no numeric or textual facts. They are often used to record events or
coverage information. Common examples of factless fact tables include:
- Identifying product promotion events (to determine promoted products that didn’t sell)
- Tracking student attendance or registration events
- Tracking insurance-related accident events
- Identifying building, facility, and equipment schedules for a hospital or university

For more information on factless fact tables, see Ralph’s earlier articles at
http://www.intelligententerprise ... 602/warehouse.shtml and
http://www.dbmsmag.com/9609d05.html.

In today’s design tip, imagine we are working on a design for a large business-to-consumer company
(pick your favorite consumer-oriented industry – airline, insurance, credit card, banking,
communications, or web retailer). The company does business with tens of millions of customers. In
addition to the typical requirements for transaction schema to track consumer behavior and periodic
snapshot schema to trend our consumer relationships over time, our business partners need the
ability to see a customer’s exact profile (including dozens of attributes) at any point in time.
Long-time readers may remember Ralph discussing a similar situation in Design Tip 13

(http://ralphkimball.com/html/designtips/2000/designtip13.html). He outlined a technique where the
dimension itself captures profile change events as a slowly changing dimension Type 2, rather than
creating a fact table to capture the profile transactions. However, we are not likely to use the DT #13
technique in the current scenario given the huge data volumes (millions of customer rows) and
potentially volatile changes (dozens of attributes).

Let’s assume we design a base customer dimension (with minimal SCD Type 2 attributes), along
with four “mini” dimensions to track changes to customer credit attributes, customer preferences,
market segmentation/propensities, and customer geography. The five foreign keys are included in
the transaction-grained fact table, as well as the monthly snapshot. These foreign keys represent
the customer’s “state” when the fact row is loaded. So far so good, but we still need to support
customer profiling at any point in time. We consider using another periodic snapshot fact table,
loaded daily for every customer to capture the point-in-time relationship of the customer dimension
and associated mini-dimensions. This translates into loading tens of millions of snapshots nightly
with several years of history. We quickly do the math and decide to evaluate other alternatives.

About now you’re thinking “That’s great, but what about the jumbo shrimp?” We can use a factless
fact table to capture the relationship between the customer dimension and mini-dimensions over
time. We load a fact row in the factless fact table whenever there is a Type 2 change to the base
customer dimension or a change in the relationship between the base dimension and the minidimensions.
The factless fact table contains foreign keys for the base customer dimension and each
of the four mini-dimensions when the row is loaded. We then embellish this design with two dates,
row effective and row expiration, to locate a customer’s profile at any point in time. We might also
add a simple dimension to flag the current customer profile, in addition to a change reason
dimension to indicate what caused a new row to be loaded into the factless fact table.
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP