Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 7)
You need to identify the reasons that data alert notifications are not being sent.
Which of the following reasons are possible? (Each correct answer presents a complete solution. Choose al that apply.)
A. The shared schedule is paused.
B. The data source used by the report is disabled.
C. The SSRS service is not running.
D. The report data has not changed since the previous notification
E. The SQL Server Agent is not running.
F. The SSRS encryption key has been deleted.
Answer: C,E
Explanation:
One possibility is that no SQL Server Agent alerts have been configured. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.
Q2. HOTSPOT - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) cube.
Revenue must be compared to a goal and described by a status and a trend. Revenue,
goal, status, and trend will be defined by Multidimensional Expressions (MDX) expressions.
You need to add the Revenue indicator.
Which tab should you select? (To answer, select the appropriate tab in the work area.)
Answer:
Q3. - (Topic 3)
You need to grant appropriate permissions to the SSISOwners SQL Server login. What should you do?
A. Map the login to the SSISDB database. Assign the user to the ssis_admin role.
B. Map the login to the msdb database. Assign the user to the db_owner role.
C. Map the login to the msdb database. Assign the user to the db_ssisadmin role.
D. Map the login to the SSISDB database. Assign the user to the db_ssisadmin role.
E. Map the login to the SSISDB database. Assign the user to the db_owner role.
F. Map the login to the msdb database. Assign the user to the ssis_admin role.
Answer: D
Q4. - (Topic 10)
You are designing a SQL Server Reporting Services (SSRS) report based on a SQL Server Analysis Services (SSAS) cube.
The cube contains a Key Performance Indicator (KPI) to show if a salesperson's sales are off target, slightly off target, or on target.
You need to add a report item that visually displays the KPI status value as a red, yellow, or green circle.
Which report item should you add?
A. Data Bar
B. Indicator
C. Radial Gauge
D. Linear Gauge
E. Sparkline
Answer: B
Q5. - (Topic 9)
A hospital has a relational data warehouse, a SQL Server Analysis Services (SSAS) database, and a SQL Server Reporting Services (SSRS) instance. The SSAS database contains a cube named Pharmacy. Shared data sources exist in SSRS for the relational
...
and SSAS databases. Each hospital department has its own report writers.
Report writers in the Human Resources (HR) department want to create new reports by using Report Builder. Many reports will include data generated by a custom formula that references data stored either in a data warehouse table or in the Pharmacy cube. The custom formula will compare time periods across multiple products, categories, and employees.
You have the following requirements:
Ensure that only the HR department report writers can access the custom formula.
Implement only one dataset.
Ensure that the dataset references the data source that will provide the fastest
data retrieval.
You need to meet the requirements to support the HR department report writers.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create, deploy, and secure a shared dataset that references the data warehouse shared data source and includes custom Transact-SQL (T-SQL) code for the custom formula.
B. Create a calculated member in the Pharmacy cube. Create, deploy, and secure a shared dataset that references the SSAS database shared data source and includes the calculated member from the cube.
C. Create and secure in the data warehouse a stored procedure that implements the custom formula. Create and deploy a shared dataset that references the data warehouse shared data source.
D. Create, deploy, and secure a shared dataset that references the SSAS database shared data source and includes a calculated member for the custom formula.
Answer: B
Q6. - (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
Answer: A
Q7. - (Topic 5)
You need to implement the aggregation designs for the cube.
What should you do?
A. Use the CREATE CACHE statement.
B. Use the Aggregation Design Wizard.
C. Create relational indexes on the source tables.
D. Use the Usage-Based Optimization Wizard.
Answer: B
Q8. - (Topic 9)
A company has a relational data warehouse, a SQL Server Analysis Services (SSAS) database, and a SQL Server Reporting Services (SSRS) instance. The SSAS database contains a cube named Sales. Shared data sources exist in SSRS for the relational and SSAS databases. Each company department has its own report writers.
Report writers in the Marketing department want to create new reports by using Report Builder. Many reports will include data generated by a custom formula that references data stored either in a data warehouse table or in the Sales cube. The custom formula will compare time periods across multiple products, categories, and regions.
You have the following requirements:
. Ensure that only Marketing department report writers can access the custom formula.
..
Implement only one dataset.
Ensure that the dataset references the data source that will provide the fastest
data retrieval.
You need to meet the requirements to support the Marketing department report writers.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create, deploy, and secure a shared dataset that references the SSAS database shared data source and includes a calculated member for the custom formula.
B. Create a calculated member in the Sales cube. Create, deploy, and secure a shared dataset that references the SSAS database shared data source and includes the calculated member from the cube.
C. Create and secure in the data warehouse a stored procedure that implements the custom formula. Create and deploy a shared dataset that references the data warehouse shared data source.
D. Create, deploy, and secure a shared dataset that references the data warehouse shared data source and includes custom Transact-SQL (T-SQL) code for the custom formula.
Answer: B
Q9. HOTSPOT - (Topic 9)
You are using a new installation of SQL Server Reporting Services (SSRS) to create three sales reports that consume data from a stored procedure. The stored procedure is defined in a Windows Azure SQL Database.
All reports must pass USA to the Country parameter of the stored procedure. Users cannot change the Country report parameter value.
You need to configure the report parameter properties.
How should you configure the report parameter properties? To answer, select the appropriate setting or settings in the answer area.
Answer:
Q10. - (Topic 10)
You are managing a SQL Server Reporting Services (SSRS) instance in native mode. A system role named Developer Support is present on the server.
Members of the Developer Support role cannot modify the report execution timeout period. You need to enable members of the Developer Support role to modify the report execution timeout period.
Which task should you add to the Developer Support role?
A. Execute report definitions
B. Manage shared schedules
C. Manage jobs
D. Manage report server properties
Answer: D
Q11. - (Topic 7)
You need to ensure that the Sales measure in the Power View field list meets the
requirements.
What should you do? (Each correct answer presents a part of the solution. Choose all that apply.)
A. Format the column to display zero decimal places.
B. Hide the column from client tools.
C. Create a measure named Sales based on the column by using the Data Analysis Expressions (DAX) SUM() function.
D. Rename the column to Sales.
E. Format the measure to display zero decimal places.
Answer: A,C
Explanation: * Data Analysis Expressions (DAX) provides many functions for creating aggregations such as sums, counts, and averages. These functions are very similar to aggregation functions used by Microsoft Excel.
* SUMX Function
Returns the sum of an expression evaluated for each row in a table.
Q12. - (Topic 10)
You are troubleshooting query performance for a SQL Server Analysis Services (SSAS) cube.
A user reports that a Multidimensional Expressions (MDX) query is very slow.
You need to identify the MDX query statement in a trace by using SQL Server Profiler.
Which event class should you use?
A. Progress Report Begin
B. Query Begin
C. Execute MDX Script Begin
D. Calculate Non Empty Begin
E. Get Data From Aggregation
F. Query Subcube
Answer: B
Q13. - (Topic 9)
You are designing a subscription strategy for a SQL Server Reporting Services (SSRS) report.
You have an application that populates a table with user-specific subscription schedules and report formats.
You need to ensure that users can receive reports by email according to their preferences. Email messages will be sent via an internal mail server.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create a standard SSRS subscription for each record in the table.
B. Create a data-driven SSRS subscription for each record in the schedule table.
C. Create a standard SSRS subscription for each subscription schedule.
D. Create one data-driven SSRS subscription. Schedule the subscription to frequently retrieve user preferences.
Answer: D
Explanation:
Ref: http://technet.microsoft.com/en-us/library/ms187066(v=sql.105).aspx
Q14. - (Topic 10)
You are modifying a SQL Server Analysis Services (SSAS) cube.
Users of the cube report that the precision for the SalesAmount measure is four digits.
You need to ensure that the SalesAmount measure stores values to two digits of precision.
What should you do?
A. Add a named query in the data source view that casts the data source column to two digits of precision. Bind the SalesAmount measure to the new query.
B. Use the MeasureExpression measure property to change the precision of SalesAmount to two digits.
C. Add a named calculation in the data source view that casts the data source column to two digits of precision. Bind the SalesAmount measure to the new column.
D. Use the FormatString measure property to format SalesAmount as Currency.
E. Use the FormatString measure property to format SalesAmount as #,##0.00;-#,##0.00.
Answer: C
Q15. - (Topic 10)
You are creating a SQL Server Analysis Services (SSAS) multidimensional database.
Users need a time dimension for:
Dates
Delivery dates
Ship dates
...
You need to implement the minimum number of required SSAS objects.
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.
Answer: A
Q16. - (Topic 8)
You need to recommend a cube architecture for CUBE1. The solution must meet the performance requirements for CUBE1.
Which two partitions should you recommend creating? Each correct answer presents part of the solution.
A. Partitions based on the values of the customerID column in the dimension table
B. Partitions based on the values of the customerID column in the fact table
C. Partitions based on the values of the productID column in the fact table
D. Partitions based on the values of the productID column in the dimension table
Answer: A,D
Topic 9, Mix Questions Set A