One of the main benefits of utilizing business intelligence software is that it gives you information needed to run your business. Being able to look at what happened in the past can give management insight into what will probably happen in the future.
The Management of Tiny Video want to expand their business and have come to you to provide the information they need to make their decision. You explain to them that the tools you have available are the database you created for the store and Microsoft Excel. You also have all the data for March that you can load into the database and to use for the reports.
The information they have requested is:
Income by Zip Code
Income by Customer
Number of Rentals and Income by Day of the Month
Number of Rentals and Income by Day of the Week
% of Total Rentals by Day of the Week
They've also requested the information in tabular and graph format. After you discuss the assignment with the owners, it's also decided that all income fields should be broken out as rental income, late fee income and total income.
You talk to some of the senior database analysts and designer that you know and come up with the following mock reports:
Day of the Week Information:
Income By Customer:
Income By Zip Code
Income By Date:
You also discuss that Management will most likely be looking for these reports on an on-going basis. So the best way to implement this would be to implement an ODBC connection between Excel and your database (that you developed as part of the Chapter 7 assignment).
The remaining data for March needs to be added to the database. The DDL can be found at:
https://njit.mrooms.net/mod/resource/view.php?id=313965
You will need to establish an ODBC Connection between Excel on your PC and the database you will be using. There is an abundance of information on the internet. Just search on Google using Excel ODBC and the DBMS you're using. If you have trouble, let me know and I'll help you get things set up.
In the examples your associates created, they created one query to use in Excel that pulls all the information you need into one sheet. You can then build Pivot Tables off of the data you pull from the database. The query they used to pull the information from an Oracle Database is as follows:
SELECT RENTAL.MEM_NUM, RENTAL.RENT_DATE, DETAILRENTAL.RENT_NUM, DETAIL_FEE,
DETAIL_DAILYLATEFEE, MEMBERSHIP.MEM_ZIP, TO_CHAR (RENT_DATE, 'DAY') AS D,
(DETAIL_DUEDATE - RENTAL.RENT_DATE) AS DAYS_RENTED,
CASE WHEN DETAIL_RETURNDATE <= DETAIL_DUEDATE THEN 0 ELSE ((DETAIL_RETURNDATE - DETAIL_DUEDATE) * DETAIL_DAILYLATEFEE) END as LATE_FEE_INCOME,
((DETAILRENTAL.DETAIL_DUEDATE - RENTAL.RENT_DATE) * DETAIL_FEE) AS RENTAL_INCOME,
CASE WHEN DETAIL_RETURNDATE <= DETAIL_DUEDATE
THEN ((DETAILRENTAL.DETAIL_DUEDATE - RENTAL.RENT_DATE) * DETAIL_FEE)
ELSE ((DETAIL_RETURNDATE - DETAIL_DUEDATE) * DETAIL_DAILYLATEFEE) + ((DETAILRENTAL.DETAIL_DUEDATE - RENTAL.RENT_DATE) * DETAIL_FEE) END AS TOTAL_INCOME
FROM RENTAL, DETAILRENTAL, MEMBERSHIP
WHERE DETAILRENTAL.RENT_NUM = RENTAL.RENT_NUM and MEMBERSHIP.MEM_NUM = RENTAL.MEM_NUM
When you ask about the query they explain:
1. Excel ends the query so you don't need a semi-colon at the end
2. Since the income for a given rental isn't stored in the database, you have to calculate it for each rental. Calculating the late fees without seeing if the rental was returned on time would return negate late fees if the rentals were returned early. So you need to use the CASE statements as a way if determining what calculation to use. You needed 2 of them, one when calculating LATE_FEE_INCOME and one when calculating TOTAL_INCOME. Excel doesn't support PL/SQL IF statements, so you needed to use another statement type.
3. They also explain that using one query containing the data needed was like a 2 dimensional data cube. This would provide more consistent data then running specific queries for each report and possibly getting inconsistent results depending on how you wrote the WHERE statements in the different SQL statements.
You need to do the following to complete the assignment.
1. Create a spreadsheet with an ODBC connection to your database that executes the SQL statement provided
2. Create pivot tables from the results sheet (where Excel puts the data it results) of the query.
3. Create a new sheet for each of the PIVOT tables. The sheets should contain the detailed data needed for the graph and the graph requested by management
Submit the complete spreadsheet for the assignment. Do not copy the results into WORD or print the Spreadsheet to a PDF. Only the spreadsheet will be accepted.
If you are using MS/SQL, the query would be:
SELECT MEM_ZIP, RENTAL.RENT_NUM,
DETAIL_FEE,
DETAIL_DAILYLATEFEE,
CASE DATEPART (dw, RENT_DATE)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday' else null end as D,
Datediff(dy, RENTAL.RENT_DATE,DETAIL_DUEDATE) AS DAYS_RENTED,
CASE WHEN DETAIL_RETURNDATE <= DETAIL_DUEDATE THEN 0 ELSE (datediff(dy,DETAIL_DUEDATE,DETAIL_RETURNDATE) * DETAIL_DAILYLATEFEE) END as LATE_FEE_INCOME,
(datediff(dy,RENTAL.RENT_DATE,DETAILRENTAL.DETAIL_DUEDATE) * DETAIL_FEE) AS RENTAL_INCOME,
RENTAL.MEM_NUM,
convert(varchar(8),RENTAL.RENT_DATE,1) AS [Rent Date],
CASE WHEN DETAIL_RETURNDATE <= DETAIL_DUEDATE
THEN (datediff(dy, RENTAL.RENT_DATE,DETAILRENTAL.DETAIL_DUEDATE) * DETAIL_FEE)
ELSE (datediff(dy,DETAIL_DUEDATE,DETAIL_RETURNDATE) * DETAIL_DAILYLATEFEE) + (datediff(dy,RENTAL.RENT_DATE,DETAILRENTAL.DETAIL_DUEDATE) * DETAIL_FEE) END AS TOTAL_INCOME
FROM RENTAL, DETAILRENTAL, MEMBERSHIP
WHERE DETAILRENTAL.RENT_NUM = RENTAL.RENT_NUM and MEMBERSHIP.MEM_NUM = RENTAL.MEM_NUM