70-470 Exam
What Latest 70-470 Is?
Exambible offers free demo for 70-470 exam. "Recertification for MCSE: Business Intelligence", also known as 70-470 exam, is a Microsoft Certification. This set of posts, Passing the Microsoft 70-470 exam, will help you answer those questions. The 70-470 Questions & Answers covers all the knowledge points of the real exam. 100% real Microsoft 70-470 exams and revised by experts!
Q11. 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:
Q12. - (Topic 9)
An existing cube dimension that has 30 attribute hierarchies is performing very poorly. You have the following requirements:
. Implement drill-down browsing. . Reduce the number of attribute hierarchies but ensure that the information contained within them is available to users on demand. . Optimize performance.
You need to redesign the cube dimension to meet the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. set the AggregateFunction property to Sum on all measures. Use the SCOPE statement in a Multidimensional Expressions (MDX) calculation to tune the aggregation types.
B. Set the AttributeHierarchyOptimizedState property to FullyOptimized on the attribute hierarchies.
C. Create user-defined hierarchies. For the attributes sourced by the levels of the user-defined hierarchies, set the RelationshipType property to Rigid. Run incremental processing.
D. Remove as many attribute hierarchies as possible from the dimension. Reintroduce the information in the attribute hierarchies as properties. Implement natural hierarchies and set the AttributeHierarchyVisible property to False for attributes used as levels in the natural
hierarchies.
Answer: D
Q13. - (Topic 2)
You need to configure SSRS to meet the maximum number of snapshots requirement.
What should you do? (Each answer presents a complete solution. Choose all that apply.)
A. In SharePoint Central Administration, set the System Snapshot Limit option to 5.
B. In Reporting Services Configuration Manager, set the System Snapshot Limit option to
5.
C. For each report, set the Limit number of snapshots option to 5.
D. Use PowerShell to set the System Snapshot Limit option to 5.
Answer: A,C
Explanation: A: Reporting Services Service Applications are managed from SharePoint Central Administration. The Management and Properties pages allow you to update the configuration of the service application as well as common administration tasks.
Note:
*
From scenario: The system must support report snapshots. The default maximum number of retained snapshots must not exceed five.
*
System Snapshot Limit Default is -1, which is no limit. Set a site-wide default value for the number of copies of report history to retain. The default value provides an initial setting that establishes the number of snapshots that can be stored for each report. You can specify different limits in property pages for specific reports.
Not B: Use the Reporting Services Configuration Manager to configure a Reporting Services Native Mode installation. If you installed a report server by using the files-only installation option, you must use this tool to configure the server before you can use it. If you installed a report server by using the default configuration installation option, you can use this tool to verify or modify the settings that were specified during setup.
Not D: SQL Server 2012 supports Windows PowerShell, which is a powerful scripting shell that lets administrators and developers automate server administration and application deployment.
Q14. - (Topic 1)
You need to create a measure for DOD sales.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Specify a date table by using a Mark as Date table.
B. Use the Data Analysis Expressions (DAX) PARALLELPERIOD() function.
C. Use the Business Intelligence Wizard to define time intelligence.
D. Use the Multidimensional Expressions (MDX) LAG() function.
Answer: A,C
Explanation: * From scenario:
A measure must be created to calculate day-over-day (DOD) sales by region based on
order date.
A: Specify Mark as Date Table for use with Time Intelligence (SSAS Tabular)
In order to use time intelligence functions in DAX formulas, you must specify a date table
and a unique identifier (datetime) column of the Date data type. Once a column in the date
table is specified as a unique identifier, you can create relationships between columns in
the date table and any fact tables.
C: The time intelligence enhancement is a cube enhancement that adds time calculations
(or time views) to a selected hierarchy. This enhancement supports the following
categories of calculations:
Period to date.
Period over period growth.
Moving averages.
Parallel period comparisons.
Topic 2, Contoso Ltd
Background You are the business intelligence (BI) solutions architect for Contoso Ltd, a multinational sales company with offices in London, Madrid, Paris, Brisbane, Tokyo, and New York. Contoso sells office consumable products such as pens, printer ink, and paper.
You produce solutions by using SQL Server 2012 Business Intelligence Edition and Microsoft SharePoint Server 2010 Enterprise Edition with SP1.
Technical Background Contoso's products are categorized by using four levels while some use only two or three levels. Products are categorized as shown in the following table.
Contoso sells products through mobile sales staff, direct marketing, and its website. Sales personnel are located in various regions around the world, and each region has a sales manager who is paid a quarterly bonus based on the total sales in the region during the quarter. Regions are categorized as shown in the following table.
SQL Server Analysis Services (SSAS) is used to host a multidimensional database. The database contains a single cube named Sales and three database dimensions named Products, Regions, and Date. A single measure named Sales Total has been defined in the cube. The data source for the database is a SQL Server data warehouse.
The Products dimension contains a single user-defined hierarchy named Products. To prevent the display of empty members when users browse the Products dimension, the Extract, Transform, and Load (ETL) process populates all missing values as shown in the following diagram.
The structure of the Products hierarchy is shown in the following diagram.
The Regions dimension contains a single user-defined hierarchy named Sales Regions. The dimension is based on a single dimension table in the data warehouse and the attribute relationships have not been modified since the dimension was created by using the Dimension wizard. The structure of the Sales Regions hierarchy is shown in the following diagram.
The Date dimension contains a single user-defined hierarchy named Calendar. The structure of the Calendar hierarchy is shown in the following diagram.
A role named UserRegions has been created in the SSAS database that will be used to filter members in the Regions dimension based on the authenticated user.
Administrative staff from around the world will produce sales reports with Microsoft Excel 2010 based on the Sales cube.
Developers will produce reports with SQL Server Reporting Services (SSRS) based on the Sales cube and the reports will be delivered to users through report subscriptions and a web browser. All users log on to an Active Directory Domain Services (AD DS) domain named contoso.com. All client computers and servers are joined to the contoso.com domain.
Business Requirements The BI system must meet the following reporting requirements: . Display all sales figures in euro currency, regardless of the client's reporting location . Include a new measure named AD Sales that calculates average daily sales for a selected month . Support near real-time reporting while maintaining good performance for
multidimensional queries . Support reports that show currency exchange rates . Deliver executive reports that are parameterized and rendered from report
snapshots
In addition, cube objects must use terms familiar to users from around the world. For example, in the Sales Regions hierarchy, users from Great Britain must see the State level presented as County when browsing the Sales cube.
The Sales cube must support a new measure group named Sales Planning. The measure group must consist of a single measure named Sales Plan that enables the management team to use Excel 2010 to enter sales plans for future monitoring.
Technical Requirements
The BI system must meet the following technical requirements: Architecture requirements The system must use separate servers for each of the following components: SQL Server Database Engine SQL Server Integration Services SQL Server Analysis Services in multidimensional mode SharePoint Server with the Reporting Services Add-in o All servers must be installed using U.S. regional settings. The system must source currency exchange rate data from a database hosted in Windows Azure SQL Databases.
Security requirements
When possible, the system must use Windows authentication for all database
connections.
The system must prevent users from querying data from outside of their region.
The system must allow certain users to query data from multiple regions.
Development requirements
When browsing the Products hierarchy, repeating values for different levels of a
given drill-path must be avoided. For example, Papers -> Copy Paper -> Copy
Paper -> Copy Paper should appear simply as Papers -> Copy Paper.
The system must support report snapshots. The default maximum number of
retained snapshots must not exceed five.
....... ...... .
Q15. - (Topic 2)
You need to modify the Sales cube to support the planning requirements.
Which SSA5 feature should you use?
A. At KPI
B. A translation
C. A writeback partition
D. A perspective
Answer: C
Q16. HOTSPOT - (Topic 9)
A SQL Server Analysis Services (SSAS) cube contains billions of rows of data and is
rapidly increasing in size. The cube consists of a single measure group and a single partition. The cube is currently processed by using the Process Full process option.
You have the following requirements to reduce the cube processing time:
Partition the measure group by month.
Create a staging table that contains only data which is more recent than the last
time the cube was processed.
Do not include data updates or deletions in the staging table.
Insert records from the staging table into the appropriate partition.
You need to change the process option to meet the requirements.
Which process option should you choose?
To answer, select the appropriate option from the drop-down list in the dialog box.
.. ..
Answer:
Q17. - (Topic 10)
You are developing a multidimensional project that includes a dimension named Organization. The dimension is based on the DimOrganization table in the data warehouse. The following diagram illustrates the table design.
The Organization dimension includes a parent-child hierarchy named Organizations.
The dimension includes the following dimension attributes:
Organization, which is a key attribute
Organizations, which defines the parent-child hierarchy
Currency Code, which is a regular attribute
When users browse the dimension, three hierarchies are visible to them.
You need to ensure that the Organization hierarchy is not visible to users.
...
What should you do?
A. Set the AttributeHierarchyDisplayFolder property to Null for the Organization attribute.
B. Delete the Organization attribute.
C. Set the AttributeHierarchyEnabled property to False for the Organization attribute.
D. Set the AttributeHierarchyVisible property to False for the Organization attribute.
Answer: D
Q18. HOTSPOT - (Topic 9)
You administer a SQL Server Analysis Services (SSAS) cube.
You plan to optimize the cube partitions by using usage-based optimization.
You need to configure the SSAS instance.
Which properties should you configure? To answer, select the appropriate properties in the
dialog box in the answer area.
Answer:
Q19. DRAG DROP - (Topic 1)
You need to complete the design of the data warehouse.
Which design should you use? (To answer, drag the appropriate tables and relationships to the correct location in the answer area. Use only the tables and relationships that apply.)
Answer:
Q20. 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.)
Answer: