תחרות אלגוריתמים ל-Big Data

מרכז המצוינות של EMC ישראל מכריז היום על פתיחת תחרות ה- Big Data הראשונה מסוגה בארץ ל-Data Scientists (מדעני נתונים) . התחרות, הנושאת פרס כספי בסך עשרת אלפים דולר, פתוחה לכל קהיליית מדעני הנתונים באמצעות אתר האינטרנט Kaggle.com עד הראשון באוגוסט 2012.

מטרת התחרות, הפתוחה ליחידים, קבוצות (עד חמישה איש) וסטארטאפים, אשר יזם ומוביל מרכז המצוינות, היא להגדיל את המודעות בתעשייה המקומית לתחום ה- Big Data בכלל ולתחום העיסוק של מדעני הנתונים בפרט, וכן לתרום לתעשייה עם פרסומם של אלגוריתמים חדשניים.  EMC ישראל קוראת לכל בעל רקע וניסיון ב-machine learning , מדעי המתמטיקה, הסטטיסטיקה והמחשב, כלכלה ופיסיקה, וכל אחד אחר, לנסות מזלו בפתרון האתגר שיחכה לו באתר.

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

ספירת הזמנות במחיר מינימום לפריט

בפוסט באתר dwh.co.il הועלתה השאלה הבאה:
כיצד להציג טבלה ובה עבור כל מוצר, ספירה של ההזמנות שבהן אותו מוצר נמכר במחיר הנמוך ביותר?
למשל: מוצר "כובע מצחיה" נמכר ב-92 הזמנות במחיר 12 ש"ח, ב-36 הזמנות במחיר 14 ש"ח, ואילו ב-45 הזמנות במחיר 13 ש"ח. הטבלה צריכה להציג עבור מוצר זה את הערך 92, כי זוהי כמות ההזמנות שבהן נמכר המוצר במחיר הנמוך ביותר (12 ש"ח).
הצורך הוא שהחישוב יהיה דינמי, ולכן חישוב בסקריפט איננו מתאים.
הנטייה הטבעית היא לנסות להשתמש בפונקציית Aggr אשר יודעת להתחשב גם באגרגציה של הפריט הנוכחי וגם באגרגציה של פריטים מקבילים. ואולם במקרה זה פונקציה זו לא תועיל, כיוון שאנו לא מחפשים את המחיר אלא את מספר ההזמנות של מחיר זה.
ניסיון לחשוב "מחוץ לקופסה" הביא אותי להפוך את הערכים המרובים למחרוזת טקסט, ובמישור זה להתמודד עם הבעיה.
כמובן, קבעתי את Product בתור מימד, ולאחר מכן הרכבתי את הביטוי אשר יתייחס אל Product.
ראשית, הביטוי צריך להכיר את כלל המחירים שבהם נמכר המוצר בכל ההזמנות (כולל מופעים כפולים בהזמנות שונות). זאת ניתן לבצע בעזרת הפונקציה Concat, אשר יודעת לשרשר ערכים מרובים למחרוזת טקסט בודדת. ולכן, הגדרתי תחילה בתור ביטוי:

Concat(PriceInOrder,'_')

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

Min(PriceInOrder)

כעת, מה שנשאר הוא לספור כמה פעמים מופיע הביטוי השני (מחיר המינימום) בתוך הביטוי הראשון (כלל המחירים)!
אם-כן הביטוי המלא הוא:

SubstringCount(Concat(PriceInOrder,'_') & '_', min(PriceInOrder) & '_')

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

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

Sum מול Count ורשומות פיקטיביות עבור גרף קו

איך מחשבים בקליקוויו ספירה של הזמנות / לקוחות וכד'? לכאורה התשובה הפשוטה - Count, או בד"כ Count Distinct, על השדה המזהה. למשל:

 Count(DISTINCT OrderID)

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

Sum(OrderCounter)

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

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

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

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

הפתרון הוא ליצור רשומה פיקטיבית עבור סוכן זה (ועבור כל סוכן / שדה אחר שבו זה נדרש), וב"סכום" ברשומה הפיקטיבית יהיה ערך 0 (יש לנקות את האפשרות Supress Zero-Values).

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

הגרף יתוקן:

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

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

הגיגים על Big Data

כולם מדברים על Big Data - הצורך ההולך וגובר לנתח כמויות מידע עצומות, כאלה שמסדי נתונים רגילים לא מסוגלים לטפל בהן בצורה יעילה.
עיקר הדיון היום הוא בהקשר התשתיתי, כלומר מוצעים פתרונות שונים בעלי תשתית חזקה או מתוחכמת יותר, כדי להצליח לנהל את המידע הרב ולתשאל אותו: שימוש במחשבי-על חזקים במיוחד; אחסון המידע In-Memory כדי לקצר את זמני השאילתות; דור חדש של מסדי-נתונים הבנויים בארכיטקטורה המתאימה למידע רב; ביזור פעולות העיבוד (Map-Reduce), ועוד.
אולם נראה שפתרון תשתיתי לבד איננו מספיק, וזאת משתי סיבות:
ראשית, נפתח בעצם "מירוץ חימוש", תחרות בין קצב יצירת הנתונים ובין ההתקדמות הטכנולוגית שמנסה לתמוך בו. לא בטוח שתמיד נוכל להדביק את הקצב הזה. המודעות לצורך ב-Big Data היא יחסית חדשה, ואפשר לומר שהשוק התעורר באיחור, ומנסה בכל כוחו לצמצם את הפער בין הרצוי למצוי. מספיק לראות כמה שנים לא הייתה כמעט שום התקדמות משמעותית במסדי הנתונים בתחום זה.
שנית, כל הפתרונות הללו מנסים לתת מענה לאחסון המידע ולשליפתו, אבל לא פותרים בעיה אחרת. יש לזכור ש-Big Data מתייחס לא רק לכמות הרשומות, אלא גם לעושר המידע, כלומר הרבה יותר סוגי מידע, אינדיקטורים, דגלים, מאפיינים. אם מאחסנים במסד הנתונים (או במחסן הנתונים) את כל המידע המפורט, למנתח / מפתח ה-BI יהיה קשה לנהל ולהבין את המידע, לשלוט בו, ולפתח דוחות על גביו. זה לא משנה כמה אנשים מונה צוות ה-BI - מישהו צריך לראות את כל התמונה, ולכן אסור שהיא תהיה גדולה מדיי.
לדוגמה, Big Data בחברה שמפעילה אתר אינטרנט גדול. החברה לא רוצה להסתפק במידע בסיסי כמו מי גלש לאתר ומתי, אלא רוצה לבדוק עשרות ואפילו מאות פרמטרים שונים. כמות השדות הזאת קשה מדיי לעיכול. אין הרבה ערך ביצירת דוח ארוך שמראה סטטיסטיקות על כל פרמטר ופרמטר - לא כי הן לא חשובות, אלא במקרה כזה "מרוב עצים לא רואים את היער", קשה קבל תובנות מדוח כזה.
למעשה, בהרבה מקרים Big Data מייצג בעיקר את היקף הנתונים שבהם יש להתחשב ושאותם צריך לשקלל, ולאו דווקא את היקף הנתונים הנדרש להצגה בדוח הסופי. לכן מה שנדרש הוא להגדיר לוגיקה עסקית על בסיס הנתונים הגולמיים. האנליסטים ומנתחי ה-BI צריכים לשבת יחד ולהגדיר עולם נתונים מופשט יותר, אשר יאפשר לוותר על אחסון הנתונים הגולמיים, מבלי לאבד מידע עסקי. כך למשל ניתן להגדיר שמספר פרמטרים שונים של גלישה באתר, אשר מייצגים את אותו היבט, יכולים להיות ממופים לפרמטר מרכזי אחד, אשר רק הוא יישמר. אפשר כמובן להגדיר לוגיקות השונות מאגרגציה, משרשור טקסט וממוצע משוקלל, ועד אלגוריתמים מורכבים.
הלוגיקה יכולה להיות מופעלת על כל נתון מיד כאשר הוא מגיע, או לחלופין על Bulks של נתונים שיעובדו מפעם לפעם. לא יהיה צורך לאגור את כל הנתונים הגולמיים.
כמובן, בעצם המתודה אין חידוש. האגרגציה (כמו גם מניפולציות אחרות) היא חלק מרכזי מתהליכי ETL באשר הם. החידוש הנדרש הוא שכרגיל לא נדרשנו לוותר על שום רזולוציה של הנתונים. הנתונים הגולמיים נשמרו במסד-הנתונים התפעולי או במחסני נתונים, והנתונים המעובדים (והמסוכמים) הועתקו למסד-נתונים יעודי ל-BI. אך בעידן ה-Big Data, יתכן שעלינו לבצע את העיבוד כבר בשלב השמירה הראשונית של הנתונים.
נכון, זה נוגד את התפיסה הקלאסית של "יש לאגור את כל המידע כפי שהוא, לעולם אי אפשר לדעת מה תרצה לעשות אתו". סביר להניח שחלק מהחברות לא יוותרו על תפיסה זו, בעיקר כאלה שהן עתירות במשאבים, או כאלה שצריכות ניתוחים מורכבים ומתקדמים במיוחד (למשל בנקים שמנסים לגלות הונאות ורוצים לנתח כל בדל מידע). אבל אולי שאר החברות יכולות להסתפק במידע המשוקלל והמתומצת.
אין ספק שזה נותן הרבה יותר משקל להגדרה נבונה ונכונה של הלוגיקה העסקית, שהרי לא ניתן יהיה לשחזר את הנתונים הגולמיים שלא נשמרו.
ואיך זה קשור לקליקוויו?
קודם כל, זה לא בהכרח קשור...
ובכל זאת, ניתן להיעזר ביכולת של קליקוויו לשמור קבצי QVD, ולהשתמש בקבצים אלו כבסיס-נתונים המתעדכן באופן אינקרמנטלי, וזאת ע"י פיתוח מודל ייעודי, אשר בכל ריצה יבדוק את עדכניות ה-QVD, ויוסיף אליו רק את הנתונים שטרם הוכנסו אליו, תוך כדי ביצוע עיבוד הנתונים. אני פיתחתי מודל מעין זה על בסיס טבלת לוג ענקית אשר בגלל גודלה, נשמרת בה רק ההיסטוריה האחרונה. בזכות מודל הקליקוויו אנו יכולים כעת לצבור ולתחקר מידע היסטורי זמן רב לאחור.

סוג שדה משתנה בקליקוויו

קליקוויו הוא פורץ דרך בתחומים רבים. מרבים לדבר על הקישור האסוציאטיבי שלו, על התחקור In-Memory ועל העובדה שהוא משלב ETL ותצוגה בכלי אחד (תכונות שבינתיים אומצו ע"י כלים אחרים ואף נחשבות בחלקן כמיינסטרים העתידי).
אבל ישנם תחומים נוספים שבהם קליקוויו מהווה חידוש. אחד מהם הוא העובדה שקליקוויו תומך באפשרות ששדה מסוים הוא גם מספר וגם טקסט.
זוהי תפיסה מהפכנית, לא רק ביחס לעולם הקשוח של בסיסי-הנתונים, שם הגדרת ה-Data Type חקוקה בסלע, אלא ביחס לעולם התוכנה בכלל. אפילו ב-#C בגרסאותיה המתקדמות (מגרסה 2), הפונקציונליות הנקראת Generics מאפשרת אמנם גמישות בהגדרת סוג המשתנה, אבל הגמישות היא רק ב-Design Time. ב-Run Time ייקבע סוג המשתנה והוא יישאר קבוע למשך כל הריצה.
קליקויו מאחסן כל שדה כ-Collection, כאשר אחד המאפיינים של כל אבר הוא IsNumeric, כלומר האם הערך הבודד בכל שדה הוא מספרי או לא. ארכיטקטורה זאת מאפשרת למפתח ליצור שדות שהם בד"כ מספריים אבל יכולים להכיל גם טקסט, מבלי להפסיד את היכולת להתייחס לערכים המספריים ככאלו. למשל, המיון נשאר מיון מספרי בערכים הללו, ובהתאם לכך, אם יש גרף אשר אחד הצירים שלו בנוי על שדה היברידי שכזה, קליקוויו ידע למיין את המספרים נכון, למרות שישנם גם ערכים טקסטואליים (אלו יופיעו בסוף). כמו-כן ניתן להפעיל על השדה פונקציות מספריות (כגון Sum) - קליקוויו יפעיל את הפונקציה על הערכים המספריים ופשוט יתעלם מערכי הטקסט.
כדי לראות זאת בעיניים, נגדיר בסקריפט את הטבלה הבאה:

Array:

load * Inline [

Value

1

2

3

4

Hello

];

ניצור כפתור אשר יריץ את המאקרו הבא:

sub ShowIsNumeric

set val = ActiveDocument.Fields("Value").GetPossibleValues

for i=0 to val.Count-1

       msgbox val.Item(i).IsNumeric

next

end sub

ניתן לראות שעבור ארבעת המספרים, מוצג True (הערך הוא מספרי), ואילו עבור המילה Hello מוצג False.

שימוש ב-Power Tools כדי ליצור קובץ טקסט של רישיונות קליקויו

זה לא חידוש שאפשר לבנות מודל המנתח את השימוש בקליקויו, בעזרת קובץ הלוג שמייצר השרת - Sessions_servername.
כידוע, מודל כזה מגיע מוכן - QlikView Server Performance. אבל המודל הזה בסיסי מאוד ואיננו מספק - הן בגלל שאין בו ניתוח מפורט יותר של השימוש (סינונים, לשוניות), אשר אפשרי מגרסה 10 בעזרת הקובץ Audit_servername, והן כי חסרים בו נתונים אדמינסטרטיביים כמו מצאי רישיונות והרשאות על המודלים.
לגבי מצאי הרישיונות - איך ניתן לייצר קובץ טקסט כזה (לאלו משתמשים יש רישיון, ואיזה סוג רישיון), כדי שנוכל לנתח בקליקויו את מצב הרישיונות במודל, ולא רק לצפות בו ב-Management console?
QlikTech הוציאה סדרת כלים שימושיים לאדמיניסטרציה בשם Power Tools for Qlikview. ניתן להוריד אותה מכאן.
אחד הכלים - qv-user-manager, מאפשר בין השאר לשלוח פקודה, שהפלט שלה מייצר רשימה מלאה של הרישיונות - מי המשתמש המורשה, תחילת הרישיון וסוג הרישיון. אנו נוסיף לפקודה אופרטור < ושם הקובץ הרצוי, כדי שהפלט יישמר כקובץ:

התוצאה תהיה כאמור, קובץ המכיל את פירוט רישיונות הקליקויו:

 מכאן נוכל כמובן לנתח אותו בעזרת מודל קליקויו.

מקבילה בקליקוויו ל-row_number() over (partition by

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

row_number()  over (partition by OrderID order by ActionDate) as ActionNum

האם ניתן לעשות גם בקליקויו? ובכן, כן. המומחה John Witherspoon פרסם כאן את הקוד הבא (בהתאמות לדוגמה שלנו):

left Join (Actions)

LOAD

       ActionID,

       if(OrderID<>previous(OrderID),1,peek('ActionNum')+1) as ActionNum

RESIDENT 

       ActionID

ORDER BY 

       OrderID, ActionDate

;

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

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

ובכן, לאחר שיצרנו את השדה ActionNum, נוכל להוסיף את הקוד הבא:

left Join (Actions)

LOAD

       ActionID,

       ActionCost + if(ActionNum>1, Peek('AccCost',-1),0) as AccCost

RESIDENT 

       ActionID

ORDER BY 

       OrderID, ActionDate

;

רשמים מקליקויו 11

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

היכולות העיקריות: 

 קליינט WEB  משופר

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

 ניתן לשתף סשן בין משתמשים (מעין WEBEX)

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

אפשרות להוסיף הערות על אובייקטים במודל

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

ניהול סטים של Set Analysis 

בהחלט עשוי להקל את העבודה עם הפונקציונליות החשובה הזאת. 

 יצירת דוחות פשוטים ע"י המשתמש

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

קישור בין טבלאות שלא על בסיס שוויון

כידוע, קליקויו מקשר בין טבלאות ע"פ שדות עם שמות זהים. יש לכך יתרון בקלות הפיתוח (לא צריך להגדיר את הקשרים באופן מפורש), אך גם חיסרון - כאשר רוצים לקשר ע"פ אופרטור שונה משוויון.
למשל, נניח שאנחנו רוצים לקשר בין טבלת מוצרים לטבלת סניפים, כך שכל מוצר יקושר לסניף שבו הוא לא קיים (נכון שניתן להציג חיתוך כזה בתצוגה, אך לפעמים ישנו צורך כזה בסקריפט). הקישור הוא על בסיס האופרטור "שונה".
בשאילתת SQL זה כמובן פשוט מאוד - יוצרים JOIN עם סימן "שונה מ-". בקליקויו זה לא אפשרי.
באותו אופן אנו עשויים לרצות לקשר על בסיס "גדול מ" ו"קטן מ", ואופרטורים נוספים.
אמנם בקליקויו קיימת האפשרות של intervalmatch, אך היא מכסה רק חלק מהמקרים - קישור על בסיס טווח ערכים חופף.
ובכן, מי שמכיר בסיסי נתונים היטב, יודע שלמעשה בכל Join, בסיס-הנתונים מבצע תחילה Cross Join, כלומר יוצר את כל הקומבינציות האפשריות בין רשומות שתי הטבלאות, ורק לאחר מכן גוזר מתוך כמות הרשומות (המפלצתית בד"כ) את הרשומות הרצויות, ע"פ משפט השוויון.
אנחנו יכולים להשתמש באותו עיקרון. אמנם בבסיס הנתונים הפעולה מתבצעת באופן פנימי ללא fetch ולכן במהירות עצומה, ואילו אנו נסבול מביצועים איטיים, אבל זה כמובן רק הסקריפט. עם זאת על רשומות רבות זה בד"כ לא יעבוד בגלל חריגת זיכרון.
אם-כן, הדרך היא להביא את הטבלה הראשונה, ואז במשפט Join להביא את הטבלה השנייה, כאשר אין כל שדה משותף ביניהן. לאחר מכן על הטבלה הגדולה שנוצרה, נוכל לבצע את צמצום הרשומות ע"י משפט Where.

קריאה מרשימות שרפוינט

אחת היכולות החזקות בקליקויו היא האפשרות לקרוא בקלות ממגוון סוגים של מקורות נתונים. אחד ממקורות הנתונים האפשריים הינו Web Service.
בארגון שבו עושים שימוש ב-Microsoft Sharepoint, ניתן לנצל יכולת זו לקריאה מרשימות שרפוינט באמצעות Web Service פנימי של שרפוינט, שמאפשר להציג כל רשימה כ-XML.
למי שלא מכיר, שרפוינט היא סביבה Web-ית לשיתוף מידע בארגון (עם הזמן התפתחה גם כתשתית פיתוח לאתרי אינטרנט בכלל), כאשר השימוש הנפוץ הוא שיתוף מסמכים, אולם ניתן ליצור גם רשימות שרפוינט מותאמות אישית בתור מעין מערכת קלה. שרפוינט מאפשר קישור בין רשימות שונות, כך למשל ניתן לנהל רשימה של לקוחות, ואז ברשימת הזמנות ליצור שדה "לקוח" המבוסס על רשימת הלקוחות.
כאמור, לשרפוינט ישנו Web Service אשר מחצין רשימות כ-XML, משם הקליקויו יכול לקרוא.
להלן דוגמה ל-URL:
http://my-sharepoint-server/my-site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={216d6c83-5dbf-48e4-8d69-ace8e833c19b}
כמובן יש לשנות בהתאם את שם השרת, שם האתר (כולל היררכיית אתרים אם ישנה) ומזהה ה-GUID של הרשימה.
בתוך הסקריפט בקליקויו, נלחץ על כפתור Web File, ובתיבת Internet File נדביק את ה-URL. במסך הבא נבחר את סוג הקובץ XML,  ותחת Tables נבחר xml/data/row ונלחץ Finish. בקוד שנוצר בסקריפט ניתן להשאיר את טבלת xml בלבד ולמחוק את שתי הטבלאות האחרות שנוצרות.
מכאן ואילך ניתן להשתמש בנתונים כמו כל נתונים אחרים.

פרצת אבטחה חמורה בקליקוויו

לראות ולא להאמין: בקבצי ה-QVW וה-QVD של קליקוויו, נשמר ה-Connection string המלא לבסיס הנתונים, כולל משתמש וסיסמה - בצורה לא מוצפנת.
הפרצה החמורה התגלתה ופורסמה ע"י Luca Jonathan Panetta, בפורום הבינלאומי של קליקויו:
http://community.qlikview.com/thread/40308?start=0&tstart=0
כדי לראות זאת, צריך פשוט לפתוח את הקובץ ב-Notepad ולחפש את המילה password.
דווח שהבעיה תוקנה בגרסה 11, כך שעכשיו יש עוד סיבה טובה לשדרג - ולא לשכוח להריץ שוב את כל המודלים (כולל הישנים שאינם בשימוש), כדי לעדכן את קבצי ה-QVD.
בינתיים מומלץ בחום לוודא שקבצי ה-QVD (וכמובן ה-QVW) מוגנים היטב ואינם נגישים למי שאיננו אמור לדעת את הסיסמה לבסיס הנתונים.

מציאת שדה טקסט ב-DataBase ע"פ ערך, בעזרת QlikView

קורה שאנחנו לא יודעים מהו שם השדה שאנו זקוקים לו ב-DataBase, ולא מצליחים למצוא אותו בעזרת שאילתות על הקטלוג.
אפשר לחפש את השדה ע"פ אחד הערכים שבו (בד"כ בשדה טקסט). ניתן להריץ ב-DataBase פונקציה שרצה על כל הטבלאות ומחפשת בכל שדה טקסט את המחרוזת. פונקציות כאלו מסתובבת ברשת (גם ל-Oracle וגם ל-MS-SQL).
הבעיה היא שהרצת פונקציה דורשת הרשאות כתיבה ל-DataBase - משהו שכדאי להימנע ממנו בתור אנשי BI.
ובכן, ניתן להשתמש ב-QlikView כדי לבצע פעולה דומה.

ראשית, כדי לנטרל הודעות שגיאה שעלולות לצוץ, נגדיר:

set errormode = 0;

ניצור את ההתחברות ל-DataBase, את הסכמה הרצויה, ואת המחרוזת שאנו מעוניינים לחפש:

(connect to db);

set vSchema = 'MySchema';

set vString = 'StringToFind';

נביא את כל הטבלאות וכל השדות:

tabs:

sqltables;

cols:

sqlcolumns;

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

Columns_Temp:

load 

     TABLE_NAME,

     COLUMN_NAME

resident

     cols

where

     TABLE_SCHEMA = '$(vSchema)'

     and DATA_TYPE = 129;

left join

load

     TABLE_NAME,

     TABLE_TYPE

resident

     tabs;

drop tables tabs, cols;

ניצור טבלת עמודות סופית, שבה רק עמודות מטבלאות מסוג "טבלה" (ולא Views וכד'), ונמחק את הטבלה הזמנית:

Columns:

load

     TABLE_NAME,

     COLUMN_NAME

resident

     Columns_Temp

where

     TABLE_TYPE = 'TABLE';

drop table Columns_Temp;

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

for x = 0 to NoOfRows('Columns')-1

     let vTable = peek('TABLE_NAME',x, 'Columns');

     let vColumn = peek('COLUMN_NAME',x, 'Columns');

     Results:

     sql select

          $(vColumn) as value,

          '$(vColumn)' as column_name,

          '$(vTable)' as table_name

     from

          $(vSchema).$(vTable)

     where

          $(vColumn) = '$(vString)' ;

next

לאחר ריצת המודל, נקבל בשדות table_name ו-column_name את הטבלה והעמודה (או הטבלאות והעמודות) שבהן נמצא הערך.

מי שמציע - משפיע

כבר יותר משנתיים ש-QlikTech מאפשרים לקהל הרחב להציע רעיונות לשיפור QlikView ולדון בהם:
http://community.qlikview.com/ideas
לא כל חברה מאפשרת את זה, ועוד פחות חברות נותנות משוב ברור האם הרעיון אומץ על ידם, או למה הוא נדחה. לא מעט רעיונות כבר התקבלו, וזאת ההזדמנות להשפיע על הגרסאות הבאות של המוצר.

הנה כמה רעיונות שאני הצעתי:
אפשרות להציג בגרף עמודות עם שני ממדים את ערכי הפרטים (בתוך העמודות) וגם את סך-הכול (מחוץ לעמודות)
http://community.qlikview.com/ideas/2119
הצגת לוג מפורט יותר בעת ריצת הסקריפט
http://community.qlikview.com/ideas/2141
אפשרות להקליט מאקרו
http://community.qlikview.com/ideas/2129
פונקציית Null-To-Value (או לחלופין הרחבת פונקציית Alt גם למחרוזת)
http://community.qlikview.com/ideas/2142
העתקה/הדפסה של כל הגרף, ולא רק החלקים המוצגים במסך
http://community.qlikview.com/ideas/2122

ליסטבוקס היררכי

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

Country & '/' & City as CountryAndCity

 ואז בליסטבוקס נסמן את Show as TreeView (ואם התו המפריד שונה מלוכסן - להכניס את התו). זה ייראה כך:

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

CitiesHierarchy:

load

  City,

  Country & '/' & City as CountryAndCity

Resident

  Cities;

load

  City,

  Country as CountryAndCity

Resident

  Cities;

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

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

שליפת שדה ע"פ ערך מינימלי/מקסימלי בשדה אחר

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

ניתן ליצור זאת בסקריפט בקלות, ע"י שימוש בפונקציה SubField:

left join (Customers)

load

  CustomerID,

  subfield(maxstring(date(SaleDate,'YYYY-MM-DD hh:mm:ss') & '>' & ProductID),'>',2) as Last_Product

resident

  Orders

group by
     CustomerID;

הסבר - מפנים הפונקציה החוצה:

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

את התאריך המפורמט אנחנו משרשרים לתו < ולמק"ט המוצר. בינתיים הערכים נראים כך:

2011-02-14 10:14:33 > 146782

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

לבסוף אנו מפעילים פונקציית subfield, ומעבירים לה את הפרמטר < ו-2, כדי שתחלץ מהביטוי הנ"ל את האבר השני שבתוכו, כאשר החלוקה לאברים היא לפי התו <.

קיבלנו עבור כל לקוח, את מק"ט המוצר שהוא קנה אחרון.

הבלוג נפתח!

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