Q1. You need to select a method of moving data from the staging tables to the factOrders table.
What type of Transact-SQL (T-SQL) statement should you use?
A. INSERT INTO...SELECT
B. SELECT...INTO
C. ALTER PARTITION-SWITCH
D. ALTER PARTITION FUNCTION
E. ALTER TABLE...SWITCH
Answer: E
Q2. An existing cube dimension that has 30 attribute hierarchies is performing very poorly. You have the following requirements:
. Implement drill-down browsing.
. Reduce the number of attribute hierarchies but ensure that the information contained within them is available to users on demand.
. Optimize performance.
You need to redesign the cube dimension to meet the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. set the AggregateFunction property to Sum on all measures. Use the SCOPE statement in a Multidimensional Expressions (MDX) calculation to tune the aggregation types.
B. Set the AttributeHierarchyOptimizedState property to FullyOptimized on the attribute hierarchies.
C. Create user-defined hierarchies. For the attributes sourced by the levels of the user-defined hierarchies, set the RelationshipType property to Rigid. Run incremental processing.
D. Remove as many attribute hierarchies as possible from the dimension. Reintroduce the information in the attribute hierarchies as properties. Implement natural hierarchies and set the AttributeHierarchyVisible property to False for attributes used as levels in the natural hierarchies.
Answer: D
Q3. You are developing the database schema for a SQL Server Analysis Services (SSAS) BI Semantic Model (BISM). The BISM will be based on the schema displayed in the following graphic.
You have the following requirements:
. Ensure that queries of the data model correctly display average student age by
class.
. Ensure that the solution supports role-based security and partitions.
. Minimize development effort.
You need to design the data model.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create a multidimensional project and define measures and a many-to-many dimensional relationship. Create partitions in SQL Server Management Studio (SSMS).
B. Create a multidimensional project and define measures and a reference relationship. Create partitions in SQL Server Data Tools (SSDT).
C. Create a tabular project and define measures. Create partitions in SQL Server Data Tools (SSDT).
D. Create a tabular project and define calculated columns. Create partitions in SQL Server Management Studio (SSMS).
Answer: A
Q4. You need to implement a strategy for efficiently storing sales order data in the data warehouse.
What should you do?
A. Separate the factOrders table into multiple tables, one for each month that has orders, and use a local partitioned view.
B. Separate the factOrders table into multiple tables, one for each day that has orders, and use a local partitioned view.
C. Create daily partitions in the factOrders table.
D. Create monthly partitions in the factOrders table.
Answer: C
Q5. You need to implement the aggregation designs for the cube. What should you do?
A. Use the CREATE CACHE statement.
B. Use the Aggregation Design Wizard.
C. Create relational indexes on the source tables.
D Use the Usage-Based Optimization Wizard.
Answer: B
Q6. You are designing a partitioning strategy for a large fact table in a data warehouse.
Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours. Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.
You need to partition the table to maximize the performance of queries.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Partition the fact table by month, and compress each partition.
B. Partition the fact table by week.
C. Partition the fact table by year.
D. Partition the fact table by day, and compress each partition.
Answer: D
Q7. You need to develop the tabular project to support the date analysis requirements.
What should you do?
A.
Create one date table named Date.
. Create an active relationship between the DateKey column of the Date table and the OrderDateKey column of the Sales table.
. Create an inactive relationship between the DateKey column of the Date table and the ShipDateKey column of the Sales table.
B.
.Create two date tables, one named Order Date and one named Ship Date.
. Create an active relationship between the DateKey column of the Order Date table and the OrderDateKey column of the Sales table.
. Create an inactive relationship between the DateKey column of the Ship Date table and the ShipDateKey column of the Sales table.
C.
. Create one date table named Date.
. Create an active relationship between the DateKey column of the Date table and the ShipDateKey column of the Sales table.
. Create an inactive relationship between the DateKey column of the Date table and the OrderDateKey column of the Sales table.
D.
. Create two date tables, one named Order Date and one named Ship Date.
. Create an active relationship between the DateKey column of the Order Date table and the OrderDateKey column of the Sales table.
. Create an active relationship between the DateKey column of the Ship Date table and the ShipDateKey column of the Sales table.
Answer: C
Q8. 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.)
A. Write a script that calls the flushcache method to clear individual items from the SSRS cache. Create a SQL Server Agent job that runs rs.exe with the script as an input file, and schedule the job to run every day after the archive process completes.
B. Create a SQL Server Agent job that uses a Transact-SQL (T-SQL) step to truncate the dbo.ExecutionCache table in the ReportServerTempDB database. Schedule the job to run every day after the archive process completes.
C. Create a SQL Server Agent job that restarts the SQL Server Reporting Services service. Schedule the job to run every day after the archive process completes.
D. Create a shared schedule. Configure the datasets to expire on the shared schedule.
Answer: A
Q9. You need to select the appropriate mode for the Sales database.
Which mode should you select?
A. ROLAP
B. Direct Query
C. MOLAP
D. In-Memory
Answer: B
Q10. You need to use SQL Server Management Studio (SSMS) to make the SSAS databases available for application testing.
What should you do?
A. Restore the SSAS databases from the latest backup to SSAS01.
B. Script the databases as a Create script to a new window and then execute the script on SSAS01.
C. Detach the SSAS databases from the SSASCluster, and then attach them to SSAS01.
D. Use the Import/Export Wizard to copy the databases from the production server to the development server.
Answer: A