Building a Data Warehouse

I've seen tutorials articles and posts on how to build datawarehouses with star and snowflakes schemas, denormalization of OLTP databases fact and dimension tables and so on.

Also seen comments like:

Star schemas are for datamarts, at best. There is absolutely no way a true enterprise data warehouse could be represented in a star schema, or snowflake either.

I want to create a database that will server for reporting services and maybe (if that isn't enough) install analisys services and extract reports and data from cubes.

My question was : Is it really necesarry to redesign my current database and follow the star/snowflake schemas with fact and dimension tables ?

Thank you

From serverfault Paul

• It pretty much is, unless you dump the whole SQL side and build the repository in a Cube - in which case you MAY get away with an OLTP schema underlying the data.

  The main problem is that a non-star-schema approach simply puts a lot of burden on the server for analysis. That said, the idea to sue analysis services is terrific - they shine in this area. Just try whether you can directly load them from... the OLTP schema, possibly a snapshot of that.

  Paul : If you give me a good resource (tutorial) on how to build and make best use of OLAP you've got yourself a winning answer :)

  TomTom : Sorry, no online resource I know of. This is something requiring experience.
  From TomTom

• Another part of the rationale of the data warehouse is that any computations to massage or transform data are done prior to loading it into a particular schema so that much what is pulled from a data warehouse is "ready to use".

  From jl

• I'd recommend a good book on the subject: http://www.amazon.co.uk/Microsoft-Data-Warehouse-Toolkit-Intelligence/dp/0471267155/ref=sr_1_3?ie=UTF8&s=books&qid=1272019644&sr=8-3

  Although it is targeted at 2005 (2008 version I think is in the pipeline) the general theory holds well, and the design and planning steps are almost platform independent anyway.

  Worth it's weight in gold if you are looking to get into DW :)

  From Meff

• There are few things I would look at before redesigning your database.

  1. I'm pretty sure that reporting services doesn't need a star/snowflake to do its work so you might see what you can build with your nomalized database.
  2. Try building views that denormalize your OLTP data. It will get you thinking about the design aspects that you will need if you do redesign your database.
  From CTKeane