A Review Of Virtual 70-470 tutorials

Q1. - (Topic 10)

You are developing a Microsoft SQL Analysis Services (SSAS) multidimensional project.

A fact table named FactHouseSales has a measure column named Area. All values in the column are stored in square feet. Users must be able to analyze the area in different units. You create a table named AreaUnit. Each row in the table consists of the unit name and a

square feet conversion factor value.

You need to implement the area conversion in the project.

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: N

Q2. - (Topic 7)

You need to develop the tabular project to support the date analysis requirements.

What should you do?

Create one date table named Date.

Create an active relationship between the DateKey column of the Date table and the OrderDateKey column of the Sales table.

Create an inactive relationship between the DateKey column of the Date table and the ShipDateKey column of the Sales table.

Create two date tables, one named Order Date and one named Ship Date.

Create an active relationship between the DateKey column of the Order Date table and the OrderDateKey column of the Sales table.

Create an inactive relationship between the DateKey column of the Ship Date table and the ShipDateKey column of the Sales table.

Create one date table named Date.

Create an active relationship between the DateKey column of the Date table and the ShipDateKey column of the Sales table.

Create an inactive relationship between the DateKey column of the Date table and the OrderDateKey column of the Sales table.

Create two date tables, one named Order Date and one named Ship Date.

Create an active relationship between the DateKey column of the Order Date table and the OrderDateKey column of the Sales table.

Create an active relationship between the DateKey column of the Ship Date table and the ShipDateKey column of the Sales table.

Answer: C

Q3. - (Topic 2)

You need to configure per-user security authentication for reporting against the Sales cube.

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

A. Install PowerPivot.

B. Create Service Principal Names (SPNs).

C. Configure account delegation.

D. Set up the Unattended Service Account.

Answer: A,B

Explanation: * From scenario:

/ Administrative staff from around the world will produce sales reports with Microsoft Excel

2010 based on the Sales cube.

/ Security requirements

When possible, the system must use Windows authentication for all database connections.

The system must prevent users from querying data from outside of their region.

The system must allow certain users to query data from multiple regions.

B: To use Kerberos authentication with SQL Server requires both the following conditions to be true: The client and server computers must be part of the same Windows domain, or in trusted domains. A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

Q4. DRAG DROP - (Topic 10)

You are developing a SQL Server Analysis Services (SSAS) multidimensional project that is configured to source data from a SQL Azure database.

The largest partition in the cube takes an unacceptably long time to process. 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.)

Answer:

Q5. - (Topic 10)

You are modifying a SQL Server Analysis Services (SSAS) cube.

Users of the cube report that the precision for the TransactionCost measure is five digits.

You need to ensure that the TransactionCost measure stores values to two digits of precision.

What should you do?

A. Add a named calculation in the data source view that casts the data source column to two digits of precision. Bind the TransactionCost measure to the new column.

B. Add a named query in the data source view that casts the data source column to two digits of precision. Bind the TransactionCost measure to the new query.

C. Use the FormatString measure property to format TransactionCost as #,##0.00;-#,##0.00.

D. Use the FormatString measure property to format TransactionCost as Currency.

E. Use the MeasureExpression measure property to change the precision of TransactionCost to two digits.

Answer: A

241. - (Topic 10)

You are developing a SQL Server Analysis Services (SSAS) tabular project for a Power View solution.

You need to grant permission for salespersons to view only the data based on their sales territory.

What should you do?

A. Use SQL Server Management Studio to create a role. Then create a Data Analysis Expressions (DAX) filter.

B. Create a member and then create a Data Analysis Expressions (DAX) filter.

C. Create a member and then create a Multidimensional Expressions (MDX) filter.

D. Use SQL Server Management Studio to create a role. Then create a Multidimensional Expressions (MDX) filter.

Q6. - (Topic 9)

You are designing a strategy for an enterprise reporting solution that uses SQL Server Reporting Services (SSRS).

Many of the SSRS reports will use common utilities and functions, including the following:

Report utility functions and business logic in code Standardized report formatting properties such as fonts and colors for report branding

Formatting may change and new functions may be added as the reporting solution evolves.

You need to create a strategy for deploying the formatting and code across the entire enterprise reporting solution. You must also ensure that reports can be easily updated to reflect formatting and function changes.

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

A. Create a report as a template. Apply standardized formatting to the template. Store code in the Code section of the template.

B. Build a web service that retrieves formatting properties and runs code. Call the web service through a report dataset.

C. Store the formatting properties and code in database objects. Use stored procedures to populate a default value for report parameters and map each parameter to a corresponding formatting property.

D. Create an assembly that contains formatting properties and code. Deploy the assembly on the Reporting Server and reference the assembly from each report.

Answer: D

Q7. - (Topic 10)

You are creating a SQL Server Analysis Services (SSAS) cube.

You need to create a time dimension. It must be linked to a measure group named Sales at the day granularity level. It must also be linked to a measure group named Salary at the month granularity level.