ב- Excel, באפשרותך ליצור מודלי נתונים המכילים מיליוני שורות ולאחר מכן לבצע ניתוח נתונים רב-עוצמה מול מודלים אלה. ניתן ליצור מודלי נתונים עם או בלי התוספת Power Pivot כדי לתמוך במספר כלשהו של טבלאות PivotTable, תרשימים ופריטים חזותיים של Power View באותה חוברת עבודה.
למרות שניתן לבנות בקלות מודלי נתונים ענקיים ב- Excel, יש כמה סיבות שלא לעשות זאת. ראשית, מודלים גדולים המכילים מספר רב של טבלאות ועמודות הם מוגזמים עבור רוב הניתוחים, ויוצרים רשימת שדות מסורבלת. שנית, מודלים גדולים משתמשים בזיכרון יקר ערך, מה שמשפיע לרעה על יישומים ודוחות אחרים החולקים את אותם משאבי מערכת. לבסוף, ב- Microsoft 365, הן SharePoint Online והן Excel Web App מגבילים את הגודל של קובץ Excel ל- 10 MB. עבור מודלי נתונים של חוברת עבודה המכילים מיליוני שורות, תיתקל במגבלת ה- 10 MB די מהר. ראה מפרט ומגבלות של מודל נתונים.
במאמר זה, תלמד כיצד לבנות מודל בנוי בצורה הדוקה, שקל יותר לעבוד איתו ומשתמש בפחות זיכרון. הקדשת זמן ללימוד שיטות עבודה מומלצות בעיצוב מודלים יעיל תשתלם בהמשך הדרך עבור כל מודל שתיצור ותשתמש בו, בין אם אתה מציג אותו ב- Excel, Microsoft 365 SharePoint Online, ב- Office Web Apps Server או ב- SharePoint.
שקול גם להפעיל את Workbook Size Optimizer (ממטב גודל חוברות העבודה). כלי זה מנתח את חוברת העבודה של Excel ואם הדבר אפשרי, דוחס אותה עוד יותר. הורד את Workbook Size Optimizer (ממטב גודל חוברות העבודה).
במאמר זה
יחסי דחיסה ומנגנון הניתוח בתוך הזיכרון
מודלי נתונים ב- Excel משתמשים במנגנון הניתוח בזיכרון לאחסון נתונים בזיכרון. המנוע מיישם טכניקות דחיסה חזקות כדי להפחית את דרישות האחסון, ומכווץ ערכת תוצאות עד שהיא שבריר מגודלה המקורי.
בממוצע, ניתן לצפות שמודל נתונים יהיה קטן פי 7 עד פי 10 מאותם נתונים בנקודת המקור שלו. לדוגמה, אם אתה מייבא 7 MB של נתונים ממסד נתונים של SQL Server, מודל הנתונים ב- Excel יכול בקלות להיות 1 MB או פחות. מידת הדחיסה שמושגת בפועל תלויה בעיקר במספר הערכים הייחודיים בכל עמודה. ככל שיש יותר ערכים ייחודיים, כך נדרש יותר זיכרון כדי לאחסן אותם.
למה אנחנו מדברים על דחיסה וערכים ייחודיים? מכיוון שבניית מודל יעיל שממזער את השימוש בזיכרון כרוכה בהגדלה של מידת הדחיסה, והדרך הקלה ביותר לעשות זאת היא להיפטר מעמודות שאינך זקוק להן באמת, במיוחד אם עמודות אלה כוללות מספר רב של ערכים ייחודיים.
הערה
ההבדלים בדרישות האחסון עבור עמודות בודדות עשויים להיות עצומים. במקרים מסוימים, עדיף שיהיו עמודות מרובות עם מספר נמוך של ערכים ייחודיים מאשר עמודה אחת עם מספר גבוה של ערכים ייחודיים. הסעיף העוסק באופטימיזציות של Datetime מתאר טכניקה זו בפירוט.
אין כמו עמודה שאינה קיימת עבור שימוש מועט בזיכרון
העמודה החסכונית ביותר בזיכרון היא זו שמעולם לא ייבאת מלכתחילה. אם ברצונך לבנות מודל יעיל, עיין בכל עמודה ושאל את עצמך אם היא תורמת לניתוח שברצונך לבצע. אם לא, או אם אינך בטוח, אל תכלול אותה. תמיד תוכל להוסיף עמודות חדשות במועד מאוחר יותר אם אתה זקוק להן.
שתי דוגמאות לעמודות שתמיד לא ייכללו
הדוגמה הראשונה מתייחסת לנתונים שמקורם במחסן נתונים. במחסן נתונים, מקובל למצוא חפצים של תהליכי ETL שטוענים ומרעננים נתונים במחסן. עמודות כגון "תאריך יצירה", "תאריך עדכון" ו"הפעלת ETL" נוצרות בעת טעינת הנתונים. אף אחת מעמודות אלה אינה נחוצה במודל ויש לבטל את בחירתה בעת ייבוא נתונים.
הדוגמה השניה כוללת השמטה של עמודת המפתח הראשי בעת ייבוא טבלת עובדות.
טבלאות רבות, כולל טבלאות עובדות, כוללות מפתחות ראשיים. עבור רוב הטבלאות, כגון אלה המכילות נתוני לקוחות, עובדים או מכירות, תרצה במפתח הראשי של הטבלה כדי שתוכל להשתמש בו כדי ליצור קשרי גומלין במודל.
טבלאות עובדות שונות. בטבלת עובדות, המפתח הראשי משמש לזיהוי ייחודי של כל שורה. אף על פי שהוא נחוץ למטרות נרמול, הוא פחות שימושי במודל נתונים שבו ברצונך להשתמש רק בעמודות אלה לניתוח או לביסוס קשרי גומלין בין טבלאות. לכן, בעת ייבוא מטבלת עובדות, אל תכלול את המפתח הראשי שלה. מפתחות ראשיים בטבלת עובדות צורכים כמויות עצומות של שטח במודל, אך אינם מספקים שום תועלת, מכיוון שלא ניתן להשתמש בהם ליצירת קשרי גומלין.
הערה
במחסני נתונים ובמסדי נתונים רב-ממדיים, טבלאות גדולות המורכבות בעיקר מנתונים מספריים מכונות לעתים קרובות "טבלאות עובדות". טבלאות עובדות כוללות בדרך כלל נתוני ביצועים עסקיים או עסקאות, כגון נקודות נתונים של מכירות ועלות, הנצברות ומותאמות ליחידות ארגוניות, למוצרים, לפלחי שוק, לאזורים גיאוגרפיים וכן הלאה. כל העמודות בטבלת עובדות המכילות נתונים עסקיים או שניתן להשתמש בהן להפניה מקושרת לנתונים המאוחסנים בטבלאות אחרות צריכות להיכלל במודל כדי לתמוך בניתוח נתונים. העמודה שברצונך לא לכלול היא עמודת המפתח הראשית של טבלת העובדות, המורכבת מערכים ייחודיים הקיימים רק בטבלת העובדות ולא בשום מקום אחר. מאחר שטבלאות עובדות הן כה גדולות, כמה מהיתרונות הגדולים ביותר ביעילות המודל נגזרים מאי-הכללת שורות או עמודות בטבלאות עובדות.
כיצד לא לכלול עמודות מיותרות
מודלים יעילים מכילים רק את העמודות הדרושות לך בחוברת העבודה. אם ברצונך לקבוע אילו עמודות ייכללו במודל, יהיה עליך להשתמש באשף ייבוא הטבלאות בתוספת Power Pivot כדי לייבא את הנתונים במקום בתיבת הדו-שיח "ייבוא נתונים" ב- Excel.
בעת הפעלת אשף ייבוא הטבלאות, אתה בוחר אילו טבלאות לייבא.
עבור כל טבלה, באפשרותך ללחוץ על לחצן 'הצג בתצוגה מקדימה & מסנן' ולבחור את חלקי הטבלה שאתה זקוק להם באמת. מומלץ לבטל תחילה את סימון כל העמודות ולאחר מכן להמשיך לבדוק את העמודות הרצויות, לאחר שתשקול אם הן נדרשות לצורך הניתוח.
מה לגבי סינון השורות הדרושות בלבד?
טבלאות רבות במסדי נתונים ארגוניים ובמחסני נתונים מכילות נתונים היסטוריים שנצברו במשך תקופות זמן ארוכות. בנוסף, ייתכן שתגלה שהטבלאות שאתה מעוניין בהן מכילות מידע עבור תחומים של העסק שאינם נדרשים עבור הניתוח הספציפי שלך.
באמצעות אשף ייבוא הטבלאות, באפשרותך לסנן נתונים היסטוריים או לא קשורים וכך לחסוך שטח רב במודל. בתמונה הבאה, מסנן תאריכים משמש לאחזור שורות המכילות נתונים עבור השנה הנוכחית בלבד, למעט נתונים היסטוריים שאין בהם צורך.
מה אם אנחנו צריכים את העמודה; האם אנחנו עדיין יכולים להפחית את עלות השטח שלו?
ישנן כמה טכניקות נוספות שבאפשרותך ליישם כדי להפוך עמודה למועמדת טובה יותר לדחיסה. זכור כי המאפיין היחיד של העמודה שמשפיע על הדחיסה הוא מספר הערכים הייחודיים. בסעיף זה, תלמד כיצד ניתן לשנות עמודות מסוימות כדי להפחית את מספר הערכים הייחודיים.
שינוי עמודות DateTime
במקרים רבים, עמודות Datetime תופסות מקום רב. למרבה המזל, קיימות מספר דרכים להפחתת דרישות האחסון עבור סוג נתונים זה. הטכניקות ישתנו בהתאם לאופן השימוש שלך בעמודה ולרמת הנוחות שלך בבניית שאילתות SQL.
העמודות DateTime כוללות חלק תאריך ושעה. כאשר אתה שואל את עצמך אם אתה זקוק לעמודה, שאל את אותה שאלה כמה פעמים עבור עמודת Datetime:
- האם אני זקוק לחלק הזמן?
- האם אני זקוק לחלק הזמן ברמת השעות? דקות?, שניות?, אלפיות השנייה?,
- האם יש לי עמודות Datetime מרובות מכיוון שאני רוצה לחשב את ההפרש ביניהן, או פשוט לצבור את הנתונים לפי שנה, חודש, רבעון וכן הלאה.
האופן שבו אתה משיב על כל אחת מהשאלות האלה קובע את אפשרויות ההתמודדות עם העמודה Datetime.
כל הפתרונות הללו דורשים שינוי של שאילתת SQL. כדי להקל על ביצוע שינויים בשאילתה, עליך לסנן עמודה אחת לפחות בכל טבלה. על-ידי סינון עמודה, אתה משנה את בניית השאילתה מתבנית מקוצרת (SELECT *) למשפט SELECT הכולל שמות עמודות מלאים, שקל הרבה יותר לשנות.
בוא נביט בשאילתות שנוצרות עבורך. מתיבת הדו-שיח 'מאפייני טבלה', באפשרותך לעבור לעורך השאילתות ולראות את שאילתת ה- SQL הנוכחית עבור כל טבלה.
מתוך 'מאפייני טבלה', בחר 'עורך Power Query'.
עורך Power Query מציג את שאילתת ה- SQL המשמשת לאכלוס הטבלה. אם סיננת עמודה כלשהי במהלך הייבוא, השאילתה שלך כוללת שמות עמודות מלאים:
לעומת זאת, אם ייבאת טבלה בשלמותה, מבלי לבטל את הסימון של עמודה כלשהי או להחיל מסנן כלשהו, תראה את השאילתה בשם "בחר * מ-", שיהיה קשה יותר לשנותה:
|
|---|
שינוי שאילתת ה- SQL
כעת, לאחר שאתה יודע כיצד למצוא את השאילתה, באפשרותך לשנות אותה כדי להקטין עוד יותר את גודל המודל שלך.
- עבור עמודות המכילות נתוני מטבע או נתונים עשרוניים, אם אין לך צורך במספרים עשרוניים, השתמש בתחביר זה כדי להיפטר מהמקומות העשרוניים:
"SELECT ROUND([Decimal_column_name],0)... .”
אם אתה זקוק לסנטים, אך לא לשברירי אגורות, החלף את 0 ב- 2. אם אתה משתמש במספרים שליליים, באפשרותך לעגל ליחידות, עשרות, מאות וכן הלאה. - אם יש לך עמודת DateTime בשם dbo. שולחן גדול. [תאריך שעה] ואינך זקוק לחלק 'שעה', השתמש בתחביר כדי להעלים את השעה:
"SELECT CAST (dbo. שולחן גדול. [Date time] as date) AS [Date time]) " - אם יש לך עמודת DateTime בשם dbo. שולחן גדול. [Date Time] ואתה זקוק הן לחלקים 'תאריך' והן ל'שעה', השתמש בעמודות מרובות בשאילתת ה- SQL במקום בעמודת DateTime היחידה:
"SELECT CAST (dbo. שולחן גדול. [date time] as date ) AS [date time],
DatePart(HH, dbo. שולחן גדול. [שעת תאריך]) כ- [Date Time Hours],
DatePart(mi, dbo. שולחן גדול. [שעת תאריך]) כ- [Date Time Minutes],
DatePart(SS, dbo. שולחן גדול. [שעת תאריך]) כ- [Date Time Seconds],
DatePart(ms, dbo. שולחן גדול. [שעת תאריך]) כ: [Date Time milliseconds]"
השתמש במספר העמודות הדרוש לך כדי לאחסן כל חלק בעמודות נפרדות. - אם אתה זקוק לשעות ודקות, ואתה מעדיף אותן יחד כעמודת זמן אחת, באפשרותך להשתמש בתחביר:
Timefromparts(datepart(hh, dbo. שולחן גדול. [תאריך שעה]), datepart(mm, dbo. שולחן גדול. [תאריך שעה])) כ: [Date Time HourMinute] - אם יש לך שתי עמודות תאריך ושעה, כגון [שעת התחלה] ו[שעת סיום], ואתה זקוק להפרש הזמן ביניהן בשניות כעמודה הנקראת [משך זמן], הסר את שתי העמודות מהרשימה והוסף:
"Datediff(ss,[Start Date],[End Date]) as [Duration]"
אם תשתמש במילת המפתח ms במקום ss, תקבל את משך הזמן באלפיות שניה
שימוש במידות מחושבות של DAX במקום בעמודות
אם עבדת עם שפת הביטוי DAX בעבר, ייתכן שאתה כבר יודע שעמודות מחושבות משמשות לגזירת עמודות חדשות בהתבסס על עמודות אחרות במודל, בעוד שמידות מחושבות מוגדרות פעם אחת במודל, אך מוערכות רק כאשר נעשה בהן שימוש ב- PivotTable או בדוח אחר.
אחת השיטות לחיסכון בזיכרון היא להחליף עמודות רגילות או מחושבות במידות מחושבות. הדוגמה הקלאסית היא מחיר ליחידה, כמות וסה"כ. אם יש לך את כל השלושה, תוכל לחסוך מקום על-ידי שמירה על שתיים בלבד וחישוב השלישי באמצעות DAX.
אילו 2 עמודות עליך לשמור?
בדוגמה שלעיל, שמור את Quantity ו- Unit Price. לשניים אלה יש פחות ערכים מאשר הסכום הכולל. כדי לחשב סכום כולל, הוסף מידה מחושבת כגון:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
עמודות מחושבות דומות לעמודות רגילות בכך ששתיהן תופסות מקום במודל. לעומת זאת, מידות מחושבות מחושבות תוך כדי תנועה ואינן תופסות מקום.
סיכום
במאמר זה דיברנו על כמה גישות שיכולות לסייע לכם לבנות מודל יעיל יותר מבחינת זיכרון. הדרך להקטין את גודל הקובץ ואת דרישות הזיכרון של מודל נתונים היא להקטין את המספר הכולל של עמודות ושורות ואת מספר הערכים הייחודיים המופיעים בכל עמודה. הנה כמה טכניקות שכיסינו:
- הסרת עמודות היא, כמובן, הדרך הטובה ביותר לחסוך מקום. החלט אילו עמודות דרושות לך באמת.
- לפעמים ניתן להסיר עמודה ולהחליף אותה במידה מחושבת בטבלה.
- ייתכן שלא תזדקק לכל השורות בטבלה. באפשרותך לסנן החוצה שורות באשף ייבוא הטבלאות.
- באופן כללי, פירוק עמודה בודדת לחלקים נפרדים מרובים הוא דרך טובה להפחית את מספר הערכים הייחודיים בעמודה. כל אחד מהחלקים יכיל מספר קטן של ערכים ייחודיים, והסכום המשולב יהיה קטן יותר מהעמודה המאוחדת המקורית.
- במקרים רבים, יש צורך גם בחלקים נפרדים לשימוש ככלי פריסה בדוחות שלך. בעת הצורך, באפשרותך ליצור הירארכיות מחלקים כגון שעות, דקות ושניות.
- פעמים רבות, עמודות מכילות יותר מידע מכפי שאתה זקוק להן. לדוגמה, נניח שעמודה מאחסנת מספרים עשרוניים, אך החלת עיצוב כדי להסתיר את כל המקומות העשרוניים. עיגול יכול להיות יעיל מאוד בהקטנת הגודל של עמודה מספרית.
כעת, לאחר שעשית כל שביכולתך כדי להקטין את גודל חוברת העבודה, שקול גם להפעיל את Workbook Size Optimizer (ממטב גודל חוברות העבודה). כלי זה מנתח את חוברת העבודה של Excel ואם הדבר אפשרי, דוחס אותה עוד יותר. הורד את Workbook Size Optimizer (ממטב גודל חוברות העבודה).