Business Intelligence: Data Analysis and Predictive Models for Loans

This report provides a comprehensive analysis of business intelligence techniques applied to loan delinquency prediction and rainfall analysis. It begins with an introduction to health and health privacy policies, highlighting the importance of data security in healthcare. The report then delves into exploratory data analysis on a loan delinquency dataset, identifying key variables influencing loan defaults using correlation analysis. Two predictive models are developed: a decision tree model and a logistic regression model, with their performance evaluated and compared, concluding that the logistic regression model offers greater precision and accuracy. Finally, the report shifts to an analysis of rainfall data in the Australian Capital Territory (ACT) and New South Wales (NSW), visualizing rainfall patterns across different locations and time periods. Desklib provides access to this and many other solved assignments for students.

Running Head: BUSINESS INTELLIGENCE
Business Intelligence
Name of the Student
Name of the University
Student ID

1BUSINESS INTELLIGENCE
Table of Contents
Task 1...............................................................................................................................................2
Introduction..................................................................................................................................2
Task 2...............................................................................................................................................5
Task 2.1........................................................................................................................................5
Task 2.2......................................................................................................................................12
Task 2.3......................................................................................................................................14
Task 2.4......................................................................................................................................15
Task 3.............................................................................................................................................17
Task 3.1......................................................................................................................................17
Task 3.2......................................................................................................................................18
Task 3.3......................................................................................................................................19
Task 3.4......................................................................................................................................19
Task 3.5......................................................................................................................................20
Reference List................................................................................................................................22

2BUSINESS INTELLIGENCE
Task 1
Introduction
Health and health privacy policies are extremely important for each and every individual
in a society. Thus extensive researches has been performed by various researchers on this issue
with the aim of improving the service on health care. The information stored in each of the
organizations on the health of the individuals are highly confidential and are needed to be
protected properly. The hardware used by the health care organizations must be associated in
such a way that it ensures the privacy of the stored information (Digitalhealth.gov.au 2018). The
information security system includes protection of information on the data that has been
collected by the organization, protection to the work that has been assigned to the organization
and also the security of the technological assets.
The digital health agency of Australia is one of the organizations that are entitled to deal
with the health care information of the people of Australia. The government of Australia has
been trying to digitalize the health care information of each of their residents for the past few
decades. Thus, there has been a high increase in the demand for providing all the health
information of each individual in the country so that their digital profiles can be designed
(Myhealthrecord.gov.au 2018). The advancement in technology and innovation in the
machineries and equipment with the help of which diagnosis and therapies are conducted
nowadays are useful in storing information related to each individual and hence storing the
information on the recent events has become much easier. The past data on the health of people
that is available easily is the data on the prenatal treatments of an individual. There are a lot of
risks involved in the health care treatments for an individual. Thus, it is important that all the
information on the previous health conditions are available for better and proper treatment. The

3BUSINESS INTELLIGENCE
conditions of malpractice can also be reduced if all the information are available to the server
(Jensen, Jensen and Brunak 2012). Other than the health care services, a lot other information are
also available in this system. The observations or instructions of the service providers are also
recorded in the system for easy access. Further, the payments related to the services
recommended are also provided so that the third party can be prepared for the expenses and
provide them in the proper time.
The confidentiality of the health care information of the people should be maintained by
the organizations where a person is going to avail the services required. Since nowadays large
amount of information is available, every person is concerned about the confidentiality so that
the information is not misused by others. Thus, information is stored in the electronic media as it
is impossible to maintain this huge amount of information on papers. The electronically stored
data is considered nowadays as an asset of greater importance (Cornelisse et al. 2016). Various
studies indicated the fact that the competing companies gather this information from the
physicians and pharmacies to disclose the information to their own companies so that they can
earn incentives. With the help of the gathered information, the profiles of the individuals can be
identified and then all the missing information can be collected. Thus, all the information on an
individual can be extracted in this manner (Gold et al. 2016). Thus, there is a high risk to the fact
that a third party can easily access the information. Thus, proper security is extremely important
to secure the information. In a secured environment, it becomes easier for the service providers
to understand the trend of their medical history and treating the individuals becomes convenient
to them. Preventive measures can also be undertaken easily with the availability of the
information digitally. The most recent technology that has been introduced to the system is the
mobile technology. With the help of this technology, information can be accessed by the both the

4BUSINESS INTELLIGENCE
service takers as well as the providers. An important role in this industry is played by HER. The
electronic system of recording information has resulted in speedy treatments than before
(McCarthy et al. 2017).
Thus, keeping in mind the security measures required to secure the information, several
technological changes has been applied to the system. A lot of authorities are concerned with the
system undergone by the electronic health record. Thus, they have put forward several
propositions on the basis of which the changes in the system has been adopted. The most
important role of the electronic health record system is played in the hospitals mostly. According
to the National Center of Health Statistics, the quality of the service provided to the patients have
benefitted 75 percent of the patients after the adoption and development of this system. The
system not only records the necessary data and information; it also provides information to the
patients that are useful to them for their own benefits. There are records of the medical
conditions and the necessary medications of the patients and also the time for the change in
medication is also recorded. Alerts are provided before the time to both the doctors as well as the
patients. The recent changes adopted by the Digital health technology has resulted in the
influence of the lives of a lot of people (Myhealthrecord.gov.au 2018).
The data on the personal information of the Australian citizens are recorded and
preserved by the Australian Digital Agency. The privacy principles that are adopted by this
organization follows the Privacy Act of 1988 (Legislation.gov.au 2018). The Health Record
System operators takes care of the organizational information. The medium of telephone, mail,
facsimile, general people and health care operators are used to collect information for the
organization. Information of the Job title, employee records, photograph, details of bank and
work history are collected for registration. Once registered, all the medical services used, even

5BUSINESS INTELLIGENCE
the nominal first aid received will be recorded in the system and can be administered only in the
premises of the Agency. For the purpose of the security, the information is not accessible any
third party without any kind of authorization. In case if some data are recorded wrongly, the
individual has to provide the necessary IDs and then only they can request for the changes in the
information (Legislation.gov.au 2018).
Task 2
Task 2.1
Exploratory data analysis is performed on all the variables that are present in the dataset.
The main aim of this task is to develop a prediction model to predict whether a person is loan
delinquent or not. Not all the variables in the dataset are responsible in influencing the predictor
variable “loan delinquency”. Thus, in order to identify the variables that can impact the predictor
variable the exploratory data analysis has been performed. The results of the exploratory analysis
are presented in in the following table 2.1.
After the exploratory data analysis to identify the variables that are most related with the
predictor variable “SeriousDlqn2yrs” correlation analysis has been conducted. From the
correlation analysis, it can be seen that the most related variables are “age”, “NumberOfTime30-
59DaysPastDueNotWorse”, “NumberOfOpenCreditLinesAndLoans”,
“NumberOfTimes90DaysLate” and “NumberOfTime60-89DaysPastDueNotWorse” as these are
the variables that have the highest degree of correlation, both positive and negative, with the
variable “SeriousDlqn2yrs”. The results of the correlation analysis are provided in figure
Table 2.1: Results of Exploratory Data Analysis for loan.delinq.csv

6BUSINESS INTELLIGENCE
Figure 1: Loan Delinquency

7BUSINESS INTELLIGENCE
Figure 2: Revolving Utilization of Unsecured Lines
Figure 3: Age

8BUSINESS INTELLIGENCE
Figure 4: Number of times 30 – 59 Days Due
Figure 5: Debt Ratio

9BUSINESS INTELLIGENCE
Figure 6: Debt Ratio
Figure 7: Number of Open Credit Lines and Loans

10BUSINESS INTELLIGENCE
Figure 8: Number of Times 90 Days Late
Figure 9: Number of Real Estate Loans Or Lines

11BUSINESS INTELLIGENCE
Figure 10: Number of times 60 – 89 Days Due
Figure 11: Number of Dependents

12BUSINESS INTELLIGENCE
Figure 12: Correlation Matrix to Identify the Important Variables
Task 2.2
The first predictor variable that has been constructed is the decision tree model. The
process, the rules and the tree are illustrated in figures 13, 14 and 15 respectively. To predict
whether a person is loan delinquent or not, at first it is checked whether the person has the loan
due for 90 days more than 2.5 times in the past 2 years. If yes, then it is checked if the number of
times is less than or more than 1.5 times in the last 2 years. If it is less than 1.5 times, then
whether it is due for less than 1.5 times for 60 – 89 days. If yes, the person is not loan delinquent.

13BUSINESS INTELLIGENCE
Figure 13: Rapid Miner Process for Decision Tree
Figure 14: Rule Involved for Decision Tree Construction

14BUSINESS INTELLIGENCE
Figure 15: Decision Tree
Task 2.3
The logistic regression model is the second prediction model that has been developed.
Logistic regression predicts the probability of the person to be loan delinquent or not. The odds
ratio of the variables enhances the probability of loan delinquency. The coefficients and the odds
ratio are illustrated in the figure 16 and the process is illustrated in the figure 17.

15BUSINESS INTELLIGENCE
Figure 16: Coefficient and Odds Ratio of the Logistic Regression Model
Figure 17: Rapid Miner Process of Logistic Regression
Task 2.4

16BUSINESS INTELLIGENCE
Table 2.2 illustrates the performance evaluation of both the models and from the table by
comparing both the models, it can be seen clearly that the logistic regression model is a much
more convenient model as it has greater precision and accuracy than the decision tree model.
Table 2.2: Results of Model Performance Evaluation (Decision Tree, Logistic Regression)
Measures Decision Tree Logistic Regression
Model Accuracy 93.2 93.18
True Positive Rate - 86.7%
False Positive Rate 93.2 93.18
Precision - 44.74%
Lift - 8.65%
Recall 93.32 93.81
Sensitivity - 8.65%
F Measure - 14.49%

17BUSINESS INTELLIGENCE
Task 3
Task 3.1
The average total Rainfalls for the State of Australian Capital Territory are given in
figure 3.1. In the table, the average total rainfalls from the month of March 2017 to April 2017 of
the ACT are illustrated with the help of a line graph. As can be seen from the graph that the trend
of rainfall is fluctuating over the month. Table 3.2 represents the rainfall in the same period in
the state of NSW. It can be seen that the rainfall trends in NSW in that period is much more
fluctuating than ACT.
Table 3.1: Rainfall for March – April 2017 in ACT

18BUSINESS INTELLIGENCE
Table 3.2: Rainfall for March – April 2017 in ACT
Task 3.2
Figure 3.2 illustrates the total rainfall in the different locations in the state of ACT. As
can be seen from the figure, the highest rainfall has been experienced in the region of
Tuggeranong. The rainfall received in the period of March – April, 2017 in Tuggeranong is more
than 100 mm.

19BUSINESS INTELLIGENCE
Figure 3.4: Total Rainfall Across different locations in ACT
Task 3.3
The sum of the evaporation amounts in the locations of ACT has to be evaluated. There is
no location in ACT for which the evaporation amounts are available. But it can be estimated that
since the highest rainfall is observed in Tuggeranong, the highest evaporation amount can also be
in that location.
Task 3.4
Figure 3.5 illustrated the total rainfall in the year 2017 in the map view. The highest
amount of rainfall is indicated with the darkest dot and the lowest amount of rainfall is indicated
by the dot with the lightest color.

20BUSINESS INTELLIGENCE
Figure 3.5: Geomap view of the Rainfalls in Locations of ACT
Task 3.5
The dashboard of graphs in all the tableau sheets is illustrated in figure 3.6. All the
functionalities are shown together in this AWL dashboard. The benefit of this dashboard is that
any changes made in any of the previous sheets will be reflected in the dashboard automatically.
No separate changes are required in the dashboard.

21BUSINESS INTELLIGENCE
Figure 3.6: AWL Dashboard

22BUSINESS INTELLIGENCE
Reference List
Cornelisse, V.J., Chow, E.P., Chen, M.Y., Bradshaw, C.S. and Fairley, C.K., 2016. Summer
heat: a cross-sectional analysis of seasonal differences in sexual behaviour and sexually
transmissible diseases in Melbourne, Australia. Sex Transm Infect, 92(4), pp.286-291.
Digitalhealth.gov.au., 2018. Privacy - Australian Digital Health Agency. [online] Available at:
https://www.digitalhealth.gov.au/policies/privacy [Accessed 11 Oct. 2018].
Gold, K.J., Andrew, L.B., Goldman, E.B. and Schwenk, T.L., 2016. “I would never want to have
a mental health diagnosis on my record”: a survey of female physicians on mental health
diagnosis, treatment, and reporting. General hospital psychiatry, 43, pp.51-57.
Jensen, P.B., Jensen, L.J. and Brunak, S., 2012. Mining electronic health records: towards better
research applications and clinical care. Nature Reviews Genetics, 13(6), p.395.
Legislation.gov.au., 2018. Healthcare Identifiers Act 2010. [online] Available at:
https://www.legislation.gov.au/Details/C2017C00239 [Accessed 11 Oct. 2018].
Legislation.gov.au., 2018. My Health Records Act 2012. [online] Available at:
https://www.legislation.gov.au/Details/C2017C00313 [Accessed 11 Oct. 2018].
McCarthy, S., Meredith, J., Bryant, L. and Hemsley, B., 2017. Legal and Ethical Issues
Surrounding Advance Care Directives in Australia: Implications for the Advance Care Planning
Document in the Australian My Health Record. Journal of law and medicine, 25(1), pp.136-149.
Myhealthrecord.gov.au., 2018. My Health Record. [online] Available at:
http://www.myhealthrecord.gov.au/ [Accessed 11 Oct. 2018].