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 are using SQL Server Data Tools to create a SQL Server Integration Services (SSIS)
package. The package will use data from a source table to populate a fact table. You have
configured Table as the data access mode for the OLE DB source.

The fact table has a column named TotalSales. The source table has two columns named
CompletedSales and ConfirmedOrders.
You need to configure the SSIS package to discard any records with zero values in the
CompletedSales and ConfirmedOrders columns then populate the TotalSales column in the
fact table with the sum values of the CompletedSales and ConfirmedOrders columns.
How should you configure the data flow task?

You work as a Database Administrator (DBA) at a company named ABC.com. The
corporate network includes a SQL Server 2012 infrastructure.
A Windows Server 2012 server named ABC-SSAS01 runs SQL Server 2012 Analysis
Services (SSAS). You have an SSAS cube named ABC-Cube1.
You need to configure ABC-Cube1 to use proactive caching and ensure the cache is
updated when the data changes after waiting for the default 10 second silence interval.
Data that has been cached for more than 30 minutes should be dropped and the storage of
the data and aggregations in the cube must be optimized.
What should you do?

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).
You have a large cube on ABC-SSAS01. The cube contains a single partition and a single
measure group. The cube contains many millions of rows of data.
You need to modify the cube to cope with the rapidly increasing amount of data.
You plan to configure a staging table that will be used to store only the data that has been
added since the last processing of the cube. The measure group will be partitioned by
month and records from the staging table will be inserted into the relevant partition. Record
deletions or updates will not be included in the staging table.
How should you modify the cube?

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-SSAS1 runs SQL Server Analysis Services (SSAS).
You are in the process of designing a monitoring strategy for the ABC-SSAS1. You want to
monitor custom trace events and detailed event durations for queries executed on the
server.
You connect to ABC-SSAS1 using SQL Server Management Studio.
Which two of the following actions should you perform next? (Choose two)

You work as a Database Administrator (DBA) at a company named ABC.com. The
corporate network includes a SQL Server 2012 infrastructure.
The company has a data warehouse named CorpDW. CorpDW is updated with
approximately 100 million new records every week outside business hours.
Data is sometimes queried at month level but more often is queried at day level using cube
processing and reports.

CorpDW contains a large fact table. You need to configure the table partitioning to ensure
that the performance of the queries is maximized.
Which two of the following options describe how you should you partition the table?
(Choose two).

You work as a Database Administrator (DBA) at a company named ABC.com. The
corporate network includes a SQL Server 2012 infrastructure.
The company has a database named CorpDB and a data warehouse named CorpDW.
You need to create an ETL process to update a large fact table in CorpDW with the prior
day’s data from CorpDB and to remove data that is more than 24 months old from CorpDW.
You need to configure the ETL process to add the new data as quickly as possible,
minimize the amount of logging in the transaction log and minimize the risk of locked
records during the update process.
You partition the fact table by date.
How should you add the new records and remove the old data from the fact table? (Choose
two).

You work as a Database Administrator (DBA) at a company named ABC.com. The
corporate network includes a SQL Server 2012 infrastructure.
A Windows Server 2012 server named ABC-SSAS01 runs SQL Server 2012 Analysis
Services (SSAS). You have an SSAS cube named SalesCube1.
Users in the Sales department are members of an Active Directory global group named
SalesUsers.
You need to grant the users in the Sales department write access to SalesCube1. You open
SQL Server Management Studio (SSMS).
Which four of the following actions should you perform next? (Choose four)

You work as a Database Administrator (DBA) at a company named ABC.com. The
corporate network includes a SQL Server 2012 infrastructure.
A Windows Server 2012 server named ABC-SSAS01 runs SQL Server 2012 Analysis
Services (SSAS).
You are creating a BI Semantic Model (BISM). The graphic below shows the schema that
the BISM will be based on.

You are planning the data model. Your data model must support queries to display the
average number of employee years of service by factory and the average output by
employee.
Which three of the following should you include in your design? (Choose three).

You work as a Database Administrator (DBA) at a company named ABC.com. The
corporate network includes a SQL Server 2012 infrastructure.

PowerPivot for Microsoft Excel workbooks and SQL Server Reporting Services (SSRS)
reports have been created and published to provide a self-service data analysis and
reporting environment.
Due to the increasing volume of data, you need to create server-based data models to
continue to provide the self-service data analysis and reporting environment.
You need to configure a solution that can support the addition of calculated columns and
use the existing SSRS reports.
Which of the following steps should you perform to create and implement your solution?
(Choose all that apply).

You work as a Database Administrator (DBA) at a company named ABC.com. The
corporate network includes a SQL Server 2012 infrastructure.
A SQL Server Analysis Services (SSAS) cube named SalesCube1 is loaded with daily sales
data from a SQL Server data mart named DMSales. The source data is read from a SQL
Server 2012 database. The DMSales data mart uses a star schema.
Two tables in DMSales include a total of 80 flag columns. The tables containing the flag
columns have the same granularity as the fact table. The flag columns each have 12
different indicators values which are reused by some flags.
You need to modify the DMSales schema to maximize cube processing and query
performance and to enable users to use all flags and indicators to slice the data.

The table used for the date dimension needs to be modified to include a numeric surrogate
key and the surrogate key needs to be added to the fact table.
Which two of the following schema modifications should you perform? (Choose two)