DW opdatering

af Joakim Dalby

 

Indledning

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 dimension i DW baseret på en historisk opsamling i EDW, idet dimensionen enten kan indholde de aktuelle dimensionsværdier eller også de historiske dimensionsværdier.

 

EDW

EDW har følgende tabel med historik over ændring af betalingsstatus navn og kategori placering: 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

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-2007

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

11

N

Normal

Dårlige betalere

01-01-2008

31-12-2050

 

Dimension med aktuelle dimensionsværdier

Der medtages de BetalingsstatusKoder fra EDW som sidst er anvendt, d.v.s. har hø­je­ste HistorikSlutdato, samtidig med at kategorierne bliver oversat til en talværdi, fordi hierar­ki­er i en olap dimension bør anvende numre fremfor tekst.

 

DW_DIM_BETALINGSSTATUS

 

Betalings-statusDID

Betalingssta-tusKategoriDID

Betalings-statusKategori

Betalings-statusKode

Betalings-statusNavn

1

1

Gode betalere

G

Godt

2

1

Gode betalere

M

Meget godt

3

2

Dårlige betalere

D

Dårlig

4

2

Dårlige betalere

N

Normal

5

2

Dårlige betalere

O

Opfølgning

6

2

Dårlige betalere

R

Restance

7

2

Dårlige betalere

S

Speciel

8

2

Dårlige betalere

T

Tabsgivende

 

BetalingsstatusDID (DW ID) er primærnøglen, et fortløbende nummer, der kan indgå i re­la­tio­ner som fremmednøgle i fact tabeller. Udvælgelsen af data ser via en stored pro­ce­du­re DW_DIM_BETALINGSSTATUS_Insert.

 

Eksempel på brug af dimensionen i en olap kube, hvor koden N er placeret under ka­te­go­rien Dårlige betalere, fordi den placering trådte i kraft fra år 2008:

 

Kategori

Betalingsstatus

2006

2007

2008

Gode betalere

G - Godt

100

110

130

 

M - Meget godt

150

170

200

I alt

 

250

280

330

Dårlige betalere

D - Dårlig

50

40

70

 

N - Normal

200

220

200

 

O - Opfølgning

 

60

30

 

R - Restance

 

20

10

 

S – Speciel

3

 

 

 

T – Tabsgivende

2

4

3

I alt

 

255

344

313

Samlet

 

505

624

643

 

Dimension med aktuelle værdier er god, når der ønskes sammenlignet på tværs af årene med de tekster og kategori placeringer der er aktuelt gældende, hvilket betyder, at tidligere års »I alt« kan ændre sig når nye ændringer i kategori placeringer kommer til.

 

Dimension med historiske dimensionsværdier

Der medtages alle betalingsstatus fra EDW inkl. start- og slutdatoerne som skal an­ven­des når fact tabellens indhold skal matches mod den rette dimensionsværdi.

 

DW_DIM_HISTORICAL_BETALINGSSTATUS

 

Betalings-statusDID

Betalingssta-tusKategoriDID

Betalings-statusKategori

Betalings-statusKode

Betalings-statusNavn

Historik-Startdato

Historik-Slutdato

1

1

Gode betalere

G

God

07-02-2006

21-11-2007

2

1

Gode betalere

G

Godt

22-11-2007

31-12-2050

3

1

Gode betalere

M

Meget godt

07-02-2006

31-12-2050

4

1

Gode betalere

N

Normal

07-02-2006

31-12-2007

5

2

Dårlige betalere

D

Dårlig

07-02-2006

31-12-2050

6

2

Dårlige betalere

N

Normal

01-01-2008

31-12-2050

7

2

Dårlige betalere

O

Opfølgning

22-11-2007

31-12-2050

8

2

Dårlige betalere

R

Restance

22-11-2007

31-12-2050

9

2

Dårlige betalere

S

Speciel

07-02-2006

22-11-2007

10

2

Dårlige betalere

T

Meget dårlig

07-02-2006

21-11-2007

11

2

Dårlige betalere

T

Tabsgivende

22-11-2007

31-12-2050

 

BetalingsstatusDID (DW ID) er primærnøglen, et fortløbende nummer, der kan indgå i re­la­tio­ner som fremmednøgle i fact tabeller. Udvælgelsen af data ser via en stored procdure DW_DIM_HISTORICAL_BETALINGSSTATUS_Insert.

 

Eksempel på brug af dimensionen i en olap kube, hvor koden N nu er placeret under to ka­te­gorier og alle teksterne vises når de har været ændret over tid:

 

Kategori

Betalingsstatus

2006

2007

2008

Gode betalere

G - God

100

110

 

 

G - Godt

 

 

130

 

M - Meget godt

150

170

200

 

N - Normal

200

220

 

I alt

 

450

500

330

Dårlige betalere

D - Dårlig

50

40

70

 

N - Normal

 

 

200

 

O - Opfølgning

 

60

30

 

R - Restance

 

20

10

 

S - Speciel

3

 

 

 

T - Meget dårlig

2

4

 

 

T - Tabsgivende

 

 

3

I alt

 

55

124

313

Samlet

 

505

624

643

 

Dimension med historiske værdier er god, når der ønskes vist et år ad gangen i en rap­port, fordi man får vist det på­gæl­den­de års tekster og kategori placeringer, mens over fle­re år er det svært at sammenligne på tværs af årene.

 

 

Stored procedure der ajourfører DW

 

CREATE PROCEDURE  [dbo].[DW_DIM_BETALINGSSTATUS_Insert]

AS

BEGIN

SET NOCOUNT ON

 

DELETE FROM dbo.DW_DIM_BETALINGSSTATUS

DBCC CHECKIDENT (DW_DIM_BETALINGSSTATUS, RESEED, 0)

 

INSERT INTO dbo.DW_DIM_BETALINGSSTATUS

SELECT dw.BetalingsstatusKategoriDID, dw.BetalingsstatusKategori,

              edw.BetalingsstatusKode, edw.BetalingsstatusNavn

FROM   dbo.EDW_DB_HISTORICAL_BETALINGSSTATUS edw

            INNER JOIN dbo.DW_DB_BETALINGSSTATUSKATEGORI dw

              ON dw.BetalingsstatusKategori = edw.BetalingsstatusKategori

            INNER JOIN

                        (SELECT BetalingsstatusKode, MAX(HistorikSlutdato) AS HistorikSlutdato

                         FROM   dbo.EDW_DB_HISTORICAL_BETALINGSSTATUS

                         GROUP BY BetalingsstatusKode) AS maks

              ON maks.BetalingsstatusKode = edw.BetalingsstatusKode AND maks.HistorikSlutdato = edw.HistorikSlutdato

ORDER BY dw.BetalingsstatusKategoriDID, dw.BetalingsstatusKategori, edw.BetalingsstatusKode

END

 

 

CREATE PROCEDURE  [dbo].[DW_DIM_HISTORICAL_BETALINGSSTATUS_Insert]

AS

BEGIN

SET NOCOUNT ON

 

DELETE FROM dbo.DW_DIM_HISTORICAL_BETALINGSSTATUS

DBCC CHECKIDENT (DW_DIM_HISTORICAL_BETALINGSSTATUS, RESEED, 0)

 

INSERT INTO dbo.DW_DIM_HISTORICAL_BETALINGSSTATUS

SELECT dw.BetalingsstatusKategoriDID, dw.BetalingsstatusKategori, edw.BetalingsstatusKode,

              edw.BetalingsstatusNavn, edw.HistorikStartdato, edw.HistorikSlutdato

FROM   dbo.EDW_DB_HISTORICAL_BETALINGSSTATUS edw

            INNER JOIN dbo.DW_DB_BETALINGSSTATUSKATEGORI dw

              ON dw.BetalingsstatusKategori = edw.BetalingsstatusKategori

ORDER BY dw.BetalingsstatusKategoriDID, dw.BetalingsstatusKategori, edw.BetalingsstatusKode,

                  edw.HistorikStartdato

END

 

 

Ovenstående tog udgangspunkt i, at dimensionernes reloades ved hvert ETL forløb.

 

Nedenstående viser stored procedures til dannelse af fact tabellens indhold indlæst fra EDW via en TMP tabel, hvor dimensionernes DID bliver udfyldt. Betalingsstatus dimensionen er medtaget både i aktuel og historisk version, d.v.s. begge dimensioner er også implementeret i olap kuben, så kan brugerne anvende dem efter behov.

 

 

CREATE PROCEDURE  [dbo].[DW_TMP_BETALER_Insert]

AS

BEGIN

SET NOCOUNT ON

 

TRUNCATE TABLE dbo.DW_TMP_BETALER

 

-- Indsæt factrækker med ukendte Betalingsstatus dimensioner.

 

INSERT INTO dbo.DW_TMP_BETALER

SELECT År, Dato, BetalingsstatusKode, Antal, År AS ÅrDID, 0 AS BetalingsstatusDID, 0 AS BetalingsstatusDHID

FROM   dbo.EDW_DB_BETALER

 

-- Tilføjelse af Betalingsstatus dimension baseret på aktuelle dimensionsværdier.

 

UPDATE dbo.DW_TMP_BETALER

SET BetalingsstatusDID = dw.BetalingsstatusDID

FROM dbo.DW_TMP_BETALER tmp

            INNER JOIN dbo.DW_DIM_BETALINGSSTATUS dw ON dw.BetalingsstatusKode = tmp.BetalingsstatusKode

 

-- Tilføjelse af Betalingsstatus_Historik dimension baseret på historiske dimensionsværdier.

 

UPDATE dbo.DW_TMP_BETALER

SET BetalingsstatusDHID = dw.BetalingsstatusDHID

FROM dbo.DW_TMP_BETALER tmp

            INNER JOIN dbo.DW_DIM_HISTORICAL_BETALINGSSTATUS dw

              ON dw.BetalingsstatusKode = tmp.BetalingsstatusKode

            WHERE tmp.Dato BETWEEN dw.HistorikStartdato AND dw.HistorikSlutdato

END

 

 

CREATE PROCEDURE  [dbo].[DW_FACT_BETALER_Insert]

AS

BEGIN

            SET NOCOUNT ON

 

            TRUNCATE TABLE dbo.DW_FACT_BETALER

 

            INSERT INTO dbo.DW_FACT_BETALER

            SELECT ÅrDID, BetalingsstatusDID, BetalingsstatusDHID, Antal

            FROM   dbo.DW_TMP_BETALER

END