we provide Pinpoint Microsoft 70-470 exam which are the best for clearing 70-470 test, and to get certified by Microsoft Recertification for MCSE: Business Intelligence. The 70-470 Questions & Answers covers all the knowledge points of the real 70-470 exam. Crack your Microsoft 70-470 Exam with latest dumps, guaranteed!
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Microsoft 70-470 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Free Instant Download NEW 70-470 Exam Dumps (PDF & VCE):
Available on:
http://www.surepassexam.com/70-470-exam-dumps.html
Q71. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) multidimensional database.
The underlying data source does not have a time dimension table. You need to implement a time dimension.
What should you do?
A. Add an existing SSAS database time dimension as a cube dimension.
B. Use the SQL Server Data Tools Dimension Wizard and generate a time table on the server.
C. Use the SQL Server Data Tools Dimension Wizard and generate a time table in the data source.
D. Use the SQL Server Data Tools Dimension Wizard and generate a time dimension by using the Use an existing table option.
E. Create a CSV file with time data and use the DMX IMPORT statement to import data from the CSV file.
F. Create a time dimension by using the Define time intelligence option in the Business Intelligence Wizard.
G. Create a time dimension by using the Define dimension intelligence option in the Business Intelligence Wizard.
H. Create a script by using a sample time dimension from a different multidimensional database. Then create a new dimension in an existing multidimensional database by executing the script.
Answer: B
Q72. - (Topic 9)
A company 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 Sales. Shared data sources exist in SSRS for the relational and SSAS databases. Each company department has its own report writers.
Report writers in the Marketing 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 Sales cube. The custom formula will compare time periods across multiple products, categories, and regions.
You have the following requirements:
. Ensure that only Marketing 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 Marketing department report writers.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create, deploy, and secure a shared dataset that references the SSAS database shared data source and includes a calculated member for the custom formula.
B. Create a calculated member in the Sales cube. Create, deploy, and secure a shared dataset that references the SSAS database shared data source and includes the calculated member from the cube.
C. 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.
D. 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.
Answer: B
Q73. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) cube for the sales department at your company.
The sales department requires the following set of metrics:
Unique count of customers
Unique count of products sold
Sum of sales
You need to ensure that the cube meets the requirements while optimizing query response time.
What should you do? (Each answer presents a complete solution. Choose all that apply.)
A. Use the Define Semiadditive Behavior page to enable semi-additive behavior.
B. Use ROLAP storage for all partitions.
C. Use the Define Semiadditive Behavior page to disable semi-additive behavior.
D. Use the Distinct Count and Sum measure aggregation functions.
E. Place the distinct count measures in separate measure groups.
F. Create a single measure group containing all measures
Answer: A,E
Explanation: A: Semiadditive Function Select the aggregation function for the selected measure. The aggregate functions available include DistinctCount, Aggregated using the DistinctCount function. E (not D, Not F): Typically, the best performance occurs when each distinct count measure is in its own measure group, and that measure group has the same dimensionality as the initial measure group.
Q74. - (Topic 8)
You are deploying the Research model.
You need to ensure that the data contained in the model can be refreshed.
What should you do?
A. Import MarketResearch.xlsx to a new tabular database on the Tabular instance.
B. Assign the Tabular instance service account permissions to the MarketResearch.xlsx network location.
C. Create a SQL Server Integration Services (SSIS) package that imports data from MarketResearch.xlsx nightly. Load the data to CUBE1.
D. Upload MarketResearch.xlsx to Library1.
Answer: B
Q75. - (Topic 5)
You need to design a cube partitioning strategy to be implemented as the cube size increases.
What should you do?
A. Use relational OLAP (ROLAP) on all local partitions.
B. Implement monthly remote partitions.
C. Use multidimensional OLAP (MOLAP) on all local partitions.
D. Implement monthly local partitions.
Answer: B
Q76. DRAG DROP - (Topic 10)
You are using Multidimensional Expressions (MDX) to query a SQL Server Analysis Services (SSAS) cube.
You need to compute the aggregate value of the 10 most-ordered produces in the Product Categories hierarchy. The Product level is the lowest in the hierarchy.
Which functions should you use to complete the MDX query? (To answer, drag the appropriate functions from the list of functions to the correct locations in the answer area.)
Answer:
Q77. 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:
Q78. - (Topic 10)
You are creating a SQL Server Analysis Services (SSAS) multidimensional database.
Users need a time dimension for:
Dates
Delivery dates
Ship dates
...
You need to implement the minimum number of required SSAS objects.
What should you do?
A. Use role playing dimensions.
B. Use the Business Intelligence Wizard to define dimension intelligence.
C. Add a measure that uses the Count aggregate function to an existing measure group.
D. Add a measure that uses the DistinctCount aggregate function to an existing measure group.
E. Add a measure that uses the LastNonEmpty aggregate function. Use a regular relationship between the time dimension and the measure group.
F. Add a measure group that has one measure that uses the DistinctCount aggregate function.
G. Add a calculated measure based on an expression that counts members filtered by the Exists and NonEmpty functions.
H. Add a hidden measure that uses the Sum aggregate function. Add a calculated measure aggregating the measure along the time dimension.
I. Create several dimensions. Add each dimension to the cube.
J. Create a dimension. Then add a cube dimension and link it several times to the measure group.
K. Create a dimension. Create regular relationships between the cube dimension and the measure group. Configure the relationships to use different dimension attributes.
L. Create a dimension with one attribute hierarchy. Set the XsAggregatable property to False and then set the DefaultMember property. Use a regular relationship between the dimension and measure group.
M. Create a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a many-to-many relationship to link the dimension to the measure group.
N. Create a dimension with one attribute hierarchy. Set the ValueColumn property, set the IsAggregatable property to False, and then set the DefaultMember property. Configure the cube dimension so that it does not have a relationship with the measure group. Add a calculated measure that uses the MemberValue attribute property.
O. Create a new named calculation in the data source view to calculate a rolling sum. Add a measure that uses the Max aggregate function based on the named calculation.
Answer: A
Q79. HOTSPOT - (Topic 9)
You have an OLAP cube named Cube1. Cube1 has two partitions named partition1 and partition2.
You need to configure the storage mode for each cube partition.
The solution must meet the following requirements:
. Users must be able to retrieve aggregate data from partition1 as quickly as
possible.
. The processing time for partition1 and partition2 must be minimized.
. The least possible amount of storage must be used for partition2.
What should you do? To answer, select the appropriate storage mode for each partition in the answer area.
Answer:
Q80. HOTSPOT - (Topic 9)
You are developing a SQL Server Analysis Services (SSAS) cube. A dimension named Category is based on the DimCategory table.
A subset of the data source view is shown in the following graphic.
You need to relate the Category dimension to the Sales measure group.
Which relationship type should you choose?
To answer, select the appropriate option from the drop-down list in the dialog box.
Answer:
