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

טבלאות 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 הוא מזהה פעמיים- פעם בגלל אי ההתאמה בין השורה הראשונה לשניה, ופעם בגלל אי ההתאמה בין השורה השניה לשלישית.