SQL Server Exercise for Data Warehouse Candidates

,

Background

Part of the process when looking for candidates to bring into a data warehouse team is to ensure that you hire competent and reliable people. We recently had to fill a data warehouse position in our team and I setup the following technical exercise that helps measure technical competencies of candidates. Candidates were required to do the exercise at their own time but return to us the solution within 3 days after having been given the exercise.

Skills Measured

  • Requirements Analysis & Documentation
  • Dimensional Modelling
  • SQL Server Server Transact SQL (T-SQL) Development
  • SQL Server Integration Services (SSIS) Development
  • SQL Server Reporting Services (SSRS) Report Development
  • SQL Server Analysis Services (SSAS) Development
  • Technical Solution Documentation

OLTP Data Source

Item Nr Transaction Date Fruit Quantity Customer MOP Account Number
1 20160101 Apple 1 CUSTA CASH 12345
2 20160111 Orange 9 CUSTB ELECTRONIC 123456
3 20160112 Banana 5 CUSTC ELECTRONIC 1234567
4 20160201 Watermelon 10 CUSTD ELECTRONIC 12345678
5 20160207 Grapes 17 CUSTE CASH 123456789
6 20160201 Apple 7 CUSTB CASH 123456
7 20160228 Grapes 4 CUSTB CASH 123456
8 20160228 Watermelon 8 CUSTB CASH 123456
9 20160211 Banana 3 CUSTB CASH 123456
10 20160124 Banana 13 CUSTD ELECTRONIC 12345678
11 20160122 Orange 15 CUSTC ELECTRONIC 1234567

Exercise 1

  1. Design, setup ETL (using SSIS – either using BIDS or SSDT) and populate a Fruits Data Mart. The mart should have the following:
    • Dimensions:
      • DimFruit
      • DimCustomer
      • DimMOP
    • Fact:
      • FactSales
  2. Produce a dimensional star-schema model of your Fruits Data Mart

Exercise 2

  1. Produce an SSRS Report which source data from Fruits Data Mart with a following business case:
    • As a user, I would like a breakdown of total quantities of fruits purchased by customer
    • I would also like to filter on Fruit name

Exercise 3

  1. Setup a FruitSales Cube with all dimensions from Fruits Data Mart
  2. In the same cube, FruitSales, setup an MOP hierarchy – with one level – MOP
  3. In the same cube, FruitSales, setup a calculated member titled DerivedQuantities which multiplies quantities of all fruits by 2

Exercise 4

  1. Produce an Excel Pivot Table Report which source data from FruitSales Cube with a following business case:
    • As a user, I would like a breakdown of DerivedQuantities of fruits purchased by customer
    • I would also like to filter on pivot using an MOP Slicer

Rate

Share

Share

Rate