CIS 624               Spring 2008                   Assignment 5 – Cognos OLAP Software

 

Due: Start of Class on 05/01/08 – at the start of class – but try to do in class on 4/24

 

Task:

                There is a multidimensional data cube available called “OceanCity” on a La Salle computer science www server. Access it and the Cognos PowerPlay software by accessing http://www.lasalle.edu/~redmond/teach/624 then looking for “research materials”, then for Cognos software. Following that link will bring you into PowerPlay with a choice of data cubes. Use the “OceanCity” link for this assignment!! (Note that in Cognos PowerPlay, a relational dimension (e.g.) is broken down if there is more than one hierarchy within it (e.g. Bedrooms, Bathrooms, etc)

 There are 7 tasks described below. Hand in a printout for each (or an electronic copy of a screen shot). Many also ask questions of you – answer those. We will have approximately ˝ of the evening for you to work on the assignment – please ask questions if it is unclear what a task is (generally they are looking into aspects of the beach rental business that seemed interesting – data is entirely made up however!!!). Generally, if the description of a table says  “show X by Y by Z”, I want X as the measure, Y as the row headers, and Z as the column headers. Please put your name on all hardcopy printouts.

 

Details:

  1. Hager is the biggest renter of Ocean City beach houses.  Their “southend” office is their biggest renting office. Drill down to agents within than office are down. Show number of rentals by renter state by renting agent (within Hager Southend office).  Question: What three agents had the most rentals (in order)?   (sorting by total number of rentals could be valuable)
  2. Returning to the Office level (still with Hager), we’re investigating rentals by number of blocks from the beach. Show a clustered bar graph illustrating Hager’s rentals by office by blocks from the beach. Make sure that the graph makes it easy to compare rentals by the number of blocks from the beach for a given office.  Question: For each Hager office, how many blocks from the beach was the least popular?
  3. Starting from Scratch with a new line of inquiry, We’re investigating new houses right at the beach – we want to filter by new condition and blocks to the beach of zero.  Show the number of rentals by number of bedrooms by type of unit (duplex, single, townhouse, condo) . Sort by total rentals across all unit types. Question: What are the two highest selling (renting) number of bedrooms?  (calculate this by hand based on info in the table).
  4. Following up on the previous query, show rentals as a percent of column total (instead of as values) Question: Among the types of units, which one type has the highest percent of its rentals being four bedroom units?  (answer should be one type of unit!)
  5. Starting from Scratch with a new line of inquiry, Drill down on Berger offices. Filter to houses with Central Air Conditioning. Show the number of rentals by Renter County by Berger Office locations. Do 80/20 suppression to focus on only counties with larger numbers of rentals. Question: Which three counties produced the most rentals at the southend office (in order)? (sorting by southend  rentals could be valuable)
  6. Starting from Scratch with a new line of inquiry, Filter to rentals of houses with 4 bedrooms, 2 baths, 1 block from the beach, with central air conditioning, non-smoking, in very good condition. Do 80/20 suppression. Show a line graph, showing number of rentals for each renter state (one line for each) for each of the 5 years in the history. Make sure your graph emphasizes trends over time. Question: Which year seemed to have more than usual rentals from Pennsylvanians and less than usual rentals from New Jerseans? (among houses meeting these criteria).
  7. Starting from Scratch with a new line of inquiry, Show rentals by Ocean (front, View, non) and condition (nested!) by Year. Show an additional column with the changes from 2001 to 2005 (an increase should be positive).  Question: What ocean/condition combination had the largest increase in rentals between 2001 and 2005?