יצירת קשרי גומלין בין טבלאות
שמח שחזרתם
היום נלך קצת אחורה וננסה ללמוד משהו חדש מנקודת מבט אחרת
מודל נתונים, מהו מודל נתונים? ומאיפה בכלל נובע הצורך במודל נתונים?
היום בלי יותר מידי הקדמות נקפוץ למים ונבחן את המודל הטבלאי בPower Bi לעומת החלופות...
מה אנחנו יודעים על מודל נתונים ? אני מאמין שהמושגים שקופצים לכם לראש הם:
· קשרי גומלין
· מודל כוכב
· יחיד לרבים
ועוד כל מיני מושגים בסיסיים שכולנו לומדנו עלייהם במסגרת לימודית כלשהיא
על הכל ועוד נעבור היום וננסה לבנות לנו בסיס להבנה של הצורך והתהליך למימוש 😁
תמונה
אוקי אז המושג מודל נתונים מאפיין את היכולת שלנו לנתח את הנתונים לפי מגוון מאפיינים, בצורה קלה, בנוסף יצירת קשרים נכונים בין טבלאות – חוסכת לנו זמן רב, ומאפשרת לעשות דברים שב- Excel רגיל, היו לוקחים הרבה יותר זמן, ומצריכים עבודה רבה יותר.
כמובן שכל משתמשי האקסל המנוסים שואלים את עצמם "מה רע באוסף הקשרים שיצרתי עם הזמן בטבלאות שלי?"
ובאמת, ניתן ליצור מודל ברמה גבוהה גם באקסל.
מודלי נתונים ב-Excel משתמשים במנגנון הניתוח בזיכרון כדי לאחסן נתונים בזיכרון. המנגנון מיישם טכניקות דחיסה חזקות כדי להפחית את דרישות האחסון, וכיווץ ערכי התוצאות עד שהיא מהווה חלק קטן מהגודל המקורי שלה.
בממוצע, באפשרותך לצפות שמודל נתונים יהיה קטן פי 7 עד 10 מאותם נתונים בנקודת המוצא שלו. לדוגמה, אם אתה מייבא 7 MB של נתונים ממסד נתונים של SQL Server, מודל הנתונים ב-Excel עשוי להיות בקלות 1 MB או פחות. מידת הדחיסה שתשיג למעשה תלויה בעיקר במספר הערכים הייחודיים בכל עמודה.
רגע!
מדוע אני בכלל מדבר על דחיסה וערכים ייחודיים?
שימו לב שאחד הצרכים המשמעותיים שבניית המודל בא לענות עליו הוא הקטנת השימוש בזיכרון ומאחר שבניית מודל יעיל ממזער את השימוש בזיכרון, והדרך הקלה ביותר לעשות זאת היא להיפטר מעמודות שאינך זקוק להן באמת, אנו נדרשים לבנות מודל.
להיפטר מעמודות ? נשמע כיף 🤗 אבל רגע Hold your horses
אנחנו לא סתם נפטרים מעמודת אנחנו יוצרים 2 סוגי טבלאות
טבלאות ממד וטבלאות עובדות Fact Tables & Dim Tables
טבלאות עובדות וממדים מכילות את העמודות שמאחסנות את הנתונים עבור המודל.
ואני מסביר
טבלאות עובדות מכילות מידות, שהן עמודות מספריות לרוב. למשל, 'תעודת זהות לקוח' 'קוד הזמנה' 'הכנסה' ו'יחידות' הן עמודות מידות.
טבלאות ממדים מכילות תכונות המתארות ישויות עסקיות. לדוגמה, שם לקוח, אזור כתובת ופילוח דמוגרפי שהן תכונות של הלקוח של הזמנה או ספק.
בואו ניתן דוגמה פשוטה – יש לי טבלה עצומה של מכירות מאתר מכירות נעליים
כל שורה מייצגת מכירה ולכל מכירה יש עמודות שמפרטות את קוד המכירה, תאריך הרכישה, קוד הפריט, ת.ז לקוח, כתובת מלאה של הלקוח, סטאטוס רכישות של הלקוח, תאריך רכישה אחרון של הלקוח, מין הלקוח, טלפון של הלקוח, גיל הלקוח, וכו
בנוסף יש את תיאור הפריט בעברית ועוד עמודה לתיאור באנגלית, תאריך כניסה של הפריט למלאי, קטגוריית הפריט ותת קטגוריה של הפריט.
עכשיו דמיינו לכם טבלת מכירות כזו עם מיליוני שורות מכירה זו ודאי תהיה טבלה כבדה מאוד
מה עושה בעצם הפירוק ל Fact Table & Dim Tables הוא בעצם מצמצם את כל עמודות התיאור של התכונות ומשאיר רק מפתח מזהה אותו נקשר לטבלה חדשה קטנטנה יחסית שנבנה במקביל
מה ? איך ?
אז ככה :
טבלת המכירות תהיה טבלת העובדות – העמודות בה יהיו רק קוד המכירה, תאריך הרכישה, קוד הפריט, ת.ז לקוח.
ונבנה במקביל 2 טבלאות ממד
אחת טבלת לקוחות שתכיל ת.ז לקוח, כתובת מלאה של הלקוח, סטאטוס רכישות של הלקוח, תאריך רכישה אחרון של הלקוח, מין הלקוח, טלפון של הלקוח, גיל הלקוח
והשנייה טבלת פריטים שתכלול קוד פריט, תיאור הפריט בעברית ועוד עמודה לתיאור באנגלית, תאריך כניסה של הפריט למלאי, קטגוריית הפריט ותת קטגוריה של הפריט.
לכל שורה בודדת של לקוח בטבלת לקוחות יש קשר יחיד לרבים למאות ואלפי שורות רכישה בטבלת מכירות, אותו דבר לגבי הפריטים
תמונה
איך תכלס עושים את זה איך מגדירים קשר אחד לרבים בין טבלאות ב Power BI ?
זה….קל באופן מפתיע🤣.
כדי להגדיר קשר אחד לרבים, פשוט נכנסים למסך של המודל ב Power BI Desktop.
בוחרים בעמודה בצד ה "אחד" (למשל – מספר לקוח בטבלת לקוחות). וגוררים אותה אל העמודה המקבילה שלה בטבלת ה "רבים". וזהו !!!
אין צורך לעשות VLookup, או נוסחאות אחרות. פשוט….גרירה ושחרור.
אוקי אז בואו נבין מה הכאב ראש הקטן הזה עוזר לנו?
מתוך טבלה מפלצתית של מיליון שורות ו15 עמודות נשארנו עם טבלה של מכירות עם 4 עמודות בלבד
לטבלה זו מקשרים (עוד רגע נראה איך) את טבלאות התכונות שבנינו אבל ויש פה אבל גדול 2 הטבלאות החדשות הן כבר לא מיליון שורות כל אחת
במידה ויש למשל 100 לקוחות בלבד ו50 סוגי פריטים אז הטבלאות יכילו שורה אחת בלבד לכל לקוח ושורה חת בלבד לכל פריט
בואו נחשוב קצת מספרים
הייתה לנו טבלה של מיליון שורות ו15 עמודות סך הכל 15,000,000
כעת יש לנו טבלה מרכזית של 4 עמודת ומיליון שורות מכירה + טבלת לקוחות של 100 שורות ו7 עמודות ועוד טבלה של פריטים עם 50 שורות ו6 עמודות תכונות, סך הכל 4,001,100
בנינו מודל פשוט וצמצמנו את הנפח שלו בכמעט 75%!!!
תמונה
הקשר בין הטבלאות יכול להזכיר לכם את Vlookup מאקסל או את סוגי ה Join בSQL
אז בואו נדבר קצת על קשרי גומלין
קשרי גומלין בין טבלאות
כאשר יש לנו 2 טבלאות ואנו מקשרים אותם על ידי חיבור של עמודה אחת מכל טבלה אשר משותפת לשתיהן – למשל קוד לקוח או קוד פריט, לא ניתן לקשר במקביל 2 טבלאות עם יותר מעמודה מקשרת אחת.
בדרך כלל יש לנו טבלת עובדות TABLE FACT שהוא צד הרבים בקשר כי השדה באותה עמודה יכול לחזור יותר מפעם אחת כלומר יכולה להכיל כפילויות שהן בעצם כל שורות המכירה.
לדוגמה בטבלת המכירות יש יותר ממוצר אחד שנקנה על ידי לקוחות שונים. סינון של צד הרבים לא משפיע על סינון הטבלה שבצד היחיד.
הטבלה השנייה היא צד היחיד בקשרי גומלין נקראת טבלת DIMENSION שהוא צד היחיד בקשרי גומלין. שם כל פריט בעמודה יופיע רק פעם אחת וחייב להיות ייחודי! לדוגמה בטבלת המוצרים כל מוצר מופיע פעם אחת בלבד עם מק"ט ייחודי לו וללא כפילויות. סינון של צד היחיד אוטומטית מסנן את הטבלה המקושרת אליה בצד הרבים, כך בקלות נוכל לראות את כל שורות המכירה של לקוח מסוים.
צד היחיד מסומן עם הספרה 1 צד הרבים עם הסימן כוכבית ⁕ .
ויש חץ בין הטבלאות החץ מראה את כיוון סינון הנתונים. סינון בטבלת פריטים ישפיע על סינון הנתונים בטבלת המכירות.
בדוגמה שלנו סינון נתונים בטבלת המכירות לא ישפיע על טבלת המוצרים.
קישור עם חץ לכיוון אחד בלבד SINGLE DIRECTION FILTER CROSS שזה ברירת המחדל, ייתן לנו מודל נתונים שמגיב מהר יותר ובטוח יותר בתוצאות המתקבלות.
טבלאות עם קשרים עם חץ לשני הכיוונים- רבים לרבים למשל מרצה באוניברסיטה שיכול ללמד כמה קורסים והקשר לטבלת קורסים שכל קורס יכול להיות על ידי מרצים שונים - נקרא BOTH DIRECTION FILTER CROSS , יוביל למודל איטי, דורש אישור לפני שהוא מתבצע , מסוכן כי יתכן והתוצאות שיתקבלו לא יהיו בהתאם למה שאנו מצפים.
חיבור קשרי גומלין בצורה לא נכונה יביא בהכרח לתוצאות שגויות במודל הנתונים.
תמונה
עכשיו, נוכל להבין מדוע הקשר הזה נקרא בשם "יחיד לרבים".
לקוח יחיד – מזמין מספר פריטים ומספר הזמנות שונות .
**כל מוצר יחיד **– יוזמן בהזמנות רבות ועל ידי לקוחות שונים.
כלומר הסיבה שקראו לקשר הזה יחיד לרבים היא כיוון שהוא מתאר מצב שבו טבלה אחת מייצגת משהו שנמצא בטבלה אחרת פעמים רבות.
תמיד הטבלה שמחזיקה את המוצרים/לקוחות/ את מה שנגדיר בתור –יחיד–, תכיל רק פעם אחת כל יחיד. למשל טבלת הלקוחות מכילה פעם אחת בלבד כל לקוח. ובאותו אופן טבלת סוגי המוצרים מכילה פעם אחת בלבד כל מוצר.
עכשיו מובן גם למה קוראים להם באנגלית lookup tables, כלומר בתרגום חופשי הייתי קורא לזה "טבלאות תכונות". למשל טבלת הלקוחות, היא למעשה טבלת אינדקס, אלפון, ריכוז של כל הלקוחות והפרטים עלייהם. במצב רגיל, טבלת הלקוחות כוללת הרבה מאוד מידע שמתאר את הלקוחות, למשל טלפון, אימייל כתובת וכדומה. זה מידע שאולי צריך אותו בהזמנות, ואולי לא, אך בכל מקרה, זה מידע שמתאר את הלקוחות.
כאשר אנחנו מתבוננים על טבלת ההזמנות, אין לנו צורך לשמור את הפרטים של כל לקוח בטבלת ההזמנות, זה בזבזני ולא יעיל. לעומת זאת, מה שעושים בפועל, בחוכמה רבה הוא לשמור קוד של הלקוח, מספר שלו, בטבלת ההזמנות. ואז נניח שנרצה לדעת פרטים על לקוח מסוים, נוכל תמיד לחפש אותו, באמצעות המספר שלו – בטבלת הלקוחות.
אז נסכם
**• יחיד לרבים **– הקשר הכי נפוץ.
**• יחיד ליחיד **– לא נפוץ , למעשה הוא הרחבה של הטבלה הקיימת,
• רבים לרבים – Relationships Weak – חדש יחסית ב bi power , מסוכן, צריך להחליט על כיוון
הקשר האם הוא לכיוון אחד או לשני הצדדים. – לא מומלץ לעבוד אתו בלי להבין את הצורך וההשלכות לעומק.
אבל רגע מה קורה אם אני מקשר למשל את טבלת המכירות לטבלת הDIM של פריטים שבה חסרים לי פריטים האם ייעלמו לי כל שורות המכירה? האם בכלל ניתן יהיה ליצור את הקשר?
תמונה
במקרה זה BI POWER מאפשר את הקשר בין הטבלאות על ידי הוספת שורה ריקה אוטומטית עבור כל הערכים שלא נמצאים בטבלה זו. אנו צריכים להיות מודעים להוספת השורה הזו. ישנם פונקציות שמחזירות תוצאות עם השורה הריקה הזאת וישנם פונקציות שהתוצאה שלהם לא לוקחת בחשבון את מה שמקושר לשורה ריקה זו. שימוש בפונקציה נכונה בDAX חשובה לתוצאה אותה אנו רוצים לקבל. לדוגמה הפקודה ALL על טבלת הממד של הלקוחות תספור גם את השורה הריקה של הצבע החסר במודול עבור 176 המוצרים שלא מוגדר להם צבע.- ארחיב על הנושא במאמרים הבאים בנושא DAX אבל כן חשוב לזכור שאם נבצע חישוב ממוצע הממוצע לא ייקח בחשבון את השורה הריקה
לסיכום
במאמר זה, דיברנו על כמה גישות שיכולות לעזור לך לבנות מודל חסכוני יותר בזיכרון. הדרך להפחתת גודל הקובץ ודרישות הזיכרון של מודל נתונים היא לצמצם את מספר העמודות והשורות הכולל ומספר הערכים הייחודיים המופיעים בכל עמודה. להלן כמה טכניקות עלייהם דיברנו וגם כאלה נוספות ששווה לנסות ליישם:
הסרת עמודות היא כמובן הדרך הטובה ביותר לחסוך מקום. בחירת העמודות הדרושות לך.
לעתים באפשרותך להסיר עמודה ולהחליף אותה במידה מחושבת בטבלה.
ייתכן שלא תזדקק לכל השורות בטבלה. באפשרותך לסנן שורות עוד בשלב ייבוא הנתונים.
באופן כללי, פירוק עמודה אחת לחלקים נפרדים מרובים היא דרך טובה לצמצם את מספר הערכים הייחודיים בעמודה. כל אחד מהחלקים יכלול מספר קטן של ערכים ייחודיים, והסכום המשולב יהיה קטן יותר מהעמודה המאוחדת המקורית- כמו שראינו על טבלת המכירות.
במקרים רבים, עליך גם להשתמש בחלקים הייחודיים לשימוש ככלי פריסה בדוחות שלך. בעת הצורך, באפשרותך ליצור הירארכיות מחלקים כגון שעות, דקות ושניות.
פעמים רבות, עמודות מכילות מידע נוסף שלא נחוץ לך. לדוגמה, נניח שעמודה של מספר רכישות שביצע הלקוח מאחסנת מקומות עשרוניים, "עיגול" עשוי להיות יעיל מאוד בהפחתת הגודל של עמודה מספרית.
נסו ליישם
נסו לבנות מודל כלשהוא אפילו על נייר בשביל להמחיש את העקרונות לעצמכם
ספרו לי איך היה ... 🤩
מקווה שקיבלתם ערך