Dimensions Loading And SOR layer creation

gistfile1.txt

CREATE SCHEMA IF NOT EXISTS star_sor;
SET SEARCH_PATH TO star_sor;

-- Dim from different source system
DROP TABLE IF EXISTS d_shop;
CREATE TABLE d_shop
(
    shop_skey        INT IDENTITY (0,1),
    shop_code        VARCHAR(16),
    shop_description VARCHAR(64),
    effective_date   DATE,
    end_date         DATE

) DISTSTYLE ALL
  SORTKEY (shop_skey);

INSERT
  INTO d_shop (shop_code, shop_description, effective_date)
VALUES ('-99', 'Unknown', '1900-01-01'),
       ('2400', 'Germany Webshop', '1900-01-01'),
       ('DE', 'Germany Webshop', '1900-01-01'),
       ('home24_de', 'Germany Webshop', '1900-01-01');

Select * from d_shop;
-- dim from single source system like SAP (SCD1 dim)
-- we will only insert new dim rows only
DROP TABLE IF EXISTS d_material_type_scd1;
CREATE TABLE d_material_type_scd1
(
    material_type_skey        INT IDENTITY (0,1),
    material_type_code        VARCHAR(64),
    material_type_description VARCHAR(128)
) DISTSTYLE ALL
  SORTKEY (material_type_skey);
INSERT
  INTO d_material_type_scd1(material_type_code,
                            material_type_description)
VALUES ('-99', 'Unknown');

DROP TABLE IF EXISTS intermediate_d_material_type_scd1;
CREATE TABLE intermediate_d_material_type_scd1
AS
SELECT mtart material_type_code,
       mtbez material_type_description
  FROM sap_erp_replication.t134t
 WHERE spras = 'D'
   AND mandt = 200;

DELETE
  FROM intermediate_d_material_type_scd1
      USING d_material_type_scd1
 WHERE intermediate_d_material_type_scd1.material_type_code = d_material_type_scd1.material_type_code
   AND intermediate_d_material_type_scd1.material_type_description = d_material_type_scd1.material_type_description;

INSERT
  INTO d_material_type_scd1
  (material_type_code,
   material_type_description)
SELECT material_type_code,
       material_type_description
  FROM intermediate_d_material_type_scd1;

DROP TABLE intermediate_d_material_type_scd1;
select * from d_material_type_scd1

-- dim from single source system like SAP (SCD2 dim for storing the historical changes in the dim )
DROP TABLE IF EXISTS d_material_type_scd2;
CREATE TABLE d_material_type_scd2
(
    material_type_skey        INT IDENTITY (0,1),
    material_type_code        VARCHAR(64),
    material_type_description VARCHAR(128),
    effective_date            DATE,
    end_date                  DATE
) DISTSTYLE ALL
  SORTKEY (material_type_skey);
INSERT
  INTO d_material_type_scd2(material_type_code,
                            material_type_description,
                            effective_date)
VALUES ('-99', 'Unknown', '1900-01-01');

DROP TABLE IF EXISTS intermediate_d_material_type_scd2;
CREATE TABLE intermediate_d_material_type_scd2 AS
SELECT mtart material_type_code,
       mtbez material_type_description
  FROM sap_erp_replication.t134t
 WHERE spras = 'D'
   AND mandt = 200;

DELETE
  FROM intermediate_d_material_type_scd2
      USING (SELECT material_type_code,
                    material_type_description
               FROM d_material_type_scd2
              WHERE end_date IS NULL) d_material_type_scd2
 WHERE intermediate_d_material_type_scd2.material_type_code = d_material_type_scd2.material_type_code
   AND intermediate_d_material_type_scd2.material_type_description = d_material_type_scd2.material_type_description;

UPDATE d_material_type_scd2
   SET end_date = current_date - 1
 WHERE end_date IS NULL
   AND material_type_code IN (
     SELECT d_material_type_scd2.material_type_code
       FROM d_material_type_scd2
            INNER JOIN intermediate_d_material_type_scd2
            ON d_material_type_scd2.material_type_code = intermediate_d_material_type_scd2.material_type_code
                AND d_material_type_scd2.material_type_description <>
                    intermediate_d_material_type_scd2.material_type_description
      WHERE end_date IS NULL)

DELETE
  FROM intermediate_d_material_type_scd2
      USING (SELECT material_type_code
               FROM d_material_type_scd2) d_material_type_scd2
 WHERE intermediate_d_material_type_scd2.material_type_code = d_material_type_scd2.material_type_code

INSERT
  INTO d_material_type_scd2
  (material_type_code,
   material_type_description,
   effective_date)
SELECT material_type_code,
       material_type_description,
       current_date
  FROM intermediate_d_material_type_scd2;

-- For Junk Dim please read this one https://ayushbi.medium.com/what-is-junk-dimension-a092bc9ae847