SQL Server connection in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a business analyst at a retail company.

📋 Request: Your manager wants you to create a sales report by connecting Power BI directly to the company's SQL Server database.

📊 Data: You have access to a SQL Server database with a Sales table containing columns: OrderID, OrderDate, Region, Product, Quantity, and SalesAmount.

🎯 Deliverable: Create a Power BI report that connects to the SQL Server database, loads the Sales data, and shows total sales by Region.

Sample Data

OrderID	OrderDate	Region	Product	Quantity	SalesAmount
1001	2024-01-05	East	Widget A	10	500
1002	2024-01-07	West	Widget B	5	300
1003	2024-01-10	East	Widget C	7	420
1004	2024-01-12	North	Widget A	3	150
1005	2024-01-15	South	Widget B	8	480
1006	2024-01-18	West	Widget C	6	360
1007	2024-01-20	North	Widget B	4	240
1008	2024-01-22	South	Widget A	9	450

1

Step 1: Open Power BI Desktop and click on 'Get Data'. Select 'SQL Server' as the data source.

Server: Enter your SQL Server name or IP address. Database: Enter the database name containing the Sales table. Choose 'Import' or 'DirectQuery' mode.

Expected Result

Power BI connects to the SQL Server and shows a list of tables.

2

Step 2: Select the 'Sales' table from the list and click 'Load' to import the data into Power BI.

No formula needed; just select the table and load.

Expected Result

Sales data is loaded into Power BI with columns OrderID, OrderDate, Region, Product, Quantity, and SalesAmount.

3

Step 3: Create a new report page. Add a 'Table' visual to the canvas.

Drag the 'Region' column to Rows and 'SalesAmount' column to Values. Set aggregation of SalesAmount to SUM.

Expected Result

The table shows total sales amount for each region.

4

Step 4: Format the table visual for clarity: sort by total sales descending and add a title 'Total Sales by Region'.

Use visual formatting pane to set sorting and title.

Expected Result

The report clearly shows regions ranked by total sales.

Final Result

-----------------------------------
| Total Sales by Region            |
|---------------------------------|
| Region | Total Sales            |
|--------|------------------------|
| East   | 920                    |
| West   | 660                    |
| South  | 930                    |
| North  | 390                    |
-----------------------------------

✓South region has the highest total sales of 930.

✓East region follows closely with total sales of 920.

✓North region has the lowest sales at 390.

Bonus Challenge

Create a line chart showing monthly sales trends by region using the same SQL Server connection.

Show Hint

Use the OrderDate column to extract month and year, then aggregate SalesAmount by month and region.