Case study overview: from SQL Server to an executive sales dashboard
This project connects Power BI to the SQL Server AdventureWorks database and builds a three-part sales dashboard covering an executive Sales Overview, Product Details and Customer Details.
The business problem. Sales data in a relational database is spread across many normalized tables. Executives want clear answers on revenue, profit, top products and best customers, but cannot get them from raw tables, and ad hoc SQL queries are slow to repeat.
The solution. Import the data from SQL Server, clean it in Power Query, reshape it into a star schema, and write DAX measures that every visual reuses. Report pages then give an executive summary first, with product and customer detail behind it.
Who it is for. Power BI developers, BI analysts, SQL Server professionals moving into reporting, and students who want a complete database-to-dashboard project.
The data: AdventureWorks in SQL Server
AdventureWorks is Microsoft’s sample database for a fictional bicycle manufacturer. It includes sales orders, products, product categories, customers and sales territories, which makes it a realistic base for sales analysis.
Connecting Power BI
- Restore the AdventureWorks database in SQL Server.
- In Power BI Desktop, choose Get Data, then SQL Server.
- Enter the server and database name, and choose Import mode.
- Select the sales, product, customer and territory tables, or write a SQL query that returns exactly the columns you need.
Import mode keeps visuals fast, and it suits a database of this size. Selecting only the needed columns and rows at the source reduces refresh time, which matters more as data grows. [State the AdventureWorks version you used and the date range of the orders.]
Cleaning in Power Query and building the star schema
Cleaning steps
- Set correct data types for dates, quantities and amounts.
- Filter to the years you want to report, so incomplete years do not distort trends.
- Remove unused columns, and rename the rest to business-friendly names.
- Combine customer name parts and product category fields into single columns.
- Create a month number column to sort month names correctly.
Star schema
The operational database is normalized, so the model reshapes it into one fact table and several dimensions.
| Table | Type | Typical contents |
|---|---|---|
| Sales | Fact | Order line, quantity, unit price, line total, cost, keys |
| Product | Dimension | Product name, subcategory, category |
| Customer | Dimension | Customer name, city, country |
| Territory | Dimension | Region, country, group |
| Date | Dimension | Date, month name, month number, quarter, year |
Relate each dimension to the fact table, one-to-many, and mark the Date table as the date table. Keep names in this table as examples and match them to the tables you import.
Essential and advanced DAX measures
Start with simple base measures, then build the advanced ones on top of them. Table and column names are examples, so match them to your model.
Essential measures
Total Sales = SUM ( Sales[LineTotal] )
Total Cost = SUM ( Sales[TotalCost] )
Total Profit = [Total Sales] - [Total Cost]
Profit Margin % = DIVIDE ( [Total Profit], [Total Sales] )
Total Orders = DISTINCTCOUNT ( Sales[OrderID] )
Total Quantity = SUM ( Sales[Quantity] )
Avg Order Value = DIVIDE ( [Total Sales], [Total Orders] )
Total Customers = DISTINCTCOUNT ( Sales[CustomerKey] )Advanced measures
Sales Prior Year =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Sales YoY % =
DIVIDE ( [Total Sales] - [Sales Prior Year], [Sales Prior Year] )
Sales YTD =
TOTALYTD ( [Total Sales], 'Date'[Date] )
Sales % of Total =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED () ) )
Sales Rank =
RANKX ( ALLSELECTED ( Product[ProductName] ), [Total Sales] )ALLSELECTED respects slicer choices while ignoring the filter from the visual’s own rows, which makes it the right tool for share-of-total and ranking measures.
Dashboard walkthrough
The report has three pages, from the executive view to the detail behind it.
| Page | Audience | Main visuals |
|---|---|---|
| Sales Overview | Executives | KPI cards for sales, profit, margin and orders; sales by month; map of sales by territory; year slicer |
| Product Details | Product and category managers | Top 10 products, sales by category matrix, profit margin by subcategory |
| Customer Details | Sales and marketing | Top customers, sales by country, customer count and average order value |
Techniques covered
- Sorting months correctly. Sort the month name column by the month number column, so months appear January to December instead of alphabetically.
- Top N filter. Apply a Top N filter on the product visual, ranked by total sales, to show the top 10 products. The filter updates with the slicers.
- Visual formatting. Format cards, matrix, map and slicers with a consistent theme, clear titles and number formats.
- Slicers and sync. Sync the year slicer across pages so the whole report follows one selection.
Explore the working report in the live dashboard.
Insights, business value and lessons learned
What the dashboard reveals
- [Headline: give total sales, profit and margin for your selected year.]
- [Trend: name the strongest and weakest months, and the year-over-year change.]
- [Products: name the top product and its share of sales, and the best category by margin.]
- [Customers: name the top customer or country and its share of sales.]
- [Territory: name the best and weakest sales territory.]
Replace each prompt with the figure shown in your live dashboard.
Business value
- Executives get one page for sales, profit and growth.
- Product and sales teams can see what drives revenue and margin.
- Measures are defined once, so every report reports the same number.
Dashboard design best practices
- Put the most important KPIs at the top, then trends, then detail.
- Limit each page to one purpose, and keep visuals aligned.
- Use consistent colours, number formats and titles.
- Use Top N filters to keep ranked visuals readable.
- Test measures against a SQL query before publishing.
Lessons for Power BI builders
- Filter and shape data at the source or in Power Query, not in visuals.
- Sort month names by month number, so charts read in calendar order.
- Build advanced measures from simple base measures.
Frequently asked questions
How do I connect Power BI to SQL Server AdventureWorks? In Power BI Desktop, choose Get Data, then SQL Server, enter the server and database name, choose Import mode and select the tables you need.
Why build a star schema from AdventureWorks? The database is normalized for transactions. A star schema with one fact table and dimensions makes filtering simpler and reports faster.
How do I sort months correctly in Power BI? Sort the month name column by a month number column, using the Sort by Column option.
How do I show the top 10 products in a visual? Add the product field to the visual, open the visual-level filter, choose Top N, enter 10 and set the value to total sales.
What DAX measures should a sales dashboard include? Start with total sales, cost, profit, margin, orders and average order value, then add year-over-year change, year-to-date and share-of-total measures.
Try it yourself
Explore the live dashboard, download the project files, and build it step by step with the video tutorial. Need a SQL Server and Power BI reporting solution for your organization? Contact datascientist.ca.