CUBEMEMBER – כתיבה עצמאית של פונקציות CUBE
| תקציר המאמר: המאמר מדריך כיצד לכתוב באופן עצמאי את פונקציית CUBEMEMBER באקסל, המתרגמת טקסט חופשי לאובייקטים המזוהים על ידי מודל הנתונים לצורך שליטה דינמית בדוחות. ההסבר כולל את פירוט התחביר של הפונקציה, בדגש על יצירת החיבור למודל (Connection) ובחירת הנתיב המדויק לפריט (Member Expression) מתוך רשימת השדות והטבלאות. |
במאמר הקודם למדנו כיצד להמיר טבלת ציר לנוסחאות באופן אוטומטי באמצעות כלי ה-OLAP.
פעולה זו מייצרת דוח מעוצב, אך הנוסחאות המתקבלות הן סטטיות. הערכים בתוכן "קשיחים", ואם נרצה לשנות אותם, נצטרך לערוך את הנוסחה ידנית.
במאמר זה נלמד כיצד לכתוב את הנוסחאות בעצמנו, כדי לקבל שליטה מלאה על הנתונים, ונתמקד בשתי הפונקציות המרכזיות שמהוות את הבסיס לכל דוח:
CUBEMEMBER ו-CUBEVALUE.
רכיב החובה: החיבור למודל (Connection)
לפני שנתחיל לכתוב את הפונקציות, חשוב להבין שפונקציות CUBE יודעת לפנות רק לחיבורים חיצוניים ולא לתאים רגילים בגיליון (למשל, A1),
ולכן עלינו להזין לתוך הפונקציה את החיבור למקור הנתונים.
החיבורים החיצוניים יכולים להיות מודל הנתונים (Data Model), או שרת חיצוני.
במקרה שלנו, מכיוון שאנחנו עובדים עם Power Pivot, שהוא מודל הנתונים הפנימי של הקובץ, נצטרך להפנות אליו את הפונקציה.
ההפניה נעשית על ידי שם החיבור הקבוע: "ThisWorkbookDataModel"
הפונקציה CUBEMEMBER
מכיוון שפונקציות CUBE לא יודעות לעבוד עם טקסט חופשי, אלא רק עם אובייקטים, אנחנו זקוקים לכלי שיתרגם את הטקסט לשפה שהמודל מבין.
לדוגמה – כשאנחנו כותבים בתא שם של מחלקה כלשהי, למשל – 'שיווק', ורוצים לקבל את סך המשכורות במחלקה,
המודל לא יודע לשייך אותה לנתונים באופן אוטומטי (כלומר – הוא לא יודע למצוא את הטקסט הספציפי הזה בתוך מודל הנתונים),
ולכן צריך לתרגם את הטקסט לשפה שהמודל מבין.
רכיב התרגום הזה הוא הפונקציה CUBEMEMBER, שמקבלת טקסט (למשל: 'שיווק'), ומחזירה אובייקט שהמערכת מזהה.
התחביר:
=CUBEMEMBER(Connection, Member_Expression, [Caption])
שימו לב שבפונקציה צריך לכתוב כל אחד מהרכיבים בתוך גרשיים.
Connection: החיבור למודל.
מכיוון שאנחנו עובדים עם ה Data Model, החיבור הקבוע הוא ThisWorkbookDataModel.
למזלנו, אין צורך להקליד את השם המלא. ברגע שנפתח את הגרשיים עבור האופרטור הראשון, אקסל יציג רשימה של חיבורים, מתוכם נבחר את החיבור הרצוי:
Member_Expression: הנתיב המדויק לפריט בתוך המודל. הוא מורכב משם הטבלה ושם העמודה הרלוונטית.
כדי לבחור את הפריט המתאים ('שיווק'), אנחנו צריכים לעבור בהיררכיה הבאה:
שם הטבלה > העמודה > All > שם הפריט
למזלנו, גם כאן, אין צורך לזכור כלום:
נפתח גרשיים כדי להזין את שם הטבלה הרצויה, ונבחר את הטבלה מתוך הרשימה:
מבלי לסגור את הגרשיים נלחץ על הנקודה, שהיא המפריד בין שם הטבלה לשם השדה (עמודה) שקיימים באותה טבלה:
נלחץ שוב על הנקודה, ונקבל את כל המחלקות (All):
וכעת נלחץ על הנקודה בפעם האחרונה, כדי לקבל את רשימת המחלקות:
נסגור את הגרשיים ונלחץ על אנטר.
זו הפונקציה שהתקבלה:
=CUBEMEMBER("ThisWorkbookDataModel","[Data].[מחלקה].[All].[שיווק]")
והתוצאה שנראה בתא היא שם המחלקה – 'שיווק'.
אם נרצה להציג בתא שם אחר ממה שקיים ברשימה שלנו, נוכל לעשות זאת על ידי הארגומנט השלישי Caption,
שהוא ארגומנט אופציונלי, לדוגמה – אנחנו מעוניינים להציג בתא את הערך 'מחלקת שיווק', בעוד שהמודל מכיר את 'שיווק'.
אז מה בעצם עשינו כאן?
קיבלנו את הנתיב שבו שמור שם האובייקט 'שיווק', כך שנוכל להשתמש בו בהמשך כדי לשלוף את הערכים המשויכים לאובייקט הזה.
ודוגמה מהעולם האמיתי: יש לנו רשימת פרמטרים שאנחנו רוצים לבדוק, שכתובים כטקסט בתוך התא.
אנחנו רוצים לגשת אל הערכים המשויכים אליהם, דרך מודל הנתונים.
כדי לעשות זאת, ניצור עמודת עזר, שתציג את אותם השמות, אבל בדרך שתאפשר לנו לשלוף אותם, באמצעות הפונקציה CUBEMEMBER.
כעת יש לנו רשימת ערכים טקסטואלים, ובעמודת עזר את אותם הערכים, אך הפעם בתוך הפונקציה.
נוכל להסתיר את עמודת העזר הזאת, אך כדי לקבל את הערכים, נצטרך להפנות את הנוסחאות אל התאים המוסתרים, שמכילים את הנתיב.
במאמר הבא נלמד על הפונקציה CUBEVALUE שמיועדת לשליפת הערכים על פי מה שהתקבל בפונקציה CUBEMEMBER,
ונראה דוגמאות מוחשיות לעבודה עם הפונקציות הנ"ל.
שאלות ותשובות בנושא שימוש בפונקציית CUBEMEMBER
שאלה:
מהו התפקיד המרכזי של הפונקציה CUBEMEMBER בעבודה מול מודל נתונים?
תשובה:
פונקציות CUBE אינן יודעות לעבוד עם טקסט חופשי, ולכן תפקיד הפונקציה הוא לתרגם טקסט המייצג נתיב לפריט (כמו שם של מחלקה מתוך עמודת המחלקות)
לאובייקט שמערכת מודל הנתונים מסוגלת לזהות ולעבוד איתו.
שאלה:
כיצד מגדירים את החיבור למודל הנתונים הפנימי (Power Pivot) בתוך הפונקציה?
תשובה:
מכיוון שהפונקציה חייבת לפנות לחיבור חיצוני ולא לתאים רגילים, יש להזין כארגומנט הראשון את שם החיבור הקבוע "ThisWorkbookDataModel",
המפנה את הנוסחה למודל הנתונים הפנימי של קובץ האקסל.
שאלה:
כיצד ניתן להציג בתא שם שונה מהשם הרשמי הקיים במודל הנתונים?
תשובה:
ניתן להשתמש בארגומנט השלישי והאופציונלי של הפונקציה (Caption).
ארגומנט זה מאפשר להציג בתא כיתוב ידידותי או מותאם אישית (למשל "מחלקת שיווק") בעוד שהפונקציה שומרת על הקישור לאובייקט המקורי במודל ("שיווק").