You design a Business Intelligence (BI) solution by using SQL Server 2008. You plan to transform sales data from a retail sales outlet database to a SQL Server 2008 data warehouse by using SQL Server 2008 Integration Services (SSIS). The retail sales database is an online transaction processing (OLTP) database that processes large amounts of transactions twenty-four hours a day.
You need to design the structure of the SSIS packages such that the performance of the source system is minimally affected.
What should you do?
A.
Load and transform data from the source directly to the data warehouse once a day.
B.
Load data from the source to a staging database once a day. Then, transform the data to the data warehouse.
C.
Load and transform data from the source directly to the data warehouse four times a day at regular intervals of time.
D.
Load data from the source to a staging database four times a day at regular intervals of time.
Then, transform the data to the data warehouse once a day.
Explanation:
Tip: "structure of the SSIS" = "Load regular intervals / transform once a day"Using a Staging Server
In most situations (whether weve chosen to use a dedicated SSIS server or not), we create one SSIS package per data source. We load all types of source dataflat files, Excel, XML, relational, and so oninto a series of staging tables in a SQL Server instance. We then perform subsequent needed processing, such as validation, cleansing, and translations using SSIS processes. It is important to understand that the data stored on this SQL Server instance is used only as a pass-through for cleansing and transformation and should never be used for end-user queries. If you use SQL Server 2008 as a staging database, you can take advantage of several new relational features that can help you create more efficient load and update staging processes.
MERGE logic is also useful for building load packages that redirect data depending on whether it is new. You can think of MERGE as alternative to the built-in Slowly Changing Dimension (SCD) transformation for those types of business scenarios. MERGE performs a validation of existing data versus new data on load, to avoid duplicates among other issues. MERGE uses ID values to do these comparisons. Therefore, pay careful attention to using correct (and unique) ID values in data sources that you intend to merge.
(Smart Business Intelligence Solutions with Microsoft SQL Server 2008, Copyright 2009 by Kevin Goff and Lynn Langit)