Microsoft Designing Business Intelligence Solutions with Microsoft SQL Server - 70-467 Exam Questions
QUESTION NO: 1
You need to recommend a partitioning strategy that meets the performance requirements for CUBE1.
What should you include in the recommendation?
You need to recommend a partitioning strategy that meets the performance requirements for CUBE1.
What should you include in the recommendation?
Correct Answer: C
QUESTION NO: 2
You are creating a Multidimensional Expressions (MDX) calculation for Projected Revenue in a cube.
For Customer A, Projected Revenue is defined as 150 percent of the Total Sales for the customer. For all other customers, Projected Revenue is defined as 110 percent of the Total Sales for the customer.
You need to calculate the Projected Revenue as efficiently as possible.
Which calculation should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)
You are creating a Multidimensional Expressions (MDX) calculation for Projected Revenue in a cube.
For Customer A, Projected Revenue is defined as 150 percent of the Total Sales for the customer. For all other customers, Projected Revenue is defined as 110 percent of the Total Sales for the customer.
You need to calculate the Projected Revenue as efficiently as possible.
Which calculation should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)
Correct Answer: B
QUESTION NO: 3
You are designing a SQL Server Reporting Services (SSRS) solution.
An existing report aggregates data from a SQL Azure database in a chart.
You need to use the chart in a new report and ensure that other users can use the chart in their reports.
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,)
You are designing a SQL Server Reporting Services (SSRS) solution.
An existing report aggregates data from a SQL Azure database in a chart.
You need to use the chart in a new report and ensure that other users can use the chart in their reports.
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,)
Correct Answer:
Explanation
Box 1:
Box 2:
Box 3:
Note:
* In Report Designer, after you create tables, charts, and other report items in a project, you can publish them asreport partsto a report server or SharePoint site integrated with a report server so that you and others can reuse them in other reports.
* . By using Report Builder, you can customize and update reports that were created in SQL Server Data Tools (SSDT) Report Designer.
* InReport Builder, IT pros and power users can create powerful operational reports, and reusable report parts and shared datasets.
Incorrect:
* (incorrect) Power View, a feature of SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Server 2010 Enterprise Edition, is an interactive data exploration, visualization, and presentation experience. It provides intuitive ad-hoc reporting for business users such as data analysts, business decision makers, and information workers. They can easily create and interact with views of data from data models based on PowerPivot workbooks published in a PowerPivot Gallery, or tabular models deployed to SQL Server 2012 Analysis Services (SSAS) instances. Power View is a browser-based Silverlight application launched from SharePoint Server 2010 that enables users to present and share insights with others in their organization through interactive presentations.
Reference: Getting Started with Report Builder
Reference: Report Parts in Report Designer (SSRS)
QUESTION NO: 4
You plan to deploy a SQL Server Integration Services (SSIS) project by using the project deployment model.
You need to monitor control flow tasks to determine whether any of them are running longer than usual.
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.)
You plan to deploy a SQL Server Integration Services (SSIS) project by using the project deployment model.
You need to monitor control flow tasks to determine whether any of them are running longer than usual.
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.)
Correct Answer:
Explanation
Box 1: Connect to the SSISDB database.
Box 2:
Box 3: Execute the query
Note:
* execution_component_phases
Displays the time spent by a data flow component in each execution phase.
* The following example uses the catalog.execution_component_phases view to find the total amount of time that a specific package has spent executing in all phases (active_time), and the total elapsed time for the package (total_time).
use SSISDB
select package_name, task_name, subcomponent_name, execution_path,
SUM(DATEDIFF(ms,start_time,end_time)) as active_time,
DATEDIFF(ms,min(start_time), max(end_time)) as total_time
from catalog.execution_component_phases
where execution_id = 1841
group by package_name, task_name, subcomponent_name, execution_path
order by package_name, task_name, subcomponent_name, execution_path
* catalog.executables
This view displays a row for each executable in the specified execution.
An executable is a task or container that you add to the control flow of a package.
*(incorrect) catalog.executions (SSISDB Database)
Displays the instances of package execution in the Integration Services catalog. Packages that are executed with the Execute Package task run in the same instance of execution as the parent package.
This view displays a row for each instance of execution in the catalog.
* (incorrect) catalog.operation_messages
Displays messages that are logged during operations in the Integration Services catalog.
This view displays a row for each message that is logged during an operation in the catalog. The message can be generated by the server, by the package execution process, or by the execution engine.
Reference: catalog.execution_component_phases
Reference: catalog.executables
QUESTION NO: 5
You are developing a SQL Server Reporting Services (SSRS) solution.
You plan to create reports based on the results of a currency exchange SOAP web service call.
You need to configure a shared data source.
Which data source type should you use?
To answer, select the appropriate type from the drop-down list in the answer area.
You are developing a SQL Server Reporting Services (SSRS) solution.
You plan to create reports based on the results of a currency exchange SOAP web service call.
You need to configure a shared data source.
Which data source type should you use?
To answer, select the appropriate type from the drop-down list in the answer area.
Correct Answer:
Explanation
QUESTION NO: 6
You need to fix the PowerPivot data refresh problem by using the least amount of administrative effort.
What should you do?
You need to fix the PowerPivot data refresh problem by using the least amount of administrative effort.
What should you do?
Correct Answer: C
QUESTION NO: 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.)
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.)
Correct Answer: B,E
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 8
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.)
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.)
Correct Answer: D
QUESTION NO: 9
You need to configure the attribute relationship types for the Salesperson dimension.
Which configuration should you use?
To answer, drag the appropriate pair of attributes and attribute relationships from the list to the correct location or locations in the answer area. (Answer choices may be used once, more than once, or not all.)
You need to configure the attribute relationship types for the Salesperson dimension.
Which configuration should you use?
To answer, drag the appropriate pair of attributes and attribute relationships from the list to the correct location or locations in the answer area. (Answer choices may be used once, more than once, or not all.)
Correct Answer:
Explanation
Note:
You connect a "higher-level" attribute to a "lower-level" attribute.
Best practice design says relationships should be rigid (bold filled lines) when members aren't shifting around.
Example:
