Online Access Free 70-470 Practice Test

DRAG DROP
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:

A hospital has a relational data warehouse, a SQL Server Analysis Services (SSAS) database, and a SQL Server Reporting Services (SSRS) instance. The SSAS database contains a cube named Pharmacy. Shared data sources exist in SSRS for the relational and SSAS databases. Each hospital department has its own report writers.
Report writers in the Human Resources (HR) department want to create new reports by using Report Builder. Many reports will include data generated by a custom formula that references data stored either in a data warehouse table or in the Pharmacy cube. The custom formula will compare time periods across multiple products, categories, and employees.
You have the following requirements:
Ensure that only the HR department report writers can access the custom formula.
Implement only one dataset.
Ensure that the dataset references the data source that will provide the fastest
data retrieval.
You need to meet the requirements to support the HR department report writers.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
---

• A.Create, deploy, and secure a shared dataset that references the SSAS database shared data source and includes a calculated member for the custom formula.
• B.Create, deploy, and secure a shared dataset that references the data warehouse shared data source and includes custom Transact-SQL (T-SQL) code for the custom formula.
• C.Create and secure in the data warehouse a stored procedure that implements the custom formula. Create and deploy a shared dataset that references the data warehouse shared data source.
• D.Create a calculated member in the Pharmacy cube. Create, deploy, and secure a shared dataset that references the SSAS database shared data source and includes the calculated member from the cube.

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 32 GB of RAM.
The source data, located in a SQL Server data warehouse, is currently using 10 terabytes of disk space and is doubling in size every three months. 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?

• A.tabular project that uses the In-Memory query mode
• B.PowerPivot workbook
• C.tabular project that uses the DirectQuery query mode
• D.multidimensional project

A group of report writers develop reports. The report writers currently use Report Builder
1.0 and report models. The report writers are not trained on SQL Server query writing. Their report assignments come from various departments within the organization.
The company is upgrading to SQL Server 2012 with SQL Server Reporting Services (SSRS) in native mode. After the upgrade, reports will retrieve data from a large new data warehouse that will reside on an instance of the SQL Server 2012 Database Engine. Reports for each department will retrieve data from only a subset of the data warehouse tables.
You are designing the data access strategy. You have the following requirements:
- Ensure that report writers can create only reports that display data which they have database permissions to view. - Minimize the effort required to update connection strings for all reports developed by the report writers. - Minimize the number of fields visible in a specific report for report writers who work for multiple departments.
You need to design the data access strategy.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A.Create one shared data source that uses integrated security. Create one shared dataset that includes all tables required by the report writers.
• B.Create one shared data source that uses integrated security. Create a shared dataset for each department's data requirements.
• C.Create report models by using SQL Server Data Tools (SSDT). Include data sources and data source views for each department's data requirements. Configure each data source to use integrated security.
• D.Create one shared data source that uses integrated security. Create an embedded dataset for each report.

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.)

Correct Answer: