Loading Measurement Tables - Methodologies

Question

I recently worked on a project where you need to populate Dim Tables from EDW tables.

EDW tables are Type II, which stores historical data. When the Dim Table load arrives, for which the source may be several EDW tables or a single table with a multi-level rotation (by attributes).

Average value: there would be 10 entries - one for each attribute that needs to be turned on domain_code to make one line in Dim. Of these 10 records, there would be some attributes with the same domain code, but with a different sub_domain_code, which needs further rotation on the subdomain code.

Example:

if I got a domain code: 01,02, 03 => which are direct turning marks for a domain code I would also get a domain code: 10 with a subdomain code / version as 2006,2007,2008,2009

This means that I need to split the source table with the above attributes into two => one for the domain code and the other for the domain_code + version.

so far so good.

When it comes to loading the Dim Table:

According to the Dimensions design specifications (originally written by a third party), they want:

for each individual EDW (attribute) change, it must collect all related records (for this NK), which mean a new one with different attribute values ​​that flow => process them to create a new dull record and insert it.

, 100 ( NK), 100 + (100 * 9) / . .

, , - , NK (, ), .

.

, .

alt text http://img96.imageshack.us/img96/1203/modelzp.jpg

Answer 1

.

.

.

""

( )

, , ( )

, , , - , , - , - .

-- SO3014289

CREATE TABLE #src (
    key1 varchar(4) NOT NULL
    ,key2 varchar(3) NOT NULL
    ,key3 varchar(3) NOT NULL
    ,AttribCode int NOT NULL
    ,AttribSubCode varchar(2)
    ,Value varchar(10) NOT NULL
    ,[Start] date NOT NULL
    ,[End] date NOT NULL
)

INSERT INTO #src VALUES
('9750', 'C04', '789', 1, NULL, 'AAA', '1/1/2000', '12/31/9999')
,('9750', 'C04', '789', 2, NULL, 'BBB', '1/1/2000', '12/31/9999')
,('9750', 'C04', '789', 3, 'V1', 'XXXX', '1/1/2000', '12/31/9999')
,('9750', 'C04', '789', 3, 'V2', 'YYYY', '1/1/2000', '1/2/2000')
,('9750', 'C04', '789', 3, 'V2', 'YYYYY', '1/2/2000', '12/31/9999')

;WITH basedata AS (
    SELECT key1 + '-' + key2 + '-' + key3 AS NK
    ,CASE WHEN AttribCode = 1 THEN Value ELSE NULL END AS COL1
    ,CASE WHEN AttribCode = 2 THEN Value ELSE NULL END AS COL2
    ,CASE WHEN AttribCode = 3 AND AttribSubCode = 'V1' THEN Value ELSE NULL END AS COL3
    ,CASE WHEN AttribCode = 3 AND AttribSubCode = 'V2' THEN Value ELSE NULL END AS COL4
    ,[Start]
    ,[End]
    FROM #src
)
,ChangeTimes AS (
    SELECT NK, [Start] AS Dt
    FROM basedata
    UNION 
    SELECT NK, [End] AS Dt
    FROM basedata
)
,Snapshots as (
    SELECT s.NK, s.Dt AS [Start], MIN(e.Dt) AS [End]
    FROM ChangeTimes AS s
    INNER JOIN ChangeTimes AS e
        ON e.NK = s.NK
        AND e.Dt > s.Dt
    GROUP BY s.NK, s.Dt
)
SELECT Snapshots.NK
    ,MAX(COL1) AS COL1
    ,MAX(COL2) AS COL2
    ,MAX(COL3) AS COL3
    ,MAX(COL4) AS COL4
    ,Snapshots.[Start]
    ,Snapshots.[End]
FROM Snapshots
INNER JOIN basedata
    ON basedata.NK = Snapshots.NK
    AND NOT (basedata.[End] <= Snapshots.[Start] OR basedata.[Start] >= Snapshots.[End])
GROUP BY Snapshots.NK
    ,Snapshots.[Start]
    ,Snapshots.[End]