VIET NAM NATIONAL UNIVERSITY OF HCM CITY UNIVERSITY OF ECONOMICS AND LAWS FACULTY OF INFORMATION SYSTEM DATA ANALYTICS IN BUSINESS PROJECT REPORT TOPIC: ANALYZE SALES DATA OF ADVENTUREWWORKS USING POWER PI Project implementation: Number Student Name Student ID 1 Vo Thi Ngoc Trinh K204110610 2 Nguyen Tran Bich Ngoc K204110602 3 Truong Do Dang Khoa K204110598 4 Nguyen Thanh Phat K204110605 Instructor guide: M. Le Ba Thien HCM City, 04th January 2024 DECLARATION We declare that our project is founded on the outcomes of our individual work, which we completed under the guidance of lecturer Le Ba Thien. The team's sources and materials are listed in the references section at the conclusion of the report. The remaining content is entirely genuine and is derived from a research procedure.
In the event of an error, the team will take full accountability. Ho Chi Minh City, January 04th, 2024 Group 5. i ACKNOWLEDGEMENT Our team would like to show our sincere appreciation to Mr. Le Ba Thien, the lecturer, for your enthusiastic guidance and passing of essential knowledge to us during this course.
Because of that, we have gained plenty of helpful information that we can afterward apply in real work. "Data Analysis In Business" is a wonderfully fascinating, very practical, and highly valuable subject. Along with our interest in this subject, the team put up a lot of effort in addition to researching more essential information. Yet, the research is undoubtedly not perfect since there are still many limitations of knowledge in this field, as well as undeveloped skills.
In order for the group to benefit from the experience and perform better on the following projects, I hope that you will give consideration to and offer suggestions. Sincerely thanks! Ho Chi Minh City, ii Too long to read on your phone? Save to read later on your computer TABLE OF CONTENTS DECLARATION .i Save to a Studylist ACKNOWLEDGEMENT. ii TABLE OF CONTENTS. iii LIST OF TABLES .vi LIST OF FIGURES.
The reason for choosing the topic:. Subject and research scope of the project:. Structure of report:. THEORETICAL BACKGROUND AND RELATED WORKS.
Overview of BI:. Introduce BI model and solution. The benefits of BI in the business. Data analysis and visualization.
Theory and Methods in Data Analysis. ANALYSIS OF USER REQUIREMENTS AND DATA DESCRIPTION. Apply the development life cycle of a data analytics project. Identify and analyze user requirements.
Business overview analysis. Sales person analysis. SQL Server Integration Services. Data Extraction Using SSIS:.
Loading Data into SQL Server:. Designing Dim and Fact Tables:. Populating Dim and Fact Tables:. Maintenance and Optimization:.
Data Quality and Consistency:. Overview of the data warehouse. Description of data building reports. Data warehouse model.
EXPERIMENTAL RESULTS AND ANALYSIS. Data analysis and visualization. Evaluation and Suggestion. Thread development direction.
46 v LIST OF TABLES Table 3. Description of dimension tables. Description of dimension tables. Fact_Sales table.
Fact_Targets table. Dim_Product table. Dim_Region table. Dim_Reseller table.
Dim_Salesperson table. Dim_Date table. The relationships in Data Warehouse schema. 32 vi LIST OF FIGURES Figure 2.
Data visualization by Power BI. The Process of Data Analysis in Six Steps. Data visualization of General Analysis. Data visualization of Revenue Analysis.
Dashboard of Product Analysis. Dashboard of Employee Evaluation. The reason for choosing the topic: Recognizing data as an indispensable component for any firm, the aim is to conduct thorough research to explore diverse perspectives on handling data effectively. The ultimate goal is to furnish the business with a plethora of intelligent and practical recommendations.
The decision to delve into this subject is underpinned by our belief that a comprehensive understanding of data, combined with our foundational knowledge in the business domain, will empower us to engage in meaningful data mining activities. By leveraging our insights into the business area, we anticipate being able to extract valuable information and contribute substantially to the overarching goals of the research. Specifically, the chosen company for analysis is Adventure Works, a prominent global entity engaged in the manufacturing and sale of diverse products, ranging from clothing and accessories to bicycle parts and complete bicycles. Operating in a commercial market that spans six countries across three continents — Australia, North America (United States and Canada), and Europe (United Kingdom, France, and Germany) — Adventure Works presents a rich and diverse dataset for investigation.
Furthermore, the delineation of the company's primary sales channels, namely online and wholesaler sales, adds an additional layer of complexity to the analysis. This multi-faceted approach aligns with our intention to explore various dimensions of data, providing a well-rounded perspective on how AdventureWorks operates in its global market. Through this exploration, we aim to contribute valuable insights that can inform data-driven decision-making processes and strategies for businesses operating in a multifaceted, global marketplace. Topic goal: - Analyze the business model from the perspectives of revenue, staff, and product.
1 - Sort the best-selling items, then group consumers and areas according to them. - Build a report including four dashboards: + General business situation + Detailed business situation by product + Detailed business situation according to employee + Detailed business situation according to revenue - Make some suggestions for the business. - Create some potential paths for the topic's development. - Make a proposal for Adventure Works company's future business plan based on the 4P model (Product - Price - Place - Promotion).
Subject and research scope of the project: - Subject: Microsoft's AdventureWorks database, a free dataset. - Research scope: Information from the Manufacturing, Sales, Purchasing, Product Management, Reseller Management, and Human Resources is investigated in this research. Tools used: - SQL Server. Research implications: After finishing this research, Adventure Works can: - Identify target customers.
- Review the statistical information of the company to build some strategies and change it promptly. 2 - Statistics on employee capacity and performance, thereby providing appropriate business strategies as well as rewards or training. Structure of report: Chapter 1: TOPIC OVERVIEW Chapter 2: THEORETICAL BACKGROUND AND RELATED WORKS Chapter 3: ANALYSIS OF USER REQUIREMENTS AND DATA DESCRIPTION Chapter 4: EXPERIMENTAL RESULTS AND ANALYSIS Chapter 5: CONCLUSION 3 CHAPTER 2. THEORETICAL BACKGROUND AND RELATED WORKS 2.
Overview of BI: In the contemporary business landscape, organizations find themselves amassing vast volumes of data pertaining to various facets such as sales, inventory, customer details, and supplier information, alongside comprehensive employee records. Despite the abundance of data, its utility in guiding managerial decisions remains limited. Establishing a unified database for the systematic classification and organization of this data holds immense potential. This approach enables businesses to retrospectively assess past performance while also facilitating the anticipation of future scenarios.
The adoption of Business Intelligence (BI) emerges as a pivotal solution in this context. Coined by Howard Dresner in 1989, BI is a broad term encompassing a range of concepts and methodologies aimed at enhancing decision-making through diverse information techniques. As articulated by Turban et al (2008), BI can be conceptualized as a suite of applications and techniques designed to collect, store, analyze, and provide data access, empowering business users in their decision- making processes. The scope of BI applications spans decision support systems (DSS), query and reporting, online processing analysis (OLAP), statistical analysis, forecasting, and data mining.
Carlo (2009) further refines the definition, portraying BI as a collection of mathematical models and analytical methods that delve into existing data to extract valuable information and knowledge crucial for decision-making. By incorporating BI methodologies, businesses gain a competitive edge by enabling managers to make faster and more informed decisions. This integrated approach fosters a comprehensive understanding of past business scenarios, empowering enterprises to predict and navigate future situations with greater precision. In essence, BI emerges as an indispensable tool, ushering in a new era of strategic decision-making for businesses aiming to stay ahead in today's dynamic and competitive markets.
Introduce BI model and solution Figure 2. BI model Within the framework of Business Intelligence (BI), a comprehensive model consists of key components aimed at enhancing data -driven decision-making for businesses. These components include: - Data Modeling: The data modeling process entails the analysis and definition of data types and interconnections within the business context. This includes the creation of conceptual, logical, and physical data models, employing text, symbols, and diagrams.
- Data Mining: Data mining is an automated process focused on revealing patterns and anomalies within data, employing diverse analytical techniques such as exploratory, descriptive, statistical, and predictive analytics. - Data Visualization: The process of data visualization involves presenting findings in an intuitive and interactive manner through mediums such as dashboards, charts, graphs, and maps. - Data Action: Data action encompasses the decision-making and implementation of actions guided by data insights. This includes adapting operational processes, understanding customer behavior, monitoring performance, establishing benchmarks, and addressing challenges.
5 These components collectively form a robust BI model, empowering businesses to make more informed decisions and enhance their efficiency, profitability, and competitiveness. By incorporating these elements into their operations, organizations can leverage the full potential of BI to navigate dynamic market conditions and achieve sustainable growth. The benefits of BI in the business Numerous scholarly papers underscore the advantages of Business Intelligence (BI) in the corporate landscape, highlighting several key points: Informed Strategic Decisions: BI plays a pivotal role in empowering businesses to make well-informed strategic decisions, delivering accurate and timely data and insights crucial for navigating the dynamic business landscape. - Trend and Pattern Identification: BI serves as a valuable tool for businesses to discern trends and patterns within their data, offering insights into customer behavior, market demand, sales performance, and operational efficiency.
- Performance and Revenue Optimization: BI becomes a catalyst for businesses seeking to enhance performance and revenue through the optimization of marketing and sales strategies, the improvement of customer satisfaction and retention, and the reinforcement of competitive advantage. - Operational Efficiency Enhancement: Businesses leverage BI to elevate operational efficiency, undertaking measures to reduce costs, eliminate waste, streamline processes, and bolster overall quality and productivity. - Opportunity Discovery through Predictions: Through the power of BI, businesses uncover opportunities for improvement by harnessing predictive capabilities, whether it be in forecasting demand, identifying risks, or receiving actionable recommendations. - Creation of Smarter and Faster Reports: BI empowers businesses to generate reports that are not only smarter and faster but also easily comprehensible, shareable, and actionable, facilitating efficient decision-making processes.
Data analysis and visualization Data analysis involves the exploration of extensive stored data to unveil novel relationships, patterns, and trends. This process employs pattern recognition technologies, statistical methods, and mathematical techniques to scrutinize repositories comprehensively. Conceptually, data analysis can be likened to "data drilling" in depth and "data aggregation" in breadth, delving into data from multiple perspectives to discern relationships among its components. This approach aims to uncover hidden trends, patterns, and past experiences within the data warehouse, ultimately supporting operational processes and decision-making.
A crucial aspect of the broader business intelligence landscape is data visualization. Simply put, data visualization entails presenting a specific dataset in a visual format, including charts, graphs, maps, and more. The graphical representation of text-based data allows for the identification of new insights and concealed patterns that might be challenging to discern in raw, non-graphical forms. The primary motivation behind data visualization is to identify patterns, trends, and relationships among diverse datasets that might be less apparent in a non- graphical representation.
This visual approach enhances users' understanding of market dynamics and facilitates the evaluation of customer needs. Consequently, businesses can evolve by developing new strategies and techniques to enhance their operations. Recognizing the significance of this, software companies are channeling their efforts into optimizing their Business Intelligence (BI) tools to provide the most effective data visibility. This emphasis on data visibility is integral to unveiling concealed information within the warehouse, contributing to more informed decision- making processes.