II型尺寸加入
我在OLTPII型尺寸加入
CREATE TABLE TransactionState
(
TransactionStateId INT IDENTITY (1, 1) NOT NULL,
TransactionStateName VarChar (100)
)
当这涉及到我的OLAP下表查找表,我改变结构如下:
CREATE TABLE TransactionState
(
TransactionStateId INT NOT NULL, /* not an IDENTITY column in OLAP */
TransactionStateName VarChar (100) NOT NULL,
StartDateTime DateTime NOT NULL,
EndDateTime NULL
)
我的问题是关于TransactionStateId柱。随着时间的推移,我可能在我的OLAP中有重复的TransactionStateId值,但是通过StartDateTime和EndDateTime的组合,它们将是唯一的。
我看到添加了一个OriginalTransactionStateId并将传入的TransactionStateId映射到它的Type-2维度的样本,再加上一个新的TransactionStateId IDENTITY字段变为PK并用于连接。
CREATE TABLE TransactionState
(
TransactionStateId INT IDENTITY (1, 1) NOT NULL,
OriginalTransactionStateId INT NOT NULL, /* not an IDENTITY column in OLAP */
TransactionStateName VarChar (100) NOT NULL,
StartDateTime DateTime NOT NULL,
EndDateTime NULL
)
我应该去bachellorete#2还是bachellorete#3?
通过这句话:
随着
StartDateTime
和EndDateTime
的组合,他们将是唯一的。
你的意思是说他们从不重叠,或者他们满足数据库UNIQUE
约束?
如果是前者,那么您可以在连接使用StartDateTime
,但要注意,它可能是低效的,因为它会使用一个"<="
条件,而不是"="
。
如果是后者,那么只需使用假身份。
数据库一般不允许一个高效的算法,此查询:
SELECT *
FROM TransactionState
WHERE @value BETWEEN StartDateTime AND EndDateTime
,除非你做神秘的技巧与SPATIAL
数据。
这就是为什么你必须在一个JOIN
使用此条件:
SELECT *
FROM factTable
CROSS APPLY
(
SELECT TOP 1 *
FROM TransactionState
WHERE StartDateTime <= factDateTime
ORDER BY
StartDateTime DESC
)
,这将剥夺的可能性优化使用HASH JOIN
,这是最有效的在许多情况下,这样的查询。
请参阅这篇文章,详细了解这种做法:
重写查询,以便它可以使用HASH JOIN
导致600%
倍的性能提升,虽然这只是可能的,如果你的日期时间具有一天或更低的准确性(或者哈希表将会变得非常大)。
由于您的时间部分被剥离的StartDateTime
和EndDateTime
,你可以创建一个CTE
这样的:
WITH cal AS
(
SELECT CAST('2009-01-01' AS DATE) AS cdate
UNION ALL
SELECT DATEADD(day, 1, cdate)
FROM cal
WHERE cdate <= '2009-03-01'
),
state AS
(
SELECT cdate, ts.*
FROM cal
CROSS APPLY
(
SELECT TOP 1 *
FROM TransactionState
WHERE StartDateTime <= cdate
ORDER BY
StartDateTime DESC
) ts
WHERE ts.EndDateTime >= cdate
)
SELECT *
FROM factTable
JOIN state
ON cdate = DATE(factDate)
如果您的日期范围跨度超过100
日期,上CTE
调整MAXRECURSION
选项。
请注意IDENTITY(1,1)
是在该列中自动生成值的声明。这与PRIMARY KEY
不同,后者是一个将列转换为主键聚簇索引的声明。这两个声明意味着不同的事情,如果您不说PRIMARY KEY
,就会有性能影响。
@大卫B:请原谅,我没有发布整个表的DDL。代码发布了OTTOMH。由于我的问题是关于数据服务器设计,所以我专注于这一部分。 – 2009-07-28 18:22:25
您也可以使用SSIS来加载DW。在slowly changing dimension(SCD)转换中,您可以设置如何处理每个属性。如果选择历史属性,则类型2 SCD应用于整行,并且转换处理细节。如果您更喜欢start_date
,end_date
或current/expired
列,您也可以进行配置。
这里要区分的是主键和业务(自然)键之间的区别。主键唯一标识表中的一行。业务密钥唯一标识业务对象/实体,并可以在维度表中重复使用。每次应用SCD 2时,都会插入一个新行,并带有一个新的主键,但具有相同的业务键;旧行被标记为过期,而新行被标记为当前 - 或开始日期和结束日期字段被适当地填充。
DW不应公开主键,因此来自OLTP的传入数据包含业务键,而主键的分配受DW的控制; IDENTITY int对维度表中的PK很有用。
很酷的是,SSIS中的SCD转换处理了这一点。
@Quassnoi:对于StartDateTime和EndDateTime都会剥离时间组件 - 它们将相隔一天。 – 2009-07-28 18:24:12
@Quassnio:感谢您的代码示例。你对我关于这两个单身汉的原始问题有什么看法? – 2009-07-28 19:21:47
@Tapori:如果用“bachelorettes”表示'SCD'类型,那么'Type 2'更好一点就是每个状态的版本要多于'2'。通常认为查询更慢(出于我在文章中描述的原因),但使用帖子中的技术可以获得不错的表现。 – Quassnoi 2009-07-28 19:59:15