Top 16 courses 70-466 for IT specialist (1 to 16)

Exam Code: 70-466 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Implementing Data Models and Reports with Microsoft SQL Server 2012
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-466 Exam.

Q1. - (Topic 4) 

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 table. 

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 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. 

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 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. 

E. 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. 

Answer: E 

Q2. - (Topic 4) 

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 ExecutionLog view in the Report Server database. 

B. View the Report Server service trace log. 

C. View the Report Server HTTP log. 

D. View the Windows System event log. 

Answer: C 

Explanation: http://msdn.microsoft.com/en-us/library/bb630443.aspx 

Q3. - (Topic 4) 

... . 

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 tabular model with one partition for all of the data. 

B. Create a multidimensional model with one partition for each month. 

C. Create a multidimensional model with one partition for all of the data. 

D. Create a tabular model with one partition for each month. 

Answer: A 

Q4. DRAG DROP - (Topic 4) 

You are developing a SQL Server Analysis Services (SSAS) cube. 

You need to add a calculated member to the Customer dimension to evaluate the sum of values for the United Kingdom and the United States. 

Which expression should you use? (To answer, drag the appropriate expression to the answer area.) 

Answer: 

Q5. - (Topic 2) 

You need to develop the executive reports. 

What should you do? (Each correct answer presents part of the solution. Choose ail that apply.) 

A. Provide default values for all parameters. 

B. Set the data source to use Windows authentication. 

C. Remove default values from all parameters. 

D. Implement dataset filters to filter data. 

E. Set the data source to use stored Windows credentials. 

F. Implement dataset query parameters to filter data. 

Answer: A,D,E 

20. - (Topic 2) 

You need to configure the UserRegions role. 

Which Multidimensional Expressions (MDX) function should you use? 

A. USERNAME ( ) 

B. USERID ( ) 

C. CUSTOMDATA ( ) 

D. UNIQUENAME ( ) 

E. LOOKUPVALUE ( ) 

Answer: A 

Q6. - (Topic 4) 

You are developing a SQL Server Analysis Services (SSAS) cube. 

You must create a four-level hierarchy for the employee dimension. Each level must be associated with an attribute in the employee dimension table. Two thirds of the dimension data contain values for all four attributes. The remainder of the dimension data contains values for the first three of the four attributes only. 

You need to create the hierarchy so that logically missing members will not be shown by the reporting tool. 

Which type of hierarchy should you create? 

A. A parent-child hierarchy 

B. A sparse hierarchy 

C. A ragged hierarchy 

D. A balanced hierarchy 

Answer: A 

Q7. - (Topic 4) 

You are developing a SQL Server Analysis Services (SSAS) tabular project. A model defines a measure named Revenue and includes a table named Date. The table includes year, semester, quarter, month, and date columns. The Date column is of data type Date. The table contains a set of contiguous dates. 

You need to create a measure to report on year-over-year growth of revenue. 

What should you do? (Each answer presents a complete solution. Choose all that apply.) 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: C,D 

Q8. - (Topic 3) 

You need create the data source view for the StandardReports project. What should you do? 

A. Create a data source, connect it to the data warehouse, and then use the Data Source View wizard. 

B. Generate a relational schema from the dimensions and cubes by using the Schema Generation wizard. 

C. Create a new data source view and then use the Import from Table wizard. 

D. Execute the Import from Table wizard and then use the Data Source View wizard. 

Answer: A 

Q9. - (Topic 2) 

You need to configure per-user security authentication for reporting against the Sales cube. 

What should you do? (Each correct answer presents part of the complete solution. Choose all that apply.) 

A. Create Service Principal Names (SPNs). 

B. Enable forms-based authentication. 

C. Configure account delegation. 

D. Enable mixed-mode authentication. 

Answer: A,D 

Q10. - (Topic 4) 

You manage a SQL Server Reporting Services (SSRS) instance. 

An application must pass credentials to the local security authority for Reporting Services. 

You need to configure Reporting Services to issue a challenge/response when a 

connection is made without credentials. 

Which authentication type should you configure in the RSReportServer.config file? 

A. RSWindowsKerberos 

B. RSWindowsNegotiate 

C. RSWindowsNTLM 

D. RSWindowsBasic 

Answer: A 

Q11. - (Topic 4) 

You maintain a multidimensional Business Intelligence Semantic Model (BISM) that was developed with default settings. 

The model has one cube and the cube has one measure group. The measure group is based on a very large fact table and is partitioned by month. The fact table is incrementally loaded each day with approximately 800,000 new rows. 

You need to ensure that all rows are available in the cube while minimizing the processing time. 

Which processing option should you use? 

A. Process Index 

B. Process Data 

C. Process Add 

D. Process Default 

E. Process Clear 

F. Process Full 

Answer: C 

Q12. - (Topic 4) 

You develop a SQL Server Analysis Services (SSAS) tabular project.The tabular model loads data from a SQL Server relational database each day. 

You define a connection. 

You need to ensure that the connection minimizes the attack surface area of the server. 

How should you define the impersonation information for the connection? (More than one answer choice may achieve the goal. Select the BEST answer.) 

A. Use your domain credentials. Grant least privilege to your account in the source database. 

B. Create and use a new Windows domain account. Grant least privilege to this account in the source database. 

C. Use the credentials of the SQL Server Analysis Services (SSAS) service account. Grant least privilege to this account in the source database. 

D. Use SQL Server authentication. 

Answer: B 

Q13. - (Topic 3) 

Topic 3, Data Architect 

General Background 

You are the data architect for a company that uses SQL Server 2012 Enterprise Edition. You design data modeling and reporting solutions that are based on a sales data warehouse. 

Background 

The solutions will be deployed on the following servers: . ServerA runs SQL Server Database Engine, ServerA is the data warehouse server. . ServerB runs SQL Server Database Engine, SQL Server Analysis Services (SSAS) in multidimensional mode, and SQL Server Integration Services (SSIS). . ServerC runs SSAS in tabular mode, SQL Server Reporting Services (SSRS) 

running in SharePoint mode, and Microsoft SharePoint 2010 Enterprise Edition with SP1. 

The data warehouse schema currently contains the tables shown in the exhibit. (Click the Exhibit button.) 

Business Requirements 

The reporting solution must address the requirements of the sales team, as follows: 

. Team members must be able to view standard reports from SharePoint. 

. Team members must be able to perform ad-hoc analysis by using Microsoft Power 

View and Excel. 

. Team members can have standard reports delivered to them on a schedule of 

their choosing. 

The standard reports 

.. 

Will use a sales territory hierarchy for organizing data by region. Will be accessible from SharePoint. 

The Excel ad-hoc reports 

.. 

Will use the same data store as the standard reports. 

Will provide direct access to the data store for the sales team and a simplified view 

for the executive team. 

Technical Requirements 

The standard reports must be based on an SSAS cube. The schema of the data warehouse on ServerA must be able to support the ability to slice the fact data by the following dates: 

... 

Order date (OrderDateKey) Due date (DueDateKey) Ship date (ShipDateKey) 

Additions and modifications to the data warehouse schema must adhere to star schema design principles to minimize maintenance and complexity . The multidimensional and tabular models will be based on the data warehouse. The tabular and multidimensional models will be created by using SQL Server Data Tools (SSDT). The tabular project is named AdhocReports and the multidimensional project is named Standard Reports. The cube design in the Standard Reports project must define two measures for the unique count of sales territories (SalesTerritoryKey) and products (ProductKey). 

A deployment script that can be executed from a command-line utility must be created to deploy the StandardReports project to ServerB. 

The tabular model in the AdhocReports project must meet the following requirements: 

. .. 

A hierarchy must be created that consists of the SalesTerritoryCountry and SalesTerritoryRegion columns from the DimSalesTerritory table and the EmployeeName column from the DimEmployee table. A key performance indicator (KPI) must be created that compares the total quantity sold (OrderQuantity) to a threshold value of 1,000. A measure must be created to calculate day-over-day (DOD) sales by region based on order date. 

SSRS on ServerC must be configured to meet the following requirements: 

... 

It must use a single data source for the standard reports. 

It must allow users to create their own standard report subscriptions. 

The sales team members must be limited to only viewing and subscribing to 

reports in the Sales Reports library. 

A week after the reporting solution was deployed to production, Marc, a salesperson, indicated that he has never received reports for which he created an SSRS subscription. In addition, Marc reports that he receives timeout errors when running some reports on demand. 

=====

You need to create the KPI in the AdhocReports project. 

What should you do? 

A. Create a measure by using the SUM([OrderQuantity]) expression. Then use the CREATE KPI CURRENTCUBE statement to define the KPI and target value. 

B. Create a measure by using the SUM([OrderQuantity]) expression and create a KPI based on the measure. Then set the target value. 

C. Create a measure by using the COUNT([OrderQuantity]) expression and create a KPI based on the measure. Then set the target value. 

D. Create a KPI based on the OrderQuantity column and then set the target value. 

Answer: C 

Q14. - (Topic 4) 

You develop a SQL Server Analysis Services (SSAS) stored procedure. 

You need to ensure that developers can create Multidimensional Expressions (MDX) calculations that use the stored procedure. 

What should you do? 

A. Register the assembly on the SSAS server instance. 

B. Copy the assembly to the SSAS installation directory and register it by using the CREATE ASSEMBLY T-SQL command. 

C. Register the assembly on the SSAS server by using regedit.exe. 

D. Register the assembly on the SharePoint server by using regeditexe. 

Answer: B 

Q15. HOTSPOT - (Topic 4) 

A sales cube contains two years of data. 

The sales team must see year-over-year (YOY) and month-over-month (MOM) sales 

metrics. 

You need to modify the cube to support the sales team's requirements. 

Which Business Intelligence Wizard enhancements should you use? (To answer, configure 

the appropriate option or options in the dialog box in the answer area.) 

Answer: 

Q16. - (Topic 4) 

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. Use the SQL Server Data Tools Dimension Wizard and generate a time table on the server. 

B. Create a CSV file with time data and use the DMX IMPORT statement to import data from the CSV file. 

C. Create a time dimension by using the Define dimension intelligence option in the Business Intelligence Wizard. 

D. Create a time dimension by using the Define time intelligence option in the Business Intelligence Wizard. 

Answer: A