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 PlaceIT. 4
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 2014).
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 2017).
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 2007).
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
The 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 organization (Louis et al. 2017).
Task 2: Considerations for the Data Warehouse Database security
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 pages.
Database 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 decisions (Cardon 2017)(Cardon 2017).
Security 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).
Security 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).
Database 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 2017).
Security 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 attention.
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), pp.15–19.
Bertino, E. & Sandhu, R., 2005. Database Security — Concepts , Approaches , and Challenges. , 2(1), pp.2–19.
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: http://www.sciencedirect.com/science/article/pii/S2212567114004651 Accessed 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.