Microsoft 70-470 Actual Free Exam Questions & Community Discussion

DRAG DROP
You have a business intelligence (BI) infrastructure.
You create a SQL Server Integration Services (SSIS) project that contains the packages for the BI infrastructure.
You need to automate the deployment of the SSIS project. The solution must ensure that the deployment can occur as part of an unattended Transact-SQL script.
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.

HOTSPOT
You are developing a SQL Server Analysis Services (SSAS) cube. A dimension named Category is based on the DimCategory table.
A subset of the data source view is shown in the following graphic.

You need to relate the Category dimension to the Sales measure group.
Which relationship type should you choose?
To answer, select the appropriate option from the drop-down list in the dialog box.

DRAG DROP
You have a single SQL Server 2008 R2 Analysis Services (SSAS) instance. You are planning to upgrade the instance to SQL Server 2012.
You need to import an existing PowerPivot workbook to create a tabular project.
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 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.)

You are developing a SQL Server Analysis Services (SSAS) cube for the sales department at your company.
The sales department requires the following set of metrics:
Unique count of customers
Unique count of products sold
Sum of sales
You need to ensure that the cube meets the requirements while optimizing query response time.
What should you do? (Each answer presents a complete solution. Choose all that apply.)

You are restructuring an existing cube. One of the measures in the cube is Amount. The Sum aggregation function is used for the Amount measure. The cube includes a dimension named Account and the dimension's Type property is set to Accounts. The Account dimension includes an account type attribute.
You need to ensure that the Amount measure aggregates correctly according to the account type classification. Development effort must be minimized.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

DRAG DROP
You are developing a SQL Server Analysis Services (SSAS) multidimensional project that is configured to source data from a SQL Azure database. The cube is processed each night at midnight.
The largest partition in the cube takes 12 hours to process, and users are unable to access the cube until noon. The partition must be available for querying as soon as possible after processing commences.
You need to ensure that the partition is available for querying as soon as possible, without using source data to satisfy the query.
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 need to re-establish subscriptions on SSRS01.
What should you do?

You need to implement security in the cube to limit the sites visible to each user.
What should you do?

You need to develop a BISM that meets the business requirements for ad-hoc and daily operational analysis. You must minimize development effort.
Which development approach and mode should you use?

You are developing a SQL Server Analysis Services (SSAS) tabular project. A model defines a measure named Revenue and includes a table named Date. The table includes year, semester, quarter, month, and date columns. The Date column is of data type Date. The table contains a set of contiguous dates.
You need to create a measure to report on year-over-year growth of revenue.
What should you do? (Each answer presents a complete solution. Choose all that apply.)

You are creating a product dimension table and a SQL Server Integration Services (SSIS)
package that will load the table.
You need to keep a history in the table of changes to the names of products.
Which three actions should you perform? Each correct answer presents part of the solution.

You need to select the appropriate mode for the Sales database. Which mode should you select?

You are developing the database schema for a SQL Server Analysis Services (SSAS) BI Semantic Model (BISM). The BISM will be based on the schema displayed in the following graphic.

You have the following requirements:
- Ensure that queries of the data model correctly display average student age by
class.
- Ensure that the solution supports role-based security and partitions.
- Minimize development effort.
You need to design the data model.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)