It is more faster and easier to pass the Microsoft 70-470 exam by using Printable Microsoft Recertification for MCSE: Business Intelligence questuins and answers. Immediate access to the Update 70-470 Exam and find the same core area 70-470 questions with professionally verified answers, then PASS your exam with a high score now.
♥♥ 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
Q11. - (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
Q12. - (Topic 3)
You need to configure the Scenario attribute to ensure that business users appropriately query the Sales Plan measure.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Set the AttributeHierarchyVisible property to False.
B. Set the IsAggregatable property to False.
C. Set the Usage property to Parent.
D. set the DefaultMember property to the Forecast member.
E. Set the AttributeHierarchyEnabled property to False.
F. Set the RootMemberIf property to ParentIsMissing.
Answer: C,D
Explanation:
The Sales measure group is based on the FactSales table. The Sales Plan measure group is based on the FactSalesPlan table. The Sales Plan measure group has been configured with a multidimensional OLAP (MOLAP) writeback partition. Both measure groups use MOLAP partitions, and aggregation designs are assigned to all partitions.
Topic 4, Contoso, Ltd Case A
General Background You are the SQL Server Administrator for Contoso, Ltd. You have been tasked with upgrading all existing SQL Server instances to SQL Server 2012.
Technical Background The corporate environment includes an Active Directory Domain Services (AD DS) domain named contoso.com. The forest and domain levels are set to Windows Server 2008. All default containers are used for computer and user accounts. All servers run Windows Server 2008 R2 Service Pack 1 (SP1). All client computers run Windows 7 Professional
SP1. All servers and client computers are members of the contoso.com domain.
The current SQL Server environment consists of a single instance failover cluster of SQL
Server 2008 R2 Analysis Services (SSAS). The virtual server name of the cluster is
SSASCluster. The cluster includes two nodes: Node1 and Node2. Node1 is currently the
active node. In anticipation of the upgrade, the prerequisites and shared components have
been upgraded on both nodes of the cluster, and each node was rebooted during a weekly
maintenance window.
A single-server deployment of SQL Server 2008 R2 Reporting Services (SSRS) in native
mode is installed on a server named SSRS01. The Reporting Server service is configured
to use a domain service account. SSRS01 hosts reports that access the SSAS databases
for sales data as well as modeling data for the Research team. SSRS01 contains 94
reports used by the organization. These reports are generated continually during business
hours. Users report that report subscriptions on SSRS01 are not being delivered. You run
the reports on demand from Report Manager and find that the reports render as expected.
A new server named SSRS02 has been joined to the domain, SSRS02 will host a single-
server deployment of SSRS so that snapshots of critical reports are accessible during the
upgrade.
The server configuration is shown in the exhibit. (Click the Exhibit button.)
The production system includes three SSAS databases that are described in the following table.
All SSAS databases are backed up once a day, and backups are stored offsite.
Business Requirements After the upgrade users must be able to perform the following tasks: . Ad-hoc analysis of data in the SSAS databases by using the Microsoft Excel PivotTable client. . Daily operational analysis by executing a custom application that uses ADOMD.NET and existing Multidimensional Expressions (MDX) queries.
...
The detailed data must be stored in the model.
Technical Requirements You need to minimize downtime during the SSASCluster upgrade. The upgrade must minimize user intervention and administrative effort.
The upgrade to SQL Server 2012 must maximize the use of all existing servers, require the least amount of administrative effort, and ensure that the SSAS databases are operational as soon as possible.
You must implement the highest level of domain security for client computers connecting to SSRS01. The SSRS instance on SSRS01 must use Kerberos delegation to connect to the SSAS databases. Email notification for SSRS01 has not been previously configured. Email notification must be configured to use the SMTP server SMTP01 with a From address of reports@contoso.com. Report distribution must be secured by using SSL and must be limited to the contoso.com domain.
You have the following requirements for SSRS02:
Replicate the SSRS01 configuration.
Ensure that all current reports are available on SSRS02.
Minimize the performance impact on SSR501.
In preparation for the upgrade, the SSRS-related components have been installed on the new SSRS02 server by using the Reporting Services file-only installation mode. The Reporting Services databases have been restored from SSRS01 and configured appropriately.
You must design a strategy to recover the SSRS instance on SSRS01 in the event of a system failure. The strategy must ensure that SSRS can be recovered in the minimal amount of time and that reports are available as soon as possible. Only functional components must be recovered.
SSRS02 is the recovery server and is running the same version of SSRS as SSRS01. A full backup of the SSRS databases on SSRS01 is performed nightly. The report server configuration files, custom assemblies, and extensions on SSRS02 are manually synchronized with SSRS01. Prior to implementing the upgrade to SQL Server 2012, you must back up all existing SSAS databases.
Databases on SSRS01 is performed nightly. The report server configuration files, custom assemblies, and extensions on SSRS02 are manually synchronized with SSRS01.
Prior to implementing the upgrade to SQL Server 2012, you must back up all existing SSAS databases. The backup must include only the partitioning, metadata, and aggregations to minimize the processing time required when restoring the databases. You must minimize processing time and the amount of disk space used by the backups.
Before upgrading SSAS on the SSASCluster, all existing databases must be moved to a temporary staging server named SSAS01 that hosts a default instance of SQL Server 2012 Analysis Services. This server will be used for testing client applications connecting to SSAS 2012, and as a disaster recovery platform during the upgrade. You must move the databases by using the least amount of administrative effort and minimize downtime. All SSAS databases other than the Research database must be converted to tabular BI Semantic Models (BISMs) as part of the upgrade to SSAS 2012. The Research team must have access to the Research database for modeling throughout the upgrade. To facilitate this, you detach the Research database and attach it to SSAS01.
While testing the Research database on SSAS01, you increase the compatibility level to 1100. You then discover a compatibility issue with the application. You must roll back the compatibility level of the database to 1050 and retest.
After completing the upgrade, you must do the following:
1.
Design a role and assign an MDX expression to the Allowed member set property of the Customer dimension to allow sales representatives to browse only members of the Customer dimension that are located in their sales regions. Use the sales representatives' logins and minimize impact on performance.
2.
Deploy a data model to allow the ad-hoc analysis of data. The data model must be cached and source data from an OData feed.
Q13. - (Topic 5)
You need to select the appropriate storage settings for the cube.
Which settings should you choose?
A. Relational OLAP (ROLAP) with proactive caching enabled
B. Multidimensional OLAP (MOLAP) with proactive caching enabled and a rebuild interval of 24 hours
C. Hybrid OLAP (HOLAP) with proactive caching disabled
D. Hybrid OLAP (HOLAP) with proactive caching enabled
Answer: C
Q14. DRAG DROP - (Topic 9)
You are validating whether a SQL Server Integration Services (SSIS) package named Master.dtsx in the SSIS catalog is executing correctly.
You need to display the number of rows in each buffer passed between each data flow component of the package.
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:
Q15. DRAG DROP - (Topic 9)
You administer a SQL Server Reporting Services (SSRS) infrastructure. Data alerts are provisioned.
You need to monitor the number of report data feed processing events during every automated execution.
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:
Q16. DRAG DROP - (Topic 9)
You have a business intelligence (BI) infrastructure that contains three servers. The servers are configured as shown in the following table.
You need to recommend a health monitoring solution for the BI infrastructure.
The solution must meet the following requirements:
Monitor the status of the Usage Data Collection feature.
Monitor the number of end-users accessing the solution.
Monitor the amount of cache used when the users query data.
Which health monitoring solution should you recommend using on each server? To answer, drag the appropriate monitoring solutions to the correct servers. Each monitoring solution may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
...
Answer:
Q17. DRAG DROP - (Topic 9)
You are designing a SQL Server Reporting Services (SSRS) solution.
An existing report aggregates data from a SQL Server 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:
Q18. HOTSPOT - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) cube.
Revenue must be compared to a goal and described by a status and a trend. Revenue,
goal, status, and trend will be defined by Multidimensional Expressions (MDX) expressions.
You need to add the Revenue indicator.
Which tab should you select? (To answer, select the appropriate tab in the work area.)
Answer:
Q19. DRAG DROP - (Topic 9)
You are the administrator of a SQL Server Integration Services (SSIS) catalog. You have access to the original password that was used to create the SSIS catalog.
A full database backup of the SSISDB database on the production server is made each day. The server used for disaster recovery has an operational SSIS catalog.
Information that is encrypted in the SSISDB database must not be lost.
You need to document the disaster recovery solution that restores the production SSIS catalog to the disaster recovery server.
Which three steps should you document 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. - (Topic 10)
You are modifying a SQL Server Analysis Services (SSAS) cube.
Users of the cube report that the precision for the TransactionCost measure is five digits.
You need to ensure that the TransactionCost measure stores values to two digits of precision.
What should you do?
A. Add a named calculation in the data source view that casts the data source column to two digits of precision. Bind the TransactionCost measure to the new column.
B. Add a named query in the data source view that casts the data source column to two digits of precision. Bind the TransactionCost measure to the new query.
C. Use the FormatString measure property to format TransactionCost as #,##0.00;-#,##0.00.
D. Use the FormatString measure property to format TransactionCost as Currency.
E. Use the MeasureExpression measure property to change the precision of TransactionCost to two digits.
Answer: A
241. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project for a Power View solution.
You need to grant permission for salespersons to view only the data based on their sales territory.
What should you do?
A. Use SQL Server Management Studio to create a role. Then create a Data Analysis Expressions (DAX) filter.
B. Create a member and then create a Data Analysis Expressions (DAX) filter.
C. Create a member and then create a Multidimensional Expressions (MDX) filter.
D. Use SQL Server Management Studio to create a role. Then create a Multidimensional Expressions (MDX) filter.
Answer: A
