###BeginCaseStudy###
Case Study: 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.
###EndCaseStudy###
You need to deploy the StandardReports project at the end of the current business day.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
A.
Use the Analysis Services Deployment utility to create an XMLA deployment script and
run it at the end of the day.
B.
Use the Analysis Services Deployment wizard to create an MDX deployment script and
run it at the end of the day.
C.
Use the Analysis Services Deployment wizard to create an XMLA deployment script and
run it at the end of the day.
D.
Deploy the project from SQL Server Data Tools (SSDT) at the end of the day.
Answer: AD
Explanation:
There are several methods you can use to deploy a tabular model project.
Most of the deployment methods that can be used for other Analysis Services projects, such as multidimensional, can also be used to deploy tabular model projects.
A: Deploy command in SQL Server Data Tools
The Deploy command provides a simple and intuitive method to deploy a tabular model project from the SQL Server Data Tools authoring environment.
Caution:
This method should not be used to deploy to production servers.
Using this method can overwrite certain properties in an existing model.
D: The Analysis Services Deployment Wizard uses the XML output files generated from a Microsoft SQL Server Analysis Services project as input files. These input files are easily modifiable to customize the deployment of an Analysis Services project. The generated deployment script can then either be immediately run or saved for later deployment.
Incorrect:
not B: The Microsoft.AnalysisServices.Deployment utility lets you start the Microsoft SQL Server Analysis Services deployment engine from the command prompt. As input file, the utility uses the XML output files generated by building an Analysis Services project in SQL Server Data Tools (SSDT).
A and C. Both the wizard and utility can generate the XMLA file rather than actually deploying the project. Per the case study, the actual deployment is driven by a command line utility. Therefore the answer cannot be D as this deploys directly from SSDT. Also, this question relates to the multidimensional cube, not the Tabular. The information quoted by Mohan references tabular models.
https://msdn.microsoft.com/en-us/library/ms162758.aspx
https://msdn.microsoft.com/en-us/library/ms174817.aspx
Yes, A and C, as stated in scenario:
A deployment script that can be executed from a command-line utility must be created to deploy the StandardReports project to ServerB.
So only scripts can be applied, and wizard does not allow to generate MDX scripts.
C & D are correct, the utility doesn’t create the script. it only executes it. so no A. , a MDX deployment script doesn’t exists so no B.
C is a valid option. Create the scipt using the wizard and run it. (It doesn’t ask or state how to run it (!).
D , the manual option, also does the job.
Thats correct! https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/deploy-model-solutions-with-the-deployment-utility