ב- 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 או ב- SharePoint 2013.
שקול גם להפעיל את Workbook Size Optimizer (ממטב גודל חוברות העבודה). כלי זה מנתח את חוברת העבודה של Excel ואם הדבר אפשרי, דוחס אותה עוד יותר. הורד את ממטב גודל חוברת העבודה.
במאמר זה
יחסי דחיסה ומנוע הניתוח בזיכרון
מודלי נתונים Excel להשתמש במנוע הניתוח בזיכרון כדי לאחסן נתונים בזיכרון. המנוע מיישם טכניקות דחיסה רבות עוצמה כדי לצמצם את דרישות האחסון, ומכווץ ערכת תוצאות עד שהוא יהיה שבר בגודלו המקורי.
בממוצע, ניתן לצפות שמודל נתונים יהיה קטן פי 7 עד 10 מאותם נתונים בנקודת המקור שלו. לדוגמה, אם אתה מייבא 7 MB של נתונים ממסד נתונים SQL Server, מודל הנתונים ב- Excel יכול להיות 1 MB או פחות בקלות. מידת הדחיסה שהושגה למעשה תלויה בעיקר במספר הערכים הייחודיים בכל עמודה. הערכים הייחודיים יותר, כך נדרש זיכרון רב יותר כדי לאחסן אותם.
מדוע אנו מדברים על דחיסה וערכים ייחודיים? מאחר שבנייה של מודל יעיל הממזער את השימוש בזיכרון היא על הגדלת דחיסה, והדרך הקלה ביותר לעשות זאת היא להיפטר מעמודות שאין לך צורך אותן, במיוחד אם עמודות אלה כוללות מספר גדול של ערכים ייחודיים.
הערה: ההבדלים בדרישות האחסון עבור עמודות בודדות יכולים להיות ענקיים. במקרים מסוימים, עדיף להוסיף עמודות מרובות עם מספר נמוך של ערכים ייחודיים במקום ערך עמודה עם מספר גבוה של ערכים ייחודיים. המקטע במיטובי Datetime מכסה טכניקה זו בפירוט.
שום דבר לא עדיף על עמודה עבור שימוש בזיכרון נמוך
הזיכרון היעילה עמודה ביותר היא זו שמעולם לא ייבאת מלכתחילה. אם ברצונך לבנות מודל יעיל, הבט בכל עמודה ושאל את עצמך אם הוא תורם לניתוח שברצונך לבצע. אם לא, או אם אינך בטוח, השאר זאת בחוץ. תמיד תוכל להוסיף עמודות חדשות מאוחר יותר אם תצטרך אותן.
שתי דוגמאות של עמודות שיש תמיד להכלל אינן נכללות
הדוגמה הראשונה מתייחסת לנתונים שמקורם במחסן נתונים. במחסן נתונים, נפוץ למצוא פריטים ממצאים של תהליכי ETL שטענים ולרענן נתונים במחסן. עמודות כמו "create date", "update date" ו- "ETL run" נוצרות כאשר הנתונים נטענים. אף אחת מעמודות אלה לא נדרשת במודל ויש לבטל את הבחירה שלה בעת ייבוא נתונים.
הדוגמה השניה כרוכה בהשבת המפתח הראשי עמודה בעת ייבוא טבלת עובדות.
טבלאות רבות, כולל טבלאות עובדות, כוללות מפתחות ראשיים. עבור רוב הטבלאות, כגון אלה המכילות נתוני לקוחות, עובדים או מכירות, תרצה את המפתח הראשי של הטבלה כדי שתוכל להשתמש בה כדי ליצור קשרי גומלין במודל.
טבלאות עובדות שונות. בטבלת עובדות, המפתח הראשי משמש לזיהוי ייחודי של כל שורה. בעת הצורך למטרות נורמליזציה, היא שימושית פחות במודל נתונים שבו ברצונך להשתמש רק בעמודות אלה לניתוח או ליצירת קשרי גומלין בין טבלאות. מסיבה זו, בעת ייבוא מטבלת עובדות, אל תכלול את המפתח הראשי שלה. מפתחות ראשיים בטבלת עובדות צורכים כמויות עצומות של שטח במודל, אך לא מספקים תועלת, כפי שלא ניתן להשתמש בהם ליצירת קשרי גומלין.
הערה: במחסני נתונים ובמסדי נתונים רב-מימדיים, טבלאות גדולות המורכבות בעיקר מנתונים מספריים נקראות לעתים קרובות "טבלאות עובדות". טבלאות עובדות כוללות בדרך כלל נתוני ביצועים עסקיים או טרנזקציות, כגון נקודות נתונים של מכירות ועלויות, אשר נצברות ו מיושרות ביחידות ארגוניות, למוצרים, לקטעי שוק, לאזורים גיאוגרפיים וכן הלאה. יש לכלול במודל את כל העמודות בטבלת עובדות המכילות נתונים עסקיים או נתונים העשויים לשמש להפניה מקושרת המאוחסנים בטבלאות אחרות במודל כדי לתמוך בניתוח נתונים. הערך עמודה שברצונך לא לכלול הוא המפתח הראשי עמודה של טבלת עצם, המורכבת מערכים ייחודיים הקיימים רק בטבלת עצם ובשום מקום אחר. מאחר שטבלאות עובדות הן כל כך גדולות, חלק מהרווחים הגדולים ביותר ביעילות המודל נגזרים מכלל שורות או עמודות מטבלאות עובדות.
כיצד לא לכלול עמודות מיותרות
מודלים יעילים מכילים רק את העמודות שתצטרך בפועל בחוברת העבודה שלך. אם ברצונך לקבוע אילו עמודות ייכללו במודל, יהיה עליך להשתמש באשף ייבוא הטבלה התוספת Power Pivot כדי לייבא את הנתונים במקום בתיבת הדו-שיח ייבוא נתונים ב- Excel.
בעת הפעלת אשף ייבוא הטבלאות, עליך לבחור אילו טבלאות לייבא.
עבור כל טבלה, באפשרותך ללחוץ על לחצן & תצוגה מקדימה של מסנן ולבחור את חלקי הטבלה שאתה באמת זקוק לה. מומלץ לבטל תחילה את הסימון של כל העמודות ולאחר מכן להמשיך לבדוק את העמודות הרצויות, לאחר שתשקול אם הן נדרשות לניתוח.
מה לגבי סינון השורות הדרושות בלבד?
טבלאות רבות במסדי נתונים ארגוניים ומחסנים נתונים מכילות נתונים היסטוריים שהצטברו במשך פרקי זמן ארוכים. בנוסף, ייתכן שנגלה שהטבלאות שעניינו אותך מכילות מידע עבור אזורים של העסק שלא נדרשים לניתוח הספציפי שלך.
באמצעות אשף ייבוא הטבלה, באפשרותך לסנן נתונים היסטוריים או לא קשורים, ובכך לחסוך שטח רב במודל. בתמונה הבאה, מסנן תאריכים משמש לאחזור שורות המכילות נתונים עבור השנה הנוכחית בלבד, למעט נתונים היסטוריים שלא יהיו דרושים.
מה אם אנו זקוקים עמודה; האם עדיין נוכל לצמצם את עלות השטח שלו?
יש כמה טכניקות נוספות שתוכל להחיל כדי להפוך עמודה למועמד טוב יותר לדחיסה. זכור שהמאפיין היחיד של עמודה שמשפיע על הדחיסה הוא מספר הערכים הייחודיים. במקטע זה, תלמד כיצד ניתן לשנות עמודות מסוימות כדי להקטין את מספר הערכים הייחודיים.
שינוי עמודות Datetime
במקרים רבים, עמודות Datetime לוקחות שטח רב. למרבה המזל, קיימים מספר דרכים לצמצם את דרישות האחסון עבור סוג נתונים זה. הטכניקות ישתנו בהתאם לאיך שאתה משתמש עמודה, וברמת הנוחות שלך בבניית SQL שאילתות.
עמודות Datetime כוללות חלק תאריך ושעה. כאשר אתה שואל את עצמך אם אתה זקוק עמודה, שאל את אותה שאלה מספר פעמים עבור תאריך ושעה עמודה:
-
האם אני זקוק לחלק הזמן?
-
האם אני זקוק לחלק הזמן ברמת השעות? , דקות? , שניות? , אלפיות-שניות?
-
האם יש לי עמודות Datetime מרובות מכיוון שאני רוצה לחשב את ההפרש ביניהם, או רק כדי צבירה של הנתונים לפי שנה, חודש, רבעון, ועוד.
האופן שבו אתה עונה לכל שאלה זו קובע את האפשרויות שלך להתמודדות עם תאריך עמודה.
כל הפתרונות הללו דורשים שינוי של שאילתת SQL אחרת. כדי להקל על שינוי השאילתה, עליך לסנן לפחות עמודה אחת בכל טבלה. על-ידי סינון עמודה, אתה משנה את בניית השאילתות מתבנית מקוצרת (SELECT *) להצהרת SELECT הכוללת שמות עמודה מלא, שקל הרבה יותר לשנות.
בוא נחפש את השאילתות שנוצרו בשבילך. מתוך תיבת הדו-שיח מאפייני טבלה, באפשרותך לעבור לעורך השאילתות ולראות את SQL הנוכחית עבור כל טבלה.
מתוך מאפייני טבלה, בחר עורך השאילתות.
עורך השאילתות מציג את SQL המשמשת לאכלוס הטבלה. אם סינון כל עמודה במהלך הייבוא, השאילתה כוללת שמות עמודה המלאים:
|
לעומת זאת, אם ייבאת טבלה בשלמותה, מבלי לבטל את הסימון של עמודה או החלת מסנן כלשהו, תראה את השאילתה כ" Select * from ", אשר יהיה קשה יותר לשנות:
|
שינוי שאילתת SQL הבאה
כעת, אם אתה יודע כיצד למצוא את השאילתה, באפשרותך לשנות אותה כדי להקטין עוד יותר את גודל המודל שלך.
-
עבור עמודות המכילות מטבע או נתונים עשרוניים, אם אינך זקוק למספרים העשרוניים, השתמש בתחביר זה כדי להיפטר מהמספרים העשרוניים:
"SELECT ROUND([Decimal_עמודה_name],0)... .”
אם אתה זקוק ל- cents אך לא לשברים של סנטים, החלף את ה- 0 ב- 2. אם אתה משתמש במספרים שליליים, באפשרותך לעגל יחידות, עשרות, מאות וכו'.
-
אם יש לך תאריך ושעה עמודה dbo. Bigtable. [Date Time] ואתה לא זקוק לחלק שעה, השתמש בתחביר כדי להיפטר מהשעה:
"SELECT CAST (dbo. Bigtable. [תאריך שעה] כתאריך) AS [Date time]) "
-
אם יש לך תאריך ושעה עמודה dbo. Bigtable. [Date Time] ואתה זקוק הן לחלקי התאריך והשעה, השתמש בעמודות מרובות בשאילתת SQL במקום בשאילתת Datetime עמודה:
"SELECT CAST (dbo. Bigtable. [תאריך ושעה] כתאריך ) 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]"
השתמש בעמודות רבות ככל הצורך כדי לאחסן כל חלק בעמודות נפרדות.
-
אם אתה זקוק לשעות ודקות, ואתה מעדיף אותן יחד כשעות עמודה, באפשרותך להשתמש בתחביר :
Timefromparts(datepart(hh, dbo. Bigtable. [Date Time]), datepart(mm, dbo. Bigtable. [Date Time])) as [Date Time HourMinute]
-
אם יש לך שתי עמודות datetime, כגון [Start Time] ו- [End Time], ומה שאתה באמת זקוק לו הוא הבדל הזמן ביניהן בשניות בתור עמודה שנקרא [משך זמן], הסר את שתי העמודות מהרשימה והוסף:
"datediff(ss,[Start Date],[End Date]) as [Duration]"
אם אתה משתמש במילת המפתח ms במקום ב- ss, אתה מקבל את משך הזמן באלפיות שני
שימוש באמצעים מחושבים של DAX במקום בעמודות
אם עבדת בעבר עם שפת הביטויים DAX, ייתכן שכבר ידעת שעמודות מחושבות משמשות לגזור עמודות חדשות בהתבסס על עמודות אחרות עמודה במודל, בעוד שמידות מחושבות מוגדרות פעם אחת במודל, אך מוערכות רק בעת שימוש ב- PivotTable או ב- הדוח.
טכניקה אחת לחיסכון בזיכרון היא להחליף עמודות רגילות או מחושבות באמצעים מחושבים. הדוגמה הקלאסית היא מחיר יחידה, כמות וסכום. אם יש לך את כל השלושה, באפשרותך לחסוך מקום על-ידי שמירה על שניים ומתמטיקה של השלישי באמצעות DAX.
אילו שתי עמודות עליך לשמור?
בדוגמה שלעיל, שמור על כמות ועל מחיר יחידה. לשניים אלה יש פחות ערכים מהסכום. כדי לחשב סכום, הוסף מידה מחושבת, כמו:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
עמודות מחושבות הן כמו עמודות רגילות ששניהם יתרווחו במודל. לעומת זאת, אמצעים מחושבים מחושבים ב- fly ולא יתרווחו.
סיכום
במאמר זה, שוחחנו על כמה גישות שיסייעו לך לבנות מודל יעיל יותר לזיכרון. הדרך להקטין את גודל הקובץ ואת דרישות הזיכרון של מודל נתונים היא לצמצם את המספר הכולל של עמודות ושורות, ואת מספר הערכים הייחודיים המופיעים בכל עמודה. להלן כמה טכניקות שסיקנו:
-
הסרת עמודות היא כמובן הדרך הטובה ביותר לחסוך מקום. החלט אילו עמודות אתה זקוק באמת.
-
לפעמים באפשרותך להסיר עמודה ולהחליף אותו במידה מחושבת בטבלה.
-
ייתכן שלא תצטרך את כל השורות בטבלה. באפשרותך לסנן שורות באשף ייבוא הטבלה.
-
באופן כללי, פירוק עמודה לחלקים נפרדים מרובים הוא דרך טובה לצמצם את מספר הערכים הייחודיים עמודה. לכל אחד מה חלקים יהיה מספר קטן של ערכים ייחודיים, והסכום המשולב יהיה קטן יותר מהערך המאוחד המקורי עמודה.
-
במקרים רבים, עליך גם להשתמש בחלקים השונים ככלי פריסה בדוחות שלך. בהתאם לצורך, באפשרותך ליצור הירארכיות מתוך חלקים, כמו שעות, דקות ודקות.
-
פעמים רבות, עמודות מכילות מידע רב יותר ממה שאתה זקוק להן. לדוגמה, נניח עמודה מאחסן מספרים עשרוניים, אך החלת עיצוב כדי להסתיר את כל המקומות העשרוניים. עיגול יכול להיות יעיל מאוד בהקטנת גודלו של עמודה.
כעת, עשית כל מה שתוכל כדי להקטין את גודל חוברת העבודה, שקול גם להפעיל את ממטב גודל חוברת העבודה. כלי זה מנתח את חוברת העבודה של Excel ואם הדבר אפשרי, דוחס אותה עוד יותר. הורד את ממטב גודל חוברת העבודה.
