Thursday, September 1, 2011

Netezza: preparing inter-linked warehouse dimension tables (interlinked by surrogate keys)


Initial Setup: Customer id is in address table and address id is in customer table. This situation can arise when we need to break up the whole customer table into basic customer table and customer address table for the warehouse.

Table: Customer

Table: Address


Requirement: (i) generate customer keys, address keys (ii) link customer table and address table with the newly generated keys

Solution Step 1: Generate keys for address rows:

Solution Step 2: Generate keys for customer rows:


Solution Step 3: Populate address keys of customer table by joining on customer id:

  
Solution Step 4: Use following query to prepare final address2 table with customer key:

INSERT INTO ADDRESS_2
(
 Address-key
,Address-id        
,Cust-id
,Cust-key
)
SELECT
 a.Address-key                                 
,a.Address-id                                    
,a.Cust-id
,COALESCE(q1.Cust-key, q2.Cust-key, -2)
FROM
ADDRESS a
LEFT OUTER JOIN
                CUSTOMER q1
ON
                (a.Cust-id         =             q1.Cust-id
                AND a.Address-id   =             q1.Pri-add-id)
LEFT OUTER JOIN
                CUSTOMER q2
ON
                (a.Cust-id         =             q2. Cust-id
                AND a.Address-id   =             q2.Sec-add-id)
;