You need to design a cube partitioning strategy to be implemented as the cube size increases
###BeginCaseStudy###
Case Study: 3
Data Architect
General Background
You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.
You have been tasked with designing a data warehouse that uses the company’s financial
database as the data source. From the data warehouse, you will develop a cube to simplify the
creation of accurate financial reports and related data analysis.
Background
You will utilize the following three servers:
• ServerA runs SQL Server Database Engine. ServerA is a production
server and also hosts the financial database.
• ServerB runs SQL Server Database Engine, SQL Server Analysis
Services (SSAS) in multidimensional mode, SQL Server Integration Services
(SSIS), and SQL Server Reporting Services (SSRS).
• ServerC runs SSAS in multidimensional mode.
• The financial database is used by a third-party application and the table
structures cannot be modified.
The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)
The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is
partitioned by month. It contains only the last five years of financial data. The CouponUsed,
OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is
less than 10 MB and has only one partition.
The SecurityFilter table specifies the sites to which each user has access.
Business Requirements
The extract, transform, load (ETL) process that updates the data warehouse must run daily
between 8:00 P.M. and 5:00 A.M. so that it doesn’t impact the performance of ServerA
during business hours. The cube data must be available by 8:00 A.M.
The cube must meet the following business requirements:
• Ensure that reports display the most current information available.
• Allow fast access to support ad-hoc reports and data analysis.
Business Analysts will access the data warehouse tables directly, and will access the cube by
using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint
Services. These tools will access only the cube and not the data warehouse.
Technical Requirements
SSIS solutions must be deployed by using the project deployment model.
You must develop the data warehouse and store the cube on ServerB. When the number of
concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to
ServerC and meet following requirements:
• Maintain copies of the cube on ServerB and ServerC.
• Ensure that the cube is always available on both servers.
• Minimize query response time.
The cube must meet the following technical requirements:
• The cube must be processed by using an SSIS package.
• The cube must contain the prior day’s data up to 8:00 P.M. but does not
need to contain same-day data.
• The cube must include aggregation designs when it is initially
deployed.
• A product dimension must be added to the cube. It will contain a
hierarchy comprised of product name and product color.
Because of the large size of the SalesTransactions table, the cube must store only
aggregations—the data warehouse must store the detailed data. Both the data warehouse and
the cube must minimize disk space usage.
As the cube size increases, you must plan to scale out to additional servers to minimize
processing time.
The data warehouse must use a star schema design. The table design must be as denormalized
as possible. The history of changes to the Customer table must be tracked in the data
warehouse. The cube must use the data warehouse as its only data source.
Security settings on the data warehouse and the cube must ensure that queries against the
SalesTransactions table return only records from the sites to which the current user has
access.
The ETL process must consist of multiple SSIS packages developed in a single project by
using the least amount of effort. The SSIS packages must use a database connection string
that is set at execution time to connect to the financial database. All data in the data
warehouse must be loaded by the SSIS packages.
You must create a Package Activity report that meets the following requirements:
• Track SSIS package execution data (including package name, status,
start time, end time, duration, and rows processed).
• Use the least amount of development effort.
###EndCaseStudy###
You need to design a cube partitioning strategy to be implemented as the cube size increases.
What should you do?
You need to choose the appropriate key to use when designing a dimension table based on the Customer table
###BeginCaseStudy###
Case Study: 3
Data Architect
General Background
You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.
You have been tasked with designing a data warehouse that uses the company’s financial
database as the data source. From the data warehouse, you will develop a cube to simplify the
creation of accurate financial reports and related data analysis.
Background
You will utilize the following three servers:
• ServerA runs SQL Server Database Engine. ServerA is a production
server and also hosts the financial database.
• ServerB runs SQL Server Database Engine, SQL Server Analysis
Services (SSAS) in multidimensional mode, SQL Server Integration Services
(SSIS), and SQL Server Reporting Services (SSRS).
• ServerC runs SSAS in multidimensional mode.
• The financial database is used by a third-party application and the table
structures cannot be modified.
The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)
The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is
partitioned by month. It contains only the last five years of financial data. The CouponUsed,
OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is
less than 10 MB and has only one partition.
The SecurityFilter table specifies the sites to which each user has access.
Business Requirements
The extract, transform, load (ETL) process that updates the data warehouse must run daily
between 8:00 P.M. and 5:00 A.M. so that it doesn’t impact the performance of ServerA
during business hours. The cube data must be available by 8:00 A.M.
The cube must meet the following business requirements:
• Ensure that reports display the most current information available.
• Allow fast access to support ad-hoc reports and data analysis.
Business Analysts will access the data warehouse tables directly, and will access the cube by
using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint
Services. These tools will access only the cube and not the data warehouse.
Technical Requirements
SSIS solutions must be deployed by using the project deployment model.
You must develop the data warehouse and store the cube on ServerB. When the number of
concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to
ServerC and meet following requirements:
• Maintain copies of the cube on ServerB and ServerC.
• Ensure that the cube is always available on both servers.
• Minimize query response time.
The cube must meet the following technical requirements:
• The cube must be processed by using an SSIS package.
• The cube must contain the prior day’s data up to 8:00 P.M. but does not
need to contain same-day data.
• The cube must include aggregation designs when it is initially
deployed.
• A product dimension must be added to the cube. It will contain a
hierarchy comprised of product name and product color.
Because of the large size of the SalesTransactions table, the cube must store only
aggregations—the data warehouse must store the detailed data. Both the data warehouse and
the cube must minimize disk space usage.
As the cube size increases, you must plan to scale out to additional servers to minimize
processing time.
The data warehouse must use a star schema design. The table design must be as denormalized
as possible. The history of changes to the Customer table must be tracked in the data
warehouse. The cube must use the data warehouse as its only data source.
Security settings on the data warehouse and the cube must ensure that queries against the
SalesTransactions table return only records from the sites to which the current user has
access.
The ETL process must consist of multiple SSIS packages developed in a single project by
using the least amount of effort. The SSIS packages must use a database connection string
that is set at execution time to connect to the financial database. All data in the data
warehouse must be loaded by the SSIS packages.
You must create a Package Activity report that meets the following requirements:
• Track SSIS package execution data (including package name, status,
start time, end time, duration, and rows processed).
• Use the least amount of development effort.
###EndCaseStudy###
You need to choose the appropriate key to use when designing a dimension table based on
the Customer table.
What should you do?
You need to implement the product dimension
###BeginCaseStudy###
Case Study: 3
Data Architect
General Background
You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.
You have been tasked with designing a data warehouse that uses the company’s financial
database as the data source. From the data warehouse, you will develop a cube to simplify the
creation of accurate financial reports and related data analysis.
Background
You will utilize the following three servers:
• ServerA runs SQL Server Database Engine. ServerA is a production
server and also hosts the financial database.
• ServerB runs SQL Server Database Engine, SQL Server Analysis
Services (SSAS) in multidimensional mode, SQL Server Integration Services
(SSIS), and SQL Server Reporting Services (SSRS).
• ServerC runs SSAS in multidimensional mode.
• The financial database is used by a third-party application and the table
structures cannot be modified.
The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)
The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is
partitioned by month. It contains only the last five years of financial data. The CouponUsed,
OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is
less than 10 MB and has only one partition.
The SecurityFilter table specifies the sites to which each user has access.
Business Requirements
The extract, transform, load (ETL) process that updates the data warehouse must run daily
between 8:00 P.M. and 5:00 A.M. so that it doesn’t impact the performance of ServerA
during business hours. The cube data must be available by 8:00 A.M.
The cube must meet the following business requirements:
• Ensure that reports display the most current information available.
• Allow fast access to support ad-hoc reports and data analysis.
Business Analysts will access the data warehouse tables directly, and will access the cube by
using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint
Services. These tools will access only the cube and not the data warehouse.
Technical Requirements
SSIS solutions must be deployed by using the project deployment model.
You must develop the data warehouse and store the cube on ServerB. When the number of
concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to
ServerC and meet following requirements:
• Maintain copies of the cube on ServerB and ServerC.
• Ensure that the cube is always available on both servers.
• Minimize query response time.
The cube must meet the following technical requirements:
• The cube must be processed by using an SSIS package.
• The cube must contain the prior day’s data up to 8:00 P.M. but does not
need to contain same-day data.
• The cube must include aggregation designs when it is initially
deployed.
• A product dimension must be added to the cube. It will contain a
hierarchy comprised of product name and product color.
Because of the large size of the SalesTransactions table, the cube must store only
aggregations—the data warehouse must store the detailed data. Both the data warehouse and
the cube must minimize disk space usage.
As the cube size increases, you must plan to scale out to additional servers to minimize
processing time.
The data warehouse must use a star schema design. The table design must be as denormalized
as possible. The history of changes to the Customer table must be tracked in the data
warehouse. The cube must use the data warehouse as its only data source.
Security settings on the data warehouse and the cube must ensure that queries against the
SalesTransactions table return only records from the sites to which the current user has
access.
The ETL process must consist of multiple SSIS packages developed in a single project by
using the least amount of effort. The SSIS packages must use a database connection string
that is set at execution time to connect to the financial database. All data in the data
warehouse must be loaded by the SSIS packages.
You must create a Package Activity report that meets the following requirements:
• Track SSIS package execution data (including package name, status,
start time, end time, duration, and rows processed).
• Use the least amount of development effort.
###EndCaseStudy###
You need to implement the product dimension.
What should you do?
You need to scale out SSAS
###BeginCaseStudy###
Case Study: 3
Data Architect
General Background
You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.
You have been tasked with designing a data warehouse that uses the company’s financial
database as the data source. From the data warehouse, you will develop a cube to simplify the
creation of accurate financial reports and related data analysis.
Background
You will utilize the following three servers:
• ServerA runs SQL Server Database Engine. ServerA is a production
server and also hosts the financial database.
• ServerB runs SQL Server Database Engine, SQL Server Analysis
Services (SSAS) in multidimensional mode, SQL Server Integration Services
(SSIS), and SQL Server Reporting Services (SSRS).
• ServerC runs SSAS in multidimensional mode.
• The financial database is used by a third-party application and the table
structures cannot be modified.
The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)
The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is
partitioned by month. It contains only the last five years of financial data. The CouponUsed,
OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is
less than 10 MB and has only one partition.
The SecurityFilter table specifies the sites to which each user has access.
Business Requirements
The extract, transform, load (ETL) process that updates the data warehouse must run daily
between 8:00 P.M. and 5:00 A.M. so that it doesn’t impact the performance of ServerA
during business hours. The cube data must be available by 8:00 A.M.
The cube must meet the following business requirements:
• Ensure that reports display the most current information available.
• Allow fast access to support ad-hoc reports and data analysis.
Business Analysts will access the data warehouse tables directly, and will access the cube by
using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint
Services. These tools will access only the cube and not the data warehouse.
Technical Requirements
SSIS solutions must be deployed by using the project deployment model.
You must develop the data warehouse and store the cube on ServerB. When the number of
concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to
ServerC and meet following requirements:
• Maintain copies of the cube on ServerB and ServerC.
• Ensure that the cube is always available on both servers.
• Minimize query response time.
The cube must meet the following technical requirements:
• The cube must be processed by using an SSIS package.
• The cube must contain the prior day’s data up to 8:00 P.M. but does not
need to contain same-day data.
• The cube must include aggregation designs when it is initially
deployed.
• A product dimension must be added to the cube. It will contain a
hierarchy comprised of product name and product color.
Because of the large size of the SalesTransactions table, the cube must store only
aggregations—the data warehouse must store the detailed data. Both the data warehouse and
the cube must minimize disk space usage.
As the cube size increases, you must plan to scale out to additional servers to minimize
processing time.
The data warehouse must use a star schema design. The table design must be as denormalized
as possible. The history of changes to the Customer table must be tracked in the data
warehouse. The cube must use the data warehouse as its only data source.
Security settings on the data warehouse and the cube must ensure that queries against the
SalesTransactions table return only records from the sites to which the current user has
access.
The ETL process must consist of multiple SSIS packages developed in a single project by
using the least amount of effort. The SSIS packages must use a database connection string
that is set at execution time to connect to the financial database. All data in the data
warehouse must be loaded by the SSIS packages.
You must create a Package Activity report that meets the following requirements:
• Track SSIS package execution data (including package name, status,
start time, end time, duration, and rows processed).
• Use the least amount of development effort.
###EndCaseStudy###
You need to scale out SSAS.
What should you do?
You need to implement security in the cube to limit the sites visible to each user
###BeginCaseStudy###
Case Study: 3
Data Architect
General Background
You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.
You have been tasked with designing a data warehouse that uses the company’s financial
database as the data source. From the data warehouse, you will develop a cube to simplify the
creation of accurate financial reports and related data analysis.
Background
You will utilize the following three servers:
• ServerA runs SQL Server Database Engine. ServerA is a production
server and also hosts the financial database.
• ServerB runs SQL Server Database Engine, SQL Server Analysis
Services (SSAS) in multidimensional mode, SQL Server Integration Services
(SSIS), and SQL Server Reporting Services (SSRS).
• ServerC runs SSAS in multidimensional mode.
• The financial database is used by a third-party application and the table
structures cannot be modified.
The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)
The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is
partitioned by month. It contains only the last five years of financial data. The CouponUsed,
OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is
less than 10 MB and has only one partition.
The SecurityFilter table specifies the sites to which each user has access.
Business Requirements
The extract, transform, load (ETL) process that updates the data warehouse must run daily
between 8:00 P.M. and 5:00 A.M. so that it doesn’t impact the performance of ServerA
during business hours. The cube data must be available by 8:00 A.M.
The cube must meet the following business requirements:
• Ensure that reports display the most current information available.
• Allow fast access to support ad-hoc reports and data analysis.
Business Analysts will access the data warehouse tables directly, and will access the cube by
using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint
Services. These tools will access only the cube and not the data warehouse.
Technical Requirements
SSIS solutions must be deployed by using the project deployment model.
You must develop the data warehouse and store the cube on ServerB. When the number of
concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to
ServerC and meet following requirements:
• Maintain copies of the cube on ServerB and ServerC.
• Ensure that the cube is always available on both servers.
• Minimize query response time.
The cube must meet the following technical requirements:
• The cube must be processed by using an SSIS package.
• The cube must contain the prior day’s data up to 8:00 P.M. but does not
need to contain same-day data.
• The cube must include aggregation designs when it is initially
deployed.
• A product dimension must be added to the cube. It will contain a
hierarchy comprised of product name and product color.
Because of the large size of the SalesTransactions table, the cube must store only
aggregations—the data warehouse must store the detailed data. Both the data warehouse and
the cube must minimize disk space usage.
As the cube size increases, you must plan to scale out to additional servers to minimize
processing time.
The data warehouse must use a star schema design. The table design must be as denormalized
as possible. The history of changes to the Customer table must be tracked in the data
warehouse. The cube must use the data warehouse as its only data source.
Security settings on the data warehouse and the cube must ensure that queries against the
SalesTransactions table return only records from the sites to which the current user has
access.
The ETL process must consist of multiple SSIS packages developed in a single project by
using the least amount of effort. The SSIS packages must use a database connection string
that is set at execution time to connect to the financial database. All data in the data
warehouse must be loaded by the SSIS packages.
You must create a Package Activity report that meets the following requirements:
• Track SSIS package execution data (including package name, status,
start time, end time, duration, and rows processed).
• Use the least amount of development effort.
###EndCaseStudy###
You need to implement security in the cube to limit the sites visible to each user.
What should you do?
Which settings should you choose?
###BeginCaseStudy###
Case Study: 3
Data Architect
General Background
You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.
You have been tasked with designing a data warehouse that uses the company’s financial
database as the data source. From the data warehouse, you will develop a cube to simplify the
creation of accurate financial reports and related data analysis.
Background
You will utilize the following three servers:
• ServerA runs SQL Server Database Engine. ServerA is a production
server and also hosts the financial database.
• ServerB runs SQL Server Database Engine, SQL Server Analysis
Services (SSAS) in multidimensional mode, SQL Server Integration Services
(SSIS), and SQL Server Reporting Services (SSRS).
• ServerC runs SSAS in multidimensional mode.
• The financial database is used by a third-party application and the table
structures cannot be modified.
The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)
The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is
partitioned by month. It contains only the last five years of financial data. The CouponUsed,
OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is
less than 10 MB and has only one partition.
The SecurityFilter table specifies the sites to which each user has access.
Business Requirements
The extract, transform, load (ETL) process that updates the data warehouse must run daily
between 8:00 P.M. and 5:00 A.M. so that it doesn’t impact the performance of ServerA
during business hours. The cube data must be available by 8:00 A.M.
The cube must meet the following business requirements:
• Ensure that reports display the most current information available.
• Allow fast access to support ad-hoc reports and data analysis.
Business Analysts will access the data warehouse tables directly, and will access the cube by
using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint
Services. These tools will access only the cube and not the data warehouse.
Technical Requirements
SSIS solutions must be deployed by using the project deployment model.
You must develop the data warehouse and store the cube on ServerB. When the number of
concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to
ServerC and meet following requirements:
• Maintain copies of the cube on ServerB and ServerC.
• Ensure that the cube is always available on both servers.
• Minimize query response time.
The cube must meet the following technical requirements:
• The cube must be processed by using an SSIS package.
• The cube must contain the prior day’s data up to 8:00 P.M. but does not
need to contain same-day data.
• The cube must include aggregation designs when it is initially
deployed.
• A product dimension must be added to the cube. It will contain a
hierarchy comprised of product name and product color.
Because of the large size of the SalesTransactions table, the cube must store only
aggregations—the data warehouse must store the detailed data. Both the data warehouse and
the cube must minimize disk space usage.
As the cube size increases, you must plan to scale out to additional servers to minimize
processing time.
The data warehouse must use a star schema design. The table design must be as denormalized
as possible. The history of changes to the Customer table must be tracked in the data
warehouse. The cube must use the data warehouse as its only data source.
Security settings on the data warehouse and the cube must ensure that queries against the
SalesTransactions table return only records from the sites to which the current user has
access.
The ETL process must consist of multiple SSIS packages developed in a single project by
using the least amount of effort. The SSIS packages must use a database connection string
that is set at execution time to connect to the financial database. All data in the data
warehouse must be loaded by the SSIS packages.
You must create a Package Activity report that meets the following requirements:
• Track SSIS package execution data (including package name, status,
start time, end time, duration, and rows processed).
• Use the least amount of development effort.
###EndCaseStudy###
You need to select the appropriate storage settings for the cube.
Which settings should you choose?
You need to configure a parameter for the database connection string
###BeginCaseStudy###
Case Study: 3
Data Architect
General Background
You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.
You have been tasked with designing a data warehouse that uses the company’s financial
database as the data source. From the data warehouse, you will develop a cube to simplify the
creation of accurate financial reports and related data analysis.
Background
You will utilize the following three servers:
• ServerA runs SQL Server Database Engine. ServerA is a production
server and also hosts the financial database.
• ServerB runs SQL Server Database Engine, SQL Server Analysis
Services (SSAS) in multidimensional mode, SQL Server Integration Services
(SSIS), and SQL Server Reporting Services (SSRS).
• ServerC runs SSAS in multidimensional mode.
• The financial database is used by a third-party application and the table
structures cannot be modified.
The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)
The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is
partitioned by month. It contains only the last five years of financial data. The CouponUsed,
OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is
less than 10 MB and has only one partition.
The SecurityFilter table specifies the sites to which each user has access.
Business Requirements
The extract, transform, load (ETL) process that updates the data warehouse must run daily
between 8:00 P.M. and 5:00 A.M. so that it doesn’t impact the performance of ServerA
during business hours. The cube data must be available by 8:00 A.M.
The cube must meet the following business requirements:
• Ensure that reports display the most current information available.
• Allow fast access to support ad-hoc reports and data analysis.
Business Analysts will access the data warehouse tables directly, and will access the cube by
using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint
Services. These tools will access only the cube and not the data warehouse.
Technical Requirements
SSIS solutions must be deployed by using the project deployment model.
You must develop the data warehouse and store the cube on ServerB. When the number of
concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to
ServerC and meet following requirements:
• Maintain copies of the cube on ServerB and ServerC.
• Ensure that the cube is always available on both servers.
• Minimize query response time.
The cube must meet the following technical requirements:
• The cube must be processed by using an SSIS package.
• The cube must contain the prior day’s data up to 8:00 P.M. but does not
need to contain same-day data.
• The cube must include aggregation designs when it is initially
deployed.
• A product dimension must be added to the cube. It will contain a
hierarchy comprised of product name and product color.
Because of the large size of the SalesTransactions table, the cube must store only
aggregations—the data warehouse must store the detailed data. Both the data warehouse and
the cube must minimize disk space usage.
As the cube size increases, you must plan to scale out to additional servers to minimize
processing time.
The data warehouse must use a star schema design. The table design must be as denormalized
as possible. The history of changes to the Customer table must be tracked in the data
warehouse. The cube must use the data warehouse as its only data source.
Security settings on the data warehouse and the cube must ensure that queries against the
SalesTransactions table return only records from the sites to which the current user has
access.
The ETL process must consist of multiple SSIS packages developed in a single project by
using the least amount of effort. The SSIS packages must use a database connection string
that is set at execution time to connect to the financial database. All data in the data
warehouse must be loaded by the SSIS packages.
You must create a Package Activity report that meets the following requirements:
• Track SSIS package execution data (including package name, status,
start time, end time, duration, and rows processed).
• Use the least amount of development effort.
###EndCaseStudy###
You need to configure a parameter for the database connection string.
What should you do?
You need to restrict access to data in the tables in the data warehouse
###BeginCaseStudy###
Case Study: 3
Data Architect
General Background
You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.
You have been tasked with designing a data warehouse that uses the company’s financial
database as the data source. From the data warehouse, you will develop a cube to simplify the
creation of accurate financial reports and related data analysis.
Background
You will utilize the following three servers:
• ServerA runs SQL Server Database Engine. ServerA is a production
server and also hosts the financial database.
• ServerB runs SQL Server Database Engine, SQL Server Analysis
Services (SSAS) in multidimensional mode, SQL Server Integration Services
(SSIS), and SQL Server Reporting Services (SSRS).
• ServerC runs SSAS in multidimensional mode.
• The financial database is used by a third-party application and the table
structures cannot be modified.
The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)
The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is
partitioned by month. It contains only the last five years of financial data. The CouponUsed,
OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is
less than 10 MB and has only one partition.
The SecurityFilter table specifies the sites to which each user has access.
Business Requirements
The extract, transform, load (ETL) process that updates the data warehouse must run daily
between 8:00 P.M. and 5:00 A.M. so that it doesn’t impact the performance of ServerA
during business hours. The cube data must be available by 8:00 A.M.
The cube must meet the following business requirements:
• Ensure that reports display the most current information available.
• Allow fast access to support ad-hoc reports and data analysis.
Business Analysts will access the data warehouse tables directly, and will access the cube by
using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint
Services. These tools will access only the cube and not the data warehouse.
Technical Requirements
SSIS solutions must be deployed by using the project deployment model.
You must develop the data warehouse and store the cube on ServerB. When the number of
concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to
ServerC and meet following requirements:
• Maintain copies of the cube on ServerB and ServerC.
• Ensure that the cube is always available on both servers.
• Minimize query response time.
The cube must meet the following technical requirements:
• The cube must be processed by using an SSIS package.
• The cube must contain the prior day’s data up to 8:00 P.M. but does not
need to contain same-day data.
• The cube must include aggregation designs when it is initially
deployed.
• A product dimension must be added to the cube. It will contain a
hierarchy comprised of product name and product color.
Because of the large size of the SalesTransactions table, the cube must store only
aggregations—the data warehouse must store the detailed data. Both the data warehouse and
the cube must minimize disk space usage.
As the cube size increases, you must plan to scale out to additional servers to minimize
processing time.
The data warehouse must use a star schema design. The table design must be as denormalized
as possible. The history of changes to the Customer table must be tracked in the data
warehouse. The cube must use the data warehouse as its only data source.
Security settings on the data warehouse and the cube must ensure that queries against the
SalesTransactions table return only records from the sites to which the current user has
access.
The ETL process must consist of multiple SSIS packages developed in a single project by
using the least amount of effort. The SSIS packages must use a database connection string
that is set at execution time to connect to the financial database. All data in the data
warehouse must be loaded by the SSIS packages.
You must create a Package Activity report that meets the following requirements:
• Track SSIS package execution data (including package name, status,
start time, end time, duration, and rows processed).
• Use the least amount of development effort.
###EndCaseStudy###
You need to restrict access to data in the tables in the data warehouse.
What should you do?
You need to ensure that managers can successfully run reports
###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 managers can successfully run reports.
What should you do?
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?