Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. - (Topic 3)
You need to configure package execution logging to meet the requirements.
What should you do?
A. Configure logging in each ETL package to log the OnError, OnInformation, and Diagnostic events.
B. Set the SSIS catalog's Server-wide Default Logging Level property to Performance.
C. Set the SSIS catalog's Server-wide Default Logging Level property to Basic.
D. Set the SSIS catalog's Server-wide Default Logging Level property to Verbose.
E. Configure logging in each ETL package to log the OnError, OnPreExecute, and OnPostExecute events.
Answer: B
Q2. - (Topic 4)
You need to use SQL Server Management Studio (SSMS) to make the SSAS databases available for application testing.
What should you do?
A. Restore the SSAS databases from the latest backup to SSAS01.
B. Script the databases as a Create script to a new window and then execute the script on SSAS01.
C. Detach the SSAS databases from the SSASCluster, and then attach them to SSAS01.
D. Use the Import/Export Wizard to copy the databases from the production server to the development server.
Answer: A
Q3. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
In the data warehouse, a table named Employee Security defines a relationship between a salesperson's name, logon ID, and assigned sales territory.
You need to ensure that each salesperson has access to data from only the sales territory assigned to that salesperson. You also need to minimize the development effort.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create a separate tabular project for each sales territory. Grant each salesperson access to the corresponding tabular model of the assigned sales territory.
B. Create a new Active Directory Domain Services (AD DS) security group and add each salesperson as a member. Then create a new role with Read permission. Add the group as a member to the new role.
C. Create a new role with Read permission and then add each salesperson's logon as a member to the role.
D. Add the Employee Security table to the model, define the relationships, and then implement dynamic security by using row filters. Grant each salesperson access to the model.
Answer: D
Q4. - (Topic 9)
You are creating a Multidimensional Expressions (MDX) calculation for Projected Revenue in a cube.
For Customer A, Projected Revenue is defined as 150 percent of the Total Sales for the customer. For all other customers, Projected Revenue is defined as 110 percent of the Total Sales for the customer.
You need to calculate the Projected Revenue as efficiently as possible.
Which calculation should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q5. DRAG DROP - (Topic 9)
You are the database administrator of a SQL Server 2012 data warehouse implemented as a single database on a production server. The database is constantly updated by using SQL Server Integration Services (SSIS) packages and SQL Server Analysis Services (SSAS) cube writeback operations.
The database uses the full recovery model. A backup strategy has been implemented to minimize data loss in the event of hardware failure.
SQL Server Agent jobs have been configured to implement the following backup operations:
A full database backup every day at 12:00 A.M.
Differential database backups every day at 6:00 A.M., 12:00 P.M., and 6:00 P.M.
Transaction log backups every hour on the hour.
...
At 2:38 P.M. a SSIS package corrupts the data in a fact table. The corruption cannot be undone. You are notified at 3:15 P.M. You immediately take the database offline to prevent further data access and modification.
You need to restore the data warehouse and minimize downtime and data loss.
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:
Q6. DRAG DROP - (Topic 10)
You are developing a SQL Server Reporting Services (SSRS) report to display a list of
employees. The report will be embedded into a Microsoft SharePoint Server Web Part Page of the company intranet site.
The report consists of a single table. The design of the table is shown in the following diagram.
You need to configure the EmailAddress detail text box to create a new email message. The email message must be addressed to the email address that was clicked by the user.
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:
312. - (Topic 10)
You are designing a SQL Server Reporting Services (SSRS) report to display product names and their year-to-date (YTD) sales quantity YTD sales quantity values are classified in three bands: High Sales, Medium Sales, and Low Sales.
You add a table to the report. Then you define two columns based on the fields named ProductName and YTDSalesQuantity.
You need to set the color of the product text to red, yellow, or blue, depending on the value of the YTD sales quantity values.
What should you do?
A. Use an expression for the TextDecoration property of the text box.
B. Use an expression for the Style property of the text box.
C. Add an indicator to the table.
D. Use an expression for the Font property of the text box.
E. Use an expression for the Color property of the text box.
Q7. 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:
Q8. - (Topic 4)
You need to implement the security requirement for the sales representatives.
Which MDX expression should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Topic 5, Data Architect
General Background
You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition.
You have been tasked with designing a data warehouse that uses the company's financial
database as the data source. From the data warehouse, you will develop a cube to simplify
the creation of accurate financial reports and related data analysis.
Background You will utilize the following three servers: . ServerA runs SQL Server Database Engine. ServerA is a production server and also hosts the financial database.
. ServerB runs SQL Server Database Engine, SQL Server Analysis Services (SSAS) in multidimensional mode, SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS).
. ServerC runs SSAS in multidimensional mode. . The financial database is used by a third-party application and the table structures cannot be modified.
The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.)
The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is partitioned by month. It contains only the last five years of financial data. The CouponUsed, OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is less than 10 MB and has only one partition.
The SecurityFilter table specifies the sites to which each user has access.
Business Requirements The extract, transform, load (ETL) process that updates the data warehouse must run daily between 8:00 P.M. and 5:00 A.M. so that it doesn't impact the performance of ServerA during business hours. The cube data must be available by 8:00 A.M.
The cube must meet the following business requirements:
..
Ensure that reports display the most current information available. Allow fast access to support ad-hoc reports and data analysis.
Business Analysts will access the data warehouse tables directly, and will access the cube by using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint Services. These tools will access only the cube and not the data warehouse.
Technical Requirements SSIS solutions must be deployed by using the project deployment model. You must develop the data warehouse and store the cube on ServerB. When the number of concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to ServerC and meet following requirements:
..
Maintain copies of the cube on ServerB and ServerC. Ensure that the cube is always available on both servers.
Guaranteed success with TestInsides practice guides
.
Minimize query response time.
The cube must meet the following technical requirements:
.. ..
The cube must be processed by using an SSIS package.
The cube must contain the prior day's data up to 8:00 P.M. but does not need to
contain same-day data.
The cube must include aggregation designs when it is initially deployed.
A product dimension must be added to the cube. It will contain a hierarchy
comprised of product name and product color.
Because of the large size of the SalesTransactions table, the cube must store only aggregations—the data warehouse must store the detailed data. Both the data warehouse and the cube must minimize disk space usage.
As the cube size increases, you must plan to scale out to additional servers to minimize processing time.
The data warehouse must use a star schema design. The table design must be as denormalized as possible. The history of changes to the Customer table must be tracked in the data warehouse. The cube must use the data warehouse as its only data source.
Security settings on the data warehouse and the cube must ensure that queries against the SalesTransactions table return only records from the sites to which the current user has access.
The ETL process must consist of multiple SSIS packages developed in a single project by using the least amount of effort. The SSIS packages must use a database connection string that is set at execution time to connect to the financial database. All data in the data warehouse must be loaded by the SSIS packages.
You must create a Package Activity report that meets the following requirements:
..
Track SSIS package execution data (including package name, status, start time,
end time, duration, and rows processed).
Use the least amount of development effort.
Q9. - (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:=RELATED(Geography[City])
B. =RELATEDTABLE(Geography)
C. =RELATED(Geography[City])
D. City:=Geography[City]
E. City:=RELATEDTABLE(Geography)
F. =Geography[City]
Answer: C
Q10. 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:
Q11. - (Topic 10)
...
A large manufacturing company has manufacturing plants in many states. Each state has a dedicated SQL Server instance that stores its manufacturing data. Each SQL Server instance is configured identically and all instances have identical database structures.
You are developing a daily report that summarizes information on manufacturing activity.
The report has the following requirements:
It must have a state name as one of the report parameters.
It must provide a daily summary of manufacturing activity of a selected state.
It must require minimal development and maintenance effort.
You need to develop the report to meet the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use a shared data source that defines an expression-based connection string based on the state parameter.
B. Build a centralized data repository, schedule a regular Extract, Transform, and Load (ETL) process on all manufacturing data, and then use the repository to generate the report.
C. Use a report-specific data source that defines an expression-based connection string based on the state parameter.
D. Build one report for each state and instruct users to execute reports as needed.
Answer: C
Q12. - (Topic 9)
You are creating a product dimension table and a SQL Server Integration Services (SSIS) package that will load the table.
You need to keep a history in the table of changes to the names of products.
Which three actions should you perform? Each correct answer presents part of the solution.
A. Add a Slowly Changing Dimension transformation to the package.
B. Enable Change Data Capture for the table.
C. Create an inferred member column in the table.
D. From the Slowly Changing Dimension Wizard, set the name column to Type 3.
E. Create an end date column in the table.
F. Create a start date column in the table.
Answer: A,E,F
Topic 10, Mix Questions Set B
Q13. - (Topic 4)
You need to perform the pre-upgrade database backup operation by using SQL Server Management Studio (SSMS).
How should you configure the backup options?
A. Select the Apply compression check box. Select the Encrypt backup file check box and supply a password.
B. Clear the Apply compression check box. Select the Encrypt backup file check box and supply a password.
C. Clear the Apply compression check box. Clear the Encrypt backup file check box.
D. Select the Apply compression check box. Clear the Encrypt backup file check box.
Answer: D
Q14. HOTSPOT - (Topic 9)
You are developing a SQL Server Analysis Services (SSAS) cube. A dimension named Category is based on the DimCategory table.
A subset of the data source view is shown in the following graphic.
You need to relate the Category dimension to the Sales measure group.
Which relationship type should you choose?
To answer, select the appropriate option from the drop-down list in the dialog box.
Answer:
Q15. - (Topic 10)
You install SQL Server Reporting Services (SSRS). You need to restore a copy of the symmetric key. Which command should you run?
A. rskeymgmt –d
B. rskeymgmt –a –f %temp%\rs.key –p Password1
C. rskeymgmt –e –f %temp%\rs. key –p Password1
D. rskeymgmt –i
Answer: B
Q16. DRAG DROP - (Topic 9)
You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a Windows Azure SQL Database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table.
You have the following requirements:
. The fact table must populate a column named TotalCost that computes the sum of the price and tax columns. . Before the sum is calculated, any records that have a price of zero must be discarded.
You need to create the SSIS package in SQL Server Data Tools.
In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.)
Answer: