70-470 Exam
How Does Ucertify Microsoft 70-470 actual exam Work?
Exambible 70-470 Questions are updated and all 70-470 answers are verified by experts. Once you have completely prepared with our 70-470 exam prep kits you will be ready for the real 70-470 exam without a problem. We have Up to date Microsoft 70-470 dumps study guide. PASSED 70-470 First attempt! Here What I Did.
Q1. - (Topic 6)
You need to ensure that you can monitor the usage data.
What should you do?
A. In SharePoint Central Administration, enable usage data collection.
B. In the PowerPivot Configuration Tool, repair PowerPivot for SharePoint.
C. In the PowerPivot Configuration Tool, upgrade PowerPivot for SharePoint.
D. In Reporting Services Configuration Manager, enable report execution logging.
Answer: A
Q2. - (Topic 9)
You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of
business hours. Most queries are generated by reports and by cube processing. Data is
frequently queried at the day level and occasionally at the month level.
You need to partition the table to maximize the performance of queries.
What should you do? (More than one answer choice may achieve the goal. Select the
BEST answer.)
A. Partition the fact table by month, and compress each partition.
B. Partition the fact table by week.
C. Partition the fact table by year.
D. Partition the fact table by day, and compress each partition.
Answer: D
Q3. - (Topic 5)
You need to implement the product dimension. What should you do?
A. In the data warehouse, create a product dimension from a view that joins the Product and ProductColor tables in the financial database and contains product name and product color attributes.
B. In the data warehouse, create a dimension table that contains product name and a dimension table that contains product color.
C. In the data warehouse, create a product dimension table that contains product name and product color.
D. In the cube, create a named query that joins the Product and ProductColor tables in the financial database.
Answer: C
Q4. DRAG DROP - (Topic 9)
You are the administrator of a SQL Server Integration Services (SSIS) catalog. You have access to the original password that was used to create the SSIS catalog.
A full database backup of the SSISDB database on the production server is made each day. The server used for disaster recovery has an operational SSIS catalog.
Information that is encrypted in the SSISDB database must not be lost.
You need to document the disaster recovery solution that restores the production SSIS catalog to the disaster recovery server.
Which three steps should you document 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:
Q5. DRAG DROP - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) multidimensional project. The project file includes two cubes named Finance and Operations. The project also includes a dimension named Date. The Date dimension includes two hierarchies named Fiscal and Calendar. The Date dimension has been added to both cubes.
You need to disable the Fiscal hierarchy in the Operations cube without impacting other database objects.
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:
Q6. - (Topic 10)
... .
You work in the Business Intelligence (BI) department of a multinational company.
The company has requested a new corporate BI solution that meets the following requirements:
The solution must use SQL Server Analysis Services (SSAS).
The model must incrementally add 10 million fact rows per month.
The model must be translated to English, French, or Spanish based on users'
locale.
The model must be able to contain the most recent 36 months of data.
You need to select the appropriate model type and partitioning strategy to meet the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create a multidimensional model with one partition for all of the data.
B. Create a multidimensional model with one partition for each month.
C. Create a tabular model with one partition for all of the data.
D. Create a tabular model with one partition for each month.
Answer: C
Q7. HOTSPOT - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) cube.
Revenue must be compared to a goal and described by a status and a trend. Revenue,
goal, status, and trend will be defined by Multidimensional Expressions (MDX) expressions.
You need to add the Revenue indicator.
Which tab should you select? (To answer, select the appropriate tab in the work area.)
Answer:
Q8. - (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
Q9. - (Topic 9)
You deploy a PowerPivot workbook to a document library in a Microsoft SharePoint site. Workbook data comes from two different sources: Source A and Source B.
The workbook contains three small lookup tables from Source A, and five tables with a total of 20 million rows from Source B. Data from Source A is updated at 9:00 A.M. and data from Source B is updated throughout the day.
You have the following requirements:
. Refresh the PowerPivot workbook with updated lookup data as soon as possible. . Minimize load on the source systems and the SharePoint environment during business hours. . Minimize user involvement in the data refresh process.
You enable automatic data refresh for the workbook.
You need to configure a data refresh schedule for the workbook that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Set the default schedule to refresh outside of business hours. Do not manually refresh the workbook.
B. Set the default schedule to refresh outside of business hours. Manually refresh the workbook at 9:00 A.M. every day.
C. Set the default schedule to refresh at 9:00 A.M. every day. Create a separate schedule for Source B's connection to refresh outside of business hours.
D. Set the default schedule to refresh at 3:00 P.M. every day. Do not create individual source schedules.
E. Set the default schedule to refresh at 9:00 A.M. every day. Do not create individual source schedules.
Answer: C
Q10. - (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.