70-466 | Microsoft 70-466 Exam Questions and Answers 2021

NEW QUESTION 1
DRAG DROP
You install a SQL Server Analysis Services (SSAS) instance in tabular mode on a server.
While processing a very large tabular model, you receive an out-of-memory error. You identify that the amount of physical memory in the server is insufficient. Additional physical memory cannot be installed in the server.
You need to configure the server to allow paging to disk by using the operating system page file (pagefile.sys).
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:

Explanation: Box 1:

Box 2:

Box 3:

Box 4:

Note:
* View or set configuration properties in Management Studio
✑ In SQL Server Management Studio, connect to an Analysis Services instance. In Object Explorer, right-click the Analysis Services instance, and then clickProperties. The General page appears, displaying the more commonly used properties.
✑ To view additional properties, click theShow Advanced (All) Propertiescheckbox at the bottom of the page.
Modifying server properties is supported only for tabular mode and multidimensional mode servers. If you installed PowerPivot for SharePoint, always use the default values unless you are directed otherwise by a Microsoft product support engineer.
* VertiPaqPagingPolicy
Specifies the paging behavior in the event the server runs low on memory. Valid values are as follows:
Zero (0) is the default. No paging is allowed. If memory is insufficient, processing fails with an out-of-memory error.
1 enables paging to disk using the operating system page file (pagefile.sys).
When VertiPaqPagingPolicy is set to 1, processing is less likely to fail due to memory constraints because the server will try to page to disk using the method that you specified. Setting the VertiPaqPagingPolicy property does not guarantee that memory errors will never happen. Out of memory errors can still occur under the following conditions:
There is not enough memory for all dictionaries. During processing, Analysis Services locks the dictionaries for each column in memory, and all of these together cannot be more than the value specified for VertiPaqMemoryLimit.
There is insufficient virtual address space to accommodate the process.
To resolve persistent out of memory errors, you can either try to redesign the model to reduce the amount of data that needs processing, or you can add more physical memory to the computer.
Applies to tabular server mode only
* Incorrect: VertiPaqMemoryLimit
If paging to disk is allowed, this property specifies the level of memory consumption (as a percentage of total memory) at which paging starts. The default is 60. If memory consumption is less than 60 percent, the server will not page to disk.
This property depends on the VertiPaqPagingPolicyProperty, which must be set to 1 in order for paging to occur.
Applies to tabular server mode only.

NEW QUESTION 2
You need to create the sales territory and product measures. Which aggregate function should you use for both measures?

• A. Count
• B. COUNT(DIST1NCT column_name)
• C. DistinctCount
• D. Distinct

Answer: C

NEW QUESTION 3
You are developing a SQL Server Analysis Services (SSAS) cube. The cube contains several dimensions, a local measure group, and a linked measure group. Both measure groups use MOLAP partitions.
You need to write-enable one of the linked measure group partitions to support Microsoft Excel 2010 PivotTable What-If Analysis.
What should you do before the partition can be write-enabled?

• A. Implement the cube as a local cube.
• B. Ensure that the measure group measures only use semiadditive aggregation functions.
• C. Implement the linked measure group as a local measure group.
• D. Ensure that the measure group measures only use nonadditive aggregation functions

Answer: C

NEW QUESTION 4
HOTSPOT
You are developing a SQL Server Analysis Services (SSAS) cube.
You create a degenerate dimension.
You need to define a relationship type for the dimension.
Which relationship type should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

Answer:

Explanation:

NEW QUESTION 5
You are developing a SQL Server Analysis Services (SSAS) tabular project.
A model contains tables and columns that must not be visible to the user. The columns and tables cannot be removed because they are used in calculations. The calculations are used to calculate the budget and forecast for the current quarter.
You need to hide the tables and columns. What should you do?

• A. Before adding the forecast calculations to the model, right-click the applicable tables and columns and select the Hide option.
• B. After adding the budget calculations to the model, in the Properties window for the applicable tables and columns, set the Visible property to True.
• C. Before adding the forecast calculations to the model, right-click the applicable tables and columns and select the Hide from Client Tools option.
• D. After adding the budget calculations to the model, in the Properties window for the applicable tables and columns, set the Enabled property to False.

Answer: C

NEW QUESTION 6
DRAG DROP
You are developing a SQL Server Analysis Services (SSAS) cube. You need to reuse a measure group from a different database.
In SQL Server Data Tools (SSDT), 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:

Explanation: Box 1:

Box 2:

Box 3:

Note:
* You can use the Linked Object Wizard to either link to or import cubes, dimensions, measure groups, calculations, and Key Performance Indicators (KPIs). You can link to or import these items from another database on the same server or from a database on a remote server
* On the Select a Data Source page of the Linked Object Wizard, choose the Analysis Services data source or create a new one.
* On the Select Objects page of the wizard, choose the dimensions you want to link to in the remote database. You cannot link to linked dimensions in the remote database.
* Incorrect:
The Business Intelligence Wizard can guide you through some or all the following steps: Define time intelligence for cubes.
Define account intelligence for cubes and dimensions. Define dimension intelligence for cubes and dimensions. Define unary operators for cubes.
Set custom member formulas for cubes and dimensions. Specify attribute ordering for dimensions.
Enable dimension writeback for dimensions. Define semi-additive behavior for cubes. Define currency conversion for cubes.

NEW QUESTION 7
You are modifying a SQL Server Analysis Services (SSAS) cube.
The cube consists of a single measure group that contains the following measures:
•Total Quantity On Hand
•Average Quantity On Hand
The measure group has a single partition that uses the MOLAP storage mode.
You need to modify the cube design to ensure that the Total Quantity On Hand measure is updated in real-time and that Average Quantity On Hand measure is updated hourly.
What should you do?

• A. Create a new measure group for the Total Quantity On Hand measur
• B. Configure the storage mode for the new measure group's partition to ROLAP.
• C. Create a drillthrough action that will query the underlying data source in real time for the Total Quantity On Hand measure.
• D. Change the storage mode of the partition to ROLAP.
• E. Add an additional MOLAP partition to the measure group.

Answer: A

NEW QUESTION 8
You are working with a SQL Server Reporting Services (SSRS) instance in native mode. An item role named Reports Writer is present on the server.
The Reports Writer role cannot view and modify report caching parameters.
You need to ensure that the Reports Writer role can view and modify report caching parameters.
What should you do?

• A. Add the Manage all subscriptions task to the Reports Writer role.
• B. Add the Manage report history task to the Reports Writer role.
• C. Add the View data sources task to the Reports Writer role.
• D. Add the Manage individual subscriptions task to the Reports Writer role.

Answer: B

NEW QUESTION 9
You are creating a SQL Server Analysis Services (SSAS) multidimensional database.
Users need a time dimension for:
✑ Dates
✑ Delivery dates
✑ Ship dates
You need to implement the minimum number of required SSAS objects. 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 functio
• F. Use a regular relationship between the time dimension and the measure group.
• G. Add a measure group that has one measure that uses the DistinctCount aggregate function.
• H. Add a calculated measure based on an expression that counts members filtered by the Exists and NonEmpty functions.
• I. Add a hidden measure that uses the Sum aggregate functio
• J. Add a calculated measure aggregating the measure along the time dimension.
• K. Create several dimension
• L. Add each dimension to the cube.
• M. Create a dimensio
• N. Then add a cube dimension and link it several times to the measure group.
• O. Create a dimensio
• P. Create regular relationships between the cube dimension and the measure grou
• Q. Configure the relationships to use different dimension attributes.
• R. Create a dimension with one attribute hierarch
• S. Set the IsAggregatable property to False and then set the DefaultMember propert
• T. Use a regular relationship between the dimension and measure group.
• U. Create a dimension with one attribute hierarch
• V. Set the IsAggregatable property to False and then set the DefaultMember propert
• W. Use a many-to-many relationship to link the dimension to the measure group.
• X. Create a dimension with one attribute hierarch
• Y. Set the ValueColumn property, set the IsAggregatable property to False, and then set the DefaultMember propert
• Z. Configure the cube dimension so that it does not have a relationship with the measure grou
• BA. Add a calculated measure that uses the MemberValue attribute property.
• BB. Create a new named calculation in the data source view to calculate a rolling su
• BC. Add a measure that uses the Max aggregate function based on the named calculation.

Answer: A

NEW QUESTION 10
You are developing a SQL Server Analysis Services (SSAS) cube for the sales department at your company.
The sales department requires the following set of metrics:
✑ Unique count of customers
✑ Unique count of products sold
✑ Sum of sales
You need to ensure that the cube meets the requirements while optimizing query response time.
What should you do? (Each answer presents a complete solution. Choose all that apply.)

• A. Place the measures in a single measure group.
• B. Place the distinct count measures in separate measure groups.
• C. Use the additive measure group functions.
• D. Use the semiadditive measure group functions.
• E. Use the Count and Sum measure aggregation functions.
• F. Use the Distinct Count and Sum measure aggregation functions.

Answer: BF

NEW QUESTION 11
HOTSPOT
You are designing a SQL Server Reporting Services (SSRS) report that sources data from a SQL Azure database.
You need to design the report to show the sum of sales. The sales must be grouped by region on the rows and year on the columns.
Which report item should you add? To answer, select the appropriate setting in the answer area.

Answer:

Explanation:

NEW QUESTION 12
You are developing a SQL Server Analysis Services (SSAS) cube.
You must create a four-level hierarchy for the employee dimension. Each level must be associated with an attribute in the employee dimension table. Two thirds of the dimension data contain values for all four attributes. The remainder of the dimension data contains values for the first three of the four attributes only.
You need to create the hierarchy so that logically missing members will not be shown by the reporting tool.
Which type of hierarchy should you create?

• A. A parent-child hierarchy
• B. A sparse hierarchy
• C. A ragged hierarchy
• D. A balanced hierarchy

Answer: C

NEW QUESTION 13
HOTSPOT
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? (To answer, select the appropriate option in the answer area.)

Answer:

Explanation:

NEW QUESTION 14
You are adding dimensions and a new measure group named Accounts Receivable to an existing SQL Server Analysis Services (SSAS) cube.
Date is one of the existing dimensions in the SSAS database. The underlying fact table for the measure group is associated with multiple dates, including InvoiceDate, DueDate and PaymentDate.
You need to ensure that users can slice the Accounts Receivable measures by InvoiceDate, DueDate, and PaymentDate. You also need to ensure that the time required to process the database is minimized.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Create three new perspectives named InvoiceDate, DueDate, and PaymentDate.
• B. Create three independent dimensions named InvoiceDate, DueDate and PaymentDate and then link all three of them to the Accounts Receivable measure group.
• C. Create cube dimensions named InvoiceDate, DueDate, and PaymentDate by using the existing Date dimension in the database.
• D. Add three attributes named InvoiceDate, DueDate, and PaymentDate to the existing Date dimension in the database.

Answer: C

NEW QUESTION 15
You are developing a SQL Server Analysis Services (SSAS) tabular project.
In the data warehouse, a table named Employee Security defines a relationship between a salesperson's name, logon ID, and assigned sales territory.
You need to ensure that each salesperson has access to data from only the sales territory assigned to that salesperson. You also need to minimize the development effort.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Create a new role with Read permission and then add each salesperson's logon as a member to the role.
• B. Create a separate tabular project for each sales territor
• C. Grant each salesperson access to the corresponding tabular model of the assigned sales territory.
• D. Add the Employee Security table to the model, define the relationships, and then implement dynamic security by using row filter
• E. Grant each salesperson access to the model.
• F. Create a new Active Directory Domain Services (AD DS) security group and add each salesperson as a membe
• G. Then create a new role with Read permissio
• H. Add the group as a member to the new role.

Answer: C

NEW QUESTION 16
You have a SQL Server Analysis Services (SSAS) database named DB1 on a server named Server1.
You need to deploy DB1 from Server1 to four other servers. If the destination server already contains a copy of DB1, the database must be updated only. If the destination server does not contain a copy of DB1, the database must be copied to the destination server.
What is the best deployment option to use? More than one answer choice may achieve the goal. Select the BEST answer.

• A. Analysis Services Deployment Wizard
• B. Analysis Management Objects (AMO) automation
• C. Backup and Restore
• D. Synchronize Database Wizard

Answer: D

Explanation: In the Synchronize Database Wizard dialog box, type the name of the source server and source database in the appropriate fields.
Synchronization will occur for source and destination databases that have the same name. If the destination server already has a database that shares the same name as the source database, the destination database will be updated with the metadata and data of the source. If the database does not exist, it will be created on the destination server. Click Next.
Ref: http://technet.microsoft.com/en-us/library/ms174928(v=sql.110).aspx

NEW QUESTION 17
You are developing a SQL Server Analysis Services (SSAS) tabular project.
You need to grant permission for salespersons to be able to view only the data based on their sales territory.
What should you do?

• A. Create a member and then create a Multidimensional Expressions (MDX) filter.
• B. Create a member and then create a Data Analysis Expressions (DAX) filter.
• C. Create a role and then create a Multidimensional Expressions (MDX) filter.
• D. Create a role and then create a Data Analysis Expressions (DAX) filter.

Answer: D

NEW QUESTION 18
You are designing a SQL Server Analysis Services (SSAS) cube based on a Microsoft Azure SQL Database data warehouse.
You need to implement a degenerate dimension. What should you do?

• A. Use the fact table as the data source for the dimension.
• B. Create snowflake dimension tables based on normalized views of the fact table in the data source.
• C. Create a junk dimension table based on the fact table in the data source.
• D. Add a surrogate key to the fact table and use it as the degenerate dimension key.

Answer: A

NEW QUESTION 19
You need create the data source view for the StandardReports project. What should you do?

• A. Create a data source, connect it to the data warehouse, and then use the Data Source View wizard.
• B. Generate a relational schema from the dimensions and cubes by using the Schema Generation wizard.
• C. Create a new data source view and then use the Import from Table wizard.
• D. Execute the Import from Table wizard and then use the Data Source View wizard.

Answer: A

NEW QUESTION 20
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. Create a member and then create a Data Analysis Expressions (DAX) filter.
• B. Create a member and then create a Multidimensional Expressions (MDX) filter.
• C. Use SQL Server Management Studio to create a rol
• D. Then create a Data Analysis Expressions (DAX) filter.
• E. Use SQL Server Management Studio to create a rol
• F. Then create a Multidimensional Expressions (MDX) filter.

Answer: C