70-470 Exam
What Improved 70-470 Is?
Downloadable of 70-470 exam guide materials and training tools for Microsoft certification for IT professionals, Real Success Guaranteed with Updated 70-470 pdf dumps vce Materials. 100% PASS Recertification for MCSE: Business Intelligence exam Today!
2021 Dec 70-470 practice question
Q1. - (Topic 10)
...
You are designing a SQL Server Reporting Services (SSRS) report to display vineyard names and their year-to-date (YTD) grape yield. Grape yield values are classified in three bands:
High Yield
Medium Yield
Low Yield
You add a table to the report. Then you define two columns based on the fields named VineyardName and YTDGrapeYield.
You need to set the color of the vineyard text to red, yellow, or blue, depending on the value of the YTD grape yield values.
What should you do?
A. Add an indicator to the table.
B. Use an expression for the Style property of the vineyard text box.
C. Use an expression for the Font property of the vineyard text box.
D. Use an expression for the TextDecoration property of the vineyard text box.
E. Use an expression for the Color property of the vineyard text box.
Answer: E
Q2. - (Topic 10)
You are deploying an update to a SQL Server Analysis Services (SSAS) cube to a production environment.
The production database has been configured with security roles.
You need to preserve the existing security roles in the production database. Database roles and their user accounts from the development environment must not be deployed to the production server.
Which deployment method should you use?
A. Use the SQL Server Analysis Services Deployment Wizard.
B. Backup and restore the database.
C. Deploy the project from SQL Server Data Tools to the production server.
D. Use the SQL Server Analysis Services Migration Wizard.
Answer: A
Q3. - (Topic 9)
You are designing an extract, transform, load (ETL) process for loading data from a SQL
....
Azure database into a large fact table in a data warehouse each day with the prior day's sales data.
The ETL process for the fact table must meet the following requirements:
Load new data in the shortest possible time.
Remove data that is more than 36 months old.
Minimize record locking.
Minimize impact on the transaction log.
You need to design an ETL process that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Partition the fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table.
B. Partition the fact table by customer. Use partition switching both to remove old data and to load new data into each partition.
C. Partition the fact table by date. Use partition switching and staging tables both to remove old data and to load new data.
D. Partition the fact table by date. Use partition switching and a staging table to remove old data. Insert new data directly into the fact table.
Answer: C
Q4. - (Topic 6)
You need to implement a strategy for efficiently storing sales order data in the data warehouse.
What should you do?
A. Separate the factOrders table into multiple tables, one for each month that has orders, and use a local partitioned view.
B. Separate the factOrders table into multiple tables, one for each day that has orders, and use a local partitioned view.
C. Create daily partitions in the factOrders table.
D. Create monthly partitions in the factOrders table.
Answer: C
Q5. - (Topic 8)
You need to recommend a partitioning strategy that meets the performance requirements for CUBE1.
What should you include in the recommendation?
A. Create separate measure groups for each distinct count measure.
B. Createone measure group for all distinct count measures.
C. Createa separate dimension for each distinct count attribute.
D. Createone dimension for all distinct count attributes.
Answer: A
91. - (Topic 8)
You need to recommend a SQL Server Integration Services (SSIS) package design that meets the ETL requirements.
What should you include in the recommendation?
A. Add new rows for changes to existing dimension members and enable inferred members.
B. Update non-key attributes in the dimension tables to use new values.
C. Update key attributes in the dimension tables to use new values.
D. Add new rows for changes to existing dimension members and disable inferred members.
Answer: A
Far out 70-470 latest exam:
Q6. - (Topic 10)
...
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.)
A. Place the measures in a single measure group.
B. Place the distinct count measures in separate measure groups.
C. Use the additive measure group functions.
D. Use the semiadditive measure group functions.
E. Use the Count and Sum measure aggregation functions.
F. Use the Distinct Count and Sum measure aggregation functions.
Answer: B,D
Explanation: B: Typically, the best performance occurs when each distinct count measure is in its own measure group, and that measure group has the same dimensionality as the initial measure group.
D: Semiadditive Function
Select the aggregation function for the selected measure.
The aggregate functions available include DistinctCount, Aggregated using the
DistinctCount function.
Q7. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) cube.
The data warehouse has a table named FactStock that is used to track movements of stock. A column named MovementQuantity contains quantities of stock. A positive quantity is used for input and negative quantity is used for output. A column named MovementDate is related to the time dimension. The quantity in stock, at a given point in time, can be evaluated as the sum of all MovementQuantity values at that point in time.
You need to create a measure that calculates the quantity in stock value.
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 IsAggregatable 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: H
Q8. - (Topic 9)
You are modifying a SQL Server Reporting Services (SSRS) report for a SQL Server Analysis Services (SSAS) cube. The report defines a report parameter of data type Date/Time with which users can filter the report by a single date. The parameter value cannot be directly used to filter the Multidimensional Expressions (MDX) query for the dataset.
You need to ensure that the report displays data filtered by the user-entered value. You must achieve this goal by using the least amount of development effort.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Edit the dataset query parameter. Change the Value property of the report parameter to an expression that uses the same format as the date dimension member key value.
B. Edit the dataset query parameter. Change the Name property of the dataset query parameter so that it points to a name value for each date dimension member.
C. Edit the dataset query parameter. Create a subcube subquery that uses the StrToSet MDX function and accepts the report parameter value.
D. Change the dataset query to Transact-SQL (T-SQL). Use the OPENROWSET function to query the cube. Output the cube results to the T-SQL query and use a Convert function to change the report parameter value into the same format as the date dimension member.
Answer: A
Q9. - (Topic 1)
You need to deploy the StandardReports project.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
A. Deploy the project from SQL Server Data Tools (SSDT).
B. Use the Analysis Services Deployment utility to create an XMLA deployment script.
C. Use the Analysis Services Deployment wizard to create an MDX deployment script.
D. Use the Analysis Services Deployment wizard to create an XMLA deployment script.
Answer: A,D
Explanation: There are several methods you can use to deploy a tabular model project. Most of the deployment methods that can be used for other Analysis Services projects, such as multidimensional, can also be used to deploy tabular model projects.
A: Deploy command in SQL Server Data Tools
The Deploy command provides a simple and intuitive method to deploy a tabular model
project from the SQL Server Data Tools authoring environment.
Caution:
This method should not be used to deploy to production servers. Using this method can
overwrite certain properties in an existing model.
D: The Analysis Services Deployment Wizard uses the XML output files generated from a
Microsoft SQL Server Analysis Services project as input files. These input files are easily
modifiable to customize the deployment of an Analysis Services project. The generated
deployment script can then either be immediately run or saved for later deployment.
Incorrect: not B: The Microsoft.AnalysisServices.Deployment utility lets you start the Microsoft SQL Server Analysis Services deployment engine from the command prompt. As input file, the utility uses the XML output files generated by building an Analysis Services project in SQL Server Data Tools (SSDT).
Q10. - (Topic 10)
You are conducting a design review of a multidimensional project.
In the geography dimension, all non-key attributes relate directly to the key attribute. The
underlying data of the geography dimension supports relationships between attributes.
You need to increase query and dimension processing performance.
What should you do?
A. For the geography dimension, set the ProcessingMode property to LazyAggregations
B. For the dimension attributes of the geography dimension, define appropriate attribute relationships.
C. For the geography dimension, set the ProcessingPriority property to 1.
D. For the dimension attributes of the geography dimension, set the GroupingBehavior property to EncourageGrouping.
Answer: B