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

<!-- #tablepress-3 from cache -->

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 the 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

CodeProject