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

29/01/2010

איתור שגיאות בטבלאות Slowly Changing Dimension

Filed under: Uncategorized — תגיות: , , — גרי רשף @ 20:05

טבלאות SCD מקובלות במחסני נתונים, וההבדל בינן לבין טבלאות מימד רגילות (טבלאות Dim שכוללות את התיאור ופרטים נוספים לכל קוד בטבלת ה-Fact) הוא שלכל קוד יכולות להיות בהן מספר רשומות לפי טווחי תאריכים.

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

שיטה זו חשובה כשרוצים לדעת מה היה תפקידו של עובד מספר 1234 בתאריך 01/01/2001 – תפקיד שלבטח שונה מזה של היום. כמובן שלגבי מספר הטלפון באותה תקופה המידע קצת פחות חשוב.. כשנשלוף נתונים מטבלת ה-Fact נבצע Join בינה לבין טבלת ה-SCD לפי ה-ID (ה-ID ב- Fact שווה ל-ID בטבלת ה-SCD) ולפי התאריך (התאריך ב-Fact נמצא בין תאריך ההתחלה ותאריך הסיום בטבלת ה-SCD):

Select *

From T_Fact F

Inner Join T_SCD SCD

    On F.ID=SCD.ID

    And F.Date Between SCD.StartDate And SCD.EndDate

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

תקלות יחודיות לטבלאות SCD כוללות תקלות פנימיות ברשומה – כאשר תאריך ההתחלה אינו לפני תאריך הסיום,

ותקלות צולבות בין רשומות כשנוצרים רווחים או תחומי חפיפה בין רשומות עוקבות.

ניצור טבלת SCD ונכניס נתונים – תקינים ושגויים:

Use TempDB

Go

Create Table T_SCD(ID Int, StartDate DateTime, EndDate DateTime)

Go

--1 תקין

Insert Into T_SCD Select 1 ID, '20090101' StartDate, '20090731' EndDate

Insert Into T_SCD Select 1 ID, '20090801' StartDate, '20090815' EndDate

Insert Into T_SCD Select 1 ID, '20090816' StartDate, '20091031' EndDate

Insert Into T_SCD Select 1 ID, '20091101' StartDate, '20091231' EndDate

--2 רשומה אחת "חותכת" את האחרת

Insert Into T_SCD Select 2 ID, '20090101' StartDate, '20090731' EndDate

Insert Into T_SCD Select 2 ID, '20090801' StartDate, '20090815' EndDate

Insert Into T_SCD Select 2 ID, '20090810' StartDate, '20091031' EndDate

Insert Into T_SCD Select 2 ID, '20091101' StartDate, '20091231' EndDate

--3 רשומה אחת "בולעת" את האחרת

Insert Into T_SCD Select 3 ID, '20090101' StartDate, '20090815' EndDate

Insert Into T_SCD Select 3 ID, '20090801' StartDate, '20090810' EndDate

Insert Into T_SCD Select 3 ID, '20090816' StartDate, '20091031' EndDate

Insert Into T_SCD Select 3 ID, '20091101' StartDate, '20091231' EndDate

--4 רשומות חופפות

Insert Into T_SCD Select 4 ID, '20090101' StartDate, '20090815' EndDate

Insert Into T_SCD Select 4 ID, '20090101' StartDate, '20090815' EndDate

Insert Into T_SCD Select 4 ID, '20090816' StartDate, '20091031' EndDate

Insert Into T_SCD Select 4 ID, '20091101' StartDate, '20091231' EndDate

--5 יש "חור" באמצע

Insert Into T_SCD Select 5 ID, '20090101' StartDate, '20090731' EndDate

Insert Into T_SCD Select 5 ID, '20090801' StartDate, '20090815' EndDate

Insert Into T_SCD Select 5 ID, '20090816' StartDate, '20091031' EndDate

Insert Into T_SCD Select 5 ID, '20091201' StartDate, '20091231' EndDate

--6 תאריך ההתחלה אחרי תאריך הסיום

Insert Into T_SCD Select 6 ID, '20090131' StartDate, '20090121' EndDate

תקלות פנימיות- החיפוש די פשוט:

Select *From T_SCDWhere StartDate>=EndDate

תקלות צולבות בין רשומות:

With T As

(Select ROW_NUMBER() Over (Partition By Id Order By StartDate) Num,

        *

From T_SCD)

Select *

From T T1

Inner Join T T2

    On T1.ID=T2.ID

    And T1.Num+1=T2.Num

    And T1.EndDate+1<>T2.StartDate

ה-CTE (כלומר- Common Table Expression) יוצר שאילתה פנימית T המתבססת על טבלת ה-SCD ומוסיף לה עמודה מחושבת Num הממספרת בסדר עולה של תאריכי התחלה את הרשומות של כל ID.

לאחר מכן מתבצע Join בין T לעצמה כך שלכל רשומה ב-T הראשון מותאמת הרשומה העוקבת ב-T השני, והן מסוננות כך שנשארות הרשומות השגויות בהן תאריך הסיום של האחת אינו יום לפני תאריך ההתחלה של העוקבת.

הפלט מזהה נכון את כל השגיאות הצולבות, כאשר את התקלה ב- ID=3 הוא מזהה פעמיים- פעם בגלל אי ההתאמה בין השורה הראשונה לשניה, ופעם בגלל אי ההתאמה בין השורה השניה לשלישית.

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

7 תגובות »

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

    פינגבאק של שימוש בפקודת Merge לעדכון טבלת Slowly Changing Dimension - גרי רשף — 19/01/2012 @ 19:07

  2. […] (ובצדק!). השוותי את התוצאות לאלו שהוחזרו בשיטת Self Join שהצגתי לפני כשנתיים – והן זהות. מה לגבי היעילות? לשליפת ה-Self Join מיותר […]

    פינגבאק של בדיקת טבלת Slowly Changing Dimension בעזרת פונקציית Lag - גרי רשף — 13/01/2012 @ 22:59

  3. […] את התוצאות לאלו שהוחזרו בשיטת Self Join שהצגתי לפני כשנתיים – והן […]

    פינגבאק של בדיקת טבלת Slowly Changing Dimension בעזרת פונקציית Lag « הבלוג של גרי רשף — 13/01/2012 @ 22:55

  4. […] (ובצדק!). השוותי את התוצאות לאלו שהוחזרו בשיטת Self Join שהצגתי לפני כשנתיים – והן […]

    פינגבאק של בדיקת טבלת Slowly Changing Dimension בעזרת פונקציית Lag « הבלוג של גרי רשף — 13/01/2012 @ 22:54

  5. […] כשנתיים כתבתי פוסט על SCD, הסברתי בקצרה מה זה, ונתתי והדגמתי כיצד לבדוק את […]

    פינגבאק של יעול שאילתת Union עצמי ודוגמה ליישום בבדיקת טבלת Slowly Changing Dimension - גרי רשף — 08/01/2012 @ 20:28

  6. […] כשנתיים כתבתי פוסט על SCD, הסברתי בקצרה מה זה, ונתתי והדגמתי כיצד לבדוק את […]

    פינגבאק של יעול שאילתת Union עצמי ודוגמה ליישום בבדיקת טבלת Slowly Changing Dimension « הבלוג של גרי רשף — 08/01/2012 @ 20:18

  7. […] — תגים:BI, Merge, Slowly Changing Dimension, SQL Server — גרי רשף @ 10:46 באחד הפוסטים הקודמים שלי עסקתי ב-Slowly Changing Dimension (להלן SCD) והקדמתי הסבר קצר מה זה, […]

    פינגבאק של שימוש בפקודת Merge לעדכון טבלת Slowly Changing Dimension « הבלוג של גרי רשף — 07/06/2010 @ 12:44


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

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