1 / 13

Data Warehouse

Data Warehouse. Week 8 Multidimensional Analysis Practice: PivotTable in Microsoft Excel. Review: OLAP. OLAP (On-Line Analytical Processing) Multidimensional data analysis techniques Why particular business events have occurred and or forecast what may occur in the future

haley-terrell
Download Presentation

Data Warehouse

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Warehouse Week 8 Multidimensional Analysis Practice: PivotTable in Microsoft Excel Fox MIS Spring 2011

  2. Review: OLAP • OLAP (On-Line Analytical Processing) • Multidimensional data analysis techniques • Why particular business events have occurred and or forecast what may occur in the future • Slice, Dice, Pivot, and Drill Down/UP • Questions expected on transaction data from operational system • Who purchased a particular product? • How much did an employee get paid? • How many of a product was manufactured? VS. • Questions expected on OLAP • What are the total sales for each product? • What are the total sales for each department? • Which salesperson has sold the most? • Which products does each salesperson sell the most of? • In which month did most of the sales occur?

  3. Slice, Dice, Drill Down/Up • Drill Down/Up: Drilling down or up is a specific analytical technique whereby the user navigates among levels of data ranging from the most summarized (up) to the most detailed (down).

  4. Practice: PivotTable in Microsoft ExcelA tool which assists users with summarising large amounts of data into useful reports.

  5. Scenario • You are a manger of a department store and one of your responsibilities is to make decisions related to product ordering, promotions, customer discounts as well as monitoring and managing the daily operations of the store. • You have a number of operational systems to assist with the recording of day to day transactions. At the end of each month you are provided with a report which displays each sale (see Excel file retail_sales_feb.xls for February 2008 report). • Although this report provides a lot of information, the information is not in a format which can easily assist in the type of decisions you are required to make.

  6. Original Data: retail_sales_feb.xls

  7. Steps • 1. Open the file retail_sales_feb08.xlsx • 2. Click Insert -> Pivot Table at the top left of the screen

  8. 3. The next screen requires you to identify the data range to be analysed by the PivotTable. You will notice that Excel has automatically detected the data range. Also indicate where the PivotTable will be created. • Click OK to continue

  9. 4. A new worksheet appears similar to below. The screen is divided into two sections. On the left is the Design Area where you define the structure of your PivotTable. On the right, is the Field List which corresponds with the column headings of your data.

  10. 5. Drag &drop the Customer field from the list on the right to the Drop Row Fields Here design area on the left. This is the same as dropping the Customer field in the Row Labels box on the lower right side of the screen. Your screen will look like this

  11. 6.Drag & drop the Total field to the Drop Data Items Here design area. Notice that the order total

  12. Questions • Retail_sales_feb.xls • Some of the questions that you are required to answer at the end of each month include: • Which customer spent the least amount of money in February? • Which customer spent the most in February and which sales person was responsible for the majority of these sales? • Which product provided the most revenue? • Which department earned the most revenue? • Which salesperson provided the most sales revenue? • Which department the salesperson who provided most sales revenue belong to? • Which salesperson provided the most revenue? Which customer bought the most from this salesperson? And which product this customer bought the most from this salesperson?

  13. Questions • incentive_database.xls • Which project got the most total amount approved (ttl_approved) in 2007? • Which program created the most jobs in 2007? • Which project created the most jobs?

More Related