Q1. - (Topic 9) 

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. 

View Answer

Q2. - (Topic 10) 

You are developing a Microsoft SQL Analysis Services (SSAS) multidimensional project. 

A fact table named FactHouseSales has a measure column named Area. All values in the column are stored in square feet. Users must be able to analyze the area in different units. You create a table named AreaUnit. Each row in the table consists of the unit name and a 

square feet conversion factor value. 

You need to implement the area conversion in the project. 

What should you do? 

A. Use role playing dimensions. 

B. Use the Business Intelligence Wizard to define dimension intelligence. 

C. Add a measure that uses the Count aggregate function to an existing measure group. 

D. Add a measure that uses the DistinctCount aggregate function to an existing measure group. 

E. Add a measure that uses the LastNonEmpty aggregate function. Use a regular relationship between the time dimension and the measure group. 

F. Add a measure group that has one measure that uses the DistinctCount aggregate function. 

G. Add a calculated measure based on an expression that counts members filtered by the Exists and NonEmpty functions. 

H. Add a hidden measure that uses the Sum aggregate function. Add a calculated measure aggregating the measure along the time dimension. 

I. Create several dimensions. Add each dimension to the cube. 

J. Create a dimension. Then add a cube dimension and link it several times to the measure group. 

K. Create a dimension. Create regular relationships between the cube dimension and the measure group. Configure the relationships to use different dimension attributes. 

L. Create a dimension with one attribute hierarchy. Set the XsAggregatable property to False and then set the DefaultMember property. Use a regular relationship between the dimension and measure group. 

M. Create a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a many-to-many relationship to link the dimension to the measure group. 

N. Create a dimension with one attribute hierarchy. Set the ValueColumn property, set the IsAggregatable property to False, and then set the DefaultMember property. Configure the cube dimension so that it does not have a relationship with the measure group. Add a calculated measure that uses the MemberValue attribute property. 

O. Create a new named calculation in the data source view to calculate a rolling sum. Add a measure that uses the Max aggregate function based on the named calculation. 

View Answer

Q3. - (Topic 10) 

You are managing a SQL Server Reporting Services (SSRS) instance. 

A website must pass credentials to the local security authority for Reporting Services. 

You need to configure Reporting Services to issue a challenge/response when a 

connection is made without credentials. 

Which authentication type should you configure in the RSReportServer.config file? 

A. RSWindowsKerberos and RSWindowsNegotiate 

B. RSWindowsKerberos only 

C. RSWindowsKerberos and RSWindowsNTLM 

D. RSWindowsBasic 

View Answer

Q4. - (Topic 10) 

You are designing a SQL Server Analysis Services (SSAS) cube based on a Windows Azure SQL Database data warehouse. 

You need to implement a degenerate dimension. 

What should you do? 

A. Use the fact table as the data source for the dimension. 

B. Create a junk dimension table based on the fact table in the data source. 

C. Create snowflake dimension tables based on normalized views of the fact table in the data source. 

D. Add a surrogate key to the fact table and use it as the degenerate dimension key. 

View Answer

Q5. - (Topic 3) 

You need to define the trend calculation for the sales performance KPI. 

Which KPI trend MDX expression should you use? 

A. CASE WHEN [Sales Variance %] < ([Sales Variance %], [Date].[Calendar].PrevMember) THEN -1 WHEN [Sales Variance %] = ([Sales Variance %], [Date].[Calendar].PrevMember) THEN 0 ELSE 1 END 

B. IIF([Sales Variance %3 < ([Sales Variance %], [Date].[Calendar].PrevMember), 1, 0) 

C. IIF([Sales Variance %] < ([Sales Variance %], [Date].[Calendar].PrevMember), 0, 1) 

D. CASE WHEN [Sales Variance %] < ([Sales Variance %], [Date].[Calendar].PrevMember) THEN 1 WHEN [Sales Variance %] = ([Sales Variance %], [Date].[Calendar].PrevMember) THEN 0 ELSE -1 END 

View Answer

Q6. - (Topic 10) 

You are developing a SQL Server Reporting Services (SSRS) report that renders in HTML. The report includes a dataset with fields named Description, Price, and Color. The report layout includes a table that displays product details and also includes columns named Description, Price, and Color. 

You need to modify the report so that users can sort products by the Price column. 

What should you do? 

A. Add a custom action to the Price text box. 

B. Set the SortExpression property to =Fields!Price.Value for the Price text box. 

C. In the Expression dialog box for the Price text box, enter the =SortBy FieldslPrice.Value expression. 

D. Set the SortExpression value to =Fields!Price.Description for the Price text box. 

View Answer

Q7. DRAG DROP - (Topic 10) 

You are developing a SQL Server Analysis Services (SSAS) cube. 

You need to reuse a measure group from a different database. 

In SQL Server Data Tools (SSDT), which three actions 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

Q8. DRAG DROP - (Topic 7) 

You need to develop the LoadFactSales package to write the error messages to the SSIS catalog log. 

Which components should you use? 

To answer, drag the appropriate components to the correct location or locations in the answer area. (Use only components that apply.) 

View Answer

Q9. - (Topic 10) 

You are developing a SQL Server Analysis Services (SSAS) cube for the accounts department. 

You create a measure group named Exchange Rate that consists of measures pertaining to currency exchange rates. One of the measures in this group is named Average Rate and it will be used to report the average currency exchange rate over time. 

Currently the AggregationFunction property for the Average Rate measure is set to Sum. 

You need to ensure that Average Rate measure reports the average of the currency exchange rate over time. 

Which value should you select for the AggregationFunction property for the Average Rate measure? To answer, select the appropriate setting in the answer area. 

A. AverageOfChildren 

B. ByAccount 

C. Count 

D. DistinctCount 

E. FirstChild 

F. FirstNonEmpty 

G. LastChild 

H. LastNonEmpty 

I. Max 

J. Min 

K. None 

L. Sum 

View Answer

Q10. - (Topic 9) 

You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables. 

You have the following requirements: 

. Ensure that the deployment model supports changing the content of connection strings by using parameters at execution time. . Ensure that the deployment model automatically starts from calls to the 

catalog.start_execution stored procedure in the SSISDB database. 

. Maximize performance at execution time. 

. Minimize development effort. 

You need to design a solution that meets the requirements. 

What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. Use a package deployment model. Use a SQL Server package configuration with a common filter. Change the contents of the SSIS Configurations table at runtime. 

B. Use a project deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package. 

C. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments. 

D. Use a project deployment model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created. 

View Answer