Which two partitions should you recommend creating?
###BeginCaseStudy###
Case Study: 6
Tailspin Toys Case B
Overview
Tailspin Toys is a manufacturing company that has offices across the United States, Europe,
and Asia.
Tailspin Toys plans to implement a business intelligence (BI) solution for its US-based
headquarters to manage the sales data, including information on customer transactions,
products, sales quotas, and bonuses.
Existing Environment
Data Sources
Tailspin Toys currently stores data in line-of-business applications, relational databases, flat
files, and the following;
• A Microsoft Excel spreadsheet named MarketResearch.xlsx. The spreadsheet is stored
on a network drive in a directory owned by an analyst.
• A tabular model named Research.xlsx used in PowerPivot for Excel. Research.xlsx
uses MarketResearch.xlsx as one of its data sources.
Network
The network contains an Active Directory forest named tailspintoys.com. The forest contains
a Microsoft SharePoint Server 2013 server farm.
Implementation Plans
Databases
Tailspin Toys plans to build a star schema data warehouse named DB1. DB1 will be loaded
from several different sources and will be updated nightly to contain new sales data.
DB1 will contain the following table types:
• A fact table to store transactional data, including transaction date, productID,
customerID, quantity, and sales amounts.
• Dimension tables to store information about each customer, each product, each date,
and each sales department user.
BI Semantic Models
Tailspin Toys plans to deploy the following BI semantic models:
• A multidimensional cube named CUBE1 that will store sales data. CUBE1 will be
based on DB1 and will be hosted in SQL Server Analysis Services (SSAS). CUBE1
will contain two distinct count measures named UniqueCustomers and
UniqueProducts. The measures are expected to aggregate hundreds of millions of
rows from DB1.
• A tabular model named SalesCommission that will contain information about sales
department user quotas and commissions.
• A tabular model named Research that will contain the migrated model from
Research.xlsx.
• An instance of SSAS in tabular mode named Tabular.
Planned Reports and Queries
Tailspin Toys plans to implement the following reports and queries:
• Power View reports that use data from the Research model.
• Reports for each year the company recorded sales data that used the SalesCommission
model. The reports will use the Dates_Between() and the DatesInPeriod() DAX
functions in queries.
• Reports that use CUBE1 that contain the following query statements:
• A report named SalesByCategory that uses CUBE1 and the following query
statement: (Line numbers are included for reference only.)
Self-Service Reporting
Tailspin Toys plans to deploy the following self-service reports:
• Reports created by sales department specialists that use CUBE1 and contain
drillthroughs, maps, sparklines, and Key Performance Indicators (KPIs). The reports
will be stored in a SharePoint Server document library named Library1.
• Reports created by sales department managers that use the SalesCommission model.
The reports will contain visualizations that show sales department users their current
sales as compared to their quota.
• Power Pivot models stored in a SharePoint Server document library that is configured
as a PowerPivot Gallery named Gallery1.
Requirements
Data Security Requirements
Sales department users browsing CUBE1 must be able to view the sales data that relates to
their respective customers only.
Access to reports must be controlled by using SharePoint permissions.
ETL Requirements
Tailspin Toys identifies the following extract, transformation, and load (ETL) requirements:
• Nightly updates of DB1 must support the incremental load of dimension and fact
tables on separate schedules. Fact data may be loaded before dimension data.
• ETL processes must be able to update dimension attributes without losing context for
historical facts.
• Referential integrity between dimension and fact tables must be maintained at all
times.
Cube Performance Requirements
The design of CUBE1 must minimize the processing time of the UniqueCustomers and
Unique Products measures. The time required to process CUBE1 each night must be
minimized.
Data Refresh Requirements
The Research model must be refreshed nightly without interrupting the workflow of the analyst.
###EndCaseStudy###
You need to recommend a cube architecture for CUBE1. The solution must meet the
performance requirements for CUBE1.
Which two partitions should you recommend creating? Each correct answer presents part of
the solution.
Which arguments should you use to complete the query?
DRAG DROP
###BeginCaseStudy###
Case Study: 5
Contoso, Ltd Case B
General Background
You are the business intelligence (BI) solutions architect for Contoso, Ltd, an online retailer.
You produce solutions by using SQL Server 2012 Business Intelligence edition and
Microsoft SharePoint Server 2010 Service Pack 1 (SP1) Enterprise edition.
A SharePoint farm has been installed and configured for intranet access only. An Internetfacing web server hosts the company’s public e-commerce website. Anonymous access is not
configured on the Internet-facing web server.
Data Warehouse
The data warehouse is deployed on a 5QL Server 2012 relational database instance. The data
warehouse is structured as shown in the following diagram.
The following Transact-SQL (T-SQL) script is used to create the FactSales and FactPopulation tables:
The FactPopulation table is loaded each year with data from a Windows Azure Marketplace
commercial dataset. The table contains a snapshot of the population values for all countries of
the world for each year. The world population for the last year loaded exceeds 6.8 billion people.
ETL Process
SQL Server Integration Services (SSIS) is used to load data into the data warehouse. All SSIS
projects are developed by using the project deployment model.
A package named StageFactSales loads data into a data warehouse staging table. The package
sources its data from numerous CSV files exported from a mainframe system. The CSV file
names begin with the letters GLSD followed by a unique numeric identifier that never
exceeds six digits. The data content of each CSV file is identically formatted.
A package named LoadFactFreightCosts sources data from a Windows Azure SQL Database
database that has data integrity problems. The package may retrieve duplicate rows from the database.
The package variables of all packages have the RaiseChangedEvent property set to true. A
package-level event handler for the OnVariableValueChanged event consists of an Execute
SQL task that logs the System::VariableName and System::VariableValue variables.
Data Models
SQL Server Analysis Services (SSAS) is used to host the Corporate BI multidimensional
database. The Corporate BI database contains a single data source view named Data
Warehouse. The Data Warehouse data source view consists of all data warehouse tables. All
data source view tables have been converted to named queries.
The Corporate BI database contains a single cube named Sales Analysis and three database
dimensions: Date, Customer and Product. The dimension usage for the Sales Analysis cube is
as shown in the following image.
The Customer dimension contains a single multi-level hierarchy named Geography. The
structure of the Geography hierarchy is shown in the following image.
The Sales Analysis cube’s calculation script defines one calculated measure named Sales Per Capita
The calculated measure expression divides the Revenue measure by the Population
measure and multiplies the result by 1,000. This calculation represents revenue per 1,000
people.
The Sales Analysis cube produces correct Sales Per Capita results for each country of the
world; however, the Grand Total for all countries is incorrect, as shown in the following
image (rows 2-239 have been hidden).
A role named Analysts grants Read permission for the Sales Analysis cube to all sales and
marketing analysts in the company.
SQL Server Reporting Services (SSRS) is configured in SharePoint integrated mode. All
reports are based on shared data sources.
Corporate logo images used in reports were originally configured as data-bound images
sourced from a SQL Server relational database table. The image data has been exported to
JPG files. The image files are hosted on the Internet-facing web server. All reports have been
modified to reference the corporate logo images by using the fully qualified URLs of the
image files. A red X currently appears in place of the corporate logo in reports.
Users configure data alerts on certain reports. Users can view a report named Sales
Profitability on demand; however, notification email messages are no longer being sent when
Sales Profitability report data satisfies alert definition rules. The alert schedule settings for the
Sales Profitability report are configured as shown in the following image.
Business Requirements
Data Models
Users must be able to:
• Provide context to measures and filter measures by using all related data warehouse
dimensions.
• Analyze measures by order date or ship date.
Additionally, users must be able to add a measure named Sales to the report canvas by
clicking only once in the Power View field list. The Sales measure must allow users to
analyze the sum of the values in the Revenue column of the FactSales data warehouse table.
Users must be able to change the aggregation function of the Sales measure.
Analysis and Reporting
A sales manager has requested the following query results from the Sales Analysis cube for
the 2012 fiscal year:
• Australian postal codes and sales in descending order of sales.
• Australian states and the ratio of sales achieved by the 10 highest customer sales made
for each city in that state.
Technical Requirements
ETL Processes
If an SSIS package variable value changes, the package must log the variable name and the
new variable value to a custom log table.
The StageFactSales package must load the contents of all files that match the file name
pattern. The source file name must also be stored in a column of the data warehouse staging
table.
In the design of the LoadFactSales package, if a lookup of the dimension surrogate key value
for the product code fails, the row details must be emailed to the data steward and written as
an error message to the SSIS catalog log by using the public API.
You must configure the LoadFactFreightCosts package to remove duplicate rows, by using
the least development effort.
Data Models
Users of the Sales Analysis cube frequently filter on the current month’s data. You must
ensure that queries to the Sales Analysis cube default to the current month in the Order Date
dimension for all users.
You must develop and deploy a tabular project for the exclusive use as a Power View
reporting data source. The model must be based on the data warehouse. Model table names
must exclude the Dim or Fact prefixes. All measures in the model must format values to
display zero decimal places.
Analysis and Reporting
Reports must be developed that combine the SSIS catalog log messages with the package
variable value changes.
###EndCaseStudy###
You are creating the Australian postal code query.
Which arguments should you use to complete the query?
To answer, drag the appropriate arguments to the correct location or locations in the answer
area. (Use only arguments that apply.)
Which value should you use for the FormatString property?
###BeginCaseStudy###
Case Study: 2
Contoso, Ltd
Background
You are the business intelligence (BI) solutions architect for Contoso Ltd, a multinational
sales company with offices in London, Madrid, Paris, Brisbane, Tokyo, and New York.
Contoso sells office consumable products such as pens, printer ink, and paper.
You produce solutions by using SQL Server 2012 Business Intelligence Edition and
Microsoft SharePoint Server 2010 Enterprise Edition with SP1.
Technical Background
Contoso’s products are categorized by using four levels while some use only two or three
levels. Products are categorized as shown in the following table.
Contoso sells products through mobile sales staff, direct marketing, and its website. Sales
personnel are located in various regions around the world, and each region has a sales
manager who is paid a quarterly bonus based on the total sales in the region during the
quarter. Regions are categorized as shown in the following table.
SQL Server Analysis Services (SSAS) is used to host a multidimensional database. The
database contains a single cube named Sales and three database dimensions named Products,
Regions, and Date. A single measure named Sales Total has been defined in the cube. The
data source for the database is a SQL Server data warehouse.
The Products dimension contains a single user-defined hierarchy named Products. To prevent
the display of empty members when users browse the Products dimension, the Extract,
Transform, and Load (ETL) process populates all missing values as shown in the following diagram.
The structure of the Products hierarchy is shown in the following diagram.
The Regions dimension contains a single user-defined hierarchy named Sales Regions. The
dimension is based on a single dimension table in the data warehouse and the attribute
relationships have not been modified since the dimension was created by using the
Dimension wizard. The structure of the Sales Regions hierarchy is shown in the following diagram.
The Date dimension contains a single user-defined hierarchy named Calendar. The structure
of the Calendar hierarchyis shown in the following diagram.
A role named UserRegions has been created in the SSAS database that will be used to filter
members in the Regions dimension based on the authenticated user.
Administrative staff from around the world will produce sales reports with Microsoft Excel
2010 based on the Sales cube.
Developers will produce reports with SQL Server Reporting Services (SSRS) based on the
Sales cube and the reports will be delivered to users through report subscriptions and a web
browser-All users log on to an Active Directory Domain Services (AD DS) domain named
contoso.com.
All client computers and servers are joined to the contoso.com domain.
Business Requirements
The BI system must meet the following reporting requirements:
• Display all sales figures in euro currency, regardless of the client’s reporting location
• Include a new measure named AD Sales that calculates average daily sales for a
selected month
• Support near real-time reporting while maintaining good performance for
multidimensional queries
• Support reports that show currency exchange rates
• Deliver executive reports that are parameterized and rendered from report snapshots
In addition, cube objects must use terms familiar to users from around the world. For
example, in the SalesRegions hierarchy, users from Great Britain must see the State level
presented as County when browsing the Sales cube.
The Sales cube must support a new measure group named Sales Planning. The measure group
must consist of a single measure named Sales Plan that enables the management team to use
Excel 2010 to enter sales plans for future monitoring.
The BI system must meet the following technical requirements:
Architecture requirements
o The system must use separate servers for each of the following components:
SQL Server Database Engine
SQL Server Integration Services
SQL Server Analysis Services in multidimensional mode SharePoint Server with
the Reporting Services Add-in
o All servers must be installed using U.S. regional settings.
o The system must source currency exchange rate data from a database hosted in
Microsoft Azure SQL Database.
Security requirements
o When possible, the system must use Windows authentication for all database
connections.
o The system must prevent users from querying data from outside of their region.
o The system must allow certain users to query data from multiple regions.
Development requirements
o When browsing the Products hierarchy, repeating values for different levels of a given
drill-path must be avoided. For example, Papers -> Copy Paper -> Copy Paper ->
Copy Paper should appear simply as Papers -> Copy Paper.
o The system must support report snapshots. The default maximum number of retained
snapshots must not exceed five.
###EndCaseStudy###
You need to configure the format of the Sales Total measure.
Which value should you use for the FormatString property?
Which two actions should you perform?
###BeginCaseStudy###
Case Study: 6
Tailspin Toys Case B
Overview
Tailspin Toys is a manufacturing company that has offices across the United States, Europe,
and Asia.
Tailspin Toys plans to implement a business intelligence (BI) solution for its US-based
headquarters to manage the sales data, including information on customer transactions,
products, sales quotas, and bonuses.
Existing Environment
Data Sources
Tailspin Toys currently stores data in line-of-business applications, relational databases, flat
files, and the following;
• A Microsoft Excel spreadsheet named MarketResearch.xlsx. The spreadsheet is stored
on a network drive in a directory owned by an analyst.
• A tabular model named Research.xlsx used in PowerPivot for Excel. Research.xlsx
uses MarketResearch.xlsx as one of its data sources.
Network
The network contains an Active Directory forest named tailspintoys.com. The forest contains
a Microsoft SharePoint Server 2013 server farm.
Implementation Plans
Databases
Tailspin Toys plans to build a star schema data warehouse named DB1. DB1 will be loaded
from several different sources and will be updated nightly to contain new sales data.
DB1 will contain the following table types:
• A fact table to store transactional data, including transaction date, productID,
customerID, quantity, and sales amounts.
• Dimension tables to store information about each customer, each product, each date,
and each sales department user.
BI Semantic Models
Tailspin Toys plans to deploy the following BI semantic models:
• A multidimensional cube named CUBE1 that will store sales data. CUBE1 will be
based on DB1 and will be hosted in SQL Server Analysis Services (SSAS). CUBE1
will contain two distinct count measures named UniqueCustomers and
UniqueProducts. The measures are expected to aggregate hundreds of millions of
rows from DB1.
• A tabular model named SalesCommission that will contain information about sales
department user quotas and commissions.
• A tabular model named Research that will contain the migrated model from
Research.xlsx.
• An instance of SSAS in tabular mode named Tabular.
Planned Reports and Queries
Tailspin Toys plans to implement the following reports and queries:
• Power View reports that use data from the Research model.
• Reports for each year the company recorded sales data that used the SalesCommission
model. The reports will use the Dates_Between() and the DatesInPeriod() DAX
functions in queries.
• Reports that use CUBE1 that contain the following query statements:
• A report named SalesByCategory that uses CUBE1 and the following query
statement: (Line numbers are included for reference only.)
Self-Service Reporting
Tailspin Toys plans to deploy the following self-service reports:
• Reports created by sales department specialists that use CUBE1 and contain
drillthroughs, maps, sparklines, and Key Performance Indicators (KPIs). The reports
will be stored in a SharePoint Server document library named Library1.
• Reports created by sales department managers that use the SalesCommission model.
The reports will contain visualizations that show sales department users their current
sales as compared to their quota.
• Power Pivot models stored in a SharePoint Server document library that is configured
as a PowerPivot Gallery named Gallery1.
Requirements
Data Security Requirements
Sales department users browsing CUBE1 must be able to view the sales data that relates to
their respective customers only.
Access to reports must be controlled by using SharePoint permissions.
ETL Requirements
Tailspin Toys identifies the following extract, transformation, and load (ETL) requirements:
• Nightly updates of DB1 must support the incremental load of dimension and fact
tables on separate schedules. Fact data may be loaded before dimension data.
• ETL processes must be able to update dimension attributes without losing context for
historical facts.
• Referential integrity between dimension and fact tables must be maintained at all
times.
Cube Performance Requirements
The design of CUBE1 must minimize the processing time of the UniqueCustomers and
Unique Products measures. The time required to process CUBE1 each night must be
minimized.
Data Refresh Requirements
The Research model must be refreshed nightly without interrupting the workflow of the analyst.
###EndCaseStudy###
You execute the SalesbyCategory report and receive the following error message:
“Members, tuples, or sets must use the same hierarchies in the function.”
You need to ensure that the query executes successfully.
Which two actions should you perform? Each correct answer presents part of the solution.
You need to create the KPI in the AdhocReports project
###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 create the KPI in the AdhocReports project.
What should you do?
You need to implement the SalesCommission model to support the planned reports and queries
###BeginCaseStudy###
Case Study: 6
Tailspin Toys Case B
Overview
Tailspin Toys is a manufacturing company that has offices across the United States, Europe,
and Asia.
Tailspin Toys plans to implement a business intelligence (BI) solution for its US-based
headquarters to manage the sales data, including information on customer transactions,
products, sales quotas, and bonuses.
Existing Environment
Data Sources
Tailspin Toys currently stores data in line-of-business applications, relational databases, flat
files, and the following;
• A Microsoft Excel spreadsheet named MarketResearch.xlsx. The spreadsheet is stored
on a network drive in a directory owned by an analyst.
• A tabular model named Research.xlsx used in PowerPivot for Excel. Research.xlsx
uses MarketResearch.xlsx as one of its data sources.
Network
The network contains an Active Directory forest named tailspintoys.com. The forest contains
a Microsoft SharePoint Server 2013 server farm.
Implementation Plans
Databases
Tailspin Toys plans to build a star schema data warehouse named DB1. DB1 will be loaded
from several different sources and will be updated nightly to contain new sales data.
DB1 will contain the following table types:
• A fact table to store transactional data, including transaction date, productID,
customerID, quantity, and sales amounts.
• Dimension tables to store information about each customer, each product, each date,
and each sales department user.
BI Semantic Models
Tailspin Toys plans to deploy the following BI semantic models:
• A multidimensional cube named CUBE1 that will store sales data. CUBE1 will be
based on DB1 and will be hosted in SQL Server Analysis Services (SSAS). CUBE1
will contain two distinct count measures named UniqueCustomers and
UniqueProducts. The measures are expected to aggregate hundreds of millions of
rows from DB1.
• A tabular model named SalesCommission that will contain information about sales
department user quotas and commissions.
• A tabular model named Research that will contain the migrated model from
Research.xlsx.
• An instance of SSAS in tabular mode named Tabular.
Planned Reports and Queries
Tailspin Toys plans to implement the following reports and queries:
• Power View reports that use data from the Research model.
• Reports for each year the company recorded sales data that used the SalesCommission
model. The reports will use the Dates_Between() and the DatesInPeriod() DAX
functions in queries.
• Reports that use CUBE1 that contain the following query statements:
• A report named SalesByCategory that uses CUBE1 and the following query
statement: (Line numbers are included for reference only.)
Self-Service Reporting
Tailspin Toys plans to deploy the following self-service reports:
• Reports created by sales department specialists that use CUBE1 and contain
drillthroughs, maps, sparklines, and Key Performance Indicators (KPIs). The reports
will be stored in a SharePoint Server document library named Library1.
• Reports created by sales department managers that use the SalesCommission model.
The reports will contain visualizations that show sales department users their current
sales as compared to their quota.
• Power Pivot models stored in a SharePoint Server document library that is configured
as a PowerPivot Gallery named Gallery1.
Requirements
Data Security Requirements
Sales department users browsing CUBE1 must be able to view the sales data that relates to
their respective customers only.
Access to reports must be controlled by using SharePoint permissions.
ETL Requirements
Tailspin Toys identifies the following extract, transformation, and load (ETL) requirements:
• Nightly updates of DB1 must support the incremental load of dimension and fact
tables on separate schedules. Fact data may be loaded before dimension data.
• ETL processes must be able to update dimension attributes without losing context for
historical facts.
• Referential integrity between dimension and fact tables must be maintained at all
times.
Cube Performance Requirements
The design of CUBE1 must minimize the processing time of the UniqueCustomers and
Unique Products measures. The time required to process CUBE1 each night must be
minimized.
Data Refresh Requirements
The Research model must be refreshed nightly without interrupting the workflow of the analyst.
###EndCaseStudy###
You need to implement the SalesCommission model to support the planned reports and
queries.
What should you do?
Which aggregate function should you use for both measures?
###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 create the sales territory and product measures.
Which aggregate function should you use for both measures?
Which design should you use?
DRAG DROP
###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 complete the design of the data warehouse.
Which design should you use? (To answer, drag the appropriate tables and relationships to
the correct location in the answer area. Use only the tables and relationships that apply.)
What is the best solution you should deploy?
###BeginCaseStudy###
Case Study: 6
Tailspin Toys Case B
Overview
Tailspin Toys is a manufacturing company that has offices across the United States, Europe,
and Asia.
Tailspin Toys plans to implement a business intelligence (BI) solution for its US-based
headquarters to manage the sales data, including information on customer transactions,
products, sales quotas, and bonuses.
Existing Environment
Data Sources
Tailspin Toys currently stores data in line-of-business applications, relational databases, flat
files, and the following;
• A Microsoft Excel spreadsheet named MarketResearch.xlsx. The spreadsheet is stored
on a network drive in a directory owned by an analyst.
• A tabular model named Research.xlsx used in PowerPivot for Excel. Research.xlsx
uses MarketResearch.xlsx as one of its data sources.
Network
The network contains an Active Directory forest named tailspintoys.com. The forest contains
a Microsoft SharePoint Server 2013 server farm.
Implementation Plans
Databases
Tailspin Toys plans to build a star schema data warehouse named DB1. DB1 will be loaded
from several different sources and will be updated nightly to contain new sales data.
DB1 will contain the following table types:
• A fact table to store transactional data, including transaction date, productID,
customerID, quantity, and sales amounts.
• Dimension tables to store information about each customer, each product, each date,
and each sales department user.
BI Semantic Models
Tailspin Toys plans to deploy the following BI semantic models:
• A multidimensional cube named CUBE1 that will store sales data. CUBE1 will be
based on DB1 and will be hosted in SQL Server Analysis Services (SSAS). CUBE1
will contain two distinct count measures named UniqueCustomers and
UniqueProducts. The measures are expected to aggregate hundreds of millions of
rows from DB1.
• A tabular model named SalesCommission that will contain information about sales
department user quotas and commissions.
• A tabular model named Research that will contain the migrated model from
Research.xlsx.
• An instance of SSAS in tabular mode named Tabular.
Planned Reports and Queries
Tailspin Toys plans to implement the following reports and queries:
• Power View reports that use data from the Research model.
• Reports for each year the company recorded sales data that used the SalesCommission
model. The reports will use the Dates_Between() and the DatesInPeriod() DAX
functions in queries.
• Reports that use CUBE1 that contain the following query statements:
• A report named SalesByCategory that uses CUBE1 and the following query
statement: (Line numbers are included for reference only.)
Self-Service Reporting
Tailspin Toys plans to deploy the following self-service reports:
• Reports created by sales department specialists that use CUBE1 and contain
drillthroughs, maps, sparklines, and Key Performance Indicators (KPIs). The reports
will be stored in a SharePoint Server document library named Library1.
• Reports created by sales department managers that use the SalesCommission model.
The reports will contain visualizations that show sales department users their current
sales as compared to their quota.
• Power Pivot models stored in a SharePoint Server document library that is configured
as a PowerPivot Gallery named Gallery1.
Requirements
Data Security Requirements
Sales department users browsing CUBE1 must be able to view the sales data that relates to
their respective customers only.
Access to reports must be controlled by using SharePoint permissions.
ETL Requirements
Tailspin Toys identifies the following extract, transformation, and load (ETL) requirements:
• Nightly updates of DB1 must support the incremental load of dimension and fact
tables on separate schedules. Fact data may be loaded before dimension data.
• ETL processes must be able to update dimension attributes without losing context for
historical facts.
• Referential integrity between dimension and fact tables must be maintained at all
times.
Cube Performance Requirements
The design of CUBE1 must minimize the processing time of the UniqueCustomers and
Unique Products measures. The time required to process CUBE1 each night must be
minimized.
Data Refresh Requirements
The Research model must be refreshed nightly without interrupting the workflow of the analyst.
###EndCaseStudy###
You need to deploy a solution for the planned self-service reports that will be used by the
sales department managers.
What is the best solution you should deploy? More than one answer choice may achieve the
goal. Select the BEST answer.
You need to ascertain why Marc did not receive his reports
###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 ascertain why Marc did not receive his reports.
What should you do?