[Free] 2018(Aug) Dumps4cert Microsoft 70-470 Dumps with VCE and PDF Download 151-160

Dumps4cert.com : Latest Dumps with PDF and VCE Files
2018 Aug Microsoft Official New Released 70-470
100% Free Download! 100% Pass Guaranteed!

Recertification for MCSE: Business Intelligence

Question No: 151 DRAG DROP – (Topic 9)

You have a business intelligence (BI) infrastructure.

You create a SQL Server Integration Services (SSIS) project that contains the packages for the BI infrastructure.

You need to automate the deployment of the SSIS project. The solution must ensure that the deployment can occur as part of an unattended Transact-SQL script.

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.

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Question No: 152 HOTSPOT – (Topic 9)

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.

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Question No: 153 DRAG DROP – (Topic 9)

You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table.

You have the following requirements:

->The fact table must populate a column named TotalCost that computes the sum of the price and tax columns.

->Before the sum is calculated, any records that have a price of zero must be

discarded.

You need to create the SSIS package in SQL Server Data Tools.

In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.)

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation:

Box 1: Ole DB Source Box 2: Conditional Split

Disregard lines with a 0 sum. Box 3: Derived column

Box 4: Ole DB Destination

Note:

  • You configure a Data Flow task by adding components to the Data Flow tab. SSIS supports three types of data flow components:

    Sources: Where the data comes from Transformations: How you can modify the data Destinations: Where you want to put the data

  • Creating a data flow includes the following steps:

/ Adding one or more sources to extract data from files and databases, and add connection managers to connect to the sources.

/ Adding the transformations that meet the business requirements of the package. A data flow is not required to include transformations.

Some transformations require a connection manager. For example, the Lookup transformation uses a connection manager to connect to the database that contains the lookup data.

/ Connecting data flow components by connecting the output of sources and

transformations to the input of transformations and destinations.

/ Adding one or more destinations to load data into data stores such as files and databases, and adding connection managers to connect to the data sources.

/ Configuring error outputs on components to handle problems.

At run time, row-level errors may occur when data flow components convert data, perform a lookup, or evaluate expressions. For example, a data column with a string value cannot be converted to an integer, or an expression tries to divide by zero. Both operations cause errors, and the rows that contain the errors can be processed separately using an error flow.

/ Include annotations to make the data flow self-documenting.

  • The capabilities of transformations vary broadly. Transformations can perform tasks such as updating, summarizing, cleaning, merging, and distributing data. You can modify values in columns, look up values in tables, clean data, and aggregate column values.

  • The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved.

Addition of a Data Flow task to a package control flow makes it possible for the package to extract, transform, and load data.

A data flow consists of at least one data flow component, but it is typically a set of connected data flow components: sources that extract data; transformations that modify, route, or summarize data; and destinations that load data.

Question No: 154 – (Topic 9)

You are designing an extract, transform, load (ETL) process for loading data from a SQL Server database into a large fact table in a data warehouse each day with the prior day#39;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.

->Ensure that data loads correctly.

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

  1. Partition the destination fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table.

  2. Partition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data.

  3. Partition the destination fact table by customer. Use partition switching both to remove old data and to load new data into each partition.

  4. Partition the destination fact table by date. Use partition switching and a staging table to remove old data. Insert new data directly into the fact table.

Answer: B

Question No: 155 – (Topic 9)

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

  1. Create report models by using SQL Server Data Tools (SSDT). Include data sources and data source views for each department#39;s data requirements. Configure each data

    source to use integrated security.

  2. Create one shared data source that uses integrated security. Create an embedded dataset for each report.

  3. Create one shared data source that uses integrated security. Create one shared dataset that includes all tables required by the report writers.

  4. Create one shared data source that uses integrated security. Create a shared dataset for each department#39;s data requirements.

Answer: D

Question No: 156 DRAG DROP – (Topic 9)

You are developing a SQL Server Integration Services (SSIS) package to add a large amount of data to a fact table named factOrders. The factOrders table is partitioned by week and has a clustered index.

You need to load three weeks#39; data into the factOrders table by using the most efficient method.

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.

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation:

Box 1:

Dumps4Cert 2018 PDF and VCE

Box 2:

Dumps4Cert 2018 PDF and VCE

Box 3:

Dumps4Cert 2018 PDF and VCE

Note:

  • Need temporary tables.

  • Can load data into the temporary tables in parallel

  • ALTER TABLE

/ SWITCH

SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_ partition_number_expression ]

Switches a block of data in one of the following ways:

Reassigns all data of a table as a partition to an already-existing partitioned table. Switches a partition from one partitioned table to another.

Reassigns all data in one partition of a partitioned table to an existing non-partitioned table.

Question No: 157 – (Topic 9)

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

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

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

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

  4. Create, deploy, and secure a shared dataset that references the SSAS database shared data source and includes a calculated member for the custom formula.

Answer: B

Question No: 158 DRAG DROP – (Topic 9)

You are designing a self-service business intelligence and reporting environment.

Business analysts will create and publish PowerPivot for Microsoft Excel workbooks and create reports by using SQL Server Reporting Services (SSRS) and Power View. When the data models become more complex and the data volume increases, the data models will be replaced by IT-hosted server-based models.

You have the following requirements:

->Maintain the self-service nature of the reporting environment.

->Reuse existing reports.

->Add calculated columns to the data models.

You need to create a strategy for implementing this process. 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.)

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation:

Box 1: Import

Box 2: tabular

Box 3: Deploy

Box 4: Modify

You can see that the audiences for these two approaches are different: Tabular models are for IT professionals, while PowerPivot models are for business users. And keep in mind you can import a PowerPivot workbook to create a new tabular product. For example, a business user might create a solution using PowerPivot. By restoring the workbook, IT can add features that are not supported in PowerPivot, such as roles to grant read permission to different groups of users. Additionally, recognition by IT that the model is important to the organization may prompt them to import the workbook into a tabular project and take ongoing responsibility for developing the model.

Question No: 159 DRAG DROP – (Topic 9)

You are designing a SQL Server Reporting Services (SSRS) solution.

An existing report aggregates data from a SQL Azure database in a chart.

You need to use the chart in a new report and ensure that other users can use the chart in their reports.

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

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

Explanation:

Box 1:

Dumps4Cert 2018 PDF and VCE

Box 2:

Dumps4Cert 2018 PDF and VCE

Box 3:

Dumps4Cert 2018 PDF and VCE

Note:

  • In Report Designer, after you create tables, charts, and other report items in a project, you can publish them asreport partsto a report server or SharePoint site integrated with a report server so that you and others can reuse them in other reports.

  • . By using Report Builder, you can customize and update reports that were created in SQL Server Data Tools (SSDT) Report Designer.

  • InReport Builder, IT pros and power users can create powerful operational reports, and reusable report parts and shared datasets.

    Incorrect:

  • (incorrect) Power View, a feature of SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Server 2010 Enterprise Edition, is an interactive data exploration, visualization, and presentation experience. It provides intuitive ad-hoc reporting for business users such as data analysts, business decision makers, and information workers. They can easily create and interact with views of data from data models based on PowerPivot workbooks published in a PowerPivot Gallery, or tabular models deployed to SQL Server 2012 Analysis Services (SSAS) instances. Power View is a browser-based Silverlight application launched from SharePoint Server 2010 that enables users to present and share insights with others in their organization through interactive presentations.

Question No: 160 HOTSPOT – (Topic 9)

You are developing a SQL Server Analysis Services (SSAS) tabular project. The DirectQuery Mode property of the data model has been set to On. The current partition for the table is configured as the DirectQuery partition.

Data is loaded into a data mart throughout the day and must be available in the data model as soon as possible. The model must support querying by clients that issue both Data Analysis Expressions (DAX) queries and Multidimensional Expressions (MDX) queries.

Clients issuing DAX queries must receive real-time query results.

You need to select the appropriate Query Mode property for deployment. Which query mode should you select?

To answer, select the appropriate query mode from the drop-down list in the dialog box.

Dumps4Cert 2018 PDF and VCE

Dumps4Cert 2018 PDF and VCE

Answer:

Dumps4Cert 2018 PDF and VCE

100% Dumps4cert Free Download!
70-470 PDF
100% Dumps4cert Pass Guaranteed!
70-470 Dumps

Dumps4cert ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.