70-470 Exam
Renovate Microsoft 70-470 - An Overview 141 to 150
Exact of 70-470 real exam materials and vce for Microsoft certification for IT candidates, Real Success Guaranteed with Updated 70-470 pdf dumps vce Materials. 100% PASS Recertification for MCSE: Business Intelligence exam Today!
2021 Dec 70-470 exam
Q141. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
You need to grant the minimum permissions necessary to enable users to query data in a
data model.
Which role permission should you use?
A. Explorer
B. Process
C. Administrator
D. select
E. Browser
F. Read
Answer: F
Q142. - (Topic 9)
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 and average class level by student.
. 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.)
A. Create a multidimensional project and define measures and a reference relationship.
B. Create a tabular project and define calculated columns.
C. Create a multidimensional project and define measures and a many-to-many dimensional relationship.
D. Create a tabular project and define measures.
Answer: C
Q143. - (Topic 9)
You are designing a subscription strategy for a SQL Server Reporting Services (SSRS) report.
You have an application that populates a table with user-specific subscription schedules and report formats.
You need to ensure that users can receive reports by email according to their preferences.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create a standard subscription for each record in the table.
B. Create a data-driven subscription for each record in the schedule table.
C. Create one data-driven subscription. Schedule the subscription to frequently retrieve user preferences.
D. Create a standard subscription for each subscription schedule.
Answer: C
Q144. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
A column named City must be added to the table named Customer. The column will be used in the definition of a hierarchy. The City column exists in the Geography table that is related to the Customer table.
You need to add the City column to the Customer table.
How should you write the calculation?
A. City:=RELATED(Geography[City])
B. =RELATEDTABLE(Geography)
C. =RELATED(Geography[City])
D. City:=Geography[City]
E. City:=RELATEDTABLE(Geography)
F. =Geography[City]
Answer: C
Q145. - (Topic 9)
...
A SQL Server Analysis Services (SSAS) cube contains a large measure group. The fact table supporting the measure group is loaded with new data throughout the day.
You have the following requirements:
Ensure that the cube displays current data as quickly as possible.
Maximize availability of the cube.
Maximize query performance for all aggregation levels.
You need to choose a partitioning strategy that meets the requirements.
Which partitioning strategy should you choose? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create one partition for the current day that uses multidimensional OLAP (MOLAP) with proactive caching as a storage mode.
B. Create one partition for the current month that uses hybrid OLAP (HOLAP) as a storage mode.
C. Create one partition for the current day that uses relational OLAP (ROLAP) as a storage mode.
D. Create one partition for the current day that uses multidimensional OLAP (MOLAP) as a storage mode. Process the partition each night.
Answer: A
Latest 70-470 exam guide:
Q146. - (Topic 3)
You need to select an appropriate tool for creating the Regional Sales report.
Which tool or tools should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. Excel 2010, using the CUBE functions
B. Power View, using a Matrix
C. Excel 2010, using a PivotTable
D. Report Builder, using a Matrix
Answer: B,C,D
Explanation:
B: Working with a matrix in Power View A matrix is a type of visualization that is similar to a table in that it is made up of rows and columns. However, a matrix can be collapsed and expanded by rows and/or columns. If it contains a hierarchy, you can drill down/drill up.
C: Using an Excel Pivot Table, connecting to the Cube, will give you the option to drill down the cube. Using conditional formatting you can highlight specific value ranges.
D: Matrices provide functionality similar to crosstabs and pivot tables. At run time, as the report data and data regions are combined, a matrix grows horizontally and vertically on the page. Values in matrix cells display aggregate values scoped to the intersection of the row and column groups to which the cell belongs. You can format the rows and columns to highlight the data you want to emphasize. You can also include drilldown toggles that initially hide detail data; the user can then click the toggles to display more or less detail as needed.
* From scenario: Management has requested a new report named Regional Sales. This report must be based on the Sales cube and must allow users to filter by a specific year and present a grid with every region on the columns and the Products hierarchy on the rows. The hierarchy must initially be collapsed and allow the user to drill down through the hierarchy to analyze sales. Additionally, sales values that are less than $5000 must be highlighted in red.
Q147. - (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.
Q148. - (Topic 5)
You need to implement security in the cube to limit the sites visible to each user.
What should you do?
A. Create an SSAS database role in the cube for each user and assign the sites each user can access to his or her database role.
B. Create an SSAS server role for each user and assign the sites each user can access to his or her server role.
C. Create an SSAS database role and define a Multidimensional Expressions (MDX) calculation to implement dynamic dimension security.
D. Create a view on the SalesTransactions table that uses the SecurityFilter and User table data to limit the sites for each user.
Answer: C
Q149. DRAG DROP - (Topic 9)
You are developing packages in a SQL Server Integration Services (SSIS) project to load a data warehouse.
You are designing a method for configuring the packages' connections when deployed to a production SSIS catalog. All packages load data from two source databases.
Two packages also load data from a third source database. Another SSIS project will use the same connections.
You need to meet the requirements.
What should you do?
To answer, drag the appropriate term or terms to the correct location or locations in the
answer area. (Each term may be used once, more than once, or not at all.)
Answer:
Q150. 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: