We provide real 70-470 exam questions and answers braindumps in two formats. Download PDF & Practice Tests. Pass Microsoft 70-470 Exam quickly & easily. The 70-470 PDF type is available for reading and printing. You can print more and practice many times. With the help of our Microsoft 70-470 dumps pdf and vce product and material, you can easily pass the 70-470 exam.
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Microsoft 70-470 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Free Instant Download NEW 70-470 Exam Dumps (PDF & VCE):
Available on:
http://www.surepassexam.com/70-470-exam-dumps.html
Q131. DRAG DROP - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) cube.
You need to reuse a measure group from a different database.
In SQL Server Data Tools (SSDT), 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:
Q132. - (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
Q133. - (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
Q134. - (Topic 10)
.. ..
You are developing a SQL Server Analysis Services (SSAS) tabular project. The model has tables named Invoice Line Items and Products. The Invoice Line Items table has the following columns:
Product Id Unit Sales Price The Unit Sales Price column stores the unit price of the product sold. The Products table has the following columns: Product Id Maximum Sales Price
The Maximum Sales Price column is available only in the Products table.
You add a column named Is Overpriced to the Invoice Line Items table. The Is Overpriced column must store a value of TRUE if the value of the Unit Sales Price is greater than the value of the Maximum Sales Price. Otherwise, a value of FALSE must be stored.
You need to define the Data Analysis Expressions (DAX) expression for the Is Overpriced column.
Which DAX formula should you use? (Each answer represents a complete solution. Choose all that apply.)
A. =IF([Unit Sales Price] > RELATED(Products[Maximum Sales Price]), TRUE, FALSE)
B. =IF(RELATED(Products[Unit Sales Price]) > [Maximum Sales Price], TRUE, FALSE)
C. =IF([Unit Sales Price] > LOOKUPVALUE(Products[Maximum Sales Price], Products[Product Id], [Product Id]), TRUE, FALSE)
D. =IF(LOOKUPVALUE(Products[Unit Sales Price], Products[Product Id], [Product Id]) > [Maximum Sales Price]), TRUE, FALSE)
Answer: A,C
Explanation: A: RELATED Function Returns a related value from another table.
* The RELATED function requires that a relationship exists between the current table and the table with related information. You specify the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table.
C:
The lookupvalue function returns the value in result_columnName for the row that meets all
criteria specified by search_columnName and search_value.
Syntax:
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[,
<search_columnName>, <search_value>]…)
Note:
The syntax of DAX formulas is very similar to that of Excel formulas, and uses a
combination of functions, operators, and values.
Q135. DRAG DROP - (Topic 7)
You are creating the Australian states query.
Which Multidimensional Expressions (MDX) calculation should you use to complete the
query?
To answer, drag the appropriate calculation to the answer area.
Answer:
85. DRAG DROP - (Topic 7)
You need to configure the LoadFactFreightCosts package to address the data integrity issues.
Which data flow component should you use?
To answer, drag the appropriate data flow component to the answer area.
Answer:
Topic 8, Tailspin Toys Case B
Overview
Tailspin Toys is a manufacturing company that has offices across the United States,
Europe, and Asia.
Tailspin Toys plans to implement a business intelligence (BI) solution for its US-based
headquarters to manage the sales data, including information on customer transactions,
products, sales quotas, and bonuses.
Existing Environment
Data Sources
Tailspin Toys currently stores data in line-of-business applications, relational databases,
flat files, and the following;
. A Microsoft Excel spreadsheet named MarketResearch.xlsx. The spreadsheet is stored on a network drive in a directory owned by an analyst. . A tabular model named Research.xlsx used in PowerPivot for Excel. Research.xlsx uses MarketResearch.xlsx as one of its data sources.
Network The network contains an Active Directory forest named tailspintoys.com. The forest contains a Microsoft SharePoint Server 2013 server farm.
Implementation Plans
Databases Tailspin Toys plans to build a star schema data warehouse named DB1. DB1 will be loaded from several different sources and will be updated nightly to contain new sales data.
DB1 will contain the following table types: . A fact table to store transactional data, including transaction date, productID, customerID, quantity, and sales amounts. . Dimension tables to store information about each customer, each product, each date, and each sales department user.
BI Semantic Models
Tailspin Toys plans to deploy the following BI semantic models:
. A multidimensional cube named CUBE1 that will store sales data. CUBE1 will be based on DB1 and will be hosted in SQL Server Analysis Services (SSAS). CUBE1 will contain two distinct count measures named UniqueCustomers and UniqueProducts. The measures are expected to aggregate hundreds of millions of rows from DB1.
. A tabular model named SalesCommission that will contain information about sales department user quotas and commissions. . A tabular model named Research that will contain the migrated model from Research.xlsx. . An instance of SSAS in tabular mode named Tabular.
Planned Reports and Queries
Tailspin Toys plans to implement the following reports and queries: . Power View reports that use data from the Research model. . Reports for each year the company recorded sales data that used the
SalesCommission model. The reports will use the Dates_Between() and the
DatesInPeriod() DAX functions in queries. . Reports that use CUBE1 that contain the following query statements:
. A report named SalesByCategory that uses CUBE1 and the following query statement: (Line numbers are included for reference only.)
Self-Service Reporting
Tailspin Toys plans to deploy the following self-service reports: . Reports created by sales department specialists that use CUBE1 and contain drillthroughs, maps, sparklines, and Key Performance Indicators (KPIs). The reports will be stored in a SharePoint Server document library named Library1. . Reports created by sales department managers that use the SalesCommission model. The reports will contain visualizations that show sales department users their current sales as compared to their quota. . Power Pivot models stored in a SharePoint Server document library that is configured as a PowerPivot Gallery named Gallery1.
Requirements
Data Security Requirements Sales department users browsing CUBE1 must be able to view the sales data that relates to their respective customers only.
Access to reports must be controlled by using SharePoint permissions.
ETL Requirements Tailspin Toys identifies the following extract, transformation, and load (ETL) requirements: . Nightly updates of DB1 must support the incremental load of dimension and fact tables on separate schedules. Fact data may be loaded before dimension data. . ETL processes must be able to update dimension attributes without losing context for historical facts.
. Referential integrity between dimension and fact tables must be maintained at all times.
Cube Performance Requirements The design of CUBE1 must minimize the processing time of the UniqueCustomers and Unique Products measures. The time required to process CUBE1 each night must be minimized.
Data Refresh Requirements The Research model must be refreshed nightly without interrupting the workflow of the analyst.
Q136. DRAG DROP - (Topic 10)
You manage a SQL Server Reporting Services (SSRS) instance in native mode. You are building a shared dataset for your weekly performance reports. The shared dataset uses a data source that is configured to use credentials that are stored in the Report Server.
You have a predefined shared schedule to perform cleanup and maintenance tasks for SSRS.
You need to enable caching on the shared dataset. You also need to use an existing shared schedule to discard the cache.
Which four 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:
Q137. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project. The model has tables named Invoice Line Items and Products.
The Invoice Line Items table has the following columns:
..
Product Id Unit Sales Price
The Unit Sales Price column stores the unit price of the product sold. The Products table
has the following columns:
. Product Id
. Minimum Sales Price
The Minimum Sales Price column is available only in the Products table.
You add a column named Is Undersell to the Invoice Line Items table. The Is Undersell column must store a value of TRUE if the value of the Unit Sales Price is less than the value of the Minimum Sales Price. Otherwise, a value of FALSE must be stored.
You need to define the Data Analysis Expressions (DAX) expression for the Is Undersell column.
Which DAX formula should you use? (Each answer represents a complete solution. Choose all that apply.)
A. =IF([Unit Sales Price] < RELATED(Products[Minimum Sales Price]), TRUE, FALSE)
B. =IF(RELATED(Products[Unit Sales Price]) < [Minimum Sales Price], TRUE, FALSE)
C. =IF([Unit Sales Price] < LOOKUPVALUE(Products[Minimum Sales Price], Products[Product Id], [Product Id]), TRUE, FALSE)
D. =IF(LOOKUPVALUE(Products[Unit Sales Price], Products[Product Id], [Product Id]) < [Minimum Sales Price]), TRUE, FALSE)
Answer: A,C
Explanation: A: RELATED Function Returns a related value from another table.
* The RELATED function requires that a relationship exists between the current table and
the table with related information. You specify the column that contains the data that you
want, and the function follows an existing many-to-one relationship to fetch the value from
the specified column in the related table.
C:
The lookupvalue function returns the value in result_columnName for the row that meets all
criteria specified by search_columnName and search_value.
Syntax:
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)
Note:
The syntax of DAX formulas is very similar to that of Excel formulas, and uses a
combination of functions, operators, and values.
Q138. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
In the data warehouse, a table named Employee Security defines a relationship between a salesperson's name, logon ID, and assigned sales territory.
You need to ensure that each salesperson has access to data from only the sales territory assigned to that salesperson. You also need to minimize the development effort.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create a separate tabular project for each sales territory. Grant each salesperson access to the corresponding tabular model of the assigned sales territory.
B. Create a new Active Directory Domain Services (AD DS) security group and add each salesperson as a member. Then create a new role with Read permission. Add the group as a member to the new role.
C. Create a new role with Read permission and then add each salesperson's logon as a member to the role.
D. Add the Employee Security table to the model, define the relationships, and then implement dynamic security by using row filters. Grant each salesperson access to the model.
Answer: D
Q139. DRAG DROP - (Topic 10)
You are developing a SQL Server Analysis Services (SAS) multidimensional project that is configured to source data from a SQL Azure database.
You plan to use multiple servers to process different partitions simultaneously. You create and configure a new data source.
You need to create a new partition and configure SQL Server Analysis Services (SSAS) to use a remote server to process data contained within the partition.
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:
Q140. DRAG DROP - (Topic 9)
You are configuring an Excel Services service application in a Microsoft SharePoint farm.
Users will deploy Excel workbooks to SharePoint libraries that allow interaction with PivotTables through Excel Services. PivotTable data is sourced from secured SQL Server Analysis Services (SSAS) cubes and PowerPivot models inside published workbooks.
You need to ensure that users can refresh the PivotTables from within Excel Services without a warning message appearing.
What should you do?
To answer, drag the appropriate term or terms to the correct location or locations in the answer area. (Use only terms that apply.)
Answer:
