קשרי גומלין בין טבלאות במודל נתונים

הדפדפן שלך אינו תומך בווידאו. התקן את Microsoft Silverlight‏, Adobe Flash Player או Internet Explorer 9.
וידאו: קשרי גומלין ב- Power View וב- PowerPivot

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

CustomerID

Name

EMail

DiscountRate

OrderID

OrderDate

Product

Quantity

1

ברק

yoav.barak@contoso.com

‎.05

256

‎2010-01-07‎

דיגיטלית קומפקטית

11

1

ברק

yoav.barak@contoso.com

‎.05

255

‎2010-01-03‎

מצלמת SLR

15

2

אפשטיין

itamar.epstein@contoso.com

‎.10

254

‎2010-01-03‎

מסרטה זולה

27

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

Customers

[CustomerID]

Name

Email

1

ברק

yoav.barak@contoso.com

2

אפשטיין

itamar.epstein@contoso.com

CustomerDiscounts

[CustomerID]

DiscountRate

1

‎.05

2

‎.10

Orders

[CustomerID]

OrderID

OrderDate

Product

Quantity

1

256

‎2010-01-07‎

דיגיטלית קומפקטית

11

1

255

‎2010-01-03‎

מצלמת SLR

15

2

254

‎2010-01-03‎

מסרטה זולה

27

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

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

לראש הדף

במאמר זה

עמודות ומפתחות

סוגי קשרי גומלין

קשרי גומלין וביצועים

קשרי גומלין מרובים בין טבלאות

דרישות לקשר גומלין בין טבלאות

מצבים לא נתמכים בקשר גומלין בין טבלאות

מפתחות מורכבים ועמודות בדיקת מידע

קשרי גומלין של רבים לרבים

צירופים עצמיים ולולאות

זיהוי והסקה אוטומטיים של קשרי גומלין ב- Power Pivot

זיהוי אוטומטי של ערכות בעלות שם

הסקת קשרי גומלין

עמודות ומפתחות

קשרי גומלין מבוססים על עמודות בכל טבלה המכילים את אותם נתונים. לדוגמה, הטבלאות Customers ו- Orders יכולות להיות קשורות זו לזו מכיוון ששתיהן מכילות עמודה המאחסנת מזהה לקוח. בדוגמה, שמות העמודות זהים, אך זו אינה דרישה מחייבת. אחד מהם יכול להיות CustomerID והשני יכול להיות CustomerNumber, כל עוד כל השורות בטבלה Orders מכילות מזהה המאוחסן גם בטבלה Customers.

במסד נתונים יחסי, יש כמה סוגי מפתחות, שהם בדרך כלל פשוט עמודות עם מאפיינים מיוחדים. הבנת מטרתו של כל מפתח יכולה לעזור לך לנהל מודל נתונים מרובה טבלאות שמספק נתונים לדוח PivotTable‏, PivotChart או Power View.

המפתחות הבאים הם המעניינים ביותר לצרכינו:

  • מפתח ראשי: מזהה שורה בטבלה באופן ייחודי, כגון CustomerID בטבלה Customers.

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

  • מפתח זר: עמודה שמפנה לעמודה ייחודית בטבלה אחרת, כגון CustomerID בטבלה Orders, שמפנה ל- CustomerID בטבלה Customers.

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

סוגי קשרי גומלין

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

הטבלה הבאה מציגה את קשרי הגומלין בין שלוש הטבלאות:

קשר גומלין

סוג

עמודת בדיקת מידע

עמודה

Customers-CustomerDiscounts

יחיד ליחיד

Customers.CustomerID

CustomerDiscounts.CustomerID

Customers-Orders

יחיד לרבים

Customers.CustomerID

Orders.CustomerID

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

קשרי גומלין וביצועים

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

קשרי גומלין מרובים בין טבלאות

מודל נתונים יכול להכיל מספר קשרי גומלין בין שתי טבלאות. כדי ליצור חישובים מדויקים, Excel זקוק לנתיב יחיד מטבלה אחת לשנייה. לכן רק קשר גומלין אחד בין כל זוג טבלאות יהיה פעיל בכל פעם. הקשרים האחרים אינם פעילים, אך באפשרותך לציין קשר גומלין שאינו פעיל בנוסחאות ובשאילתות. בתצוגת דיאגרמה, קשר הגומלין הפעיל הוא קו מלא וקשרי הגומלין שאינם פעילים הם קווים מקווקווים. לדוגמה, ב- AdventureWorksDW2012, הטבלה DimDate מכילה עמודה בשם DateKey הקשורה לשלוש עמודות שונות בטבלה FactInternetSales‏: OrderDate‏, DueDate ו- ShipDate. אם קשר הגומלין הפעיל הוא בין DateKey ו- OrderDate, זהו קשר ברירת המחדל בנוסחאות, אלא אם תציין אחרת.

לראש הדף

דרישות לקשר גומלין בין טבלאות

ניתן ליצור קשר גומלין כאשר הדרישות הבאות מתקיימות:

קריטריונים

תיאור

מזהה ייחודי עבור כל טבלה

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

עמודות בדיקת מידע ייחודיות

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

סוגי נתונים תואמים

סוגי הנתונים בעמודת המקור ובעמודת בדיקת המידע צריכים להיות תואמים. לקבלת מידע נוסף אודות סוגי נתונים, ראה סוגי הנתונים הנתמכיםבמודלים של נתונים.

מצבים לא נתמכים בקשר גומלין בין טבלאות

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

מפתחות מורכבים ועמודות בדיקת מידע

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

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

קשרי גומלין של רבים לרבים

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

צירופים עצמיים ולולאות

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

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

  • טבלה 1, עמודה a אל טבלה 2, עמודה f    

  • טבלה 2, עמודה f אל טבלה 3, עמודה n    

  • טבלה 3, עמודה n אל טבלה 1, עמודה a    

אם תנסה ליצור קשר גומלין שיגרום ליצירת לולאה, תוצג שגיאה.

לראש הדף

זיהוי והסקה אוטומטיים של קשרי גומלין ב- Power Pivot

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

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

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

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

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

  • ודא ששמה של עמודת המפתח בצד ה'רבים' דומה לשמה של עמודת המפתח בטבלת בדיקת המידע. שמות העמודות אינם חייבים להיות זהים לגמרי. לדוגמה, בסביבה עסקית, יש לעתים קרובות גירסאות דומות לשמות עמודות המכילות למעשה את אותם נתונים: Emp ID‏, EmployeeID‏, Employee ID‏, EMP_ID וכן הלאה. האלגוריתם מזהה שמות דומים ומקצה סבירות גבוהה יותר לעמודות ששמותיהן דומים או תואמים במדויק. לכן, כדי להגדיל את הסבירות ליצירת קשר גומלין, באפשרותך לנסות לשנות את שמות העמודות בנתונים שאתה מייבא לשמות דומים לעמודות בטבלאות הקיימות שלך. אם Excel ימצא קשרי גומלין אפשריים מרובים, לא ייווצרו קשרי גומלין.

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

זיהוי אוטומטי של ערכות בעלות שם

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

הסקת קשרי גומלין

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

  • Products ו- Category -- נוצר באופן ידני

  • Category ו- SubCategory -- נוצר באופן ידני

  • Products ו- SubCategory -- המערכת מסיקה שקיים קשר גומלין

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

לראש הדף

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

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

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

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

×