Master the 70-470 Recertification for MCSE: Business Intelligence content and be ready for exam day success quickly with this Ucertify 70-470 exam cost. We guarantee it!We make it a reality and give you real 70-470 questions in our Microsoft 70-470 braindumps.Latest 100% VALID Microsoft 70-470 Exam Questions Dumps at below page. You can use our Microsoft 70-470 braindumps and pass your exam.
Q1. DRAG DROP - (Topic 10)
You have a single SQL Server 2008 R2 Analysis Services (SSAS) instance. You are planning to upgrade the instance to SQL Server 2012.
You need to import an existing PowerPivot workbook to create a tabular project.
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 5)
You need to implement the aggregation designs for the cube.
What should you do?
A. Use the CREATE CACHE statement.
B. Use the Aggregation Design Wizard.
C. Create relational indexes on the source tables.
D. Use the Usage-Based Optimization Wizard.
Answer: B
Q3. - (Topic 6)
You need to configure permissions for the Customers dimension.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
A. In SQL Server Management Studio, configure the BusinessUsers role to disallow the reading of all definitions.
B. In SQL Server Data Tools, configure the BusinessUsers role to disallow the reading of the Customers dimension definition.
C. In SQL Server Management Studio, deny the member set for the Customers dimension data by using the Multidimensional Expressions (MDX) expression Filter([BusinessUsers]).
D. In SQL Server Management Studio, configure the BusinessUsers role to disallow the reading of the Customers dimension definition.
Answer: B,D
66. - (Topic 6)
You need to implement the date dimension in the Operations database.
What should you do?
A. Create three database dimensions. Add each database dimension as a cube dimension by setting the Referenced relationship type.
B. Create one database dimension. Add three cube dimensions based on the database dimension. Set the Regular relationship type for each cube dimension.
C. Create three database dimensions. Add each database dimension as a cube dimension by setting the Regular relationship type.
D. Create one database dimension. Add three cube dimensions based on the database dimension. Set the Referenced relationship type for each cube dimension.
Answer: B
Q4. - (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
Q5. - (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.
Q6. HOTSPOT - (Topic 10)
You create a new report in SQL Server Data Tools (SSDT). The report queries a Windows Azure SQL Database database table.
In a report table showing sales by countries and cities, you need to enable users to hide or show cities by clicking the name of a country.
Which property page of the city text box should you use? (To answer, select the appropriate tab in the answer area.)
Answer:
281. - (Topic 10)
You manage a SQL Server Reporting Services (SSRS) instance. The ReportingServicesService.exe.config file has been modified to enable logging. Some users report that they cannot access the server.
You need to ascertain the IP addresses of the client computers that are accessing the server.
What should you do?
A. View the Report Server service trace log.
B. View the ExecutionLog view in the Report Server database.
C. View the Report Server HTTP log.
D. View the Windows System event log.
Answer: C
Q7. - (Topic 6)
You need to design the dimCustomers table.
Which design approach should you use?
A. Reference dimension
B. Type 2 slowly changing dimension
C. Junk dimension
D. Conformed dimension
E. Type 1 slowly changing dimension
Answer: B Topic 7, Contoso, Ltd Case B
General Background You are the business intelligence (BI) solutions architect for Contoso, Ltd, an online retailer.
You produce solutions by using SQL Server 2012 Business Intelligence edition and Microsoft SharePoint Server 2010 Service Pack 1 (SP1) Enterprise edition.
A SharePoint farm has been installed and configured for intranet access only. An Internet-facing web server hosts the company's public e-commerce website. Anonymous access is not configured on the Internet-facing web server.
Data Warehouse The data warehouse is deployed on a 5QL Server 2012 relational database instance. The data warehouse is structured as shown in the following diagram.
The following Transact-SQL (T-SQL) script is used to create the FactSales and FactPopulation tables:
The FactPopulation table is loaded each year with data from a Windows Azure Marketplace commercial dataset. The table contains a snapshot of the population values for all countries of the world for each year. The world population for the last year loaded exceeds
6.8 billion people.
ETL Process SQL Server Integration Services (SSIS) is used to load data into the data warehouse. All SSIS projects are developed by using the project deployment model.
A package named StageFactSales loads data into a data warehouse staging table. The package sources its data from numerous CSV files exported from a mainframe system. The CSV file names begin with the letters GLSD followed by a unique numeric identifier that never exceeds six digits. The data content of each CSV file is identically formatted.
A package named LoadFactFreightCosts sources data from a Windows Azure SQL Database database that has data integrity problems. The package may retrieve duplicate rows from the database.
The package variables of all packages have the RaiseChangedEvent property set to true. A package-level event handler for the OnVariableValueChanged event consists of an Execute SQL task that logs the System::VariableName and System::VariableValue variables.
Data Models SQL Server Analysis Services (SSAS) is used to host the Corporate BI multidimensional database. The Corporate BI database contains a single data source view named Data Warehouse. The Data Warehouse data source view consists of all data warehouse tables. All data source view tables have been converted to named queries.
The Corporate BI database contains a single cube named Sales Analysis and three database dimensions: Date, Customer and Product. The dimension usage for the Sales Analysis cube is as shown in the following image.
The Customer dimension contains a single multi-level hierarchy named Geography. The structure of the Geography hierarchy is shown in the following image.
The Sales Analysis cube's calculation script defines one calculated measure named Sales Per Capita. The calculated measure expression divides the Revenue measure by the Population measure and multiplies the result by 1,000. This calculation represents revenue per 1,000 people.
The Sales Analysis cube produces correct Sales Per Capita results for each country of the world; however, the Grand Total for all countries is incorrect, as shown in the following image (rows 2-239 have been hidden).
A role named Analysts grants Read permission for the Sales Analysis cube to all sales and marketing analysts in the company.
SQL Server Reporting Services (SSRS) is configured in SharePoint integrated mode. All reports are based on shared data sources.
Corporate logo images used in reports were originally configured as data-bound images sourced from a SQL Server relational database table. The image data has been exported to JPG files. The image files are hosted on the Internet-facing web server. All reports have been modified to reference the corporate logo images by using the fully qualified URLs of the image files. A red X currently appears in place of the corporate logo in reports.
Users configure data alerts on certain reports. Users can view a report named Sales Profitability on demand; however, notification email messages are no longer being sent when Sales Profitability report data satisfies alert definition rules. The alert schedule settings for the Sales Profitability report are configured as shown in the following image.
Business Requirements Data Models
Users must be able to: . Provide context to measures and filter measures by using all related data warehouse dimensions. . Analyze measures by order date or ship date.
Additionally, users must be able to add a measure named Sales to the report canvas by clicking only once in the Power View field list. The Sales measure must allow users to analyze the sum of the values in the Revenue column of the FactSales data warehouse table. Users must be able to change the aggregation function of the Sales measure.
Analysis and Reporting A sales manager has requested the following query results from the Sales Analysis cube for the 2012 fiscal year:
. Australian postal codes and sales in descending order of sales. . Australian states and the ratio of sales achieved by the 10 highest customer sales made for each city in that state.
Technical Requirements ETL Processes If an SSIS package variable value changes, the package must log the variable name and the new variable value to a custom log table.
The StageFactSales package must load the contents of all files that match the file name pattern. The source file name must also be stored in a column of the data warehouse staging table. In the design of the LoadFactSales package, if a lookup of the dimension surrogate key value for the product code fails, the row details must be emailed to the data steward and written as an error message to the SSIS catalog log by using the public API.
You must configure the LoadFactFreightCosts package to remove duplicate rows, by using the least development effort.
Data Models Users of the Sales Analysis cube frequently filter on the current month's data. You must ensure that queries to the Sales Analysis cube default to the current month in the Order Date dimension for all users.
You must develop and deploy a tabular project for the exclusive use as a Power View reporting data source. The model must be based on the data warehouse. Model table names must exclude the Dim or Fact prefixes. All measures in the model must format values to display zero decimal places.
Analysis and Reporting Reports must be developed that combine the SSIS catalog log messages with the package variable value changes.
Q8. 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:
Q9. - (Topic 9)
You have a SQL Server Reporting Services (SSRS) 2008 instance integrated with an Internet-facing Microsoft SharePoint 2010 farm. PerformancePoint Services and Excel Services are installed and configured.
You need to upgrade to SSRS 2012 with the minimum downtime and hardware requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Migrate SSRS content to a separate instance of SSRS 2012 on the same server.
B. Perform an in-place upgrade of the SSRS environment.
C. Migrate SSRS content to new servers that run SSRS 2012.
D. Install SSRS 2012 on a new SharePoint farm and migrate content to the new farm.
Answer: C
Q10. DRAG DROP - (Topic 9)
You are administering a SQL Server Analysts Services (SSAS) database on a server. The database hosts a financial cube based on a SQL Azure database.
You need to grant write access to the financial cube for all users in the group USA\PowerUsers.
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:
Q11. DRAG DROP - (Topic 9)
You are designing a SQL Server Reporting Services (SSRS) solution.
An existing report aggregates data from a SQL Azure database in a chart.
You need to use the chart in a new report and ensure that other users can use the chart in
their reports.
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:
Q12. - (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 32 GB of RAM.
The source data, located in a SQL Server data warehouse, is currently using 10 terabytes of disk space and is doubling in size every three months. 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. PowerPivot workbook
B. tabular project that uses the In-Memory query mode
C. multidimensional project
D. tabular project that uses the DirectQuery query mode
Answer: C
Q13. - (Topic 5)
You need to restrict access to data in the tables in the data warehouse.
What should you do?
A. Configure column-level permissions.
B. Configure database roles.
C. Create views and grant permissions to the views.
D. Configure application roles.
Answer: C
Q14. DRAG DROP - (Topic 9)
You administer a SQL Server Analysis Services (SSAS) instance.
You need to capture a continuous log of detailed event and subevent durations and custom
trace events from queries executed in the SSAS instance.
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:
Q15. - (Topic 8)
You need to implement the SalesCommission model to support the planned reports and queries.
What should you do?
A. Create a date table that contains only one row for each date on which a sale is recorded.
B. Use the existing transaction date column in the sales table for date calculations.
C. Create a date table that contains a row for every date since data started being recorded.
D. Create a new calculated date column in the sales table for date calculations.
Answer: C
Q16. DRAG DROP - (Topic 9)
...
You are designing a self-service business intelligence and reporting environment.
Business analysts will create and publish PowerPivot for Microsoft Excel workbooks and create reports by using SQL Server Reporting Services (SSRS) and Power View. When the data models become more complex and the data volume increases, the data models will be replaced by IT-hosted server-based models.
You have the following requirements:
Maintain the self-service nature of the reporting environment.
Reuse existing reports.
Add calculated columns to the data models.
You need to create a strategy for implementing this process.
What should you do?
To answer, drag the appropriate term or terms to the correct location or locations in the answer area. (Answer choices may be used once, more than once, or not all.)
Answer: