You are the database administrator for a manufacturing company. Your company sells the products it manufactures to wholesale distributors and directly to consumers. Your company wants to create a data warehouse to analyze past sales revenue, analyze key performance indicators, predict products that will be popular with new customers, and make decisions about discontinuing specific product lines.
SQL Server 2008 Enterprise Edition is installed on the server computer named Srvr1. At weekly intervals, you must load the data from the following data stores into staging tables of the DW1 database on the server:
Customer demographic data and order data that is captured and maintained in an Oracle database using an Oracle-based order entry system
Historical customer sales data stored in SQL Server tables
Historical records of machine downtime that are captured manually by machine operators and stored in Microsoft Excel
Specific rules must be enforced when loading data into the staging tables so that only valid data that does not already exist in the data warehouse is transferred.
Which component should you use to load the data?
A. SQL Server Reporting Services (SSRS)
B. SQL Server Analysis Services (SSAS)
C. SQL Server Integration Services (SSIS)
D. SQL Server Notification Services (SSNS)
E. Microsoft Sync Framework
Correct Answer: C
Explanation/Reference:
SSIS can create packages to retrieve data from multiple data sources, such as the Oracle database, SQL Server tables, and Microsoft Excel in this scenario. You can then transform the data as needed into a standard format, cleanse it, and load it into a variety of data sources, such as the staging tables in this scenario.
During the transfer process, you can transform, validate, cleanse, and manipulate the data as needed, or combine data from multiple data stores. For example, you can aggregate or sort data, convert data types, or create new calculated columns from the existing data. In addition to SSIS’ traditional ETL function, SSIS also can be used to access data on demand from different data sources. For example, SSIS provides a DataReader destination you can use as a data source to access data using a DataReader. With SSIS, you can often bypass or minimize staging. SSIS also exposes .NET Framework and native APIs that you can use to extend SSIS’s functionality or integrate with legacy systems or third-party products.
You should not use SQL Server Analysis Services (SSAS) to load the data. SSAS provides tools to create and maintain SSAS databases. You can use SSAS to define cubes and dimensions and to perform complex data analysis, including defining and analyzing key performance indicators and making business predictions based on the data. In this scenario, after the data is loaded into the staging areas with SSIS, you could use SSAS to create the data warehouse, analyze past sales revenue and key performance indicators, predict products that will be popular with new customers, and make decisions about discontinuing specific product lines.
You should not use SQL Server Reporting Services (SSRS) to load the data. SSRS is a SQL Server component used to create, generate, deliver, and process reports from a variety of data sources, such as relational data, multidimensional data, or XML. You can create traditional reports directly from a data source or from SSAS databases. You can create reports in a variety of formats, export reports to other applications, allow users to interactively drill down through reports to explore the data, provide scheduled or on-demand reports, and even programmatically integrate reporting with custom applications or meet complex reporting requirements. SSRS also provides security features to allow only authorized users to access reports.
SSIS, SSAS, and SSRS can be used together to create complex Business Intelligence (BI) applications using SQL Server Business Intelligence Development Studio (BIDS).
You should not use SQL Server Notification Services (SSNS). SSNS provides support for notification applications in previous SQL Server versions. SSNS allows you to generate and send notifications to database users. With SQL Server 2008, notification support is incorporated into SSRS.
You should not use Microsoft Sync Framework. The Microsoft Sync Framework supports collaboration by providing services and tools to synchronize databases, files, and files systems, provide roaming support, and provide access to offline data.
