Microsoft Designing Business Intelligence Solutions with Microsoft SQL Server

QUESTION NO: 1
You need to select and configure a tool for the monitoring solution.
What should you choose?

A. SQL Server Profiler configured with the Query Processing: Query Subcube event B. Performance Monitor configured with the MSAS11:Storage Engine Query counter C. SQL Server Profiler configured with the Queries Events: Query Begin event D. Performance Monitor configured with the MSAS11:Processing counter

Correct Answer: B

QUESTION NO: 2
You are deploying the Research model.
You need to ensure that the data contained in the model can be refreshed.
What should you do?

A. Assign the Tabular instance service account permissions to the MarketResearch.xlsx network location. B. Import MarketResearch.xlsx to a new tabular database on the Tabular instance. C. Create a SQL Server Integration Services (SSIS) package that imports data from MarketResearch.xlsx nightly. Load the data to CUBE1. D. Upload MarketResearch.xlsx to Library1.

Correct Answer: A

QUESTION NO: 3
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: 4
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 week. B. Partition the fact table by year. C. Partition the fact table by month, and compress each partition. D. Partition the fact table by day, and compress each partition.

Correct Answer: D

QUESTION NO: 5
Your network contains a development environment, a staging environment, and a production environment.
You have a SQL Server Integration Services (SSIS) project. All of the packages in the project load data from files in a shared network folder. The packages use indirect XML configurations to set the location of the network folder.
The project is deployed to the three environments. Each environment has a different set of source files and a different network folder for the source files.
Currently, if an environment variable is missing, the package will use the network folder specified in the package, not the folder specified in the XML configuration file.
You need to ensure that each time a package is executed, the network folder location specified in the package is NOT used.
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:

Explanation
Box 1:

Box 2:

Box 3:

QUESTION NO: 6
You are creating the Australian postal code query.
Which arguments should you use to complete the query?
To answer, drag the appropriate arguments to the correct location or locations in the answer area. (Use only arguments that apply.)

Correct Answer:

Explanation
Box 1: BDESC
Box 2: DESC
Topic 6, Tailspin Toys Case B
Overview
Tailspin Toys is a manufacturing company that has offices across the United States, Europe, and Asia.
Tailspin Toys plans to implement a business intelligence (BI) solution for its US-based headquarters to manage the sales data, including information on customer transactions, products, sales quotas, and bonuses.
Existing Environment
Data Sources
Tailspin Toys currently stores data in line-of-business applications, relational databases, flat files, and the following;
* A Microsoft Excel spreadsheet named MarketResearch.xlsx. The spreadsheet is stored on a network drive in a directory owned by an analyst.
* A tabular model named Research.xlsx used in PowerPivot for Excel. Research.xlsx uses MarketResearch.xlsx as one of its data sources.
Network
The network contains an Active Directory forest named tailspintoys.com. The forest contains a Microsoft SharePoint Server 2013 server farm.
Implementation Plans
Databases
Tailspin Toys plans to build a star schema data warehouse named DB1. DB1 will be loaded from several different sources and will be updated nightly to contain new sales data.
DB1 will contain the following table types:
* A fact table to store transactional data, including transaction date, productID, customerID, quantity, and sales amounts.
* Dimension tables to store information about each customer, each product, each date, and each sales department user.
BI Semantic Models
Tailspin Toys plans to deploy the following BI semantic models:
* A multidimensional cube named CUBE1 that will store sales data. CUBE1 will be based on DB1 and will be hosted in SQL Server Analysis Services (SSAS). CUBE1 will contain two distinct count
* measures named UniqueCustomers and UniqueProducts. The measures are expected to aggregate hundreds of millions of rows from DB1.
* A tabular model named SalesCommission that will contain information about sales department user quotas and commissions.
* A tabular model named Research that will contain the migrated model from Research.xlsx.
* An instance of SSAS in tabular mode named Tabular.
Planned Reports and Queries
Tailspin Toys plans to implement the following reports and queries:
* Power View reports that use data from the Research model.
* Reports for each year the company recorded sales data that used the SalesCommission model. The reports will use the Dates_Between() and the DatesInPeriod() DAX functions in queries.
* Reports that use CUBE1 that contain the following query statements:

* A report named SalesByCategory that uses CUBE1 and the following query statement: (Line numbers are included for reference only.)

Self-Service Reporting
Tailspin Toys plans to deploy the following self-service reports:
* Reports created by sales department specialists that use CUBE1 and contain drillthroughs, maps,
* sparklines, and Key Performance Indicators (KPIs). The reports will be stored in a SharePoint Server document library named Library1.
* Reports created by sales department managers that use the SalesCommission model. The reports will contain visualizations that show sales department users their current sales as compared to their quota.
* Power Pivot models stored in a SharePoint Server document library that is configured as a PowerPivot Gallery named Gallery1.
Requirements
Data Security Requirements
Sales department users browsing CUBE1 must be able to view the sales data that relates to their respective customers only.
Access to reports must be controlled by using SharePoint permissions.
ETL Requirements
Tailspin Toys identifies the following extract, transformation, and load (ETL) requirements:
* Nightly updates of DB1 must support the incremental load of dimension and fact tables on separate schedules. Fact data may be loaded before dimension data.
* ETL processes must be able to update dimension attributes without losing context for historical facts.
* Referential integrity between dimension and fact tables must be maintained at all times.
Cube Performance Requirements
The design of CUBE1 must minimize the processing time of the UniqueCustomers and Unique Products measures. The time required to process CUBE1 each night must be minimized.
Data Refresh Requirements
The Research model must be refreshed nightly without interrupting the workflow of the analyst.

QUESTION NO: 7
You are defining a named set by using Multidimensional Expressions (MDX) in a sales cube.
The cube includes a Customer dimension that contains a Geography hierarchy and a Gender attribute hierarchy.
You need to return only the female customers in the Geography hierarchy.
Which set should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Option A B. Option C C. Option D D. Option B

Correct Answer: A

QUESTION NO: 8
You need to implement the SalesCommission model to support the planned reports and queries.
What should you do?

A. Create a date table that contains only one row for each date on which a sale is recorded. B. Create a new calculated date column in the sales table for date calculations. C. Use the existing transaction date column in the sales table for date calculations. D. Create a date table that contains a row for every date since data started being recorded.

Correct Answer: D

QUESTION NO: 9
You need to implement a strategy for efficiently storing sales order data in the data warehouse.
What should you do?

A. Create daily partitions in the factOrders table. B. Create monthly partitions in the factOrders table. C. Separate the factOrders table into multiple tables, one for each day that has orders, and use a local partitioned view. D. Separate the factOrders table into multiple tables, one for each month that has orders, and use a local partitioned view.

Correct Answer: A

QUESTION NO: 10
You need to implement the requirements for the StageFactSales package.
Which four 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:

Explanation
Box 1:

Box 2:

Box 3:

Box 4:

Note:
* MULTIFLATFILE
A Multiple Flat Files connection manager enables a package to access data in multiple flat files.
* From scenario: A package named StageFactSales loads data into a data warehouse staging table. The package sources its data from numerous CSV files exported from a mainframe system. The CSV file names begin with the letters GLSD followed by a unique numeric identifier that never exceeds six digits. The data content of each CSV file is identically formatted.

QUESTION NO: 11
You are developing a SQL Server Reporting Services (SSRS) solution.
You plan to create reports based on the results of a currency exchange SOAP web service call.
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:

Explanation

QUESTION NO: 12
You need to roll back the compatibility level of the Research database.
What should you do?

A. Restore a backup of the previous version of the database. B. Change the CompatibilityLevel property in the XMLA script, and then execute the script. C. In SQL Server Management Studio (SSMS), change the compatibility level in the database properties. D. Use an ALTER DATABASE statement to set the compatibility option.

Correct Answer: A

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

A. Report Builder B. Power View C. Microsoft Excel D. Report Designer

Correct Answer: B

QUESTION NO: 14
You are the administrator of a SQL Server Integration Services (SSIS) catalog. You have access to the original password that was used to create the SSIS catalog.
A full database backup of the SSISDB database on the production server is made each day. The server used for disaster recovery has an operational SSIS catalog.
The production server that hosts the SSISDB database fails.
Sensitive data that is encrypted in the SSISDB database must not be lost.
You need to restore the production SSIS catalog to the disaster recovery server.
Which three steps 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:

Explanation
Box 1:

Box 2:

Box 3:

QUESTION NO: 15
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

Microsoft Designing Business Intelligence Solutions with Microsoft SQL Server - 070-467 Exam Questions