ניהול שינויים במימד באמצעות T-SQL

By | 30 ביוני 2010

זהו המאמר הרביעי בנושא ניהולי שינויים במימד (Slowly Changing Dimension) בסיביבת SQL Server 2008. שלושת המאמרים הקודמים היו:

  1. הפקודה Merge. למי שלא מכיר פקודה זו אשר הוצגה לראשונה ב- SQL Server 2008, מומלץ לקרוא מאמר זה תחילה.

לאחר ההקדמה מרובת השלבים, הגיע הזמן להתחיל. תחילה נקים (שוב) את טבלת המימד שלנו וטבלת נתוני המקור. שתי הטבלאות ידמו מימד קיים ונתונים חדשים אשר נטענים ממערכת המקור.

הערה: נתוני הטבלאות הוכנסו באותיות לטיניות כיוון שלא מצאתי דרך טובה לבצע העתק-הדבק (Copy Paste) מה- Manager Studio אל Word לאותיות עבריות. אם מישהו מכיר דרך לבצע העתקה הדבקה כולל שמירה על הצבעים, אשמח אם יגיב למאמר זה.

— בניה של טבלה למימד לקוחות

IF
EXISTS
(SELECT * FROM

sys.objects
WHERE
object_id =

OBJECT_ID(N'[dbo].[dmnLakoah]')
AND
type
in
(N'U'))

DROP
TABLE [dbo].[dmnLakoah]

CREATE
TABLE [dbo].[dmnLakoah](

[Gkakoah] [int] IDENTITY(1,1)
NOT
NULL,

[MisZehut] [int] NOT
NULL,

[ShemLakoah] [varchar](20)
NOT
NULL,

[YeshuvMegurim] [varchar](20)
NOT
NULL,

[RamatSahar] [char](1)
NOT
NULL,

[DerugAshray] [char](2)
NOT
NULL,

[PailLoPail] [bit] NOT
NULL,

[TaarichPtiha] [date] NOT
NULL,

[TaarichSgira] [date] NOT
NULL,

[SCDLog] varchar(200)

)
ON [PRIMARY]

— הכנסת מספר רשומות למימד הלקוחות

INSERT
INTO [dbo].[dmnLakoah]


([MisZehut]


,[ShemLakoah]


,[YeshuvMegurim]


,[RamatSahar]


,[DerugAshray]


,[PailLoPail]


,[TaarichPtiha]


,[TaarichSgira])


VALUES


(279754


,'Moshe Cohen'


,'Afula'


,'X'


,'AA'


,1


,'2010-5-30'


,'9999-12-31')


,


(234875


,'Yuval Levy'


,'Tel Aviv'


,'Y'


,'B'


,1


,'2010-5-30'


,'9999-12-31')


,


(5498124


,'Yonat Shimon'


,'Haifa'


,'Z'


,'A'


,1


,'2010-5-30'


,'9999-12-31')


,


(948102


,'Dalit Yosef'


,'Afula'


,'X'


,'AA'


,1


,'2010-5-30'


,'9999-12-31')

————————————————-

— בניה של טבלת נתוני הלקוח ממערכת המקור

IF
EXISTS
(SELECT * FROM

sys.objects
WHERE
object_id =

OBJECT_ID(N'[dbo].[Ods_Lakoah]')
AND
type
in
(N'U'))

DROP
TABLE [dbo].Ods_Lakoah

CREATE
TABLE [dbo].Ods_Lakoah(

[MisZehut] [int] NOT
NULL,

[ShemLakoah] [varchar](20)
NOT
NULL,

[YeshuvMegurim] [varchar](20)
NOT
NULL,

[RamatSahar] [char](1)
NOT
NULL,

[DerugAshray] [char](2)
NOT
NULL,

)


ON [PRIMARY]


———————————–


— הכנסת נתוני הלקוח אל הטבלה החדשה

INSERT
INTO [dbo].Ods_Lakoah


([MisZehut]


,[ShemLakoah]


,[YeshuvMegurim]


,[RamatSahar]


,[DerugAshray]


)


VALUES


(279754


,'Moshe Cohen'


,'Afula'


,'X'


,'AA')


,


(234875


,'Yuval Levy'


,'Tel Aviv'


,'Y'


,'B')


,


(5498124


,'Yonat Shimon'


,'Haifa'


,'Z'


,'A')


,


(948102


,'Dalit Yosef'


,'Nazeret'


,'Y'


,'B+'),


(1234


,
'Yoel Moshe'


,
'Tel Aviv'


,
'Z'


,'C')


,


(934134,


'Yaakov Slomon'


,
'Raanana'


,
'Y'


,
'A');

עתה נגיע לעיקר, ניהול השינויים במימד על בסיס הרשומות הקיימות והרשומות החדשות (אשר נמצאות בטבלה השניה: Ods_Lakoah. את ניהול השינויים יש צורך לבצע בשני שלבים:

  1. שלב ראשון: ביצוע עדכון לרשומות אשר עודכנו ואין צורך לבצע שמירה של הסטוריית השינויים – Slowly Changing Dimension Type 1. בדוגמה שלנו, השדה שם לקוח יעודכן ללא שמירת הסטוריה.
  2. שלב שני: שינוי נתונים להם מבצעים שמירת ההסטוריה. שלב זה מבוצע באמצעות הוספה של רשומות חדשות עבור הרשומות שעודכן בהן שדה כלשהו – Slowly Changing Dimension Type 2.

שלב ראשון:

— Slowly Changing Dimension Type 1


— Update ShemLakoah


update dmnLakoah


set ShemLakoah=Ods_Lakoah.ShemLakoah


from dmnLakoah inner
join Ods_Lakoah on dmnLakoah.MisZehut=Ods_Lakoah.MisZehut


where dmnLakoah.ShemLakoah <> Ods_Lakoah.ShemLakoah

הערה: חלק ה- Where בפקודת ה- Update נראה מיותר (התוצאה תהיה זהה גם בלעדיו), ברם, במידה וטבלת המימד מכילה רשומות רבות, עדיף לכלול תנאי אשר נעדכן רק את הרשומות הדורשות עדכון וכך להקטין כתיבת ללוג ולשפר ביצועים. במידה וישנם מספר שדות אשר דורשים עדכון ללא שמירת היסטוריה, נשתמש ב- OR בין תנאיי אי שוויון על כל השדות אשר מעודכנים באמצעות פקודה זו.

שלב שני (לבסוף הגענו לעיקר): שדות עיר מגורים, רמת הכנסה ודירוג אשראי יעודכנו תוך שמירה על כל ההיסטוריה. כלומר, בכל עדכון של רמת שכר, דירוג אשראי או עיר מגורים תיווצר רשומת לקוח חדשה במימד.

— Update with History


/*1*/insert
into dmnLakoah


/*2*/
( MisZehut,ShemLakoah,YeshuvMegurim,RamatSahar,DerugAshray,PailLoPail,TaarichPtiha,TaarichSgira,SCDLog)

/*3*/
select MisZehut,ShemLakoah,YeshuvMegurim,RamatSahar,DerugAshray,PailLoPail,TaarichPtiha,TaarichSgira,SCDLog

/*4*/
from

(

/*5*/
merge dmnLakoah as Matara

/*6*/ using

/*7*/ (select

MisZehut

, ShemLakoah

, YeshuvMegurim

, RamatSahar

, DerugAshray

/*8*/ from Ods_Lakoah)
as Makor

/*9*/ on matara.MisZehut = makor.MisZehut

/*10*/ when
not
matched
then
— This is new Customer

/*11*/ insert     ( MisZehut,ShemLakoah,YeshuvMegurim,RamatSahar,DerugAshray,PailLoPail,TaarichPtiha,TaarichSgira,SCDLog)

/*12*/ values

/*13*/ (

/*14*/ makor.MisZehut

, makor.ShemLakoah

, makor.YeshuvMegurim

, makor.RamatSahar

, makor.DerugAshray

/*15*/ , 1 — PailLoPail

/*16*/ ,
getdate()-1 — TaarichPtiha

/*17*/ ,
'9999-12-31'
— TaarichSgira

/*18*/ ,
'New '+convert(char(10),getdate()-1,103)
— SCDLog

)

/*19*/ when
matched
and PailLoPail=1

/*20*/ and

(Makor.YeshuvMegurim <> Matara.YeshuvMegurim

OR makor.RamatSahar <> Matara.RamatSahar

OR Makor.DerugAshray <> Matara.DerugAshray)

/*21*/ then
update

set

/*22*/ matara.PailLoPail = 0

, matara.TaarichSgira =
getdate()-1

, matara.SCDLog =
isnull(Matara.SCDLog,")
+ 'SC2 '+convert(char(10),getdate()-1, 103)

/*23*/ output
$Action
as Peula,

makor.MisZehut

, makor.ShemLakoah

, makor.YeshuvMegurim

, makor.RamatSahar

, makor.DerugAshray

/*24*/ , 1 as PailLoPail

/*25*/ ,
getdate()
as TaarichPtiha

/*26*/ ,
'9999-12-31'
as TaarichSgira

/*27*/ ,
'SC2 New Rec '+convert(char(10),getdate(),103)as SCDLog

/*28*/ ) MergeOut

/*29*/ where MergeOut.Peula='UPDATE';

הורדה של כל דוגמאות הקוד

עקב מורכבות העלילה בתוך פקודה זו, הוספתי מספרי שורות אשר יעזרו לי לתאר כל אחד מחלקי הפקודה:

  1. פקודת Insert אשר מכניסה אל תוך טבלת מימד הלקוחות את הפלט (Output) של פקודת ה- Merge. פקודה זו למעשה מקבלת את כל הרשומות ששונו ומכניסה את הרשומות עם הערכים העדכניים.
  2. רשימת השדות עבור פקודת ה- Insert לעיל.
  3. ,4 שאילתא אשר ה- From שלה הינו הפלט של פקודת ה- Merge עם תנאי שרק רשומות שעודכנו (סינון החוצה של רשומות חדשות לגמרי) יכנסו אל ה- Insert.
  1. תחילת פקודת ה- Merge. הגדרת טבלת המימד – טבלת המטרה.
  2. , 7,8 הגדרת מקור הנתונים. אפשר להשתמש מחיבור של מספר טבלאות. כל מה שמתקבל ב- Select אפשרי.
  3. תנאי החיבור בין טבלת המטרה לטבלת מקור הנתונים – המפתח העסקי (Busyness Key) – מספר הזהות של הלקוח. על פי תנאי זה מזהה השרת האם להוסיף רשומה חדשה או לעדכן רשומה קיימת.
  4. אם מספר הזהות מטבלת הנתונים העדכניים אינו בטבלת המימד, אזי מדובר בלקוח חדש.
  5. -14 . הכנסת רשומת הלקוח החדשה.
  6. הרשומה החדשה נכנסת עם דגל רשומה פעילה = 1.
  7. תאריך פתיחת הרשומה הינו יום אתמול (בהנחה שטעינת הנתונים מבוצעת לאחר חצות יום העסקים).
  8. תאריך סגירת הרשומה אינו ידוע עדיין כיוון שמדובר ברשומה פתוחה. חשוב להכניס תאריך ולא להשאיר NULL כדי שאפשר יהיה לבצע שאילתות עם תנאי Between על שני התאריכים האלו.
  9. הכנסת ערך ללוג השינויים של המימד. לא חיוני אך מנסיון, עשוי לסייע להבין בעתיד את רשומות המימד.
  10. כאשר מספר הזהות שהגיע מטבלת הנתונים החדשים (Ods_Lakoah) כבר קיים בטבלת המימד, והרשומה פעילה
  11. בודקים האם ישנו שינוי באחד השדות עליהם מבצעים עדכון עם שמירת הסטוריה. כפי שנרשם למעלה, תנאי האי שיוויון האלו חשובים על מנת לא לעדכן רשומות מיותרות דבר שיפגע בביצועים.
  12. עדכון דגל פעיל / לא פעיל למצב לא פעיל ברשומה הקיימת (זו אשר מסתבר שיש נתונים עדכניים לגביה).
  13. עדכון תאריך סגירה לרשומה הקיימת. חשוב שלא תהיה חפיפה בין תאריך הסגירה (Getdate-1) לבין תאריך הפתיחה (GetDate) על מנת שבמידה ונבצע שאילתת Between, כדי לדעת את מאפייני הלקוח בתקופה מסויימת, לא נקבל את שתי הרשומות.
  14. הגדרה של פקודת ה- Output אשר קובעת אלו רשומות ייצאו כפלט של פעולת ה- Merge.
  15. הגדרה שהדגל פעיל יורם על מנת שסמן שזו הרשומה הפעילה.
  16. התאריך הנוכחי הינו תאריך פתיחת הרשומה.
  17. התאריך האחרון הקיים הינו תאריך סגירת הרשומה. כלומר, הרשומה פתוחה.
  18. הגדרת לוג השינויים.
  19. מזכיר לנו שפקודת ה- Merge כולה הינה החלק הפנימי של פקודת Insert אשר הוגדרה בשורה הראשונה.
  20. אל תוך ה- Insert ניקח רק רשומות שעודכנו.

תקציר הפעולות שביצענו:

  1. הגדרת מסגרת של Insert.
  2. פעולת Merge כפעולה פנימית ל- Insert. פקודה זו תכניס רשומות של לקוח חדש אל טבלת הלקוחות, תעדכן שדות ברשומות לקוח קיים אשר שונו והוגדרו לשמירת היסטוריה. כל רשומה שעוכנה הופכת לפגת תוקף.
  3. פלט ה- Merge הינו אוסף הרשומות שעודכנו (והפכו לפגות תוקף), רשומות אלו יוכנסו למימד מחדש (באמצעות ה- Insert) מסעיף א' אך עם הערכים החדשים שלהן ועם תוקף ותאריך סגירה אשר יעידו על תקפותן הנוכחית.

ניתן לבדוק את פעולתינו באמצעות השאילתא:

select
*
from dmnLakoah

order
by MisZehut

אשר תניב:

SCD by Merge

שימו לב בבקשה ללקוח Yuval Levi. בתחילה שונה שמו מ- Levy ל- Levi אך זהו שינוי שלא שומר היסטוריה ולכן אין לו עדות. לאחר מכן שונתה רמת השכר שלו מ0 X ל- Y ודירוג האשראי שלו שונה מ- A ל- B. כתוצאה מכך הפכה הרשומה שנוצרה ב- 30.5.2010 לפגת תוקף ב- 26.6.2010 וב- 27.6.2010 נוצרה רשומה חדשה עבור לקוח זה עם אותו שם ומפתח עסקי (מספר זהות) אך עם פרטים חדשים.

בעת הכנסת נתונים אל ה- Fact Table יבוצע Join אל טבלת המימד אך עם הגדרה של דגל PailLoPail=1 כדי להכניס את מפתח הלקוח (GkLakoah) המתאים.

בעת שאילתא על נתונים הסטוריים ב- Fact Table ילקח עבור אדון לוי ה- GkLakoah הישן שלו עם המאפיינים המתאימים לאותו תאריך.

תודה לאלה משיח
על שדחפה אותי (בעדינות) לרשום על נושא זה בעת מפגש שהיה לנו בכנס Data Platform. באותו כנס ראינה אלה את דונאלד פארמר על עתיד הבינה העסקית אליבה דמיקרוסופט. מומלץ.

Share

One thought on “ניהול שינויים במימד באמצעות T-SQL

  1. Pingback: השלמה למאמר על ניהול שינויים במימד | BI and More

כתיבת תגובה

האימייל לא יוצג באתר.