{Author Name [first-name middle-name-initials last-name]}
{Institution Affiliation [name of Author’s institute]}
Introduction
This application database named 'Budget Car Rental' is being maintained by an imaginary company to process car rentals for customers. It is a small business based in Melbourne. They mainly rent out Luxury Cars and 4X4, sport, supercars. The database consists of tables for customers, cars, and order processing. Customers need to be a member to be able to rent cars from Budget car Rental Service. In order to service the demands of customers and to run the business smoothly, the company needs to know how many numbers of cars were rented by customers and the amount of days. It also needs to be informed that which all cars are available.
The database is created in Microsoft Access. The dashboard and Pivot tables are created in MS Excel for depicting the data to answer above questions.
Budget Car Rental has a database warehouse which keeps information about Cars, members, Credit card details and rentals and prices. The database enables the business to able to find out the number of cars left, number of cars rented, and the number of times a car was rented.
The 'Rental Orders' table includes attributes like rental number, Car ID, r and return due date. Similarly, membership table includes membership number, member’s name, contacts and their remaining balance. The Cars table includes the Category of the car, number of Cars available, model and its Brand. There is a separate pricing table with description, fees and late fees. Furthermore, rental table has its unique id, rental date and member number.
Following are the characteristics of the tables and its attributes:
• Each and every table has its unique number.
• A member can rent one or many cars but is assigned only one unique rental number is assigned to a particular transaction.
• Only one price can be assigned to a car but depends on the category.
Data Warehouse
In computing world, a data warehouse (DW, DWH) is a data analysis and reporting system. Data warehouses accumulate historical and current data and help in forming trending reports that are used by senior management for annual and quarterly reports and decision making. Data warehouse has the main focus on the data storage. The data extracted, transformed, cleaned and cataloged inside the data warehouse database and then it is available for managers and other business professionals for use. The data is also used for decision support, data mining, market research and online analytical processing. An elaborate definition of data warehouse includes tools to extract, transform, load data, business intelligence tools and tools to retrieve and manage metadata (Kimball, & Ross, 2011).
The present day concept of data warehouse can be traced back to late 1980s when some IBM researchers created a "business data warehouse". The concept was to provide an architectural model for data flow from decision support and operational perspectives. The data warehouse concept addressed various issues that were associated with the data flow including high costs. Lack of data warehouse architecture, big amount of redundancy was present while supporting multiple decision support systems (Inmon, 2005).
The Data warehouse for Budget Car Rental Service is created in Microsoft access including tables, queries, and relationships have been made alongside this report. Similarly, dashboards have been included too which is created in Microsoft Excel.
Dimensions of the Data Warehouse
The dimensions of the data warehouse are:
Customers
Rental Rates
Cars
Facts of the Data Warehouse
The fact of the data warehouse is
Rental Orders
Description of Tables
The figure below depicts the relationship between the various tables of the database.
Figure: Relationships between the tables
Data warehouse Queries for Decision Making
Query to find available Cars
Cars.Car_ID, Cars.Car_Rego, Cars.Make, Cars.Model, Cars.Year, Availability,
IIF (Availability='Yes', 1, 0) AS Available
Cars
Order By
Cars.Make, Cars.Model;
Query to find number of days a Car was rented
Cars.Car_ID, Cars.Car_Rego, Cars.Make, Cars.Model, Cars.Year,
DateDiff("d", [Rental Orders].Rent_Start_date, IIF ( IsNull([Rental Orders].Rent_End_Date), now(),
[Rental Orders].Rent_End_Date)) AS [Days Car Rented],
[Rental Orders].Rent_Start_date, [Rental Orders].Rent_End_date
Cars INNER JOIN [Rental Orders] ON Cars.Car_ID = [Rental Orders].Car_Id
Order By
Cars.Make, Cars.Model;
Dashboard
The Dashboard is created inside the sheet Dashboard in excel file Dashboard.xls.
Pivot Table
The Pivot tables are created inside the sheet Pivot Tables in excel file Dashboard.xls
Evaluation of BI System
Business intelligence is a compilation of decision support technologies enabling knowledge workers like managers, analysts and executives in order to make better decisions. There are several applications through to perform BI tasks like enterprise portals, spreadsheets, and performance management applications (Chaudhuri, Surajit, Dayal, & Narasayya, 2011).
Business intelligence utilizes analytical tools over the operational data for presenting information to decision makers. Business Intelligence enables the capabilities of the firm to latest trends, technologies, future directions in the markets, and the actions of the firm’s competitors.
The emergence of the data warehouse increased the capability of hardware and software, and powerful web architecture enables to create richer business intelligence applications.
The data warehouse enables BI (Business Intelligence), DSS (Decision Support Systems) and RS (Realtime Systems). DSS are information technology based interactive Systems enabling decision-makers take decisions using models to solve non-patterned problems. The techniques for DSS include Data mining, OLAP and Statistics. Business intelligence is a result of interpretation, assessment, collation and exploitation of data to form meaningful information. The techniques for BI are Scorecards and Dashboards, Statistics, OLAP and Data-mining. RS, on the other hand, applies knowledge discovery and statistical techniques for problem-solving (Delgado, González, Miranda, Navarro, & Graverán, 2013).
Report on decision making making activities through Dashboard
The dashboard presents the data of cars availability and the number of days cars were rented out. The dashboard presents the data in form of a table which is segregated by Car Make, Model. The data shows that many cars returned after being rented out while some are still on rent. This information is obtained by looking at the field ‘Rent End Date’ which is blank. Such Cars which are currently on rent are not available.
Research and Analysis on Findings
Analyzing the dashboard and Pivot Tables following outcomes are visible from the data:
- The least rented brand is BMW and with high availability
- Mitsubishi, Nissan and Subaru have the highest demand
- Nissan and Subaru have low availability
- Volvo is the second least rented brand
- Toyota, KIA and Maserati have equal hiring rates
- Lancer, Magna and Pajero models of Mitsubishi are constantly rented out
- Sunny of Nissan and WRX of Subaru have together the maximum period of rent out
Reference
Alexander, M. (2011). Excel 2007 Dashboards and Reports For Dummies. John Wiley & Sons.
Chaudhuri, Surajit, Umeshwar Dayal, and Vivek Narasayya. (2011). An overview of business intelligence technology. Communications of the ACM, 54(8), 88-98.
Delgado, T., González, G., Miranda, G., Navarro, D. G., & Graverán, A. (2013). Context-aware Spatial Decision Support Systems (CA-SDSS): Articulating Decision Support Systems, Business Intelligence and Recommender Systems considering the geospatial component. In Fourth International Workshop on Knowledge Discovery, Knowledge Management and Decision Support. Atlantis Press.
Inmon, W. H. (2005). Building the data warehouse. John wiley & sons.
Kimball, R., & Ross, M. (2011). The data warehouse toolkit: the complete guide to dimensional modeling. John Wiley & Sons.
Negash, S. (2004). Business intelligence. The Communications of the Association for Information Systems, 13(1), 54.
