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
- 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:
- Produce a dimensional star-schema model of your Fruits Data Mart
Exercise 2
- 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
- Setup a
FruitSales
Cube with all dimensions from Fruits Data Mart - In the same cube,
FruitSales
, setup an MOP hierarchy – with one level – MOP - In the same cube,
FruitSales
, setup a calculated member titled DerivedQuantities
which multiplies quantities of all fruits by 2
Exercise 4
- 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
This article was originally posted at
http://blogs.selectsifiso.com
Sifiso W. Ndlovu is a certified Microsoft professional who holds a Master’s degree in IT Management from the University of Johannesburg. He specializes on a range of enterprise and consumer technologies using open source and proprietary software. He is the member of the Johannesburg SQL User Group wherein he has made several presentations on User Group Meetings and SQL Saturday sessions. He has written for a number of publications including SQLShack.com and SQLServerCentral.com.