You need to design a solution that meets these requirements
You are designing a data warehouse that contains a customer dimension.
The customer dimension contains the original customer attributes as well as the most recent
set of updated attributes. When a customer is updated, the original customer attributes must
be preserved.
The customer dimension design must:
Support a Type 3 Slowly Changing Dimension (SCD) process
Minimize the total storage consumed
You need to design a solution that meets these requirements.
What should you do?
You need to design a table structure to ensure that certain users can see sales data for only certain regions
You are designing a data warehouse hosted on Windows Azure SQL Database. The data
warehouse currently includes the dimUser and dimRegion dimension tables and the
factSales fact table. The dimUser table contains records for each user permitted to run
reports against the warehouse, and the dimRegion table contains information about sales
regions.
The system is accessed by users from certain regions, as well as by area supervisors and
users from the corporate headquarters.
You need to design a table structure to ensure that certain users can see sales data for only
certain regions. Some users must be permitted to see sales data from multiple regions.
What should you do?
Which data flow components should you use to identify modified data?
You are developing a SQL Server Integration Services (SSIS) package.
The package uses a data flow task to source data from a SQL Server database for loading
into a dimension table in a data warehouse.
You need to create a separate data flow path for data that has been modified since it was
last processed.
Which data flow components should you use to identify modified data? (Each correct answer
presents a complete solution. Choose all that apply.)
Which index type should you add to the table?
You are designing a data warehouse that uses SQL Server 2012.
The data warehouse contains a table named factSales that stores product sales. The table
has a clustered index on the primary key, four foreign keys to dimension tables, and an
aggregate column for sales totals. All key columns use the INT data type, and the aggregate
column uses the MONEY data type.
You need to increase the speed of data retrieval from the factSales table.
Which index type should you add to the table?
You need to ensure that the design can provide…?
You are designing an Extract, Transform and Load (ETL) solution that loads data into
dimension tables. The ETL process involves many transformation steps.
You need to ensure that the design can provide:
Auditing information for compliance and business user acceptance
Tracking and unique identification of records for troubleshooting and error correction
What should you do?
What should you use to set the variable?
You are implementing a new SQL Server Integration Services (SSIS) 2012 package that
loads data from various flat files and a Windows Azure SQL Database database.
Daily transactions must be loaded into a staging database. All the SSIS tasks will use the
CurrentDate variable as the transaction date.
You need to set the CurrentDate variable to the current execution date when the package
starts. You need to achieve this goal by using the least amount of development effort.
What should you use to set the variable?
You need to define and reuse the connection managers in all the packages by using the least development effort
You are designing a SQL Server Integration Services (SSIS) project that uses the Project
Deployment Model.
The project will contain 10 packages. All the packages must connect to the same data
source and destination.
You need to define and reuse the connection managers in all the packages by using the
least development effort.
What should you do?
Which property should you use?
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package to load data into a
SQL Server 2012 database.
The package is allowed to connect to only one database. An Environment variable contains
the name of the database.
The OLE DB project connection manager has been parameterized.
You need to configure the connection manager property to accept the value of the
Environment variable.
Which property should you use? (To answer, configure the appropriate option or options in
the dialog box in the answer area.)
Which Data Flow transformation should you use?
You are designing a SQL Server Integration Services (SSIS) data flow to load sales
transactions from a source system into a data warehouse hosted on Windows Azure SQL
Database. One of the columns in the data source is named ProductCode.
Some of the data to be loaded will reference products that need special processing logic in
the data flow.
You need to enable separate processing streams for a subset of rows based on the source
product code.
Which Data Flow transformation should you use?
You need to grant the minimum possible privileges to the QA testers
You are administering SQL Server Integration Services (SSIS) permissions on a production
server that runs SQL Server 2012.
Quality Assurance (QA) testers in the company must have permission to perform the
following tasks on specific projects:
View and validate projects and packages
View Environments and Environment variables
Execute packages
You need to grant the minimum possible privileges to the QA testers.
What should you do? (Each correct answer presents part of the solution. Choose all that
apply.)