DW Modeling Help

View previous topic View next topic Go down

DW Modeling Help

Post  jjervis on Thu Nov 01, 2012 11:35 am

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.

jjervis

Posts : 1
Join date : 2012-11-01
Age : 34
Location : Rochester, IN

View user profile http://pbssolutions.com

Back to top Go down

Re: DW Modeling Help

Post  ngalemmo on Thu Nov 01, 2012 1:01 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: DW Modeling Help

Post  BoxesAndLines on Thu Nov 01, 2012 1:58 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: DW Modeling Help

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum