יצירת מודל נתונים חסכוני בזיכרון באמצעות Excel ואת התוספת Power Pivot

חשוב: מאמר זה תורגם בתרגום מכונה, ראה כתב ויתור. תוכל למצוא את הגרסה באנגלית של המאמר כאן לעיונך.

ב- Excel 2013 ואילך, באפשרותך ליצור מודלי נתונים המכיל מיליוני שורות, ולאחר מכן לבצע ניתוח נתונים רב-עוצמה כנגד מודלים אלה. ניתן ליצור מודלי נתונים עם או בלי תוספת Power Pivot לתמיכה מספר כלשהו של טבלאות Pivottable, תרשימים ופריטים חזותיים של Power View באותה חוברת עבודה.

הערה: מאמר זה מתאר מודלי נתונים ב- Excel 2013. עם זאת, אותם נתונים מידול והתכונות Power Pivot הציגה ב- Excel 2013 גם להחיל ל- Excel 2016. קיים הבדל באופן יעיל מעט בין גירסאות אלה של Excel.

למרות שניתן בקלות לבנות מודלי נתונים גדול ב- Excel, קיימות מספר סיבות לא. מודלים הראשונה, גדולים המכילים multitudes של טבלאות ועמודות הם overkill עבור רוב ניתוחים ובצע עבור רשימת שדות מסורבל. השתמש מודלים השניה, גדולים זיכרון רב ערך, להשפיע לרעה להשפיע על האחרות יישומים ודוחות משתף אותם משאבי מערכת. לבסוף, ב- Office 365 SharePoint Online והן ב- Excel Web App להגביל את הגודל של קובץ Excel ל- 10 MB. עבור מודלי נתונים בחוברת עבודה המכילים מיליוני שורות, תיתקל המגבלה של 10MB די במהירות. ראה מודל הנתונים מפרט ומגבלות.

במאמר זה, תלמד כיצד לבנות מודל חסכוני הצורך פחות זיכרון ושקל יותר לעבוד איתו. אם תקדיש מזמנך ללמוד את שיטות העבודה המומלצות לעיצוב מודלים יעילים, הדבר ישרת אותך בהמשך הדרך עם כל מודל שתיצור ותשתמש בו, אם תציג אותו ב- Excel 2013, ב- Office 365 SharePoint Online‏, ב- Office Online Server, או ב- SharePoint 2013.

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

במאמר זה

יחסי דחיסה ומנוע את בתוך הזיכרון

מושלם, אין כמו עמודות שאינן אם אתה מחפש ניצול זיכרון

שתי דוגמאות לעמודות שתמיד יש להסיר

כיצד לא לכלול עמודות שאינן נדרשות

מה בנוגע לסינון השורות הנדרשות בלבד?

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

שינוי עמודות Datetime

שינוי שאילתת ה-SQL

באמצעות DAX במידות מחושבות במקום בעמודות

אילו 2 עמודות מומלץ לשמור?

סיום

קישורים באותו נושא

יחסי דחיסה ומנוע הניתוח בתוך הזיכרון

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

בממוצע, תוכל לצפות לקבל מודל נתונים שגודלו פי 7 עד 10 קטן יותר מגודלם של אותם נתונים במצבם המקורי. לדוגמה, אם אתה מייבא ‎7 MB של נתונים ממסד נתונים של SQL Server, גודלו של מודל הנתונים ב- Excel בקלות יוכל להיות ‎1 MB או פחות. מידת הדחיסה בפועל שתושג תלויה בעיקר במספר הערכים הייחודיים בכל עמודה. ככל שיש יותר ערכים ייחודיים, כך גדל הזיכרון הדרוש לאחסון ערכים אלה.

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

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

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

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

שתי דוגמאות לעמודות שתמיד יש להסיר

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

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

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

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

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

כיצד לא לכלול עמודות שאינן נדרשות

מודלים יעיל להכיל רק את העמודות אשר ישמש אותך למעשה בחוברת העבודה שלך. אם ברצונך לשלוט אילו עמודות כלולות במודל, יהיה עליך להשתמש באשף ייבוא הטבלאות בתוספת Power Pivot כדי לייבא את הנתונים במקום את תיבת הדו-שיח "ייבוא הנתונים" ב- Excel.

בעת הפעלת אשף ייבוא הטבלאות, עליך לבחור אילו טבלאות לייבא.

אשף ייבוא הטבלאות בתוספת של PowerPivot

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

חלונית התצוגה המקדימה באשף ייבוא הטבלאות

מה בנוגע לסינון השורות הנדרשות בלבד?

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

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

חלונית הסינון באשף ייבוא הטבלאות

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

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

שינוי עמודות Datetime

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

עמודות Datetime כוללות חלק של תאריך וחלק של שעה. כשאתה שואל את עצמך אם העמודה דרושה לך, שאל את אותה שאלה כמה פעמים עבור עמודת Datetime:

  • האם חלק הזמן דרוש לי?

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

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

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

כל הפתרונות הללו דורשים שינוי של שאילתת SQL. כדי להפוך את שינוי השאילתה לקל יותר, עליך לסנן החוצה עמודה אחת לפחות בכל טבלה. כאשר אתה מסנן החוצה עמודה, אתה משנה את מבנה השאילתה מתבנית מקוצרת (SELECT *‎) לביטוי SELECT המכיל שמות עמודות מלאים, הקלים יותר לשינוי.

הבה נביט בשאילתות הנוצרות עבורך. בתיבת הדו-שיח 'מאפייני טבלה', באפשרותך לעבור אל עורך השאילתות ולראות את שאילתת ה- SQL הנוכחית עבור כל טבלה.

רצועת כלים בחלון PowerPivot המציגה את הפקודה 'מאפייני טבלה'

ב'מאפייני טבלה', בחר עורך השאילתות.

פתיחת עורך השאילתות מתוך תיבת הדו-שיח 'מאפייני טבלה'

עורך השאילתה מציג את שאילתת ה- SQL המשמשת לאכלוס הטבלה. אם סיננת החוצה עמודה כלשהי במהלך הייבוא, השאילתה שלך כוללת שמות עמודות מלאים:

שאילתת ה- SQL המשמשת לאחזור הנתונים

לעומת זאת, אם אתה מיובא טבלה במלואה, מבלי לבטל את הסימון של עמודה כלשהי או להחיל מסנן כלשהו, תראה את השאילתה כ- "Select * from ", מצב שקשה יותר לשנותו:

שאילתת SQL המשתמשת בתחביר ברירת המחדל הקצר יותר

שינוי שאילתת ה- SQL

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

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

    ‎"SELECT ROUND([Decimal_column_name],0)… ."‎‎

    אם עליך להציג אגורות אך לא חלקי אגורות, החלף את ה- 0 ב- 2. אם אתה משתמש במספרים שליליים, באפשרותך לעגל ליחידות, עשרות, מאות וכו'.

  2. אם יש לך עמודת Datetime בשם dbo.Bigtable.[Date Time]‎ ואינך זקוק לחלק של Time, השתמש בתחביר הבא כדי להיפטר מהשעה:

    ‎"SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) "‎

  3. אם יש לך עמודת Datetime בשם dbo.Bigtable.[Date Time]‎ ודרושים לך שני החלקים, Date ו- Time, השתמש בעמודות מרובות בשאילתת ה- SQL במקום בעמודת Datetime הבודדת:

    ‎"SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],‎

    datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],‎

    datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],‎

    datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],‎

    datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”‎

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

  4. אם דרושות לך שעות ודקות, ואתה מעדיף אותן יחד כעמודת זמן אחת, תוכל להשתמש בתחביר הבא:

    ‎Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]‎

  5. אם יש לך שתי עמודות datetime, כגון [Start Time] ו- [End Time], ומה שדרוש לך באמת הוא הפרש הזמנים ביניהם בשניות כעמודה בשם [Duration], הסר את שתי העמודות מהרשימה והוסף את:

    ‎“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 ולא אם הדבר אפשרי, דוחס אותו עוד יותר. הורד את ממטב גודל חוברות העבודה.

קישורים באותו נושא

מפרט ומגבלות של מודל נתונים

הורדת ממטב גודל חוברות העבודה

Power Pivot: ניתוח נתונים רב-עוצמה ומידול נתונים ב- Excel

הערה: כתב ויתור בנוגע לתרגום מכונה: מאמר זה תורגם על-ידי מערכת מחשב, ללא התערבות אדם. Microsoft מציעה את תרגומי המכונה כדי לסייע למשתמשים שאינם דוברי אנגלית ליהנות מתוכן בנושא מוצרים, שירותים וטכנולוגיות של Microsoft. מכיוון שהמאמר תורגם על-ידי מכונה, הוא עלול להכיל שגיאות באוצר המילים, בתחביר או בדקדוק.

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×