AdventureWorksDW - Python Series#1 - Computing & Plot Yearly Sales for a Territory

Connecting Python with SQL Server
If you are looking for Python Corporate Training - Instrovate Corporate Training
 If you are looking for Python Consulting Work - Instrovate Consulting Services

 

Prerequisites To Follow this Exercise : 

  1. Microsoft SQL Server Database Express Edition & Adventure Works DataWarehouse - If you Don't have a Microsoft SQL Server express Database and want to install it in your system and also install AdventureWorks DW , Follow -https://instrovate.com/2019/05/22/download-install-free-microsoft-sql-server-install-adventureworks-database-data-warehouse/ 
  2. Python Installed in your System : If you are a new user to Python and want to know how to install Python via the Anaconda Distribution , You can go through the step by step Blog i have written to install Python via Anaconda Distribution & start using Jupyter Notebook : https://instrovate.com/2019/06/09/python-anaconda-distribution-how-to-download-and-install-it-and-run-the-first-python-program/

Once you have the Microsoft SQL Server Express Edition and Python Installed in your system you are Good to Go ahead and follow the below Use Case and Example. 

 

Computing & Plot Yearly Sales for a Territory in AdventureWorksDW - Python.

Considerations:

  • The SQL Server 2017 is used as a back end to store AdventureWorksDW
  • We will connect Python to connect to SQL Server to fetch the required data
  • We will use pyodbc library of python to connect to SQL Server from Python
  • The territory for which we will be computing Sales is United Kingdom

The transaction of orders are stored in fact table named FactInternetSales in the Database AdventureWorksDW.

The column SalesAmount in the table FactInternetSales  has the sales amount of the order in consideration.

The dimension table DimSalesTerritory has all the details of territories from which orders are being received.

As marked above the column SalesTerritoryKey is the joining column between the fact and dim table FactInternetSales and DimSalesTerritory. The column SalesTerritoryCountry has the country name. For our problem we will take United KIngdom as the territory for yearly sales analysis.

So, our final output which will be having yearly sales of the territory named United Kingdom will be having data from two tables i.e. FactInternetSales and DimSalesTerritory.

So, the sql query to fetch the desired details are as follows :

Year  is the sql server inbuilt function to fetch the value of year from the date field. IN the query summation is performed on column SalesAmount of the table FactInternetSales . And hence, using the other columns in the group by clause which are present in select clause.

To learn how to connect python to sql server you can refer to the below Instrovate Technologies Blog:

https://instrovate.com/2019/04/12/how-to-connect-python-with-sql-server/

Below is the python code to solve the problem :

Code Key Points:

  • After the query execution is done in python initialize two python lists namely years and total_sales.
  • While iterating through each row the list years is being populated with second indexed value in the row and total_sales is being populated with fourth indexed value.
  • Please note the index count starts from zero
  • The matplotlib  library of python is being used to plot total sales against year.
  • The plot  function from matplotlib.pyplot is used to plot 2 dimensional Data. For more details below blog can be referred:

https://instrovate.com/forums/topic/plotting-actual-vs-predicted-sales/

After executing the above python program below is the outcome displayed  :

 
 
 
If you would like to have additional examples on Data Visualization over AdventureWorks DW using Python you can visit the below link on our Company Website 
 
 

Comments

There are no entries yet.
Please enter the code
* Required fields

Subscribe to Our YouTube Channel To recieve updates on Free Learning

 

 

Visit Our Website to View the Upcoming Training Schedule

Looking for Corporate Training ? Reach out to us at Akriti.Lal@instrovate.com

Reach out to us if you are looking for Corporate Training to Build The Next Generation Analytical Workforce with an in-depth understanding of  Exploratory Data Analysis , Data Visualisation, Data Analytics , AI First , Machine Learning & Deep Learning Training & Consulting helping them to take Data Informed Decision at each stage of the business. 

We understand that At the present times , the Entire Industry is in a Tranformation stage with the Softwares  being rebuilt with Artificial Intelligence Capabilities .

We need SMART WORKFORCE for the SMART SOFTWARES to reap the maximum return . 

 

Whatsapp at +91-9953805788 or email at - akriti.lal@instrovate.com if you would like to know more . 

 

  • Corporate Tableau Training in Gurgaon
  • Corporate Data Analytics Training in Gurgaon
  • Corporate Microsoft Power BI Training in Gurgaon
  • Corporate Microstrategy Training in Gurgaon
  • Corporate Google Data Studio Training in Gurgaon
  • Corporate Python Training in Gurgaon
  • Corporate Advance Analytics in R Programming Training in Gurgaon
  • Corporate Machine Learning Training in Gurgaon
  • Corporate Deep Learning Training in Gurgaon
  • Corporate Data Visualization Training in Gurgaon

 

Address

Instrovate Technologies

Noida, Gurgaon

 

akriti.lal@instrovate.com 

Hit Your Refresh Button To Rise Higher

Subscribe to Our YouTube Channel To Receive Updates on the Latest Free Learning Video Added

Print Print | Sitemap
All rights reserved @ Instrovate Technologies
GSTIN : 09AAECI6862K1Z1

Terms Of Services    Privacy Policy    Disclaimer     Refund Policy    


Call

E-mail