Q1. DRAG DROP - (Topic 4)
You are making changes to a cube named Sales.
You must increase the value of the measure named Sales Amount by 10%. The increase must be applied only to the children of the member named East in the dimension named Customer.
You need to complete the Multidimensional Expressions (MDX) statement in the calculations section of the Sales cube.
Which statement fragments should you use? (To answer, drag the appropriate statement component to the correct location or locations in the answer area. Use only components that apply.)
Answer:
Q2. - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
You need to grant the minimum permissions necessary to enable users to query data in a data model.
Which role permission should you use?
A. Explorer
B. Process
C. Browser
D. Administrator
E. Select
F. Read
Answer: F
Q3. - (Topic 4)
Topic 4, Mix Questions
You are developing a SQL Server Analysis Services (SSAS) tabular project. The model includes a table named Sales. The Sales table includes a single date column.
The Sales table must meet the following requirements:
. Queries must be able to return all rows. . Must be able to support four different processing schedules for different date ranges. . Date ranges must not include any overlapping data.
You need to implement a solution that meets the requirements.
What should you do?
A. Create four partitions for the Sales table. Create four roles. Use the same row filter queries for each role and partition.
B. Convert the Sales table into four smaller tables by using row filter queries. Use one perspective for all four tables.
C. Create four partitions for the Sales table. Use row filter queries for each partition.
D. Convert the Sales table into four smaller tables by using row filter queries. Use one perspective for each of the four tables.
Answer: C
Q4. - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
The model includes a table named DimEmployee. The table contains employee details, including the sales territory for each employee. The table also defines a column named EmployeeAlias which contains the Active Directory Domain Services (AD DS) domain and logon name for each employee. You create a role named Employees.
You need to configure the Employees roles so that users can query only sales orders for their respective sales territory.
What should you do?
A. Add a row filter that implements the LOOKUPVALUE and USERNAME functions.
B. Add a row filter that implements only the CUSTOMDATA function.
C. Add a row filter that implements the LOOKUPVALUE and CUSTOMDATA functions.
D. Add a row filter that implements only the USERNAME function.
Answer: A
Q5. - (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. City:= LOOKUP(Geography[City],Geography[GeographyKey],[GeographyKey])
B. City:= LOOKUPVALUE(Geography[City],Geography[GeographyKey],[GeographyKey])
C. =RELATED(Geography[City]) =RELATED(Geography.City)
D. =VALUES(Geography[City])
E. City:=VALUES(Geography[City])
Answer: C
Explanation: * RELATED Function Returns a related value from another table.
Q6. - (Topic 3)
Topic 3, Data Architect
General Background
You are the data architect for a company that uses SQL Server 2012 Enterprise Edition. You design data modeling and reporting solutions that are based on a sales data warehouse.
Background
The solutions will be deployed on the following servers: . ServerA runs SQL Server Database Engine, ServerA is the data warehouse server. . ServerB runs SQL Server Database Engine, SQL Server Analysis Services (SSAS) in multidimensional mode, and SQL Server Integration Services (SSIS). . ServerC runs SSAS in tabular mode, SQL Server Reporting Services (SSRS)
running in SharePoint mode, and Microsoft SharePoint 2010 Enterprise Edition with SP1.
The data warehouse schema currently contains the tables shown in the exhibit. (Click the Exhibit button.)
Business Requirements
The reporting solution must address the requirements of the sales team, as follows:
. Team members must be able to view standard reports from SharePoint.
. Team members must be able to perform ad-hoc analysis by using Microsoft Power
View and Excel.
. Team members can have standard reports delivered to them on a schedule of
their choosing.
The standard reports
..
Will use a sales territory hierarchy for organizing data by region. Will be accessible from SharePoint.
The Excel ad-hoc reports
..
Will use the same data store as the standard reports.
Will provide direct access to the data store for the sales team and a simplified view
for the executive team.
Technical Requirements
The standard reports must be based on an SSAS cube. The schema of the data warehouse on ServerA must be able to support the ability to slice the fact data by the following dates:
...
Order date (OrderDateKey) Due date (DueDateKey) Ship date (ShipDateKey)
Additions and modifications to the data warehouse schema must adhere to star schema design principles to minimize maintenance and complexity . The multidimensional and tabular models will be based on the data warehouse. The tabular and multidimensional models will be created by using SQL Server Data Tools (SSDT). The tabular project is named AdhocReports and the multidimensional project is named Standard Reports. The cube design in the Standard Reports project must define two measures for the unique count of sales territories (SalesTerritoryKey) and products (ProductKey).
A deployment script that can be executed from a command-line utility must be created to deploy the StandardReports project to ServerB.
The tabular model in the AdhocReports project must meet the following requirements:
. ..
A hierarchy must be created that consists of the SalesTerritoryCountry and SalesTerritoryRegion columns from the DimSalesTerritory table and the EmployeeName column from the DimEmployee table. A key performance indicator (KPI) must be created that compares the total quantity sold (OrderQuantity) to a threshold value of 1,000. A measure must be created to calculate day-over-day (DOD) sales by region based on order date.
SSRS on ServerC must be configured to meet the following requirements:
...
It must use a single data source for the standard reports.
It must allow users to create their own standard report subscriptions.
The sales team members must be limited to only viewing and subscribing to
reports in the Sales Reports library.
A week after the reporting solution was deployed to production, Marc, a salesperson, indicated that he has never received reports for which he created an SSRS subscription. In addition, Marc reports that he receives timeout errors when running some reports on demand.
=====
You need to create the KPI in the AdhocReports project.
What should you do?
A. Create a measure by using the SUM([OrderQuantity]) expression. Then use the CREATE KPI CURRENTCUBE statement to define the KPI and target value.
B. Create a measure by using the SUM([OrderQuantity]) expression and create a KPI based on the measure. Then set the target value.
C. Create a measure by using the COUNT([OrderQuantity]) expression and create a KPI based on the measure. Then set the target value.
D. Create a KPI based on the OrderQuantity column and then set the target value.
Answer: C
Q7. - (Topic 1)
After you deploy the RegionalSales report, users report that they cannot see product data when they receive the reports by email.
You need to ensure that the sales department managers can see all of the data.
In the report, you update the Hidden property of each group.
What should you do next?
A. In When the report is initially run, select Show or hide based on an expression. Set the expression to = (GlobalslRenderFormat.IsIntcractivc)
B. In When the report is initially run, select Show.
C. In When the report is initially run, select Show or hide based on an expression. Set the expression to =NOT (GlobalslRenderFormat.IsInteractive).
D. In When the report is initially run, select Hide.
Answer: D
Q8. - (Topic 1)
You need to configure the dataset for the ManufacturingIssues report. The solution must
meet the technical requirements and the reporting requirements.
What should you do?
A. Configure the dataset to use a stored procedure. Add the necessary parameters to the stored procedure.
B. Add a query to retrieve the necessary data from the database. Configure the dataset to use query parameters.
C. Add a query to retrieve the necessary data from the database. Configure the dataset to use filter parameters.
D. Configure the dataset to use a table. Ensure that the database has a table that contains the necessary information.
Answer: B
Q9. DRAG DROP - (Topic 4)
You are developing a SQL Server PowerPivot model to monitor the rate of production of defective products.
The PowerPivot model contains many tables. The FactProduction table is configured as shown in the following diagram.
The model requires a key performance indicator (KPI) named Defective Units to measure the rate of production of defective units. The rate of production of defective units is calculated by dividing the sum of the Defects column by the sum of the Units column.
The KPI has the following requirements.
. If the rate of production of defective products equals or exceeds 5% then the KPI must report a status of off target (red). . If the rate of production of defective products is less than 5% and equals or exceeds 2% then the KPI must report a status of slightly off target (yellow). . If the rate of production of defective products less than 2% then the KPI must report a status of on target (green).
You need to develop the KPI to meet the requirements.
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:
Q10. - (Topic 4)
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: A