Microsoft Implementing Data Models and Reports with Microsoft SQL Server - 070-466 Exam Practice Test
You need to develop the executive reports.
What should you do? (Each correct answer presents part of the solution. Choose ail that apply.)
What should you do? (Each correct answer presents part of the solution. Choose ail that apply.)
Correct Answer: B,D,F
You have a database for a mission-critical web application. The database is stored on a SQL Server 2012 instance and is the only database on the instance. The application generates all T-SQL statements dynamically and does not use stored procedures.
You need to maximize the amount of memory available for data caching.
Which advanced server option should you modify?
You need to maximize the amount of memory available for data caching.
Which advanced server option should you modify?
Correct Answer: D
Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).
You are modifying a SQL Server Analysis Services (SSAS) cube that aggregates mobile phone usage data from a Microsoft Azure SQL Database database. The existing database contains a device dimension.
The Research and Development team has requested that capabilities be added to the database.
The capabilities must meet the following requirements:
*A device member must be able to have multiple capability members.
*A capability member must be able to belong to several device members.
*The Research and Development team must be able to create new capabilities every quarter in the data source.
You need to implement the appropriate solution to meet the requirements while ensuring that the amount of development and maintenance time is minimized.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
The Research and Development team has requested that capabilities be added to the database.
The capabilities must meet the following requirements:
*A device member must be able to have multiple capability members.
*A capability member must be able to belong to several device members.
*The Research and Development team must be able to create new capabilities every quarter in the data source.
You need to implement the appropriate solution to meet the requirements while ensuring that the amount of development and maintenance time is minimized.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Correct Answer: D
You need to create the sales territory and product measures.
Which aggregate function should you use for both measures?
Which aggregate function should you use for both measures?
Correct Answer: D
You are developing a SQL Server Reporting Services (SSRS) report for a company that has a subscription to a dataset from the Microsoft Azure Data Market.
The dataset permits flexible queries.
You need to choose the connection type for the data source.
Which connection type should you choose?
The dataset permits flexible queries.
You need to choose the connection type for the data source.
Which connection type should you choose?
Correct Answer: A
A multinational retailer has retail locations on several continents. A single SQL Server Reporting Services (SSRS) instance is used for global reporting.
A SQL Server Analysis Services (SSAS) instance for each continent hosts a multidimensional database named RetailSales. Each RetailSales database stores data only for the continent in which it resides. All of the SSAS instances are configured identically.
The cube names and objects are identical.
Reports must meet the following requirements:
* A report parameter named ServerName must be defined in each report.
* When running a report, users must be prompted to select a server instance.
* The report data source must use the Microsoft SQL Server Analysis Services data source type.
You need to create a data source to meet the requirements.
How should you define the expression that is assigned to the connection string property of the data source?
A SQL Server Analysis Services (SSAS) instance for each continent hosts a multidimensional database named RetailSales. Each RetailSales database stores data only for the continent in which it resides. All of the SSAS instances are configured identically.
The cube names and objects are identical.
Reports must meet the following requirements:
* A report parameter named ServerName must be defined in each report.
* When running a report, users must be prompted to select a server instance.
* The report data source must use the Microsoft SQL Server Analysis Services data source type.
You need to create a data source to meet the requirements.
How should you define the expression that is assigned to the connection string property of the data source?
Correct Answer: A
DRAG DROP
You are developing a SQL Server Reporting Services (SSRS) report that sources data from a SQL Azure database and a SQL Server Analysis Services (SSAS) cube. The cube contains a date dimension and other dimensions. The report design includes two report parameters named StartDate and FinishDate as shown in the following diagram.
The Data Type property of the parameters is set to Date/Time.
You need to create the dataset based on the SSAS cube. You also need to ensure that the dataset is filtered by the existing report parameters.
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.)
You are developing a SQL Server Reporting Services (SSRS) report that sources data from a SQL Azure database and a SQL Server Analysis Services (SSAS) cube. The cube contains a date dimension and other dimensions. The report design includes two report parameters named StartDate and FinishDate as shown in the following diagram.
The Data Type property of the parameters is set to Date/Time.
You need to create the dataset based on the SSAS cube. You also need to ensure that the dataset is filtered by the existing report parameters.
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.)
Correct Answer:
Explanation:
Box 1:
Box 2:
Box 3:
Note:
* In Reporting Services, a dataset is based on an existing a data source. A dataset specifies a query, query parameters, filters, and a field collection. You can also specify data options, such as case, collation, kanatype, width, and accent, for the data retrieved from the data source. For more information, see Understanding Report Datasets.
To create a dataset, you must have defined an embedded or shared data source.
* When you deploy Reporting Services, a set of data processing extensions are automatically installed and registered on both the report authoring client and on the report server to provide access to a variety of data source types . SQL Server 2008 Reporting Services (SSRS) installs the following data source types: Microsoft SQL Server, Microsoft SQL Server Analysis Services, Oracle, SAP NetWeaver BI, Hyperion Essbase, Teradata, OLE DB, ODBC, and XML.
* In the Report Data pane, right-click on a dataset created from a SQL Server Analysis Services data source type, and then clickQuery. The MDX query designer opens in Design mode.
* Drag a dimension to the filter area and drop it on the first cell in
theDimensioncolumn.
* In theHierarchycolumn, choose a value from the drop-down list.
* In theOperatorcolumn, choose an operator for the drop-down list.
* In theFilter Expressioncolumn, select individual values from the drop-down list, or click theAllmember to choose all values.
* In theParameterscolumn, select the check box to create a report parameter.
* ClickRun.
* To define a query parameter in MDX in Design modeAfter you run the query, clickDesign on the toolbar to toggle to Query mode to view the MDX query that was created. Do not change the query text in Query mode if you want to continue to use Design mode to develop the query. ClickDesignto toggle back to Design mode.
* Click OK.
* In the Report Data pane, right-click the name of the data source, and then clickAdd Dataset. TheQuerypage of theDataset Propertiesdialog box opens.
* InName, type a name for the dataset or accept the default name.
* In Data source, select the name of an existing shared data source, or click New to create a new embedded data source.
* Select a Query type option. Options vary depending on the data source type.
* In Query, type the query, stored procedure, or table name. Alternatively, click Query Designer to open the graphical or text-based query designer tool, or Import to import the query from an existing report.
* To create a datasetIn a few cases, the field collection specified by the query can only be determined by running the query on the data source. For example, a stored procedure may return a variable set of fields in the result set. Click Refresh Fields to run the query on the data source and retrieve the field names that are needed to populate the dataset field collection in the Report Data pane. The field collection appears under the dataset node after you close the Dataset Properties dialog box.
* In Timeout, type the number of seconds that the report server waits for a response from the database. The default value is 0 seconds. When the time out value is 0 seconds, the query does not time out.
* Click OK.
The dataset and its field collection appear in the Report Data pane under the data source node.
HOTSPOT
A SQL Server Analysis Services (SSAS) cube has roles to define dimension data security.
A role named USA allows users to browse data pertaining to the United States. A role named Canada allows users to browse data pertaining to Canada.
A user can browse sales data pertaining to the United States but cannot browse sales data pertaining to Canada. You validate that the user belongs to the USA and Canada roles.
You need to reproduce the issue in SQL Server Management Studio (SSMS).
Which option should you select? To answer, select the appropriate action in the answer area.
A SQL Server Analysis Services (SSAS) cube has roles to define dimension data security.
A role named USA allows users to browse data pertaining to the United States. A role named Canada allows users to browse data pertaining to Canada.
A user can browse sales data pertaining to the United States but cannot browse sales data pertaining to Canada. You validate that the user belongs to the USA and Canada roles.
You need to reproduce the issue in SQL Server Management Studio (SSMS).
Which option should you select? To answer, select the appropriate action in the answer area.
Correct Answer:
You need to recommend a solution for the sales department that meets the security requirements.
What should you recommend?
What should you recommend?
Correct Answer: A
Explanation: Only visible for TrainingDump members. You can sign-up / login (it's free).
DRAG DROP
You are developing a SQL Server PowerPivot model to monitor the rate of production of defective products.
The PowerPivot model contains many tables. The FactProduction table is configured as shown in the following diagram.
The model requires a key performance indicator (KPI) named Defective Units to measure the rate of production of defective units. The rate of production of defective units is calculated by dividing the sum of the Defects column by the sum of the Units column.
The KPI has the following requirements.
* If the rate of production of defective products equals or exceeds 5% then the KPI must report a status of off target (red).
* If the rate of production of defective products is less than 5% and equals or exceeds 2% then the KPI must report a status of slightly off target (yellow).
* If the rate of production of defective products less than 2% then the KPI must report a status of on target (green).
You need to develop the KPI to meet the requirements.
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.)
You are developing a SQL Server PowerPivot model to monitor the rate of production of defective products.
The PowerPivot model contains many tables. The FactProduction table is configured as shown in the following diagram.
The model requires a key performance indicator (KPI) named Defective Units to measure the rate of production of defective units. The rate of production of defective units is calculated by dividing the sum of the Defects column by the sum of the Units column.
The KPI has the following requirements.
* If the rate of production of defective products equals or exceeds 5% then the KPI must report a status of off target (red).
* If the rate of production of defective products is less than 5% and equals or exceeds 2% then the KPI must report a status of slightly off target (yellow).
* If the rate of production of defective products less than 2% then the KPI must report a status of on target (green).
You need to develop the KPI to meet the requirements.
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.)
Correct Answer:
Explanation: Box 1:
Box 2:
Box 3:
Note:
* (Step 1, step 2): The model requires a key performance indicator (KPI) named Defective Units to measure the rate of production of defective units. The rate of production of defective units is calculated by dividing the sum of the Defects column by the sum of the Units column.
* Step 3: The KPI has the following requirements.
*If the rate of production of defective products equals or exceeds 5% then the KPI must report a status of off target (red).
*If the rate of production of defective products is less than 5% and equals or exceeds 2% then the KPI must report a status of slightly off target (yellow).
*If the rate of production of defective products less than 2% then the KPI must report a status of on target (green).
You are developing a SQL Server Analysis Services (SSAS) tabular project. The model has tables named Invoice Line Items and Products.
The Invoice Line Items table has the following columns:
* Product Id
* Unit Sales Price
The Unit Sales Price column stores the unit price of the product sold.
The Products table has the following columns:
* Product Id
* Maximum Sales Price
The Maximum Sales Price column is available only in the Products table.
You add a column named Is Overpriced to the Invoice Line Items table. The Is Overpriced column must store a value of TRUE if the value of the Unit Sales Price is greater than the value of the Maximum Sales Price. Otherwise, a value of FALSE must be stored.
You need to define the Data Analysis Expressions (DAX) expression for the Is Overpriced column.
Which DAX formula should you use? (Each answer represents a complete solution.
Choose all that apply.)
The Invoice Line Items table has the following columns:
* Product Id
* Unit Sales Price
The Unit Sales Price column stores the unit price of the product sold.
The Products table has the following columns:
* Product Id
* Maximum Sales Price
The Maximum Sales Price column is available only in the Products table.
You add a column named Is Overpriced to the Invoice Line Items table. The Is Overpriced column must store a value of TRUE if the value of the Unit Sales Price is greater than the value of the Maximum Sales Price. Otherwise, a value of FALSE must be stored.
You need to define the Data Analysis Expressions (DAX) expression for the Is Overpriced column.
Which DAX formula should you use? (Each answer represents a complete solution.
Choose all that apply.)
Correct Answer: B,D
DRAG DROP
You have a cube named Cube1 that contains the sales data for your company.
You plan to build a report based on the cube.
You need to write an MDX expression that returns the total sales from the first month of the
2009 fiscal year and the total sales from the same period of the 2008 fiscal year.
Which code segments should you insert at line 03 and line 05? To answer, drag the appropriate code segments to the correct lines. Each code segments may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
You have a cube named Cube1 that contains the sales data for your company.
You plan to build a report based on the cube.
You need to write an MDX expression that returns the total sales from the first month of the
2009 fiscal year and the total sales from the same period of the 2008 fiscal year.
Which code segments should you insert at line 03 and line 05? To answer, drag the appropriate code segments to the correct lines. Each code segments may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Correct Answer:
HOTSPOT
You are developing a SQL Server Analysis Services (SSAS) cube that contains the data for a running team.
The data warehouse used by the cube contains the time durations of laps run by each runner on the team. The time durations are stored in seconds as an integer.
You need to build the following two measures in the cube:
* A measure named Measure1 that must contain the average time duration of the laps run by each runner.
* A measure named Measure2 that must contain the lap-time duration and the name of the runner who ran the fastest lap.
What should you do? To answer, select the appropriate Aggregation Function property for each measure in the answer area.
You are developing a SQL Server Analysis Services (SSAS) cube that contains the data for a running team.
The data warehouse used by the cube contains the time durations of laps run by each runner on the team. The time durations are stored in seconds as an integer.
You need to build the following two measures in the cube:
* A measure named Measure1 that must contain the average time duration of the laps run by each runner.
* A measure named Measure2 that must contain the lap-time duration and the name of the runner who ran the fastest lap.
What should you do? To answer, select the appropriate Aggregation Function property for each measure in the answer area.
Correct Answer:
Explanation:
Note: For Measure1 we use Average, and for Measure2 Min to get the fastest time.