- בדיקת טבלה במחסן הנתונים

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

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

מאפיין טקסט חדש (Lookup) טבלה

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

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

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

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

טבלת הדגימה המלאה נמצאת מתחת לטבלה 1 . הנה השלבים הבאים:

• שלב 1: יש צורך ברשימה נפרדת משדה הסוג עבור טבלת החיפוש. זה יהיה שדה החיפוש שלך. ממערכת הנתונים שלך, צור רשימת ערכים ברורה משדה סוג ההזמנה. הזן את הרשימה הנבחרת של "סוגי" לחוברת עבודה של Excel. תייג את סוג העמודה.
• שלב 2: בעמודה שליד עמודת החיפוש (סוג), הוסף את השדה החדש שאליו ברצונך לקבץ. בדוגמה שלנו, הוסף עמודה עם תווית בשם קטגוריה.
• שלב 3: עבור כל ערך ברשימת הערכים הנבחרת שלך (סוגי בדוגמה זו), הוסף את הערכים המתאימים "קטגוריה". בדוגמה הפשוטה שלנו, הזן מחשבים, מצלמות או טלפונים בעמודה 'קטגוריה'.
• שלב 4: העתק את הטבלה סוג נתונים קטגוריה ללוח שלך.
• שלב 5: פתח את חוברת העבודה של Excel עם נתוני ההזמנה ב- PowerPivot עבור Excel. הפעל את חלון PowerPivot. לחץ על הדבק שיביא לטבלת החיפוש החדשה שלך. תן לטבלה שם וודא שאתה בודק "השתמש בשורה הראשונה ככותרות עמודות". לחץ על אישור. יצרת שולחן בדיקה ב- PowerPivot.
• שלב 6: יצירת מערכת יחסים בין השדה 'סוג' בטבלת ההזמנה ובשדה 'קטגוריה' בטבלת החיפוש. לחץ על הסרט 'עיצוב' ובחר 'צור קשר'. בצע את הבחירות בתיבת הדו-שיח 'צור קשר' ולחץ על 'צור'.

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

מאפיין תאריך (Lookup) טבלה

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

עם זאת, אם אתה צריך את הטקסט בחודש בפועל או ברבעון, אתה צריך לכתוב נוסחה מורכבת. קל יותר לכלול טבלה של תאריך תאריכים (חיפוש) ולהתאים אותה עם מספר החודש במערך הנתונים הראשי שלך. יהיה עליך להוסיף עמודה לטבלת ההזמנה כדי לייצג את מספר החודש משדה תאריך ההזמנה. נוסחת DAX לחודש "חודש" בדוגמה שלנו היא "חודש = חודש ([Order Order]"), אשר יחזיר מספר בין 1 ל -12 עבור כל רשומה.טבלת המאפיינים שלנו תספק ערכים חלופיים, המקשרים למספר החודש. יספק לך גמישות בניתוח שלך.טבלה מלאה תאריך תאריך המאפיין הוא מתחת בטבלה 2 .

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

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

• שלב 3 : יצירת מערכת יחסים בין השדה 'חודש' בטבלת ההזמנה ובשדה חודש חודש בטבלת החיפוש. לחץ על הסרט 'עיצוב' ובחר 'צור קשר'. בצע את הבחירות בתיבת הדו-שיח 'צור קשר' ולחץ על 'צור'.

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

מימד לדוגמא (טבלה) טבלאות

שולחן 1

שולחן 2