70-470 Exam
Top Tips Of 70-470 keys
Want to know Exambible 70-470 Exam practice test features? Want to lear more about Microsoft Recertification for MCSE: Business Intelligence certification experience? Study Highest Quality Microsoft 70-470 answers to Updated 70-470 questions at Exambible. Gat a success with an absolute guarantee to pass Microsoft 70-470 (Recertification for MCSE: Business Intelligence) test on your first attempt.
Q1. DRAG DROP - (Topic 10)
You manage a SQL Server Reporting Services (SSRS) instance running in native mode.
You are troubleshooting a performance problem and need to know which reports are frequently executed. You discover that the report server execution logs are empty, despite significant report activity.
You need to ensure that the server is configured for report execution logging.
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:
Q2. - (Topic 4)
You need to implement the security requirement for the sales representatives.
Which MDX expression should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Topic 5, Data Architect
General Background
You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.
You have been tasked with designing a data warehouse that uses the company's financial
database as the data source. From the data warehouse, you will develop a cube to simplify
the creation of accurate financial reports and related data analysis.
Background You will utilize the following three servers: . ServerA runs SQL Server Database Engine. ServerA is a production server and also hosts the financial database.
. ServerB runs SQL Server Database Engine, SQL Server Analysis Services (SSAS) in multidimensional mode, SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS).
. ServerC runs SSAS in multidimensional mode. . The financial database is used by a third-party application and the table structures cannot be modified.
The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)
The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is partitioned by month. It contains only the last five years of financial data. The CouponUsed, OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is less than 10 MB and has only one partition.
The SecurityFilter table specifies the sites to which each user has access.
Business Requirements The extract, transform, load (ETL) process that updates the data warehouse must run daily between 8:00 P.M. and 5:00 A.M. so that it doesn't impact the performance of ServerA during business hours. The cube data must be available by 8:00 A.M.
The cube must meet the following business requirements:
..
Ensure that reports display the most current information available. Allow fast access to support ad-hoc reports and data analysis.
Business Analysts will access the data warehouse tables directly, and will access the cube by using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint Services. These tools will access only the cube and not the data warehouse.
Technical Requirements SSIS solutions must be deployed by using the project deployment model. You must develop the data warehouse and store the cube on ServerB. When the number of concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to ServerC and meet following requirements:
..
Maintain copies of the cube on ServerB and ServerC. Ensure that the cube is always available on both servers.
Guaranteed success with TestInsides practice guides
.
Minimize query response time.
The cube must meet the following technical requirements:
.. ..
The cube must be processed by using an SSIS package.
The cube must contain the prior day's data up to 8:00 P.M. but does not need to
contain same-day data.
The cube must include aggregation designs when it is initially deployed.
A product dimension must be added to the cube. It will contain a hierarchy
comprised of product name and product color.
Because of the large size of the SalesTransactions table, the cube must store only aggregations—the data warehouse must store the detailed data. Both the data warehouse and the cube must minimize disk space usage.
As the cube size increases, you must plan to scale out to additional servers to minimize processing time.
The data warehouse must use a star schema design. The table design must be as denormalized as possible. The history of changes to the Customer table must be tracked in the data warehouse. The cube must use the data warehouse as its only data source.
Security settings on the data warehouse and the cube must ensure that queries against the SalesTransactions table return only records from the sites to which the current user has access.
The ETL process must consist of multiple SSIS packages developed in a single project by using the least amount of effort. The SSIS packages must use a database connection string that is set at execution time to connect to the financial database. All data in the data warehouse must be loaded by the SSIS packages.
You must create a Package Activity report that meets the following requirements:
..
Track SSIS package execution data (including package name, status, start time,
end time, duration, and rows processed).
Use the least amount of development effort.
Q3. - (Topic 10)
You are modifying a SQL Server Analysis Services (SSAS) cube.
Users of the cube report that the precision for the TransactionCost measure is five digits.
You need to ensure that the TransactionCost measure stores values to two digits of precision.
What should you do?
A. Add a named calculation in the data source view that casts the data source column to two digits of precision. Bind the TransactionCost measure to the new column.
B. Add a named query in the data source view that casts the data source column to two digits of precision. Bind the TransactionCost measure to the new query.
C. Use the FormatString measure property to format TransactionCost as #,##0.00;-#,##0.00.
D. Use the FormatString measure property to format TransactionCost as Currency.
E. Use the MeasureExpression measure property to change the precision of TransactionCost to two digits.
Answer: A
241. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project for a Power View solution.
You need to grant permission for salespersons to view only the data based on their sales territory.
What should you do?
A. Use SQL Server Management Studio to create a role. Then create a Data Analysis Expressions (DAX) filter.
B. Create a member and then create a Data Analysis Expressions (DAX) filter.
C. Create a member and then create a Multidimensional Expressions (MDX) filter.
D. Use SQL Server Management Studio to create a role. Then create a Multidimensional Expressions (MDX) filter.
Answer: A
Q4. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
In the data warehouse, a table named Employee Security defines a relationship between a salesperson's name, logon ID, and assigned sales territory.
You need to ensure that each salesperson has access to data from only the sales territory assigned to that salesperson. You also need to minimize the development effort.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create a separate tabular project for each sales territory. Grant each salesperson access to the corresponding tabular model of the assigned sales territory.
B. Create a new Active Directory Domain Services (AD DS) security group and add each salesperson as a member. Then create a new role with Read permission. Add the group as a member to the new role.
C. Create a new role with Read permission and then add each salesperson's logon as a member to the role.
D. Add the Employee Security table to the model, define the relationships, and then implement dynamic security by using row filters. Grant each salesperson access to the model.
Answer: D
Q5. - (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
Q6. - (Topic 9)
You are designing a reporting solution that uses SQL Server Reporting Services (SSRS) in SharePoint integrated mode.
The reporting solution must meet the following requirements:
. Allow report writers to reuse content between different reports.
. Allow report writers to modify reusable content in SharePoint.
. Retain version history for report content.
You need to choose a reporting method that meets the requirements.
What should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. drillthrough reports
B. linked reports
C. subreports
D. report parts
Answer: D
Q7. - (Topic 10)
You are developing a SQL Server Reporting Services (SSRS) report. The report includes a dataset with fields named Year, MonthNumber, and RegCount. The report includes a table that displays the number of recorded registration occurrences per year, as shown in the following diagram.
You need to modify the table to include a graphical item displaying the monthly registration trend to the right of the Reg Count column.
What should you do?
A. Add an Indicator item to a new column on the right of the Reg Count column. Select the Directional Indicator Type and then assign the MonthNumber field to the Start property.
B. Add a text box to a new column on the right of the Reg Count column. Then use a Go to report action to link to a separate report showing the monthly trend.
C. Add a Sparkline item to a new column on the right of the Reg Count column. Then select the RegCount field for Values and the MonthNumber field for Series Groups.
D. Add a Sparkline item to a new column on the right of the Reg Count column. Then select the RegCount field for Values and the MonthNumber field for Category Groups.
E. Add an Indicator item to a new column on the right of the Reg Count column. Select the Directional Indicator Type and then select the MonthNumber field for Value.
Answer: D
Q8. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
A column named City must be added to the table named Customer. The column will be used in the definition of a hierarchy. The City column exists in the Geography table that is related to the Customer table.
You need to add the City column to the Customer table.
How should you write the calculation?
A. City:= LOOKUPVALUE(Geography[City],Geography[GeographyKey],[GeographyKey])
B. =RELATED(Geography[City])
C. City:=VALUES(Geography[City])
D. =VALUES(Geography[City])
E. City:=LOCKUP(Geography[City],Geography[GeographyKey],[GeographyKey])
F. =RELATED(Geography.City)
Answer: B
Q9. - (Topic 10)
You are developing a BI Semantic Model (BISM) that will be used to analyze complex budgeting and forecast data sourced from a financial database. The model will be deployed to a server with 28 GB of RAM.
The source data, located in a SQL Server data warehouse, is currently using 15 terabytes of disk space and is doubling in size every month. The model will be queried by staff in the accounting department by using Microsoft Excel 2010.
You need to ensure the highest query performance and scalability for the accounting department queries.
Which project type should you choose?
A. Tabular project that uses the In-Memory query mode
B. Tabular project that uses the DirectQuery query mode
C. Multidimensional project
D. PowerPivot workbook deployed to SharePoint
Answer: C
Q10. - (Topic 7)
You need to identify the reasons that data alert notifications are not being sent.
Which of the following reasons are possible? (Each correct answer presents a complete solution. Choose al that apply.)
A. The shared schedule is paused.
B. The data source used by the report is disabled.
C. The SSRS service is not running.
D. The report data has not changed since the previous notification
E. The SQL Server Agent is not running.
F. The SSRS encryption key has been deleted.
Answer: C,E
Explanation:
One possibility is that no SQL Server Agent alerts have been configured. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.