Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. HOTSPOT - (Topic 4)
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.
Answer:
114. DRAG DROP - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
You need to add a calculated column to a table in the model.
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.)
Q2. - (Topic 4)
You create a materialized reference dimension between a dimension named Regions and a measure group named Orders.
You use a table named Customers as a bridge as shown in the exhibit. (Click the Exhibit button.)
You need to ensure that data is updated to reflect any changes in the relationship. The solution must NOT require that the entire cube be reprocessed.
What should you reprocess?
A. The Sales partition
B. The Regions dimension
C. The Customers dimension
D. The Orders partition
Answer: C
Q3. - (Topic 4)
You develop a SQL Server Analysis Services (SSAS) stored procedure.
You need to ensure that developers can create Multidimensional Expressions (MDX) calculations that use the stored procedure.
What should you do?
A. Register the assembly on the SSAS server instance.
B. Copy the assembly to the SSAS installation directory and register it by using the CREATE ASSEMBLY T-SQL command.
C. Register the assembly on the SSAS server by using regedit.exe.
D. Register the assembly on the SharePoint server by using regeditexe.
Answer: B
Q4. - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) tabular project that will be used by the finance, sales, and marketing teams.
The sales team reports that the model is too complex and difficult to use. The sales team does not need any information other than sales-related resources in the tabular model. The finance and marketing teams need to see all the resources in the tabular model.
You need to implement a solution that meets the needs of the sales team while minimizing development and administrative effort.
What should you do?
A. Create a separate partition for each team.
B. Create a separate data source for each team.
C. Create a perspective for the sales team.
D. Enable client side security to filter non-sales data.
Answer: C
Q5. - (Topic 1)
Topic 1, Tailspin Toys
Tailspin Toys is a multinational company that manufactures toys. Tailspin Toys has offices in five regions worldwide. The company sells toys at various retail stores. The company also sells toys directly to consumers through a web site.
The company has the following departments:
...
Sales Distribution Manufacturing
Each department has an office in each region.
The fiscal calendar of Tailspin Toys runs from June to May.
The network contains a server farm that has Microsoft SharePoint Server 2013 installed.
Existing Environment Current Database Environment
Each department uses SharePoint team sites for internal collaboration.
All manufacturing information is stored in a relational database named Manufacturing. All sales information is stored in a relational database named Sales.
Tailspin Toys deploys SQL Server Analysis Services (SSAS) and configures SSAS to use tabular models. SSAS will be used for all sales reports.
Tailspin Toys deploys a SQL Server Reporting Services (SSRS) instance in SharePoint mode.
Sales Database
A database named Sales contains two tables named FactSales and DimProduct. FactSales contains the following columns:
...
SalesID Total Due OrderDate
DimProduct contains the following columns:
....
ProductID ProductName ProductCategory ProductSubcategory
The Sales database contains information about the products. Most of the products have a category and a subcategory. Certain products only have a category.
A sample from DimProduct is shown in the following table.
Requirements
Security Requirements
Tailspin Toys identifies the following security requirement: . Sales department users must be allowed to view the sales transactions from their region only. . Sales department users must be able to view the contents of the manufacturing reports. . Manufacturing department users must be able to create new manufacturing
reports. . Third-party and custom solutions must NOT be deployed to the reporting server. . Sales department users must NOT be able to create new manufacturing reports.
Planned Reporting Implementation
The manufacturing department plans to use the SSRS instance for its reports. The manufacturing department also plans to make its reports accessible from SharePoint. All manufacturing reports will use an existing database named Manufacturing.
Reporting Requirements
Tailspin Toys identifies the following reporting requirements: . All reports must contain the company logo and a header that contains the date and the time that the report was executed. . All reports must be created by using the SQL Server Data Tools.
Manufacturing report
You plan to create a report named Manufacturinglssues.rdl. The report has the following requirements: . Manufacturing department managers must be able to view product issues by product type, manufacturing plant location, and error type. . The manufacturing department managers must be able to change views by choosing options from drop-down lists.
Sales reports
You plan to create a sales report named RegionalSales.rdl. The report has the following requirements: . Users must be able to view the report by using a web browser. By default, subcategories and product details must be hidden when using the browser. . Users must be able to subscribe to receive the report by email. The report must be sent by email as a PDF attachment.
You plan to create a quarterly sales report named QuarterSales.rdl. The report must
display sales data by fiscal quarter.
Technical Requirements
Tailspin Toys identifies the following technical requirements: . Products in the DimProduct table that do NOT have a subcategory must use the category value as the subcategory value. . SSRS must NOT connect to databases more frequently than once every 30 minutes. . Sales department users must be able to use Microsoft Excel to browse tabular data.
=====
You need to recommend a solution for the sales department that meets the security requirements.
What should you recommend?
A. Create one role for all of the sales department users. Add a DAX filter that reads the current user name and retrieves the user's region.
B. Create one role for each region. Configure each role to have read access to a specific region. Add the sales department users to their corresponding role.
C. Create a table for each region. Create a role for each region. Grant each role read access to its corresponding table.
D. Create one role for all of the sales department users. Configure the role to have read access to the sales transactions. Ensure that all of the reports that access the sales transaction data restrict read access to the data from the corresponding sales department region only.
Answer: C
Explanation: Scenario: Tailspin Toys identifies the following security requirement:
.
Sales department users must be allowed to view the sales transactions from their region only.
.
Sales department users must be able to view the contents of the manufacturing reports.
.
Sales department users must NOT be able to create new manufacturing reports.
Q6. - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) tabular project. The model has tables named Invoice Line Items and Products.
The Invoice Line Items table has the following columns:
..
Product Id Unit Sales Price
The Unit Sales Price column stores the unit price of the product sold. The Products table has the following columns:
..
Product Id Maximum Sales Price
The Maximum Sales Price column is available only in the Products table.
You add a column named Is Overpriced to the Invoice Line Items table. The Is Overpriced column must store a value of TRUE if the value of the Unit Sales Price is greater than the value of the Maximum Sales Price. Otherwise, a value of FALSE must be stored.
You need to define the Data Analysis Expressions (DAX) expression for the Is Overpriced column.
Which DAX formula should you use? (Each answer represents a complete solution. Choose all that apply.)
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A,C
Q7. - (Topic 4)
You install SQL Server Reporting Services (SSRS). You need to back up a copy of the symmetric key. Which command should you run?
A. rskeymgmt -a -f %temp%\rs.key -p Password1
B. rskeymgmt -d
C. rskeymgmt -i
D. rskeymgmt -e -f %temp%\rs.key -p Password1
Answer: D
Q8. - (Topic 4)
...
You are developing a SQL Server Analysis Services (SSAS) multidimensional project.
A fact table is related to a dimension table named DimScenario by a column named ScenarioKey.
The dimension table contains three rows for the following scenarios:
Actual
Budget Q1
Budget Q3
You need to create a dimension to allow users to view and compare data by scenario.
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 function. Add a calculated measure aggregating the measure along the time dimension.
H. Create several dimensions. Add each dimension to the cube.
I. Create a dimension. Then add a cube dimension and link it several times to the measure group.
J. Create a dimension. Create regular relationships between the cube dimension and the measure group. Configure the relationships to use different dimension attributes.
K. Create a dimension with one attribute hierarchy. Set the IsAggrcgatable property to False and then set the DefaultMember property. Use a regular relationship between the dimension and measure group.
L. Create a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a many-to-many relationship to link the dimension to the measure group.
M. Create a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a many-to-many relationship to link the dimension to the measure group.
N. Create a dimension with one attribute hierarchy. Set the ValueColumn property, set the IsAggregatable property to False, and then set the DefaultMember property. Configure the cube dimension so that it does not have a relationship with the measure group. Add a calculated measure that uses the MemberValue attribute property.
O. Create a new named calculation in the data source view to calculate a rolling sum. Add a measure that uses the Max aggregate function based on the named calculation.
Answer: L
Q9. - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
A column named City must be added to the table named Customer. The column will be used in the definition of a hierarchy. The City column exists in the Geography table that is related to the Customer table.
You need to add the City column to the Customer table.
How should you write the calculation?
A. =RELATEDTABLE(Geography)
B. =RELATED(Geography[City])
C. =Geography[City]
D. City:=Geography[City]
E. City:=RELATED(Geography[City])
F. City :=RELATEDTABLE (Geography)
Answer: E
Q10. - (Topic 4)
You manage a SQL Server Reporting Services (SSRS) instance in SharePoint integrated mode.
You identify a problem with the operations performed by the Report Server Web service.
You need to view the Report Server trace logs. In which directory should you view the files?
A. %ProgramFiles%\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles
B. %ProgramFiles%\Common Files\Microsoft Shared\Web Server Extensions\14\Web Services\ReportServer\LogFiles
C. %ProgramFiles%\Common Files\ReportServer\LogFiles
D. %ProgramFiles%\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles
Answer: D
Q11. - (Topic 4)
You are restructuring an existing cube. One of the measures in the cube is Amount. The Sum aggregation function is used for the Amount measure. The cube includes a dimension named Account and the dimension's Type property is set to Accounts. The Account dimension includes an account type attribute.
You need to ensure that the Amount measure aggregates correctly according to the account type classification. Development effort must be minimized.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Develop a .NET application that uses Analysis Management Objects (AMO) to change the existing AggregateFunction property value of the Amount measure to FirstNonEmpty and then use the application.
B. Develop a .NET application that uses Analysis Management Objects (AMO) to change the existing AggregateFunction property value of the Amount measure to ByAccount and then use the application.
C. Use SQL Server Data Tools to change the AggregateFunction property value of the Amount measure to ByAccount.
D. Add the ByAccount attribute to the account dimension.
Answer: C
Q12. - (Topic 4)
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. Set the Type property of the partition's measure group to Forecast.
B. Implement the linked measure group as a local measure group.
C. Implement the local measure group as a linked measure group.
D. Set the StorageMode property of the linked measure group to Rolap.
Answer: B
Q13. DRAG DROP - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) multidimensional project. The project file includes two cubes named Finance and Operations. The project also includes a dimension named Date. The Date dimension includes two hierarchies named Fiscal and Calendar. The Date dimension has been added to both cubes.
You need to disable the Fiscal hierarchy in the Operations cube without impacting other database objects.
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:
Q14. - (Topic 3)
You need to create the KPI in the AdhocReports project in time for the next production release cycle.
What should you do?
A. Create a measure by using the COUNT([OrderQuantity]) expression and create a KPI based on the measure. Then set the target value. Check in the changes before the next release cycle.
B. Create a KPI based on the OrderQuantity column and then set the target value. Check in the changes before the next release cycle.
C. Create a measure by using the SUM([OrderQuantity]) expression and create a KPI based on the measure. Then set the target value. Check in the changes before the next release cycle.
D. Create a measure by using the SUM((OrderQuantity]) expression. Then use the CREATE KPI CURRENTCUBE statement to define the KPI and target value. Check in the changes before the next release cycle.
Answer: C
Q15. - (Topic 4)
You are developing a BI Semantic Model (BISM) that retrieves data from several sources including a Microsoft Azure SQL Database database and an OData data feed. The model will be deployed to a server with significantly more memory than the total size of the source data.
You have the data feed URL, which you will use when developing the model in SQL Server Data Tools (SSDT).
The model must meet the following requirements:
. Maximize performance
. Data latency of up to one month is acceptable
You need to choose a project type and a data access mode to meet the requirements.
What should you do?
A. Select the multidimensional project type and use the ROLAP storage mode.
B. Select the tabular project type and use the In-Memory query mode.
C. Select the tabular project type and use the DirectQuery query mode.
D. Select the multidimensional project type and use the MOLAP storage mode.
Answer: B
Q16. - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
In the data warehouse, a table named Sales Persons and Territories 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 need to use the least amount of development effort to achieve this goal.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create a new role named Sales Persons with Read permission. Add each salesperson's logon as a member to the role.
B. Add the Sales Persons and Territories table to the model, define the relationships, and then implement dynamic security by using row filters. Grant each salesperson access to the model.
C. Create a new Active Directory Domain Services (AD DS) security group and add each salesperson as a member. Then create a new role named Sales Persons with Read permission. Add the group as a member to the new role.
D. Create a separate tabular model for each sales territory and assign each tabular model a corresponding sales territory name. Grant each salesperson access to the corresponding tabular model of the assigned sales territory.
Answer: B