1. Find the total revenue generated for each product.

SELECT p.ProductName, SUM(s.Revenue) AS TotalRevenue

FROM Sales s

JOIN Products p ON s.ProductID = p.ProductID

GROUP BY p.ProductName;

• Explanation: This query joins the Sales and Products tables on the ProductID column and groups by ProductName to calculate the total revenue for each product.

2. Revenue cannot have negative values. Identify and replace negative values with NULL.

SELECT SaleID, ProductID, CustomerID, SaleDate,

       CASE WHEN Revenue < 0 THEN NULL ELSE Revenue END AS Revenue

FROM Sales;

• Explanation: This query uses a CASE statement to check if the Revenue is negative and replace it with NULL. If it's not negative, it retains the original value.

3. Find customers who have made multiple purchases, along with their total revenue.

SELECT s.CustomerID, c.CustomerName, COUNT(s.SaleID) AS TotalPurchases, SUM(s.Revenue) AS TotalRevenue

FROM Sales s

JOIN Customers c ON s.CustomerID = c.CustomerID

GROUP BY s.CustomerID, c.CustomerName

HAVING COUNT(s.SaleID) > 1;

• Explanation: This query finds customers who have made more than one purchase by counting SaleID and summing Revenue. The HAVING clause filters out customers with only one purchase.

4. Calculate the monthly revenue for the first quarter of 2023.

SELECT MONTH(SaleDate) AS Month, SUM(Revenue) AS MonthlyRevenue

FROM Sales

WHERE SaleDate BETWEEN '2023-01-01' AND '2023-03-31'

GROUP BY MONTH(SaleDate);

• Explanation: This query calculates the monthly revenue for the first quarter of 2023 by summing the Revenue for each month in the specified date range.

5. For each product category, find the customer who generated the highest revenue.

WITH CategoryRevenue AS (

    SELECT p.Category, s.CustomerID, SUM(s.Revenue) AS TotalRevenue

    FROM Sales s

    JOIN Products p ON s.ProductID = p.ProductID

    GROUP BY p.Category, s.CustomerID

)

SELECT Category, CustomerID, MAX(TotalRevenue) AS MaxRevenue

FROM CategoryRevenue

GROUP BY Category;

• Explanation: This query first calculates total revenue for each customer per product category and then identifies the customer with the highest revenue for each category.

6. Ensure every sale in the Sales table has a valid ProductID and CustomerID.

SELECT *

FROM Sales

WHERE ProductID NOT IN (SELECT ProductID FROM Products)

   OR CustomerID NOT IN (SELECT CustomerID FROM Customers);

• Explanation: This query checks for invalid ProductID and CustomerID values in the Sales table by ensuring they exist in the Products and Customers tables, respectively.

7. Write a query to identify invalid rows.

SELECT *

FROM Sales

WHERE ProductID IS NULL OR CustomerID IS NULL OR Revenue < 0;

• Explanation: This query identifies rows in the Sales table where the ProductID or CustomerID is missing, or where the Revenue is negative, marking them as invalid.

Power BI & SSRS/SSIS-Related Questions

8. What is the importance of data modeling in Power BI?

• Answer: Data modeling is crucial in Power BI as it defines how data tables are related, ensuring accurate reporting. It enables users to create meaningful calculations and aggregations, which helps in developing complex reports and dashboards. Data models are also optimized for performance, ensuring that large datasets are handled efficiently.

9. What are the differences between Import and Direct Query modes? When would you choose one over the other?

• Answer:
• Import Mode: Data is loaded into Power BI’s in-memory engine, providing faster performance for large datasets.
• DirectQuery Mode: Data remains in the source system and queries are sent live when needed, ideal for real-time data but can be slower.
• When to use: Choose Import for performance and large datasets that don’t change frequently. Choose DirectQuery for real-time data or when the dataset is too large to import into memory.

10. What is the purpose of creating Dataflows in Power BI?

• Answer: Dataflows are used to define, transform, and clean data in Power BI before it is loaded into datasets. They provide reusable transformations that can be used across multiple reports and dashboards, ensuring consistency and reducing redundancy.

11. A report has duplicate data due to incorrect joins in the data model. How would you identify and resolve this issue?

• Answer:
• Identification: Examine the relationships between tables in the data model to identify unnecessary or incorrect joins.
• Resolution: Remove or correct the join, possibly using LEFT JOIN instead of INNER JOIN if appropriate. Consider using aggregation functions or DISTINCT to eliminate duplicates in the data.

12. Suppose your client needs a report with drill-through functionality to analyze individual product sales. How would you implement this in Power BI?

• Answer:
• To implement drill-through, create a separate drill-through page in Power BI.
• Add a drill-through filter (e.g., ProductID) to the page.
• When users click on a product in the main report, they can right-click and choose to drill through to the detailed report for that product.

13. Your report needs to display data only from the past 3 months dynamically. How would you accomplish this?

• Answer:
• Use DAX to create a calculated column or measure that filters data based on the current date:

FilteredData = IF(DATEDIFF(Sales[SaleDate], TODAY(), MONTH) <= 3, 1, 0)

• Apply this as a filter in your report or visualization to only show data from the last 3 months.

14. What is SSIS, and how is it used in ETL processes?

• Answer: SSIS (SQL Server Integration Services) is a tool for data extraction, transformation, and loading (ETL). It is used to automate the process of moving and transforming data from different sources into a data warehouse or another destination.

15. Explain the difference between a Control Flow and a Data Flow in SSIS.

• Answer:
• Control Flow: Defines the sequence of tasks and processes in an SSIS package (e.g., executing SQL queries, running scripts).
• Data Flow: Specifies how data moves and is transformed within the package (e.g., extracting data, applying transformations, and loading data).

16. What are the key components of an SSIS package?

• Answer: Key components include:
• Control Flow: Organizes tasks and containers in a sequential order.
• Data Flow: Moves and transforms data.
• Tasks: Perform actions like executing SQL or running scripts.
• Connections: Define connections to source and destination data.
• Variables: Store values used across the package.

17. How do you handle errors in SSIS? Describe the types of logging available.

• Answer:
• Errors can be handled by redirecting failed rows or using Error Output to capture and handle data issues.
• Logging Options:
• SSIS Log Provider: Logs events like package execution, task failure, or progress.
• SQL Server Profiler: Used to trace and monitor SQL-based operations within SSIS.

18. What is the difference between Merge and Merge Join transformations?

• Answer:
• Merge: Sorts data from two sources and then combines them based on a common key, similar to a union.
• Merge Join: Performs an actual join (inner, left, or full outer join) between two datasets, ensuring more complex transformations.

19. How do you parameterize an SSIS package for deployment in different environments?

• Answer:
• Use SSIS package configurations or parameters to store environment-specific values (e.g., connection strings).
• Define package parameters and variable mappings to ensure the package runs correctly across different environments.