Microsoft SQL Server is a relational database management system widely used by top industries. It has a large user base across different industries such as IT, healthcare, retail, insurance, finance, education, management, sales, and marketing for their storage and data processing operations. In this article, we learn how to prepare a dashboard to analyze product sales and prioritize products in production with the Contoso BI demo database using Bold BI Dashboards.
Defining metrics to analyze product sales
Let’s consider the following metrics to analyze product sales:
- The total sales and total return amounts.
- Difference in return amounts among product categories.
- Return quantities by product category during each sales year.
- Top 10 products with lowest production cost.
- Top 10 products with good returns.
- Distribution of return amount by year and month.
Installing Microsoft SQL Server and configuring Contoso BI demo database
Install the SQL Server in your local machine or in a remote accessible server by referring to the Microsoft documentation.
Furthermore, Download, extract, and restore the Contoso BI demo database by referring to the Install Instructions.
Creating a dashboard and data source
Firstly, select the New Dashboard tile in the homepage and click Start from Scratch of the Bold BI Dashboard Platform. The Dashboard Designer page will open with a new blank dashboard created like in the following.
Connecting to Microsoft SQL Server database
- Select the data source icon on the right. The data source panel will expand as shown in the following.
- Similarly, select CREATE NEW in the previous panel.
- Furthermore, choose the category SQL in the Categories drop-down list. As a result, this list shows the data connectors available under this category like in the following.
- Finally, choose the Microsoft SQL connector. The Microsoft SQL data connection window will display in the DATA SOURCES panel like in the following.
Creating a data source
- Firstly, name the data source as ContosoBIDataSource at Name field in the NEW DATA SOURCE window.
- Furthermore, fill the SQL Server name where your database resides in Server name field. For example, remotemachine(or)ipaddress,1433 where, 1433 is the port number. In addition, the SQL Server running at remote machine should be reachable for Bold BI Dashboard’s site.
- Likewise, fill the login details at User name and Password fields.
- Finally, choose the database in Database field for Contoso BI demo data. For example, the database name is ContosoRetailDW.
Editing the data source
- Firstly, navigate to the dashboard design page and expand the DATA SOURCES panel on the right.
- Furthermore, click the Edit icon near the ContosoBIDataSource data source. As a result, the data source window will open like in the following.
- Likewise, drag and drop the FactSales table and related dimension tables such as DimProduct, DimProductSubcategory, and DimProductCategory by using INNER JOIN with their respective keys.
Most importantly, the actual relationship among these tables is like in the following image.
- As a result, the data source editing view is like in the following image.
- Finally, save the data source.
Configuring widgets in dashboard
Select the widget to configure and click the Widget Settings icon at the top-right corner of the focused border like in the following.
As a result, the panel on the right side expands toward the left. Choose the ASSIGN DATA tab and start configuring the mentioned fields in corresponding sections as described in the following table.
|Widget Title||Widget Type||Data Configuration|
|Sales year||Combo Box|
|Return amount comparison among product subcategories||Doughnut Chart|
|Total sales amount||Card|
|Total return amount||Card|
|Return quantity comparison by product category level in each sales year||Column Chart|
|Top 10 products with lowest production cost||Bar Chart|
|Top 10 products based on return amount||Bar Chart|
|Return amount comparison for each month of sales year||Heatmap|
Finally, the sales dashboard is ready.
Now, share this dashboard with your sales team to analyze the sales and make wise decisions about product(s) to improve them.
We hope this article helps you build a sales analysis dashboard with Microsoft SQL Server data using the Bold BI Dashboards. For any questions or clarifications, please post us through the comments section available below. You can also contact us by submitting your queries here or if you already have an account, please login to submit your support query. Bold BI Dashboards now comes with a 15-day free trial with no credit card information required. Give it a try on your own and let us know what you think! We are happy to assist you. Design with a smile!