Q1. 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.)
Answer:
Q2. 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?
A. Clustered
B. Semantic search
C. Nonclustered
D. XML
Answer: C
Q3. You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables.
What should you do?
A. Create a view on the sales table.
B. Partition the fact tables by day.
C. Create a surrogate key for the time dimension.
D. Change the level of granularity in both fact tables to be the same.
Answer: D
Explanation: In order to join the facts table in time dimension we cannot have two different time measures (time and day).
Q4. You are designing a fact table in a SQL Server database.
The fact table must meet the following requirements:
Include a columnstore index.
Allow users to choose up to 10 dimension tables and up to five facts at one time.
Maximize performance of queries that aggregate measures by using any of the 10
dimensions.
Support billions of rows.
Use the most efficient design strategy.
You need to design the fact table to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the columnstore index on the dimensional key columns.
B. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the columnstore index on the measure columns.
C. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on the dimensional key columns and the measure columns.
D. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on only the measure columns.
Answer: C
Q5. Your team is creating SQL Server Integration Services (SSIS) packages that have several dependencies. The packages use parameters for configuration purposes.
Your company's IT policies include the following change control requirements:
. After a package is ready for deployment, your team must hand over the process to junior IT personnel. . The process must guarantee that when a package has been validated, that same package (and all its dependencies) is deployed to production. . The process must be repeatable and reliable and must be executed with the least administrative and training effort by junior IT personnel.
You need to use the most appropriate deployment unit to satisfy the company policies, while minimizing issues such as incorrect version or configuration.
Which type of deployment unit should you use?
A. dtsx
B. SSISdeploymentmanifest
C. msi
D. ispac
Answer: D
Q6. You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse.
You need to establish the correct order for loading each table to maximize parallel processing.
Which order should you use?
A. 1. DimCurrency, DimScenario, DimAccount in parallel
2. DimOrganization
3. FactFinance
B. 1. DimCurrency, DimOrganization in parallel
2. DimScenario, DimAccount in parallel
3. FactFinance
C. 1. DimCurrency, FactFinance in parallel
2. DimOrganization, DimScenario, DimAccount in parallel
D. 1. FactFinance
2. DimOrganization, DimScenario, DimAccount in parallel
3. DimCurrency
E. 1. DimCurrency
2. DimOrganization
3. DimScenario, DimAccount in parallel
4. FactFinance
Answer: A
Explanation: References: http://msdn.microsoft.com/en-us/library/ms139892.aspx
http://msdn.microsoft.com/en-us/library/ms141261.aspx
Q7. You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table.
You have the following requirements:
. The fact table must populate a column named TotalCost that computes the sum of the price and tax columns. . Before the sum is calculated, any records that have a price of zero must be discarded.
You need to create the SSIS package in SQL Server Data Tools.
In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.)
Answer:
Q8. You are using the Master Data Services (MDS) Add-in for Excel to configure the entities in a model- The model consists of two entities: one named Customer and one named State. You opened the Customer entity.
Currently, data stewards can enter any text value in the Customer entity's State attribute. You must restrict the entry of values in the State attribute to members defined in the State entity.
You need to configure the State attribute of the Customer entity.
Which command should you use? (To answer, select the appropriate command in the answer area.)
Answer:
Q9. You are developing a SQL Server Integration Services (SSIS) package.
The package must run a parameterized query against a Windows Azure SQL Database
database.
You need to use the least amount of development effort to meet the package requirement.
Which task should you use? (To answer, select the appropriate task in the answer area.)
Answer:
Q10. You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart.
You have the following requirements:
. Ensure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values.
. Ensure that users can generate the exception report in an XML format. . Use the minimum development effort.
You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use a Data Profiling task. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server table. Query the table to view the exceptions.
B. Use Data Flow tasks to process the clean data.
C. Use a Data Profiling task. Read the exceptions in Data Profile Viewer.
D. Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task.
Answer: C