SAITS Learning OpportunitiesIntroduction to Pivots and Dashboards

December 12, 200710:00 – 12:00

Cathy Bates

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

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

Part 1 – Intro to Pivots

What is a Pivot?

Key components of a pivot.

What are the key requirements to building a pivot?

What is a Pivot?

A pivot is an easy and flexible way

to summarize data.

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.

Exercise 1Fact or Dimension?

Amount Paid Payment Type

(Debit/Credit) Quantity GPA Range Units Completed Class Level

Fact Dimension

Fact Dimension Fact Dimension

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.

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.

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.

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

Review Transaction Data

Which columns are facts? Which columns are useful dimensions?

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.

Pivot Creation Demo

Watch how a pivot is created. Double click on picture to replay.

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.

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.

After formatting….

Table Options

To preserve formatting changes you must

Uncheck “AutoFormat Table”

and Check “Preserve Formatting”

Demonstrate Pivot Functionality

Focusing Drilling Updating Adding a second pivot Preview of more advanced pivots Questions???

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.

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.

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:

Today’s Dashboard Discussion

Introduction only:

Five Things you need to know about Dashboards

More details will be covered in a future Learning Opportunity.

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.