In a previous blog post, I showed you how to connect to a SQL Server database using Python, and I mentioned other tools that you can leverage. One of those tools is Dash, a Python framework for creating data visualizations that abstracts a lot of the difficulties in building a web dashboard.
Let’s take a look at how to create a sales report with Dash leveraging the AdventureWorks database as the source. If you want to use containers, check out my blog post that covers creating a container for hosting your AdventureWorks database.
Getting Set Up
If you do not already have Python installed, please use one of the following links to install it:
Now, we need to install all the Python components that are required.
Let’s dig in and get to creating the Sales report.
Creating the Sales Report
Let’s create an app.py file that will host the code and then open it in your favorite editor. We are going to import the Python libraries we need. We will be using several libraries from a previous post to connect to SQL Server using Python—Dash and pandas being the new additions. Earlier, I shared what Dash enables; pandas is a data analysis library for Python that Dash has native support for using.
Now, we can create the Dash application.
Next, we need to set up the database connection. Since I am using Docker and the AdventureWorks container that I linked to earlier, I will be using the connection information in that blog post.
Let’s create the SQL query that will bring back the data we need to make the report. We will return the salesperson’s name, sales quota, and sales year-to-date.
We have all the critical pieces in place to leverage pandas to execute the query and return a DataFrame. Pandas DataFrame is the crucial data structure in pandas and can be leveraged by Dash natively. The read_sql method takes the query and the connection that you want to use to retrieve the data.
Now that we have the data, we need to define the two bar charts that we want to create. We will create one for sales year-to-date by salesperson and one for sales quota by salesperson.
All that is left is to build the page layout to create the dashboard.
Finally, we need to wire up the special main method to start up the dashboard application when you execute the app.py file. When you deploy this in a production environment, you need to set debug to false.
Here is the complete file.
Running the Dash Application
Now that we have created the Dash application, we can execute the application using the Python app.py command.
If we navigate to http://127.0.0.1:8050, we should see the following set of bar graphs.
Next Steps
I hope this blog post has helped you get started using Dash with SQL Server to create a dashboard. Dash has many features and can create rich and interactive dashboards that would be too much to cover here.
