snippetMinor
SSRS: how to handle unavailability of service when datawarehouse is being rebuilt
Viewed 0 times
ssrshandleunavailabilitybeingservicehowdatawarehousewhenrebuilt
Problem
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.
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.
Solution
-
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.
-
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.
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.
-
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.
Context
StackExchange Database Administrators Q#15683, answer score: 2
Revisions (0)
No revisions yet.