![Page 1: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/1.jpg)
SAITS Learning OpportunitiesIntroduction to Pivots and Dashboards
December 12, 200710:00 – 12:00
Cathy Bates
![Page 2: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/2.jpg)
Schedule
Introductions Part 1 – Introduction to Pivots Part 2 – Creating a Pivot Table Break Part 3 – Intro to Dashboards
5 Basic Steps to Using a Dashboard
![Page 3: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/3.jpg)
Learning Outcomes
As a result of this training, you will be able to:
Describe the purpose of a pivot. Define a Fact and a Dimension Create a basic pivot report in Excel Modify an existing pivot Explain the difference between a
pivot and a dashboard
![Page 4: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/4.jpg)
Part 1 – Intro to Pivots
What is a Pivot?
Key components of a pivot.
What are the key requirements to building a pivot?
![Page 5: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/5.jpg)
What is a Pivot?
A pivot is an easy and flexible way
to summarize data.
![Page 6: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/6.jpg)
Pivot Components
A pivot contains two types of data: facts and dimensions.
A Fact is usually some type of numerical data that can be summed or averaged. This includes things such as counts, dollars, GPAs, eligibility indexes, etc.
Dimensions are label categories by which to summarize: Major, Gender, Ethnicity, Fund Code, Budget Item, etc.
![Page 7: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/7.jpg)
Exercise 1Fact or Dimension?
Amount Paid Payment Type
(Debit/Credit) Quantity GPA Range Units Completed Class Level
Fact Dimension
Fact Dimension Fact Dimension
![Page 8: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/8.jpg)
Key Point to Remember
The success and usefulness of your pivot is dependent upon the content and format of the underlying transaction data.
For this reason, think about the questions you want to answer; the facts you want to measure; and how your want to summarize or categorize your fact data.
![Page 9: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/9.jpg)
Problematic Data Format
Account data is on separate worksheets. Pivots are based on a single worksheet.
Although data is in a transactional format, it is important to have the Account number and name included in each transaction.
![Page 10: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/10.jpg)
Improved format to Pivot
This worksheet is now in a more favorable format for the pivots.All account information from each tab should be merged into one worksheet.
![Page 11: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/11.jpg)
Part 2-Creating a Pivot
Review transaction data for facts and dimensions.
Steps to Creating a Pivot
Let’s Build a very simple pivot using some data fromthe Event Management System
![Page 12: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/12.jpg)
Review Transaction Data
Which columns are facts? Which columns are useful dimensions?
![Page 13: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/13.jpg)
Add a Fact Column
There is no existing data column that
is numeric and can be summed.
Add a count field to the Excel
Worksheet.
Make sure you include a heading.
![Page 14: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/14.jpg)
Pivot Creation Demo
Watch how a pivot is created. Double click on picture to replay.
![Page 15: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/15.jpg)
Helpful Hints
Click in cell A1 of your data source before you start building your pivot.
Make sure you have no blank rows in your data.
If you follow these two steps, Excel will automatically select your entire worksheet as a basis for your pivot.
![Page 16: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/16.jpg)
Formatting your Pivot
When you create a pivot it auto sizes the columns by default. To change the appearance of the pivot you can wrap text, narrow column widths and add color.
![Page 17: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/17.jpg)
After formatting….
![Page 18: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/18.jpg)
Table Options
To preserve formatting changes you must
Uncheck “AutoFormat Table”
and Check “Preserve Formatting”
![Page 19: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/19.jpg)
Demonstrate Pivot Functionality
Focusing Drilling Updating Adding a second pivot Preview of more advanced pivots Questions???
![Page 20: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/20.jpg)
Pivot vs. Dashboard
A pivot is an easy and effective way to view data in summary form with drilling and focusing capabilities.
A Dashboard is a graphical way to view and compare related performance indicators about a specific topic.
![Page 21: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/21.jpg)
Pivot vs. Dashboard (cont’d)
A pivot is just one way to explore your data. A pivot can be one of several objects within a dashboard.
Pivots you can build yourself in Excel.
Dashboards are typically built for you, yet provide flexibility to drill and focus.
![Page 22: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/22.jpg)
When you hear Dashboard…
Data Warehouse project Key Performance Indicators
A metric that measures progress towards the achievement of strategy.
Hyperion Software Charts and Graphs Flexible access to related data to
identify or answer strategic questions.
THINK:
![Page 23: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/23.jpg)
Today’s Dashboard Discussion
Introduction only:
Five Things you need to know about Dashboards
More details will be covered in a future Learning Opportunity.
![Page 24: SAITS Learning Opportunities Introduction to Pivots and Dashboards December 12, 2007 10:00 – 12:00 Cathy Bates](https://reader035.vdocuments.us/reader035/viewer/2022062409/56649cb55503460f949790b7/html5/thumbnails/24.jpg)
SAITS Learning OpportunitiesIntroduction to Pivots and Dashboards
Questions?
To get additional assistance log a ticket online for help with “pivots”http://dsa.csupomona.edu/saits/help.asp
This was only an introduction to pivots and dashboards; we are here to help you. Once you use it you will see how easy it is.