Microsoft 70-470 ExamRecertification for MCSE: Business Intelligence

Total Question: 316 Last Updated: December 16,2017
  • Updated 70-470 Dumps
  • Based on Real 70-470 Exams Scenarios
  • Free 70-470 pdf Demo Available
  • Check out our 70-470 Dumps in a new PDF format
  • Instant 70-470 download
  • Guarantee 70-470 success in first attempt
Package Select:

Questions & Answers PDF

Practice Test Software

Practice Test + PDF 30% Discount

Price: $65.95 $29.99

Buy Now Free Trial

Top Tested 70-470 questions pool Tips!

It is impossible to pass Microsoft 70-470 exam without any help in the short term. Come to Pass4sure soon and find the most advanced, correct and guaranteed Microsoft 70-470 practice questions. You will get a surprising result by our Updated Recertification for MCSE: Business Intelligence practice guides.

2017 Jan 70-470 test engine

Q1. - (Topic 6) 

You need to create the factCustomerContact table. 

Which type of table should you create? 

A. A fact table with a non-additive measure 

B. A factless fact table 

C. A periodic snapshot fact table 

D. A fact table with an additive measure 


Q2. - (Topic 10) 

You are developing a SQL Server Analysis Services (SSAS) tabular project that will be used by the finance, sales, and marketing teams. 

The sales team reports that the model is too complex and difficult to use. The sales team does not need any information other than sales related resources in the tabular model. The finance and marketing teams need to see all the resources in the tabular model. 

You need to implement a solution that meets the needs of the sales team while minimizing development and administrative effort. 

What should you do? 

A. Create a separate partition for each team. 

B. Create a perspective for the sales team. 

C. Create a separate data source for each team. 

D. Enable client side security to filter non-sales data. 


Q3. - (Topic 10) 

You are developing a SQL Server Analysis Services (SSAS) tabular project. 

You need to grant the minimum permissions necessary to enable users to query data in a tabular model. 

Which role permission should you use? 

A. Browser 

B. ReadDefinition 

C. Read 

D. Process 

E. Explorer 

F. Select 


Q4. - (Topic 10) 

You are developing a SQL Server PowerPivot workbook that sources data from a SQL Azure database. The PowerPivot model includes a single table named FactSales that consists of four columns named Year, Country, Product, and Revenue. The model includes the following two measures. 

. Sales:=SUM(FactSales[Revenue;) 

. Sales %:=;Sales] / CALCULATE([Sales], ALL(FactSales)) 

In Microsoft Excel 2010 you create the following PivotTable report. 

Users report that the Sales % measure computes an incorrect ratio. The measure should meet a requirement to compute a ratio over all visible sales values defined by the query filters. The Grand Total value for the Sales % measure should equal 100%. 

You need to fix the Sales % measure to meet the requirement. 

Which Data Analysis Expressions (DAX) expression should you use? 

A. = [Sales] / CALCULATE([Sales]) 

B. = [sales] / [Sales](ALLSELECTED(FactSales)) 

C. = [sales] / CALCULATE([Sales], VALUES(FactSales[Year]), VALUES(FactSales[Country])) 

D. = [sales] / [Sales](ALLEXCEPT(FactSales, FactSales[Year])) 


Q5. HOTSPOT - (Topic 9) 


You are designing a SQL Server Integration Services (SSIS) package configuration strategy. 

The package configuration must meet the following requirements: Include multiple properties in a configuration. Force packages to load all settings in the configuration. Support Encrypting File System (EFS) formats. You need to select the appropriate configuration. 

Which configuration type should you use? 

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


Improve 70-470 exam cost:

Q6. - (Topic 5) 

You need to slice data by the CouponUsed, OnSale, and Closeout columns. 

What should you do? 

A. Create one linked dimension for each column. 

B. Create one degenerate dimension. 

C. Create one role-playing dimension. 

D. Create one junk dimension. 


Q7. - (Topic 4) 

You need to roll back the compatibility level of the Research database. 

What should you do? 

A. Restore a backup of the previous version of the database. 

B. Use an ALTER DATABASE statement to set the compatibility option. 

C. Change the CompatibilityLevel property in the XMLA script, and then execute the script. 

D. In SQL Server Management Studio (SSMS), change the compatibility level in the database properties. 


Q8. DRAG DROP - (Topic 9) 

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


Q9. - (Topic 2) 

You need to configure the partition storage settings to support the reporting requirements. 

Which partition storage setting should you use? 

A. DirectQuery 

B. In-Memory 


D. Low-latency MOLAP 

E. Scheduled MOLAP 

F. High-latency MOLAP 


Q10. - (Topic 10) 

You are developing a SQL Server Analysis Services (SSAS) tabular project. A model defines a measure named Profit and includes a table named Date. The table includes year, semester, quarter, month, and date columns. The Date column is of data type Date. The table contains a set of contiguous dates. 

You need to create a measure to report on year-over-year growth of profit. 

What should you do? (Each answer presents a complete solution. Choose all that apply.) 

A. Use the Business Intelligence Wizard and then use the Define time intelligence enhancement. 

B. Define the following calculation.Year Over Year Profit Growth:=CALCULATE([Profit], DATEADD('Date'[Date], 1, YEAR)) 

C. Define the following calculation.Year Over Year Profit Growth:=[Profit] -CALCULATE([Profit], SAMEPERIODLASTYEAR('Date'[Date])) 

D. Define the following calculation.Year Over Year Profit Growth:=[Profit] -CALCULATE([Profit], PARALLELPERIOD('Date'[Date], -12, MONTH)) 

Answer: A,D 

Explanation: A: SSAS Provides feature called "Time Intelligence Wizard". This feature will 

provide neat GUI to achieve the same purpose which we were trying by MDX code [using 

the PARALLELPERIOD function]. 


Lets explore the "Time Intelligence Wizard": 

1) In BIDS, Click "Cube" in menu bar and select "Add business Intelligence" 

2) Click "Time Intelligence Wizard" on next screen. 

3) "Choose Target Hierarchy and Calculations".screen 00/TIW_CTH.JPG Etc. 

D: Variance analysis for SSAS OLAP cubes is not a simple matter of adding a calculated field to a pivot table. Planning along with the use of the ParallelPeriod MDX functions allows us to quickly create a variance infrastructure for a particular measure. Furthermore, by utilizing a date hierarchy in the Parallel Period function, we can easily traverse down the hierarchy for any attribute below the parallel period level noted in the function (i.e., parallel period based on Year can show either one year back per year, quarter, or month). Although, other methods exist, the parallel period method can be easily followed and applied to various measures. 

Related 70-470 Articles