Bird
Raised Fist0
Power BIbi_tool~15 mins

SQL Server connection in Power BI - Real Business Scenario

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
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.
Progress0 / 4 steps
Sample Data
OrderIDOrderDateRegionProductQuantitySalesAmount
10012024-01-05EastWidget A10500
10022024-01-07WestWidget B5300
10032024-01-10EastWidget C7420
10042024-01-12NorthWidget A3150
10052024-01-15SouthWidget B8480
10062024-01-18WestWidget C6360
10072024-01-20NorthWidget B4240
10082024-01-22SouthWidget A9450
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.

Practice

(1/5)
1. What is the main purpose of connecting Power BI to a SQL Server database?
easy
A. To import or query data directly for creating reports
B. To design the SQL Server database schema
C. To write SQL Server stored procedures
D. To backup the SQL Server database

Solution

  1. Step 1: Understand Power BI data sources

    Power BI connects to data sources like SQL Server to get data for reports.

  2. Step 2: Identify the purpose of connection

    The connection allows importing or querying data to visualize it in Power BI.

  3. Final Answer:

    To import or query data directly for creating reports -> Option A

  4. Quick Check:

    Power BI connection = data import/query [OK]
Hint: Power BI connects to get data, not to manage database [OK]
Common Mistakes:
  • Confusing Power BI with database design tools
  • Thinking connection is for database backup
  • Assuming Power BI writes SQL code
2. Which of the following is the correct way to specify the server name when connecting Power BI to SQL Server?
easy
A. ServerName/InstanceName
B. ServerName\InstanceName
C. ServerName-InstanceName
D. ServerName.InstanceName

Solution

  1. Step 1: Recall SQL Server instance naming

    SQL Server uses backslash \ to separate server and instance names.

  2. Step 2: Match correct syntax

    The correct format is ServerName\InstanceName, not with slashes or dots.

  3. Final Answer:

    ServerName\InstanceName -> Option B

  4. Quick Check:

    SQL Server instance separator = \ [OK]
Hint: Use backslash \ between server and instance [OK]
Common Mistakes:
  • Using forward slash or dot instead of backslash
  • Omitting instance name when required
  • Adding spaces around the backslash
3. You connect Power BI to SQL Server using DirectQuery mode. What happens when you refresh the report visuals?
medium
A. Power BI imports all data again from SQL Server
B. Power BI uses cached data and does not query SQL Server
C. Power BI queries the SQL Server live for updated data
D. Power BI disconnects from SQL Server after refresh

Solution

  1. Step 1: Understand DirectQuery mode

    DirectQuery mode means Power BI sends live queries to the database on demand.

  2. Step 2: Identify refresh behavior

    Refreshing visuals triggers live queries to SQL Server for current data.

  3. Final Answer:

    Power BI queries the SQL Server live for updated data -> Option C

  4. Quick Check:

    DirectQuery = live queries on refresh [OK]
Hint: DirectQuery means live data queries, not cached data [OK]
Common Mistakes:
  • Confusing DirectQuery with Import mode
  • Thinking refresh always imports all data
  • Assuming Power BI disconnects after refresh
4. You try to connect Power BI to SQL Server but get an error: "Cannot connect to server." What is the most likely cause?
medium
A. Incorrect server or database name entered
B. Power BI does not support SQL Server connections
C. Your computer is offline but server is reachable
D. You forgot to install Power BI Desktop

Solution

  1. Step 1: Check connection error meaning

    "Cannot connect to server" usually means wrong server or database info.

  2. Step 2: Rule out other causes

    Power BI supports SQL Server; offline computer means no connection; Power BI Desktop must be installed.

  3. Final Answer:

    Incorrect server or database name entered -> Option A

  4. Quick Check:

    Connection error = wrong server/database name [OK]
Hint: Double-check server and database names for typos [OK]
Common Mistakes:
  • Assuming Power BI lacks SQL Server support
  • Ignoring network or firewall issues
  • Not verifying Power BI installation
5. You want to create a Power BI report that always shows the latest sales data from SQL Server without importing all data each time. Which connection mode should you choose and why?
hard
A. DirectQuery, because it imports all data once
B. Import, because it loads all data once and never updates
C. Import, because it queries live data on demand
D. DirectQuery, because it queries live data on demand

Solution

  1. Step 1: Understand Import vs DirectQuery

    Import loads data once; DirectQuery queries live data each time.

  2. Step 2: Choose mode for latest data without full import

    DirectQuery fits because it fetches current data without importing all.

  3. Final Answer:

    DirectQuery, because it queries live data on demand -> Option D

  4. Quick Check:

    Latest data without import = DirectQuery [OK]
Hint: Use DirectQuery for live data, Import for snapshot [OK]
Common Mistakes:
  • Confusing Import with live querying
  • Choosing Import when data freshness is needed
  • Misunderstanding DirectQuery behavior
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard