Designing Business Intelligence Solutions with Microsoft SQL Server

Question 1

You execute the SalesbyCategory report and receive the following error message: "Members, tuples, or sets must use the same hierarchies in the function." You need to ensure that the query executes successfully.
Which two actions should you perform? Each correct answer presents part of the solution.

A. Move the Product clause from line 08 to line 10.

B. Move the Date and Product clauses on line 11 to axis 0.

C. Move the Date clause from line 10 to line 08.

D. Move the Measures clause on line 02 to axis 1.

Correct Answer: C

Question 2

You need to create the Sales Reporting shared SSRS data source.
Which SSRS data connection type should you use?

A. OData

B. ODBC

C. OLE DB

D. Microsoft SQL Server

Correct Answer: D

Question 3

You are designing a strategy for an enterprise reporting solution that uses SQL Server Reporting Services (SSRS).
Many of the SSRS reports will use common utilities and functions, including the following:
* Report utility functions and business logic in code
* Standardized report formatting properties such as fonts and colors for report branding Formatting may change and new functions may be added as the reporting solution evolves.
You need to create a strategy for deploying the formatting and code across the entire enterprise reporting solution. You must also ensure that reports can be easily updated to reflect formatting and function changes.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Create an assembly that contains formatting properties and code. Deploy the assembly on the Reporting Server and reference the assembly from each report.

B. Store the formatting properties and code in database objects. Use stored procedures to populate a default value for report parameters and map each parameter to a corresponding formatting property.

C. Create a report as a template. Apply standardized formatting to the template. Store code in the Code section of the template.

D. Build a web service that retrieves formatting properties and runs code. Call the web service through a report dataset.

Correct Answer: A

Question 4

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 5

You need to configure the Scenario attribute to ensure that business users appropriately query the Sales Plan measure.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

A. Set the Usage property to Parent.

B. Set the RootMemberIf property to ParentIsMissing.

C. set the DefaultMember property to the Forecast member.

D. Set the AttributeHierarchyVisible property to False.

E. Set the IsAggregatable property to False.

F. Set the AttributeHierarchyEnabled property to False.

Correct Answer: A,C

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

Question 6

You need to implement the product dimension.
What should you do?

A. In the data warehouse, create a dimension table that contains product name and a dimension table that contains product color.

B. In the data warehouse, create a product dimension from a view that joins the Product and ProductColor tables in the financial database and contains product name and product color attributes.

C. In the cube, create a named query that joins the Product and ProductColor tables in the financial database.

D. In the data warehouse, create a product dimension table that contains product name and product color.

Correct Answer: D

Question 7

You are designing a fact table in a SQL Server database.
The fact table must meet the following requirements:
* Include a columnstore index.
* Allow users to choose up to 10 dimension tables and up to five facts at one time.
* Maximize performance of queries that aggregate measures by using any of the 10 dimensions.
* Support billions of rows.
* Use the most efficient design strategy.
You need to design the fact table to meet the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the columnstore index on the measure columns.

B. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on the dimensional key columns and the measure columns.

C. Design a fact table with 5 dimensional key columns and 10 measure columns. Place the columnstore index on the dimensional key columns.

D. Design a fact table with 10 dimensional key columns and 5 measure columns. Place the columnstore index on only the measure columns.

Correct Answer: B

Question 8

You administer a SQL Server Analysis Services (SSAS) instance.
You need to capture a continuous log of detailed event and subevent durations and custom trace events from queries executed in the SSAS instance.
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: Write an XMLA script to log the extended events of the trace.
Box 2: Launch SQL Server Management Studio and connect to the instance.
Box 3: Execute the script.
Note:
* Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. The SQL Server Audit object collects a single instance of server- or database-level actions and groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance. The Server Audit Specification object belongs to an audit. You can create one server audit specification per audit, because both are created at the SQL Server instance scope.
* Trace events can be started and captured using SQL Server Profiler, , or can be started from an XMLA command as SQL Server Extended Events and later analyzed.
* Extended Event tracing is enabled using a similar XMLA create object script.
Reference: Analysis Services Trace Events

Question 9

You have a data warehouse named DW1.
An OLAP cube named Cube1 uses DW1 as its data source. Cube1 uses the MOLAP storage mode.
You create a SQL Server Integration Services (SSIS) package that updates DW1, and then processes Cube1.
The package executes each day at 06:00.
You need to configure the storage settings of Cube1. The solution must ensure that aggregation processing is reduced as much as possible during cube querying.
What is the best storage setting you should you use for Cube1? More than one answer choice may achieve the goal. Select the BEST answer.

A. Scheduled MOLAP

B. Medium-latency MOLAP

C. Automatic MOLAP

D. Low-latency MOLAP

Correct Answer: C

Question 10

You need to perform the pre-upgrade database backup operation by using SQL Server Management Studio (SSMS).
How should you configure the backup options?

A. Select the Apply compression check box. Select the Encrypt backup file check box and supply a password.

B. Clear the Apply compression check box. Clear the Encrypt backup file check box.

C. Select the Apply compression check box. Clear the Encrypt backup file check box.

D. Clear the Apply compression check box. Select the Encrypt backup file check box and supply a password.

Correct Answer: C

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

Question 11

You are designing a SQL Server 2012 Integration Services (SSIS) deployment strategy. You currently have many SQL Server 2008 SSIS packages that require upgrading.
The production environment includes SSIS 2012 and SSIS 2008. The environment includes existing command shell scripts that invoke the dtutil command-line utility.
You need to design a deployment strategy that supports existing deployment strategies and requires the minimum amount of effort.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Use a package deployment model. Use the Integration Services Deployment Wizard.

B. Use a project deployment model. Change the command shell scripts to reference the SQL Server 2012 path to dtutil.

C. Use a project deployment model. Use the Integration Services Deployment Wizard.

D. Use a package deployment model. Change the command shell scripts to reference the SQL Server 2012 path to dtutil.

Correct Answer: D

Question 12

You are defining a named set by using Multidimensional Expressions (MDX) in a sales cube.
The cube includes a Product dimension that contains a Category hierarchy and a Color attribute hierarchy.
You need to return only the blue products in the Category hierarchy.
Which set should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Option D

B. Option B

C. Option A

D. Option C

Correct Answer: D

Question 13

A school stores information about teachers, students, classes, and enrollments in a Microsoft Azure SQL Database database. The database includes a table that maps the user IDs of teachers to the subjects they teach.
Teachers access reports in a SQL Server Reporting Services (SSRS) instance by using their credentials.
You are developing a report that displays a table of class enrollments for a specific subject. The report will prompt teachers to select from their mapped subjects. The table is based on a dataset named ClassData. To minimize report execution time, the ClassData dataset has been configured to cache all class enrollment data.
You need to ensure that the report displays the correct class enrollment data.
Which item should you configure?
To answer, select the appropriate item in the answer area.

Correct Answer:

Explanation

Question 14

You need to create the Package Activity report.
What should you do?

A. Create a log table and build a custom log provider to write to the log table. Then create an SSRS report that uses the log table.

B. Use the SSIS log provider for SQL Server. Then create an SSRS report that uses the sysssislog table.

C. Create an SSRS report that uses thecatalog.executions and catalog.execution_data_statistics views.

D. Create a log table and use SSIS event handlers to write to the log table. Then create an SSRS report that uses the log table.

Correct Answer: C

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

Latest Update

Useful Links

Contact Us