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:
• Server A runs SQL Server Database Engine, Server A is the data warehouse server.
• Server B runs SQL Server Database Engine, SQL Server Analysis Services (SSAS) in multidimensional mode, and SQL Server Integration Services (SSIS).
• Server C 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 Server A 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 Standard Reports project to Server B.
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 Server C 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: A