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 |
|
helpme
Posting Yak Master
141 Posts |
 Posted - 2009-02-20 : 17:10:18
|
| We are looking at making some of our data warehouse information available to the public for ad-hoc querying. We currently have procedures(some long running) that run nightly that rebuild the tables making up the data warehouse with information from our transactional databases. Right now, these data warehouse tables aren't too heavily used, so if there is a problem during this nightly processing, it's not the end of the world if the tables aren't available for a little while the next morning. However, if they are opened up to the public, this becomes more of an issue. I'm thinking the solution may be build them in a staging database(s) and set up some kind of automated process to copy/restore these databases to the public ad-hoc query region only if the nightly processing succeeds. That way, if the nightly procedure doesn't succeed, the public tables are still accessible for ad-hoc queries, they will just not have the prior days updates included.Any thoughts? How have some of you handled similar situations? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-20 : 17:26:51
|
| Have you looked at Datamart which provides necessary Dimensions and Measures for public ad-hoc query? Or even ODS. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-21 : 02:32:08
|
quote:
Originally posted by helpme
We are looking at making some of our data warehouse information available to the public for ad-hoc querying. We currently have procedures(some long running) that run nightly that rebuild the tables making up the data warehouse with information from our transactional databases. Right now, these data warehouse tables aren't too heavily used, so if there is a problem during this nightly processing, it's not the end of the world if the tables aren't available for a little while the next morning. However, if they are opened up to the public, this becomes more of an issue. I'm thinking the solution may be build them in a staging database(s) and set up some kind of automated process to copy/restore these databases to the public ad-hoc query region only if the nightly processing succeeds. That way, if the nightly procedure doesn't succeed, the public tables are still accessible for ad-hoc queries, they will just not have the prior days updates included.Any thoughts? How have some of you handled similar situations?
we have implemented similarly in our warehouse. we've an intermediate staging server which will be populated by the delta changes each day and then based on if it was success we will be applying the changes to main warehouse db.any failures caused in etl step will leave the warehouse table with same state as previous data without containing latest deltas. |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2009-02-23 : 08:29:40
|
| Thanks visakh16. Are you doing this with stored procedures? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-02-23 : 10:23:01
|
| What kind of rebuilding are you doing that takes all night?Tell me you're not nuking and paving the data every night? I've seen this done...________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-24 : 10:11:13
|
quote:
Originally posted by helpme
Thanks visakh16. Are you doing this with stored procedures?
yup. stored procedure as well as ssis as etl tool. |
|
|
|
|
|
|
|
|
data warehouse question