Blog Post

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 NrTransaction DateFruitQuantityCustomerMOPAccount Number
120160101Apple1CUSTACASH12345
220160111Orange9CUSTBELECTRONIC123456
320160112Banana5CUSTCELECTRONIC1234567
420160201Watermelon10CUSTDELECTRONIC12345678
520160207Grapes17CUSTECASH123456789
620160201Apple7CUSTBCASH123456
720160228Grapes4CUSTBCASH123456
820160228Watermelon8CUSTBCASH123456
920160211Banana3CUSTBCASH123456
1020160124Banana13CUSTDELECTRONIC12345678
1120160122Orange15CUSTCELECTRONIC1234567

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

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating