In product_category the distinct value of each category will be stored. In the list sales_cat the sale for each sub-category will be stored. Also to compute the total sales per Category Wise (Parent Filed) we will initialize one dictionary by the name sales. one list having data for the X – Axis and one list having data for the Y – Axis. Now to plot the data we would need two lists for each plot i.e. To make the visualization the programmer must be familiar with the basic functionality of matplotlib library in python for which the below forum can be referred: This is relevant when we process data in python as sequence of fields or columns in each row is important. If we are running the same query from Microsoft SQL server the output will looks like as follows: So, once we have query executed, now the next is to fetch the data.
#Adventureworks database w code#
The code piece for the above solution is as follows: After connection is established the python code would execute the above query and fetch the results in a python data structure. So, the python solution for the above problem would begin with making ODBC connection from python to Microsoft SQL server by using the library pyodbc.
#Adventureworks database w how to#
To learn how to connect python to Microsoft SQL Server please refer to below blog: Now we will write a python code to connect to AdwentureWorksDW database stored in Microsoft SQL server. The sql query for the same is as follows: Since ProductCategory is the parent for ProductSubCategory so we will take the sum of SalesAmount based on ProductSubCategory with a column giving the value of parent field ProductCategory. So, the first step is to write a sql query that can fetch the sum of SalesAmount based on ProductSubCategory and ProductCategory. The join between DimProductSubCategory and DimProductCategory can be made using field ProductCategoryKey.
The join between DimProduct and DimProductSubCategory can be made using field ProductSubcategoryKey. The join between FactInternetSales and DimProduct can be made using field ProductKey. The Data Model to fetch the required data is as follows: In the data model of AdventureWorksDW the fact table FactInternetSales has the transactions where in we find the sales amount incurred in each order transaction.
The visualization will contain the bar chart to showcase the sales for different categories and subcategories of the products. In this blog we create a visualization in Python over AdventureWorksDW to showcase the Sales of different Products according to their category and sub category.
0 kommentar(er)
