70-470 Exam
Dec 2021 updated: Examcollection Microsoft 70-470 question 11-20
Cause all that matters here is passing the Microsoft 70-470 exam. Cause all that you need is a high score of 70-470 Recertification for MCSE: Business Intelligence exam. The only one thing you need to do is downloading Testking 70-470 exam study guides now. We will not let you down with our money-back guarantee.
2021 Dec 70-470 free exam questions
Q11. DRAG DROP - (Topic 9)
You are designing a SQL Server Reporting Services (SSRS) solution.
An existing report aggregates data from a SQL Azure database in a chart.
You need to use the chart in a new report and ensure that other users can use the chart in
their reports.
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:
Q12. - (Topic 9)
You have a data warehouse named DW1.
An OLAP cube named Cube1 uses DW1 as its data source. Cube1 uses the MOLAP
storage mode.
You create a SQL Server Integration Services (SSIS) package that updates DW1, and then processes Cube1. The package executes each day at 06:00.
You need to configure the storage settings of Cube1. The solution must ensure that aggregation processing is reduced as much as possible during cube querying.
What is the best storage setting you should you use for Cube1? More than one answer choice may achieve the goal. Select the BEST answer.
A. Medium-latency MOLAP
B. Scheduled MOLAP
C. Low-latency MOLAP
D. Automatic MOLAP
Answer: D
Q13. - (Topic 3)
You need to grant appropriate permissions to the SSISOwners SQL Server login. What should you do?
A. Map the login to the SSISDB database. Assign the user to the ssis_admin role.
B. Map the login to the msdb database. Assign the user to the db_owner role.
C. Map the login to the msdb database. Assign the user to the db_ssisadmin role.
D. Map the login to the SSISDB database. Assign the user to the db_ssisadmin role.
E. Map the login to the SSISDB database. Assign the user to the db_owner role.
F. Map the login to the msdb database. Assign the user to the ssis_admin role.
Answer: D
Q14. HOTSPOT - (Topic 9)
You have an OLAP cube named Cube1. Cube1 has two partitions named partition1 and partition2.
You need to configure the storage mode for each cube partition.
The solution must meet the following requirements:
. Users must be able to retrieve aggregate data from partition1 as quickly as
possible.
. The processing time for partition1 and partition2 must be minimized.
. The least possible amount of storage must be used for partition2.
What should you do? To answer, select the appropriate storage mode for each partition in the answer area.
Answer:
Q15. - (Topic 8)
You need to recommend a cube architecture for CUBE1. The solution must meet the performance requirements for CUBE1.
Which two partitions should you recommend creating? Each correct answer presents part of the solution.
A. Partitions based on the values of the customerID column in the dimension table
B. Partitions based on the values of the customerID column in the fact table
C. Partitions based on the values of the productID column in the fact table
D. Partitions based on the values of the productID column in the dimension table
Answer: A,D
Topic 9, Mix Questions Set A
Abreast of the times 70-470 practice exam:
Q16. 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.
Q17. - (Topic 5)
You need to implement the product dimension. What should you do?
A. In the data warehouse, create a product dimension from a view that joins the Product and ProductColor tables in the financial database and contains product name and product color attributes.
B. In the data warehouse, create a dimension table that contains product name and a dimension table that contains product color.
C. In the data warehouse, create a product dimension table that contains product name and product color.
D. In the cube, create a named query that joins the Product and ProductColor tables in the financial database.
Answer: C
Q18. - (Topic 10)
You are modifying a SQL Server Analysis Services (SSAS) multidimensional database.
You have identified a dimension that is no longer used by any cubes.
You need to delete the dimension.
What should you do?
A. Write a Multidimensional Expressions (MDX) command to drop the dimension from the database.
B. Write a Data Mining Extensions (DMX) command to drop the dimension from the database.
C. Write a T-SQL command to drop the dimension from the database.
D. Delete the dimension by using SQL Server Management Studio Object Explorer.
Answer: D
Q19. DRAG DROP - (Topic 9)
You are administering a SQL Server Analysis Services (SSAS) database on a server. The database hosts a financial cube based on a SQL Azure database.
You need to grant read access to the financial cube for all users in the group USA\PowerUsers.
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:
Q20. DRAG DROP - (Topic 10)
You are planning the installation of PowerPivot for SharePoint that will be used by your company's sales and marketing team.
You install SharePoint Server 2010 Enterprise Edition with Service Pack 1.
You need to install the PowerPivot for SharePoint instance. Then you need to configure the Default Account username used to provision shared services in the SharePoint farm.
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: