Q1. DRAG DROP - (Topic 9)
You are validating whether a SQL Server Integration Services (SSIS) package named Master.dtsx in the SSIS catalog is executing correctly.
You need to display the number of rows in each buffer passed between each data flow component of the package.
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.)
Answer:
Q2. - (Topic 10)
You are administrating a SQL Server Analysis Services (SSAS) tabular database.
You need to create a new role that allows its members to query data and to refresh data in the model.
Which permission should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Read and Process
B. Explore and Manage
C. Browse And Manage
D. Administrator
Answer: A
Q3. - (Topic 2)
You need to modify the Sales Regions hierarchy to meet the reporting requirements.
Which S5A5 feature should you use?
A. Calculation
B. Translation
C. Action
D. Perspective
Answer: B
Q4. 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.)
Answer:
Q5. DRAG DROP - (Topic 9)
A company has a data warehouse that is rapidly increasing in size.
You plan to improve query performance by partitioning a very large fact table.
You need to identify the columns that are good partitioning candidates and the range that
each partition should include.
Which component should you add to the package?
To answer, drag the appropriate component from the list of items to the answer area.
Answer:
Q6. - (Topic 3)
You need to select an appropriate tool for creating the Regional Sales report.
Which tool or tools should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. Excel 2010, using the CUBE functions
B. Power View, using a Matrix
C. Excel 2010, using a PivotTable
D. Report Builder, using a Matrix
Answer: B,C,D
Explanation:
B: Working with a matrix in Power View A matrix is a type of visualization that is similar to a table in that it is made up of rows and columns. However, a matrix can be collapsed and expanded by rows and/or columns. If it contains a hierarchy, you can drill down/drill up.
C: Using an Excel Pivot Table, connecting to the Cube, will give you the option to drill down the cube. Using conditional formatting you can highlight specific value ranges.
D: Matrices provide functionality similar to crosstabs and pivot tables. At run time, as the report data and data regions are combined, a matrix grows horizontally and vertically on the page. Values in matrix cells display aggregate values scoped to the intersection of the row and column groups to which the cell belongs. You can format the rows and columns to highlight the data you want to emphasize. You can also include drilldown toggles that initially hide detail data; the user can then click the toggles to display more or less detail as needed.
* From scenario: Management has requested a new report named Regional Sales. This report must be based on the Sales cube and must allow users to filter by a specific year and present a grid with every region on the columns and the Products hierarchy on the rows. The hierarchy must initially be collapsed and allow the user to drill down through the hierarchy to analyze sales. Additionally, sales values that are less than $5000 must be highlighted in red.
Q7. DRAG DROP - (Topic 7)
You need to implement the requirements for the StageFactSales package.
Which four 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.)
Answer:
Q8. - (Topic 10)
You install SQL Server Reporting Services (SSRS). You need to restore a copy of the symmetric key. Which command should you run?
A. rskeymgmt –d
B. rskeymgmt –a –f %temp%\rs.key –p Password1
C. rskeymgmt –e –f %temp%\rs. key –p Password1
D. rskeymgmt –i
Answer: B
Q9. - (Topic 5)
You need to create the Package Activity report.
What should you do?
A. Create a log table and use SSIS event handlers to write to the log table. Then create an SSRS report that uses the log table.
B. Use the SSIS log provider for SQL Server. Then create an SSRS report that uses the sysssislog table.
C. Create a log table and build a custom log provider to write to the log table. Then create an SSRS report that uses the log table.
D. Create an SSRS report that uses thecatalog.executions and catalog.execution_data_statistics views.
Answer: D
Q10. - (Topic 10)
You are developing a BI Semantic Model (BISM) that will be used to analyze complex budgeting and forecast data sourced from a financial database. The model will be deployed to a server with 32 GB of RAM.
The source data, located in a SQL Server data warehouse, is currently using 10 terabytes of disk space and is doubling in size every three months. The model will be queried by staff in the accounting department by using Microsoft Excel 2010.
You need to ensure the highest query performance and scalability for the accounting department queries.
Which project type should you choose?
A. PowerPivot workbook
B. tabular project that uses the In-Memory query mode
C. multidimensional project
D. tabular project that uses the DirectQuery query mode
Answer: C