DAX – מדדים ועמודות מחושבות

נושאים

עקבו אחרינו

מי מפחד ממדדים?

אז כשפגשתי לראשונה את המילה מדד היו לי מלא שאלות:

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

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

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

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

המנוע החישובי של POWER BI נקרא VRTIPAQ – זה מנוע IN MEMORY שמעלה לזיכרון הקאש של המחשב שלכם את כל הטבלאות ואת כל המידע שטענתם לתוך ה POWER BI.

אתם איתי? מעולה נמשיך.

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

DAX היא שפה חישובית שרצה בתוך קובייה מסוג TABULAR (שרת של Analysis server) והיא נמצא כמובן בתוך POWER BI ויכולה להימצא גם במקומות אחרים כמו POWER PIVOT  (באקסל) או SSAS, אבל המשותף לשניהם הוא ששניהם מריצים מאחורי הקלעים קובייה מסוג SQL Server Analysis Services (SSAS).

אז מה זה DAX

DAX הוא אוסף של פונקציות ונוסחאות קבועות שניתן באמצעותם לחשב מדדים שמבוססים המידע שנמצא בתוך המודל שלכם.

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

למה ליצור מדדים

בעיקרון אפשר לוותר על השימוש בשפת ה DAX וליצור דוחות טובים ב POWER BI ללא שימוש ב DAX.

אבל ישנה תקרת זכוכית בהמנעות משימוש ב DAX. ברגע שתרצו ליצור חישובים שהם לא רק אגרגטיביים (כמו SUM או COUNT  וכו') תצטרכו לעשות שימוש ב DAX. לדוגמא, במידה ותרצו לחשב:

  • חישוב של אחוזי הצמיחה של המוצרים השונים השנה בהשוואה לשנה שעברה?
  • חישוב של סה"כ מכירות של המוצרים בצבע אדום בין השנה ללפני שנתיים?

אלו החישוביים האמיתיים שעונים לנו על שאלות עיסקיות קריטיות ולרוב נדרש שימוש ב DAX ובחישובים מורכבים.

מה קורה אחרי שיצרנו מדד? היכן הוא נשמר?

חשוב לציין שלאחר שיצרנו מדדים ושמרנו אותם בשמות ייחודיים אנחנו נשתמש בהם ונציג אותם על גבי הדוח שלנו. חישוב של כל מדד יבוצע ON THE FLY (בזמן אמת) ויחושב לפי ה FILTER CONTEXT הרלוונטי באותו הגרף.

מה זאת אומרת?

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

אני אסביר,

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

בגלל שהמדד מפוזר על פני ימים (ציר ה X) הוא צריך לחשב את עצמו בכל פעם מחדש – כלומר בכל יום כאשר בכל יום ישFILTER CONTEXT  שונה.

אני אחזור, בכל אחד מהימים הפזורים על ציר ה X המדד צריך לחשב את עצמו מחדש.
נסתכל על התאריך המסומן הראשון (1) – כאן עוד לפני שהוא יחשב את המדד ויספור את סה"כ המכירות המודל שלנו יפלטר את עצמו פעמיים בכפוף לשני פילטרים – תאריך 02-JAN-2017 וגם SERVICE DESCRIPTION מסוג ISP. רק אח"כ הוא יחשב את המדד של סה"כ מכירות.

נסתכל על התאריך השני (2) – גם כאן עוד לפני שהוא יחשב את המדד המודל שלנו יפלטר את עצמו על שני פילטרים – תאריך 01-Feb-2017 וגם SERVICE DESCRIPTION מסוג ISP. רק אח"כ הוא יחשב את המדד של סה"כ מכירות.

כלומר אנחנו יכולים להסיק שהמנוע של ה POWER BI עובד מאוד קשה בכדי לחשב את המדדים האלו, למזלנו הוא עושה את זה בצורה מדהימה ומהירה (כל עוד אנחנו שומרים עליו וא מסבכים אותו עם חישובים מיותרים וסבוכים).

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

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

מה זה עמודה מחושבת?

עמודה מחושבת הינה עמודה שנוצרה באמצעות שפת DAX (כמו מדד) והיא מחושבת פעם אחת בזמן הריענון של המודל – זמן ה PROCESS

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

אני אתן דוגמא פשוטה כדי להמחיש.

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

עמודות שהיו במקור הנתונים עמודה מחושבת
מספר חשבונית שם המוצר מחיר ליחידה כמות כמות X מחיר ליח'
1 מוצר א' 10 2 20
2 מוצר ב' 5 3 15

בניתי עמודה מחושבת שמייצגת את ההכנסות – מחיר ליחידה כפול כמות.

כעת כל שנותר הוא להשתמש בעמודה המחושבת על גבי הדוח בצורה אגרגטיבית ולקבל את סה"כ ההכנסות = 35.

במידה היינו בחורים לייצר מדד במקום עמודה מחושבת היינו מעוותים את התוצאה. למה?

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

זה אומר שכדי לחשב את סה"כ ההכנסות המדד יחשב את סה"כ המחיר ליחידה 15 (10+5) כפול הכמות 5 (3+2) ויקבל תוצאה של 60 (15*5).

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

סיכום ההבדלים בין עמודה מחושבת לבין מדד:

קטגוריה מדד עמודה מחושבת
שפה DAX DAX
היכן נשמר המידע? אינה נשמרת בזיכרון אלא נשמר רק אופן החישוב נשמרת בזיכרון RAM
מתי מחושב מחושבת כל פעם מחדש בזמן השימוש בה מחושבת פעם אחת בזמן ריענון
סוג החישוב בד"כ תוצאה של חישוב אגרגטיבי (מצטבר, כמו SUM וכו) מחושבת שורה אחרי שורה במודל הנתונים
היכן נמצאים? ניתן לראות את התוצאה שלה כאשר נשתמש במדד על גבי דוח מסויים ניתן לראות את התוצאה שלה בתור עמודה מחושבת באזור ה DATA TAB יחד עם שאר הטבלאות שיש במודל
מתי נייצר אותם? ברוב המקרים ניתן לוותר על עמודה מחושבת וליצור אותה בשלבים מוקדמים יותר – POWER QUERT עושה את מצויין או אפילו בשאילתת SQL במידה ופונים לדאטה בייס. במידה ויש חישובים בסיסיים מצטברים כמו SUM COUNT אז ניתן לוותר . רמה טיפה יותר גבוה חייב לייצר מדד לחישוב

סיכום

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

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

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

אני מצרף כאן רשימה של לינקים לאתרים שהם ניתן למצוא מומחים מהשורה הראשונה שעוסקים ב DAX וב POWER QUERY.

URL Blog Name
http://www.powerquery.training/portfolio Power Query Training
http://samuelvanga.com Sam Vanga
https://sqlserverbiblog.wordpress.com Paul Turley’s SQL Server BI Blog
http://www.powerpivotpro.com PowerPivotPro
http://www.sqlbi.com SQLBI
https://cwebbbi.wordpress.com Chris Webb’s BI Blog
http://www.powerpivotblog.nl Kasper de Jonge PowerPivot and Power BI Blog
http://www.excelguru.ca/blog The Ken Puls (Excelguru) Blog
http://tinylizard.com Tiny Lizard
https://eriksvensen.wordpress.com Erik Svensen
http://blog.gbrueckl.at Gerhard Brueckl’s BI Blog
http://jenstirrup.com jenstirrup
https://powerplanning.wordpress.com powerplanning
http://christopherfinlan.com Christopher Finlan
http://www.jenunderwood.com Business Intelligence & Advanced Analytics
http://exceleratorbi.com.au ExceleratorBI