Refined 70-466 Pack 2021

NEW QUESTION 1

You are developing a SQL Server Analysis Services (SSAS) cube.
The data warehouse has a table named FactStock that is used to track movements of stock. A column named MovementQuantity contains quantities of stock. A positive quantity is used for input and negative quantity is used for output. A column named Movement Date is related to the time dimension. The quantity in stock, at a given point in time, can be evaluated as the sum of all MovementQuantity values at that point in time.
You need to create a measure that calculates the quantity in stock value. 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 group that has one measure that uses the DistinctCount aggregate function.
• F. Add a calculated measure based on an expression that counts members filtered by the Exists and NonEmpty functions.
• G. Add a hidden measure that uses the Sum aggregate functio
• H. Add a calculated measure aggregating the measure along the time dimension.
• I. Create several dimension
• J. Add each dimension to the cube.
• K. Create a dimensio
• L. Then add a cube dimension and link it several times to the measure group.
• M. Create a dimensio
• N. Create regular relationships between the cube dimension and the measure grou
• O. Configure the relationships to use different dimension attributes.
• P. Create a dimension with one attribute hierarch
• Q. Set the IsAggrcgatable property to False and then set the DefaultMember propert
• R. Use a regular relationship between the dimension and measure group.
• S. Create a dimension with one attribute hierarch
• T. Set the IsAggregatable property to False and then set the DefaultMember propert
• . Use a many-to-many relationship to link the dimension to the measure group.
• . Create a dimension with one attribute hierarch
• . Set the IsAggregatable property to False and then set the DefaultMember propert
• . Use a many-to-many relationship to link the dimension to the measure group.
• . Create a dimension with one attribute hierarch
• . Set the ValueColumn property, set the IsAggregatable property to False, and then set the DefaultMember propert
• . Configure the cube dimension so that it does not have a relationship with the measure grou
• . Add a calculated measure that uses the MemberValue attribute property.
• . Create a new named calculation in the data source view to calculate a rolling su
• . Add a measure that uses the Max aggregate function based on the named calculation.

Answer: G

NEW QUESTION 2

You are developing a Microsoft SQL Analysis Services (SSAS) multidimensional project
A fact table named FactHouseSales has a measure column named Area. All values in the column are stored in square feet. Users must be able to analyze the area in different units.
You create a table named AreaUnit. Each row in the table consists of the unit name and a square feet conversion factor value.
You need to implement the area conversion in the project. 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.
• . Create a dimension with one attribute hierarch
• . Set the IsAggregatable property to False and then set the DefaultMember propert
• . Use a many-to-many relationship to link the dimension to the measure group.
• . Create a dimension with one attribute hierarch
• . Set the ValueColumn property, set the IsAggregatable property to False, and then set the DefaultMember propert
• . Configure the cube dimension so that it does not have a relationship with the measure grou
• . Add a calculated measure that uses the MemberValue attribute property.
• . Create a new named calculation in the data source view to calculate a rolling su
• . Add a measure that uses the Max aggregate function based on the named calculation.

Answer: N

NEW QUESTION 3

You are developing a new SQL Server Reporting Services (SSRS) report in SQL Server Data Tools (SSDT).
The report must define a report parameter to prompt the user for the business unit. Each business unit has a unique font scheme combination of font and size properties.
You need to ensure that all of the text boxes in the table headers use the correct business unit font properties.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Add one report variable for FontFamily Assign it with an expression to return the appropriate color
• B. For each header text box, set the Color and FontSize properties by using the variables.
• C. For each header text box, assign expressions to the FontFamily and FontSize properties.
• D. Add two report variables named FontFamily and FontSiz
• E. Assign them with expressions to return the appropriate color
• F. For each header text box, use expressions to set the FontFamily and FontSize properties by using the variables.
• G. Add two Microsoft Visual C# functions to the code block of the report to implement FontFamily and FontSize function
• H. For each header text box, use expressions to set the FontFamily and FontSize properties by using the functions.

Answer: D

NEW QUESTION 4
DRAG DROP
You are developing a SQL Server Analysis Services (SSAS) tabular project based on a Microsoft Azure SQL Database database. The ProcessingOption property for the project is set to Do Not Process.
Several calculated columns have been added to a table. The project has been deployed to the production server.
You need to ensure that newly added data is processed on the production server.
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.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1:

Box 2:

Box 3:

Note:
* To process a table
✑ In SQL Server Management Studio, in the tabular model database which contains the table you want to process, expand theTablesnode, then right-click on the table you want to process, and then clickProcess Table.
✑ In theProcess Tabledialog box, in theModelistbox, select one of the following process modes:
Etc-

NEW QUESTION 5

You need to create the hierarchy in the AdhocReports project. What should you do?

• A. Multi-select all of the columns, right-click the columns, and then click the Create Hierarchy command.
• B. Use the RELATEDTABLE() function to consolidate the tables, multi-select the columns in the hierarchy, right-click the columns, and then click the Create Hierarchy command.
• C. Use the RELATED() function to consolidate the columns in the DimSalesTerritory table, multi-select the columns, right-click the columns, and then click the Create Hierarchy command.
• D. Use the RELATED() function to consolidate the columns in the DimEmployee table, multi-select the columns, right-click the columns, and then click the Create Hierarchy command.

Answer: D

NEW QUESTION 6
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.)

• A. Mastered
• B. Not Mastered

Answer: A

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 7

You have a SQL Server Reporting Services (SSRS) instance. The instance has a report that displays 3 million records.
Users report that they experience performance issues when they use the report. You need to ensure that the report renders as quickly as possible.
Which two actions should you perform? Each correct answer presents part of the solution.

• A. Enable versioning.
• B. Enable caching.
• C. Enable report history.
• D. Create snapshots.
• E. Implement pagination.

Answer: AB

NEW QUESTION 8

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. Browse and Manage
• B. Administrator
• C. Read and Process
• D. Explore and Manage

Answer: C

Explanation:
* Giving a database role permission to process an Analysis Services database means that the role has permission to perform all processing options on the database. This includes the processing of all cubes, dimensions, mining structures, and mining models in the database. However, the role does not have permission to read database metadata or access any data in the database itself.

NEW QUESTION 9

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

NEW QUESTION 10

You are modifying a SQL Server Analysis Service (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. Change the storage mode of the partition to use proactive caching with minimum latency.
• B. Create an XMLA script that will process the cube and then use SQL Server Agent to execute the script continuously.
• C. Create a new measure group for the Average Quantity On Hand measur
• D. Configure the storage mode for the new measure group's partition to ROLAP.
• E. Create a new measure group for the Total Quantity On Hand measur
• F. Configure the storage mode for the new measure group's partition to ROLAP.

Answer: D

NEW QUESTION 11

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 12

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 13

You need to create the KPI in the AdhocReports project. What should you do?

• A. Create a measure by using the SUM([OrderQuantity]) expressio
• B. Then use the CREATE KPI CURRENTCUBE statement to define the KPI and target value.
• C. Create a measure by using the SUM([OrderQuantity]) expression and create a KPI based on the measur
• D. Then set the target value.
• E. Create a measure by using the COUNT([OrderQuantity]) expression and create a KPI based on the measur
• F. Then set the target value.
• G. Create a KPI based on the OrderQuantity column and then set the target value.

Answer: A

NEW QUESTION 14
HOTSPOT
You are designing a dimension named Employee for a SQL Server Analysis Services multidimensional project.
The Employee dimension contains a DateOfBirth attribute and a MaritalStatus attribute. You need to minimize the amount of time required to process the cube.
What should you do? To answer, select the appropriate relationship type for each attribute in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 15

You are planning to develop a SQL Server Analysis Services (SSAS) tabular project. The project will be deployed to a SSAS server that has 16 GB of RAM.
The project will source data from a SQL Server database that contains a fact table named Sales. The fact table has more than 60 billion rows of data.
You need to select an appropriate design to maximize query performance.
Which data access strategy should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Configure the database to use DirectQuery mod
• B. Create a clustered index which includes all of the foreign key columns of the fact table.
• C. Configure the database to use In-Memory mod
• D. Create a clustered index which includes all of the foreign key columns of the fact table.
• E. Configure the database to use In-Memory mod
• F. Create a columnstore index on all the columns of the fact table.
• G. Configure the database to use DirectQuery mod
• H. Create a columnstore index on all the columns of the fact table.

Answer: D

NEW QUESTION 16

You are designing a SQL Server Reporting Services (SSRS) report for a bank. The bank has Automated Teller Machines (ATMs) in several regions. ATM operational data is stored in a Microsoft Azure SQL Database database.
The report must use a map to display the location and status of the ATMs as shown in the following exhibit. (Click the Exhibit button.)

You need to ensure that the report displays only a user selected map region. Which source of spatial data should you use for the map?

• A. SQL Server spatial query
• B. Map gallery
• C. ESRI shape file
• D. Bing Maps layer

Answer: A

NEW QUESTION 17

You are developing a BI Semantic Model (BISM) based on a simple and small dataset sourced from SQL Server. The data size and complexity of the data relationships will not change. The model will be used to produce reports in Power View. The reports will show the relationship between product sales and rainfall over time.
You need to use an appropriate project type.
Which project types should you use? (Each answer presents a complete solution. Choose all that apply.)

• A. A tabular project that uses the In-Memory query mode
• B. A tabular project that uses the DirectQuery query mode
• C. A multidimensional project that uses the MOLAP storage mode and proactive cache
• D. A multidimensional project that uses the ROLAP storage mode and columnstore indexes
• E. A PowerPivot workbook that is deployed to Microsoft SharePoint Server 2010

Answer: ABE

NEW QUESTION 18

A production SQL Server Analysis Services (SSAS) cube is processed daily. The users query products by using a hierarchy named Products from a dimension named Product.
The DimProduct table in the data source view is used as the source of the Product dimension. The table has the following structure.

The Product dimension has three attribute hierarchies:
✑ Product
✑ Subcategory
✑ Category
The attributes have the following relationships defined: Product > Subcategory > Category. Each attribute has a key and a name sourced from the related key and name columns in the DimProduct table.
During processing, you receive the following error message: 'Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimProduct', Column: 'SubCategoryKey1, Value: "23'. The attribute is Subcategory'."
You verify that the data is accurate.
You need to ensure that the dimension processes successfully. What should you do?

• A. Delete the Products hierarchy.
• B. Relate the Subcategory and Category attributes directly to the Product attribute.
• C. Remove the duplicate data from the DimProduct table.
• D. Remove the Subcategory attribute.

Answer: B

NEW QUESTION 19

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

• A. High-latency MOLAP
• B. In-Memory
• C. Low-latency MOLAP
• D. DirectQuery
• E. LazyAggregations
• F. Regular

Answer: C

NEW QUESTION 20

You manage a SQL Server Reporting Services (SSRS) instance. The ReportingServicesService.exe.config file has been modified to enable logging.
Some users report that they cannot access the server.
You need to ascertain the IP addresses of the client computers that are accessing the server.
What should you do?

• A. View the ExecutionLog view in the Report Server database.
• B. View the Report Server service trace log.
• C. View the Report Server HTTP log.
• D. View the Windows System event log.

Answer: C

NEW QUESTION 21

You are developing a new SQL Server Reporting Services (SSRS) report in SQL Server Data Tools (SSDT). This report has a table named Table1 and a textbox named Textbox1.
Table1 is initially visible but the user must be able to choose when to hide it. You need to develop the report to meet the requirement.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. For the properties of Table1, configure the Display can be toggled by this report item option to use Textbox1.
• B. Configure Textbox1 to drill through to rerun the report to toggle the display of Table1.
• C. For the properties of Table1, configure the Display can be toggled by this report item option to use Table1
• D. Add a parameter to the report so users can choose the display state of Table1.

Answer: A

NEW QUESTION 22
HOTSPOT
You have a database named DB1. DB1 contains three tables named FactSales, DimCustomer, and DimEmployee. A sample from DimEmployee is shown in the following table.

A sample from DimCustomer is shown in the following table.

A sample from FactSales is shown in the following table.

You create a cube named Sales that uses the three tables. FactSales is used as a fact table, DimCustomer is used to create a dimension named Customer, and DimEmployee is used to create a dimension named Employee.
Users report the following issues:
✑ When browsing the Customer dimension, the users see a blank member in the state hierarchy under United Kingdom.
✑ When browsing the Employee dimension, the sales data for Sales Managers and
Sales Supervisors is NOT displayed.
You need to resolve the issues by configuring the properties of the dimension attributes.
What should you do? To answer, select the appropriate value for each property in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 23
HOTSPOT
A SQL Server Analysis Services (SSAS) cube has roles to define dimension data security. A role named USA allows users to browse data pertaining to the United States. A role named Canada allows users to browse data pertaining to Canada.
A user can browse sales data pertaining to the United States but cannot browse sales data pertaining to Canada. You validate that the user belongs to the USA and Canada roles.
You need to reproduce the issue in SQL Server Management Studio (SSMS).
Which option should you select? To answer, select the appropriate action in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 24

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

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 26
......