You need to ensure that the cube meets the requirements while optimizing query response time
You are developing a SQL Server Analysis Services (SSAS) cube for the sales department
at your company.
The sales department requires the following set of metrics:
Unique count of customers
Unique count of products sold
Sum of sales
You need to ensure that the cube meets the requirements while optimizing query response
time.
What should you do? (Each answer presents a complete solution. Choose all that apply.)
You need to store validated XML data in Schema1.Table1
You have a text file that contains an XML Schema Definition (XSD).
You have a table named Schema1.Table1.
You have a stored procedure named Schema1.Proc1 that accepts an XML parameter
named Param1.
You need to store validated XML data in Schema1.Table1. The solution must ensure that
only valid XML data is accepted by Param1.
What should you do? (Each correct answer presents part of the solution. Choose all that
apply.)
Which partitioning strategy should you choose?
A SQL Server Analysis Services (SSAS) cube contains a large measure group. The fact
table supporting the measure group is loaded with new data throughout the day.
You have the following requirements:
Ensure that the cube displays current data as quickly as possible.
Maximize availability of the cube.
Maximize query performance for all aggregation levels.
You need to choose a partitioning strategy that meets the requirements.
Which partitioning strategy should you choose? (More than one answer choice may achieve
the goal. Select the BEST answer.)
You need to select the appropriate model type and partitioning strategy to meet the requirements
You work in the Business Intelligence (BI) department of a multinational company.
The company has requested a new corporate BI solution that meets the following
requirements:
The solution must use SQL Server Analysis Services (SSAS).
The model must incrementally add 10 million fact rows per month.
The model must be translated to English, French, or Spanish based on users’ locale.
The model must be able to contain the most recent 36 months of data.
You need to select the appropriate model type and partitioning strategy to meet the
requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)
Which four MDX segments should you insert in sequence before a SELECT statement?
DRAG DROP
You are developing reports based on the SQL Server Analysis Services (SSAS) cube
named ProcessedOrders.
A Multidimensional Expressions (MDX) query must include a query-scoped calculated
member, which computes average sales per order item. The query must also return the set
of three states in a query-scoped named set named East Coast Customers.
You need to define the calculations in an MDX query to meet the requirements.
Which four MDX segments should you insert in sequence before a SELECT statement? (To
answer, move the appropriate actions from the list of actions to the answer area and arrange
them in the correct order.)
What should you set from the index options?
You have an index for a table in a SQL Azure database. The database is used for Online
Transaction Processing (OLTP).
You discover that many page splits occur when records are inserted or updated in the table.
You need to minimize the number of page splits.
What should you set from the index options?
You need to design an ETL process that meets the requirements
You are designing an extract, transform, load (ETL) process for loading data from a SQL
Azure database into a large fact table in a data warehouse each day with the prior day’s
sales data.
The ETL process for the fact table must meet the following requirements:
Load new data in the shortest possible time.
Remove data that is more than 36 months old.
Minimize record locking.
Minimize impact on the transaction log.
You need to design an ETL process that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST
answer.)
Which project type should you choose?
You are developing a BI Semantic Model (BISM) that will be used to analyze complex
budgeting and forecast data sourced from a financial database. The model will be deployed
to a server with 28 GB of RAM.
The source data, located in a SQL Server data warehouse, is currently using 15 terabytes of
disk space and is doubling in size every month. The model will be queried by staff in the
accounting department by using Microsoft Excel 2010.
You need to ensure the highest query performance and scalability for the accounting
department queries.
Which project type should you choose?
You need to reduce the amount of time it takes to execute Proc1
You have a SQL Server 2012 database named Database1.
You execute the following code:
You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1.
What should you do?
You need to design a MOLAP cube that meets the requirements
You are designing a multidimensional OLAP (MOLAP) cube.
The MOLAP cube must meet the following requirements:
Ensure that workloads for aggregation tuning can be automatically collected.
Require the least amount of effort to perform manual aggregation tuning.
Minimize impact on the performance of previously tuned queries.
You need to design a MOLAP cube that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)