Real Microsoft 70-467 Practice Questions

1. You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.
You have the following requirements:
- Ensure that the deployment model supports changing the content of connection strings by using parameters at execution time.
- Ensure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database.
- Maximize performance at execution time.
- Minimize development effort.
You need to design a solution that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

2. You are designing a SQL Server 2012 Integration Services (SSIS) deployment strategy. You currently have many SQL Server 2008 SSIS packages that require upgrading. The production environment includes SSIS 2012 and SSIS 2008. The environment includes existing command shell scripts that invoke the dtutil command-line utility. You need to design a deployment strategy that supports existing deployment strategies and requires the minimum amount of effort.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

3. The IT department is currently upgrading all SQL Server Integration Services (SSIS) packages to SSIS 2012. The final version of a complex SSIS 2005 package has been overwritten. Four prior versions of the package exist. Each of the prior versions contains a different part of the final solution. You have backups of all associated project files. You need to create an updated package as quickly and accurately as possible.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

4. A company runs SQL Server Database Engine and SQL Server Reporting Services (SSRS) in native mode. Reports are based on data that is cached in multiple shared datasets. Source data is archived each day at midnight for regulatory compliance purposes. The shared datasets may continue to cache data that should not be used in reports. Shared report schedules are often paused during nightly server maintenance windows. Reports must not return archived data. You need to create a fully automated solution to ensure that reports do not deliver archived data.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

5. You are designing a reporting solution that uses SQL Server Reporting Services (SSRS) in SharePoint integrated mode.
The reporting solution must meet the following requirements:
- Allow report writers to reuse content between different reports.
- Allow report writers to modify reusable content in SharePoint.
- Retain version history for report content.
You need to choose a reporting method that meets the requirements.
W hat should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)

6. You need to design a data warehouse schema to support a multidimensional cube. The fact table will contain multiple columns representing order dates, shipping dates, and billing dates. The cube will contain a measure group based on the fact table.
What is the best design to achieve the goal? More than one answer choice may achieve the goal. Select the BEST answer.

7. You are creating a product dimension table and a SQL Server Integration Services (SSIS) package that will load the table. You need to keep a history in the table of changes to the names of products.
Which three actions should you perform? Each correct answer presents part of the solution.

8. You are redesigning a SQL Server Analysis Services (SSAS) database that contains a cube named Sales. Before the initial deployment of the cube, partition design was optimized for processing time. The cube currently includes five partitions named FactSalesl through FactSales5. Each partition contains from 1 million to 2 million rows. The FactSales5 partition contains the current year's information. The other partitions contain information from prior years; one year per partition. Currently, no aggregations are defined on the partitions. You remove fact rows that are more than five years old from the fact table in the data source and configure query logs on the SSAS server. Several queries and reports are running very slowly. You need to optimize the partition structure and design aggregations to improve query performance and minimize administrative overhead.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

9. You have a data warehouse named DW1. An OLAP cube named Cube1 uses DW1 as its data source. Cube1 uses the MOLAP storage mode. You create a SQL Server Integration Services (SSIS) package that updates DW1, and then processes Cube1. The package executes each day at 06:00. You need to configure the storage settings of Cube1. The solution must ensure that aggregation processing is reduced as much as possible during cube querying.
What is the best storage setting you should you use for Cube1? More than one answer choice may achieve the goal. Select the BEST answer.

10. The Sales database includes a table named Products that contains 42 columns, and two tables that record all product sales transactions. The database does not store aggregated data. The database is continually maintained to provide optimal indexing. The database server is sized appropriately. The Marketing department requests a delimited text file that includes seven fields containing product information from the Products table.
You have the following requirements:
- Display the ProductID in the first column. Organize the remaining columns alphabetically by column heading.
- Sort rows in ProductID order.
- Aggregate product units sold.
- Minimize the time required to return the results.
You create a SQL Server Integration Services (SSIS) package and add the appropriate connection managers and a Data Flow task. You need to develop the Data Flow task.
What should you add to the Data Flow task? (More than one answer choice may achieve the goal. Select the BEST answer.)

11. You are creating a calculated measure in a sales cube by using a Multidimensional Expressions (MDX) calculation. The cube includes a date dimension. The date dimension has a hierarchy named Calendar that includes Calendar Year, Month, and Day attributes. The calculated measure must calculate the year-to-date sales for the current slicer. The MDX calculation must work for as many hierarchy levels as possible. You need to create a calculated measure for the cube that meets the requirements.
Which expression should you use in the calculation designer? (More than one answer choice may achieve the goal. Select the BEST answer.)

12. You are designing a customer dimension for a multidimensional OLAP (MOLAP) database. The dimension table will contain millions of rows of data.
Customer dimension attributes will come from the following two sources:
- An enterprise resource planning (ERP) system that contains millions of rows of data. Attributes from this source will be updated daily.
- A marketing database that contains thousands of rows of data representing customers' geographic information. Attributes from this source will be updated monthly.
Most report queries against the cube use attributes from both sources. Customers in the cube dimension will frequently be filtered by the geographic attributes. You need to design the dimension table and cube dimension to ensure that report queries perform well, and minimize the time required to reprocess cube attributes.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

13. You have two servers named Served and Server2. Both servers have SQL Server 2012 installed. Both servers have a SQL Server Integration Services (SSIS) catalog. You are preparing a disaster recovery plan for a business intelligence (BI) solution. You need to ensure that the SSIS catalog from Server1 can be restored to Server2.
Which two actions should you perform on Server1? Each correct answer presents part of the solution.

14. A company has a relational data warehouse, a SQL Server Analysis Services (SSAS) database, and a SQL Server Reporting Services (SSRS) instance. The SSAS database contains a cube named Sales. Shared data sources exist in SSRS for the relational and SSAS databases. Each company department has its own report writers. Report writers in the Marketing department want to create new reports by using Report Builder. Many reports will include data generated by a custom formula that references data stored either in a data warehouse table or in the Sales cube. The custom formula will compare time periods across multiple products, categories, and regions.
You have the following requirements:
- Ensure that only Marketing department report writers can access the custom formula.
- Implement only one dataset.
- Ensure that the dataset references the data source that will provide the fastest data retrieval.
You need to meet the requirements to support the Marketing department report writers.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

15. You are designing a self-service reporting solution based on published PowerPivot workbooks.
The reporting solution must allow users to perform the following tasks:
- Easily create reports.
- Create report queries by dragging and dropping fields.
- Create presentation-quality reports with minimal effort.
You need to choose a reporting tool that meets the requirements.
Which reporting tool should you choose? (More than one answer choice may achieve the goal. Select the BEST answer.)

16. You are designing a subscription strategy for a SQL Server Reporting Services (SSRS) report. You have an application that populates a table with user-specific subscription schedules and report formats. You need to ensure that users can receive reports by email according to their preferences. Email messages will be sent via an internal mail server.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

17. A hospital has a relational data warehouse, a SQL Server Analysis Services (SSAS) database, and a SQL Server Reporting Services (SSRS) instance. The SSAS database contains a cube named Pharmacy. Shared data sources exist in SSRS for the relational and SSAS databases. Each hospital department has its own report writers. Report writers in the Human Resources (HR) department want to create new reports by using Report Builder. Many reports will include data generated by a custom formula that references data stored either in a data warehouse table or in the Pharmacy cube. The custom formula will compare time periods across multiple products, categories, and employees.
You have the following requirements:
- Ensure that only the HR department report writers can access the custom formula.
- Implement only one dataset.
- Ensure that the dataset references the data source that will provide the fastest data retrieval.
You need to meet the requirements to support the HR department report writers.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

18. Your network contains the following components:
- Microsoft SharePoint Server 2010
- SQL Server 2008 Service Pack 2 (SP2)
- SQL Server 2008 Service Pack 1 (SP1) in Reporting Services Native Mode
You need to recommend a solution to upgrade the network to SQL Server 2014.
The solution must meet the following requirements:
- Minimize the amount of time that SharePoint services are unavailable.
- Deploy the upgraded Reporting Services instance in Reporting Services SharePoint Integrated Mode.
You upgrade the Database Engine to SQL Server 2014.
Which three actions should you recommend performing next? Each correct answer presents part of the solution.

19. You have a SQL Server Reporting Services (SSRS) 2012 instance integrated with a Microsoft SharePoint 2010 farm. You need to upgrade to SSRS 2014 with the minimum downtime and hardware requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

20. You are designing a partitioning strategy for a SQL Server Analysis Services (SSAS) cube. New data is loaded in real-time into the data warehouse that feeds the cube. Between 10 million and 15 million rows of data are loaded into the main fact table each day from a Microsoft Azure SQL Database.
You have the following requirements:
- Maximize cube query performance during business hours.
- Ensure that data is available in the cube as soon as possible after it is loaded into the data warehouse.
You need to design a partitioning strategy that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)