Microsoft 70-466 Exam Practice Test Instant Access

Question 1

You are deploying an update to a SQL Server Analysis Services (SSAS) cube to a production environment.

The production database has been configured with security roles.

You need to preserve the existing security roles in the production database. Database roles and their user accounts from the development environment must not be deployed to the production server.

Which deployment method should you use?

AUse the SQL Server Analysis Services Deployment Wizard.

BBackup and restore the database.

CUse the SQL Server Analysis Services Migration Wizard.

DDeploy the project from SQL Server Data Tools to the production server.

Answer : A

Question 2

You are developing a tabular Business Intelligence Semantic Model (BISM) database based on a SQL Server database.

In the data source, the FactInternetSales table is partitioned by month. Data from the current month has been updated and new data has been inserted in the FactInternetSales table, in the DimProduct table, and in the DimCustomer table.

In the model, the FactInternetSales table is also partitioned by month.

You need to ensure that the model has the most recent data while minimizing the processing time.

What should you do?

AProcess the latest FactInternetSales model table partition, the DimProduct table, and the DimCustomer table with the Process Clear processing option. Then process the database with the Process Data processing option.

BProcess the latest FactInternetSales model table partition, the DimProduct table, and the DimCustomer table with the Process Clear processing option. Then process the database with the Process Full processing option.

CProcess the latest FactInternetSales model table partition, the DimProduct table, and the DimCustomer table with the Process Defrag processing option. Then process the database with the Process Recalc processing option.

DProcess the latest FactInternetSales model table partition, the DimProduct table, and the DimCustomer table with the Process Data processing option. Then process the database with the Process Defrag processing option.

EProcess the latest FactInternetSales model table partition, the DimProduct table, and the DimCustomer table with the Process Data processing option. Then process the database with the Process Recalc processing option.

Answer : D

Question 3

You need to create the KPI in the AdhocReports project.

What should you do?

ACreate a measure by using the SUM([OrderQuantity]) expression. Then use the CREATE KPI CURRENTCUBE statement to define the KPI and target value.

BCreate a measure by using the SUM([OrderQuantity]) expression and create a KPI based on the measure. Then set the target value.

CCreate a measure by using the COUNT([OrderQuantity]) expression and create a KPI based on the measure. Then set the target value.

DCreate a KPI based on the OrderQuantity column and then set the target value.

Answer : A

Question 4

You have a SQL Server 2012 instance that hosts a single-user database. The database does not contain usercreated stored procedures or user-created functions. You need to minimize the amount of memory used for query plan caching.

Which advanced server option should you modify?

AEnable Contained Databases

BAllow Triggers to Fire Others

COptimize for Ad hoc Workloads

DScan for Startup Procs

Answer : C

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

References: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hocworkloads-server-configuration-option

Question 5

You plan to design an application that temporarily stores data in a SQL Azure database.

You need to identify which types of database objects can be used to store data for the application. The solution must ensure that the application can make changes to the schema of a temporary object during a session.

Which type of objects should you identify?

ACommon table expressions (CTEs)

BTable variables

CTemporary tables

DTemporary stored procedures

Answer : C

There are two types of temporary tables: local and global. Local temporary tables are visible only to their

creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server.

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

References: https://technet.microsoft.com/en-us/library/ms186986(v=sql.105).aspx

Question 6

You are developing a SQL Server Analysis Services (SSAS) tabular project. A model defines a measure named Profit and includes a table named Date. The table includes year, semester, quarter, month, and date columns. The Date column is of data type Date. The table contains a set of contiguous dates.

You need to create a measure to report on year-over-year growth of profit.

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

AOption A

BOption B

COption C

DOption D

Answer : B, C, D

Question 7

You need to create the sales territory and product measures.

Which aggregate function should you use for both measures?

ACount

BCOUNT(DIST1NCT column_name)

CDistinctCount

DDistinct

Answer : C

Microsoft 70-466 Implementing Data Models and Reports with Microsoft SQL Server 2012 Exam Practice Test