ב- Excel 2013 ואילך, באפשרותך ליצור מודלי נתונים המכילים מיליוני שורות ולאחר מכן לבצע ניתוח נתונים רב-עוצמה מול מודלים אלה. ניתן ליצור מודלי נתונים עם או בלי Power Pivot כדי לתמוך במספר כלשהו של טבלאות PivotTable, תרשימים ופריטים חזותיים של Power View באותה חוברת עבודה.
הערה: מאמר זה מתאר מודלי נתונים ב- Excel 2013. עם זאת, אותם תכונות של מידול נתונים ו- Power Pivot שהוצגו ב- Excel 2013 חלות גם על Excel 2016. אין הבדל קטן ביעילות בין גירסאות אלה של Excel.
למרות שניתן לבנות בקלות מודלי נתונים עצומים ב- Excel, ישנן כמה סיבות לכך שלא. תחילה, מודלים גדולים המכילים מספר רב של טבלאות ועמודות משתמשים באופן מוגזם ברוב האנליטיים, ופונים לרשימת שדות מסורבלת. שנית, מודלים גדולים משתמשים בזיכרון רב-ערך, ומשפיעים באופן שלילי על יישומים ודוחות אחרים בעלי אותם משאבי מערכת. לבסוף, ב- Microsoft 365, הן SharePoint Online והן Excel Web App מגבילים את הגודל של קובץ Excel ל- 10 MB. עבור מודלי נתונים של חוברות עבודה המכילים מיליוני שורות, אתה נתקל במהירות רבה במגבלה של 10 MB. ראה מפרט ומגבלות של מודל נתונים.
במאמר זה, תלמד כיצד לבנות מודל בנוי היטב וקל יותר לעבוד איתו ומשתמש בפחות זיכרון. הקדשת הזמן כדי ללמוד שיטות עבודה מומלצות בעיצוב מודל יעיל תחסוך לך את הזמן בכל מודל שתיצור ותשתמש בו, בין אם אתה מציג אותו ב- Excel 2013, ב- Microsoft 365 SharePoint Online, ב- Office Online Server או ב- SharePoint 2013.
שקול גם להפעיל את Workbook Size Optimizer (ממטב גודל חוברות העבודה). כלי זה מנתח את חוברת העבודה של Excel ואם הדבר אפשרי, דוחס אותה עוד יותר. הורד את Workbook Size Optimizer.
במאמר זה
יחסי דחיסה ומנגנון הניתוח בתוך הזיכרון
מודלי נתונים ב- Excel משתמשים במנגנון הניתוח בתוך הזיכרון כדי לאחסן נתונים בזיכרון. המנוע מיישם טכניקות דחיסה רבות-עוצמה כדי להפחית את דרישות האחסון, ולצמצם ערכת תוצאות עד שהיא תפחת מגודלה המקורי.
בממוצע, ניתן לצפות שמודל נתונים יהיה קטן פי 7 עד 10 מאותם נתונים בנקודת מוצאו. לדוגמה, אם אתה מייבא 7 MB של נתונים ממסד נתונים של SQL Server נתונים, מודל הנתונים ב- Excel יכול להיות בקלות 1 MB או פחות. מידת הדחיסה שהושגה בפועל תלויה בראש ובראשונה במספר הערכים הייחודיים בכל עמודה. מספר הערכים הייחודיים גדול יותר, כך נדרש זיכרון נוסף כדי לאחסן אותם.
למה אנחנו מדברים על דחיסה וערכים ייחודיים? מכיוון שהבנייה של מודל יעיל הממזערת את השימוש בזיכרון כוללת את הגדלת הדחיסה, והדרך הקלה ביותר לעשות זאת היא להיפטר מהעמודות שאינך זקוק לה באמת, במיוחד אם עמודות אלה כוללות מספר גדול של ערכים ייחודיים.
הערה: ההבדלים בדרישות האחסון עבור עמודות בודדות יכולים להיות ענקיים. במקרים מסוימים, עדיף לכלול עמודות מרובות עם מספר נמוך של ערכים ייחודיים במקום עמודה אחת עם מספר גבוה של ערכים ייחודיים. הסעיף בנושא מיטוב תאריך ושעה מכסה טכניקה זו בפירוט.
דבר אינו מנצח עמודה שאינה קיימת עבור שימוש בזיכרון נמוך
העמודה היעילה ביותר בזיכרון היא העמודה שמעולם לא ייבאת מלכתחילה. אם ברצונך לבנות מודל יעיל, עיין בכל עמודה ושאל את עצמך אם היא תורמת לניתוח שברצונך לבצע. אם לא, או שאינך בטוח, אל תמיט את זה. תוכל תמיד להוסיף עמודות חדשות מאוחר יותר אם תזדקק להן.
שתי דוגמאות של עמודות שיש תמיד לא לכלל
הדוגמה הראשונה מתייחסת לנתונים שמקורם במחסן נתונים. במחסן נתונים, זה נפוץ למצוא ממצאים של תהליכי ETL שטענים ורענון נתונים במחסן. עמודות כגון "create date", "update date" ו- "ETL run" נוצרות כאשר הנתונים נטענים. אין צורך להוסיף אף אחת מעמודות אלה למודל ויש לבטל את הבחירה בה בעת ייבוא נתונים.
הדוגמה השניה כוללת השמטה של עמודת המפתח הראשי בעת ייבוא טבלת עובדה.
טבלאות רבות, כולל טבלאות עובדה, כוללות מפתחות ראשיים. עבור רוב הטבלאות, כגון טבלאות המכילות נתוני לקוח, עובד או מכירות, מומלץ להשתמש במפתח הראשי של הטבלה כדי שתוכל להשתמש בה כדי ליצור קשרי גומלין במודל.
טבלאות עובדה הן שונות. בטבלת עובדה, המפתח הראשי משמש לזיהוי ייחודי של כל שורה. למרות הצורך למטרות נרמול, היא פחות שימושית במודל נתונים שבו ברצונך להשתמש רק בעמודות המשמשות לניתוח או ליצירת קשרי גומלין בין טבלאות. מסיבה זו, בעת ייבוא מטבלת עובדה, אל תכלול את המפתח הראשי שלה. מפתחות ראשיים בטבלת עובדה צורכים כמויות עצומות של שטח במודל, אך לא מספקים תועלת, כי לא ניתן להשתמש בהם ליצירת קשרי גומלין.
הערה: במחסנים נתונים ובמסדי נתונים רב-ממדיים, טבלאות גדולות המורכבות בעיקר מנתונים מספריים נקראות לעתים קרובות "טבלאות עובדה". טבלאות עובדה כוללות בדרך כלל נתונים של ביצועים עסקיים או טרנזקציות, כגון נקודות נתונים של מכירות ועלות שנצברות ויתואמות יחידות ארגוניות, מוצרים, מקטעי שוק, אזורים גיאוגרפיים וכן הלאה. יש לכלול במודל את כל העמודות בטבלת עובדה המכילות נתונים עסקיים או שניתן להשתמש בהם להפניה מקושרת לנתונים המאוחסנים בטבלאות אחרות כדי לתמוך בניתוח נתונים. העמודה שברצונך לא לכלול היא עמודת המפתח הראשית של טבלת עובדה, המורכבת מערכים ייחודיים הקיימים רק בטבלת העובדה ולא בשום מקום אחר. מאחר שטבלאות עובדה הן כה עצומות, חלק מהרווחים הגדולים ביותר ביעילות המודל נגזרים מהכללת שורות או עמודות מטבלאות עובדה.
כיצד לא לכלול עמודות מיותרות
מודלים יעילים מכילים רק את העמודות הדרושות לך בפועל בחוברת העבודה. אם ברצונך לקבוע אילו עמודות ייכללו במודל, יהיה עליך להשתמש באשף ייבוא הטבלאות בתוספת Power Pivot כדי לייבא את הנתונים במקום בתיבת הדו-שיח "ייבוא נתונים" ב- Excel.
בעת הפעלת אשף ייבוא הטבלאות, אתה בוחר אילו טבלאות לייבא.
עבור כל טבלה, באפשרותך ללחוץ על לחצן תצוגה & בתצוגה מקדימה ולבחור את חלקי הטבלה הדרושים לך באמת. מומלץ לבטל תחילה את הסימון של כל העמודות ולאחר מכן להמשיך לבדוק את העמודות הרצויות, לאחר שתשקול אם הן נדרשות לצורך הניתוח.
מה לגבי סינון השורות הדרושות בלבד?
טבלאות רבות במסדי נתונים ארגוניים ומחסנים נתונים מכילות נתונים היסטוריים שהצטברו במשך פרקי זמן ארוכים. בנוסף, ייתכן שתימצא שהטבלאות הרצויות מכילות מידע עבור תחומי העסק שאינם דרושים לניתוח הספציפי שלך.
באמצעות אשף ייבוא הטבלאות, באפשרותך לסנן נתונים היסטוריים או לא קשורים, ולפיכך לחסוך שטח רב במודל. בתמונה הבאה, מסנן תאריכים משמש לאחזור שורות המכילות נתונים עבור השנה הנוכחית בלבד, למעט נתונים היסטוריים שלא יהיו דרושים.
מה אם נצטרך את העמודה; האם עדיין נוכל להפחית את עלות השטח שלו?
יש כמה טכניקות נוספות שניתן להחיל כדי להפוך עמודה למועמד טוב יותר לדחיסה. זכור כי המאפיין היחיד של העמודה המשפיע על הדחיסה הוא מספר הערכים הייחודיים. בסעיף זה, תלמד כיצד ניתן לשנות עמודות מסוימות כדי להפחית את מספר הערכים הייחודיים.
שינוי עמודות תאריך/שעה
במקרים רבים, עמודות Datetime צורבות שטח רב. למרבה המזל, קיימות מספר דרכים להפחית את דרישות האחסון עבור סוג נתונים זה. הטכניקות ישתנו בהתאם לאופן השימוש בעמודה, וברמת הנוחות שלך בבניית שאילתות SQL.
עמודות תאריך ושעה כוללות חלק תאריך ושעה. כאשר אתה שואל את עצמך אם אתה זקוק לעמודה, שאל את אותה שאלה כמה פעמים עבור העמודה 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. טבלאות גדולות. [תאריך ושעה] כתאריך) AS [Date time]) "
-
אם יש לך עמודת Datetime בשם dbo. טבלאות גדולות. [תאריך ושעה] ואתה זקוק הן לחלקי התאריך והן בחלקי השעה, השתמש בעמודות מרובות בשאילתת ה- SQL במקום בעמודה ה בודדת Datetime:
"SELECT CAST (dbo. טבלאות גדולות. [תאריך ושעה] כתאריך ) AS [Date Time],
datepart(hh, dbo. טבלאות גדולות. [תאריך ושעה]) as [Date Time Hours],
datepart(mi, dbo. טבלאות גדולות. [תאריך ושעה]) as [Date Time Minutes],
datepart(ss, dbo. טבלאות גדולות. [תאריך ושעה]) as [Date Time Seconds],
datepart(ms, dbo. טבלאות גדולות. [תאריך ושעה]) as [Date Time Milliseconds]"
השתמש בעמודות רבות ככל שתצטרך כדי לאחסן כל חלק בעמודות נפרדות.
-
אם דרושות לך שעות ודקות, ואתה מעדיף אותן יחד כעמודה חד-פעמית, באפשרותך להשתמש בתחביר :
Timefromparts(datepart(hh, dbo. טבלאות גדולות. [תאריך ושעה]), datepart(mm, dbo. טבלאות גדולות. [תאריך ושעה])) as [Date Time HourMinute]
-
אם יש לך שתי עמודות תאריך/שעה, כגון [שעת התחלה] ו- [שעת סיום], ומה שאתה באמת זקוק לו הוא הפרש הזמן ביניהן בשניות כעמודה בשם [משך זמן], הסר את שתי העמודות מהרשימה והוסף:
"datediff(ss,[Start Date],[End Date]) as [Duration]"
אם תשתמש במילת המפתח ms במקום ב- ss, תקבל את משך הזמן באלפיות השניה
שימוש בממידות מחושבות של DAX במקום בעמודות
אם כבר עבדת עם שפת הביטויים של DAX, ייתכן שכבר ידעת שעמודות מחושבות משמשות לגזור עמודות חדשות בהתבסס על עמודה אחרת במודל, בעוד שמידות מחושבות מוגדרות פעם אחת במודל, אך מוערכות רק בעת שימוש ב- PivotTable או בדוח אחר.
שיטה אחת לחיסכון בזיכרון היא להחליף עמודות רגילות או מחושבות בממידות מחושבות. הדוגמה הקלאסית היא מחיר ליחידה, כמות וסכום כולל. אם יש לך את כל השלושה, באפשרותך לחסוך מקום על-ידי שמירה על שניים בלבד וחישוב השלישי באמצעות DAX.
אילו 2 עמודות עליך לשמור?
בדוגמה שלעיל, שמור את כמות ואת מחיר יחידה. לשניים אלה יש פחות ערכים מהסכום הכולל. כדי לחשב את הסכום הכולל, הוסף מידה מחושבת כגון:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
עמודות מחושבות הן כמו עמודות רגילות ששניהם צורכו שטח במודל. לעומת זאת, מידות מחושבות במהירות ואינן לוקחות מקום.
סיכום
במאמר זה, דיברנו על כמה גישות שעשויות לעזור לך לבנות מודל יעיל יותר לזיכרון. הדרך להקטין את גודל הקובץ ואת דרישות הזיכרון של מודל נתונים היא להפחית את המספר הכולל של עמודות ושורות, ואת מספר הערכים הייחודיים המופיעים בכל עמודה. להלן כמה טכניקות שאנו כלולים הבאות:
-
הסרת עמודות היא כמובן הדרך הטובה ביותר לחסוך מקום. החלט אילו עמודות דרושות לך באמת.
-
לעתים ניתן להסיר עמודה ולהחליף אותה במדד מחושב בטבלה.
-
ייתכן שלא תזדקק לכל השורות בטבלה. באפשרותך לסנן שורות באשף ייבוא הטבלאות.
-
באופן כללי, הפרד עמודה בודדת לחלקים נפרדים מרובים הוא דרך טובה להפחית את מספר הערכים הייחודיים בעמודה. לכל אחד מהרכיבים יהיה מספר קטן של ערכים ייחודיים, והסכום המשולב יהיה קטן יותר מהעמודה המאוחדת המקורית.
-
במקרים רבים, עליך גם להשתמש בחלקים הייחודיים ככלי פריסה בדוחות שלך. בהתאם לצורך, באפשרותך ליצור הירארכיות בחלקים כגון 'שעות', 'דקות' ו'שניות'.
-
פעמים רבות, עמודות מכילות מידע רב יותר מכפי שאתה זקוק להן. לדוגמה, נניח שעמודה מאחסנה מקומות עשרוניים, אך החלת עיצוב כדי להסתיר את כל המקומות העשרוניים. עיגול יכול להיות יעיל מאוד בהקטנת גודל עמודה מספרית.
כעת, לאחר שעשית מה שניתן כדי להקטין את גודל חוברת העבודה, שקול להפעיל גם את 'ממטב גודל חוברת העבודה'. כלי זה מנתח את חוברת העבודה של Excel ואם הדבר אפשרי, דוחס אותה עוד יותר. הורד את Workbook Size Optimizer.
