When it comes to building a data warehouse, one of the key factors is ensuring that you have a competent and reliable team. Hiring the right candidates with the necessary technical skills is crucial for the success of your data warehouse project. In this article, we will discuss a technical exercise that can help measure the technical competencies of candidates when hiring for a data warehouse team.
Skills Measured
The technical exercise covers various skills that are essential for working with SQL Server and building a data warehouse. The skills measured include:
- 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
Technical Exercise
The exercise involves designing and setting up an ETL process using SQL Server Integration Services (SSIS) to populate a Fruits Data Mart. The data mart should have the following dimensions: DimFruit, DimCustomer, and DimMOP. The exercise also requires creating a fact table, FactSales, to store the sales data.
In addition to the ETL process, the exercise includes the following tasks:
- Produce a dimensional star-schema model of the Fruits Data Mart.
- Create an SSRS report that sources data from the Fruits Data Mart. The report should provide a breakdown of total quantities of fruits purchased by customer and allow filtering by fruit name.
- Setup a FruitSales Cube with all dimensions from the Fruits Data Mart. Additionally, create an MOP hierarchy with one level.
- In the FruitSales Cube, setup a calculated member titled DerivedQuantities, which multiplies the quantities of all fruits by 2.
- Produce an Excel Pivot Table report that sources data from the FruitSales Cube. The report should provide a breakdown of DerivedQuantities of fruits purchased by customer and allow filtering using an MOP slicer.
By completing this exercise, candidates can demonstrate their understanding and proficiency in various SQL Server concepts and tools commonly used in data warehousing projects.
Conclusion
Building a data warehouse requires a team with the right technical skills. By using a technical exercise like the one described in this article, you can assess the technical competencies of candidates and ensure that you hire the right people for your data warehouse team. Remember to evaluate their skills in requirements analysis, dimensional modeling, SQL Server development, SSIS, SSRS, and SSAS. With a competent team, you can successfully build a robust and efficient data warehouse.