t-sql测试数据仓库类型2变化

Question

我需要查看数据仓库并检查类型 2 更改是否正常工作

我需要检查一行的有效日期是否与下一行的起始日期有效。

此检查是为了确保已结束的行也已正确开始，

谢谢，Marc

Answer 1

以下涉及 Kimball 2 型尺寸表。

请注意，这假定

1. 3000-01-01 作为当前条目的遥远未来的日期。
2. CustomerKey 是一个自动递增的整数。

此示例应该为您提供缺少或不匹配下一个条目的行列表。

;
with
q_00 as  (
select
      CustomerKey
    , CustomerBusinessKey
    , rw_ValidFrom
    , rw_ValidTo
    , row_number() over (partition by CustomerBusinessKey order by CustomerKey asc) as rn
from dimCustomer
)
select
      a.CustomerKey
    , a.CustomerBusinessKey
    , a.rw_ValidFrom
    , a.rw_ValidTo
    , b.CustomerKey          as b_key
    , b.CustomerBusinessKey  as b_bus_key
    , b.rw_ValidFrom         as b_ValidFrom
    , b.rw_ValidTo           as b_ValidTo
from      q_00 as a
left join q_00 as b on b.CustomerBusinessKey = a.CustomerBusinessKey and (b.rn = a.rn + 1) 
where a.rw_ValidTo < '3000-01-01'
  and a.rw_ValidTo != b.rw_ValidFrom ;

也有用

-- Make sure there are no nulls
-- for rw_ValidFrom, rw_ValidTo
select
      CustomerKey
    , rw_ValidFrom
    , rw_ValidTo
from dimCustomer
where rw_ValidFrom is null
   or rw_ValidTo   is null ;

-- make sure there are no duplicates in  rw_ValidFrom
-- for the same customer
select
      CustomerBusinessKey
    , rw_ValidFrom
    , count(1) as cnt
from dimCustomer
group by CustomerBusinessKey, rw_ValidFrom
having count(1) > 1  ;

-- make sure there are no duplicates in  rw_ValidTo
-- for the same customer
select
      CustomerBusinessKey
    , rw_ValidTo
    , count(1) as cnt
from dimCustomer
group by CustomerBusinessKey, rw_ValidTo
having count(1) > 1  ;