You need to implement a strategy for efficiently storing sales order data in the data warehouse
###BeginCaseStudy###
Case Study: 4
WingTip Toys
General Background
You are a data architect for WingTip Toys. The company uses SQL Server 2012 Enterprise
edition. SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS)
are installed on separate servers.
Data Warehouse
The company’s data warehouse initially contained less than 100 MB and 100 million rows of
data from only one data source. It now contains more than 10 TB and 10 billion rows of data,
in 25 tables, from 12 data sources.
The largest table in the data warehouse, the factOrders table, contains 5 TB of data. The
factOrders table contains three date keys: OrderDateKey InvoiceDateKey, and ShipDateKey.
The data warehouse server has 1 TB of RAM. Memory usage is currently at 20 percent.
One billion rows of data are added to the data warehouse each month. New data is copied
each night from the data sources into SQL Server staging tables, and existing records are not
updated. The largest data set is order information, which is loaded in parallel into multiple
staging tables, one for each data source. All the staging tables have the same structure and
belong to the same filegroup as the factOrders table.
The dimCustomers table stores customer information that may change over time.
Data Models
You are developing three SSAS databases, as described in the following table.
Reporting
Business users frequently generate reports in Microsoft Excel by using PowerPivot. The
PowerPivot Management Dashboard does not currently display any usage data.
Several SSRS reports exist that use the data warehouse as a source. The data warehouse
queries are aggregate queries that use the factOrders table and one or more dimension tables.
All SSRS data sources use Integrated Windows authentication.
SSRS displays a security access error message when managers run SSRS reports based on the
Operations database.
Reporting performance has become unacceptably slow.
Business Requirements
Improve the query speed of the SSRS reports.
Allow business users to create reports by using PowerPivot and Power View.
Ensure that all users other than business users can view metadata for the Customers
dimension. Ensure that business users cannot view metadata for the Customers dimension.
Technical Requirements
Modify the tables in the data warehouse to minimize aggregate query processing time.
Minimize disk storage in the data warehouse.
Ensure that all multidimensional models process data as quickly as possible.
Create a fact table named factCustomerContact in the data warehouse to store the contact
date, customer key, and communication type for each instance of customer contact.
Store the history of customer information changes in the dimCustomers table.
Move data from the staging tables into the factOrders table as quickly as possible. When
creating dimensions for the date keys in the factOrders table, minimize storage space
requirements and optimize the cube processing time.
Ensure that queries against the Sales database return the most current data in the data
warehouse.
Ensure that the SSAS model of the Finance database does not page to disk or return a
memory error as the size of the database grows.
Create an SSAS monitoring solution that tracks the following data:
• Queries answered per second
• Queries from cache direct per second
• Queries from file per second.
###EndCaseStudy###
You need to implement a strategy for efficiently storing sales order data in the data warehouse.
What should you do?
You need to configure the dataset for the ManufacturingIssues report
###BeginCaseStudy###
Case Study: 1
Tailspin Toys
Background
Tailspin Toys is a multinational company that manufactures toys. Tailspin Toys has offices in
five regions worldwide. The company sells toys at various retail stores. The company also
sells toys directly to consumers through a web site.
The company has the following departments:
• Sales
• Distribution
• Manufacturing
Each department has an office in each region.
The fiscal calendar of Tailspin Toys runs from June to May.
The network contains a server farm that has Microsoft SharePoint Server 2013 installed.
Existing Environment
Current Database Environment
Each department uses SharePoint team sites for internal collaboration.
All manufacturing information is stored in a relational database named Manufacturing. All
sales information is stored in a relational database named Sales.
Tailspin Toys deploys SQL Server Analysis Services (SSAS) and configures SSAS to use
tabular models. SSAS will be used for all sales reports.
Tailspin Toys deploys a SQL Server Reporting Services (SSRS) instance in SharePoint
mode.
Sales Database
A database named Sales contains two tables named FactSales and DimProduct. FactSales
contains the following columns:
• SalesID
• Total Due
• OrderDate
DimProduct contains the following columns:
• ProductID
• ProductName
• ProductCategory
• ProductSubcategory
The Sales database contains information about the products. Most of the products have a
category and a subcategory. Certain products only have a category.
A sample from DimProduct is shown in the following table.
Requirements
Security Requirements
Tailspin Toys identifies the following security requirement:
• Sales department users must be allowed to view the sales transactions from their
region only.
• Sales department users must be able to view the contents of the manufacturing
reports.
• Manufacturing department users must be able to create new manufacturing reports.
• Third-party and custom solutions must NOT be deployed to the reporting server.
• Sales department users must NOT be able to create new manufacturing reports.
Planned Reporting Implementation
The manufacturing department plans to use the SSRS instance for its reports. The
manufacturing department also plans to make its reports accessible from SharePoint. All
manufacturing reports will use an existing database named Manufacturing.
Reporting Requirements
Tailspin Toys identifies the following reporting requirements:
• All reports must contain the company logo and a header that contains the date and the
time that the report was executed.
• All reports must be created by using the SQL Server Data Tools.
Manufacturing report
You plan to create a report named Manufacturinglssues.rdl. The report has the following
requirements:
• Manufacturing department managers must be able to view product issues by product
type, manufacturing plant location, and error type.
• The manufacturing department managers must be able to change views by choosing
options from drop-down lists.
Sales reports
You plan to create a sales report named RegionalSales.rdl. The report has the following
requirements:
• Users must be able to view the report by using a web browser. By default,
subcategories and product details must be hidden when using the browser.
• Users must be able to subscribe to receive the report by email. The report must be sent
by email as a PDF attachment.
You plan to create a quarterly sales report named QuarterSales.rdl. The report must display
sales data by fiscal quarter.
Technical Requirements
Tailspin Toys identifies the following technical requirements:
• Products in the DimProduct table that do NOT have a subcategory must use the
category value as the subcategory value.
• SSRS must NOT connect to databases more frequently than once every 30 minutes.
• Sales department users must be able to use Microsoft Excel to browse tabular data.
###EndCaseStudy###
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?
What should you add to line 08 in usp_ImportOrderDetails?
###BeginCaseStudy###
Case Study: 6
Coho Winery
Overview
You are a database developer for a company named Coho Winery. Coho Winery has an
office in London.
Coho Winery has an application that is used to process purchase orders from customers and
retailers in 10 different countries.
The application uses a web front end to process orders from the Internet. The web front end
adds orders to a database named Sales. The Sales database is managed by a server named
Server1.
An empty copy of the Sales database is created on a server named Server2 in the London
office. The database will store sales data for customers in Europe.
A new version of the application is being developed. In the new version, orders will be placed
either by using the existing web front end or by loading an XML file.
Once a week, you receive two files that contain the purchase orders and the order details of
orders fromoffshore facilities.
You run the usp_ImportOders stored procedure and the usp_ImportOrderDetails stored
procedure to copy the offshore facility orders to the Sales database.
The Sales database contains a table named Orders that has more than 20 million rows.
Database Definitions
Database and Tables
The following scripts are used to create the database and its tables:
Stored Procedures
The following are the definitions of the stored procedures used in the database:
Indexes
The following indexes are part of the Sales database:
Data Import
The XML files will contain the list of items in each order. Each retailer will have its own
XML schema and will be able to use different types of encoding. Each XML schema will use
a default namespace. The default namespaces are not guaranteed to be unique.
For testing purposes, you receive an XSD file from a customer.
For testing purposes, you also create an XML schema collection named ValidateOrder.
ValidateOrder contains schemas for all of the retailers.
The new version of the application must validate the XML file, parse the data, and store the
parsed data along with the original XML file in the database. The original XML file must be
stored without losing any data.
Reported Issues
Performance Issues
You notice the following for the usp_GetOrdersAndItems stored procedure:
• The stored procedure takes a long time to complete.
• Less than two percent of the rows in the Orders table are retrieved by
usp_GetOrdersAndItems.
• A full table scan runs when the stored procedure executes.
• The amount of disk space used and the amount of time required to insert data are very
high.
You notice that the usp_GetOrdersByProduct stored procedure uses a table scan when the
stored procedure is executed.
Page Split Issues
Updates to the Orders table cause excessive page splits on the IX_Orders_ShipDate index.
Requirements
Site Requirements
Users located in North America must be able to view sales data for customers in North
America and Europe in a single report. The solution must minimize the amount of traffic over
the WAN link between the offices.
Bulk Insert Requirements
The usp_ImportOrderDetails stored procedure takes more than 10 minutes to complete. The
stored procedure runs daily. If the stored procedure fails, you must ensure that the stored
procedure restarts from the last successful set of rows.
Index Monitoring Requirements
The usage of indexes in the Sales database must be monitored continuously. Monitored data
must be maintained if a server restarts. The monitoring solution must minimize the usage of
memory resources and processing resources.
###EndCaseStudy###
You need to implement a solution that addresses the bulk insert requirements.
What should you add to line 08 in usp_ImportOrderDetails?
You need to ensure that you can monitor the usage data
###BeginCaseStudy###
Case Study: 4
WingTip Toys
General Background
You are a data architect for WingTip Toys. The company uses SQL Server 2012 Enterprise
edition. SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS)
are installed on separate servers.
Data Warehouse
The company’s data warehouse initially contained less than 100 MB and 100 million rows of
data from only one data source. It now contains more than 10 TB and 10 billion rows of data,
in 25 tables, from 12 data sources.
The largest table in the data warehouse, the factOrders table, contains 5 TB of data. The
factOrders table contains three date keys: OrderDateKey InvoiceDateKey, and ShipDateKey.
The data warehouse server has 1 TB of RAM. Memory usage is currently at 20 percent.
One billion rows of data are added to the data warehouse each month. New data is copied
each night from the data sources into SQL Server staging tables, and existing records are not
updated. The largest data set is order information, which is loaded in parallel into multiple
staging tables, one for each data source. All the staging tables have the same structure and
belong to the same filegroup as the factOrders table.
The dimCustomers table stores customer information that may change over time.
Data Models
You are developing three SSAS databases, as described in the following table.
Reporting
Business users frequently generate reports in Microsoft Excel by using PowerPivot. The
PowerPivot Management Dashboard does not currently display any usage data.
Several SSRS reports exist that use the data warehouse as a source. The data warehouse
queries are aggregate queries that use the factOrders table and one or more dimension tables.
All SSRS data sources use Integrated Windows authentication.
SSRS displays a security access error message when managers run SSRS reports based on the
Operations database.
Reporting performance has become unacceptably slow.
Business Requirements
Improve the query speed of the SSRS reports.
Allow business users to create reports by using PowerPivot and Power View.
Ensure that all users other than business users can view metadata for the Customers
dimension. Ensure that business users cannot view metadata for the Customers dimension.
Technical Requirements
Modify the tables in the data warehouse to minimize aggregate query processing time.
Minimize disk storage in the data warehouse.
Ensure that all multidimensional models process data as quickly as possible.
Create a fact table named factCustomerContact in the data warehouse to store the contact
date, customer key, and communication type for each instance of customer contact.
Store the history of customer information changes in the dimCustomers table.
Move data from the staging tables into the factOrders table as quickly as possible. When
creating dimensions for the date keys in the factOrders table, minimize storage space
requirements and optimize the cube processing time.
Ensure that queries against the Sales database return the most current data in the data
warehouse.
Ensure that the SSAS model of the Finance database does not page to disk or return a
memory error as the size of the database grows.
Create an SSAS monitoring solution that tracks the following data:
• Queries answered per second
• Queries from cache direct per second
• Queries from file per second.
###EndCaseStudy###
You need to ensure that you can monitor the usage data.
What should you do?
What is the best way to achieve the goal?
###BeginCaseStudy###
Case Study: 1
Tailspin Toys
Background
Tailspin Toys is a multinational company that manufactures toys. Tailspin Toys has offices in
five regions worldwide. The company sells toys at various retail stores. The company also
sells toys directly to consumers through a web site.
The company has the following departments:
• Sales
• Distribution
• Manufacturing
Each department has an office in each region.
The fiscal calendar of Tailspin Toys runs from June to May.
The network contains a server farm that has Microsoft SharePoint Server 2013 installed.
Existing Environment
Current Database Environment
Each department uses SharePoint team sites for internal collaboration.
All manufacturing information is stored in a relational database named Manufacturing. All
sales information is stored in a relational database named Sales.
Tailspin Toys deploys SQL Server Analysis Services (SSAS) and configures SSAS to use
tabular models. SSAS will be used for all sales reports.
Tailspin Toys deploys a SQL Server Reporting Services (SSRS) instance in SharePoint
mode.
Sales Database
A database named Sales contains two tables named FactSales and DimProduct. FactSales
contains the following columns:
• SalesID
• Total Due
• OrderDate
DimProduct contains the following columns:
• ProductID
• ProductName
• ProductCategory
• ProductSubcategory
The Sales database contains information about the products. Most of the products have a
category and a subcategory. Certain products only have a category.
A sample from DimProduct is shown in the following table.
Requirements
Security Requirements
Tailspin Toys identifies the following security requirement:
• Sales department users must be allowed to view the sales transactions from their
region only.
• Sales department users must be able to view the contents of the manufacturing
reports.
• Manufacturing department users must be able to create new manufacturing reports.
• Third-party and custom solutions must NOT be deployed to the reporting server.
• Sales department users must NOT be able to create new manufacturing reports.
Planned Reporting Implementation
The manufacturing department plans to use the SSRS instance for its reports. The
manufacturing department also plans to make its reports accessible from SharePoint. All
manufacturing reports will use an existing database named Manufacturing.
Reporting Requirements
Tailspin Toys identifies the following reporting requirements:
• All reports must contain the company logo and a header that contains the date and the
time that the report was executed.
• All reports must be created by using the SQL Server Data Tools.
Manufacturing report
You plan to create a report named Manufacturinglssues.rdl. The report has the following
requirements:
• Manufacturing department managers must be able to view product issues by product
type, manufacturing plant location, and error type.
• The manufacturing department managers must be able to change views by choosing
options from drop-down lists.
Sales reports
You plan to create a sales report named RegionalSales.rdl. The report has the following
requirements:
• Users must be able to view the report by using a web browser. By default,
subcategories and product details must be hidden when using the browser.
• Users must be able to subscribe to receive the report by email. The report must be sent
by email as a PDF attachment.
You plan to create a quarterly sales report named QuarterSales.rdl. The report must display
sales data by fiscal quarter.
Technical Requirements
Tailspin Toys identifies the following technical requirements:
• Products in the DimProduct table that do NOT have a subcategory must use the
category value as the subcategory value.
• SSRS must NOT connect to databases more frequently than once every 30 minutes.
• Sales department users must be able to use Microsoft Excel to browse tabular data.
###EndCaseStudy###
You need to ensure that all reports meet the reporting requirements.
What is the best way to achieve the goal? More than one answer choice may achieve the
goal. Select theBEST answer.
You need to ensure that usp_GetOrdersAndItems completes as quickly as possible
###BeginCaseStudy###
Case Study: 6
Coho Winery
Overview
You are a database developer for a company named Coho Winery. Coho Winery has an
office in London.
Coho Winery has an application that is used to process purchase orders from customers and
retailers in 10 different countries.
The application uses a web front end to process orders from the Internet. The web front end
adds orders to a database named Sales. The Sales database is managed by a server named
Server1.
An empty copy of the Sales database is created on a server named Server2 in the London
office. The database will store sales data for customers in Europe.
A new version of the application is being developed. In the new version, orders will be placed
either by using the existing web front end or by loading an XML file.
Once a week, you receive two files that contain the purchase orders and the order details of
orders fromoffshore facilities.
You run the usp_ImportOders stored procedure and the usp_ImportOrderDetails stored
procedure to copy the offshore facility orders to the Sales database.
The Sales database contains a table named Orders that has more than 20 million rows.
Database Definitions
Database and Tables
The following scripts are used to create the database and its tables:
Stored Procedures
The following are the definitions of the stored procedures used in the database:
Indexes
The following indexes are part of the Sales database:
Data Import
The XML files will contain the list of items in each order. Each retailer will have its own
XML schema and will be able to use different types of encoding. Each XML schema will use
a default namespace. The default namespaces are not guaranteed to be unique.
For testing purposes, you receive an XSD file from a customer.
For testing purposes, you also create an XML schema collection named ValidateOrder.
ValidateOrder contains schemas for all of the retailers.
The new version of the application must validate the XML file, parse the data, and store the
parsed data along with the original XML file in the database. The original XML file must be
stored without losing any data.
Reported Issues
Performance Issues
You notice the following for the usp_GetOrdersAndItems stored procedure:
• The stored procedure takes a long time to complete.
• Less than two percent of the rows in the Orders table are retrieved by
usp_GetOrdersAndItems.
• A full table scan runs when the stored procedure executes.
• The amount of disk space used and the amount of time required to insert data are very
high.
You notice that the usp_GetOrdersByProduct stored procedure uses a table scan when the
stored procedure is executed.
Page Split Issues
Updates to the Orders table cause excessive page splits on the IX_Orders_ShipDate index.
Requirements
Site Requirements
Users located in North America must be able to view sales data for customers in North
America and Europe in a single report. The solution must minimize the amount of traffic over
the WAN link between the offices.
Bulk Insert Requirements
The usp_ImportOrderDetails stored procedure takes more than 10 minutes to complete. The
stored procedure runs daily. If the stored procedure fails, you must ensure that the stored
procedure restarts from the last successful set of rows.
Index Monitoring Requirements
The usage of indexes in the Sales database must be monitored continuously. Monitored data
must be maintained if a server restarts. The monitoring solution must minimize the usage of
memory resources and processing resources.
###EndCaseStudy###
You discover that the usp_GetOrdersAndItems stored procedures takes a long time to
complete while usp_AddOrder or usp_AddXMLOrder run.
You need to ensure that usp_GetOrdersAndItems completes as quickly as possible.
What should you do? (Each correct answer presents part of the solution. Choose all that
apply.)
You need to improve the performance of data warehouse queries
###BeginCaseStudy###
Case Study: 4
WingTip Toys
General Background
You are a data architect for WingTip Toys. The company uses SQL Server 2012 Enterprise
edition. SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS)
are installed on separate servers.
Data Warehouse
The company’s data warehouse initially contained less than 100 MB and 100 million rows of
data from only one data source. It now contains more than 10 TB and 10 billion rows of data,
in 25 tables, from 12 data sources.
The largest table in the data warehouse, the factOrders table, contains 5 TB of data. The
factOrders table contains three date keys: OrderDateKey InvoiceDateKey, and ShipDateKey.
The data warehouse server has 1 TB of RAM. Memory usage is currently at 20 percent.
One billion rows of data are added to the data warehouse each month. New data is copied
each night from the data sources into SQL Server staging tables, and existing records are not
updated. The largest data set is order information, which is loaded in parallel into multiple
staging tables, one for each data source. All the staging tables have the same structure and
belong to the same filegroup as the factOrders table.
The dimCustomers table stores customer information that may change over time.
Data Models
You are developing three SSAS databases, as described in the following table.
Reporting
Business users frequently generate reports in Microsoft Excel by using PowerPivot. The
PowerPivot Management Dashboard does not currently display any usage data.
Several SSRS reports exist that use the data warehouse as a source. The data warehouse
queries are aggregate queries that use the factOrders table and one or more dimension tables.
All SSRS data sources use Integrated Windows authentication.
SSRS displays a security access error message when managers run SSRS reports based on the
Operations database.
Reporting performance has become unacceptably slow.
Business Requirements
Improve the query speed of the SSRS reports.
Allow business users to create reports by using PowerPivot and Power View.
Ensure that all users other than business users can view metadata for the Customers
dimension. Ensure that business users cannot view metadata for the Customers dimension.
Technical Requirements
Modify the tables in the data warehouse to minimize aggregate query processing time.
Minimize disk storage in the data warehouse.
Ensure that all multidimensional models process data as quickly as possible.
Create a fact table named factCustomerContact in the data warehouse to store the contact
date, customer key, and communication type for each instance of customer contact.
Store the history of customer information changes in the dimCustomers table.
Move data from the staging tables into the factOrders table as quickly as possible. When
creating dimensions for the date keys in the factOrders table, minimize storage space
requirements and optimize the cube processing time.
Ensure that queries against the Sales database return the most current data in the data
warehouse.
Ensure that the SSAS model of the Finance database does not page to disk or return a
memory error as the size of the database grows.
Create an SSAS monitoring solution that tracks the following data:
• Queries answered per second
• Queries from cache direct per second
• Queries from file per second.
###EndCaseStudy###
You need to improve the performance of data warehouse queries.
What should you do?
You need to configure a hierarchy for DimProduct that meets the technical requirements
###BeginCaseStudy###
Case Study: 1
Tailspin Toys
Background
Tailspin Toys is a multinational company that manufactures toys. Tailspin Toys has offices in
five regions worldwide. The company sells toys at various retail stores. The company also
sells toys directly to consumers through a web site.
The company has the following departments:
• Sales
• Distribution
• Manufacturing
Each department has an office in each region.
The fiscal calendar of Tailspin Toys runs from June to May.
The network contains a server farm that has Microsoft SharePoint Server 2013 installed.
Existing Environment
Current Database Environment
Each department uses SharePoint team sites for internal collaboration.
All manufacturing information is stored in a relational database named Manufacturing. All
sales information is stored in a relational database named Sales.
Tailspin Toys deploys SQL Server Analysis Services (SSAS) and configures SSAS to use
tabular models. SSAS will be used for all sales reports.
Tailspin Toys deploys a SQL Server Reporting Services (SSRS) instance in SharePoint
mode.
Sales Database
A database named Sales contains two tables named FactSales and DimProduct. FactSales
contains the following columns:
• SalesID
• Total Due
• OrderDate
DimProduct contains the following columns:
• ProductID
• ProductName
• ProductCategory
• ProductSubcategory
The Sales database contains information about the products. Most of the products have a
category and a subcategory. Certain products only have a category.
A sample from DimProduct is shown in the following table.
Requirements
Security Requirements
Tailspin Toys identifies the following security requirement:
• Sales department users must be allowed to view the sales transactions from their
region only.
• Sales department users must be able to view the contents of the manufacturing
reports.
• Manufacturing department users must be able to create new manufacturing reports.
• Third-party and custom solutions must NOT be deployed to the reporting server.
• Sales department users must NOT be able to create new manufacturing reports.
Planned Reporting Implementation
The manufacturing department plans to use the SSRS instance for its reports. The
manufacturing department also plans to make its reports accessible from SharePoint. All
manufacturing reports will use an existing database named Manufacturing.
Reporting Requirements
Tailspin Toys identifies the following reporting requirements:
• All reports must contain the company logo and a header that contains the date and the
time that the report was executed.
• All reports must be created by using the SQL Server Data Tools.
Manufacturing report
You plan to create a report named Manufacturinglssues.rdl. The report has the following
requirements:
• Manufacturing department managers must be able to view product issues by product
type, manufacturing plant location, and error type.
• The manufacturing department managers must be able to change views by choosing
options from drop-down lists.
Sales reports
You plan to create a sales report named RegionalSales.rdl. The report has the following
requirements:
• Users must be able to view the report by using a web browser. By default,
subcategories and product details must be hidden when using the browser.
• Users must be able to subscribe to receive the report by email. The report must be sent
by email as a PDF attachment.
You plan to create a quarterly sales report named QuarterSales.rdl. The report must display
sales data by fiscal quarter.
Technical Requirements
Tailspin Toys identifies the following technical requirements:
• Products in the DimProduct table that do NOT have a subcategory must use the
category value as the subcategory value.
• SSRS must NOT connect to databases more frequently than once every 30 minutes.
• Sales department users must be able to use Microsoft Excel to browse tabular data.
###EndCaseStudy###
You need to configure a hierarchy for DimProduct that meets the technical requirements.
What should you do?
Which statement should you execute?
###BeginCaseStudy###
Case Study: 6
Coho Winery
Overview
You are a database developer for a company named Coho Winery. Coho Winery has an
office in London.
Coho Winery has an application that is used to process purchase orders from customers and
retailers in 10 different countries.
The application uses a web front end to process orders from the Internet. The web front end
adds orders to a database named Sales. The Sales database is managed by a server named
Server1.
An empty copy of the Sales database is created on a server named Server2 in the London
office. The database will store sales data for customers in Europe.
A new version of the application is being developed. In the new version, orders will be placed
either by using the existing web front end or by loading an XML file.
Once a week, you receive two files that contain the purchase orders and the order details of
orders fromoffshore facilities.
You run the usp_ImportOders stored procedure and the usp_ImportOrderDetails stored
procedure to copy the offshore facility orders to the Sales database.
The Sales database contains a table named Orders that has more than 20 million rows.
Database Definitions
Database and Tables
The following scripts are used to create the database and its tables:
Stored Procedures
The following are the definitions of the stored procedures used in the database:
Indexes
The following indexes are part of the Sales database:
Data Import
The XML files will contain the list of items in each order. Each retailer will have its own
XML schema and will be able to use different types of encoding. Each XML schema will use
a default namespace. The default namespaces are not guaranteed to be unique.
For testing purposes, you receive an XSD file from a customer.
For testing purposes, you also create an XML schema collection named ValidateOrder.
ValidateOrder contains schemas for all of the retailers.
The new version of the application must validate the XML file, parse the data, and store the
parsed data along with the original XML file in the database. The original XML file must be
stored without losing any data.
Reported Issues
Performance Issues
You notice the following for the usp_GetOrdersAndItems stored procedure:
• The stored procedure takes a long time to complete.
• Less than two percent of the rows in the Orders table are retrieved by
usp_GetOrdersAndItems.
• A full table scan runs when the stored procedure executes.
• The amount of disk space used and the amount of time required to insert data are very
high.
You notice that the usp_GetOrdersByProduct stored procedure uses a table scan when the
stored procedure is executed.
Page Split Issues
Updates to the Orders table cause excessive page splits on the IX_Orders_ShipDate index.
Requirements
Site Requirements
Users located in North America must be able to view sales data for customers in North
America and Europe in a single report. The solution must minimize the amount of traffic over
the WAN link between the offices.
Bulk Insert Requirements
The usp_ImportOrderDetails stored procedure takes more than 10 minutes to complete. The
stored procedure runs daily. If the stored procedure fails, you must ensure that the stored
procedure restarts from the last successful set of rows.
Index Monitoring Requirements
The usage of indexes in the Sales database must be monitored continuously. Monitored data
must be maintained if a server restarts. The monitoring solution must minimize the usage of
memory resources and processing resources.
###EndCaseStudy###
You need to modify the Orders table to store the XML data used by the retailers.
Which statement should you execute?
Which type of table should you create?
###BeginCaseStudy###
Case Study: 4
WingTip Toys
General Background
You are a data architect for WingTip Toys. The company uses SQL Server 2012 Enterprise
edition. SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS)
are installed on separate servers.
Data Warehouse
The company’s data warehouse initially contained less than 100 MB and 100 million rows of
data from only one data source. It now contains more than 10 TB and 10 billion rows of data,
in 25 tables, from 12 data sources.
The largest table in the data warehouse, the factOrders table, contains 5 TB of data. The
factOrders table contains three date keys: OrderDateKey InvoiceDateKey, and ShipDateKey.
The data warehouse server has 1 TB of RAM. Memory usage is currently at 20 percent.
One billion rows of data are added to the data warehouse each month. New data is copied
each night from the data sources into SQL Server staging tables, and existing records are not
updated. The largest data set is order information, which is loaded in parallel into multiple
staging tables, one for each data source. All the staging tables have the same structure and
belong to the same filegroup as the factOrders table.
The dimCustomers table stores customer information that may change over time.
Data Models
You are developing three SSAS databases, as described in the following table.
Reporting
Business users frequently generate reports in Microsoft Excel by using PowerPivot. The
PowerPivot Management Dashboard does not currently display any usage data.
Several SSRS reports exist that use the data warehouse as a source. The data warehouse
queries are aggregate queries that use the factOrders table and one or more dimension tables.
All SSRS data sources use Integrated Windows authentication.
SSRS displays a security access error message when managers run SSRS reports based on the
Operations database.
Reporting performance has become unacceptably slow.
Business Requirements
Improve the query speed of the SSRS reports.
Allow business users to create reports by using PowerPivot and Power View.
Ensure that all users other than business users can view metadata for the Customers
dimension. Ensure that business users cannot view metadata for the Customers dimension.
Technical Requirements
Modify the tables in the data warehouse to minimize aggregate query processing time.
Minimize disk storage in the data warehouse.
Ensure that all multidimensional models process data as quickly as possible.
Create a fact table named factCustomerContact in the data warehouse to store the contact
date, customer key, and communication type for each instance of customer contact.
Store the history of customer information changes in the dimCustomers table.
Move data from the staging tables into the factOrders table as quickly as possible. When
creating dimensions for the date keys in the factOrders table, minimize storage space
requirements and optimize the cube processing time.
Ensure that queries against the Sales database return the most current data in the data
warehouse.
Ensure that the SSAS model of the Finance database does not page to disk or return a
memory error as the size of the database grows.
Create an SSAS monitoring solution that tracks the following data:
• Queries answered per second
• Queries from cache direct per second
• Queries from file per second.
###EndCaseStudy###
You need to create the factCustomerContact table.
Which type of table should you create?