New Updated Microsoft MCP 70-467 Real Exam Questions and Answers Download Topic 3, Data Architect

Ensurepass

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:

 

clip_image002ServerA runs SQL Server Database Engine. ServerA is a production server and also hosts the financial database.

clip_image002[1]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).

clip_image002[2]ServerC runs SSAS in multidimensional mode.

clip_image002[3]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.)

 

clip_image004

 

The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. Th
e 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:

 

clip_image002[4]Ensure that reports display the most current information available.

clip_image002[5]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:

 

clip_image002[6]Maintain copies of the cube on ServerB and ServerC.

clip_image002[7]Ensure that the cube is always available on both servers.

clip_image002[8]Minimize query response time.

 

The cube must meet the following technical requirements:

 

clip_image002[9]The cube must be processed by using an SSIS package.

clip_image002[10]The cube must contain the prior day’s data up to 8:00 P.M.but does not need to contain same-day data.

clip_image002[11]The cube must include aggregation designs when it is initially deployed.

clip_image002[12]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 settin
gs 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 5SIS 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:

 

clip_image002[13]Track SSIS package execution data (including package name, status, start time, end time, duration, and rows processed).

clip_image002[14]Use the least amount of development effort.

 

 

QUESTION 22  (Topic 3)

You need to identify changes in the financial database. What should you do?

 

A.

Add SQL Server replication to each table.

B.

Extract data from the current partition of each table.

C.

Add a timestamp column to each table.

D.

Perform a full extract of each table.

E.

Enable change data capture on each table.

 

Correct Answer: E

 

 

QUESTION 23

You need to create the Package Activity report. What should you do?

 

A.

Create a log table and use SSIS event handlers to write to the log table. Then create an SSRS report that uses the log table.

B.

use the SSIS log provider for SQL Server. Then create an SSRS report that uses the sysssislog table.

C.

Create a log table and build a custom log provider to write to the log table. Then create an SSRS report that uses the log table.

D.

Create an SSRS report that uses the

catalog.executions and catalog.execution_data_statistics views.

 

Correct Answer: D

 

 

QUESTION 24

You need to implement the aggregation designs for the cube. What should you do?

 

A.

Use the CREATE CACHE statement.

B.

Use the Aggregation Design Wizard.

C.

Create relational indexes on the source tables.

D.

Use the Usage-Based Optimization Wizard.

 

Correct Answer: B

 

 

QUESTION 25

You need to slice data by the CouponUsed, OnSale, and Closeout columns. What should you do?

 

A.

Create one linked dimension for each column.

B.

Create one degenerate dimension.

C.

Create one role-playing dimension.

D.

Create one junk dimension.

 

Correct Answer: D

 

 

QUESTION 26

You need to design a cube partitioning strategy to be implemented as the cube size increases. What should you do?

 

A.

Use relational OLAP (ROLAP) on all local partitions.

B.

Implement monthly remote partitions.

C.

Use multidimensional OLAP (MOLAP) on all local partitions.

D.

Implement monthly local partitions.

 

Correct Answer: B

 

 

 

 

 

QUESTION 27

You need to choose the appropriate key to use when designing a dimension table based on the Customer table. What should you do?

 

A.

Use a surrogate key.

B.

Use a natural key.

C.

Use the CustomerNumber column as the key.

D Concatenate the CustomerName and CustomerNumber columns and use the concatenated string as the key.

D.

Use the CustomerName column as the key.

 

Correct Answer: A

 

 

QUESTION 28

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.

 

Correct Answer: C

 

 

QUESTION 29

You need to scale out SSAS. What should you do?

 

A.

Back up the cube on ServerB and restore it on ServerC each day.

B.

Create an empty cube on ServerC and link to the objects in the cube on ServerB.

C.

Process the cube on both ServerB and ServerC each day.

D.

Synchronize the cube from ServerB to ServerC each day.

 

Correct Answer: D

 

 

QUESTION 30

You need to implement security in the cube to limit the sites visible to each user. What should you do?

 

A.

Create an SSAS database role in the cube
for each user and assign the sites each user can access to his or her database role.

B.

Create an SSAS server role for each user and assign the sites each user can access to his or her server role.

C.

Create an SSAS database role and define a Multidimensional Expressions (MDX) calculation to implement dynamic dimension security.

D.

Create a view on the SalesTransactions table that uses the SecurityFilter and User table data to limit the sites for each user.

 

Correct Answer: C

 

 

QUESTION 31

You need to implement the aggregation designs for the cube. What should you do?

 

A.

Use the Usage-Based Optimization Wizard.

B.

Use the Aggregation Design Wizard.

C.

Partition the cube by month.

D.

Implement cache warming in SSAS via an SSIS package.

 

Correct Answer: B

 

 

QUESTION 32

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

 

Correct Answer: C

 

 

QUESTION 33

You need to identify changes in the financial database. What should you do?

 

A.

Add SQL Server log shipping to each table.

B.

Add SQL Server mirroring to each table.

C.

Perform a full extract of each table.

D.

Enable change data capture on each table.

E.

Create an AlwaysOn Availability Group that includes all the tables.

 

Correct Answer: D

 

 

QUESTION 34

You need to configure a parameter for the database connection string. What should you do?

 

A.

Use a required package parameter.

B.

Use a required project parameter.

C.

Use a package configuration.

D.

Use a global variable.

 

Correct Answer: B

 

 

Instant Access to Download Latest Complete Collection of Microsoft MCP 70-467 Real Exam

Try Microsoft MCP 70-467 Free Demo