SCD - Slowly Changing Dimension - חלק 2
ALTER TABLE, שלוש עמודות: StartDate ו-EndDate מסוג datetime שמקבלות NULL, ו-IsCurrent מסוג bit.UPDATE על Products שמעדכנת את העמודות שמותר לשנות, רק לרשומות עם EndDate IS NULL.UPDATE שסוגרת את הרשומה הקודמת, ו-Union All שמחזיר את הרשומה כדי להוסיף אותה כחדשה עם StartDate, ואז Insert.פרקי ההרצאה: SCD - Slowly Changing Dimension - חלק 2
תמלול ההרצאה: SCD - Slowly Changing Dimension - חלק 2
הטקסט המלא של הדקות הראשונות בהרצאה «SCD - Slowly Changing Dimension - חלק 2» בקורס הווידאו של PracticU – כתוביות בעברית עם זמן התחלה לכל פסקה. כך תוכלו לקרוא ולחפש את הנושאים לפני שמתחילים ללמוד.
בדוגמה הבאה אנחנו נראה הדגמה של האובייקט Slowly Change Dimension. לצורך כך אני יוצר כאן פקאג' חדש, נשנה לו את השם, Slowly Change Dimension, ומדובר על דמו אחד ואנחנו נשנה אותו בהתאם לכך.
Slowly Change Dimension, האובייקט הזה נמצא אצלנו ברגע שאנחנו פותחים את Data Flow Task בהעברת נתונים מ-Source ל-Destination, יש לנו אפשרות לעקוב ולבצע ניטור אחר רשומות שהשתנו ולהתנהג בהתאם על פי סוגי ה-Slowly Change Dimension השונים כפי שראינו בשיעור הקודם.
נזרוק לכאן את Data Flow Task על מנת להדגים לעצמנו איזשהו דמו של Slowly Change Dimension. דאבל קליק על מנת להיכנס בפנים. ברגע שאנחנו נכנסים כאן לתוך ה-Slowly Change Dimension, בתוך ה-data flow אנחנו ניקח איזשהו source שממנו אנחנו נעתיק את הנתונים. ה-source יכול להיות אקסל פייל, פלאט פייל, טבלה, משהו מתוך ה-database ותכף נראה איך אנחנו
גם מקנפגים את ה-destination בהתאם. Slowly Change Dimension, לצורך הדוגמה שלנו אנחנו ניצור כאן חיבור לתוך flatfile ולכן אני ניגש, בוחר את הקובץ שאותו אנחנו רוצים למפות, עובר כאן ל-all files, אחר כך את הקובץ שאנחנו מכירים product preview מראה לנו את כל המידע עצמו.
אנחנו שולפים את כל הנתונים מתוך קובץ flat file, קובץ CSV, ואותם אנחנו רוצים לייבא. שנייה לפני שאנחנו מייבאים אותם, כאן אנחנו לקחנו את product CSV. לפני שאנחנו מייבאים אותם אני רוצה להדגים את האובייקט עצמו שהוא נמצא כאן אצלי בתוך ה-common: slowly change dimension.
סלואולי צ'נג' דיימנשן. נחזור רגע על שקופית שראינו אותה כבר בשיעור הקודם. בסלואולי צ'נג' דיימנשן אנחנו יכולים לדבר על מספר סוגים, כאשר טייפ 0 מדובר על מצב שבו אנחנו לא רגישים לשינויים ואנחנו לא עושים איתם כלום.
זה מה שפחות כרגע מעניין אותי. מה שאותי מעניין זה מצב שבו אנחנו עושים אובר-רייד. שני סוגים כרגע מעניינים אותנו, טייפ 1 וטייפ 2, בהמשך אנחנו נראה גם שימושים של טייפ 3, ב-type 1 אנחנו עושים Override, ב-type 2 אנחנו יוצרים Additional Records, רשומה חדשה שבעצם מכילה את הנתונים החדשים כפי שראינו
בשיעור הקודם. אנחנו יכולים ב-type 1 לדרוס ולא לשמור היסטוריה, ואילו ב-type 2 אנחנו פשוט יוצרים שורה חדשה תוך כדי זה שאנחנו מוסיפים תקורה של שלוש עמודות נוספות, כפי שאתם רואים אותן כאן, קארנט סטארט ואנד כאשר שלושת העמודות הללו מציינות לי מי השורה הנוכחית מי השורה כרגע הנכונה כאשר קארנט זה true או false זה yes
ו-no ואילו שתי העמודות האחרות סטארט ואנד נותנות לי חיווי אודות התאריך נכונות של כל רישומה. נחזור לתוכנית שלנו נסגור את המצגת ולפני שאנחנו מעתיקים את הנתונים לתוך טבלת פרודקס אנחנו צריכים להוסיף איזושהי תקורה למבנה הדאטאבייס.
ובכן טבלת product שלפנינו מכילה כאמור את המבנה הבא SPL product על מנת לקבל את הארכיטקטורה של הטבלה עצמה מה יש בה מה מבנה המטא דאטה שלה מבנה עמודות הם כדלקמן אנחנו רואים שאלו עמודות שהדאטה שמכילה הטבלה אולם אנחנו רוצים להוסיף עמודות נוספות על מנת לתמוך באפשרות של slowly change dimension אנחנו רוצים להוסיף
את העמודות של start end ועמודת current. לתוך טבלת פרודקטס אנחנו נוסיף את העמודות הבאות. סטאר דייט כעמודה שמסוג דייט טיים והיא יכולה לקבל נ"ל.
אלטר טייבל, אז אנחנו נוסיף לתוך טבלת פרודקטס אלטר טייבל, נוסיף את העמודה סטאר דייט שהיא מדייט טייט של דייט טיים ואנחנו נוסיף את העמודה הנוספת שלנו עבור אנד דייט שגם היא תהיה מסוג דייט טיים והיא תקבל עמודת נ"ל.
אנחנו נוסיף עוד עמודה נוספת שהיא תהיה is current. קורנט כאינדיקציה האם מדובר בעמודה הנוכחית שלנו. זו אין צורך שהיא תהיה מסוג של דיי טיים אלא אנחנו נצמן אותה כעמודה של ביט.
אלטר טייבל פרודקט שלוש עמודות אנחנו מוסיפים לטבלת פרודקט נריץ את זה נקבל סקספולי העמודות עצמם מעודכנות ואם נבחן שוב את הטבלה נראה שכרגע לפנינו יש לנו עמודות נוספות סטארט דייט end date ו-ease current, ועכשיו אנחנו ערוכים לעבור ל-SSS ולקנפג את התהליך של ה-slowly
change dimension. כיצד אנחנו עושים זאת? ה-source של הדאטה שלנו הוא מתוך, כפי שאנחנו אמרנו, מתוך הנתונים עצמם שהבנו, מתוך קובץ flat file, קובץ CSV. אנחנו נדרשים, לפני שאנחנו מייבאים את הנתונים, רק לבצע איזושהי המרה על מנת שנוכל להשתמש בהם. מאחר
וכל הנתונים, כשאנחנו מייבאים אותם, כשאני עומד כאן על הקומפוננטה ואני לוחץ show advanced editor על מנת לראות מה הדאטה טייפ של הנתונים שאותם אנחנו מביאים אז כל הנתונים עצמם שימו לב הם מגיעים בפורמט של סטרינג גם הפרודקט איי-די וגם הפרודקט ניים סטרינג וגם הסופלייר איי-די סטרינג קבצי csv יודעים לספק לנו נתונים שהדאטה טייפ שלהם הוא csv בלבד אם אנחנו רוצים לעשות להם המרה
אנחנו נשתמש בדאטה conversion כפי שראינו בסרטונים קודמים Product ID, אנחנו ניצור copy of Product ID, כלומר ניצור עמודה חדשה, וכיוון שאנחנו רוצים ליצור אותה כאינטג'ר, אז הבחירה שלנו כאן תהיה 4 byte signed integer, כמספר אינטג'רי שמכיל 4 בתים. אני מגדיר את
זה לצורך העניין. גם פרודקטים אנחנו צריכים לשנות אותו, מאחר שהוא מגיע לסטרינג, ואילו בטבלת יעד שאליה אנחנו רוצים להכניס אותו, אנחנו מכניסים את זה ל-n ורקר, ל-unicode ורקר, זאת אומרת שאנחנו מכניסים את זה לשתי בתים, תו ולא לבית אחד לא לקוד אסקי אלא ליוניקוד ולכן צריך לעשות את השינוי כאן בדאטה קונברז'ן
ליוניקוד סטרינג. כנ"ל לגבי סופלייר איידי אני מסמן אותו עומד כאן בוחר שוב פעם סופלייר איידי אם נסתכל לאן אנחנו מכניסים אותו גם את סופלייר איידי וגם את קטגורי איידי אנחנו מכניסים לשדה מסוג אינטג'ר ולכן אנחנו נעביר אותו למה שמקביל כאן לאינטג'ר שזה 4 בייט סיינד אינטג'ר אז גם ל-supplier, גם לקטגורי 4-byte-signed-integer,
Quantity Per Units, רק צריך לבדוק מה אנחנו רוצים לעשות כאן. Quantity Per Units זהו שדה מכרוזתי, Unicode, וזה בדיוק מה שאנחנו צריכים לעשות בהמרה שלנו כאן.
אז ב-Quantity Per Units אנחנו נמיר אותו לשדה מסוג Unicode String. נתקדם הלאה, Unit Price זה Money, ועוד שלושת השדות שלאחר מכן, יוניטין סטוק און אורדר וריאורדר לבל הם סמול אינטג'ר מה שאומר שזה לא ארבעה בתים אלא סמול אינט אלא שתי בתים.
אז ראשית נעביר את ה-unit price להיות money וה-unit price שלנו כ-money זה ה-currency זה המקבילה שלו ושלושת העמודות הללו יוניטין סטוק יוניטין אורדר וריאורדר לבל את שלושתם אנחנו מעבירים לא ל-4 בייט סיינט אינטג'ר לא לאינטג'ר לארבעה בתים אלא לשתי בתים, שתי בתים מבחינתנו זה לבחור כאן את השדה שמכיל שתי בתים Ui2,
Ui2 הנה זה 2 byte signed integer אנחנו נעשה את זה בשניהם 2 byte signed integer ו-2 byte signed integer שתי בתים שזה המקבילה של small אינטג'ר נותר לנו דבר אחרון שזה הדיסקונטיניו והדיסקונטיניו הוא מסוג של ביט.
בביט אנחנו יכולים להשתמש בערך אקוויוולנטי שלנו שזה יהיה הבוליין. אז עשינו כאן המראה אז מכאן ואילך אנחנו כבר לא עובדים על הצדות המקוריים שאותם קיבלנו על ה input column אלא אנחנו נעבוד על הoutput alias הכינוי החדש שאותו אנחנו נתנו.
הם אלו השדות שאיתם אנחנו נתקדם קדימה. את כל המידע הזה אנחנו שופכים אותו ל-Slowly Change Dimension. עכשיו אנחנו רוצים להכניס נתונים חדשים לתוך טבלת Products, אחרי שביצענו המרה לנתונים מתוך הקובץ CSV, אבל אנחנו רוצים להיות רגישים ל-Changing.
בואו נראה כיצד זה קורה. דאבל קליק על התהליך עצמו על מנת לקנפג אותו, ונפתח לנו כאן ויזארד. וויזארד שאיתו אנחנו נעבוד. אני מגדיל אותו למסך מלא ובדבר הראשון שבוויזארד שואל אותי אוקיי אני יודע מאיפה אתה מביא אבל אני צריך שתגיד לי לאן אתה מביא וההנחה היא שכשאנחנו מביאים נתונים אנחנו מביאים אותם לתוך דיימנשנס שם אנחנו רוצים לשמור
את הצ'יינג'ינג של הדאטה ולכן הוא שואל אותי מהו הדיימנשן טייבל מהו טבלת היעד שהוא מניח שהיא דיימנשנס אבל זה לאו דווקא מהו טבלת יעד שאליה אתה רוצה ולייבא את הנתונים, במקרה שלנו אנחנו רוצים להביא את זה לתוך טבלת Products, כל מה שנותר לי זה רק לבחור את הטבלה, וממילא הוא מסמן לי את שמות כל העמודות הרלוונטיות באותה טבלה,
Category ID, Discontinue, End Date, Ease Current, Product ID, הוא מסמן לי את שמות כל העמודות, ואני בהתאמה יכול לעשות את המצ'ינג ביניהם. אנחנו לא רוצים לעשות את המצ'ינג לפי השם, כי אנחנו לא רוצים להכניס את Product ID מאחר וראינו שהוא מגיע בפורמט שונה שלא מתאים לטבלת יעד,
אלא אנחנו נעבור שדה שדה וניקח את המקבילו. תחת השלג Category ID ניקח את copy of Category ID, ובמקום discontinue ניקח את copy of discontinue.
End date ו-is current לא הבאנו אותם, אז אין לנו מה לגעת בהם. Product ID, אז אנחנו לוקחים את ה-copy of Product ID.
כנ"ל לגבי Product Name, אנחנו לא ניקח את ה-Product Name עצמו, אלא copy of Product Name. כנ"ל לגבי כל שאר העמודות, Quantity Per Units, Reorder Level, Start Date זו עמודה שאנחנו לא מייבאים אותה, אחת משלושת העמודות תקורה שבהן אנחנו נשתמש על מנת לנטר את התהליך
ולהכניס שינויים בהתאם לשינויים שנעשו. אז עשינו כאן מיפוי, ולמטה צועק לי ה-SSIS: At least one business key needs to be selected.
כדי לזהות רשומה בצורה חד-חד-ערכית אני חייב שתהיה לי עמודה אחת, שלא כל העמודות יהיו מסומנות כ-not-a-key column, אלא אני חייב שאחת מהן תהיה מפתח, כמובן שמי שמפתח אצלנו זה ה-product ID, ורק בו אני הולך ומסמן business scheme.
אז יש לנו מיפוי שלם של כל העמודות, אנחנו גם מצליחים לעשות מיפוי ברמת ה-data type, אם לא היינו עושים את ה-data conversion לא היינו מצליחים לעשות מיפוי בין השדות כלת, בין ה-source לדסטיליישן.
מאחר ועשינו גם את ההמרה הנכונה, שייכנו כאן את השדות בצורה נכונה, יש לנו מפתח, אנחנו יכולים להתקדם על ידי כפתור נקסט בפינה הימנית התחתונה לעבר המסך הבא.
המסך הבא מחזיר אותנו, שימו לב כאן, טייפ 1, טייפ 2. מחזיר אותנו למצגת שבה ראינו מה ההבדלים בין טייפ 1 לטייפ 2. היכולת שלנו או לעשות אובריי או לעשות Additional New Records, ליצור רשומה חדשה.
זה בדיוק מה שאני נדרש לבצע כאן. אני ממליץ לשבת ולקרוא את שלושת האפשרויות הרשומות כאן מצד שמאל. ו-Fixed Attributes זה היכולת שלנו בעצם למנוע העתקה.
כלומר, לא לאפשר שתהיה העתקה. עבור כל שדה, שימו לב אני לוחץ כאן, נפתחת לי רשומה חדשה עם השדה החדש. עבור כל שדה אני יכול לבחור אחת ממי האפשרויות הבאות.
עבור Category ID אני יכול להחליט או שזה Fixed, או שזה Changing, או שזה Historical. בהתאמה, או שזה טייפ אפס, לא מאפשרים שינוי.
Select this type, קראו איתי כאן בצד השמאל להסבר המופלא, שהוא בדיוק מחזיר אותנו להסבר שהיינו במצגת. When value column should not change, בדיוק כמו שראינו במצגת, כאשר הערך איננו יכול להשתנות, changing is treated as error.
כלומר, אנחנו מקבלים כאן שגיאה, זה בדיוק המצב שבו אנחנו לא מאפשרים שינוי. האם אנחנו רוצים שכל העמודות שלנו יהיו Fixed Attributes?
יכול להיות שכן. בדוגמה שלנו אנחנו נבחר שלא כך, אלא אנחנו רוצים לשנות אותם. לאן אנחנו רוצים לשנות אותם? אז יש לנו שתי אפשרויות: או פשוט לדרוס את השינוי, Changing, או לשמור היסטוריה של הערכים.
שתי האפשרויות הללו נתמכות על ידי זה שאנחנו בוחרים, Changing. אנחנו רוצים לשנות, כל אחת מהאפשרויות הללו אנחנו רוצים לשנות, נבחר אותם אנחנו רק אחרי זה נרצה לבחור לעצמנו שתי עמודות שלא רק שאנחנו רוצים בהחלט לאפשר לשנות אותם אלא אנחנו רוצים לשמור את הערך ההיסטורי שהשתנה במרוצת הזמן לאורך הזמן עצמו
לדוגמה product name אולי נכון לי גם לשמור את שם המוצר לאורך ההיסטוריה היסטוריקל אטריבוט כלומר לא רק לשמור לדרוס ולאפשר שינוי כפי שמאפשר לנו טייפ אחד אלא לשמור היסטוריה של השינויים ועל מנת לדעת לחזור בכל רגע נתון לכל שלב בהתפלגות ההיסטורית לדעת מה היה שם המוצר ואנחנו נבחר גם את
unit price לצורך כך על מנת לדעת מה היה המחיר לאורך ההיסטוריה כולה. הנה הרי לפנינו שאנחנו בתהליך שבו יצרנו עומק תלת מימד לטבלה.
לא טבלה דו-ממדית של שורות ועמודות, יש גם עומק היסטורי על מנת לראות את השתנות הערכים בהתאם להתפלגות הזמן. נתקדם למסך הבא על מנת לקנפג כל אחת מהאפשרויות הבאות.
רק לצורך הדוגמה, אני אקח כאן אחת העמודות, את העמודת Uniting