Online Access Free 070-467 Practice Test

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.

You develop a SQL Server Integration Services (SSIS) project named Project1 by using SQL Server Data Tools (SSDT). Project1 contains a package named Package1.
You add a project parameter named EnvironmentText to Project1.
In SQL Server Management Studio (SSMS), you create the SSIS catalog.
You have the following requirements:
* Configure Package1 so it can run within either a development environment or a test environment.
* Pass the value of an SSIS environment variable to the EnvironmentText project parameter.
* The value of the environment variable must be different for each SSIS environment.
You need to deploy the SSIS project and configure the SSIS environment.
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:

You are developing packages in a SQL Server Integration Services (SSIS) project to load a data warehouse.
You are designing a method for configuring the packages' connections when deployed to a production SSIS catalog. All packages load data from two source databases.
Two packages also load data from a third source database. Another SSIS project will use the same connections.
You need to meet the requirements.
What should you do?
To answer, drag the appropriate term or terms to the correct location or locations in the answer area. (Each term may be used once, more than once, or not at all.)

Correct Answer:

Explanation

You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.
You have the following requirements:
* Ensure that the deployment model supports changing the content of connection strings by using parameters at execution time.
* Ensure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database.
* Maximize performance at execution time.
* Minimize development effort.
You need to design a solution that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A.Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments.
• B.Use a project deployment model. Modify connection manager properties to use project parameters.
  Ensure that the SSISDB database is created.
• C.Use a project deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
• D.Use a package deployment model. Use a SQL Server package configuration with a common filter.
  Change the contents of the SSIS Configurations table at runtime.

You are designing a SQL Server Analysis Services (SSAS) data model on a very large data warehouse.
The fact tables in the data warehouse contain terabytes of data in tens of billions of rows.
You must support the following features:
* Complex attribute/column relationships
* Advanced calculations in the data model definition
* Advanced calculations using logic deployed in a custom assembly
You need to choose the correct SSAS design strategy.
What should you do?
To answer, drag the appropriate term or terms to the correct location or locations in the answer area. (Answer choices may be used once, more than once, or not all.)

Correct Answer:

Explanation

Note:
Box 1:
The primary reason for building an Analysis Services multidimensional model is to achieve fast performance of ad hoc queries against business data. A multidimensional model is composed of cubes and dimensions that can be annotated and extended to support complex query constructions.
Box 2:
A partition is a container for a portion of the measure group data. Partitions are not seen from MDX queries; all queries reflect the whole content of the measure group, regardless of how many partitions are defined for the measure group. The data content of a partition is defined by the query bindings of the partition, and by the slicing expression.
Box 3:
Multidimensional Expressions (MDX) is the query language that you use to work with and retrieve multidimensional data in Microsoft SQL Server 2005 Analysis Services (SSAS).