You need to include additional data sourced from a Micr…
You are designing a SQL Server Reporting Services (SSRS) report.
The report defines a single SQL Server data source and dataset.
You need to include additional data sourced from a Microsoft Azure SQL Database in the report.
What should you do?
You need to ensure that the model has the most recent d…
You are developing a tabular Business Intelligence Semantic Model (BISM) database based on a SQL Server database.
In the data source, the FactInternetSales table is partitioned by month. Data from the current month has been updated and new data has been inserted in the
FactInternetSales table, in the DimProduct table, and in the DimCustomer table.
In the model, the FactInternetSales table is also partitioned by month.
You need to ensure that the model has the most recent data while minimizing the processing time.
What should you do?
Which tool should you use?
You maintain SQL Server Analysis Services (SSAS) instances.
You need to configure an installation of PowerPivot for Microsoft SharePoint in a SharePoint farm.
Which tool should you use? (Each correct answer presents a complete solution. Choose all that apply.)
Which event class should you use?
You are troubleshooting query performance for a SQL Server Analysis Services (SSAS) cube.
A user reports that a Multidimensional Expressions (MDX) query is very slow.
You need to identify the MDX query statement in a trace by using SQL Server Profiler.
Which event class should you use?
You need to use the least amount of development effort …
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.)
Which role permission should you use?
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?
You need to implement a solution that meets the needs o…
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?
You need to create the hierarchy in the AdhocReports project
###BeginCaseStudy###
Testlet 1
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 create the hierarchy in the AdhocReports project.
What should you do?
You need to ascertain why Marc did not receive his reports
###BeginCaseStudy###
Testlet 1
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 ascertain why Marc did not receive his reports.
What should you do?
Which aggregate function should you use for both measures?
###BeginCaseStudy###
Testlet 1
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 create the sales territory and product measures.
Which aggregate function should you use for both measures?