Task 1: Data
Analysis/OLAP/Mining Investigation. 2
A) Create a pivot table
for the PlaceIT. 2
B) Create a dashboard to inform the design for
Task 2: Considerations
for the Data Warehouse Database security. 6
The process of mining useful
information from raw data, possess significant important. The Relational
Database Management System (RDBMS) only supports transactions and only used to
access and modify the data, but it is not sufficient for organizational decision-making
process. To produce conclusive and summarized visualization of the data
requires analytical tools including Online Analytical Processing (OLAP). The
data stored in data warehouse is provide summarization, which is taken from
various databases. To maintain the security of this data is very significant
and research based challenge. The security may be breached by external attacker
or even legitimate and authorized internal users. So, it requires risk free
security system for the data stored in data warehouses.
Task 1: Data Analysis/OLAP/Mining Investigation
A) Create a pivot table for the PlaceIT
Here is showing the average
salary per job per year per year’s quarter and compare it to other jobs and
years and years’ quarter, Also it is showing when the salaries are declining and
increasing by using different arrows colours.
Data analysis is a method of
cleansing given data, and transforming it to modular format, so that
appropriate discovery of information may be performed. Data analysis is
significant features used for the decision making process by providing useful
conclusions and suggestions (Andoh-baidoo
& Kasper n.d.).
Business intelligence also plays prominent role in the process of
decision making by generating actionable intelligence from source of raw data.
It helps to analyse various dimensions of business, including profit margins,
sales of particular period and particular region, cost for manufacturing vs
benefits, and so on (Mariu?a
Online Analytical Processing (OLAP)
is used in data warehouses, to extract the useful meaning from large bulk of
the data. For example, if organization is interested to know that how many of
the customers are retain clients and already purchased any product from the
company in last six month, this sort of query will be better analysed using
Online Analytical Processing (OLAP), which will assist in decision of
communicating retain customers and providing them promotional offers and
similar profitable decisions (Cardon
Data analysis, business intelligence
and online analytical processing are the tools which can be used for better
visualization of the given input, as given in above task. The reporting
facilities are available in OLAP, which can be utilized to make clear reporting
for the given task. Moreover, the generated reports may further be analysed by
using algorithms and frameworks of business intelligence, through which
meaningful information may be extracted to know the possible benefits from the
data. The analytical tools may be used in the task for the predictions as well,
by analysing previous trends in data. As given in the task, the salaries of each
category of employees and their respective trend of increment or decrement can
be analysed to produce meaningful conclusion, which may provide basis for better
planning for future.
Microsoft Excel is also OLAP friendly as easy editing is possible without
writing and producing complex commands. Also, various formulas are given, which
support in calculation related to different fields, including statistics,
finance, and so on. It also provides flexible charts and summarization tools
like pivot table (Anon
The pivot table is a multidimensional tool, where rows and columns of the
given input table of database or excel sheet are utilized to produce required
report. This newly generated table is featured by summary, but main advantage
of using table is that original data will not be affected with the creation of
summarized table, this will eliminate the risk of damaging original database or
sheet. This organized table is finally used for the purpose of visualization
charts, for final decision making (Slater
et al. n.d.).
B) Create a
dashboard to inform the design for PlaceIT
dashboard showing all reports that have been run successfully
Data analysis is performed for the extracted meaningful information from
given input of the data. The review and analyse is very complex process, which
requires clarity of visualization. The conclusive information can be analysed
through different analytical tools as creating dashboard. Moreover, various perspectives can by analysed by
using business intelligence as to decide where should organization invest,
prediction of future trends, finding performance and deep analyses of human
resource of the organization and decision of taking next project accordingly,
and various other analytical task may be analysed by using analytical tools. The graphical interface of dashboard provides
clear visualization of information and help to excerpt conclusion. The popular
services of visualization and dashboard creation are provided by various tools
such as Tableau, earthsoft, Microsoft Excel, Microsoft Bing and so on. These
are reporting tools, which convert numerical data into easy to understand
format, which will help in situational understanding and managers will be able
to take spontaneous decisions. Moreover, the change in system is visible also
visible to the organization, the acknowledgement of any remarkable change will
bring usefulness regarding future choices (Pu et
al. 2015) (Andoh-baidoo
& Kasper n.d.).
Digital dashboards are widely used to indicate “vital signs”, which are
also known as Key Performance Indicator (KPI). The type of information shown by
dashboard may include financial progress of any organization, assessment of
human resources acquired by any organization, tracking movable devices and
vehicles, and inventory of the stock, and so on. The process of data analytics
is made easier with the use of dashboards, as they help in displaying current
trends to assess the situation, and support to decide upcoming course of
actions. They provide hint for the available opportunities, so that
organization may focus and get appropriate benefit from current situation. It assists
to keep track of the progress of initiatives taken by organization, so that
deficiencies of the tasks may be identified to improve the efficiency. It also
helps in providing conclusive reports to all stakeholders. These reports may be
publicly released and published in media to raise the high values of
et al. 2017).
Task 2: Considerations for the Data Warehouse
Here is showing the users access
have been restricted
Here is showing each user and
their access in the access control page
This is administration access
which C3517350, dan and susie.parker have administration access .They have
access to all the work Edit ,view and add users. They have privilege full control assess.
This example of view access,
john.bell has view access that is why he
is restricted and can not see the work that has been done due to the
restriction that he has.
This example of a user who has
an Edit access brad.knight ,He just has access to specific pages but not to all
vs Data warehouse
The revolution of technology has increased the number of users and
machines by many folds, especially during last two decades. From personal to
professional levels, huge amount of data is produced with passage of every
movement, and it requires appropriate security. Initially, files were used for
the purpose of data storage, later on databases were evolved to be used for
same reason. Database is application oriented storage and structured
organization of contents, it has reduced various problems of file storage
system, including data redundancy. Database mostly focus of transactions and do
not perform analytical tasks. However, data warehouse is subject oriented
collection of non-volatile data, which is used for the purpose of taking
breaches in databases
The storage of data in database faces various challenges related to
security. The breaches of database security includes data observation by
illegal and unauthorized way, modifying data without appropriate permissions,
and unavailability of data. So, implementation of security measures for the
data residing in database requires privacy, integrity and availability. Privacy
refers to access of data only by authorized persons. Integrity denotes
avoidance of any illegal modification in data. Availability refers that data
should have to be available for access, by eliminating all types of software
and hardware error (Quirchmayr
& Stolba n.d.)(Bertino
& Sandhu 2005).
breaches in data warehouse
Generally, Data Warehouse is aimed to store huge amount of data for
longer period of time. Similar to database security, the security for data
warehouse also requires prevention form unauthorized access or modification and
availability of data to right persons. Initially, encryption was used for
maintaining security of data. Later on, the Database as a Service (DAS) was
used, where data is stored on cloud. Various other approaches are used to
maintain the security in Data Warehouse including Metadata base security
mechanism, Online Analytical Processing (OLAP) security design, Unified Modelling
Based (UML) based design, XML based solutions and may more (Oracle
& Paper 2005) (Bertino
& Sandhu 2005) (Gosain
& Arora 2015).
security and data warehouse
Database is Online Transaction Processing (OLTP), which maintain record
of transaction. Data warehouse is combination of databases, which supports
Online Analytical Processing (OLAP). It is necessary to maintain ethics and
security in database, to continue it in data warehouse. Database support its
response to single application. On the contrary, data warehouse responds to
multiple applications. For example is during transaction in database, name of
patient is hidden and only id is entered, then in data warehouse also hide name
of patient (Oracle
& Paper 2005) (Cardon
at data and client level via code
The users of database may gain the control of database management system
(DBMS) either legitimately or illegally, and can perform malicious activity,
which can be detected by auditing data logs. The exploration of audit log notifies
regarding compromised data and its contents. These logs are defined by DBMS and
may be tampered. More traces of security threats can also be notified with
exploration of abnormal use of main memory and hard disk.
Security implementation needs defined mechanism, which may contain list
of users, with their respective rights, security labels and so on. Another
security measures implemented is encryption techniques. Each user is provided
with the code through which only authenticate users can see and modify the
data. Moreover, the integrity of the data is managed by implementing
cryptographic checksums. Username and passwords are used to for authentication
of legitimate users (Andoh-baidoo
& Kasper n.d.).
The usage of analytical tools produces very effective way to visualizing
data for decision making by different stakeholders of organization, various
popular tools are used to further clarify the visualization including Tableau,
Pivot Tables, and so on. They are used on the data available in data warehouse,
which is long term data storage, contain huge amount of data. The security of
data stored in warehouses possess high significance and require proper
Andoh-baidoo, F.K. &
Kasper, G.M., Business Intelligence & Analytics Education?: An Exploratory
Study of Business & Non- Business School IS Program Offerings. , pp.1–9.
Anon, 2007. Don’t
Discard Those Spreadsheets: The Power of Excel-Friendly OLAP. , (February),
Bertino, E. &
Sandhu, R., 2005. Database Security — Concepts , Approaches , and Challenges. ,
Cardon, B.D., 2017.
Database vs Data Warehouse?: A Comparative Review. , pp.1–4.
Gosain, A. & Arora,
A., 2015. Security issues in data warehouse: A systematic review. Procedia
Computer Science, 48(C), pp.149–157.
Louis, S. et al., 2017.
Using the Dashboard Technology Properly. , (September 2008).
Mariu?a, ?., 2014.
Principles of Security and Integrity of Databases. Procedia Economics and
Finance, 15, pp.401–405. Available at:
January 7, 2018.
Oracle, A. & Paper,
W., 2005. Security and the Data Warehouse. , (April).
Pu, Q. et al., 2015. Low
Latency Geo-distributed Data Analytics. , pp.421–434.
Quirchmayr, N.K.G. &
Stolba, J.S.M., A Prototype Model for Data Warehouse Security Based on Metadata
Institute of Applied Computer Science and Information Systems.
Slater, R.D. et al.,
USING EXCELS PIVOT TABLE FUNCTION FOR VISUAL DATA ANALYSIS OF EXAM RESULTS?: A
SUPPLEMENTAL PROCEDURE TO CLASSICAL TEST THEORY.