Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 2 down vote accepted

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.
share|improve this answer

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.

share|improve this answer
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 :) – Paul Apr 15 '10 at 20:21
Sorry, no online resource I know of. This is something requiring experience. – TomTom Apr 15 '10 at 23:25

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".

share|improve this answer

I'd recommend a good book on the subject:

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 :)

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.