קורס Power Pivot + חומר הדרכה.

לחץ כאן למידע אודות הקורס אקסל  POWER BI+ תוכנית לימודים

 

1. הנחיות לתרגיל ב POWER PIVOT

2. קובץ אקסל לתרגול POWER PIVOT

3. פתרון לתרגיל ב POWER PIVOT

4. סרטוני הדרכה באנגלית - ראה בתחתית העמוד. 

 

מבוא:  

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

מודל הנתונים הוא הבסיס self service BI  ול Power BI .

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

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

היתרונות של power pivot :

  1. כוח חישוב גדול יותר מעל מגבלת ה 1048576 שורות של האקסל.
  2. מקשר בין טבלאות שונות לא רק על טבלה אחת . לוקח שדות מטבלאות אחרות ממקורות שונים, המקושרת אחת לשנייה.
  3. מתעדכן בצורה דינמית ברגע שהנתונים מתעדכנים ניתן לראות מידית את השינוי בטבלה. אין צורך בריענון
  4. מאפשר ליצור מודל נתונים ולהציג רק את השדות שאתה צריך.
  5. אפשרות לבצע שאילתות מורכבות בעזרת שפת DAXליצור חישובים מורכבים . להשוות נתונים משנה אחת לשנה קודמת בקלות.
  6. ניתן לבנות היררכיה להוספת מיימדים למודל נתונים שלנו. (לדוגמא ניתן לקשר שנים לחודשים לימים או יבשות מדינות ערים).
  7. עובד מהר יותר מפיבוט רגיל.

הקורס Power Pivot כולל את הנושאים הבאים:

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

ניתוח המידע בעזרת טבלאות ציר מקושרות.
עמודות מחושבות
מימדים(Measures).
פונקציות DAX, פונקציות בינת זמן,
היררכיות.
יצירת  KPIS - key performances indicator.

 התקנה של Power Pivot . 

ניתן להוסיף את התוכנה החל מגרסה 2013 על ידי לחיצה על לשונית קובץ ברצועת הכלים, בחירה באפשרויות , לאחר מכן בתוספות, בחירת תוספות COM ולחץ ביצוע. 
ברשימה שנפתחת לסמן ב V את Power Pivot for Excel  ואישור לסיום. בסוף ההתקנה תתווסף לנו לשונית באקסל בשם POWERPIVOT.


למידע נוסף והורדה של POWER PIVOT עבור גרסאות אחרות ניתן להוריד גם בקישור הזה

Excel add on com PowerPivot

יצירת קשרי גומלין בין הטבלאות– (דומה / מקביל לפונקצית vlookup באקסל)

  1. ניתן לבצע רק קישור אחד בן טבלאות .
  2. עמודה אחת יכולה לקשר פעם אחת ולא במקביל ל 2 מקומות.
  3. עמודה של הקישור חייבת להיות יחודית בטבלה המקשרת.
  4. תאימות של סוג הנתונים לאורך כל העמודה המקשרת (לא לערבב תאריכים, מספרים וטקסט).
  5. עמודה אחת בלבד של קשרי גומלין היא המפתח המקשר .לא ניתן להשתמש במספר עמודות כמפתח ייחודי.

אנו משתמשים במודל הנתונים לבנות POWER PIVOT / Charts, Maps, Reports

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

קישור נתונים במודל.

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

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

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

הוספת נתונים למודל מתוך אקסס או SQL  תייבא גם את קשרי הגומלין של טבלאות אלו.

ניקוי מודל הנתונים.

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

להוסיף למשתמש את הסלייסר שיהיה לו קל למצוא את המידע שלו.

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

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

הורדה של מודל קונטוס contoso v2 model  לצורך תרגול . נוצר על ידי מיקרוסופט ניתן להוריד בחינם.

http://powerpivotsdr.codeplex.com/

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

ניתן בתוך המודל של הפיבוט לבצע חישובים בין עמודות בעזרת חישוב בין שמות העמודות.
מתחילים כרגיל עם הסימן = לאחר מכן סוגריים מרובעים (שמאלי) ייתן לנו את רשימת שמות העמודות . אפשר ב TAB להשלים את הנוסחה לאחר מכן בסיום הנוסחה ללחוץ ENTER.

ל פאואר פיבוט יש שפת פונקציות משלה בשם DAXDATA EXPRESSION LANGUAGE .

סיכום של כל הפקודות DAX

https://support.office.com/en-us/article/DAX-Function-Reference-DAX-6be048ab-a454-4a1d-bb37-e3d21bfb6a65?CorrelationId=9a73832c-ad16-4a4f-b4b7-d7791cb4fed8&ui=en-US&rs=en-US&ad=US&fromAR=1

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

היררכיה במודל הנתונים .

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

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

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

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

יוצרים באזור החישוב למטה של המודל ( אם לא מופיע ללחוץ על "אזור החישובים")
מתחילים בשם המדידה שאנו רוצים לתת לחישוב זה . לדוגמא מכירות אונליין בלבד. לאחר מכן מוסיפים נקודה פסיק (ככה הוא יודע שעד כאן זה השם) לאחר מכן= ו את הפקודה CALCULATE(SUM[sale],CHANNEL="online") מה שאחר הפסיק זה פילטר (דומה לפונקציה SUBTOTAL סכום בייניים.  לסיכום הפונקציה אומרת בצע חישוב של סכום רק למכירות שבוצעו אונליין.  מאוחר נראה את ה MEASSURE שיצרנו בטבלת הציר ונוכל לגרור אותו לתוך עמודות הערכים הוא יבצע עבור כל נתון את הפונקציה של סכום אבל רק למכירות אונליין. זה חוסך לנו לבנות מחדש ידנית את הפונקציה הזאת על ידי פילטרים בתוך הפיבוט כל פעם מחדש.

KPISkey performances indicator   
בכדי לעבוד איתם צריך קודם כל ליצור Measure)) base value – זה חישוב. זה חייב להיות חישוב אחרת זה לא עובד. לאחר מכן צריך לבצע השוואה לנתון מסוים
Target Value(Goal)  - כמו אחוז מכירות צריך להיות גדול מ 30% וכד' . לסיום צריך שיהיה לנו status thresholds . להגדיר את גבולות הגזרה. מה נחשב לטוב ומה נחשב להכי גרוע. בכדי שנוכל לצבוע את מקרי הקיצון בצבעים מתאימים.  לאחר מכן לוחצים בקליק ימני על הממד שאותו יצרנו בשלב הקודם ויוצרים KPI מגדירים את טווח הצבעים לכל אחד מהנוסחאות שהגדרנו.
לסיכום ניגשים ל טבלת ציר ואנו נראה שם בכל טבלה את ה KPI שיצרנו . ניתן לגרור אותם לטבלה ואנו נראה את נתונים בנוסף את בר הצבעים שנותן לנו KPI .  לדוגמא בטבלת המכירות שלנו נראה למטה ציור של רמזור . נוכל לפתוח אותו לבחור את השדות שאנו רוצים לראות בטבלת הציר. מכירות אונליין, יעדים, ומצב KPI .

חוברת מבוא לפונקציות DAX

חוברת לפונקציות DAX למתקדמים

לסרטוני הדרכה של מיקרוסופט(באנגלית)  Excel 2013 Power BI Fundamentals

סרטוני הדרכה של מיקרוסופט בנושא Power BI Desktop  ביוטיוב

מומלץ - סדרה של 20 סרטוני הדרכה ביוטיוב (באנגלית) The Excel 2013 Power BI Tools

מידע נוסף באתר מיקרוסופט אודות Power pivot בעברית 

מידע נוסף באתר התמיכה של מיקרוסופט אודות Power Pivot  באנגלית 

 

 

 

Joomla SEF URLs by Artio