Which two of the following options should you configure?
Topic 2, Westbridge Industries
General Overview
You have been hired as a Data Architect by a company named Westbridge Industries.
The company has recently hired a team of Business Analysts to analyze and report on sales and
financial data trends. You have been hired to design and implement a data warehouse and cube
to aid the Business Analysts in their reporting and data analysis.
Existing Environment – SQL Servers
DB1: A Windows Server 2012 server named DB1 runs SQL Server 2012 Enterprise Edition.
DB1 hosts the following SQL Server roles:
SQL Server Database Engine (single default instance)
DB2: A Windows Server 2012 server named DB2 runs SQL Server 2012 Enterprise Edition. This
server will be used to host the data warehouse and the cube.
DB2 hosts the following SQL Server roles:
SQL Server Database Engine (single default instance).
SQL Server Reporting Services (SSRS)
SQL Server Integration Services (SSIS)
SQL Server Analysis Services (SSAS) in multidimensional mode
DB3: A Windows Server 2012 server named DB3 runs SQL Server 2012 Enterprise Edition.
This server will be used as a scale-out server when the number of SSAS users on DB2 reaches a
specified limit.
DB3 hosts the following SQL Server roles:
SQL Server Analysis Services (SSAS) in multidimensional mode
Existing Environment – Databases
All production databases are hosted on DB1. The main Sales and Financial database is named
CorpDB. This is the database that will serve as the data source for the data warehouse.
The CorpDB database has one large table and several small tables. The large table is named
Orders and contains information about every order processed by the company in the last 5 years.
The Orders table is nearly 1 TB in size and is partitioned by month. The Orders table is anticipated
to expand to 3 TB.
CorpDB includes the following smaller tables: ProductName, ProductType, Customers,
SalesRegion, SalesPerson and SalesRegionFilter. Each of these tables consists of a single
partition and is less than 10MB in size.
Access to database information will be restricted to company users and sales people based on
Sales Region. The SalesRegionFilter table lists the regions that each user is permitted to access.
Cube Requirements
The company has identified the following requirements for the cube:
An SSIS package must be used to process the cube.
The time taken to respond to queries must be minimized.
Ad-hoc reporting and analysis must be possible.
Disk space used by the cube must be minimized.
The cube must contain data from the prior day up to 7:00 P.M.
To ensure redundancy and availability, a copy of the cube must exist and be available on both
DB2 and DB3.
A product dimension containing a hierarchy of product name and product type will be added to the
cube.
The cube will store only aggregations of the Orders table due to the large size of the table. The
detailed Orders data will be stored in the data warehouse.
The data warehouse will be the only data source for the cube.
When the cube is initially deployed, it should include aggregation designs.
The cube data must be available for reports when the office opens at 9:00 A.M.
The most current information available must be returned in the reports.
Microsoft SharePoint Server 2010 PerformancePoint Services, Microsoft Excel and SQL Server
Reporting Services (SSRS) will be used to access the cube.
Date Warehouse Requirements
The company has identified the following requirements for the data warehouse:
Disk space used by the data warehouse must be minimized.
The table design of the data warehouse should be as denormalized as possible.
The schema of the data warehouse must be a star design.
The data warehouse tables will be directly accessed by the Business Analysts.
Security must be configured to ensure that Order queries return data relevant to only the sales
regions that the users are permitted to access.
ETL Process Requirements
The company has identified the following requirements for the Extract, Transform and Load (ETL)
process:
The data warehouse will be updated by an ETL process that runs overnight. The ETL process
must start no earlier than 7:00 P.M. and finish no later than 4:00 A.M. the following morning.
A single project containing multiple SSIS packages will be developed and used to load data in the
data warehouse. A database connection string that is set at execution time will be used by the
SSIS packages to connect to the CorpDB database. Development effort used to create the SSIS
packages must be minimized.
*******************************
You need to configure the cube storage settings to meet the data availability requirements.
Which two of the following options should you configure? (Choose two)
How can you restrict access?
Topic 2, Westbridge Industries
General Overview
You have been hired as a Data Architect by a company named Westbridge Industries.
The company has recently hired a team of Business Analysts to analyze and report on sales and
financial data trends. You have been hired to design and implement a data warehouse and cube
to aid the Business Analysts in their reporting and data analysis.
Existing Environment – SQL Servers
DB1: A Windows Server 2012 server named DB1 runs SQL Server 2012 Enterprise Edition.
DB1 hosts the following SQL Server roles:
SQL Server Database Engine (single default instance)
DB2: A Windows Server 2012 server named DB2 runs SQL Server 2012 Enterprise Edition. This
server will be used to host the data warehouse and the cube.
DB2 hosts the following SQL Server roles:
SQL Server Database Engine (single default instance).
SQL Server Reporting Services (SSRS)
SQL Server Integration Services (SSIS)
SQL Server Analysis Services (SSAS) in multidimensional mode
DB3: A Windows Server 2012 server named DB3 runs SQL Server 2012 Enterprise Edition.
This server will be used as a scale-out server when the number of SSAS users on DB2 reaches a
specified limit.
DB3 hosts the following SQL Server roles:
SQL Server Analysis Services (SSAS) in multidimensional mode
Existing Environment – Databases
All production databases are hosted on DB1. The main Sales and Financial database is named
CorpDB. This is the database that will serve as the data source for the data warehouse.
The CorpDB database has one large table and several small tables. The large table is named
Orders and contains information about every order processed by the company in the last 5 years.
The Orders table is nearly 1 TB in size and is partitioned by month. The Orders table is anticipated
to expand to 3 TB.
CorpDB includes the following smaller tables: ProductName, ProductType, Customers,
SalesRegion, SalesPerson and SalesRegionFilter. Each of these tables consists of a single
partition and is less than 10MB in size.
Access to database information will be restricted to company users and sales people based on
Sales Region. The SalesRegionFilter table lists the regions that each user is permitted to access.
Cube Requirements
The company has identified the following requirements for the cube:
An SSIS package must be used to process the cube.
The time taken to respond to queries must be minimized.
Ad-hoc reporting and analysis must be possible.
Disk space used by the cube must be minimized.
The cube must contain data from the prior day up to 7:00 P.M.
To ensure redundancy and availability, a copy of the cube must exist and be available on both
DB2 and DB3.
A product dimension containing a hierarchy of product name and product type will be added to the
cube.
The cube will store only aggregations of the Orders table due to the large size of the table. The
detailed Orders data will be stored in the data warehouse.
The data warehouse will be the only data source for the cube.
When the cube is initially deployed, it should include aggregation designs.
The cube data must be available for reports when the office opens at 9:00 A.M.
The most current information available must be returned in the reports.
Microsoft SharePoint Server 2010 PerformancePoint Services, Microsoft Excel and SQL Server
Reporting Services (SSRS) will be used to access the cube.
Date Warehouse Requirements
The company has identified the following requirements for the data warehouse:
Disk space used by the data warehouse must be minimized.
The table design of the data warehouse should be as denormalized as possible.
The schema of the data warehouse must be a star design.
The data warehouse tables will be directly accessed by the Business Analysts.
Security must be configured to ensure that Order queries return data relevant to only the sales
regions that the users are permitted to access.
ETL Process Requirements
The company has identified the following requirements for the Extract, Transform and Load (ETL)
process:
The data warehouse will be updated by an ETL process that runs overnight. The ETL process
must start no earlier than 7:00 P.M. and finish no later than 4:00 A.M. the following morning.
A single project containing multiple SSIS packages will be developed and used to load data in the
data warehouse. A database connection string that is set at execution time will be used by the
SSIS packages to connect to the CorpDB database. Development effort used to create the SSIS
packages must be minimized.
*******************************
Access to the data contained in the data warehouse tables needs to be restricted.
How can you restrict access?
Which two of the following actions should you perform?
Topic 2, Westbridge Industries
General Overview
You have been hired as a Data Architect by a company named Westbridge Industries.
The company has recently hired a team of Business Analysts to analyze and report on sales and
financial data trends. You have been hired to design and implement a data warehouse and cube
to aid the Business Analysts in their reporting and data analysis.
Existing Environment – SQL Servers
DB1: A Windows Server 2012 server named DB1 runs SQL Server 2012 Enterprise Edition.
DB1 hosts the following SQL Server roles:
SQL Server Database Engine (single default instance)
DB2: A Windows Server 2012 server named DB2 runs SQL Server 2012 Enterprise Edition. This
server will be used to host the data warehouse and the cube.
DB2 hosts the following SQL Server roles:
SQL Server Database Engine (single default instance).
SQL Server Reporting Services (SSRS)
SQL Server Integration Services (SSIS)
SQL Server Analysis Services (SSAS) in multidimensional mode
DB3: A Windows Server 2012 server named DB3 runs SQL Server 2012 Enterprise Edition.
This server will be used as a scale-out server when the number of SSAS users on DB2 reaches a
specified limit.
DB3 hosts the following SQL Server roles:
SQL Server Analysis Services (SSAS) in multidimensional mode
Existing Environment – Databases
All production databases are hosted on DB1. The main Sales and Financial database is named
CorpDB. This is the database that will serve as the data source for the data warehouse.
The CorpDB database has one large table and several small tables. The large table is named
Orders and contains information about every order processed by the company in the last 5 years.
The Orders table is nearly 1 TB in size and is partitioned by month. The Orders table is anticipated
to expand to 3 TB.
CorpDB includes the following smaller tables: ProductName, ProductType, Customers,
SalesRegion, SalesPerson and SalesRegionFilter. Each of these tables consists of a single
partition and is less than 10MB in size.
Access to database information will be restricted to company users and sales people based on
Sales Region. The SalesRegionFilter table lists the regions that each user is permitted to access.
Cube Requirements
The company has identified the following requirements for the cube:
An SSIS package must be used to process the cube.
The time taken to respond to queries must be minimized.
Ad-hoc reporting and analysis must be possible.
Disk space used by the cube must be minimized.
The cube must contain data from the prior day up to 7:00 P.M.
To ensure redundancy and availability, a copy of the cube must exist and be available on both
DB2 and DB3.
A product dimension containing a hierarchy of product name and product type will be added to the
cube.
The cube will store only aggregations of the Orders table due to the large size of the table. The
detailed Orders data will be stored in the data warehouse.
The data warehouse will be the only data source for the cube.
When the cube is initially deployed, it should include aggregation designs.
The cube data must be available for reports when the office opens at 9:00 A.M.
The most current information available must be returned in the reports.
Microsoft SharePoint Server 2010 PerformancePoint Services, Microsoft Excel and SQL Server
Reporting Services (SSRS) will be used to access the cube.
Date Warehouse Requirements
The company has identified the following requirements for the data warehouse:
Disk space used by the data warehouse must be minimized.
The table design of the data warehouse should be as denormalized as possible.
The schema of the data warehouse must be a star design.
The data warehouse tables will be directly accessed by the Business Analysts.
Security must be configured to ensure that Order queries return data relevant to only the sales
regions that the users are permitted to access.
ETL Process Requirements
The company has identified the following requirements for the Extract, Transform and Load (ETL)
process:
The data warehouse will be updated by an ETL process that runs overnight. The ETL process
must start no earlier than 7:00 P.M. and finish no later than 4:00 A.M. the following morning.
A single project containing multiple SSIS packages will be developed and used to load data in the
data warehouse. A database connection string that is set at execution time will be used by the
SSIS packages to connect to the CorpDB database. Development effort used to create the SSIS
packages must be minimized.
*******************************
You need to configure cube security to meet the sales region access restrictions.
Which two of the following actions should you perform? (Choose two)
which three of the following actions should you perform next?
You work as a Database Administrator (DBA) for a company named ABC.com. The
company has a Windows Azure subscription. The company uses a cloud-based SQL
Server environment hosted on SQL Azure.
You have multiple databases hosted on separate database servers.
You need to design a SQL Server Reporting Services (SSRS) report project that will access
each of your databases on SQL Azure.
You need to configure the report to enable users to select the database server when they
run the report. You also need to ensure report subscriptions are supported in the report.
You verify that the security configuration and the schema of each database are identical.
After configuring the data source in the report, which three of the following actions should
you perform next? (Choose three).
Which of the following actions would achieve this?
You work as a Database Administrator (DBA) at a company named ABC.com. The
corporate network includes a SQL Server 2012 infrastructure.
A server named ABC-SSRS01 runs SQL Server 2012 Reporting Services (SSRS). SSRS01
hosts many reports. A report named Report1 queries data in a SQL Server 2012 database
and loads the data into a chart.
You want to make the chart available for use for other users in their reports.
Which of the following actions would achieve this? (Choose all that apply)
What is the easiest way to ensure that users can receive reports according to the schedules and format they sp
You work as a Database Administrator (DBA) at a company named ABC.com.
A Windows Server 2012 server named ABC-SSRS01 runs SQL Server 2012 Reporting
Services (SSRS).
A Windows Server 2008R2 server named ABC-Mail1 runs the Simple Mail Transport
Protocol (SMTP) service.
ABC-SSRS01 has a report named CorpReport1. Users will subscribe to CorpReport1 using
various schedules and reporting formats. The schedules and format information is entered
in a table by a custom application. The reports will be emailed using ABC-Mail1.
What is the easiest way to ensure that users can receive reports according to the
schedules and format they specify?
Which two of the following actions would require the least amount of development effort to achieve this goal?
You work as a Database Administrator (DBA) at a company named ABC.com.
A Windows Server 2012 server named ABC-SSAS01 runs SQL Server 2012 Analysis
Services (SSAS). Another Windows Server 2012 server named ABC-SSRS01 runs SQL
Server 2012 Reporting Services (SSRS).
ABC-SSRS01 has a report that company users use to query a cube on ABC-SSAS01.
Users filter the report by a single date using Date/Time data type report parameter. The
Multidimensional Expressions (MDX) query for the dataset cannot be filtered by directly
using the report parameter.
You need to ensure that a value entered by the user can be used to filter the report data.
Which two of the following actions would require the least amount of development effort to
achieve this goal? (Choose two)
Which role should users be assigned to enable them to create folders and upload and deploy reports?
You work as a Database Administrator (DBA) at ABC.com. The company uses a Microsoft
SQL Server 2012 infrastructure.
A Windows Server 2012 server named ABC-SSRS01 runs SQL Server 2012 Reporting
Services (SSRS). SSRS is configured to run in native mode.
You are evaluating the SSRS predefined roles to determine which users should be
assigned to the roles.
Company security policy states that all users must be assigned the minimum permissions
to perform their required tasks.
Which role should users be assigned to enable them to create folders and upload and
deploy reports?
Which role should users be assigned to enable them to run reports, subscribe to reports, and navigate through
You work as a Database Administrator (DBA) at ABC.com. The company uses a Microsoft
SQL Server 2012 infrastructure.
A Windows Server 2012 server named ABC-SSRS01 runs SQL Server 2012 Reporting
Services (SSRS). SSRS is configured to run in native mode.
You are evaluating the SSRS predefined roles to determine which users should be
assigned to the roles.
Company security policy states that all users must be assigned the minimum permissions
to perform their required tasks.
Which role should users be assigned to enable them to run reports, subscribe to reports,
and navigate through the folder structure?
Which role should users be assigned to give them full permission to manage report server content…?
You work as a Database Administrator (DBA) at ABC.com. The company uses a Microsoft
SQL Server 2012 infrastructure.
A Windows Server 2012 server named ABC-SSRS01 runs SQL Server 2012 Reporting
Services (SSRS). SSRS is configured to run in native mode.
You are evaluating the SSRS predefined roles to determine which users should be
assigned to the roles.
Company security policy states that all users must be assigned the minimum permissions
to perform their required tasks.
Which role should users be assigned to give them full permission to manage report server
content, including the ability to grant permissions to other users, and to define the folder
structure for storing reports and other items?