Project Deliverable 6: Project Plan

Running head: DATABASE AND DATA WAREHOUSING DESIGN

DATABASE AND DATA WAREHOUSING DESIGN 10

Database and Data Warehousing Design

Necosa Hollie

Dr. Ford

Information Systems Capstone CIS499

May 5, 2019

Introduction

Somar and Co. Data Collection Company collects and analyzes data by using operational systems and web analytics. The data used in the analysis is collected from diverse operating systems such as ERP software. Various applications such as payrolls, human resources, and insurance claims are used in, modern-day enterprises and data from them keep on increasing day by day (Schoenherr, & Speier‐Pero, 2015). The ever-increasing data has been overwhelming organizations’ ability to analyze it due to its complex nature. This challenge has forced Somar and Co. Data Collection Company to seek a solution to it to deliver quality results to their clients. As the chief information officer (CIO) at the company, will be in charge of designing the solution that will incorporate data warehousing. This will make it possible to be consolidating large amounts of data quickly and be creating quality analytical reports within the shortest time possible.

Need for Data Warehousing

Data warehouses are central storage systems in companies where vital information from other applications such as ERP system is deposited. The data is periodically extracted from these applications. Data is sent to the data warehouse in different formats as different applications have distinct ways of keeping information. Then the data warehouse by having a uniform operational system will process and analyze discrete data into a more straightforward form. Somar and Co. Data Collection Company manages data from various clients with the information having been collected from multiple departments such as marketing, sales, and finance. To develop an active data warehouse, data consistency from different applications plays a crucial part (Waller, & Fawcett, 2013). This enables establishing of a constant process for all types of data. The information is analyzed for analytical reports, market research and decision report. The processed data also gives insight about the direction of the company to the management. The data is considered by the management during decision making and strategic planning.

Due to the importance of the data reposted in the data warehouse to the management, it should be analyzed in such a way that it is easy to comprehend and interpret (Schoenherr, & Speier‐Pero, 2015). As the processed data originates from different departments of the organization, this makes it be a reliable source of information to the management. If every department were to analyze its data, this would result in different information in different formats hence tricky for the administration to interpret it accurately. The data warehouse helps to resolve this problem by offering a centralized system where data from various departments is interpreted uniformly.

Building a data warehouse will benefit the organization from data mining tools and techniques. The process of data mining involves analyzing large amounts of data to find hidden patterns and relationship between different unrelated sets of data. As the information processed in the data warehouse comes in their unstructured and raw format, they do not make any sense until when analyzed. Data mining tools help to sort out through vast amounts of information in the data warehouse to find out any unique patterns (Sivarajah et al., 2017). After finding out specific patterns and relationship between various sets of data, it now becomes easy to predict where the organization is heading based on the current information. The information produced by data mining tools gives valuable information such as status reports to the organizational stakeholders. This kind of information is critical in determining the future direction of the organization.

In addition to providing data analytics, data warehousing also offers data visualization tools. The data visualization tools help people to understand the analyzed data better by using visual images. The data visualization tools go beyond using the standard graphs and charts displayed in excel sheets. Information is displayed using sophisticated methods such as infographics, gauges, dials, fever charts, sparklines and heat maps. Users can then access this information in the database warehouse through an interactive dashboard. Visualization tools expose some trends and patterns that fail to be recognized in text-based data. New visualization tools are invented every day to effectively identify trends, patterns, and correlations in advanced data analytics. Moreover, visual tools are more accessible to operate than the earlier statistical analysis software.

Speed is essential while considering any technological solution. Data warehouse offers real-time results during data analysis. The data warehouse has a higher processing capacity than the traditional operating systems. The ability to complete tasks within a short time makes the data warehouse to be a better solution to analyze a vast amount of data. The technology cannot be compared to human labor which is slower and needs being in large number to analyze a large amount of data. Decision making in most organizations is usually urgent and requires results within the shortest time possible making it convenient to use a data warehouse to analyze required data (Waller, & Fawcett, 2013). Also with the use of data warehouse, data can be accessed from multiple sources at any time. The system reduces reliance on IT professionals since all the information can be accessed from a single interface.

Security of confidential data is paramount as data breach may lead to a bad reputation and unrecoverable loss to the affected organization. Data warehouse offers limitation to access data from it. Only authorized users with user accounts can access data from it. Secure passwords with two identification factor can be created on users’ accounts to enhance their security (Sivarajah et al., 2017). The data in the data warehouse can be encrypted to prevent eavesdropping during information transfer. Encrypting the data deters users without decrypting keys from accessing information stored. This comes to Somar and Co. Data Collection Company at the right time, when they need a secure database and there are increased data breaches in companies all over the world.

Database Schema

A database schema is a logical illustration of either a section or a complete database. The picture includes the name and all components within the database. Database schema indicates definitions, attributes, and relationship among different entities in the data organization. The diagram below shows schema for a module of the Somar and Co. Data Collection Company database.

Figure 1: Database Schema

The above illustration indicates workers record for one of the Somar and Co. Data Collection Company customers. On the Schema, Workers’ personal information is stated together with details on their relationship with the employer. Their relationship information with the employer includes salaries, positions held, employer number and their employment dates.

Figure 2: Entity -Relationship Model

The above model helps to depict the relationship of employees with different aspects within the client’s system. For “works on” item, there is a many-to-many relationship, and on the works at” item, there is a one-to-many relationship (Sivarajah et al., 2017). Entity relationship model is essential because it acts as a remission point in case of unusual something happening along the way. The model is helpful in data organization within a company.

Figure 2: Data Flow Diagram (DFD)

The diagram encapsulates the layout and functionalities at various stages within the system.

Figure 4: Flow of Data used in the Data Warehouse

Operating system

ETL (Extraction, transformation and loading)

Sales

ERP System

Marketing

Data Warehouse

CRM system

OLAP Server

Procurement

SCM system Human Resources

Flat Files

Senior Management

Conclusion

Data analytics industry has experienced massive growth in recent years. This has compelled private organizations like Somar and Co. Data Collection Company to deploy advanced tools and techniques to process their data to keep up with the new trend and competition in the business world. Data warehousing facilitates growth in an organization among other benefits. More information at the fingertips is helping organizations with modern data houses have a competitive advantage and make informed decisions. I, therefore, recommend Somar and Co. Data Collection Company to the benefit of this opportunity to be one step ahead of the clients.

References

Schoenherr, T., & Speier‐Pero, C. (2015). Data science, predictive analytics, and big data in supply chain management: Current state and future potential. Journal of Business Logistics, 36(1), 120-132.

Sivarajah, U., Kamal, M. M., Irani, Z., & Weerakkody, V. (2017). Critical analysis of Big Data challenges and analytical methods. Journal of Business Research, 70, 263-286.

Waller, M. A., & Fawcett, S. E. (2013). Data science, predictive analytics, and big data: a revolution that will transform supply chain design and management. Journal of Business Logistics, 34(2), 77-84.