Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2003-09-08 : 09:40:58
|
| I'm looking to setup a Data Mart (I think). The system I administer is poorly structured and the reponse time of some queries/reports is correspondingly slow but I can't change that.What I am thinking of trying is creating a seperate database, which I can populate using either replication or DTS (not sure which) and put into tables structured for the queries/reports that we need and which may be de-normalized.Does anyone have any comments on this sort of approach. Is there something else that might work that doesn't involve beating our supplier or a complete re-write?thankssteveSteve no function beer well without |
|
|
rharmon
Starting Member
41 Posts |
Posted - 2003-09-08 : 10:02:12
|
| I spend a lot of time developing reporting systems, datawarehouses and datamarts. It's very hard to give you a perfect answer without knowing all the symantecs of the situation, but there are a couple of pointers I can probably throw in:First, replicate the production system to an intermediary database.Snapshot, transactional or log shipping is your choice, but if you replicate to an intermediary database, the queries you run for extracting the data won't get in the way of the operational customers. I generally try and maintain 30 to 45 days worth of transactions in the replicated database. If it becomes neccessary at a later date, this can be a good candidate database for an Operational Datastore.Once the database is replicated, denormalize to a second reporting database. Using DTS or Stored procedures to populate the denormalized datamart is your call, you may want to look into the Accellorator for Business Intelligence at http://www.microsoft.com/bi as it can save quite a bit of time building ETL processes, especially if you're looking at Analysis Services. Also remember, if you denormalize data, you should add constraints to ensure domain integrity is not violated.If this project is going to have legs, you'll probably want to read as much from Ralph Kimball and Bill Inmon as you can get your hands on.And last, partition a lot! Dimensional (denormalized) reporting structures tend to grow like mad. It's best to start with a partitioned environment even if you don't neccessarily need it in the short term as it will take care of a lot of headaches if you have to federate later.Hope this helps and let me know if you have any questions,Rob |
 |
|
|
|
|
|
|
|
To Data Mart or not to Data Mart