EDW opdatering

af Joakim Dalby

 

Indledning

Her præsenteres kort to eksempler på indlæsning til Enterprise Data Warehouse (EDW) databasen fra Data Sta­ging Area (DSA).

 

1: KLASSISK INDLÆSNING MED DATA UDSØGNING OG

    DATA BERIGELSE

Et kildesystem leverer data eller et LIS system trækker data fra kilden. Leverancen af data sker til en eller flere tabeller i DSA databasen, og tabellerne tømmes inden le­veran­cen påbegynder. EDW databasen udtrækker data fra DSA databasen via views i EDW databasen som kan indeholde omdannelse af feltnavne og forskellige dataudsøgningskriterier som findes i EDW i RULE tabeller. Efter ETL processen for­bli­ver data i DSA tabellen for at kunne teste og opnå sporbarhed i data, f.eks. hvilke data opfylder ikke dataudsøgningskriterierne og derved ikke blev medtaget i EDW tabellen.

 

Eksempelvis på et view i EDW databasen som udsøger data fra DSA databasen:

 

CREATE VIEW [dbo].[DSA_MedarbejderAnsættelse]

AS

SELECT Personnr AS Cprnr,

       AnsættelsesMånedDato, --en række pr måned en medarbejder ansat

       AdminEnh AS OrganisationNummer

FROM  DSA.dbo.MedarbejderAnsættelse dsa WITH (TABLOCK)

WHERE LønudbetaltIMåned = 1 AND

      EXISTS(SELECT 1 FROM dbo.EDW_RULE_Aflønningsform rul

           WHERE rul.AflønningsformNummer = dsa.AflønningsformNummer)

 

De udsøgte data lagres i en NEW tabellen i EDW databasen gennem en stored pro­ce­dure, som starter med at undersøge om der er data i DSA inden NEW tabellen tøm­mes og fyldes op med data fra DSA. Samtidig med og bagefter lagringen i NEW ta­bel­len foretages forskellige databerigelser f.eks. Køn og Alder og senere Med­ar­bej­derId fra EDW via en oversættelse af Cprnr.

 

CREATE PROCEDURE [dbo].[EDW_NEW_MedarbejderAnsættelse_Insert]

AS

BEGIN

SET NOCOUNT ON

IF (SELECT COUNT(*)

    FROM dbo.DSA_MedarbejderAnsættelse WITH (TABLOCK)) = 0

   RETURN

TRUNCATE TABLE dbo.EDW_NEW_MedarbejderAnsættelse

INSERT INTO dbo.EDW_NEW_MedarbejderAnsættelse WITH (TABLOCK)

(

      Cprnr,

      AnsættelsesMånedDato,

      OrganisationNummer,

      OrganisationNummerCenterKontor,

      KønId,

      AlderId,

      MedarbejderId,

)

SELECT

      Cprnr,

      AnsættelsesMånedDato,

      OrganisationNummer,

      OrganisationNummer,

      dbo.Get_KønId(Cprnr),

      dbo.Get_AlderFraCprnr(Cprnr, AnsættelsesMånedDato),

      0 AS MedarbejderId,

FROM dbo.DSA_MedarbejderAnsættelse WITH (TABLOCK)

-- MedarbejderId

UPDATE dbo.EDW_NEW_MedarbejderAnsættelse WITH (TABLOCK)

SET  MedarbejderId = stam.MedarbejderId

FROM dbo.EDW_NEW_MedarbejderAnsættelse new

     INNER JOIN

     (SELECT Cprnr, MIN(MedarbejderId) AS MedarbejderId

      FROM dbo.EDW_DB_Medarbejder WITH (TABLOCK)

      GROUP BY Cprnr

     ) stam ON stam.Cprnr = new.Cprnr

-- OrganisationNummerCenterKontor

UPDATE dbo.EDW_NEW_MedarbejderAnsættelse WITH (TABLOCK)

SET OrganisationNummerCenterKontor = org.OrganisationNummerOverniveau

FROM dbo.EDW_NEW_MedarbejderAnsættelse new

     INNER JOIN dbo.EDW_DB_Organisation org WITH (TABLOCK)

       ON org.OrganisationNummer = new.OrganisationNummer

WHERE org.Team = 1

END

 

De berigede data overføres fra NEW til DB tabellen i EDW, hvor der her er tale om full dump d.v.s. en tømning af DB tabellen og en total overførsel af alle rækker i NEW tabellen:

 

CREATE PROCEDURE  [dbo].[EDW_DB_MedarbejderAnsættelse_Insert]

AS

BEGIN

      SET NOCOUNT ON

      DECLARE @antalRækkerFør int

      DECLARE @antalRækkerEfter int

 

      SELECT @antalRækkerFør = COUNT(*)

      FROM dbo.EDW_DB_MedarbejderAnsættelse WITH (TABLOCK)

   

      TRUNCATE TABLE dbo.EDW_DB_MedarbejderAnsættelse

 

      INSERT INTO dbo.EDW_DB_MedarbejderAnsættelse WITH (TABLOCK)

      SELECT *

      FROM dbo.EDW_NEW_MedarbejderAnsættelse WITH (TABLOCK)

     

      SELECT @antalRækkerEfter = COUNT(*)

      FROM dbo.EDW_DB_MedarbejderAnsættelse WITH (TABLOCK) 

      IF @antalRækkerEfter > @antalRækkerFør

      BEGIN

         EXEC dbo.EDW_DB_Update_Kube 44

      END

END

 

De to stored procedures kaldes fra en IS-pakke (se nedenfor) der indgår i en samlet EDW_ETL IS-pakke (batch) som kaldes fra et SQL Agent job der afvikles automatisk hver mor­gen og udsender en email til driftpersonen om ETL proces status.

 

 

Incremental update med nye data repræsenteret i ny AnsættelsesMånedDato. I tilfælde af omkørsel af ETL indledes der med en sletning af tidligere leveret data med samme AnsættelsesMånedDato som findes i NEW tabellen, inden indsættelse i DB tabellen. Men der sker ikke nogen tømning af DB tabellen, for så mistes jo alle data der hidtil er leveret fra kildesystemet.

 

DELETE

FROM dbo.EDW_DB_MedarbejderAnsættelse WITH(TABLOCK)

WHERE AnsættelsesMånedDato IN (SELECT DISTINCT AnsættelsesMånedDato FROM dbo.EDW_NEW_MedarbejderAnsættelse WITH (TABLOCK))

 

INSERT INTO dbo.EDW_DB_MedarbejderAnsættelse WITH (TABLOCK)

SELECT *

FROM dbo.EDW_NEW_MedarbejderAnsættelse WITH (TABLOCK)

 

2: HISTORIK OPSAMLING I EDW

I en ledelsesinformationssystem løsning (LIS) vil Enterprise Data Warehouse (EDW) normalt opsamle, bevare og håndtere aktuelle og historiske data fra kildesystemerne ved indlæsning fra Data Staging Area (DSA), så historikken senere hen kan anven­des i en dimension i Data Warehouse (DW). I dette tip vises et eksempel på en ta­bel i DSA hvis indhold løbende ændres, og hvordan en tabel i EDW håndterer ændrin­ger sammen med en stored procedure til opdatering af tabellen.

 

DSA første gang

Et kildesystem leverer en række betalingsstatusoplysninger, der 07-02-2006 så ud:

 

DSA_BETALINGSSTATUS

 

Betalings-

statusKode

Betalings-

statusNavn

Betalings-

statusKategori

D

Dårlig

Dårlige betalere

G

God

Gode betalere

M

Meget godt

Gode betalere

N

Normal

Gode betalere

S

Speciel

Gode betalere

T

Meget dårlig

Dårlige betalere

 

BetalingsstatusKode er primærnøglen, og kommer fra kildesystemet (business key).

 

EDW første gang

I EDW er der implementeret en tabel som håndterer kommende opdateringer og efter et Extracting, Trans­for­ming, Loading (ETL) forløb mellem DSA og EDW hvor Historik­Slutdato er en system fix dato på 31-12-2050 og HistorikStartdato og HistorikSlutdato angiver den periode data er aktuelt for i kildesystemet.

 

EDW_DB_HISTORICAL_BETALINGSSTATUS

 

Betalings-

statusEID

Betalings-

statusKode

Betalings-

statusNavn

Betalings-

statusKategori

Historik-

Startdato

Historik-

Slutdato

1

D

Dårlig

Dårlige betalere

07-02-2006

31-12-2050

2

G

God

Gode betalere

07-02-2006

31-12-2050

3

M

Meget godt

Gode betalere

07-02-2006

31-12-2050

4

N

Normal

Gode betalere

07-02-2006

31-12-2050

5

S

Speciel

Gode betalere

07-02-2006

31-12-2050

6

T

Meget dårlig

Dårlige betalere

07-02-2006

31-12-2050

 

BetalingsstatusEID (EDW ID) er primærnøglen, et fortløbende nummer, der kan indgå i relationer som fremmednøgle. Derudover er der to andre sammen­satte kandidat­nøgler så det sikres, at samme Kode ikke står i to rækker med h.h.v. samme HistorikStartdato eller HistorikSlutdato, hvilket er implementeret som unikke indeks:

 

·         IX_EDW_DB_HISTORICAL_BETALINGSSTATUS_Startdato

o   BetalingsstatusKode

o   HistorikStartdato

·         IX_EDW_DB_HISTORICAL_BETALINGSSTATUS_Slutdato

o   BetalingsstatusKode

o   HistorikSlutdato

 

DSA anden gang

Den 22-11-2007 tilføjes nye koder O og R, G og T ændrer navn og S udgår:

 

Betalings-

statusKode

Betalings-

statusNavn

Betalings-

statusKategori

D

Dårlig

Dårlige betalere

G

Godt

Gode betalere

M

Meget godt

Gode betalere

N

Normal

Gode betalere

T

Tabsgivende

Dårlige betalere

O

Opfølgning

Dårlige betalere

R

Restance

Dårlige betalere

 

EDW anden gang

Efter ETL mellem DSA og EDW angives den periode dataet var aktuelt i kilde­sy­ste­met. Når data ændrer sig, sættes HistorikSlutdato til gårsdagens dato og den nye værdi indsættes som ny række med HistorikStartdato lig med dagsdato mens udgået data sættes til at slutte på dagsdato. Der­ved får to rækker med samme Kode ikke samme HistorikStartdato eller HistorikSlutdato i det tilfælde, at ETL køres flere gan­ge samme dag, hvor data imellemtiden ændrer sig i kildesystemet enten ved at ændre værdi eller udgå og ikke udgår.

 

Betalings-

statusEID

Betalings-

statusKode

Betalings-

statusNavn

Betalings-

statusKategori

Historik-

Startdato

Historik-

Slutdato

1

D

Dårlig

Dårlige betalere

07-02-2006

31-12-2050

2

G

God

Gode betalere

07-02-2006

21-11-2007

3

M

Meget godt

Gode betalere

07-02-2006

31-12-2050

4

N

Normal

Gode betalere

07-02-2006

31-12-2050

5

S

Speciel

Gode betalere

07-02-2006

22-11-2007

6

T

Meget dårlig

Dårlige betalere

07-02-2006

21-11-2007

7

G

Godt

Gode betalere

22-11-2007

31-12-2050

8

O

Opfølgning

Dårlige betalere

22-11-2007

31-12-2050

9

R

Restance

Dårlige betalere

22-11-2007

31-12-2050

10

T

Tabsgivende

Dårlige betalere

22-11-2007

31-12-2050

 

DSA tredje gang

Den 31-12-2007 om eftermiddagen flytter koden N ka­te­go­ri i kildesystemet, og ETL køres den 01-01-2008 kl. 00:05.

 

Betalings-

statusKode

Betalings-

statusNavn

Betalings-

statusKategori

N

Normal

Dårlige betalere

 

EDW tredje gang

Efter ETL mellem DSA og EDW fås ændring i eksisterende række og indsat ny række:

 

Betalings-

statusEID

Betalings-

statusKode

Betalings-

statusNavn

Betalings-

statusKategori

Historik-

Startdato

Historik-

Slutdato

4

N

Normal

Gode betalere

07-02-2006

31-12-2007

11

N

Normal

Dårlige betalere

01-01-2008

31-12-2050

 

 

 

Stored procedure der ajourfører EDW

 

CREATE PROCEDURE  [dbo].[EDW_DB_HISTORICAL_BETALINGSSTATUS_Insert]

AS

BEGIN

SET NOCOUNT ON

 

DECLARE @dagsdato datetime

DECLARE @igårsdato datetime

DECLARE @slutdato datetime

           

SET @dagsdato = Getdate()

SET @dagsdato = CAST(CONVERT(char(10), @dagsdato, 101) AS datetime)

SET @igårsdato = DATEADD(day, -1, @dagsdato)

SET @slutdato  = '20501231'

 

-- Opdatering af data d.v.s. en kode som allerede findes i tabellen med slutdato 31-12-2008 der sættes til igårsdato

-- og senere tilføjelse af ny data med startdato dagsdato og slutdato 31-12-2050.

-- Er startdato lig med dagsdato skyldes opdateringen en genkørsel af ETL hvor dsa imellemtiden har opdateret data,

-- og så opdateres edw og med slutdato 31-12-2050 hvis en udgået alligevel ikke skulle udgå i dsa.

 

UPDATE dbo.EDW_DB_HISTORICAL_BETALINGSSTATUS

SET HistorikSlutdato = @igårsdato

FROM dbo.EDW_DB_HISTORICAL_BETALINGSSTATUS edw

           INNER JOIN dbo.DSA_BETALINGSSTATUS dsa ON dsa.BetalingsstatusKode = edw.BetalingsstatusKode

WHERE edw.HistorikStartdato <> @dagsdato AND edw.HistorikSlutdato = @slutdato AND

             (edw.BetalingsstatusNavn <> dsa.BetalingsstatusNavn OR

              edw.BetalingsstatusKategori <> dsa.BetalingsstatusKategori)

 

UPDATE dbo.EDW_DB_HISTORICAL_BETALINGSSTATUS

SET BetalingsstatusNavn = dsa.BetalingsstatusNavn,

        BetalingsstatusKategori = dsa.BetalingsstatusKategori,

        HistorikSlutdato = @slutdato

FROM dbo.EDW_DB_HISTORICAL_BETALINGSSTATUS edw

           INNER JOIN dbo.DSA_BETALINGSSTATUS dsa ON dsa.BetalingsstatusKode = edw.BetalingsstatusKode

WHERE edw.HistorikStartdato = @dagsdato AND

             (edw.BetalingsstatusNavn <> dsa.BetalingsstatusNavn OR

              edw.BetalingsstatusKategori <> dsa.BetalingsstatusKategori)

 

-- Udgået data d.v.s. en kode som allerede findes i tabellen med slutdato 31-12-2050 der sættes til dagsdato.

 

UPDATE dbo.EDW_DB_HISTORICAL_BETALINGSSTATUS

SET HistorikSlutdato = @dagsdato

FROM dbo.EDW_DB_HISTORICAL_BETALINGSSTATUS edw

WHERE edw.HistorikSlutdato = @slutdato AND

             NOT EXISTS(SELECT 1 FROM dbo.DSA_BETALINGSSTATUS dsa

                                                 WHERE dsa.BetalingsstatusKode = edw.BetalingsstatusKode)

 

-- Indsættelse af ny data d.v.s. en kode som ikke tidligere er anvendt og derfor ikke findes i tabellen,

-- tilføjes med startdato dagsdato og slutdato 31-12-2050.

-- Ellers indsættelse af data som er blevet opdateret i dsa.

-- Eller indsættelse af udgået data der er kommer igen i dsa.

 

INSERT INTO dbo.EDW_DB_HISTORICAL_BETALINGSSTATUS

    (BetalingsstatusKode, BetalingsstatusNavn, BetalingsstatusKategori, HistorikStartdato, HistorikSlutdato)

SELECT dsa.BetalingsstatusKode, dsa.BetalingsstatusNavn, dsa.BetalingsstatusKategori, @dagsdato, @slutdato

FROM dbo.DSA_BETALINGSSTATUS dsa

WHERE dsa.BetalingsstatusKode IS NOT NULL AND dsa.BetalingsstatusNavn IS NOT NULL AND

             dsa.BetalingsstatusKategori IS NOT NULL AND

             NOT EXISTS(SELECT 1 FROM dbo.EDW_DB_HISTORICAL_BETALINGSSTATUS edw

                                                 WHERE edw.BetalingsstatusKode = dsa.BetalingsstatusKode AND

                                                              (edw.HistorikStartdato = @dagsdato OR edw.HistorikSlutdato = @slutdato))

ORDER BY dsa.BetalingsstatusKode

 

END