Q1. - (Topic 4)
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You administer a Microsoft SQL Server Analysis Services (SSAS) tabular model for a retail company. The model is the basis for reports on inventory levels, popular products, and regional store performance.
The company recently split up into multiple companies based on product lines. Each company starts with a copy of the database and tabular model that contains data for a specific product line.
You need to optimize performance of queries that use the copied tabular models while minimizing downtime.
What should you do?
A. Ensure that DirectQuery is enabled for the model.
B. Ensure that DirectQuery is disabled for the model.
C. Ensure that the Transactional Deployment property is set to True.
D. Ensure that the Transactional Deployment property is set to False.
E. Process the model in Process Full mode.
F. Process the model in Process Data mode.
G. Process the model in Process Defrag mode.
Answer: C
Explanation:
The Transactional Deployment setting controls whether the deployment of metadata changes and process commands occurs in a single transaction or in separate transactions. If this option is True (default), Analysis Services deploys all metadata changes and all process commands within a single transaction.
If this option is False, Analysis Services deploys the metadata changes in a single transaction, and deploys each processing command in its own transaction.
References:https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/deployment-script-files-specifying-processing-options
Q2. - (Topic 4)
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have an existing multidimensional cube that provides sales analysis. The users can slice by date, product, location, customer, and employee.
The management team plans to evaluate sales employee performance relative to sales targets. You identify the following metrics for employees:
You need to implement the KPI based on the Status expression. Solution: You design the following solution:
Does the solution meet the goal?
A. Yes
B. No
Answer: A
Q3. - (Topic 4)
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a Microsoft SQL Server Analysis Services (SSAS) instance that is configured to use multidimensional mode. You create the following cube:
Users need to be able to analyze sales by color.
You need to create a dimension that contains all of the colors for products sold by the company.
Which relationship type should you use between the InternetSales table and the new dimension?
A. no relationship
B. regular
C. fact
D. referenced
E. many-to-many
F. data mining
Answer: B
Explanation:
A regular dimension relationship between a cube dimension and a measure group exists when the key column for the dimension is joined directly to the fact table.
References: https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models-olap-logical-cube-objects/dimension-relationships
Q4. HOTSPOT - (Topic 3)
A database named DB2 uses the InMemory query mode. Users frequently run the following query:
You need to configure SQL Server Profiler to determine why the query is performing poorly. Which three event should you monitor on the SQL Server Profiler trace events
configuration page? To answer, select the appropriate options in the answer area.
Answer:
Explanation:
By using SQL Profiler, you can intercept two classes of trace events from Analysis Services, DAX Query Plan and DirectQuery events, both generated by the DirectQuery engine. Here, in this scenario we have a DAX Query.
DAX Query Plan events are generated by the DAX formula.
By using the In-Memory mode, you store a copy of data in the xVelocity (VertiPaq) storage engine.
Figure: This is how a query is executed by using In-Memory mode.
References: Microsoft SQL Server 2012 Analysis Services, The BISM Tabular Model,
Microsoft Press (July 2012), page 331
From Scenario: Users report that the query takes a long time to complete.
Topic 4, Mix Questions
10. - (Topic 4)
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have an existing multidimensional cube that provides sales analysis. The users can slice by date, product, location, customer, and employee.
The management team plans to evaluate sales employee performance relative to sales targets. You identify the following metrics for employees:
You need to implement the KPI based on the Status expression. Solution: You design the following solution:
Does the solution meet the goal?
A. Yes
B. No
Q5. - (Topic 4)
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You administer a Microsoft SQL Server Analysis Services (SSAS) tabular model for a retail company. The model is the basis for reports on inventory levels, popular products, and regional store performance.
The company recently split up into multiple companies based on product lines. Each company starts with a copy of the database and tabular model that contains data for a specific product line.
You need to optimize performance of queries that use the copied tabular models while minimizing downtime.
What should you do?
A. Ensure that DirectQuery is enabled for the model.
B. Ensure that DirectQuery is disabled for the model.
C. Ensure that the Transactional Deployment property is set to True.
D. Ensure that the Transactional Deployment property is set to False.
E. Process the model in Process Full mode.
F. Process the model in Process Data mode.
G. Process the model in Process Defrag mode.
Answer: C
Explanation:
The Transactional Deployment setting controls whether the deployment of metadata changes and process commands occurs in a single transaction or in separate transactions. If this option is True (default), Analysis Services deploys all metadata changes and all process commands within a single transaction.
If this option is False, Analysis Services deploys the metadata changes in a single transaction, and deploys each processing command in its own transaction.
References:https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/deployment-script-files-specifying-processing-options
Q6. - (Topic 3)
A database named DB2 uses the InMemory query mode. Users frequently run the following query:
You need to ensure no users see the PriorYearSales measure in the field list for the Sales table.
What should you do?
A. Create a perspective, and ensure that the PriorYearSales measure is not added to the perspective. Ensure that users connect to the model by using the perspective.
B. Set the Display Folder property for PriorYearSales toHidden.
C. Remove the PriorYearSales measure from the default field set of the Sales table.
D. Create a role using Read permissions, and define a DAX expression to filter out the PriorYearSales measure. Add all users to the role.
Answer: A
Explanation:
Using perspectives in the data model might help you expose a subset of tables, columns, and measures that are useful for a particular type of analysis.
Usually, every user needs only a subset of data you create, and showing him or her the model through perspectives can offer a better user experience.
From scenario; The PriorYearSales measure is referenced by other measures, and is not intended to be analyzed directly by users.
References: Microsoft SQL Server 2012 Analysis Services, The BISM Tabular Model, Microsoft Press (July 2012), page 305
Q7. DRAG DROP - (Topic 3)
A database named DB2 uses the InMemory query mode. Users frequently run the following query:
You need to reconfigure the SSAS instance that hosts DB1.
Which three actions should 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:
Step 1: Set the default mode for the data model to DirectQuery.
You discover that the project has been deployed with the Direct Query Mode option set to OFF.
Step 2: Set the mode for the FactInternetSales table's partition to DirectQueryOnly. Initially, even DirectQuery models are always created in memory. The default query mode for the workspace database is also set toDirectQuery with In-Memory. This hybrid working mode lets you use the cache of imported data for improved performance during the model design process, while validating the model against DirectQuery requirements.
From Scenario: Most queries that use the SalesAnalysis data model use data from a table named FactInternetSales that is 20 gigabyte (GB) in size. Cached data must be available for the FactInternetSales table. All queries accessing the SalesAnalysis model must be executed in near real time.
Step 3: Run Process Full for the FactInternetSales partition.
When Process Full is executed against an object that has already been processed, Analysis Services drops all data in the object, and then processes the object. This kind of processing is required when a structural change has been made to an object, for example, when an attribute hierarchy is added, deleted, or renamed
Q8. DRAG DROP - (Topic 2)
You need to configure the SalesAnalysis cube to correct the sales analysis by customer calculation.
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:
Step 1: Open the cube editor, and open the Dimension Usage tab.
Step 2: Configure a relationship between the Customer dimension and the Sales measure group. Use Day as the granularity.
From scenario: The SalesAnalysis cube contains a fact table named CoffeeSale loaded from a table named FactSale in the data warehouse. The time granularity within the cube is 15 minutes. The cube is processed every night at 23:00. You determine that the fact table cannot be fully processed in the expected time. Users have reported slow query response times.
Step 3: Reprocess the cube.
Step 4: Deploy the project changes.
Q9. HOTSPOT - (Topic 4)
You are a database administrator in a company that uses Microsoft SharePoint Server for all intranet sites. You are responsible for the installation of new database server instances.
You must install Microsoft SQL Server Analysis Server (SSAS) to support deployment of the following projects. You develop both projects by using SQL Server Data Tools.
You need to install the appropriate services to support both projects.
What should you do? In the table below, select the appropriate services for each project. NOTE: Make only one selection in each column. Each correct selection is worth one point.
Answer:
Explanation:
Project1:
Project2: Multidimensional
Note: Analysis Services can be installed in one of three server modes: Multidimensional and Data Mining (default), Power Pivot for SharePoint, and Tabular.
Q10. - (Topic 4)
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have an existing multidimensional cube that provides sales analysis. The users can slice by date, product, location, customer, and employee.
The management team plans to evaluate sales employee performance relative to sales targets. You identify the following metrics for employees:
You need to implement the KPI based on the Status expression. Solution: You design the following solution:
Does the solution meet the goal?
A. Yes
B. No
Answer: B