[2021-New] Microsoft 70-470 Dumps With Update Exam Questions (1-10)

70-470 Dumps

70-470 Exam Questions - Online Test

70-470 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. - (Topic 10)

You are designing a SQL Server Analysis Services (SSAS) cube.

You need to create a measure to count unique customers.

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 prooerty 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: F

Q2. - (Topic 4)

You need to use Reporting Services Configuration Manager to configure SSRS to complete the installation on SSRS02.

What should you do? (Each correct answer presents a partial solution. Choose all that apply.)

A. Change the encryption key.

B. Specify the execution account.

C. Join the scale-out deployment.

D. Set the Report Server Web Service URL.

E. Set the Report Manager URL.

F. Delete the encryption key.

Answer: A,B,E

Explanation: A: We need to restore a copy of the encryption key from SSRS01. This step is necessary for enabling reversible encryption on pre-existing connection strings and credentials that are already in the report server database.

B: Reporting Services provides a special account that is used for unattended report

processing and for sending connection requests across the network. The account is used

in the following ways:

/ Send connection requests over the network for reports that use database authentication,

or connect to external report data sources that do not require or use authentication.

/ Retrieve external image files that are used in report. If you want to use an image file and

the file cannot be accessed through Anonymous access, you can configure the unattended

report processing account and grant the account permission to access the file.

E: Example:

1.First of all open Internet Explorer and go to.Report Manager URL.which is something.like

below:

http://string-pc/Reports2012

Click on your SSRS project. So now it will show you the list of reports which are

deployed on your report server.

Now click on down arrow on the report which you want to subscribe and selectManage. Etc.

* From Scenario:

/ 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.

/ 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 SSRS01.

/ 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.

/ SSRS02 is the recovery server and is running the same version of SSRS as SSRS01. The report server configuration files, custom assemblies, and extensions on SSRS02 are manually synchronized with SSRS01.

...

Q3. - (Topic 9)

You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.

You have the following requirements:

. Ensure that the deployment model supports changing the content of connection strings by using parameters at execution time. . Ensure that the deployment model automatically starts from calls to the

catalog.start_execution stored procedure in the SSISDB database.

. Maximize performance at execution time.

. Minimize development effort.

You need to design a solution that meets the requirements.

What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Use a project deployment model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created.

B. Use a project deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.

C. Use a package deployment model. Use a SQL Server package configuration with a common filter. Change the contents of the SSIS Configurations table at runtime.

D. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments.

Answer: A

Q4. DRAG DROP - (Topic 10)

You are developing a SQL Server Reporting Services (SSRS) report to display a list of

employees. The report will be embedded into a Microsoft SharePoint Server Web Part Page of the company intranet site.

The report consists of a single table. The design of the table is shown in the following diagram.

You need to configure the EmailAddress detail text box to create a new email message. The email message must be addressed to the email address that was clicked by the user.

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:

312. - (Topic 10)

You are designing a SQL Server Reporting Services (SSRS) report to display product names and their year-to-date (YTD) sales quantity YTD sales quantity values are classified in three bands: High Sales, Medium Sales, and Low Sales.

You add a table to the report. Then you define two columns based on the fields named ProductName and YTDSalesQuantity.

You need to set the color of the product text to red, yellow, or blue, depending on the value of the YTD sales quantity values.

What should you do?

A. Use an expression for the TextDecoration property of the text box.

B. Use an expression for the Style property of the text box.

C. Add an indicator to the table.

D. Use an expression for the Font property of the text box.

E. Use an expression for the Color property of the text box.

Q5. - (Topic 10)

You develop a SQL Server Analysis Services (SSAS) tabular project. The tabular model loads data from a SQL Server 2012 relational database each day.

You define a connection.

You need to ensure that the connection minimizes the attack surface area of the server.

How should you define the impersonation information for the connection? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Use the credentials of the SQL Server Analysis Services (SSAS) service account. Grant least privilege to this account in the source database.

B. Use your domain credentials. Grant least privilege to your account in the source database.

C. Create and use a new Windows domain account. Grant least privilege to this account in the source database.

D. Use SQL Server authentication.

Answer: C

Q6. - (Topic 10)

You are administrating a SQL Server Analysis Services (SSAS) tabular database.

You need to create a new role that allows its members to query data and to refresh data in the model.

Which permission should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Read and Process

B. Explore and Manage

C. Browse And Manage

D. Administrator

Answer: A

Q7. - (Topic 10)

You install SQL Server Reporting Services (SSRS). You need to back up a copy of the symmetric key. Which command should you run?

A. rskeymgmt -d

B. rskeymgmt -i.

C. rskeymgmt -e -f %temp%\rs.key -p Password1

D. rskeymgmt -a -f %temp%\rs.key -p Password1

Answer: C

210. - (Topic 10)

...

A multinational retailer has retail locations on several continents. A single SQL Server Reporting Services (SSRS) instance is used for global reporting.

A SQL Server Analysis Services (SSAS) instance for each continent hosts a multidimensional database named RevenueData. Each RevenueData database stores data only for the continent in which it resides. All of the SSAS instances are configured identically. The cube names and objects are identical. Reports must meet the following requirements:

A report parameter named ServerName must be defined in each report.

When running a report, users must be prompted to select a server instance.

The report data source must use the Microsoft SQL Server Analysis Services data

source type.

You need to create a data source to meet the requirements.

How should you define the expression that is assigned to the connection string property of the data source?

A. ="Server=" &Parameters!ServerName.Value& ";Initial Catalog=RevenueData"

B. ="Data Source=" &Parameters!ServerName.Value& ";Initial Catalog=RevenueData"

C. ="Server=" & Parameters!ServerName.Value

D. ="Data Source=@ServerName;Initial Catalog=RevenueData"

E. ="Server=@ServerName;Initial Catalog=RevenueData"

Q8. - (Topic 9)

A company runs SQL Server Database Engine and SQL Server Reporting Services (SSRS) in native mode. Reports are based on data that is cached in multiple shared datasets. Source data is archived each day at midnight for regulatory compliance purposes. The shared datasets may continue to cache data that should not be used in reports. Shared report schedules are often paused during nightly server maintenance windows.

Reports must not return archived data.

You need to create a fully automated solution to ensure that reports do not deliver archived data.

What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Write a script that calls the flushcache method to clear individual items from the SSRS cache. Create a SQL Server Agent job that runs rs.exe with the script as an input file, and schedule the job to run every day after the archive process completes.

B. Create a SQL Server Agent job that uses a Transact-SQL (T-SQL) step to truncate the dbo.ExecutionCache table in the ReportServerTempDB database. Schedule the job to run every day after the archive process completes.

C. Create a SQL Server Agent job that restarts the SQL Server Reporting Services service. Schedule the job to run every day after the archive process completes.

D. Create a shared schedule. Configure the datasets to expire on the shared schedule.

Answer: A

Q9. - (Topic 9)

You are designing a reporting solution that uses SQL Server Reporting Services (SSRS) in SharePoint integrated mode.

The reporting solution must meet the following requirements:

. Allow report writers to reuse content between different reports.

. Allow report writers to modify reusable content in SharePoint.

. Retain version history for report content.

You need to choose a reporting method that meets the requirements.

What should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. drillthrough reports

B. linked reports

C. subreports

D. report parts

Answer: D

Q10. - (Topic 4)

You need to implement the security requirement for the sales representatives.

Which MDX expression should you use?

A. Option A

B. Option B

C. Option C

D. Option D

Answer: A

Topic 5, Data Architect

General Background

You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.

You have been tasked with designing a data warehouse that uses the company's financial

database as the data source. From the data warehouse, you will develop a cube to simplify

the creation of accurate financial reports and related data analysis.

Background You will utilize the following three servers: . ServerA runs SQL Server Database Engine. ServerA is a production server and also hosts the financial database.

. ServerB runs SQL Server Database Engine, SQL Server Analysis Services (SSAS) in multidimensional mode, SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS).

. ServerC runs SSAS in multidimensional mode. . The financial database is used by a third-party application and the table structures cannot be modified.

The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)

The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is partitioned by month. It contains only the last five years of financial data. The CouponUsed, OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is less than 10 MB and has only one partition.

The SecurityFilter table specifies the sites to which each user has access.

Business Requirements The extract, transform, load (ETL) process that updates the data warehouse must run daily between 8:00 P.M. and 5:00 A.M. so that it doesn't impact the performance of ServerA during business hours. The cube data must be available by 8:00 A.M.

The cube must meet the following business requirements:

Ensure that reports display the most current information available. Allow fast access to support ad-hoc reports and data analysis.

Business Analysts will access the data warehouse tables directly, and will access the cube by using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint Services. These tools will access only the cube and not the data warehouse.

Technical Requirements SSIS solutions must be deployed by using the project deployment model. You must develop the data warehouse and store the cube on ServerB. When the number of concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to ServerC and meet following requirements:

Maintain copies of the cube on ServerB and ServerC. Ensure that the cube is always available on both servers.

Guaranteed success with TestInsides practice guides

Minimize query response time.

The cube must meet the following technical requirements:

.. ..

The cube must be processed by using an SSIS package.

The cube must contain the prior day's data up to 8:00 P.M. but does not need to

contain same-day data.

The cube must include aggregation designs when it is initially deployed.

A product dimension must be added to the cube. It will contain a hierarchy

comprised of product name and product color.

Because of the large size of the SalesTransactions table, the cube must store only aggregations—the data warehouse must store the detailed data. Both the data warehouse and the cube must minimize disk space usage.

As the cube size increases, you must plan to scale out to additional servers to minimize processing time.

The data warehouse must use a star schema design. The table design must be as denormalized as possible. The history of changes to the Customer table must be tracked in the data warehouse. The cube must use the data warehouse as its only data source.

Security settings on the data warehouse and the cube must ensure that queries against the SalesTransactions table return only records from the sites to which the current user has access.

The ETL process must consist of multiple SSIS packages developed in a single project by using the least amount of effort. The SSIS packages must use a database connection string that is set at execution time to connect to the financial database. All data in the data warehouse must be loaded by the SSIS packages.

You must create a Package Activity report that meets the following requirements:

Track SSIS package execution data (including package name, status, start time,

end time, duration, and rows processed).

Use the least amount of development effort.