Miky Schreiber's BI Blog

• הגדרת טבלאות היעד
• כל השדות הרצויים הוגדרו
• אורכי שדות תקינים
• הגדרות nullable
• שמות שדות לפי הסטנדרט
• אינדקסים רצויים הוגדרו (עם שמות סטנדרטיים)
• ETL
• בדיקה שה-ETL כולו סיים בהצלחה
• בדיקה שכל השלבים ב-ETL אכן רצו
• בדיקה ב-log-ים שאין הודעות שגיאה
• בדיקה שה-ETL אכן הכניס/עדכן/מחק רשומות בטבלאות היעד
• בדיקה שטבלאות ביניים האמורות להימחק בסיום התהליך אכן ריקות או לא קיימות
• שליפות פרטניות
• שלוף לפי ערך מסוים באינדקס (לדוגמא, לקוח מסוים) ובדוק שכמות הרשומות תואמת לשליפה המקבילה במערכת המקור
• שלוף לפי ערך מסוים באינדקס (לדוגמא, לקוח מסוים) ובדוק שהשדות מכילים את הערכים המופיעים במערכות המקור. אם מתבצעות מניפולציות על חלק מהשדות ב-ETL, בדוק את תקינות החישובים.
• חזור על הפעולות הללו לסוגים שונים של ערכים, כאשר החלוקה היא לפי מקרים הגורמים לחישובים שונים ב-ETL. לדוגמא, לקוח חדש, לקוח מתמיד, לקוח ללא ת.ז. תקינה וכו'
• שליפות כוללות
• השווה כמויות אל מול מערכות המקור. אם ה-ETL מכפיל או משמיט רשומות - בצע את ההשוואה בהתאם.
• בדוק האם ישנם שדות שתמיד יש בהם אותו הערך (בדגש על null)
• בדוק את (max(dwh_create_date בכל טבלה ע"מ לוודא שאכן הוכנסו נתונים
• בדוק שאין רשומות כפולות בטבלת היעד
• האם כל מה שמוגדר כ-Unique הוא באמת כזה
• האם ביחסי Foreign Key אין רשומות יתומות/עריריות
• בדיקות אינטגרציה וביצועים
• ביצוע JOIN מטבלת ה-FACT (או הטבלה המרכזית של הפרויקט) לטבלאות המימד ובדיקה שאין הכפלה או השמטה של רשומות. לדוגמא, JOIN מ-FACT המכירות לטבלת מימד הלקוחות.
• תקינות של טבלאות Slowly Changing Dimensions
• במידה ומתבצע מיקבול של טבלאות (רלוונטי ל-exadata, netezza ודומיהן) - לבדוק שהתפלגות הנתונים בשרתי ה-slave השונים נורמלית (לדוגמא, שאין שרת אחד המכיל 90% מהרשומות)
• בפרויקט קיים - השוואת זמני ריצה של שאילתות מייצגות (מה-log) לפני ואחרי השינויים
• חווית משתמש
• בדוק שהאפליקציה (מחולל דו"חות בדר"כ) מצליחה לשלוף את הטבלאות מבסיס הנתונים
• בדוק ששליפה אורכת אותו אורך-זמן כמו שליפה ישירות מול בסיס הנתונים
• במקרה והאפליקציה מבצעת דברים בעצמה (מעבר להצגת הנתונים מבסיס הנתונים), בדוק את תקינותם
• Security
• לבדוק עבור מספר משתמשים בעלי הרשאות שונות שהם רואים מה שהם צריכים לראות ולא רואים מה שהם לא צריכים לראות
• לבדוק שטבלאות היעד אינן מכילות רשומות שאף-משתמש לא אמור לראות (לדוגמא, משכורת המנכ"ל)
• ביצוע סקר קוד אצל הגורם הרלוונטי (חבר צוות / ראש צוות / DBA וכו')

מצטער על הכותרת באנגלית. בדר"כ אני אוהב לכתוב בעברית אבל אני לא בדיוק יודע איך לתרגם ETL לעברית (אי.טי.אל? מהלך? טעינה? גזירה?) אשמח לקבל פתרונות והצעות לבעיה זו...

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

לשמחתי הצלחתי לעשות את זה ורציתי לשתף אתכם בפתרון. לדעתי, כלי ה-ETL המהיר ביותר (מבחינת פיתוח כמובן) הוא לא פחות או יותר מאשר Microsoft Access. בפוסט זה אדגים איך אפשר להעביר נתונים באמצעות Access במהירות הבזק ע"י שימוש בחיבור ODBC. בפועל ביצעתי העברה מאקסל ל-Oracle. כאן אדגים העברה מאקסל ל-SQL Server כי אין לי כרגע Oracle מותקן במחשב הביתי. בכל מקרה, ההדגמה משתמשת ב-ODBC בלבד ולכן מה שאדגים יעבוד בין כל בסיסי הנתונים שאפשר לגשת אליהם ע"י ODBC. לאחר ההדגמה אכתוב קצת Q&A. אם ישנן שאלות נוספות אתם מוזמנים להגיב.

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

נוריד את קובץ המקור ונפתח אותו.


שימו לב שבמקרה זה השורה הראשונה אינה מכילה את שמות העמודות, וזה מה צריכים שיהיה. נסיר את השורה הראשונה.
נגדיר חיבור ODBC ל-DWH שלנו: נפתח את חלון חיבורי ה-ODBC ע"י לוח הבקרה, Administrative Tools, ומשם Data Sources. לחלופין, אפשר לכתוב בשורת הפקודה odbcad32. תחת הלשונית User DSN נלחץ על Add ושם נבחר ב-SQL Server. כאמור, במשימה המקורית שלי השתמשתי ב-Oracle אבל זה ממש אותו דבר. כמו"כ, מומלץ לגלול ימינה את המסך ולבחור את הדרייבר עם הגירסה העדכנית ביותר. חשוב לשים כשמזינים את שם ה-DB, לכתוב גם את שם ה-Instance למקרה שיש כמה כאלו על אותה מכונה פיזית.

במסך הבא נזין את פרטי החיבור שלנו ל-DB. חשוב לבחור ביוזר שיש לו הרשאת כתיבה אם משתמשים ב-Basic Authentication ואם משתמשים ב-NT Authentication אז לוודא שיש לנו הרשאת כתיבה. צריך לבחור גם את שם הסכמה ה-default-ית אליה יוכנסו הנתונים.

לאחר עוד כמה לחיצות על Next נגיע למסך הסיכום. חשוב מאוד ללחוץ על Test Data Source כדי לוודא שהגדרנו את החיבור בצורה נכונה. זה רלוונטי לכל סוגי החיבורים ולא רק ב-SQL Server.

לאחר שהגדרנו את החיבורים השונים, הגיע הזמן להעביר את הנתונים. נפתח Access (אני מדגים על גרסת 2007, זה פועל בגרסאות ישנות יותר בדיוק באותה צורה. בדוק!!) וניצור Blank Database חדש. התוכנה יוצרת אוטומטית טבלה חדשה בשם Table1. מי שזה מפריע לו יכול למחוק אותה. נלך ללשונית External Data, נלחץ על Excel ונבחר את קובץ האקסל שלנו. חשוב מאוד לבחור באפשרות Link to the data source כדי לחסוך זמן ולא לייבא את הנתונים כבר בשלב הזה.
במסך הבא נבחר את הלשונית ממנה אנו רוצים לקחת את הנתונים ובמסך שאחריו נסמן את First Row contains column headings, כפי שדאגנו שיקרה. במסך הבא ניתן שם לטבלה המקושרת שיצרנו ונסיים.
כעת נייצא את תוכן הטבלה המקושרת שיצרנו ל-DWH: לחיצה ימנית על הטבלה המקושרת, Export, ומשם נבחר ב-ODBC.

נבחר את שם הטבלה שתווצר ביעד ולאחר מכן נעבור ללשונית Machine Data Source ונבחר בקישור ODBC שיצרנו מקודם.

זהו. הטבלה תיווצר ב-DB ה-default-יבי שהגדרנו כשיצרנו את חיבור ה-ODBC ל-DWH.
אולי זה נראה ארוך, אבל בפעם השנייה זה ייקח לכם פחות מ-60 שניות.

Q & A

למה לא לעשות את זה ב-SSIS?
א. לא בכל ארגון יש SSIS (תתפלאו)
ב. לא בכל עמדת מחשב מותקן SSIS
ג. לדעתי זה יותר מהיר מ-SSIS
ד. ב-SSIS יש נטייה להתעסקות יתר עם Locale ושאר ירקות, וכאן זה נחסך

מה לגבי מניפולציות נוספות על המידע?
ישנה אפשרות לבנות שאילתה ששולפת את כל הטבלה המקושרת (האקסל) ואז לעשות שאילתת Insert או Update. לא הראיתי את זה כי הפוסט יצא ארוך גם ככה. בנוסף, תמיד אפשר לעשות אח"כ פעולות על הטבלה ב-DWH.

זה לא מסוכן לעשות פעולות מעין אלו ישירות על סביבת הייצור?
בהחלט. ולכן זה דבר שנעשה רק במקרים יוצאי דופן ובתיאום מלא עם הלקוח וה-DBA

או Storage Tiering בלעז.

הבעיות: א. אחסון עולה המון כסף. ב. האחסון שלנו איטי ובדר"כ לא מספק את דרישות מהירות השליפות.

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

קישור (pdf)

לאחר שש שנים בחיל-האוויר השתחררתי השבוע בשעה טובה ויצאתי לאזרחות. בחרתי לשתף כאן את מכתב השחרור שכתבתי, הכולל טיפים רבים ו"אני מאמין" לבאים אחרי. אני לא רושם כאן את הדברים האישיים יותר ואת הבדיחות הפנימיות, אלא את מה שרלוונטי לקהילת ה-BI ומערכות המידע ככלל.

אם יש לכם מחלוקות, הערות או הארות לגבי האמור, אשמח מאוד אם תשאירו תגובה.

ניהול פרוייקטים

• שיווק זה הכל. אין מה לעשות. אם לא תשווקו נכון את המערכות והיכולות שלכם, הצוות ותוצריו ישבו על המדף ויצברו אבק.
• POT - Proof of Technology. יש לזה גם הרבה שמות אחרים, אבל בסופו של דבר זה אותו הדבר: רוצים להוכיח לראש-צוות/חברים/לקוחות שמשהו אפשרי וישים? תתבודדו לקצת זמן ותממשו את זה quick & dirty. החוכמה היא לבנות את זה אח"כ מחדש בצורה נכונה.
• תשמרו הכל. תכתובות, מחשבות, תובנות. מתישהו בעתיד תרצו לראות את זה שוב.
• תבנו את מסמך ההתקנה תוך כדי הפיתוח. כך לא תפספסו שום דבר במהלך ההתקנה.
• תעבדו עם מסמך כיסוי דרישות. אל תגיעו לסוף הפיתוח או אפילו מאוחר יותר כדי למצוא שישנה דרישה שלא כיסיתם בפרויקט.
• העסק לא מתקדם? תרימו דגלים בזמן. ככל שתציפו את הבעיה מאוחר יותר, כך יהיה יותר קשה לטפל בה. זו לא בושה לומר שהפרויקט שאתם מובילים תקוע בגלל סיבות כאלו ואחרות.
• לאחר כל פגישה עם משתמשים, מינורית ככל שתהיה, תשלחו סיכום פגישה. זה חשוב לשם התיעוד, הנושא החוזי וכדי ליישר קו בין כל המעורבים.
• פרויקט חייב הגדרה ברורה וחד-משמעית של משתמש מוביל. אחרת יש בלאגן.
• אני מאמין גדול בפיתוח מהיר של פיילוט בתחילת הפרויקט, כדי שיהיה מובן לך ולמשתמש מה הוא עומד לקבל. בנוסף, כך מגיעים להרבה תובנות שקשה יותר ליישם את מגלים אותן בשלב מאוחר יותר. אני בהחלט מאמין שזה שווה את הזמן ואת המאמץ.
• גם הבוס שלכם הוא משאב לכל דבר. אפשר לסכם מראש שיש נושאים שאתם עושים outsourcing אליהם. דוגמא אחת מבין רבות היא נושא המשמעת (רלוונטי יותר בצבא מאשר באזרחות...)

תוכנה

• זו לא חוכמה גדולה לפתח מערכות. ישנם גם ילדים בגיל בר-מצווה שעושים זאת. החוכמה הגדולה והאתגר האמיתי הוא לפתח מערכות שקל ונוח לתחזק אותן לאורך זמן, לעשות להם Scale-Up ו-Scale-Out ואולי הכי קשה - שבאמת יענו על ציפיות המשתמש.
• אין קיצורי דרך. כדי להיות טובים יותר אתם צריכים:
• ניסיון, ניסיון, ניסיון
• לקרוא ספרים
• לקרוא בלוגים
• להשתתף בכנסים
• תקראו את הספר The Pragmatic Programmer, לא משנה אם אתם אנשי אפליקציה או תשתיות, יוניקס, לינוקס, Windows או z/OS. הוא יפתח לכם את הראש וישנה את הדרך בה אתם עובדים, אם רק תחליטו לאמץ את האמור שם.
• תיישמו את המשפט הקודם.
• Talk to a rubber duck - אתם תקועים בבעיה ולא מוצאים פתרון? הקוד לא מתקמפל ואין לכם מושג למה? דברו עם ברווז גומי או כם חבר. בהמון מקרים, תוך כדי תיאור הסימפטום תמצאו לבד את מקור הבעיה. זה קרה לי המון פעמים גם בתור המתייעץ וגם בתור החבר. אגב, בעבר אשכרה הבאתי ברווז גומי למשרד...
• אנו חיים ב"עלמא דשקרא" (עולם השקר בארמית), וגם ה-Look & Feel יקבע את יחס המשתמש למערכת. גם אם אחרים חושבים שזה לא שווה את הזמן, תשקיעו במראה של המערכת שלכם.
• עבודה עם שני מסכים בו-זמנית היא לא סתם דאווין. היא באמת יעילה ומקצרת זמנים. גם בבית אני עובד כך.
• המגמה הכללית בעולם ה-IT היא שאיפה לעבודה עם כלים ופחות כתיבת קוד (יש המכנים זאת תכנות-עכבר) ולא נפרט כאן למה. בחירת הכלים צריכה להיות מתוך מתודולוגיה מוגדרת ובהחלט לא ההיפך. כלי, כשמו כן הוא - כלי עבודה ממש כמו כלי לעבודה בגינה. אם הוא לא נוח, לא חד מספיק או יותר מדי מורכב - אולי הכלי לא מתאים.
• נסו לאמץ את פרספקטיבת העולם של המשתמש שלכם ולא להסגר בראייה הצרה שלכם כאנשי תוכנה. משתמש ממוצע לא חושב ומתנהג כמו תוכניתן. זה קשור לעיצוב של המסכים שלכם, ל"זרימה" של התהליכים ולהבנה של מושגים.

כללי

• אפשר לקדם המון דברים בצורה טובה בשיחות מסדרון קטנות ולאו דווקא לעשות הכל במייל. הטאץ' האנושי מוסיף המון.
• תלמדו גם לומר דברים שלא לציטוט ובאופן לא פורמלי.
• טעיתם? פישלתם? תבואו ותגידו. אל תטאטאו מתחת לשטיח. זה גם פוגע בכם אישית כבני-אדם וגם יתגלה בסופו של דבר. אני מאמין שהממונים עליכם יעריכו את זה לאורך זמן, גם אם הם יכעסו עליכם באותו הרגע.
• מה שעולה חינם שווה חינם. אגב, Open Source הוא לא חינם. הוא כולל בתוכו מגבלות רבות.
• תפתחו בלוג. בין אם אתם חדשים או ותיקים, מאפיינים או תוכניתנים, מרגישים שאתם מקצוענים או לא - תפתחו בלוג. עצם הכתיבה הופכת אתכם למקצוענים יותר וכמובן שעצם השיווק יעזור לכם מאוד בעתיד.
• אל תהיו קונפירמיסטים. אתם חושבים שמסביבכם עושים דברים לא נכון? תבואו ותגידו.
• תהיו טובים וכנים עם חבריכם לעבודה. ובכלל בחיים.

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

כנראה שכן, וזה טוב לכולנו.

סוף כל סוף, הצעד הראשון לקראת ווב סמנטי (Semantic Web) כבר כאן. כעת, מנועי החיפוש כבר לא צריכים לנחש מה משמעות הטקסט שיש באתרים. האתרים יספקו את המידע הזה.
מבחינה טכנית, מה שקורה הוא שאנו (לפחות מפתחי האתרים שבינינו) נוסיף מאפיינים נוספים ל-HTML Tags שלנו שמתארים מה הטקסט מכיל. לדוגמא: אפשר להוסיף מאפיין ל-Tag שמציין שמדובר במאמר, דעה, רעיון יצירתי וכו'.

מה זה אומר לגבי מי שכותב בקהילת הבלוגים של מיקרוסופט? תלוי. מי שרוצה להיחשף בצורה יותר טובה במנועי החיפוש צריך לערוך באופן ידני את ה-HTML שלו כשהוא כותב פוסט (או לחלופין להכין תבנית מוכנה מראש). מי שלא - לא חייב.
מי שרוצה להעמיק בנושא - מוזמן להכנס לאתר של הסטנדרט המשותף, שנקרא Schema.org

לאחר שש שנים של עבודה עם SSAS, התחלתי לקרוא מחדש את הספר המקיף ביותר בנושא - Microsoft SQL Server 2005 Analysis Services. למה דווקא 2005 כשיש גרסאות חדשות יותר? כי זהו הספר המקיף ביותר בנושא עד היום והיחיד שנכתב ע"י צוות הפיתוח של המוצר. הספר מעניק ראייה עמוקה יותר של הטכנולוגיה ואת המוטיבציה לכל החלטה עיצובית מזווית-הראיה של מי שבנה את המוצר. תוך כדי קריאה, אני נזכר בנושאים שרציתי לכתוב עליהם ואני מאמין שתוך כדי קריאת הספר אכתוב עוד.

כריכת הספר. ולא, אין לי מניות אצלם

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

בכל פעם שאנו משייכים שדה מסוים ב-DB ל-Attribute במימד, נוצר מאחורי הקלעים אובייקט מסוג DataItem. האובייקט מגדיר את השיוך בין המודל התפיסתי לבין המודל הפיזי. לדוגמא, הוא מגדיר מאיזו טבלה ועמודה נלקחים הנתונים. בין השאר, ישנו מאפיין מאוד חשוב שנקרא NullProcessing שמגדיר (הבנתם לבד מהשם) איך המערכת מתייחסת ל-null. ברירת המחדל היא ZeroOrBlank, כלומר ה-null יוצג למשתמש כאפס (אם ה-DataType אינו String) או מחרוזת ריקה (אם ה-DataType הוא String). הבעיה היא שבמקרה וה-Attribute המדובר הוא הורה בהיררכיה התוצאה יכולה להיות רעה מאוד, וזה בדיוק מה שקרה לנו בעבר: הוגדר מדד "כמות מכירות" תחת היררכית קטגוריה -> תת-קטגוריה -> מוצר, ובטעות אחד מהחברים ברמת קטגוריה קיבל את הערך null והוא תורגם בקוביה לאפס. המשתמש התרגל לראות תמיד את תת-הקטגוריה "נעליים" תחת קטגורית "ביגוד". יום אחד הוא ראה שכמות מכירות הנעליים ירדה באופן דרסטי: הוצגו מוצרים תחת תת-קטגורית נעליים שתחת קטגורית ביגוד עם כמות מכירות נמוכה ב-Sub-Total. בהמשך הטבלה "התחבאו" מוצרים נוספים שהם תחת תת-קטגורית נעליים אך גם תחת קטגוריה אפס. הסיבה הייתה שכמה ממוצרי הנעליים קיבלו קטגורית null ולכן בדו"ח שהוצג למשתמש, מכירות הנעליים התחלקו בין קטגורית "ביגוד" ובין הקטגוריה אפס. המשתמש קיבל חצי אמת וכמובן שהבין זאת כטעות בנתונים, דבר שאסור שיקרה במערכת BI.

מה עושים? לעניות דעתי, ברוב המקרים יש להגדיר את NullProcessing כ-Error, מה שיזרוק שגיאה וימנע מאיתנו לתת למשתמש מידע שגוי. כך, אנחנו (אנשי ה-IT) נדע על הבעיה לפני המשתמש ולא להיפך.

אילו אפשרויות נוספות יש ל-NullProcessing?
* Preserve - שומר את ערך ה-null ולא מחליף אותו בדבר אחר. כפי שהצגתי, הדבר אינו מומלץ כלל. בנוסף, Preserve צורך יותר משאבי מערכת.
* Unknown Member - מחליף את ה-null ב-Unknown Member שניתן להגדיר במימד. אני ממליץ להשתמש באפשרות הזו רק אם המשתמשים מכירים היטב את עצם קיומו של Unknown Member ואת המשמעות שלו.
* Automatic - השרת יקבע את האפשרות המועדפת לבדו בהתאם להקשר (Context). לא ברור לי בדיוק במה מדובר והכותבים לא מרחיבים את היריעה בנושא, אך באופן כללי מומלץ להגדיר התנהגות קבועה לשרת ולא לאפשר לו "להפתיע" אותנו.

האם עכשיו נעבור על כל ה-Data Bindings שלנו ונשנה לכולם את ההתנהגות הזו? בפירוש לא. צריך לחשוב איפה ההתנהגות הזו משפיעה לרעה ואיפה לא. לדוגמא, ב-Key Attribute של מימד שמשמש כעלה (Leaf) של היררכיה ההתנהגות ZeroOrBlank דווקא מבורכת.

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

כאשר אנו מאפיינים תהליך ETL, בייחוד בטבלאות Fact, אנו מוצאים לנכון, במקרים רבים, לכתוב את הלוגיקה של התהליך כ-Flowchart. הדבר הזה יוצר שפה פשוטה ומשותפת בין המאפיין/מנתח לתוכניתן ויתרה מזאת - בין אנשי ה-IT ללקוחות. הרבה יותר קל ללקוח לקרוא ולהבין Flowchart מאשר מסמך מורכב ו"סיפור חייה של הרשומה". לכן, אנו יוצרים באמצעות Visio תרשימים כגון זה (זה נוצר ב-Google Docs, אבל הוא מאוד דומה למקור):

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

• עוד בשלבים מוקדמים של פיתוח ה-ETL ניתן לבדוק סטטיסטיקות (כמה רשומות של כל מקרה קיימות), לראות האם הן מתאימות למה שאנו מצפים (וכך לגלות טעויות בשלב מוקדם) ולפעמים ללמוד דברים חדשים.
• הרשומה יודעת "לספר על עצמה" למה היא נוצרה כפי שנוצרה ואיזה מסלול חישוב היא עברה. הדבר הזה חוסך לנו מאמץ רב כשצריך לתחקר את הנתונים ולמצוא את מקורן של הבעיות.
• במהלך הבדיקות אנו בודקים שני דברים - האם כל רשומה "זוהתה" כמו שצריך (האם המקרה הוא הנכון) והאם הערכים שהוכנסו לכל השדות נכונים. בעוד שבעבר היינו בודקים את רק את נכונות הערכים, כעת אפשר למצוא שגיאות מיד בסוג הראשון של הבדיקות מבלי לגלות אותם ע"י בעיה בערכים.

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

• הפירמידה הארגונית - היחס לאורך השנים בין כמות האוכלוסיות השונות. לדוגמא, היחס בין כמות מרצים בכירים, מרצים, מתרגלים וסטודנטים. הדו"ח מוצג כגרף עמודות מוערם (Stacked). הגרף נותן מענה לשאלה הבסיסית: האם יש לנו די סטודנטים כדי ליצור מהם מספיק מרצים בשנים הבאות? האם יש לנו די מרצים כדי שיהיו לנו מספיק מרצים בכירים בעתיד? את היחס הזה אפשר לראות לאורך השנים. בתמונה יצרתי באקסל גרף מאוד דומה למה שיש לנו, רק שכאן אני מציג את כמות הסטודנטים בכל שנה"ל שלהם:
• פער בקליטת סטודנטים - עבור כל פקולטה ומגמה, הפער בין כמות הסטודנטים שהאוניברסיטה רצתה לקלוט לבין הכמות בפועל. ניתן לחתוך את הדו"ח לפי שנה אחרת ולראות איך הנתונים הללו נראו לפני שנתיים, לדוגמא. כמו-כן, ניתן "לצלול" (Drill Down) לתוך פקולטה או מגמה מסוימת עד לרמה השמית.
• קידומים ופרישות - כמות קידומים ופרישות של מרצים לפי שנים, ניתן גם לפלח לפי פקולטות ומגמות. גם כאן ניתן "לצלול" לרמה השמית.
• ציונים - גרפים קלאסיים של ממוצע ציונים בפקולטה או מגמה מסוימת ע"פ ציר הזמן. פתרון מצוין כדי לגלות מגמות מעניינות בפקולטות כגון מרצים שכותבים מבחנים קשים יותר או ירידה ברמת הלימודים.

עוד אחת קטנה לסיום - ההתחלה של שמות הפוסטים (כ"א BI, BI כח-אדם וכו') משתנים בגלל שה-Community Server לא מאפשר שתחילת השמות יחזרו על עצמם. להם הפתרונים באשר לסיבה לכך.

זה הפוסט הרביעי בסדרה העוסקת במערכות BI של כ"א. הפוסט הקודם עסק במידול המתקדם של הפרויקט בו פירטתי את המבנה הטבלאי של מחסן הנתונים וכיצד ניתן לענות על רוב השאלות תוך שימוש במידול שהצעתי. בפוסט הזה אמשיך ואציג נושא מורכב יותר בעולם הכ"א - תהליכים וניקוי רשומות. אציג את הנושא ואיך מידלתי את מחסן הנתונים באופן כזה שיתמוך בתהליכים ובניקוי רשומות.

אני ממליץ לקרוא את הפוסט הקודם לפני קריאת הפוסט הזה כדי להבין טוב יותר את הדברים. נסכם מה שעשינו בפוסט הקודם: הגענו למסקנה שכדי לענות בצורה יעילה על כל השאלות שנשאלו בנושא כ"א יש לבנות טבלת Fact "רחבה" מאוד (כלומר עם הרבה עמודות) המכילה את כל הנתונים מכל הנגררות הרלוונטיות. הראיתי גם את בעית חוסר הרלוונטיות וכיצד פותרים אותה ע"י הכנסת הערך N/A במקום null במקומות בהן הנגררת עדיין לא התחילה.

בעצם לאחר שעשינו את המתואר סיימנו 90% מהפרויקט. הבעיה היא שהייתה עוד דרישה בסיסית (שהייתה יוזמה ורעיון שלנו ולא של הלקוח) שלא הצגתי בפוסטים הקודמים מהסיבה הפשוטה שלא רציתי לבלבל את הקורא בשלב מוקדם של הסדרה. הדרישה היא לנקות את היומן האישי של האדם מרשומות שאינן רלוונטיות ולזהות תהליכים. אסביר לקורא המבולבל את משמעות הדברים (בשלב זה השימוש בדוגמא של אוניברסיטה קצת פחות רלוונטי ולכן אני מתנצל מראש על הדוגמאות ההזויות):

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

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

זיהוי תהליכים
פעמים רבות קורה בעולם הכ"א שאדם עובר תהליך מהותי בקריירה שלו הגורם למספר שינויים במספר נגררות תוך מספר ימים מועט, אך מבחינה מהותית האדם עבר תהליך אחד. לדוגמא, כאשר מתרגל הופך למרצה הוא משנה את המעמד שלו (סטודנט/מתרגל -> מרצה), את הקורסים שלו (בדר"כ הוא סיים את כל הקורסים שלו ומתחיל ללמד קורסים אחרים) וכמובן את השכר שלו (מן הסתם הוא עולה באופן משמעותי).
אם נסתכל בנגררות שהרגע ציינו, נמצא שלאדם המדובר התווספו רשומות בכל הנגררות ומדובר באותם הימים פלוס-מינוס. למה פלוס-מינוס? כי בהחלט ייתכן שהשינוי בשכר יקרה רק לפני התשלום החודשי, בעוד השינוי במעמד יחול מיידית ברגע המינוי. כלומר, מבחינה טכנית האדם בסה"כ שינה כמה נגררות שלו, אך מבחינה מהותית, מה שנקרא from the business view, האדם עבר תהליך. הדרישה היא שהמערכת תזהה ותציף את השינויים הללו ואף תאפשר תחקור וסינון על פיהם.
בשפה המקצועית הדבר הזה נקרא Super Transaction. אתם מוזמנים לקרוא עוד בנושא אצל קימבל.

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

כללי - מידול
כדי לממש את הדרישות הללו נגדיר את שלושת מצבי העבודה שהמשתמש יכול לראות בכל עת.

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

נחזור לחשיבה של מנתח המערכות. אם נסנן את הרשומות בהתאם למצב העבודה שבחר המשתמש, ניתקל בבעיה. כל רשומה יכולה להיות שייכת לכמה מצבי עבודה: רשומה שאיננה נמחקת בתהליך הסינון תהיה שייכת לכל המצבים. רשומה שמסוננת בתהליך הסינון שייכת למצב המסונן ולמצב המשולב. רשומה תהליכית (רשומה המציינת תהליך, יפורט בהמשך) שייכת למצב המסונן והמשולב. הפתרון ה"נאיבי" אומר להוסיף ליומן שלוש אינדיקציות (boolean) המציינות לאילו מצבים הרשומה שייכת ולסנן ע"פ האינדיקציות הללו כשהמשתמש בוחר מצב עבודה מסוים. הבעיה הגדולה היא שחייבים להגדיר אינדקסים על השדות הללו (אחרת נחווה הרעה איומה בביצועים) והאינדקסים הללו מאוד לא יעילים כי השונות בין הערכים מזערית (הרי מדובר בשדה boolean).
הפתרון שיישמנו הוא פיצול של היומן לשלוש טבלאות שונות, כאשר כל מצב נמצא בטבלה אחרת. שינוי מצב עבודה ע"י המשתמש משנה את ה-connection וגורם לתצוגה להתמפות מעל טבלה אחרת. היתרון הגדול של הפתרון הזה הוא שיפור בביצועים. השליפה מתמפה מעל טבלה מסוימת ולא צריכה לדאוג לסינון רשומות בכל הנוגע למצבי עבודה. כמובן שצריך כלי Reporting שתומך בזה, אך אני מאמין שבכל כלי קצת Workarounds יכולים לעשות את העבודה. ב-Reporting Services עושים זאת ע"י הגדרה של Stored Procedure כ-Data Source ושליחת פרמטר אליו המגדיר את הסכמה המבוקשת (קוד כאן). ב-OBIEE עושים זאת ע"י value-based fragmentation (הוראות כאן). החסרון הוא תפיסת נפח מיותר לכאורה, אך אנחנו שמים ביצועים לפני נפחים.
לתהליך ה-ETL יש שלוש טבלאות מטרה, אחת עבור כל מצב עבודה. המסננים שבתוך תהליך הטעינה מגדירים לאילו יומנים, או טבלאות כל רשומה צריכה להגיע.

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

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

זיהוי תהליכים, או: מי זו אסתי?
הגענו לחלק המורכב יותר - זיהוי תהליכים. נזכיר שוב מהו תהליך, גם מבחינת ה-Business והפעם גם מהפן הטכני: תהליך, או Super Transaction בעגה המקצועית (ולכן קראנו לזה ST או אסתי המכוערת :-) הוא אירוע מהותי שהתרחש אצל הפרט, המתבטא בדר"כ על-פני מספר נגררות ופעמים רבות גם מתרחש על-פני מספר ימים הקרובים אחד לשני. אזכיר שוב את הדוגמא שהבאתי לגבי מתרגל שהופך למרצה ומשנה תוך מספר ימים קצר את המעמד, הקורסים והשכר שלו. דרישת הלקוח היא להציף למשתמש שהתרחש כאן תהליך ולא רק עוד כמה שינויים בנגררות של האדם.
נעבור לפן הטכני. ישנן כמה שאלות בסיסיות בנוגע לתהליכים:

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

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

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

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

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

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

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

נגררת מחלקה
012	01/01/2000	היסטוריה
012	01/06/2005	מחשבים

נגררת מעמד
012	01/01/2000	סטודנט
012	01/01/2006	מתרגל

נגררת קורסים
012	01/01/2000	ימי הביניים
012	01/06/2005	מבוא למדעי המחשב

נגררת שכר
012	01/02/2006	בסיסי

 

וכך תיראה הטבלה הרחבה, או "היומן" כפי שבחרנו לקרוא לה:

 

ת"ז	תאריך תוקף	מה השתנה	שם	שכר	מחלקה	מעמד	קורסים	תאריך סיום תוקף
012	01/01/2000	מחלקה מעמד קורסים	מיקי שרייבר		היסטוריה	סטודנט	ימי הביניים	31/05/2005
012	01/06/2005	מחלקה קורסים	מיקי שרייבר		מחשבים	סטודנט	מבוא למדעי המחשב	31/12/2005
012	01/01/2006	מעמד	מיקי שרייבר		מחשבים	מתרגל	מבוא למדעי המחשב	31/01/2006
012	01/02/2006	שכר	מיקי שרייבר	בסיסי	מחשבים	מתרגל	מבוא למדעי המחשב	31/12/2999

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

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

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

1. ישנה בעיית מינוח קשה מאוד בארגון ("אמת אחת"). לדוגמא, ההגדרות הכי בסיסיות - סטודנט ומרצה אינן קונצנזוס. לדוגמא, מה קורה כאשר סטודנט הוא גם מרצה? מהי המחלקה של המרצה אם הוא מרצה בשתי מחלקות? האם מעבר למחלקה אחרת וחזרה כעבור סמסטר נחשב מעבר אחד, שני מעברים או בכלל כאילו לא היו דברים מעולם? כמובן שמאנשים שונים קיבלתי תשובות שונות לשאלות אלו.
2. כל מקורות המידע מגיעים כנגררות. למי שלא מכיר, נגררת היא טבלה המכילה את תעודת הזהות, התאריך ומהות השינוי. כלומר, כל רשומה בנגררת מראה את המצב החדש/נוכחי בלבד. כשרוצים לשאול שאלה הנוגעת לנגררת אחת בלבד (שכר, לדוגמא) אין כל בעיה. הבעיות עולות כאשר שואלים שאלות המשלבות מספר נגררות (שכר ומחלקה, לדוגמא). ישנו צורך אמיתי בביצוע אינטגרציה של הנגררות. נו, בשביל מה יש DataWarehouse?
3. לא מצאנו את ה-Fact במערכת. מצד אחד נראה שכל נגררת היא fact שהרי היא מתארת שינויים/התרחשויות שהתרחשו בעולם, אך... היכן המדדים (measures)? מצד שני, הנגררות נראות כמו Slowly Changing Dimensions או SCD אך... היכן ה-fact? ללא ספק מדובר ב-Factless Fact.

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

במילים אחרות - Star Schema קלאסי. הנגררות הן SCD וה-Fact (להלן פאקט כי נמאס לי לעבור בין עברית לאנגלית) באמצע. הפאקט יכיל רשומה אחת בלבד לכל אדם ותכיל רק נתונים סטטיים של האדם, כגון ת.ז, שם ותאריך לידה. המידול הזה מחזיר אותנו לשאלות אופן הקישור בין המימדים ל-Fact ומהו המדד (measure) ב-Fact. ניסינו לחשוב איך נענה על השאלות הבסיסיות ביותר הנדרשות מהמערכת (אגב, כך לרוב בוחנים את איכות ה-ERD שלנו ועולים על בעיות נוספות):

1. מה השכר, מחלקה והמעמד הנוכחי של המרצה? - נשלוף מכל מימד את הרשומה הנוכחית של המרצה. הקישור יבוצע ע"י ה-fact (רשומת המרצה) דרך שדה הת.ז.
2. מה השכר, מחלקה והמעמד של המרצה בתאריך X? - נשלוף מכל מימד רק את הרשומה שתאריך התוקף שלה החל לפני X והיא הקרובה ביותר ל-X ושוב נקשר דרך הפאקט. מכאן שכדאי לחשב באופן אנליטי (analytical functions) בכל רשומה את תאריך הסיום שלה (יום לפני תחילת הרשומה הבאה) ואז קל לבצע שאילתות עם between הנדרשות לשאלות מעין אלו.
3. ממוצע המשכורות בכל מחלקה - שולפים ממימד המחלקות את רשימת המרצים לכל מחלקה. בוחרים ממימד המשכורות תאריך מסוים המעניין אותנו (בכל פעם), שולפים לכל אדם רק את הרשומה שהיא בתוקף של אותו היום (between) ומבצעים group by. לכאורה נראה לא טריוויאלי לכלי דו"חות קלאסי, אך בואו נחשוב לרגע: מה אנחנו עושים? קיבוץ לפי חברים במימד מסוים וספירה לפי מימד אחר, כאשר המימדים מקושרים דרך פאקט. נראה בסדר. מה הבעיה? הבעיה היא שאנחנו מבצעים סכימה למאפיין במימד ולא למדד וזה כבר לא אפשרי בכל כלי דו"חות. (פתרון איכותי יעבוד עם כלים רבים ולא רק עם כלי ייעודי למערכת מסוימת ולמידול מסוים)
4. שאלות המערבות רק נגררת אחת - קלות מאוד ואינן מצריכות Datawarehouse.
5. הצגת השתלשלות האירועים לאדם מסוים מיום קליטתו ועד היום - לא אפשרי בכלי דו"חות קלאסי. בואו נאמר שאנחנו רוצים לראות את "יומן האירועים" של המרצה Y, ממוין כמובן לפי תאריך התוקף בסדר עולה. הדבר מצריך union של כל הנגררות/מימדים ומיון של כל הרשומות לפי תאריכי התוקף, כאשר ייתכנו מספר רשומות בנגררות שונות באותו התאריך (אפילו מאוד ייתכן. שינוי במעמד גורר בדר"כ העלאה במשכורת).

בשלב זה הבנו שצריך לסגת אחורה, כי נראה בתחילה שהמידול טוב ואיכותי אך השאלה האחרונה שהיא מאוד בסיסית וחיונית הוכיחה שהמידול לא טוב. איך אנחנו מציגים את יומן האירועים המהווה שילוב של הנגררות? כיצד נוכל לזהות תהליכים בהתרחשותם, כגון עלייה בכמות קורסים ושכר המובילים לבסוף לעלייה במעמד? כל אלו הן שאלות שהמערכת צריכה לתת עליהן מענה ו-Star Schema קלאסי לא יכול לעזור לנו כאן.

מה עשינו? המשך בפוסט הבא...

• עולם התוכן - העמודה שכבר קיימת המכילה את שם התהליך או עולם התוכן
• תיאור מקוצר - כדי שגם מי שיעבוד אחרינו יבין למה התכוונו
• זמנים נדרשים - האם התחקור הנדרש לתהליך הינו לעבר, להווה או לעתיד (כמובן שיכול להיות שילוב שלהם). העמודה הזו היא קריטית כי שינוי בה יכול לשנות את המימוש מקצה לקצה.
• עדיפות במטה - עד כמה מטה החברה מעוניין במימוש התהליך הזה. מספר מ-1 עד 5
• עדיפות בשטח - עד כמה גורמי השטח (החל ממנהלי דרג ביניים וכלה ברצפת הייצור) מעוניינים במימוש התהליך. 1 עד 5
• מערכות מקור - אילו מערכות מקור נדרשות כדי להביא את המידע הנדרש לתהליך
• מורכבות קליטת נתונים ממערכות המקור - על סמך העמודה הקודמת, עד כמה מורכב "לשים את הידיים על הנתונים". הקשיים יכולים להיות טכנולוגיים (מערכות Legacy, מערכות ללא ממשק קיים, טכנולוגיות לא מוכרות) או פוליטיים (ה-DBA של המערכת מערים קשיים ולא משתף פעולה, קיימת כבר "מיני-מערכת-BI" שלא רוצים שיחליפו אותה). 1 עד 5
• מורכבות התהליך - ללא שום קשר לעמודות הקודמות, עד כמה התהליך מורכב. לפישוט העניין, עד כמה יהיה ארוך מסמך האפיון והעיצוב של התהליך או כמה מורכב יהיה תהליך ה-ETL. 1 עד 5
• הערות נוספות

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

כעת נצא לרגע מה-Scope המצומצם של הפרויקט שלנו ונחשוב בגדול.
המטרה: בניית מטריצת עלויות עבור כל חלק/מודול בפרויקט. טוב מראה עיניים ממשמע אזניים (או הסברים במקרה שלנו) ולכן הנה דוגמא של מטריצת העלויות.

	קל	בינוני	קשה
אפיון על	2	5	10
אפיון מפורט	2	5	12
חילול ERD	1	1	1
ETL - Dimension	1	3	7
ETL - Fact	1	6	15
Reporting Model	1	6	10
OLAP Project	1	3	7
Reports	0.3	0.5	1
OLAP Reports	0.3	0.5	1.5
Web Site	2	10	20
Security	3	6	10

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

תהליך 1
	קל	בינוני	קשה
אפיון על		1
אפיון מפורט		1	1
חילול ERD		1
ETL - Dimension	1
ETL - Fact		1	1
Reporting Model	1
OLAP Project	1
Reports	1
OLAP Reports	1
Web Site	1
Security	1

הפסימיסט: רגע רגע. למדתי באוניברסיטה על מתודולוגית WBS שבה אני לוקח כל חלק/מודול בפרויקט ופורט אותו לרכיבים האטומיים שלו. מה ההבדל בעצם? מה חדש פה?
מיקי: שום דבר והרבה מאוד. גם כאן אנו מפרקים את הבעיה הגדולה לחלקים קטנים, אך כאן אנו מנצלים את העובדה שהרכיבים הם זהים. כלומר, כל פרויקט BI מורכב מאותם החלקים - אפיונים, ETL, מימדים, קוביות, דו"חות וכו'.

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

• בדיקות מפתחים - 6
• בדיקות משתמשים - 2
• התקנה - 2

בנוסף לעלויות הללו ישנם גם קבועים שצריך להוסיף למשוואה:

• ברוטו - 1.3 (חופשות, מחלות וכו')
• ניהול - 1.15 (זמן עבודה של מנהל הפרויקט)
• באפר - 1.1

ערכי הקבועים הללו הם תוצאה של ניסיון וכמובן שהם מתעדכנים עם הזמן ועם הפרויקטים השונים. נכפיל את M בקבועים ולאחר מכן נוסיף את העלויות הקבועות ונקבל 97.16 ימים. נחלק ב-20 (ימי עבודה בחודש) ונקבל 4.8 חודשי-אדם (להלן ח"א). זו העלות אליה הגענו עבור מימוש התהליך הראשון. יש להדגיש שמדובר בחודשי עבודה (Work) ולא משך, כלומר תיאורטית אם נקצה שני אנשים לעבודה הפרויקט יימשך רק 2.4 ח"א. כמובן שזה תיאורטי ולא מדויק מהסיבה הפשוטה שישנם אילוצים ותלויות בתהליך הפיתוח (לא ניכנס לזה עכשיו).

 

את התהליך המפרך הפשוט הזה נבצע לכל תהליך ובסוף נקבל את התמחור בח"א לכל אחד מהתהליכים אותם אפיינו בתחילת הפוסט.

הפסימיסט: מצטער שאני מציק, אבל... לא מדובר ביותר מדי עבודה בשביל תמחור? מנהל מיומן ינחש/ישערך פחות או יותר אותו דבר, לא?
מיקי: דבר ראשון - לא בטוח. דבר שני ויותר חשוב - השיטה הזו נותנת למנהל ולצוות כולו שפה משותפת לגבי הלו"ז והתמחור. לכן חשוב לכתוב את המטריצות עם המפתחים או לפחות לסקור אותן יחד. חשוב לוודא שהצוות מכיר את המטריצות ואת המשמעויות שלהן לגבי הלו"ז. בנוסף, שיטה זו מעניקה שפה משותפת גם עם הלקוח. אם הלקוח רוצה להבין למה הפרויקט עתיד לקחת כך וכך זמן/כסף אפשר לשבת ולהסביר. אם הלקוח רוצה להפחית עלויות אפשר לדבר איתו במושגים של מימדים ו-Facts ולעשות סימולציה Online ולראות כמה הורדת מימד אחד בדרגת קושי בינוני תוריד לו מעלות הפרויקט כולו. נחמד, לא?

 

 

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

אז עכשיו הנתונים לפנינו ואנו חוזרים לשאלה ממנה התחלנו: איך נחליט מה לממש קודם? הנה התשובה:
הסבר: המספר בסוגריים הוא התמחור בח"א. ציר X הוא קלות הביצוע (ולא קושי, מטעמי נוחות) המתקבל ע"י שקלול של מורכבות קליטת הנתונים ממערכות המקור ומורכבות התהליך (ממוצע אם תרצו או שקלול משלכם). ציר Y הוא העדיפות, כאשר אני נתתי פקטור של 0.8 למטה ו-0.2 לשטח, אך גם כאן תוכלו לשקלל כפי שמתאים לארגון. הגרף/המפה הזו הוא בהחלט תוצר שאפשר להראות ללקוח ולמקבלי ההחלטות וביחד להחליט עם מה להתחיל. ההיגיון הבריא אומר להתחיל מהרבע הימני-עליון (הכי חשוב והכי קל) אבל כמובן שישנם שיקולים נוספים שלא מופיעים כאן. לדוגמא, ישנו שיקול קטנטן ולא חשוב במיוחד כמו... כמה כ"א וזמן מוקצה לנו בכלל???

 

האם בזה הסתיימה עבודת התמחור והתעדוף?
כמובן שלא! מטריצת העלויות דורשת תחזוקה, אך עם הזמן היא תיעשה יותר ויותר מדויקת. כמובן שמנהל הפרויקט צריך לעקוב אחרי ההתקדמות והלו"ז כדי לדעת כמה זמן או יותר נכון כמה עבודה הושקעה בכל שלב בפיתוח. לפעמים גם נגלה שהאופן שבו רשמנו את כתב הכמויות גם לא נכון וגם במקרה כזה נבוא ונעדכן את התמחירים עבור התהליכים העתידים להתממש. בנוסף, עם הזמן נגלה שהערכים במטריצת המחירים הולכים ויורדים עקב התמקצעות ורכישת מיומנויות של צוות הפיתוח.

 

תודה רבה לארקדי גורביץ', ראש-הצוות שלי על שהנחה אותי ביישום הרעיון הזה ועל העזרה בפרויקט בכלל.

 

עכשיו, לאחר שהחלטנו עם אילו תהליכים להתחיל את הפרויקט, אפשר להתקדם. נתראה בפוסטים הבאים בהם נתחיל לתכנן את מערכת ה-BI לכ"א.

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

עד כאן הכל טוב ויפה. הבעיה היא שלפעמים אנחנו שוכחים שמאחורי ה"חברים" במימד העובדים יש אנשים אמיתיים ומאחורי המספרים בטבלאות ה-fact יש פעולות ומאמצים רבים של אנשים (שוב - אמיתיים). שינוי שיטת המדידה או תחילת המדידה עשוי להשפיע רבות על אותם האנשים ועל המאמצים שלהם, או במילים אחרות על החיים שלהם ושל הסובבים אותם.

ולמה אני כותב את זה? כי ראיתי היום פוסט מאוד מעניין לגבי השינויים שנעשו באחרונה בגזרת חברון של משטרת ישראל. מסתבר שהטמעת המערכת החדשה מעודדת מעצרים של פלסטינים על בסיס יידוי אבנים וכדומה להם. אני מכיר חלק מהאנשים שעשו את מערכת ה-BI החדשה של המשטרה, מנה"ל שמה. המערכת מאוד יפה ויזואלית כפי שאתם יכולים לראות וההטמעה שלה הצליחה. אבל, וזה האבל הגדול, משהו שם לא הלך טוב. כנראה שהמדידה הרצויה הרבה יותר מורכבת מאשר המדידה שעל-פיה אופיינה המערכת והתוצאות לא אחרו לבוא.

מה המסקנה או הלקח? קשה לומר באופן חד-משמעי. אבל מה שבטוח הוא שאפיון המערכת חייב להתחשב גם בתוצאות של השינויים התהליכיים שיבואו בעקבותיה. עד כמה אפשרי לשנות עכשיו את אופן המדידה? האם הם Hard Coded או שאפשר לשנות אותם בלחיצת עכבר? האם השינוי ניתן לביצוע ע"י הלקוח או שצריך התערבות של צוות הפיתוח?

איחלתי לקצינים בהצלחה. :-)

רק עכשיו סיימתי לסכם את הסמינר של איתי בראון שנקרא Solutions - Oriented BI for Developers.

חילקתי את הסיכום לחמישה פוסטים נפרדים ואתם מוזמנים לקרוא (באנגלית):

• נושאים כלליים ב-BI, או: דברים שכולנו יודעים אבל שוכחים
• Analysis Services Design
• Near Real-Time Cubes (נושא מרתק לדעתי)
• Integration Services
• Reporting Services

קריאה נעימה!

מדי פעם אני מוצא את עצמי כותב פונקציות היררכיות באורקל. הן עוזרות לי מאוד כאשר אני צריך ללמוד מידע היררכי, לרוב כאשר הוא נמצא במערכות המקור של מחסן הנתונים. בשבוע שעבר שמתי לב שהרבה מחברי הצוות שלי לא מכירים את הנושא, אפילו המיומנים שבהם. לכן, להלן מדריך קטנטן לנושא שתוכלו לחזור אליו מתי שתרצו. מה שחשוב הוא שתכירו שהנושא קיים אפילו אם בפעם הבאה שתחפשו תגגלו אותו (חפשו start with או connect by).

בואו נאמר שיש לכם טבלה שנראית כך:

Key	Father	Description
1	10	Renault
2	20	Jambo
3	20	Airbus
4	10	Hyundai
5	10	Toyota
6	10	Honda
7	20	F16
8	20	F15
9	10	Alpha Romeo
10	-1	Cars
20	-1	Airplanes
-1	-99	Transportation

 

אתם רוצים לראות את הנתונים בצורת עץ, כלומר לראות לדוגמא את רנו, ההורה שלו, הסבא שלו וכן הלאה. כל מה שאתם צריכים לעשות הוא לכתוב את השאילתא הבאה:

select * from MyTable
start with KEY = 2
connect by prior FATHER = KEY

זה הכל. התוצאה היא:

Key	Father	Description
1	10	Renault
10	-1	Cars
-1	-99	Transportation

 

שימו לב שהרקורסיה עוצרת רק כאשר אין אפשרות למצוא את ההורה הבא. בנוסף, פעמים רבות אתם תרצו רק שורה אחת מהעץ (השורש, הבן של השורש או כל קריטריון אחר). במקרה כזה כל מה שצריך הוא להוסיף משפט where לשאילתא.

למרבה הצער, לממש את אותו הדבר עם SQL Server זה הרבה יותר קשה (תקנו אותי אם אני טועה).

 

עדכון: העירו לי שיש יכולות חדשות בנושא ב-SQL Server 2008. אתם יכולים לראות כאן. תודה רבה, אורן. אגב, שמעון קרוחמל הוא חבר ותיק שלי, מצחיק היה להתקל ב-post שלו בנושא.