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
- 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
- Dimensions:
- 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 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
