Microsoft Recertification for MCSE: Business Intelligence

QUESTION NO: 1
DRAG DROP
You are developing a SQL Server Analysis Services (SSAS) multidimensional project. The project file includes two cubes named Finance and Operations. The project also includes a dimension named Date. The Date dimension includes two hierarchies named Fiscal and Calendar. The Date dimension has been added to both cubes.
You need to disable the Fiscal hierarchy in the Operations cube without impacting other database objects.
Which three actions should you perform in sequence? (To answer, move the appropriate
actions from the list of actions to the answer area and arrange them in the correct order.)

Correct Answer:

QUESTION NO: 2
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.)

A. Combine all the partitions into a single partition. Use the Usage-Based Optimization Wizard to create aggregations. B. Use the Aggregation Design Wizard to create aggregations for the current partitions. C. Use the Usage-Based Optimization Wizard to create aggregations for the current partitions. D. Combine all the partitions into a single partition. Use the Aggregation Design Wizard to create aggregations.

Correct Answer: C

QUESTION NO: 3
DRAG DROP
You are designing a dataset for a SQL Server Reporting Services (SSRS) report. The report includes the report items displayed in the following graphic.

The dataset is sourced from a commonly used stored procedure in an inventory data mart hosted in a SQL Azure database.
It returns the status for all products across all storage sites. The report must display data for the storage site that is selected by the Site report parameter. You cannot change the stored procedure code.
You need to filter the dataset to use only data specific to the selected site.
How should you configure the filter?
To answer, drag the appropriate expression or expressions to the correct location or locations in the answer area. (Answer choices may be used once, more than once, or not all.)

Correct Answer:

QUESTION NO: 4
You are developing a SQL Server Analysis Services (SSAS) tabular project.
The model includes a table named DimEmployee. The table contains employee details, including the sales territory for each employee. The table also defines a column named EmployeeAlias which contains the Active Directory Domain Services (AD DS) domain and logon name for each employee. You create a role named Employees.
You need to configure the Employees roles so that users can query only sales orders for their respective sales territory.
What should you do?

A. Add a row filter that implements only the USERNAME function. B. Add a row filter that implements only the CUSTOMDATA function. C. Add a row filter that implements the LOOKUPVALUE and USERNAME functions. D. Add a row filter that implements the LOOKUPVALUE and CUSTOMDATA functions.

Correct Answer: C

QUESTION NO: 5
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. 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. B. 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. C. Set the AttributeHierarchyOptimizedState property to FullyOptimized on the attribute hierarchies. D. set the AggregateFunction property to Sum on all measures. Use the SCOPE statement in a Multidimensional Expressions (MDX) calculation to tune the aggregation types.

Correct Answer: B

QUESTION NO: 6
DRAG DROP
You need to extend the schema design to store the product dimension data.
Which design should you use?
To answer, drag the appropriate table or tables to the correct location or locations in the answer area. (Fill from left to right. Answer choices may be used once, more than once, or not all.)

Correct Answer:

QUESTION NO: 7
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.)

A. Partition the fact table by date. Use partition switching and a staging table to remove old data. Insert new data directly into the fact table. B. Partition the fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table. C. Partition the fact table by date. Use partition switching and staging tables both to remove old data and to load new data. D. Partition the fact table by customer. Use partition switching both to remove old data and to load new data into each partition.

Correct Answer: C

QUESTION NO: 8
HOTSPOT
You are developing a SQL Server Analysis Services (SSAS) cube.
You are writing the following Multidimensional Expressions (MDX) statement for use by a calculated measure. The measure computes the sales amount for the same time period of the previous year. (Line numbers are included for reference only.)

You need to complete the MDX statement.
Which MDX function should you use in line 03? To answer, select the appropriate MDX function in the functions list.

Correct Answer:

QUESTION NO: 9
You are developing a BI Semantic Model (BISM) based on a simple and small dataset sourced from SQL Server. The data size and complexity of the data relationships will not change. The model will be used to produce reports in Power View. The reports will show the relationship between product sales and rainfall over time.
You need to use an appropriate project type.
Which project types should you use? (Each answer presents a complete solution. Choose all that apply.)

A. a tabular project that uses the DirectQuery query mode B. a multidimensional project that uses the ROLAP storage mode and columnstore indexes C. a PowerPivot workbook that is deployed to Microsoft SharePoint Server 2010 D. a multidimensional project that uses the MOLAP storage mode and proactive cache E. a tabular project that uses the In-Memory query mode

Correct Answer: A,C,E

Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).

QUESTION NO: 10
You are developing a BI Semantic Model (BISM) that retrieves data from several sources. These sources include a SQL Azure database and an OData data feed that includes rainfall data for towns on the east coast of Australia. The model will be deployed to a server with significantly more memory than the total size of the source data.
You have the data feed URL, which you will use when developing the model in SQL Server Data Tools (SSDT). The model must meet the following requirements:
- Performance must be maximized.
- Data latency of up to one month is acceptable.
You need to choose a project type and a data access mode to meet the requirements.
What should you do?

A. In SSDT, select the multidimensional project type and use the ROLAP storage mode. B. In SSDT, select the multidimensional project type and use the MOLAP storage mode. C. In SSDT, select the tabular project type and use the In-Memory query mode. D. In SSDT, select the tabular project type and use the DirectQuery query mode.

Correct Answer: C

QUESTION NO: 11
You need to implement the date dimension in the Operations database.
What should you do?

A. Create three database dimensions. Add each database dimension as a cube dimension by setting the Referenced relationship type. B. Create three database dimensions. Add each database dimension as a cube dimension by setting the Regular relationship type. C. Create one database dimension. Add three cube dimensions based on the database dimension. Set the Regular relationship type for each cube dimension. D. Create one database dimension. Add three cube dimensions based on the database dimension. Set the Referenced relationship type for each cube dimension.

Correct Answer: C

QUESTION NO: 13
You deploy a PowerPivot workbook to a PowerPivot Gallery in a Microsoft SharePoint site. Workbook data comes from two different sources: Source A and Source B.
The workbook contains five tables with a total of 20 million rows from Source A, and three small lookup tables from Source B.
Data from Source A is updated throughout the day. Data from Source B is updated at 3:00 P.M.
You have the following requirements:
- Refresh the PowerPivot workbook with updated lookup data as soon as possible.
- Minimize load on the source systems and the SharePoint environment during business hours.
- Minimize user involvement in the data refresh process.
You enable automatic data refresh for the workbook.
You need to configure a data refresh schedule for the workbook that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Set the default schedule to refresh at 3:00 P.M. every day. Do not create individual source schedules. B. Set the default schedule to refresh outside of business hours. Manually refresh the workbook at 9:00 A.M. every day. C. Set the default schedule to refresh at 9:00 A.M. every day. Do not create individual source schedules. D. Set the default schedule to refresh at 9:00 A.M. every day. Create a separate schedule for Source A's connection to refresh outside of business hours. E. Set the default schedule to refresh outside of business hours. Do not manually refresh the workbook.

Correct Answer: D

QUESTION NO: 14
HOTSPOT
You are developing a SQL Server Reporting Services (SSRS) solution.
You plan to create reports based on a SQL Server Analysis Services (SSAS) tabular
database configured in DirectQuery mode.
You need to configure a shared data source.
Which data source type should you use?
To answer, select the appropriate type from the drop-down list in the answer area.

Correct Answer:

Microsoft Recertification for MCSE: Business Intelligence - 70-470 Exam Questions