data warehouse design question

[aleks1429]

i have an oltp db that gets updated with data every week or so that i would like to report from....

should i create a new data warehouse design where data is going to flow from the oltp into data warehouse db and then build SSAS on top of DW?

or should i just use the existing oltp and construct my SSAS on top of it?

are there cases where each one would work and wouldn't?

thanks

 

[RiverGuy]

It's best practice to design and populate a Star or Snowflake schema to use as the source for SSAS, though not absolutely required. There is also a good chance that the star schema itself would be used for some reports outside of SSAS--and that way you can keep your dimensions and measures uniform. Here's a blog posting with a few other reasons:

http://biperformance.spaces.live.com/Blog/cns!DC1DCE8A0D78EEA7!162.entry

 

[johnherman]

I would use the staging approach as it allows cleansing of the data as well as separation of the OLTP from OLAP activities. So, stage the data in a DW, datamart, or ODS, then load the SSAS. As RiverGuy mentions, get a good star schema design (SSAS is good at helping you get there).

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright