[2021-New] Microsoft 70-470 Dumps With Update Exam Questions (171-179)

70-470 Dumps

70-470 Exam Questions - Online Test

70-470 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. HOTSPOT - (Topic 9)

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.

Answer:

Q2. - (Topic 8)

You need to deploy a solution for the planned self-service reports that will be used by the sales department managers.

What is the best solution you should deploy? More than one answer choice may achieve the goal. Select the BEST answer.

A. A filter

B. A KPI

C. A calculated column

D. A measure

Answer: B

Q3. - (Topic 6)

You need to select the appropriate mode for the Sales database.

Which mode should you select?

A. ROLAP

B. Direct Query

C. MOLAP

D. In-Memory

Answer: B

Q4. DRAG DROP - (Topic 10)

You are making changes to a cube named Sales.

You must increase the value of the measure named Sales Amount by 10%. The increase must be applied only to the children of the member named East in the dimension named Customer.

You need to complete the Multidimensional Expressions (MDX) statement in the calculations section of the Sales cube.

Which statement fragments should you use? (To answer, drag the appropriate statement component to the correct location or locations in the answer area. Use only components that apply.)

Answer:

264. - (Topic 10)

You are developing a SQL Server Analysis Services (SSAS) tabular project that will be used by the finance, sales, and marketing teams.

The sales team reports that the model is too complex and difficult to use. The sales team does not need any information other than sales related resources in the tabular model. The finance and marketing teams need to see all the resources in the tabular model.

You need to implement a solution that meets the needs of the sales team while minimizing development and administrative effort.

What should you do?

A. Create a separate tabular model for each team.

B. Hide the non-sales columns from the client tools.

C. Create a security role for the sales team.

D. Create a perspective for the sales team.

Q5. DRAG DROP - (Topic 3)

You need to complete the following UPDATE statement to initialize the budget sales values for 2012.

Which MDX weight value expression should you use?

To answer, drag the appropriate weight value expression to the answer area.

Answer:

Q6. - (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

Q7. - (Topic 8)

You need to prepare the infrastructure for the planned implementation of Gallery1.

Which three actions should you perform? Each correct answer presents part of the solution,

A. Install a Database Engine instance.

B. Run the PowerPivot Configuration Tool.

C. Install the SQL Server Reporting Services add-in for SharePoint.

D. Install SQL Server PowerPivot for SharePoint.

E. Install the SQL Server Reporting Services - SharePoint feature.

F. Run the Install-SPUserSolution cmdlet.

Answer: B,C,D

Q8. - (Topic 5)

You need to configure a parameter for the database connection string. What should you do?

A. Use a required package parameter.

B. Use a required project parameter.

C. Use a package configuration.

D. Use a global variable.

Answer: B

Topic 6, WingTip Toys

General Background

You are a data architect for WingTip Toys. The company uses SQL Server 2012 Enterprise

edition. SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS)

are installed on separate servers.

Data Warehouse

The company's data warehouse initially contained less than 100 MB and 100 million rows

of data from only one data source. It now contains more than 10 TB and 10 billion rows of

data, in 25 tables, from 12 data sources.

The largest table in the data warehouse, the factOrders table, contains 5 TB of data. The

factOrders table contains three date keys: OrderDateKey InvoiceDateKey, and

ShipDateKey. The data warehouse server has 1 TB of RAM. Memory usage is currently at

20 percent.

One billion rows of data are added to the data warehouse each month. New data is copied

each night from the data sources into SQL Server staging tables, and existing records are

not updated. The largest data set is order information, which is loaded in parallel into

multiple staging tables, one for each data source. All the staging tables have the same

structure and belong to the same filegroup as the factOrders table.

The dimCustomers table stores customer information that may change over time.

Data Models

You are developing three SSAS databases, as described in the following table.

Reporting

Business users frequently generate reports in Microsoft Excel by using PowerPivot. The

PowerPivot Management Dashboard does not currently display any usage data.

Several SSRS reports exist that use the data warehouse as a source. The data warehouse

queries are aggregate queries that use the factOrders table and one or more dimension

tables. All SSRS data sources use Integrated Windows authentication.

SSRS displays a security access error message when managers run SSRS reports based

on the Operations database.

Reporting performance has become unacceptably slow.

Business Requirements

Improve the query speed of the SSRS reports.

Allow business users to create reports by using PowerPivot and Power View.

Ensure that all users other than business users can view metadata for the Customers

dimension. Ensure that business users cannot view metadata for the Customers

dimension.

Technical Requirements

Modify the tables in the data warehouse to minimize aggregate query processing time.

Minimize disk storage in the data warehouse.

Ensure that all multidimensional models process data as quickly as possible.

Create a fact table named factCustomerContact in the data warehouse to store the contact

date, customer key, and communication type for each instance of customer contact.

Store the history of customer information changes in the dimCustomers table.

Move data from the staging tables into the factOrders table as quickly as possible. When

creating dimensions for the date keys in the factOrders table, minimize storage space

requirements and optimize the cube processing time.

Ensure that queries against the Sales database return the most current data in the data

warehouse.

Ensure that the SSAS model of the Finance database does not page to disk or return a

memory error as the size of the database grows.

Create an SSAS monitoring solution that tracks the following data: Queries answered per second Queries from cache direct per second Queries from file per second.

...

Q9. - (Topic 9)

You are redesigning a SQL Server Analysis Services (SSAS) database that contains a cube named Sales. Before the initial deployment of the cube, partition design was optimized for processing time. The cube currently includes five partitions named FactSalesl through FactSales5. Each partition contains from 1 million to 2 million rows.

The FactSales5 partition contains the current year's information. The other partitions contain information from prior years; one year per partition. Currently, no aggregations are defined on the partitions.

You remove fact rows that are more than five years old from the fact table in the data source and configure query logs on the SSAS server.

Several queries and reports are running very slowly.

You need to optimize the partition structure and design aggregations to improve query performance and minimize administrative overhead.

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

A. Use the Usage-Based Optimization Wizard to create aggregations for the current partitions.

B. Use the Aggregation Design Wizard to create aggregations for the current partitions.

C. Combine all the partitions into a single partition. Use the Usage-Based Optimization Wizard to create aggregations.

D. Combine all the partitions into a single partition. Use the Aggregation Design Wizard to create aggregations.

Answer: A