10 Tips For 70-470 examinee

70-470 Exam

Exam Code: 70-470 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Recertification for MCSE: Business Intelligence
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-470 Exam.

2021 Dec 70-470 exam price

Q21. - (Topic 9)

You are defining a named set by using Multidimensional Expressions (MDX) in a sales cube.

The cube includes a Product dimension that contains a Category hierarchy and a Color attribute hierarchy.

You need to return only the blue products in the Category hierarchy.

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

A. Option A

B. Option B

C. Option C

D. Option D

Answer: C

Q22. - (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 purged 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 purged data.

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

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

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

B. 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 purge process completes.

C. Create a SQL Server Agent job that uses a Transact-SQL (T-SQL) step to delete the data from the dbo.ExecutionCache table in the ReportServerTempDB database. Schedule the job to run every day after the purge process completes.

D. Republish the cached datasets by using SQL Server Data Tools.

Answer: B

Q23. - (Topic 9)

You need to design a data warehouse schema to support a multidimensional cube. The fact table will contain multiple columns representing order dates, shipping dates, and billing dates. The cube will contain a measure group based on the fact table.

What is the best design to achieve the goal? More than one answer choice may achieve the goal. Select the BEST answer.

A. In the database, create a time table for each date column in the fact table.

From the data source view, join each date column in the fact table to the corresponding

time table.

Create dimensions for each time table.

B. In the database, create a time table containing a column that corresponds to each date

column in the fact table.

From the data source view, join each date column in the fact table to the corresponding

column in the time table.

Create a dimension for the time table.

C. In the database, create a time table that uses an integer primary key and a datetime

column.

Create a dimension based on the time table.

From the Cube Designer, use the Dimension Usage tab to define a relationship between

the measure group date columns and the datetime column of the time dimension.

D. In the database, create a time table that uses an integer primary key and a datetime

column.

Create a dimension based on the time table.

From the Cube Designer, use the Dimension Usage tab to define a relationship between

the measure group date columns and the key column of the time dimension.

Answer: B

Q24. - (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 package deployment model. Use a SQL Server package configuration with a common filter. Change the contents of the SSIS Configurations table at runtime.

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. Save each SSIS package to a file share that can be accessed from all environments.

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

Answer: D

Q25. - (Topic 10)

You maintain a multidimensional Business Intelligence Semantic Model (BISM) that was developed with default settings.

The model has one cube and the cube has one measure group. The measure group is based on a very large fact table and is partitioned by month. The fact table is incrementally loaded each day with approximately 800,000 new rows.

You need to ensure that all rows are available in the cube while minimizing the processing time.

Which processing option should you use?

A. Process Add

B. Process Clear

C. Process Default

D. Process Index

E. Process Data

F. Process Full

Answer: A

Leading 70-470 test engine:

Q26. - (Topic 9)

You are designing a customer dimension for a multidimensional OLAP (MOLAP) database. The dimension table will contain millions of rows of data.

Customer dimension attributes will come from the following two sources:

. An enterprise resource planning (ERP) system that contains millions of rows of data. Attributes from this source will be updated daily.

. A marketing database that contains thousands of rows of data representing customers' geographic information. Attributes from this source will be updated monthly.

Most report queries against the cube use attributes from both sources. Customers in the cube dimension will frequently be filtered by the geographic attributes.

You need to design the dimension table and cube dimension to ensure that report queries perform well, and minimize the time required to reprocess cube attributes.

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

A. Create a dimension table for each source. Create one cube dimension that joins the two dimension tables together.

B. Create one dimension table that combines data from both sources. Create a cube dimension for each source.

C. Create one dimension table that combines data from both sources. Create one cube dimension that references the dimension table.

D. Create a dimension table for each source, and a cube dimension for each source.

Answer: D

Q27. - (Topic 2)

You need to configure the partition storage settings to support the reporting requirements. Which partition storage setting should you use?

A. Low-latency MOLAP

B. In-Memory

C. High-latency MOLAP

D. Regular

E. DirectQuery

F. LazyAggregations

Answer: A

Q28. DRAG DROP - (Topic 9)

You are developing a SQL Server Integration Services (SSIS) package to add a large amount of data to a fact table named factOrders. The factOrders table is partitioned by week and has a clustered index.

You need to load three weeks' data into the factOrders table by using the most efficient method.

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:

Q29. DRAG DROP - (Topic 9)

You are configuring an Excel Services service application in a Microsoft SharePoint farm.

Users will deploy Excel workbooks to SharePoint libraries that allow interaction with PivotTables through Excel Services. PivotTable data is sourced from secured SQL Server Analysis Services (SSAS) cubes and PowerPivot models inside published workbooks.

You need to ensure that users can refresh the PivotTables from within Excel Services without a warning message appearing.

What should you do?

To answer, drag the appropriate term or terms to the correct location or locations in the answer area. (Use only terms that apply.)

Answer:

Q30. DRAG DROP - (Topic 4)

You need to upgrade the SSASCluster.

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:

45. - (Topic 4)

You need to configure SSRS to send the required notification messages.

Which configuration settings should you use? (Each correct answer presents a partial solution. Choose all that apply.)

A. <SendUsing>2</SendUsing>

B. <SendUsing>contoso.com</SendUsing>

C. <SMTPServer>SMTP01/SMTPServer>

D. <SMTPServerPort>110</SMTPServerPort>

E. <SMTPServer>SSRS01/SMTPServer>

F. <From>reports@contoso.com</From>

G. <PermittedHosts>contoso.com</PermittedHosts>

Answer: A,C,F,G

Explanation:

In the configuration file, the delivery method is set through the SendUsing configuration setting.

SendUsing specifies a method for sending messages. You can choose between a network SMTP service or a local SMTP service pickup directory. To use a remote SMTP service, this value must be set to 2 in the RSReportServer.config file.

C, F:

From scenario: 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.

SMTPServer specifies the remote SMTP server or forwarder. This value is a required value if you are using a remote SMTP server or forwarder.

* From scenario: Report distribution must be secured by using SSL and must be limited to the contoso.com domain.

Note:

Configuration Options for Remote SMTP Service

The connection between the report server and an SMTP server or forwarder is determined by the following configuration settings:

SMTPServer specifies the remote SMTP server or forwarder. This value is a required value if you are using a remote SMTP server or forwarder.

From sets the value that appears in the From: line of an e-mail message. This value is a required value if you are using a remote SMTP server or forwarder.

Other values that are used for remote SMTP service include the following (note that you do not need to specify these values unless you want to override the default values).

SMTPServerPort is configured for port 25.

SMTPAuthenticate specifies how the report server connects to the remote SMTP server.

Reference: Configure a Report Server for E-Mail Delivery (Reporting Services), Configuration Options for Remote SMTP Service