OData and REST API connections in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to create a report that shows the latest product sales data by connecting Power BI directly to the company's online sales system using OData and REST API connections.

📊 Data: You have access to the company's OData feed URL that provides product sales data including ProductID, ProductName, SalesDate, QuantitySold, and SalesAmount. Additionally, you have a REST API endpoint that returns customer feedback data with CustomerID, ProductID, FeedbackDate, and Rating.

🎯 Deliverable: Create a Power BI report that combines sales data from the OData feed and customer feedback from the REST API. The report should show total sales and average customer rating per product.

Sample Data

ProductID	ProductName	SalesDate	QuantitySold	SalesAmount
101	Wireless Mouse	2024-05-01	15	300
102	USB Keyboard	2024-05-01	10	250
101	Wireless Mouse	2024-05-02	20	400
103	Webcam HD	2024-05-02	5	150
102	USB Keyboard	2024-05-03	8	200
103	Webcam HD	2024-05-03	7	210

CustomerID	ProductID	FeedbackDate	Rating
C001	101	2024-05-02	4
C002	102	2024-05-02	5
C003	101	2024-05-03	3
C004	103	2024-05-03	4
C005	102	2024-05-04	4

1

Step 1: Connect Power BI to the OData feed URL to import product sales data.

In Power BI Desktop, go to 'Get Data' > 'OData Feed', enter the OData URL, and load the ProductSales table.

Expected Result

Product sales data with columns ProductID, ProductName, SalesDate, QuantitySold, SalesAmount is loaded into Power BI.

2

Step 2: Connect Power BI to the REST API endpoint to import customer feedback data.

In Power BI Desktop, go to 'Get Data' > 'Web', enter the REST API URL, use 'Advanced' to set HTTP method to GET, and parse the JSON response to extract CustomerFeedback table with CustomerID, ProductID, FeedbackDate, Rating.

Expected Result

Customer feedback data is loaded into Power BI with columns CustomerID, ProductID, FeedbackDate, Rating.

3

Step 3: Create a relationship between ProductSales and CustomerFeedback tables on ProductID.

In Power BI Model view, drag ProductID from ProductSales to ProductID in CustomerFeedback to create a one-to-many relationship.

Expected Result

Tables are linked by ProductID allowing combined analysis.

4

Step 4: Create a measure to calculate total sales amount per product.

Total Sales = SUM(ProductSales[SalesAmount])

Expected Result

Measure 'Total Sales' sums sales amount for each product.

5

Step 5: Create a measure to calculate average customer rating per product.

Average Rating = AVERAGE(CustomerFeedback[Rating])

Expected Result

Measure 'Average Rating' calculates average rating for each product.

6

Step 6: Build a report visual showing ProductName, Total Sales, and Average Rating.

Add a table visual with fields: ProductName, Total Sales, Average Rating.

Expected Result

Report table displays each product with its total sales and average customer rating.

Final Result

---------------------------------------------
| Product Name   | Total Sales | Avg Rating |
---------------------------------------------
| Wireless Mouse | $700        | 3.5        |
| USB Keyboard   | $450        | 4.5        |
| Webcam HD      | $360        | 4.0        |
---------------------------------------------

✓Wireless Mouse has the highest total sales but a moderate average rating of 3.5.

✓USB Keyboard has lower sales but the highest average customer rating of 4.5.

✓Webcam HD has the lowest sales and a good average rating of 4.0.

Bonus Challenge

Add a time filter slicer to the report to allow filtering sales and feedback data by SalesDate and FeedbackDate.

Show Hint

Create a date table and relate it to both ProductSales[SalesDate] and CustomerFeedback[FeedbackDate]. Use slicers linked to the date table.