The assessment consists of business requirements, modeling requirements, an OLTP normalized, physical data model and some questions. The candidate is asked to review the requirements and data model and create a dimensional model that will serve as the basis for a data warehouse. There are also some follow-up questions.
The current version of the assessment asks for a dimensional model but has some questions that cross over into the physical world and assumes a SQL Server environment. It can probably be improved or at least made applicable to more organizations by making it more generic. However the position I created it for requires some knowledge of SQL Server so I incorporated it into the test.
Below is the problem statement and the physical OLTP data model. You can download the entire assessment here. Please e-mail me at firstname.lastname@example.org if you'd like the answers.
You are a consultant and have been engaged by a manufacturing company to develop a business intelligence solution to help them reduce their raw material costs. Understanding the Purchase Order process which includes both purchases and receipts against those purchases is key to any potential cost reduction program.
The project’s sponsor has requested a first iteration of a Kimball-style dimensional model representing the material acquisition process. This model will be the basis of a star-schema based data warehouse. It is your task to develop this model.