DW Modeling Help

My company has tasked me with creating a data warehouse to support the reporting needs of one of our customers. To give a little background, my company is in the criminal justice industry and we have a well-normalized database that is organized into two main tiers: Client and Case. The Client table contains information such as: name, DOB, race, etc. That table is related to other tables for addresses, employment, family info, vehicle info, etc. The Case table contains information such as: offense, officer name, case number, case status, begin date, end date, etc. It is also related to the Client table. The Case table also has many other tables related to it. Examples would include tables for case notes, hearings, and risk assessments.

I am getting tripped up trying to determine what should be in my dimension tables and what should be in my fact tables. Any and all help is appreciated.

The fundamental rule is: A fact represents a business event or state and dimensions provide context for the fact. Fact tables contain FKs to the dimensions and measures (numbers representing the magnitude of the event or state). Dimensions contain attributes.

For example, serving a subpoena is an event. Being a resident of the grey bar hotel is a state. Who, what, where, when, and why are contexts.

You need to read two books, the ETL Toolkit and the Data Modeling Toolkit. Those two have all the information you need (sans reporting). Right now you are just focusing on the target models. Additionally you will need ETL, staging areas, auditing processes, and reporting. That's a lot of expertise for one person.