Q1. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project. A model defines a measure named Profit 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 profit.
What should you do? (Each answer presents a complete solution. Choose all that apply.)
A. Use the Business Intelligence Wizard and then use the Define time intelligence enhancement.
B. Define the following calculation.Year Over Year Profit Growth:=CALCULATE([Profit], DATEADD('Date'[Date], 1, YEAR))
C. Define the following calculation.Year Over Year Profit Growth:=[Profit] -CALCULATE([Profit], SAMEPERIODLASTYEAR('Date'[Date]))
D. Define the following calculation.Year Over Year Profit Growth:=[Profit] -CALCULATE([Profit], PARALLELPERIOD('Date'[Date], -12, MONTH))
Answer: A,D
Explanation: A: SSAS Provides feature called "Time Intelligence Wizard". This feature will
provide neat GUI to achieve the same purpose which we were trying by MDX code [using
the PARALLELPERIOD function].
Example:
Lets explore the "Time Intelligence Wizard":
1) In BIDS, Click "Cube" in menu bar and select "Add business Intelligence"
2) Click "Time Intelligence Wizard" on next screen.
3) "Choose Target Hierarchy and Calculations".screen
http://4.bp.blogspot.com/_ntCExJSVw8o/TKm091Wyh_I/AAAAAAAAMV8/dzpwjOPJ_wI/s4 00/TIW_CTH.JPG Etc.
D: Variance analysis for SSAS OLAP cubes is not a simple matter of adding a calculated field to a pivot table. Planning along with the use of the ParallelPeriod MDX functions allows us to quickly create a variance infrastructure for a particular measure. Furthermore, by utilizing a date hierarchy in the Parallel Period function, we can easily traverse down the hierarchy for any attribute below the parallel period level noted in the function (i.e., parallel period based on Year can show either one year back per year, quarter, or month). Although, other methods exist, the parallel period method can be easily followed and applied to various measures.
Q2. - (Topic 9)
You are designing a subscription strategy for a SQL Server Reporting Services (SSRS) report.
You have an application that populates a table with user-specific subscription schedules and report formats.
You need to ensure that users can receive reports by email according to their preferences. Email messages will be sent via an internal mail server.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create a standard SSRS subscription for each record in the table.
B. Create a data-driven SSRS subscription for each record in the schedule table.
C. Create a standard SSRS subscription for each subscription schedule.
D. Create one data-driven SSRS subscription. Schedule the subscription to frequently retrieve user preferences.
Answer: D
Explanation:
Ref: http://technet.microsoft.com/en-us/library/ms187066(v=sql.105).aspx
Q3. - (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
Q4. DRAG DROP - (Topic 1)
You need to complete the design of the data warehouse.
Which design should you use? (To answer, drag the appropriate tables and relationships to the correct location in the answer area. Use only the tables and relationships that apply.)
Answer:
Q5. - (Topic 3)
You need to fix the PowerPivot data refresh problem by using the least amount of administrative effort.
What should you do?
A. Use the PowerPivot Configuration Tool and select the Upgrade Features, Services, Applications and Solutions option.
B. Use the PowerPivot Configuration Tool and select the Configure or Repair PowerPivot for SharePoint option.
C. Reinstall SSAS in PowerPivot for SharePoint mode by using the SQL Server 2012 installation media.
D. In SharePoint Central Administration, create a target application and configure the
PowerPivot service application settings to use the target application.
Answer: B
Q6. DRAG DROP - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) multidimensional project. The project file includes two cubes named Finance and Operations. The project also includes a dimension named Date. The Date dimension includes two hierarchies named Fiscal and Calendar. The Date dimension has been added to both cubes.
You need to disable the Fiscal hierarchy in the Operations cube without impacting other database objects.
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:
Q7. - (Topic 10)
You are modifying a SQL Server Analysis Service (SSAS) cube. The cube consist of a single measure group that contains the following measures:
. Total Quantity On Hand
. Average Quantity On Hand
The measure group has a single partition that uses the MOLAP storage mode.
You need to modify the cube design to ensure that the Total Quantity On Hand measure is updated in real-time and that Average Quantity On Hand measure is updated hourly.
What should you do?
A. Create a new measure group for the Total Quantity On Hand measure. Configure the storage mode for the new measure group's partition to ROLAP.
B. Create an XMLA script that will process the cube and then use SQL Server Agent to execute the script continuously.
C. Change the storage mode of the partition to use proactive caching with minimum latency.
D. Create a new measure group for the Average Quantity On Hand measure. Configure the storage mode for the new measure group's partition to ROLAP.
Answer: A
Q8. - (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.
...
Q9. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular database.
To maximize performance, the queries must be resolved by using cache unless otherwise
specified in the connection string.
You need to configure the appropriate query mode.
Which query mode should you select?
A. In-Memory with DirectQuery
B. DirectQuery with In-Memory
C. DirectQuery
D. In-Memory
Answer: A
Q10. - (Topic 9)
You are designing a SQL Server 2012 Integration Services (SSIS) deployment strategy. You currently have many SQL Server 2008 SSIS packages that require upgrading.
The production environment includes SSIS 2012 and SSIS 2008. The environment includes existing command shell scripts that invoke the dtutil command-line utility.
You need to design a deployment strategy that supports existing deployment strategies and requires the minimum amount of effort.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use a project deployment model. Change the command shell scripts to reference the SQL Server 2012 path to dtutil.
B. Use a package deployment model. Use the Integration Services Deployment Wizard.
C. Use a package deployment model. Change the command shell scripts to reference the SQL Server 2012 path to dtutil.
D. Use a project deployment model. Use the Integration Services Deployment Wizard.
Answer: C