Welcome to the AtliQ Hardware Data Analytics project repository! In this project, we have implemented data analytics using Power BI to empower AtliQ Hardware with the ability to make data-driven decisions, surpass competitors in the market, and drive growth in various aspects of their business, including finance, sales, marketing, and supply chain management.
##Table of Content
Project Overview:
AtliQ Hardware specializes in manufacturing and distributing a wide range of hardware products including PCs , mouse, printers, and more through various channels/ mediums i.e. direct, retail and distributor. It's customers spans across the globe and includes renowned companies like Amazon, Croma, Neptune, Staples, and Walmart.
AtliQ Hardware is currently facing some challenges in Latin America, where they operate their business. Until now, they've been making important decisions based on surveys and instincts. However, as the company has grown, they've started collecting more data. They've realized that this data can be incredibly valuable for making better decisions. To make sense of this data, they've decided to hire a Data Analyst.
The management believes that by analyzing this information, they can make smarter and more accurate choices to improve their business in Latin America. Essentially, they want the Data Analyst to help them turn this wealth of data into actionable insights, ultimately improving their business operations in the region.
Tech stacks :
-
SQL
-
PowerBi Desktop
-
Excel
-
DAX language
-
DAX studio (for optimizing the report)
Learnings :
-
What questions should be asked before staring the project
-
Creating calculated columns
-
creating measure using DAX language
-
Data modeling
-
Using Bookmarks to switch between two visuals
-
Page navigation with buttons
-
Using divide function to prevent zero division errors
-
creating date table using m language
-
Using KPI indicators
-
Conditional formatting the values in visuals using icons or background color
-
Data validation techniques
-
PowerBi services
-
Publishing reports to PowerBi services
-
Setting up personal gateway to set up the auto refresh of data
-
Collaboration, workspace, access permissions in PowerBi services and more.
Business related terms :
-
Gross price
-
Pre-invoice deductions
-
Post-Invoice deductions
-
Net Invoice sale
-
Gross Margin
-
Net sales
-
Net profit
-
COGC - cost of goods sold
-
YTD - Year to Date
-
YTG - Year to Go
-
Direct channel
-
Retailer channel
-
Distributors channel
-
Consumer
Lets kick off our project, but before starting the project it's important to understand all the what's and why's and all other questions
Questions to ask before starting with dashboard :
objective of building this PowerBi dashboard?
success of the project would be based on what criteria?
What's the dead-line of the project?
do the stakeholders expects pre-view before the actual release?
what are all fears the stakeholder have in terms of building this dashboard?
Who would be our audience/stakeholder and what role they play in the project success?
What can go wrong while building this project?
is there any inputs from stakeholders in terms of design and views of the dashboard?
After the project kick off meetings, the data engineering team has given the data as per the request of data analytics team, let’s explore them.
Understanding data:
It's important to have a good understanding of available data.
available data as follow -
Dimension table : It contain the static data.
Fact table : It contain data about the transactions.
gdb041 DB: Tables stored in this database as follow:
--> dim_customer :
It contains details of customers i.e 27 distinct markets(ex India, USA), 75 distinct customers, 2 types of platforms(E-commerse, Brick & Motors) , 3 channels (Retailer, Direct, Distributor)
--> dim_market
This table stores market information 27 distinct market(ex India, Spain), 7 sub-zones, and 4 regions(APAC, EU, NAN, LaTAM) details
--> dim_product
This tales contains product details such as Divisions (P & A, N & S), Categories, varients
--> fact_forecast_monthly
This table is used to forecast the customer’s need in advance, which can help in Higher customer satisfaction, Reduced cost in warehouses for storage purpose.
--> fact_sales_monthly
This table is more or less is same as fact_forecase_monthly table, but the last column has the value of sold quantity instead of forecast value.
gdb056 DB : Tables stored in this Database as follows:
--> freight_cost
This table has details of travel cost and other cost for each market with fiscal year
--> gross_price
Has the details of gross prices with product code
--> manufacturing_cost
Has the details of manufacturing cost with product code with year
--> Pre_invoice_dedutions
Has the details of pre invoice deductions percentage for each cutomer with year
--> Post_invoice_deductions
Post invoice deductions and other deductions details
Importing data into PowerBi
As the database is MySQL in this project, we need to import the datasets from Mysql database to PowerBi by providing the Database access credential