Business Intelligence Essay For Lawrence Smith Animal Rescue Hospital.

Learning Objectives:
The assignment will allow you to demonstrate and assess your ability to:
• In assignment 2a:
analyse a typical business intelligence design problem to identify key decision requirements for senior managers;
select appropriate data visualisations to provide effective decision support;
design a multi-dimensional model to support the identified requirements;
draw Thomsen diagrams.
• In assignment 2b:
develop a physical database schema based on a star-schema approach to provide data warehouse support for multi-dimensional data analysis;
consider appropriate technical architecture options;
plan an ETL process to populate a data warehouse with data;

The objective is to do analysis on the typical problem of given business intelligence case of a business organization and to determine the requirements to make decision for senior managers. To achieve this aim, an approach that supports data visualization and helps in decision making process efficiently. This newly proposed model will be mainly designed to determine the supporting requirements of application. The Thomsen diagram diagrams will be drawn for finding out the relationship among the tables of given schema. The designing concepts of data warehouse will be discussed in detail. The technical architectural design of the multidimensional data will be investigated and the data dictionary will be prepared with appropriate tables and attributes. These will be discussed in details.

On 1973, the hospital named ‘Lawrence Smith Animal Rescue Hospital’ at Melbourne city. This is the special hospital which provides the treatment especially for the freed animals rather than pet animals and profitable animal cases. Because this hospital runs under non-profitable organization.

Sasha Gordon is presently working as the GM (General Manager) of the Lawrence Smith Animal Rescue Hospital Organization.

The Lawrence Smith hospital is running based on the funding amount resources and it gets funds from the nearby councils which provides very special veterinary services and treatments mostly for freed animals that found by the council members. From most of the trusts and funds, the hospital gets donations and also from the nearby animal welfare charity associations. So that the hospital is a non-profit organization.

The Lawrence Smith hospital association organizes the following systems.

The below information are kept in Works Management System.

• Type of Animal species
• Date and Time of animal admission
• Name of the staff member who admits the animal
• Native place of the animal
• Name of the admitting person
• Contact details
• Result

The above stated information as well as the owner’s information are saved in a record. The new data will be added on the Access Database with the help of Donor Management System.

On 2012, a nurse who is volunteered in the hospital has been complicated with the disagreement with her senior manager of the hospital. She had complicated in some tentative choices about the treatment and care of rescued animals. In that organization, Sasha Gordon has been working as a General Manager. As the nurse was going to leave the organization, after a year of authorizing team found that false claims of methodically euthanizing animals and misconduct of funds being provided. The information about donations, funds and more information are kept in the Forms and in Access Database through Donor Management System. But retrieving information from database and integrate them to present the records in front of associates is not an easy task for Sasha and her team. Because they always extract the information manually from the work sheets.

Business Requirements for Lawrence Smith Animal Rescue Hospital

The general manager, Sasha Gordon interrogated and obligated by the brand new board members, and she needs to do make a meeting arrangement for all the members of association to provide the accessibility for all the members for accessing the data directly from database and analyze the act and movement of administration. And also she might respond for all the query asked by the board members efficiently. Therefore the future business intelligence system needs to determine the desires and complications that most necessary for board members. Moreover, the administrator can execute and deliver what they desire.

The business requirements of the Lawrence Smith hospital is analyzed and listed them below.

Key Indicators:

1. Determining the budget
2. Outcomes of stray animal
3. Total number of animals treated
4. How the animals are treated

Present grants:

1. Analysing that the councils associates which helps at most cases and which one donates high funding amount?

Income Streams:

1. The organization needs to take assessment on the income streams
2. Analyzing grants
3. Analyzing donations
4. Comparing streams

Donations:

1. Finding fund resources
2. Analyzing the most business funding resource
3. Analyzing the donors from public
4. Analyzing the profiles of donors and their changed rate of donation

Work patterns of the employees:

1. Understanding the relationship between which outcomes for paid versus volunteer vet staff?

The hospital organization require to determine the statistics about treated animals versus euthanized animals and they should maintain the record of each and every operations of the association.

Generating Dashboard for the reporting System:

The most important thing is that we need to analyze the key performance indicators of the hospital. The solution system should be designed based on the hospital constraints and then it should validate the viability of the hospital organization for the entire year which means it should maintain both revenues as well as expenditure. This result might be viewed in both monthly basis and yearly basis (Steiger, 2010). The propose business intelligence application should allow the organization associates to determine the overall performance of the year in monthly basis and yearly basis (Yusof & Yusof, 2013).

This system will assist the health facility to categorize and size the owed budget, the prices, the income engendered and as a result, the overall outflow. Furthermore, the health facility can hold manner of the earnings created achieved through changed sales streams. It enables in categorizing how awards, donations evaluate with every other.

The act of each worker of the hospital in phrases of the unpaid worker and vet group of workers can also be acknowledged (Khatun & Miah, 2018). The system will offer resource to the clinic to look into special effects on services and auctions whilst the display is on and whilst it is not, hence, additionally providing the entire donations and general animals from a specific employer.

The health facility will also be capable of recognize which yielding granting sources and donors are the most supportive or furnish and donate the maximum amount together with their frequencies and while the offers and donations are made. Ultimately, the health center could be capable of preserve music of numerous charges (and the related quantities) that they're incurring.

Designing the Prototype: Dashboard

Designing the prototype of the business intelligence application is done with Dashboard. To prepare solution for business key requirements of the organization, it is necessary to analyze the Dashboard (O’Neill, 2011).

A Dashboard is a tool and it is used for statistic management purpose and for the visualization of data. It fetches the user asked information from database source, and displays the necessary information. So that makes the investigation process of the organization. It displays the overall performance of the organization as daily/weekly/monthly/quarterly/yearly basis.

Dashboard:

The above shown dashboard is explained below.

• The growth rate, renewal rate of donations obtained by the organization from various sources and the for 2^ndyear 3^rd year growth rate is shown in the above dashboard.
• The top most bar chart displays the count of animals admitted to the hospital. The analysis was done for monthly basis. On each month, how many animals are admitted is analysed. On July month, the highest number of animals were admitted.
• From the total budget, 75% of amount is left. So 35% of amount is analyzed as expenses. Then total turnover throughout the year is analyzed and it is $22,500 from employees.
• The bottom most table shows that the status of each month.

In the above shown Dashboard, the business viability is analyzed. In July and September months, the organization gone through highest viability. Then the correlation of animals in all sessions was analysed from June 2017 to April 2018.

Then the total earnings achieved by the organization is $23,580 and annual target buget is $2,190. So, the organization achieved more that the target amount.

The conceptual model is also known as multidimensional model. This model helps in analyzing the dimensions of the application. These dimensions are members associated with the board and its processes. The multidimensional expository databases are useful in giving information related responses to complex business questions rapidly and precisely. Further, dissimilar to other information models, OLAP in information warehousing empowers clients to see information from various edges and measurements, in this manner displaying a more extensive examination for business purposes. Generally, information warehousing bolsters a few dimensional 3D shapes; in any case, there are in excess of three information measurements delineated by the block alluded to as Hybrid solid shape. As we design new business intelligence application, it is necessary to understand what are required attributes, dimensions and their relationships. So that, we have constructed Thomsen Diagrams.

Organization:

• Nation name
• Metropolis name
• Suburb name
• Enterprise name

TIME:

• yr: 2018, 2017, 2016, 2015, 2014, 2013,……,1987

Month: economic yr: July, August, September, October, November, December, January, February, March, April, can also, June

Personnel:

• Name
• Age
• Gender
• Country
• Cellphone
• E-mail

 Funding supply:

• Granting Sources

? Name of the Granting Source

? Quantity

? cope with

• preferred Public

? Name of the public

? Quantity

? Deal with

• Donations

? Name of the public

? Quantity

? Address

 Animals:

• Animal Species and their native
• Season

 Revenue Fact

• Overall revenue
• Total income
• Total charges
• General profit
• Total donations
• Overall presents

The star schema mapping for data warehouse center is composed with fact tables that will reference to the dimension tables. In each dimension tables, there are related qualities are exhibited (Chen & Zhao, 2014). These qualities are connected for the development of dimensional information marketplaces and information distribution centers that help the logical utilizations of business knowledge. Those attributes are carried out for the development of dimensional statistics marts and statistics warehouses that help the analytic applications of commercial enterprise intelligence.

Multidimensional Data and Thomsen Diagrams

The facts warehouse particularly objectives to store the records. Commonly, the records warehouse can be described as a gathered, interrelated and organized information from various statistics resources in an organization. The principle reason of designing facts warehouse is that designing and developing business intelligences in various applications which facilitates in selection making for the management. It integrates the heterogeneous data from different sources and it produces an evaluation output within the shape of stories. It’s miles the primary records storage shape. The facts saved in information warehouse couldn't be converted. But this information may be de-normalized into augmented presentation or scalability. There are various architectures designed and presented for statistics warehouse in step with their blessings and disadvantages related to this organization and its facts control machine.

The structure design of the organization records Warehouse is a regular and very suitable for storing the facts of large groups. Right here, the facts are thoroughly tested, analyzed, included, documented and collected statistics from heterogeneous resources. These facts are included from the significant fundamentals history of alternate degree records. This type of facts warehouse is designed for reporting and information evaluation and a primary constituent of enterprise intelligence.

Advantages:

• Only one database warehouse is taken for investigating all stats from diversely origin into one
• Centralized database control machine and big in scope
• targeted and organized statistics to relieve with various retrieval requests from user
• helps in strategic decision making process
• optimizes the method of initialization

Disadvantages:

• Huge measurement which often pass to shoot screw ups
• Execution is liberal and historical devoted
• Difficult to meet the requirements of every affiliation or different

After figuring out the well worth, quantity, or high-quality of the benefits and downsides of the above defined technical related to the beautiful design and production of homes, etc., business/project records Warehouse is the maximum appropriate and completely fits to the wished things and systems of enterprise if you want to advantage them to complete their dreams and targeted descriptions of precisely what's required and due to this, the endorsed outline.

The star schema is chosen for this case. A star pattern orders the properties of an occasion into realities, and spellbinding measurement characteristics that give the certainties a unique circumstance. A reality record is the nexus between the particular measurement esteems and the recorded certainties. The actualities are put away at a uniform level of detail (the grain) in the reality table. Measurement properties are sorted out into proclivity gatherings and put away in an insignificant number of measurement tables. A climate star composition that records climate information may have realities of temperature, barometric weight, wind speed, precipitation, overcast cover, and so on and measurements of area, date/time, correspondent, and so forth.

Star schema are intended to upgrade client usability and recovery execution by limiting the quantity of tables to join to emerge an exchange.

Advantages

• Minor support
• Basic meaning of metadata
• Execution is a vital thought of any pattern, especially with a choice emotionally supportive network in which you routinely inquiry a lot of information. IBM Red Brick Warehouse bolsters all pattern plans. Be that as it may, star mappings have a tendency to play out the best in choice help applications.

• Total is troublesome
• huge measurement tables are tedious

The very first and crucial step in creating the records dictionary is to pick out and categorize machine input and output data flow. After, the form has been finished, each detail should be analyzed to determine whether or not the element repeats, whether or not it's miles elective, or whether or not it's far collectively exclusive of some other element. Factors that fall into a collection or that frequently integrate with numerous different elements in lots of structures must be placed collectively in a structural report.

The next step of developing the information dictionary is developing information stores. Up to now, we've got decided what facts desires to drift from one procedure to every other. This information is defined in facts systems. The statistics, however, may be saved in several locations, and in each region the records store may be different. Whereas facts flows constitute information in motion, information stores constitute records at rest.

Name of Table &Description	Name of Attribute & Description
FUNDRISING CAMPAIGN This dimension table identifies different time values in a unique manner.	Campaign ID It represents the Primary Key to identify different and unique ID of Campaign
	Campaign Name This attribute represents the Attribute for Campaign name
	Notes It represents that the Attribute for various values of notes
	Managing Staff Member ID It represents the attribute to identify uniquely the Managing staff members
	Campaign Start Date It represents that the Attribute for identifying the starting date of each Campaign conducted by organization
	Campaign End Date It represents that the Attribute to  identify the date of completion of  each Campaign
	Total Raised from Campaign It represents that the attribute to calculate Total amount raised from different Campaign
DONATIONS This dimension table provides details of the donations made	Donation ID It is the unique key to represent the specific identity of donation
	Donation Date This attribute represents that the amount donated to the organization on a specific date
	Donation Amount It represents that the Attribute for showing the donation amount received by the organization from donors in dollars
	Notes This attribute represents that a small description consists of how the donation was made and what type of donation
	Organisational Donor ID It represents that the unique identity of the donor of another organization and it can be accessed through organization’s ID
	Contact Donor ID It represents that the unique identity of the donor. The donor may be either from other organization or from public.
	Event ID It represents that the identity of the each event to make the donation and conducted by the hospital association.
GRANTS It is the dimension table and it consists of the information about the grants asked for and made by the organization.	Grant Application ID It is the primary key of this table. It represents that the unique identification of the application provided for Grant
	Grant Submission Date It represents that the attribute for determining the date of submission of grant application.
	Grant Outcome It represents that the attribute to identify the status of the application submitted for grant permission.
	Grant Amount Applied for It represents that the Attribute to determine the total amount to apply the grant application in dollars
	Amount Granted It represents that the attribute for determining the total amount granted by the organization.
	Amount Received It represents that the total amount accepted for grant and received between a particular time periods. This received amount will be compared to the total accepted amount.
	Notes It represents that the small description attribute which consists of how the grant was made by the organization and determining that it was one of parts of the program
	Programme ID It represents that the attribute to determine the information about the programme by the organization
GRANTING ORGANISATIONS It is a dimension table that represents that the particulars of granting organizations and details about the organization which conducts program.	Granting Organisation ID It represents that the primary Key to uniquely determine the information about the particular and who did the grant
	Name It represents that the small description of the name of the organization.
	Contact It represents that the attribute describes the contact information of the organization which made grant.
	Street Address This attribute represents that the street address of the organization which provides grants
	City It represents that the attribute describes about the city of the granting organization being located
	Postcode It represents that the attribute describes about the Postcode of the granting organization being located
	State It represents that the attribute describes about the state of the granting organization being located
	Country It represents that the attribute describes about the country of the granting organization being located
REVENUE FACT It is the fact table that provides the information about revenue generated and expenses incurred by organisation	Time Key ID It is a Foreign Key and it represents that identification of the time details.
	Donation ID It is a Foreign Key from donation table and it represents that identification of the donation id details.
	Sale ID It is a Foreign Key from donation table and it represents that identification of the sales details.
	Grant Application ID It is a Foreign Key from Grants table and it represents that identification of the Grant application details.
	Granting Organisation ID It is a Foreign Key from Granting Organization table and it represents that identification of the granting organization details.
	Total Revenue It represents that the attribute of total revenue generated over the particular period.
	Total Expenses It represents that the attribute for calculating and showing grant total expenses incurred

 

Conclusion

This newly proposed model is mainly designed to determine the supporting requirements of application. The Thomsen diagram diagrams is drawn for finding out the relationship among the tables of given schema. The designing concepts of data warehouse is discussed in detail. The technical architectural design of the multidimensional data are investigated. These are discussed in details. Data Dictionary is generated by analyzing the attributes and tables for ETL process.

References

Chen, Z., & Zhao, T. (2014). A Multidimensional Model of Data Warehouses. Advanced Materials Research, 989-994, 1657-1659. https://dx.doi.org/10.4028/www.scientific.net/amr.989-994.1657

Khatun, M., & Miah, S. (2018). Design Methods for Decision Support Systems. International Journal Of Business Intelligence Research, 9(1), 38-52. https://dx.doi.org/10.4018/ijbir.2018010103

O’Neill, D. (2011). Business Intelligence Competency Centers. International Journal Of Business Intelligence Research, 2(3), 21-35. https://dx.doi.org/10.4018/jbir.2011070102

Steiger, D. (2010). Decision Support as Knowledge Creation. International Journal Of Business Intelligence Research, 1(1), 29-47. https://dx.doi.org/10.4018/jbir.2010071703

Yusof, E., & Yusof, A. (2013). The Study on the Application of Business Intelligence in Manufacturing. International Journal Of Business Intelligence Research, 4(1), 43-51. https://dx.doi.org/10.4018/jbir.2013010104