Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Topics include in this course are Data Warehousing Concepts, Design and Development, Extraction, Transformation and Loading, OLAP Technology, Data Mining Techniques: Classification, Clustering and Decision Tree, Advanced Topics. This lecture handout includes: Using, Pivot, Tables, Data, Organization, Analysis, Excel, Summary, Aggregates, Spreadsheet, Status
Typology: Lecture notes
1 / 11
This page cannot be seen from the preview
Don't miss anything!
DB 430- Data Warehousing and Data Mining
Using Pivot Tables for Data Organization
DB 430- Data Warehousing and Data Mining
Use a Pivot Table in Excel 2007 for Data Organization
Pivot tables are an Excel feature that you should learn how to use. Instead of analyzing rows upon rows of records, a pivot table can aggregate your data and show a new perspective and few clicks. You can also move columns to rows or vice versa.
You might think of a pivot table as a user created summary table of your original spreadsheet. You create the table by defining which fields to view and how the data should be displayed. Based on your field selections, Excel aggregates and organizes the data so you see a different view of your data.
As example, a sample spreadsheet of 4000 fictitious voters is provided to you in this lab, which includes the following data fields:
Voter ID Party Affiliation Their precinct Age group When they last voted Years they’ve been registered Ballot status
DB 430- Data Warehousing and Data Mining
A pivot tables allow you to group the spreadsheet or external data source by any of your data fields. The thumbnail below shows a count of voters by party by precinct.
Using a pivot table, you can continue to slice the data by selecting additional fields from the PivotTable Field List. For example, we can take the same data and segment by voter age group.
DB 430- Data Warehousing and Data Mining
In the thumbnail above, the main areas of the pivot table are labeled.
There are several ways to build a pivot table. Excel has logic that knows the field type and will try to place it in the correct row or column if you check the box. For example, data that is numeric such as Precinct counts tends to appear to the right in columns. Data, which is textual, such as Party would appear in rows.
While you can simply check fields to display and let Excel build your pivot table, one way is to use the “drag and drop” method. This is partly because we want to visualize our data in columns and rows. It may also be easier if you have fields, which can appear to be numbers like a precinct value.
DB 430- Data Warehousing and Data Mining
A new worksheet opens with a blank pivot table. You’ll see that the fields from our source spreadsheet were carried over to the PivotTable Field List.
DB 430- Data Warehousing and Data Mining
DB 430- Data Warehousing and Data Mining
sort by the month first. You might prefer to move the data so the election dates are in a chronological order.
Prefer not adding fields to a pivot table. It’s easier to add the fields first to your source spreadsheet. The reason is you might get items out of sync if you move data unless you make them a calculated field.
Pivot tables may not make the election data exciting, but it can make the analysis process easier. Without these tables, you’d probably spend more time filtering, sorting and subtotaling. The other benefit is that it’s easy to start over by deselecting fields or moving them to another location. Feel free to download the tutorial spreadsheet below and play with the data. This will probably be the only time you’re allowed to manipulate election data.
DB 430- Data Warehousing and Data Mining
Problem 1: Construct a pivot table with CardiologyCategorical.xls database file. Make angina and thal row attributes and class a column attribute. Place class, angina , and thal in the data area. Specify slope , sex , and #colored vessels as page variables. Use the pivot table to answer the following questions:
Problem 2: Construct a pivot table with CreditCardPromotion.xls database file to answer the following questions:
