We use SSIS to build datawarehouse (full Microsoft environment). This is legacy SSIS package that starts by truncating all tables in datawarehouse, run nightly.

During this time, we restrict access to the application, this way the database is not altered during integration.

1/ can integration be done on Snapshot of the database, so that we can keep the app up and running?

2/ general question: how to handle the period when datawarehouse is rebuilt? Access to report should be forbidden until all tables are refilled with fresh data. Does exist a mecanism of balance between 2 datawarehouses ? It could be done with parameterised datasources in SSRS instead of shared datasource, but it seems to me like gasworks.

Environment: SQL Server 2005 and we plan to migrate to 2012.

  • A snapshot is read only so you could not point your integration at the snapshot. However, you can point your reports at the snapshot. During the rebuild of the warehouse, reports would look at the cached data in the snapshot. Once the datawarehouse load has completed, it'd signal reporting services that the DW is available. The signal would ultimately entail modifying the data source to switch the catalog from DW to DW_SNAPSHOT but I'd have to research on how to do that.
    – billinkc
    Mar 21, 2012 at 14:31
  • Do you think there are built-in functionalities (SSRS 2012) to change the "shared data source" from DW to DW_SNAPSHOT before integration, and DW_SNAPSHOT -> DW after (I'm looking for docs).
    – Francois B.
    Mar 21, 2012 at 16:29

2 Answers 2

  1. Database Snapshots are read-only, so you couldn't run read/write ETL processes against the snapshot. However, you could point reports, end-user adhoc queries, etc. at the snapshot. This would allow you to run the ETL processes against the live database without interfering with user reporting. A new snapshot would need to be created after ETL processes complete. Depending on available storage, you could store multiple snapshot copies.

  2. Regarding the data warehouse (DW) refresh, you may want to implement incremental load processes. Instead of mass truncate/insert operations, compare the staged source data with what already exists in the DW; Only insert/update the data that needs to change. This will give you a huge benefit in terms of resources used and time required for the ETL processes to complete.

  • 1/ Could you be more specific about "you couldn't run R/W ETL processes againts the snapshot"? For writing ops, I get it, but for read only ones? In my mind, DW is built in a different database than Snapshot, but points to it, no to the "real" one. 2/ Incremental load: is it related to Change Capture Data? I'm affraid of the complexity of updating statistics/counters/denormalized table made across several tables... so was born the idea of truncate everything.
    – Francois B.
    Mar 21, 2012 at 16:25
  • @FrancoisB. 1) What you're implying is correct, you can always read from the snapshot; Insert/Update/Delete operations are not available. Sorry I didn't communicate this well. 2) Incremental loading is a concept, you do not have to use Change Data Capture (CDC, and you won't be able to use this since you are on SQL Server 2005) to incrementally load data. The link provided in the answer is a sample implementation of an incremental load without CDC. I don't understand the statistics update/denormalization reference, please elaborate.
    – Bryan
    Mar 21, 2012 at 18:20
  • I think I've understand what is Incremental Load (as we plan to migrate to 2012, I do some tests using CDC - it's very easy to get, for each line in table, if it has been added, updated, deleted). Our thoughts are to create tables with aggregated data (fetch full name of client, amount of sales, etc) and also tables with counters (number of XX per YY). Since our app use custom roles defined by end-users, when one category or type or qualification changes, there are a lot of counters to update. And I wonder if it won't be "quicker" and "bug-proof" to clean and recompute everything.
    – Francois B.
    Mar 21, 2012 at 19:16

you have a pretty nasty scenario here. If your DW were only being accessed by a OLAP cube you would be fine because the data would be in it during the refresh (unless you have some ROLAP configured)

A snapshot would work for a read only DB during the rebuild process. You would have to create the snapshot before the update, point all your reports to it and them point them back when the update is over.

But why are you forbidding access to the source DB during the refresh? Are you afraid that someone would insert data while it is being copied to the destination DB? You could easily solve that with a good transaction control on your SSIS package.

  • Thanks for your answer. This is a legacy SSIS package that takes very long to execute (1 hour) and if someone connects itself to the app, or create client, it fails... We run it nightly. Now we are trying to run it more frequently, without offlining the app.
    – Francois B.
    Mar 21, 2012 at 19:19
  • ok, but where does the app insert data? on the source DB or on the destination DB?
    – Diego
    Mar 21, 2012 at 20:45
  • in source DB, but SSIS package is designed in a strange way, and near the last task, it reads data from source DB and try to match them with data from DW that it has created previously.
    – Francois B.
    Mar 21, 2012 at 21:58
  • do you have a insert date on this table on your source DB? You could ignore records inserted after the refresh started.
    – Diego
    Mar 22, 2012 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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