Click here to Skip to main content
13,801,061 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

6.6K views
2 bookmarked
Posted 21 May 2016
Licenced CPOL

SQL Server Exercise for Data Warehouse Candidates

, 21 May 2016
Rate this:
Please Sign up or sign in to vote.
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sifiso W Ndlovu
Technical Lead select SIFISO
South Africa South Africa
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.

You may also be interested in...

Comments and Discussions

 
QuestionGood task list - could you provide the solutions pls Pin
Josh 136341472-Mar-18 18:52
professionalJosh 136341472-Mar-18 18:52 
Questioni Need the solution Pin
shadi salmat24-Jul-17 3:47
membershadi salmat24-Jul-17 3:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web02 | 2.8.181215.1 | Last Updated 21 May 2016
Article Copyright 2016 by Sifiso W Ndlovu
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid