Q1. - (Topic 5) 

You need to select the appropriate storage settings for the cube. 

Which settings should you choose? 

A. Relational OLAP (ROLAP) with proactive caching enabled 

B. Multidimensional OLAP (MOLAP) with proactive caching enabled and a rebuild interval of 24 hours 

C. Hybrid OLAP (HOLAP) with proactive caching disabled 

D. Hybrid OLAP (HOLAP) with proactive caching enabled 

View Answer

Q2. - (Topic 3) 

You need to create the calculation for SalespersonName. 

What should you do? (Each correct answer presents a complete solution. Choose all that apply.) 

A. Create a computed column in the data warehouse's DimSalesperson table. Include the column in the Sales Reporting model's Salesperson table. 

B. Modify the data warehouse's DimSalesperson table and add a new column. Use an UPDATE statement to populate the new column with values. Update the SSIS package developed to populate the data warehouse's DimSalesperson table to use a Derived Column transformation to produce the calculation. 

C. Configure the Sales Reporting model's Salesperson table properties to be based on a query. Define a derived column in the query. 

D. Add a calculated column to the Sales Reporting model's Salesperson table by using the Data Analysis Expressions (DAX) language CONCATENATE function. 

E. Create a view in the data warehouse that defines a derived column based on the DimSalesperson table. Base the Sales Reporting model's Salesperson table on the view. Include the column in the Sales Reporting model's Salesperson table. 

F. Add a calculated column to the Sales Reporting model's Salesperson table by using the Data Analysis Expressions (DAX) language ADDCOLUMNS function. 

View Answer

Q3. - (Topic 7) 

You need to update the Execute SQL task in the OnVariableValueChanged event handler of all SSIS packages. 

Which additional variable should be logged? 

A. System::ExecutionInstanceGUID 

B. System::ServerExecutionID 

C. System::VariableID 

D. System::SourceID 

View Answer

Q4. - (Topic 3) 

You need to create the Sales Reporting shared SSRS data source. Which SSRS data connection type should you use? 

A. OData 

B. Microsoft SQL Server 

C. ODBC 

D. OLE DB 

View Answer

Q5. - (Topic 4) 

You need to re-establish subscriptions on SSRS01. What should you do? 

A. Manually failover the active node. 

B. Install prerequisites and upgrade shared components on Node1 and Node2. 

C. Generate a SQL Server 2012 configuration file by running the SQL Server Setup executable. 

D. Upgrade Node1 by using the SQL Server 2012 Upgrade wizard. 

View Answer

Q6. - (Topic 10) 

... 

A large manufacturing company has manufacturing plants in many states. Each state has a dedicated SQL Server instance that stores its manufacturing data. Each SQL Server instance is configured identically and all instances have identical database structures. 

You are developing a daily report that summarizes information on manufacturing activity. 

The report has the following requirements: 

It must have a state name as one of the report parameters. 

It must provide a daily summary of manufacturing activity of a selected state. 

It must require minimal development and maintenance effort. 

You need to develop the report to meet the requirements. 

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

A. Use a shared data source that defines an expression-based connection string based on the state parameter. 

B. Build a centralized data repository, schedule a regular Extract, Transform, and Load (ETL) process on all manufacturing data, and then use the repository to generate the report. 

C. Use a report-specific data source that defines an expression-based connection string based on the state parameter. 

D. Build one report for each state and instruct users to execute reports as needed. 

View Answer

Q7. - (Topic 5) 

You need to restrict access to data in the tables in the data warehouse. 

What should you do? 

A. Configure column-level permissions. 

B. Configure database roles. 

C. Create views and grant permissions to the views. 

D. Configure application roles. 

View Answer

Q8. - (Topic 9) 

You deploy a PowerPivot workbook to a PowerPivot Gallery in a Microsoft SharePoint site. Workbook data comes from two different sources: Source A and Source B. 

The workbook contains five tables with a total of 20 million rows from Source A, and three small lookup tables from Source B. Data from Source A is updated throughout the day. Data from Source B is updated at 3:00 P.M. 

You have the following requirements: 

. Refresh the PowerPivot workbook with updated lookup data as soon as possible. . Minimize load on the source systems and the SharePoint environment during business hours. . Minimize user involvement in the data refresh process. 

You enable automatic data refresh for the workbook. 

You need to configure a data refresh schedule for the workbook that meets the requirements. 

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

A. Set the default schedule to refresh outside of business hours. Do not manually refresh the workbook. 

B. Set the default schedule to refresh outside of business hours. Manually refresh the workbook at 9:00 A.M. every day. 

C. Set the default schedule to refresh at 9:00 A.M. every day. Create a separate schedule for Source A's connection to refresh outside of business hours. 

D. Set the default schedule to refresh at 3:00 P.M. every day. Do not create individual source schedules. 

E. Set the default schedule to refresh at 9:00 A.M. every day. Do not create individual source schedules. 

View Answer

Q9. HOTSPOT - (Topic 10) 

A sales cube contains two years of data. 

The sales team must see year-over-year (YOY) and month-over-month (MOM) sales 

metrics. 

You need to modify the cube to support the sales team's requirements. 

Which Business Intelligence Wizard enhancements should you use? (To answer, configure 

the appropriate option or options in the dialog box in the answer area.) 

View Answer

Q10. - (Topic 9) 

. ... 

The Sales database includes a table named Products that contains 42 columns, and two tables that record all product sales transactions. The database does not store aggregated data. The database is continually maintained to provide optimal indexing. The database server is sized appropriately. 

The Marketing department requests a delimited text file that includes seven fields containing product information from the Products table. 

You have the following requirements: 

Display the ProductID in the first column. Organize the remaining columns 

alphabetically by column heading. 

Sort rows in ProductID order. 

Aggregate product units sold. 

Minimize the time required to return the results. 

You create a SQL Server Integration Services (SSIS) package and add the appropriate connection managers and a Data Flow task. 

You need to develop the Data Flow task. 

What should you add to the Data Flow task? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. A table-based OLE DB source; a Script component; and a Flat File destination. 

B. Only a SQL command-based OLE DB source and a Flat File destination. 

C. A table-based OLE DB source; Lookup, Aggregate, and Sort transformations; and a Flat File destination. 

D. A SQL command-based OLE DB source; Aggregate and Sort transformations; and a Flat File destination. 

View Answer