70-470 Exam
What Refresh 70-470 Is?
Exam Code: 70-470 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Recertification for MCSE: Business Intelligence
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-470 Exam.
2021 Jan 70-470 exam answers
Q131. DRAG DROP - (Topic 9)
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.)
Answer:
Q132. HOTSPOT - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) cube.
You are writing the following Multidimensional Expressions (MDX) statement for use by a calculated measure. The measure computes the sales amount for the same time period of
the previous year. (Line numbers are included for reference only.)
You need to complete the MDX statement.
Which MDX function should you use in line 03? To answer, select the appropriate MDX function in the functions list.
Answer:
Q133. - (Topic 10)
You are creating a new report in SQL Server Report Builder. You add a SQL Azure data
source. Then you add a dataset that has four fields named Year, Country, Category, and Sales.
You must design a matrix as shown in the following diagram.
The category rows (the first three rows as shown in the diagram) must present total sales amount by category. The country rows (the next six rows as shown in the diagram) must present total sales amount by country. The total row must present the total sales for each year.
You add a matrix to the report. You add a grouping of the Category field on the rows and a grouping of the Year field on the columns.
You need to add the countries on the rows of the matrix.
Which Row Group option should you select when you add the group?
A. Adjacent Below
B. Parent Group
C. Adjacent Above
D. Child Group
Answer: A
Q134. - (Topic 7)
You need to ensure that the Sales Per Capita calculated measure produces correct results.
What should you do?
A. Set the DataType property of the Population column of the FactPopulation data source view table to System.Int64.
B. Set the Source DataType property of the Population measure to Biglnt.
C. Set the data type of the Population column of the FactPopulation data warehouse table to BIGINT.
D. Set the DataType property of the Population measure to Biglnt.
Answer: B
Q135. - (Topic 4)
You need to design the recovery strategy for SSRS01.
What should the strategy include? (Each correct answer presents part of the solution. Choose all that apply.)
A. Re-create the SQL Server Agent jobs that are used to trigger schedules.
B. Restore the ReportServer and ReportServerTempDB databases with recovery.
C. Restore the ReportServer and ReportServerTempDB databases with no recovery.
D. Restore the msdb database.
E. Restore the Report Server encryption key.
F. Restore the database encryption key.
Answer: A,B,E
39. - (Topic 4)
You need to re-establish subscriptions on SSRS01.
What should you do?
A. Start the SQL Server Agent on SSRS01.
B. Restore the ReportServer database.
C. Restore the ReportServerTempDB database.
D. Use the SQL Server Configuration Manager to reset the SQL Service account credentials.
Answer: A
Regenerate 70-470 free exam questions:
Q136. - (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].
Example:
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
http://4.bp.blogspot.com/_ntCExJSVw8o/TKm091Wyh_I/AAAAAAAAMV8/dzpwjOPJ_wI/s4 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.
Q137. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project. The model includes a table named Sales. The Sales table includes a single date column.
The Sales table must meet the following requirements:
. Queries must be able to return all rows. . Must be able to support four different processing schedules for different date ranges. . Date ranges must not include any overlapping data.
You need to implement a solution that meets the requirements.
What should you do?
A. Create four partitions for the Sales table. Use row filter queries for each partition.
B. Convert the Sales table into four smaller tables by using row filter queries. Use one perspective for all four tables.
C. Create four partitions for the Sales table. Create four roles. Use the same row filter queries for each role and partition.
D. Convert the Sales table into four smaller tables by using row filter queries. Use one perspective for each of the four tables.
Answer: A
Q138. - (Topic 4)
You need to use SQL Server Management Studio (SSMS) to make the SSAS databases available for application testing.
What should you do?
A. Restore the SSAS databases from the latest backup to SSAS01.
B. Script the databases as a Create script to a new window and then execute the script on SSAS01.
C. Detach the SSAS databases from the SSASCluster, and then attach them to SSAS01.
D. Use the Import/Export Wizard to copy the databases from the production server to the development server.
Answer: A
Q139. DRAG DROP - (Topic 9)
A company has a data warehouse that is rapidly increasing in size.
You plan to improve query performance by partitioning a very large fact table.
You need to identify the columns that are good partitioning candidates and the range that
each partition should include.
Which component should you add to the package?
To answer, drag the appropriate component from the list of items to the answer area.
Answer:
Q140. DRAG DROP - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project based on a SQL Azure database. The ProcessingOption property for the project is set to Do Not Process.
Several calculated columns have been added to a table. The project has been deployed to the production server.
You need to ensure that newly added data is processed on the production server.
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: