that identifying the correct fact record between SCD2 and fact table between dates

Question

that identifying the correct fact record between SCD2 and fact table between dates

azdeji

SSCrazy

Points: 2535
More actions
December 6, 2016 at 3:03 am

#332601

Hi,
I’m trying to create SCD bridge table between Dim and fact table that would allow for point in time reporting and aggregation from SCD type2 Dim and the fact table.
The query that identifies the correct fact record at this time between the dim and fact is what I require help on and removing the subset table if possible for better performance.
-- SET EXTRACT DATE
SET DATEFORMAT YMD
-- find all the records between dimcustomer table scd2 and fact transaction table would allow for point in time reporting and aggregation
DECLARE @ExtractDate DATE = '2016-09-20'
-- CREATE TABLES
IF OBJECT_ID('tempdb..#BridgeCustomerSCD') IS NOT NULL
BEGIN
DROP TABLE #BridgeCustomerSCD
END
CREATE TABLE #BridgeCustomerSCD
(
SK_FirstSCD int,
SK_SecondSCD int,
EffectiveFrom date,
EffectiveTill date,
IsCurrent int
)
IF OBJECT_ID('tempdb..#BridgeCustomerSCDSubset') IS NOT NULL
BEGIN
DROP TABLE #BridgeCustomerSCDSubset
END
CREATE TABLE #BridgeCustomerSCDSubset
(
SK_FirstSCD int,
SK_SecondSCD int
)
CREATE NONCLUSTERED INDEX IDX_CustSCD ON #BridgeCustomerSCDSubset(SK_FirstSCD, SK_SecondSCD)
IF OBJECT_ID('tempdb..#DimCustomer') IS NOT NULL
BEGIN
DROP TABLE #DimCustomer
END
CREATE TABLE #DimCustomer
(
SK_Customer int,
BK_Customer varchar(5),
CustomerDOB Date,
EffectiveFrom date,
EffectiveTill date,
IsCurrent int
)
IF OBJECT_ID('tempdb..#FactTransaction') IS NOT NULL
BEGIN
DROP TABLE #FactTransaction
END
CREATE TABLE #FactTransaction
(
PK_Transaction int,
BK_Policy varchar(15),
SK_Customer int,
TransactionType varchar(25),
TransactionDate date,
TotalPremium decimal(12,2)
)
IF OBJECT_ID('tempdb..#FactTransactionRecords') IS NOT NULL
BEGIN
DROP TABLE #FactTransactionRecords
END
CREATE TABLE #FactTransactionRecords
(
PK_Transaction int,
BK_Policy varchar(15),
SK_Customer int,
TransactionType varchar(25),
TransactionDate date,
TotalPremium decimal(12,2)
)
-- POPULATE MOCK DATA
INSERT INTO #DimCustomer
(
SK_Customer,
BK_Customer,
CustomerDOB,
EffectiveFrom,
EffectiveTill,
IsCurrent
)
SELECT 1, 'Test1', '1950-01-01', '2016-01-01', '2016-09-01', 0
UNION ALL
SELECT 2, 'Test1', '1951-01-01', '2016-09-02', '2099-12-31', 1
UNION ALL
SELECT 3, 'Test2', '1952-01-01', '2016-01-01', '2016-09-01', 0
UNION ALL
SELECT 4, 'Test2', '1953-01-01', '2016-09-02', '2016-10-01', 0
UNION ALL
SELECT 5, 'Test2', '1954-01-01', '2016-10-02', '2099-12-31', 1
INSERT INTO #FactTransaction
(
PK_Transaction,
BK_Policy,
SK_Customer,
TransactionType,
TransactionDate,
TotalPremium
)
SELECT 1, 'Policy1', 1, 'Sale', '2016-01-01', 100.00
UNION ALL
SELECT 2, 'Policy1', 1, 'PremiumIncrease', '2016-03-01', 120.00
UNION ALL
SELECT 3, 'Policy2', 3, 'Sale', '2016-01-01', 150.00
UNION ALL
SELECT 4, 'Policy2', 5, 'ChangeInBenefit', '2016-10-02', 150.00
INSERT INTO #BridgeCustomerSCD
(
SK_FirstSCD,
SK_SecondSCD,
EffectiveFrom,
EffectiveTill,
IsCurrent
)
SELECT
allSKs.SK_Customer,
SKsDates.SK_Customer,
SKsDates.EffectiveFrom,
SKsDates.EffectiveTill,
SKsDates.IsCurrent
FROM
(
SELECT
SK_Customer, BK_Customer
FROM
#DimCustomer
) allSKs
INNER JOIN
(
SELECT
SK_Customer, BK_Customer, EffectiveFrom, EffectiveTill, IsCurrent
FROM
#DimCustomer
) SKsDates
ONallSKs.BK_Customer = SKsDates.BK_Customer
ORDER BY
allSKs.SK_Customer,
SKsDates.SK_Customer
--- POINT IN TIME EXTRACT
/*
Gets data as at date specified at top of code, this does not include the query that idetifies the correct fact record at this time (not sure how to that yet was think some
SELECT ODC.SK_Customer AS SK_FirstCustomer
, DC.SK_Customer AS SK_Customer
FROM (
SELECT SK_Customer
, BK_Customer
, EffectiveTill
FROM #DimCustomer
WHERE EffectiveFrom <= @ExtractDate
AND EffectiveTill >= @ExtractDate
) AS DC
LEFT JOIN #DimCustomer AS ODC
ON DC.BK_Customer = ODC.BK_Customer
AND ODC.EffectiveTill <= DC.EffectiveTill
--and join on transaction table to idetifies the correct fact record at this time
)so is only used to deomstrate use of the bridge.
*/
--create scd subset
--I’m currently using a subset table to get the results but it will become monstrous to deal with in the future, is there a better way?
INSERT INTO #BridgeCustomerSCDSubset
(
SK_FirstSCD,
SK_SecondSCD
)
SELECT
SK_FirstSCD,
SK_SecondSCD
FROM
#BridgeCustomerSCD
WHERE
EffectiveFrom <= @ExtractDate
ANDEffectiveTill >= @ExtractDate
--identify transactions
INSERT INTO #FactTransactionRecords
SELECT
PK_Transaction,
BK_Policy,
SK_Customer,
TransactionType,
TransactionDate,
TotalPremium
FROM
(
SELECT
PK_Transaction,
BK_Policy,
SK_Customer,
TransactionType,
TransactionDate,
TotalPremium,
ROW_NUMBER() OVER (PARTITION BY BK_Policy ORDER BY TransactionDate DESC) Ordinal
FROM
#FactTransaction
WHERE
TransactionDate <= @ExtractDate
) sub
WHERE
sub.Ordinal = 1
-- Wanted result -- if the date was '2016-09-20' the following policies should be showing-
SELECT
ft.BK_Policy,
dc.CustomerDOB,
ft.TotalPremium,
@ExtractDate
FROM
#FactTransaction ft
INNER JOIN#BridgeCustomerSCDSubset bc
ONbc.SK_FirstSCD = ft.SK_Customer
INNER JOIN#DimCustomer dc
ONdc.SK_Customer = bc.SK_SecondSCD
WHERE
ft.PK_Transaction in (2,4) ----
-- Clean Temps
IF OBJECT_ID('tempdb..#BridgeCustomerSCD') IS NOT NULL
BEGIN
DROP TABLE #BridgeCustomerSCD
END
IF OBJECT_ID('tempdb..#BridgeCustomerSCDSubset') IS NOT NULL
BEGIN
DROP TABLE #BridgeCustomerSCDSubset
END
IF OBJECT_ID('tempdb..#DimCustomer') IS NOT NULL
BEGIN
DROP TABLE #DimCustomer
END
IF OBJECT_ID('tempdb..#FactTransaction') IS NOT NULL
BEGIN
DROP TABLE #FactTransaction
END
Dimcusomer table
SK_CustomerBK_CustomerCustomerDOBEffectiveFromEffectiveTillIsCurrent
1Test11950-01-012016-01-012016-09-010
2Test11951-01-012016-09-022099-12-311
3Test21952-01-012016-01-012016-09-010
4Test21953-01-012016-09-022016-10-010
5Test21954-01-012016-10-022099-12-311
FactTransaction table
PK_TransactionBK_PolicySK_CustomerTransactionTypeTransactionDateTotalPremium
1Policy11Sale2016-01-01100.00
2Policy11PremiumIncrease2016-03-01120.00
3Policy23Sale2016-01-01150.00
4Policy25ChangeInBenefit2016-10-02150.00
Wanted result -- if the date was '2016-09-20' the following policies should be showing-
PK_TransactionBK_PolicySK_CustomerTransactionTypeTransactionDateTotalPremium
2Policy11PremiumIncrease2016-03-01120.00
3Policy23Sale2016-01-01150.00
I’m currently using a subset table to get the results but it will become monstrous to deal with in the future, is there a better way?
Thanks in advance

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

I'm not really seeing what your objective means in real-world terms. You seem to want some kind of bridge between your dimension table and your fact table, but I have no clear picture from either your queries or your post as to what that relationship needs to be. If you could express that in words that describe real-world events, it would be much simpler to understand your objective, as we don't really know what kind of record counts exist in your real tables, nor any idea why there's a problem brewing.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)