Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 4)
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: A,B,E
Q2. - (Topic 4)
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
Q3. - (Topic 4)
You are conducting a design review of a multidimensional project.
In the Customer Geography dimension, all non-key attributes relate directly to the key attribute.
The underlying data of the Customer Geography dimension supports relationships between attributes.
You need to increase query and dimension processing performance.
What should you do?
A. For the dimension attributes of the Customer Geography dimension, define appropriate attribute relationships.
B. For the dimension attributes of the Customer Geography dimension, set the GroupingBehavior property to EncourageGrouping.
C. For the Customer Geography dimension, set the Processing Mode property to LazyAggregations.
D. For the Customer Geography dimension, set the ProcessingPriority property to 1.
Answer: A
Q4. - (Topic 4)
You work in the Business Intelligence (BI) department of a multinational company.
To share its sales data between the various subsidiaries, the company has requested a new corporate BI solution that meets the following requirements:
. The solution must use SQL Server Analysis Services (SSAS) multidimensional or tabular Business Intelligence Semantic Model (BISM).
. The model must incrementally add 10 million fact rows of sales data per month. . The model must be translated to English, German, Chinese, or Spanish based on users' locale. . The model must be able to contain the most recent 36 months of data, in order to let users query the data.
You need to select the appropriate model type and partitioning strategy to meet the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create and deploy a BISM multidimensional model with one partition for all of the data.
B. Create and deploy a BISM tabular model with one partition for each of the 36 months.
C. Create and deploy a BISM multidimensional model with one partition for each of the 36 months.
D. Create and deploy a BISM tabular model with one partition for all of the data.
Answer: C
Q5. - (Topic 2)
You need to modify the Sales Regions hierarchy to meet the reporting requirements.
Which SSAS feature should you use?
A. Calculation
B. Translation
C. Perspective
D. Action
Answer: B
Q6. - (Topic 4)
You develop a SQL Server Analysis Services (SSAS) tabular project.The tabular model loads data from a SQL Server relational database each day.
You define a connection.
You need to ensure that the connection minimizes the attack surface area of the server.
How should you define the impersonation information for the connection? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use your domain credentials. Grant least privilege to your account in the source database.
B. Create and use a new Windows domain account. Grant least privilege to this account in the source database.
C. Use the credentials of the SQL Server Analysis Services (SSAS) service account. Grant least privilege to this account in the source database.
D. Use SQL Server authentication.
Answer: B
Q7. - (Topic 3)
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 RELATEDTABLEO 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 RELATEDO 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 RELATEDO 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: B
Q8. - (Topic 4)
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
Q9. - (Topic 4)
You are adding dimensions and a new measure group named Service Quality 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 FirstServiceDate, SecondServiceDate, and ThirdServiceDate.
You need to ensure that users can slice the Service Quality measures by FirstServiceDate, SecondServiceDate, and ThirdServiceDate. 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 cube dimensions named FirstServiceDate. SecondServiceDate, and ThirdServiceDate by using the existing date dimension in the database.
B. Create three independent dimensions named FirstServiceDate, SecondServiceDate, and ThirdServiceDate and then link all three of them to the Service Quality measure group.
C. Create three new perspectives named FirstServiceDate. SecondServiceDate, and ThirdServiceDate.
D. Add three attributes named FirstServiceDate. SecondServiceDate, and ThirdServiceDate to the existing date dimension in the database.
Answer: C
Q10. - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) tabular database. To maximize performance, queries must be resolved only by using cache.
You need to configure the appropriate query mode.
Which query mode should you select?
A. DirectQuery with In-Memory
B. In-Memory with DirectQuery
C. In-Memory
D. DirectQuery
Answer: C
Q11. - (Topic 4)
You are managing a SQL Server Reporting Services (SSRS) instance in native mode. A
role named Folder Access Controller is present on the server.
The Folder Access Controller role consists of only the Set security for individual items task.
When role members open Report Manager, they cannot view folders.
You need to modify the Folder Access Controller role sc that the role members can view
folders.
Which task should you add to the Folder Access Controller role?
A. Manage models
B. Manage reports
C. View reports
D. Manage folders
Answer: D
Q12. HOTSPOT - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) cube.
Revenue must be compared to a goal and described by a status and a trend. Revenue,
goal, status, and trend will be defined by Multidimensional Expressions (MDX) expressions.
You need to add the Revenue indicator.
Which tab should you select? (To answer, select the appropriate tab in the work area.)
Answer:
Q13. - (Topic 4)
You are managing a SQL Server Reporting Services (SSRS) instance in native mode. A system role named Developer Support is present on the server.
Members of the Developer Support role cannot modify the report execution timeout period.
You need to enable members of the Developer Support role to modify the report execution timeout period.
Which task should you add to the Developer Support role?
A. Manage report server properties
B. Manage shared schedules
C. Execute report definitions
D. Manage jobs
Answer: A
Q14. - (Topic 1)
You need to configure a hierarchy for DimProduct that meets the technical requirements.
What should you do?
A. Set ProductName as the parent of ProductSubCategory and set ProductSubcategory as the parent of ProductCategory. For ProductSubcategory, click Hide if Name Equals Parent.
B. Set ProductCategory as the parent of ProductSubCategory and set ProductSubcategory as the parent of ProductName. For ProductSubcategory, click Hide if Name Equals Parent.
C. Set ProductName as the parent of ProductSubcategory and set ProductSubCategory as the parent of ProductCategory. For ProductCategory, click Hide if Name Equals Parent.
D. Set ProductCategory as the parent of ProductSubcategory and set ProductSubCategory as the parent of ProductName. For ProductCategory, click Hide if Name Equals Parent.
Answer: B
Q15. - (Topic 4)
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
Q16. - (Topic 4)
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 role. Then create a Data Analysis Expressions (DAX) filter.
D. Use SQL Server Management Studio to create a role. Then create a Multidimensional Expressions (MDX) filter.
Answer: C