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

קליקוויו הוא פורץ דרך בתחומים רבים. מרבים לדבר על הקישור האסוציאטיבי שלו, על התחקור 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 במשרד ממשלתי.
תגובות יתקבלו בשמחה.
נתנאל אלירז