דיאטה למודל הנתונים: כך תהפכו את דוחות ה-Power BI שלכם למהירים ויעילים יותר

אופטימיזציה ב-Power BI

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

מה קורה מאחורי הקלעים? האויב שנקרא Cardinality

כדי לייעל את המודל, אנחנו חייבים להבין איך Power BI שומר את הנתונים.
המנוע של Power BI (שנקרא VertiPaq) הוא מנוע In-Memory שדוחס את הנתונים בעמודות, כדי לתפוס פחות מקום בזיכרון.
כאן נכנס המושג הקריטי Cardinality (קרדינליות – מספר הערכים הייחודיים):
ככל שבעמודה מסוימת יש יותר ערכים ייחודיים, למנוע קשה יותר לדחוס אותה.
עמודה של מגדר (זכר/נקבה) לדוגמה, נדחסת בקלות רבה משום שיש בה שני ערכים בלבד.
עמודה של מספר הזמנה או תאריך ושעה, שבהם הערכים ייחודיים ברובם, תופסת מקום רב בזיכרון ומאטה את כל המודל.

אופטימיזציה כבר בשלב ה-Power Query

הכלל הראשון באופטימיזציה הוא: אם זה לא נחוץ לדוח, זה לא צריך להיות במודל.
הסרת עמודות מיותרות: עברו על הטבלאות שלכם ב-Power Query. האם אתם באמת צריכים את עמודת ה-ID הפנימית של מערכת ה-CRM?
האם עמודת "הערות חופשיות" נחוצה לחישובים?
כל עמודה שאתם מסירים מקטינה את נפח המודל ומשפרת את מהירות השאילתות.

מוקש ה-DateTime: אחד הגורמים הנפוצים ביותר לניפוח מיותר של מודל הנתונים הוא שימוש לא מבוקר בעמודות הכוללות תאריך ושעה.
כדי להבין למה זה קורה, אנחנו צריכים לחזור למושג ה-Cardinality (קרדינליות):
בכל עמודה, Power BI מנסה לדחוס את הנתונים על ידי זיהוי ערכים ייחודיים.
בעמודת DateTime, המנוע שומר ערך נפרד עבור כל צירוף של תאריך ושעה, כך שבסופו של דבר, אתם עלולים להגיע למצב שבו הדחיסה בעמודה הזו היא מינימלית, ותתפוס מקום כמעט כמספר הרשמות הקיימות בטבלה (שיכולות להגיע גם למיליונים).
הכלל הראשון: האם אתם באמת צריכים את השעה?
במקרים רבים, אנחנו מייבאים עמודת DateTime פשוט כי כך היא מופיעה במסד הנתונים, למרות שבפועל אנחנו מנתחים את המכירות או הביצועים ברמה יומית בלבד.
אם אין צורך בשעה לצורך הניתוח, אל תשאירו אותה שם. פרמטו את העמודה כבר בשלב ה-Power Query כסוג נתונים "Date" בלבד.
הפעולה הזו תצמצם את מספר הערכים הייחודיים ממאות אלפים (צירופי זמן) לכמה מאות או אלפים בודדים (ימים), ותוריד את נפח העמודה בזיכרון בצורה דרמטית.
מה עושים כשחייבים לנתח לפי שעות?
ישנם מקרים, כמו בניתוח עומסים במוקד טלפוני או כניסות לאתר, שבהם השעה היא קריטית. כאן יש לנו שתי אפשרויות אופטימיזציה:
שימוש בשעות עגולות: אם הניתוח שלכם לא דורש דיוק של דקות, עגלו את השעה (למשל, כל הפעולות שקרו בין 08:00 ל-08:59 יירשמו כ-08:00).
הצמצום הזה מוריד את הקרדינליות מ-1,440 ערכים אפשריים ביממה (דקות) ל-24 ערכים בלבד.
פיצול לשתי עמודות נפרדות: אם אתם חייבים לשמור על רמת הדיוק המקורית, הפתרון האופטימלי הוא לפצל את העמודה ב-Power Query לשתי עמודות נפרדות:
עמודת תאריך (Date) ועמודת שעה (Time).

איך פיצול לשתי עמודות חוסך זיכרון?

זה עשוי להישמע פרדוקסלי – איך שתי עמודות תופסות פחות מקום מעמודה אחת? התשובה טמונה בדרך שבה מנוע ה-VertiPaq דוחס נתונים:
בעמודה אחת (DateTime): המנוע צריך לנהל אינדקס של צירופים ייחודיים. במיליון שורות עם תאריכים על פני שנתיים ושעות ברמת הדקה, יכולים להיות מאות אלפי צירופים ייחודיים.
בפיצול לשתי עמודות, לעומת זאת, עמודת התאריך תכיל רק כ-730 ערכים ייחודיים (מספר הימים בשנתיים).
עמודת השעה תכיל לכל היותר 1,440 ערכים ייחודיים (דקות ביממה) או 86,400 (שניות ביממה).
במקום לאחסן אינדקס ענק של צירופים מורכבים, המנוע מאחסן שני אינדקסים קטנים ופשוטים בהרבה.
התוצאה היא מודל רזה יותר, רענון נתונים מהיר יותר ושאילתות DAX שמגיבות במהירות.

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

מבנה המודל: Star Schema (מודל כוכב)

בעולם ה-BI, הדרך המהירה ביותר לעבוד היא באמצעות מודל כוכב (Star Schema).
המבנה הזה מפריד בין טבלאות עובדות (Facts – למשל מכירות) לבין טבלאות מימדים (Dimensions – למשל לקוחות או מוצרים).
הימנעו מטבלה אחת שטוחה הכוללת את כל הנתונים.
מנוע ה-DAX עובד בצורה האופטימלית ביותר כשהוא יכול לסנן טבלת מימדים קטנה, ולהשליך את הסינון על טבלת עובדות גדולה דרך קשר של יחיד לרבים (One-to-Many).
בנוסף, השתדלו להימנע ככל הניתן מקשרים דו-כיווניים (Bi-directional Filters) אלא אם כן אין ברירה אחרת, משום שהם מייצרים עומס חישובי מיותר בכל אינטראקציה בדוח.

כתיבת DAX חכם וחסכוני

לאחר שהמודל רזה ומסודר, הנה כמה טיפים לכתיבה נכונה של נוסחאות:
משתנים (VAR): שימוש במשתנים בתוך נוסחת DAX הוא לא רק עניין של סדר, אלא של ביצועים.
כשאתם מגדירים משתנה, Power BI מחשב אותו פעם אחת ומשתמש בתוצאה לאורך כל הנוסחה, במקום לחשב את אותו הביטוי שוב ושוב.
מדדים (Measures) על פני עמודות מחושבות: עמודות מחושבות (Calculated Columns) תופסות מקום קבוע בזיכרון ה-RAM ומחושבות בזמן רענון הנתונים.
מדדים, לעומת זאת, מחושבים בזמן אמת רק כשצריך אותן. ככלל אצבע – אם אפשר לבצע את החישוב כמדד, עשו זאת.
פונקציית DIVIDE: תמיד העדיפו את DIVIDE(a, b) על פני a / b .
מעבר לטיפול האוטומטי בשגיאת חילוק ב-0, היא מותאמת טוב יותר לביצועי המנוע.

בדיקת ביצועים בזמן אמת: Performance Analyzer

איך תדעו מה בדיוק מעכב את הדוח שלכם? ב-Power BI Desktop קיימת לשונית Optimize ובה הכלי PerformanceAnalyzer.
הפעילו את ההקלטה, ורעננו את הוויז'ואלס.
הכלי יראה לכם בדיוק כמה מילי-שניות לוקח לכל אלמנט להיטען, ויפריד בין זמן ה-DAX לזמן התצוגה הוויזואלית.
אם אתם רואים ויז'ואל שלוקח לו יותר מ ms1000, לרוב כדאי לבדוק את המדדים שלו (1000 ms הוא כלל אצבע, שנובע גם מהסבלנות של המשתמש…)

סיכום

אופטימיזציה היא לא פעולה חד-פעמית, אלא הרגל של עבודה נכונה.
על ידי הסרת נתונים מיותרים ב-Power Query, שמירה על מבנה Star Schema וכתיבת DAX יעיל עם משתנים,
תוכלו להבטיח שהדוחות שלכם יישארו מהירים, מקצועיים ומגיבים – גם כשהנתונים שלכם ימשיכו לגדול.

שאלות ותשובות בנושא אופטימיזציית מודל נתונים ב-Power BI

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

שאלה:
מהו היתרון בשימוש במשתנים (VAR) בתוך נוסחאות DAX?
תשובה:
השימוש במשתנים משפר את הביצועים מכיוון ש-Power BI מחשב את המשתנה פעם אחת בלבד ומשתמש בתוצאה לאורך כל הנוסחה.
ללא משתנים, המנוע עלול להיאלץ לחשב את אותו ביטוי שוב ושוב בתוך אותה נוסחה, מה שיוצר עומס חישובי מיותר.

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