t-sql测试数据仓库类型2的更改

Question

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

我需要检查一行上的vaild to date与下一行上的vaild from date是否相同。

此检查是为了确保已结束的行也已正确启动

谢谢，马克

Answer 1

下面是关于Kimball类型2维度表的内容。

请注意，这里假设

当前entries.

• CustomerKey的远期日期

1. 3000-01-01是一个自动递增的整数。

此示例应给出缺少或未匹配next条目的行的列表。

;
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  ;