Q1. You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes the dimUser and dimDistrict dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse; and the dimDistrict table contains information about sales districts. 

The system is accessed by users from certain districts, 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 districts. Some users must be permitted to see sales data from multiple districts. 

What should you do? 

A. Add a district column to the dimUser table. 

B. Partition the factSales table on the district column. 

C. Create a userDistrict table that contains primary key columns from the dimUser and dimDistrict tables. 

D. For each district, create a view of the factSales table that includes a WHERE clause for the district. 

View Answer

Q2. You are developing a SQL Server Integration Services (SSIS) package that is ready for deployment to a production server. The package contains sensitive information secured by using the EncryptSensitiveWithUserKey package protection level. 

You are preparing the package for deployment by the production operations team. 

You need to ensure that the production operations team can open and execute the package without re-entering the sensitive information. 

Which three steps should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.) 

View Answer

Q3. You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS). 

You need to complete the post-installation configuration. 

What should you do? 

A. Run the DQSInstaller.exe command. 

B. Install the data providers that are used for data refresh. 

C. Install ADOMD.NET. 

D. Run the dbimpexp.exe command. 

View Answer

Q4. To support the implementation of new reports, Active Directory data will be downloaded to a SQL Server database by using a SQL Server Integration Services (SSIS) 2012 package. 

The following requirements must be met: 

All the user information for a given Active Directory group must be downloaded to a SQL Server table. The download process must traverse the Active Directory hierarchy recursively. 

You need to configure the package to meet the requirements by using the least development effort. 

What should you use? 

A. script component 

B. custom component 

C. XML source 

D. script task 

View Answer

Q5. You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse. 

The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis. 

You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum. 

What should you do? 

A. Add an OnError event handler to the SSIS project. 

B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility. 

C. Use an msi file to deploy the package on the server. 

D. Open a command prompt and run the gacutil command. 

E. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server. 

F. Open a command prompt and run the dtutil /copy command. 

G. Create a reusable custom logging component and use it in the SSIS project. 

H. Configure the SSIS solution to use the Project Deployment Model. 

I. Configure the output of a component in the package data flow to use a data tap. 

J. Open a command prompt and run the dtexec /rep /conn command. 

K. Open a command prompt and run the dtexec /dumperror /conn command. 

View Answer

Q6. You are developing a data flow to load sales data into a fact table. In the data flow, you configure a Lookup Transformation in full cache mode to look up the product data for the sale. 

The lookup source for the product data is contained in two tables. 

You need to set the data source for the lookup to be a query that combines the two tables. 

Which page of the Lookup Transformation Editor should you select to configure the query? To answer, select the appropriate page in the answer area. 

View Answer

Q7. You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Windows Azure SQL Database database. The package has been deployed to a production server that runs Windows Server 2008 R2 and SQL Server 2012. 

The package fails when executed on the production server. 

You need to ensure that the package can load the Excel workbook data without errors. You need to use the least amount of administrative effort to achieve this goal. 

What should you do? 

A. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option. 

B. Enable Address Windowing Extensions (AWE) for the local SQL Server instance. 

C. Replace the SSIS Excel source with a SSIS Flat File source. 

D. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source. 

View Answer

Q8. You administer a large and complex SQL Server Integration Services (SSIS) solution in the SSIS catalog. 

You are instructed to execute a package by using PowerShell. You need to create the correct PowerShell command. 

How should you place the four code segments in sequence? (To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.) 

View Answer

Q9. You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named Quotes. The table has no indexes and consists of seven columns: 

 [ID] 

[QuoteDate] 

[Open] 

[Close] 

[High] 

[Low] 

[Volume] 

Each of the following queries must be able to use a columnstore index: 

SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] 

BETWEEN '20100101' AND '20101231'. 

SELECT AVG([High] - [Low]) AS [AverageRange] FROM Quotes WHERE 

[QuoteDate] BETWEEN '20100101' AND '20101231'. 

SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] 

BETWEEN '20100101' AND '20101231'. 

You need to ensure that the indexing strategy meets the requirements. The strategy must also minimize the number and size of the indexes. 

What should you do? 

A. Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate]. 

B. Create three coiumnstore indexes: 

One containing [QuoteDate] and [Close] 

One containing [QuoteDate], [High], and [Low] 

One containing [QuoteDate] and [Volume] 

C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and 

[Volume]. 

D. Create two columnstore indexes: 

One containing [ID], [QuoteDate], [Volume], and [Close] 

One containing [ID], [QuoteDate], [High], and [Low] 

View Answer

Q10. You are using the Knowledge Discovery feature of the Data Quality Services (DQS) client application to modify an existing knowledge base. 

In the mapping configuration, two of the three columns are mapped to existing domains in the knowledge base. The third column, named Team Type, does not yet have a domain. 

You need to complete the mapping of the Team Type column. 

What should you do? 

A. Add a column mapping for the Team Type column. 

B. Map a composite domain to the source column. 

C. Create a composite domain that includes the Team Type column. 

D. Add a domain for the Team Type column. 

View Answer