!pip install dash
!pip install pyodbc
!pip install dash_core_components
import dash
from dash import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output
import plotly.graph_objs as go
import pyodbc
import pandas as pd
Connect to the AdventureWorks database
conn_str = ‘DRIVER={SQL Server};SERVER=DESKTOP-LQ3ADLT\SQLEXPRESS;DATABASE=AdventureWorks;UID=DESKTOP-LQ3ADLT_;PWD=your_password’
conn = pyodbc.connect(conn_str)
Create Dash app
app = dash.Dash(name)
Define function to execute SQL queries
def execute_query(query):
cursor = conn.cursor()
cursor.execute(query)
columns = [column[0] for column in cursor.description]
data = cursor.fetchall()
return pd.DataFrame(data, columns=columns)
Define layout
app.layout = html.Div(children=[
dcc.Tabs(id=‘tabs’, value=‘products’, children=[
dcc.Tab(label=‘Products’, value=‘products’),
dcc.Tab(label=‘Customers’, value=‘customers’),
dcc.Tab(label=‘Sales Orders’, value=‘sales_orders’),
dcc.Tab(label=‘Purchases’, value=‘purchases’)
]),
html.Div(id=‘tab-content’)
])
Define callback to update tab content
@app.callback(
Output(‘tab-content’, ‘children’),
[Input(‘tabs’, ‘value’)]
)
def update_tab(tab):
if tab == ‘products’:
# Products summary
products_df = execute_query(“”"
– Total number of products
SELECT COUNT(*) AS TotalProducts FROM Production.Product;
-- Top-selling products
SELECT TOP 10 p.Name AS ProductName, SUM(od.OrderQty) AS TotalSales
FROM Sales.SalesOrderDetail od
JOIN Production.Product p ON od.ProductID = p.ProductID
GROUP BY p.Name
ORDER BY TotalSales DESC;
-- Product categories and their sales
SELECT pc.Name AS CategoryName, SUM(od.OrderQty) AS TotalSales
FROM Sales.SalesOrderDetail od
JOIN Production.Product p ON od.ProductID = p.ProductID
JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
GROUP BY pc.Name
ORDER BY TotalSales DESC;
""")
# Create tables
total_products_table = html.Table([
html.Tr([html.Th('Total Products')]),
html.Tr([html.Td(products_df.iloc[0]['TotalProducts'])])
])
top_products_table = html.Table([
html.Tr([html.Th('Product Name'), html.Th('Total Sales')]),
*[
html.Tr([
html.Td(row['ProductName']),
html.Td(row['TotalSales'])
]) for index, row in products_df.iloc[1:11].iterrows()
]
])
category_sales_table = html.Table([
html.Tr([html.Th('Category Name'), html.Th('Total Sales')]),
*[
html.Tr([
html.Td(row['CategoryName']),
html.Td(row['TotalSales'])
]) for index, row in products_df.iloc[11:].iterrows()
]
])
return html.Div([
html.H2('Products Summary'),
html.Div(total_products_table),
html.Div([html.H3('Top Selling Products'), top_products_table]),
html.Div([html.H3('Category Sales'), category_sales_table])
])
elif tab == 'customers':
# Customers summary
customers_df = execute_query("""
-- Total number of customers
SELECT COUNT(*) AS TotalCustomers FROM Sales.Customer;
-- Top-spending customers
SELECT TOP 10 c.FirstName + ' ' + c.LastName AS CustomerName, SUM(so.TotalDue) AS TotalSpent
FROM Sales.Customer c
JOIN Sales.SalesOrderHeader so ON c.CustomerID = so.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.LastName
ORDER BY TotalSpent DESC;
-- Customers by region
SELECT a.City, COUNT(*) AS CustomerCount
FROM Person.Address a
JOIN Sales.Customer c ON a.AddressID = c.CustomerID
GROUP BY a.City;
""")
# Create tables
total_customers_table = html.Table([
html.Tr([html.Th('Total Customers')]),
html.Tr([html.Td(customers_df.iloc[0]['TotalCustomers'])])
])
top_customers_table = html.Table([
html.Tr([html.Th('Customer Name'), html.Th('Total Spent')]),
*[
html.Tr([
html.Td(row['CustomerName']),
html.Td(row['TotalSpent'])
]) for index, row in customers_df.iloc[1:11].iterrows()
]
])
customers_by_region_table = html.Table([
html.Tr([html.Th('City'), html.Th('Customer Count')]),
*[
html.Tr([
html.Td(row['City']),
html.Td(row['CustomerCount'])
]) for index, row in customers_df.iloc[11:].iterrows()
]
])
return html.Div([
html.H2('Customers Summary'),
html.Div(total_customers_table),
html.Div([html.H3('Top Spending Customers'), top_customers_table]),
html.Div([html.H3('Customers by Region'), customers_by_region_table])
])
elif tab == 'sales_orders':
# Sales orders summary
sales_orders_df = execute_query("""
-- Total number of sales orders
SELECT COUNT(*) AS TotalOrders FROM Sales.SalesOrderHeader;
-- Sales orders by year and month
SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, COUNT(*) AS TotalOrders
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY Year, Month;
-- Total sales amount over time
SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, SUM(TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY Year, Month;
""")
# Create graphs
sales_orders_graph = dcc.Graph(
figure={
'data': [
go.Scatter(
x=sales_orders_df['Year'].astype(str) + '-' + sales_orders_df['Month'].astype(str),
y=sales_orders_df['TotalOrders'],
mode='lines+markers',
name='Total Orders'
)
],
'layout': {
'title': 'Sales Orders Over Time',
'xaxis': {'title': 'Date'},
'yaxis': {'title': 'Total Orders'}
}
}
)
total_sales_graph = dcc.Graph(
figure={
'data': [
go.Scatter(
x=sales_orders_df['Year'].astype(str) + '-' + sales_orders_df['Month'].astype(str),
y=sales_orders_df['TotalSales'],
mode='lines+markers',
name='Total Sales'
)
],
'layout': {
'title': 'Total Sales Amount Over Time',
'xaxis': {'title': 'Date'},
'yaxis': {'title': 'Total Sales'}
}
}
)
return html.Div([
html.H2('Sales Orders Summary'),
html.Div([html.H3('Sales Orders Over Time'), sales_orders_graph]),
html.Div([html.H3('Total Sales Amount Over Time'), total_sales_graph])
])
elif tab == 'purchases':
# Purchases summary
purchases_df = execute_query("""
-- Total number of purchases
SELECT COUNT(*) AS TotalPurchases FROM Purchasing.PurchaseOrderDetail;
-- Purchases by store
SELECT v.Name AS VendorName, COUNT(*) AS TotalPurchases
FROM Purchasing.Vendor v
JOIN Purchasing.PurchaseOrderHeader po ON v.VendorID = po.VendorID
JOIN Purchasing.PurchaseOrderDetail pod ON po.PurchaseOrderID = pod.PurchaseOrderID
GROUP BY v.Name
ORDER BY TotalPurchases DESC;
-- Purchases by product category
SELECT pc.Name AS CategoryName, COUNT(*) AS TotalPurchases
FROM Purchasing.PurchaseOrderDetail pod
JOIN Production.Product p ON pod.ProductID = p.ProductID
JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
GROUP BY pc.Name
ORDER BY TotalPurchases DESC;
""")
# Create tables
total_purchases_table = html.Table([
html.Tr([html.Th('Total Purchases')]),
html.Tr([html.Td(purchases_df.iloc[0]['TotalPurchases'])])
])
purchases_by_store_table = html.Table([
html.Tr([html.Th('Vendor Name'), html.Th('Total Purchases')]),
*[
html.Tr([
html.Td(row['VendorName']),
html.Td(row['TotalPurchases'])
]) for index, row in purchases_df.iloc[1:11].iterrows()
]
])
purchases_by_category_table = html.Table([
html.Tr([html.Th('Category Name'), html.Th('Total Purchases')]),
*[
html.Tr([
html.Td(row['CategoryName']),
html.Td(row['TotalPurchases'])
]) for index, row in purchases_df.iloc[11:].iterrows()
]
])
return html.Div([
html.H2('Purchases Summary'),
html.Div(total_purchases_table),
html.Div([html.H3('Purchases by Store'), purchases_by_store_table]),
html.Div([html.H3('Purchases by Product Category'), purchases_by_category_table])
])
if name == ‘main’:
app.run_server(debug=True)
