הבלוג של גרי רשף

07/06/2010

שימוש בפקודת Merge לעדכון טבלת Slowly Changing Dimension

Filed under: Uncategorized — תגיות: , , , — גרי רשף @ 10:46

באחד הפוסטים הקודמים שלי עסקתי ב-Slowly Changing Dimension (להלן SCD) והקדמתי הסבר קצר מה זה, וכדאי לחזור עליו אם הנושא אינו מוכר.

פקודת Merge קיימת החל מ-SQL 2008, והיא מאפשר לבצע בפקודה אחת Update+Insert+Delete לטבלת יעד Target על פי טבלת המקור Source. שתי הטבלאות מושוות ואזי אחת מכמה:
Matched – במקרה של התאמה ניתן לעדכן בטבלת היעד את השורות או למחוק אותן, ולשם כך פקודת Merge מאפשרת לטפל בנפרד ב-Matched מותנה וב-Matched לא מותנה (Else).
Not Matched By Source – קיימות ביעד אך לא במקור, וניתן לעדכן או למחוק אותן מהיעד.
Not Matched By Target – קיימות במקור אך לא ביעד, וניתן להוסיף אותן ליעד.
באופן כללי היא נראית כך (הכל מודולרי וניתן לבחור באילו אופציות להשתמש וכיצד):

Merge    T_Target T

Using    T_Source S

        On        T...=S...

When Matched And ...

        Then Delete

When Matched

        Then Update

            Set T...=...,

                T...=...

When Not Matched By Source

        Then Update

            Set T...=...,

                T...=...

When Not Matched By Target

        Then Insert(..,..,..)

            Values(..,..,..)

Output $Action,

       Inserted.*,

       Deleted.*;;

ה-Output בסוף משמש לדיבוג ומאפשר לדעת מה התווסף לטבלת היעד (יש Inserted ואין Deleted),

מה נמחק מטבלת היעד (אין Inserted ויש Deleted),

ומה עודכן בטבלת היעד (יש Inserted ו-Deleted).

במערכת Production ניתן לתת לשדות ה-Output שמות מתאימים ולהפנותן לטבלת מעקב.

טבלאות SCD אינן מתנהלות כולן בצורה אחידה: יש הכוללות שדה המציין איזו רשומה פעילה ואיזו לא ויש שניתן ללמוד זאת רק מהיעדר תאריך סיום או מקיום תאריך סיום עתידי,

יש הכוללות תאריך סיום ויש שלא (ואז יש לחשב אותו לפי תאריך ההתחלה הבא),

יש כאלו שכל שינוי גורם להוספת שורה ויש כאלו שרק שינויים בעמודות מסויימות גורם להוספת שורה ובעמודות אחרות מסתפקים בעדכון השורה הפעילה,

יש טבלאות בהן תאריך הסיום של שורה אחת זהה לתאריך הפתיחה של השורה הבאה ויש כאלו בהן יש הפרש של יום,

מקור העדכונים יכול לבוא בצורות שונות,

וכך הלאה.

בדוגמה הבאה יש טבלת עובדים ומשכורות (כל שינוי במשכורת גורם להוספת שורה),

יש תאריך התחלה ותאריך סיום (שמתעדכן כשמתווספת שורה חדשה),

ויש עמודת המציינת האם השורה פעילה או לא (זה יוצר כפילות מכיוון שניתן היה ללמוד על כך מתאריך הסיום העתידי);

ובנוסף- תאריך פתיחת השורה החדשה יהיה התאריך השוטף, והשורה הקודמת "תיסגר" עם התאריך של היום הקודם..

ניצור טבלת מקור (טבלת השינויים) וטבלת יעד (טבלת ה-SCD), ונזין לתוכן נתונים:

Use tempdb;

Go


If Object_Id('T_SCD') Is Not Null Drop Table T_SCD;

Go

Create Table T_SCD(Shem Varchar(50) Not Null,

                Mascoret Int,

                TaarihHathala DateTime Not Null,

                TaarihSium DateTime,

                Peila Bit);

Go


Alter Table T_SCD

Add Constraint PK_SCD Primary Key Clustered (Shem,TaarihHathala) With FillFactor = 100

Go


Insert Into T_SCD

Select 'Amit',1500,'20100101','29991231',1

Union All

Select 'Boris',1900,'20100101','20100430',0

Union All

Select 'Boris',1800,'20100501','29991231',1

Union All

Select 'Canarit',2000,'20100101','20100228',0

Union All

Select 'Canarit',2100,'20100301','20100531',0

Union All

Select 'Canarit',2300,'20100601','29991231',1;


If Object_Id('T_Idcun') Is Not Null Drop Table T_Idcun;

Go

Create Table T_Idcun(Shem Varchar(50) Primary Key,

                Mascoret Int);

Go


Insert Into T_Idcun

Select 'Boris',1800

Union All

Select 'Canarit',2200

Union All

Select 'Dov',1500

Go

כפי שאפשר לראות- את Amit יש לסגור מבלי לפתוח שורה חדשה מכיוון שהוא אינו מופיע בטבלת העדכונים,

את Boris אין צורך לעדכן מכיוון שהמשכורת שלו לא השתנתה,

ל-Canarit יש לסגור את השורה הפעילה ולפתוח אחת חדשה כי המשכורת שלה השתנתה,

ואת Dov יש להוסיף לטבלה – הוא עובד חדש שלא היה קיים עד כה.

את טבלת המקור עם השינויים אני אצרף לפקודת ה-Merge כשליפה מעט מורכבת שתציג ללא שינוי את Boris, תציג שתי שורות עבור Canarit (עדכון והוספה), ותציג שורה חדשה עבור Dov;

כל זה בעזרת Join בין טבלת המקור לטבלת היעד, ובין שתיהן לטבלה טכנית בת שתי שורות שנועדה להכפיל את מספר השורות בהתאם לצורך.

התאריך השוטף – Taarih@ מוגדר ומחושב לפני ה-Merge:

Declare    @Taarih DateTime;

Set        @Taarih=DateDiff(D,0,GetDate());


Merge    T_SCD T

Using    (Select    N.UI,

                S.Shem,

                Case When UI=1 Then S.Mascoret Else T.Mascoret End Mascoret,

                Case When ((UI=1 And S.Mascoret=T.Mascoret) Or (UI=0 And S.Mascoret<>T.Mascoret)) Then T.TaarihHathala

                    Else @Taarih End TaarihHathala,

                Case When UI=1 Then IsNull(T.TaarihSium,'20991231') Else DateAdd(D,-1,@Taarih) End TaarihSium

        From    T_Idcun S

        Left Join T_SCD T

                On S.Shem=T.Shem

                And T.Peila=1

        Inner Join (Select 0 UI

                    Union All

                    Select 1 UI) N

                On UI=1

                    Or (UI=0 And S.Mascoret<>T.Mascoret)) S

        On        T.Shem=S.Shem

                And T.TaarihHathala=S.TaarihHathala

                And T.Peila=1

When Matched

        Then Update

            Set T.TaarihSium=S.TaarihSium,

                Peila=UI

When Not Matched By Source And Peila=1

        Then Update

            Set T.TaarihSium=DateAdd(D,-1,@Taarih),

                Peila=0

When Not Matched By Target

        Then Insert(Shem,

                Mascoret,

                TaarihHathala,

                TaarihSium,

                Peila)

            Values(Shem,

                Mascoret,

                @Taarih,

                '20991231',

                1)

Output $Action,

       Inserted.*,

       Deleted.*;

כאמור- פקודת ה-Output בסוף היא אופציונלית ומשמשת כאן לדיבוג, וניתן לוותר עליה.

כמובן שמומלץ לשמור בצד את נתוני הטבלה T_SCD לפני השינויים כדי לוודא שהם השתנו כאופן תקין:

Select    *

From    T_SCD

Order By Shem,

        TaarihHathala;

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: