Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 10)
... .
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 multidimensional model with one partition for all of the data.
B. Create a multidimensional model with one partition for each month.
C. Create a tabular model with one partition for all of the data.
D. Create a tabular model with one partition for each month.
Answer: C
Q2. - (Topic 5)
You need to create the Package Activity report.
What should you do?
A. Create a log table and use SSIS event handlers to write to the log table. Then create an SSRS report that uses the log table.
B. Use the SSIS log provider for SQL Server. Then create an SSRS report that uses the sysssislog table.
C. Create a log table and build a custom log provider to write to the log table. Then create an SSRS report that uses the log table.
D. Create an SSRS report that uses thecatalog.executions and catalog.execution_data_statistics views.
Answer: D
Q3. DRAG DROP - (Topic 7)
You need to implement the requirements for the StageFactSales package.
Which four 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:
Q4. - (Topic 10)
.. ..
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.)
A. =IF([Unit Sales Price] > RELATED(Products[Maximum Sales Price]), TRUE, FALSE)
B. =IF(RELATED(Products[Unit Sales Price]) > [Maximum Sales Price], TRUE, FALSE)
C. =IF([Unit Sales Price] > LOOKUPVALUE(Products[Maximum Sales Price], Products[Product Id], [Product Id]), TRUE, FALSE)
D. =IF(LOOKUPVALUE(Products[Unit Sales Price], Products[Product Id], [Product Id]) > [Maximum Sales Price]), TRUE, FALSE)
Answer: A,C
Explanation: A: RELATED Function Returns a related value from another table.
* The RELATED function requires that a relationship exists between the current table and the table with related information. You specify the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table.
C:
The lookupvalue function returns the value in result_columnName for the row that meets all
criteria specified by search_columnName and search_value.
Syntax:
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[,
<search_columnName>, <search_value>]…)
Note:
The syntax of DAX formulas is very similar to that of Excel formulas, and uses a
combination of functions, operators, and values.
Q5. - (Topic 10)
You install SQL Server Reporting Services (SSRS). You need to back up a copy of the symmetric key. Which command should you run?
A. rskeymgmt -d
B. rskeymgmt -i.
C. rskeymgmt -e -f %temp%\rs.key -p Password1
D. rskeymgmt -a -f %temp%\rs.key -p Password1
Answer: C
210. - (Topic 10)
...
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 RevenueData. Each RevenueData 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. ="Server=" &Parameters!ServerName.Value& ";Initial Catalog=RevenueData"
B. ="Data Source=" &Parameters!ServerName.Value& ";Initial Catalog=RevenueData"
C. ="Server=" & Parameters!ServerName.Value
D. ="Data Source=@ServerName;Initial Catalog=RevenueData"
E. ="Server=@ServerName;Initial Catalog=RevenueData"
Q6. - (Topic 4)
You need to use Reporting Services Configuration Manager to configure SSRS to complete the installation on SSRS02.
What should you do? (Each correct answer presents a partial solution. Choose all that apply.)
A. Change the encryption key.
B. Specify the execution account.
C. Join the scale-out deployment.
D. Set the Report Server Web Service URL.
E. Set the Report Manager URL.
F. Delete the encryption key.
Answer: A,B,E
Explanation: A: We need to restore a copy of the encryption key from SSRS01. This step is necessary for enabling reversible encryption on pre-existing connection strings and credentials that are already in the report server database.
B: Reporting Services provides a special account that is used for unattended report
processing and for sending connection requests across the network. The account is used
in the following ways:
/ Send connection requests over the network for reports that use database authentication,
or connect to external report data sources that do not require or use authentication.
/ Retrieve external image files that are used in report. If you want to use an image file and
the file cannot be accessed through Anonymous access, you can configure the unattended
report processing account and grant the account permission to access the file.
E: Example:
1.First of all open Internet Explorer and go to.Report Manager URL.which is something.like
below:
http://string-pc/Reports2012
2.
Click on your SSRS project. So now it will show you the list of reports which are
deployed on your report server.
3.
Now click on down arrow on the report which you want to subscribe and selectManage. Etc.
* From Scenario:
/ A new server named SSRS02 has been joined to the domain, SSRS02 will host a single-
server deployment of SSRS so that snapshots of critical reports are accessible during the
upgrade.
/ You have the following requirements for SSRS02:
Replicate the SSRS01 configuration.
Ensure that all current reports are available on SSRS02.
Minimize the performance impact on SSRS01.
/ In preparation for the upgrade, the SSRS-related components have been installed on the new SSRS02 server by using the Reporting Services file-only installation mode. The Reporting Services databases have been restored from SSRS01 and configured appropriately.
/ SSRS02 is the recovery server and is running the same version of SSRS as SSRS01. The report server configuration files, custom assemblies, and extensions on SSRS02 are manually synchronized with SSRS01.
...
Q7. - (Topic 9)
You have a data warehouse named DW1.
An OLAP cube named Cube1 uses DW1 as its data source. Cube1 uses the MOLAP
storage mode.
You create a SQL Server Integration Services (SSIS) package that updates DW1, and then processes Cube1. The package executes each day at 06:00.
You need to configure the storage settings of Cube1. The solution must ensure that aggregation processing is reduced as much as possible during cube querying.
What is the best storage setting you should you use for Cube1? More than one answer choice may achieve the goal. Select the BEST answer.
A. Medium-latency MOLAP
B. Scheduled MOLAP
C. Low-latency MOLAP
D. Automatic MOLAP
Answer: D
Q8. - (Topic 2)
You need to configure the partition storage settings to support the reporting requirements.
Which partition storage setting should you use?
A. DirectQuery
B. In-Memory
C. MOLAP
D. Low-latency MOLAP
E. Scheduled MOLAP
F. High-latency MOLAP
Answer: D
Q9. 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:
Q10. - (Topic 4)
You need to implement the Customer Sales and Manufacturing data models.
What should you do? (Each correct answer presents a partial solution. Choose all that apply.)
A. Use the Database Synchronization Wizard to upgrade the database to tabular mode.
B. Use SQL Server Integration Services (SSIS) to copy the database design to the SSAS instance, and specify tabular mode as the destination.
C. Use SQL Server Data Tools (SSDT) to redevelop and deploy the projects.
D. Use the current SSAS instance.
E. Install a new instance of SSAS in tabular mode.
Answer: C,E
Explanation:
C: Tabular models are authored in SQL Server Data Tools (SSDT) using new tabular model project templates. You can import data from multiple sources, and then enrich the model by adding relationships, calculated columns, measures, KPIs, and hierarchies. Models can then be deployed to an instance of Analysis Services where client reporting applications can connect to them. Deployed models can be managed in SQL Server Management Studio just like multidimensional models. They can also be partitioned for optimized processing and secured to the row-level by using role based security.
E: If you are installing Analysis Services to use the new tabular modeling features, you must install Analysis Services in a server mode that supports that type of model. The server mode is Tabular, and it is configured during installation. After you install the server in this mode, you can use it host solutions that you build in tabular model designer. A tabular mode server is required if you want tabular model data access over the network.
*
From scenario: / Deploy a data model to allow the ad-hoc analysis of data. The data model must be cached and source data from an OData feed. / All SSAS databases other than the Research database must be converted to tabular BI Semantic Models (BISMs) as part of the upgrade to SSAS 2012. The Research team must have access to the Research database for modeling throughout the upgrade. To facilitate this, you detach the Research database and attach it to SSAS01.
*
The Business Intelligence Semantic Model (BISM) is a single unified BI platform which has both multi-dimensional as well as tabular data modeling capabilities to offer best of both worlds and choice for the developer.
Reference: Install Analysis Services in Tabular Mode
Reference: Tabular Modeling (SSAS Tabular)
Q11. - (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. Install PowerPivot.
B. Create Service Principal Names (SPNs).
C. Configure account delegation.
D. Set up the Unattended Service Account.
Answer: A,B
Explanation: * From scenario:
/ Administrative staff from around the world will produce sales reports with Microsoft Excel
2010 based on the Sales cube.
/ 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.
B: To use Kerberos authentication with SQL Server requires both the following conditions to be true: The client and server computers must be part of the same Windows domain, or in trusted domains. A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.
Q12. - (Topic 6)
You need to ensure that you can monitor the usage data.
What should you do?
A. In SharePoint Central Administration, enable usage data collection.
B. In the PowerPivot Configuration Tool, repair PowerPivot for SharePoint.
C. In the PowerPivot Configuration Tool, upgrade PowerPivot for SharePoint.
D. In Reporting Services Configuration Manager, enable report execution logging.
Answer: A
Q13. - (Topic 5)
You need to implement the product dimension. What should you do?
A. In the data warehouse, create a product dimension from a view that joins the Product and ProductColor tables in the financial database and contains product name and product color attributes.
B. In the data warehouse, create a dimension table that contains product name and a dimension table that contains product color.
C. In the data warehouse, create a product dimension table that contains product name and product color.
D. In the cube, create a named query that joins the Product and ProductColor tables in the financial database.
Answer: C
Q14. - (Topic 5)
You need to implement security in the cube to limit the sites visible to each user.
What should you do?
A. Create an SSAS database role in the cube for each user and assign the sites each user can access to his or her database role.
B. Create an SSAS server role for each user and assign the sites each user can access to his or her server role.
C. Create an SSAS database role and define a Multidimensional Expressions (MDX) calculation to implement dynamic dimension security.
D. Create a view on the SalesTransactions table that uses the SecurityFilter and User table data to limit the sites for each user.
Answer: C
Q15. DRAG DROP - (Topic 9)
You are administering a SQL Server Analysis Services (SSAS) database on a server. The database hosts a financial cube based on a SQL Azure database.
You need to grant read 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:
Q16. HOTSPOT - (Topic 10)
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.
Answer: