[2021-New] Microsoft 70-470 Dumps With Update Exam Questions (51-60)

70-470 Dumps

70-470 Exam Questions - Online Test

70-470 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. DRAG DROP - (Topic 10)

You have a single SQL Server 2008 R2 Analysis Services (SSAS) instance. You are planning to upgrade the instance to SQL Server 2012.

You need to import an existing PowerPivot workbook to create a tabular project.

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

Answer:

Q2. - (Topic 10)

You are developing a BI Semantic Model (BISM) based on a simple and small dataset sourced from SQL Server. The data size and complexity of the data relationships will not change. The model will be used to produce reports in Power View. The reports will show the relationship between product sales and rainfall over time.

You need to use an appropriate project type.

Which project types should you use? (Each answer presents a complete solution. Choose all that apply.)

A. a tabular project that uses the DirectQuery query mode

B. a tabular project that uses the In-Memory query mode

C. a multidimensional project that uses the ROLAP storage mode and columnstore indexes

D. a multidimensional project that uses the MOLAP storage mode and proactive cache

E. a PowerPivot workbook that is deployed to Microsoft SharePoint Server 2010

Answer: A,B,E

Explanation: Power View is a thin web client that launches right in the browser from a data model in SharePoint Server 2010. The model can be a PowerPivot model workbook or a tabular model running on a SQL Server 2012 Analysis Services (SSAS) server.

Q3. HOTSPOT - (Topic 9)

You are designing aggregations for a SQL Server Analysis Services (SSAS) cube. You need to ensure that every aggregation excludes the Category attribute. Which option should you select?

To answer, select the appropriate option in the answer area.

Answer:

Q4. - (Topic 6)

You need to design the dimCustomers table.

Which design approach should you use?

A. Reference dimension

B. Type 2 slowly changing dimension

C. Junk dimension

D. Conformed dimension

E. Type 1 slowly changing dimension

Answer: B Topic 7, Contoso, Ltd Case B

General Background You are the business intelligence (BI) solutions architect for Contoso, Ltd, an online retailer.

You produce solutions by using SQL Server 2012 Business Intelligence edition and Microsoft SharePoint Server 2010 Service Pack 1 (SP1) Enterprise edition.

A SharePoint farm has been installed and configured for intranet access only. An Internet-facing web server hosts the company's public e-commerce website. Anonymous access is not configured on the Internet-facing web server.

Data Warehouse The data warehouse is deployed on a 5QL Server 2012 relational database instance. The data warehouse is structured as shown in the following diagram.

The following Transact-SQL (T-SQL) script is used to create the FactSales and FactPopulation tables:

The FactPopulation table is loaded each year with data from a Windows Azure Marketplace commercial dataset. The table contains a snapshot of the population values for all countries of the world for each year. The world population for the last year loaded exceeds

6.8 billion people.

ETL Process SQL Server Integration Services (SSIS) is used to load data into the data warehouse. All SSIS projects are developed by using the project deployment model.

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.

A package named LoadFactFreightCosts sources data from a Windows Azure SQL Database database that has data integrity problems. The package may retrieve duplicate rows from the database.

The package variables of all packages have the RaiseChangedEvent property set to true. A package-level event handler for the OnVariableValueChanged event consists of an Execute SQL task that logs the System::VariableName and System::VariableValue variables.

Data Models SQL Server Analysis Services (SSAS) is used to host the Corporate BI multidimensional database. The Corporate BI database contains a single data source view named Data Warehouse. The Data Warehouse data source view consists of all data warehouse tables. All data source view tables have been converted to named queries.

The Corporate BI database contains a single cube named Sales Analysis and three database dimensions: Date, Customer and Product. The dimension usage for the Sales Analysis cube is as shown in the following image.

The Customer dimension contains a single multi-level hierarchy named Geography. The structure of the Geography hierarchy is shown in the following image.

The Sales Analysis cube's calculation script defines one calculated measure named Sales Per Capita. The calculated measure expression divides the Revenue measure by the Population measure and multiplies the result by 1,000. This calculation represents revenue per 1,000 people.

The Sales Analysis cube produces correct Sales Per Capita results for each country of the world; however, the Grand Total for all countries is incorrect, as shown in the following image (rows 2-239 have been hidden).

A role named Analysts grants Read permission for the Sales Analysis cube to all sales and marketing analysts in the company.

SQL Server Reporting Services (SSRS) is configured in SharePoint integrated mode. All reports are based on shared data sources.

Corporate logo images used in reports were originally configured as data-bound images sourced from a SQL Server relational database table. The image data has been exported to JPG files. The image files are hosted on the Internet-facing web server. All reports have been modified to reference the corporate logo images by using the fully qualified URLs of the image files. A red X currently appears in place of the corporate logo in reports.

Users configure data alerts on certain reports. Users can view a report named Sales Profitability on demand; however, notification email messages are no longer being sent when Sales Profitability report data satisfies alert definition rules. The alert schedule settings for the Sales Profitability report are configured as shown in the following image.

Business Requirements Data Models

Users must be able to: . Provide context to measures and filter measures by using all related data warehouse dimensions. . Analyze measures by order date or ship date.

Additionally, users must be able to add a measure named Sales to the report canvas by clicking only once in the Power View field list. The Sales measure must allow users to analyze the sum of the values in the Revenue column of the FactSales data warehouse table. Users must be able to change the aggregation function of the Sales measure.

Analysis and Reporting A sales manager has requested the following query results from the Sales Analysis cube for the 2012 fiscal year:

. Australian postal codes and sales in descending order of sales. . Australian states and the ratio of sales achieved by the 10 highest customer sales made for each city in that state.

Technical Requirements ETL Processes If an SSIS package variable value changes, the package must log the variable name and the new variable value to a custom log table.

The StageFactSales package must load the contents of all files that match the file name pattern. The source file name must also be stored in a column of the data warehouse staging table. In the design of the LoadFactSales package, if a lookup of the dimension surrogate key value for the product code fails, the row details must be emailed to the data steward and written as an error message to the SSIS catalog log by using the public API.

You must configure the LoadFactFreightCosts package to remove duplicate rows, by using the least development effort.

Data Models Users of the Sales Analysis cube frequently filter on the current month's data. You must ensure that queries to the Sales Analysis cube default to the current month in the Order Date dimension for all users.

You must develop and deploy a tabular project for the exclusive use as a Power View reporting data source. The model must be based on the data warehouse. Model table names must exclude the Dim or Fact prefixes. All measures in the model must format values to display zero decimal places.

Analysis and Reporting Reports must be developed that combine the SSIS catalog log messages with the package variable value changes.

Q5. DRAG DROP - (Topic 3)

You need to configure the attribute relationship types for the Salesperson dimension.

Which configuration should you use?

To answer, drag the appropriate pair of attributes and attribute relationships from the list to the correct location or locations in the answer area. (Answer choices may be used once, more than once, or not all.)

Answer:

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

Answer:

Q7. DRAG DROP - (Topic 9)

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

Answer:

Q8. - (Topic 9)

You need to design a data warehouse schema to support a multidimensional cube. The fact table will contain multiple columns representing order dates, shipping dates, and billing dates. The cube will contain a measure group based on the fact table.

What is the best design to achieve the goal? More than one answer choice may achieve the goal. Select the BEST answer.

A. In the database, create a time table for each date column in the fact table.

From the data source view, join each date column in the fact table to the corresponding

time table.

Create dimensions for each time table.

B. In the database, create a time table containing a column that corresponds to each date

column in the fact table.

From the data source view, join each date column in the fact table to the corresponding

column in the time table.

Create a dimension for the time table.

C. In the database, create a time table that uses an integer primary key and a datetime

column.

Create a dimension based on the time table.

From the Cube Designer, use the Dimension Usage tab to define a relationship between

the measure group date columns and the datetime column of the time dimension.

D. In the database, create a time table that uses an integer primary key and a datetime

column.

Create a dimension based on the time table.

From the Cube Designer, use the Dimension Usage tab to define a relationship between

the measure group date columns and the key column of the time dimension.

Answer: B

Q9. - (Topic 5)

You need to implement the aggregation designs for the cube.

What should you do?

A. Use the CREATE CACHE statement.

B. Use the Aggregation Design Wizard.

C. Create relational indexes on the source tables.

D. Use the Usage-Based Optimization Wizard.

Answer: B

Q10. - (Topic 10)

You need to use an appropriate project type.

Which project types should you use? (Each answer presents a complete solution. Choose all that apply.)

A. a tabular project that uses the DirectQuery query mode

B. a tabular project that uses the In-Memory query mode

C. a multidimensional project that uses the ROLAP storage mode

D. a PowerPivot workbook that is deployed to Microsoft SharePoint Server 2010

E. a multidimensional project that uses the MOLAP storage mode

Answer: A,B,E