Q1. - (Topic 10)
You are developing a new SQL Server Reporting Services (SSRS) report in SQL Server
Data Tools (SSDT). This report has a table named Table1 and a textbox named Textbox1.
Table1 is initially visible but the user must be able to choose when to hide it.
You need to develop the report to meet the requirement.
What should you do? (More than one answer choice may achieve the goal. Select the
BEST answer.)
A. Add a parameter to the report so users can choose the display state of Table1.
B. For the properties of Table1, configure the Display can be toggled by this report item option to use Textbox1.
C. Configure Textbox1 to drill through to rerun the report to toggle the display of Table1.
D. For the properties of Table1, configure the Display can be toggled by this report item option to use Table1.
Answer: B
Q2. - (Topic 10)
You are working with a SQL Server Reporting Services (SSRS) instance in native mode. An item role named Reports Writer is present on the server.
The Reports Writer role cannot view and modify report caching parameters.
You need to ensure that the Reports Writer role can view and modify report caching parameters.
What should you do?
A. Add the Manage individual subscriptions task to the Reports Writer role.
B. Add the Manage report history task to the Reports Writer role.
C. Add the View data sources task to the Reports Writer role.
D. Add the Manage all subscriptions task to the Reports Writer role.
Answer: B
Q3. - (Topic 10)
You are designing a SQL Server Reporting Services (SSRS) report that sources data from a SQL Azure database.
The report must display the value and status of a Key Performance Indicator (KPI).
Which report item should you use? (Each answer presents a complete solution. Choose all that apply.)
A. Sparkline
B. Gauge
C. Indicator
D. Data Bar
E. Image
Answer: B,C
Explanation: B: The Gauge data region is a one-dimensional data region that displays a
single value in your dataset. An individual gauge is always positioned inside a gauge panel,
where you can add child or adjacent gauges..
You can use gauges to perform many tasks in a report:
Display key performance indicators (KPIs) in a single radial or linear gauge.
Place a gauge inside a table or matrix to illustrate values inside each cell.
Use multiple gauges in a single gauge panel to compare data between fields.
C: Indicators are minimal gauges that convey the state of a single data value at a glance.
The icons that represent indicators and their states are simple and visually effective even
when used in small sizes.
You can use state indicators in your reports to show the following:
Trends by using trending-up, flat (no change), or trending-down arrows.
State by using commonly recognized symbols such as checkmarks and exclamation
marks.
Conditions by using commonly recognized shapes such traffic lights and signs.
Ratings by using common recognized shapes and symbols that show progress such
number of quadrants in a square and stars.
Incorrect: Not A, not D: Sparklines and data bars are small, simple charts that convey a lot of information in a little space, often inline with text. Sparklines and data bars are often used in tables and matrices. Their impact comes from viewing many of them together and being able to quickly compare them one above the other, rather than viewing them singly. They make it easy to see the outliers, the rows that are not performing like the others. Although they are small, each sparkline often represents multiple data points, often over time. Data bars can represent multiple data points, but typically illustrate only one.
Q4. - (Topic 9)
The IT department is currently upgrading all SQL Server Integration Services (SSIS) packages to SSIS 2012.
The final version of a complex SSIS 2005 package has been overwritten. Four prior versions of the package exist. Each of the prior versions contains a different part of the final solution. You have backups of all associated project files.
You need to create an updated package as quickly and accurately as possible.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use an XML editing tool to merge the compatible changes of the package versions by comparing the package refID fields. Upgrade the merged version.
B. Upgrade the four package versions. Use an XML editing tool to merge the compatible changes of the package versions by comparing the package refID fields.
C. Use the DataSet.Merge method to merge the compatible changes of the package versions by using the Lineage IDs. Upgrade the merged version.
D. Upgrade the four package versions. Use SSIS Designer to visually compare the package versions, determine the compatible changes, and copy the compatible tasks into a single package.
Answer: B
166. DRAG DROP - (Topic 9)
You are designing a business intelligence (BI) solution for a hotel booking system. Each hotel booking may have more than one guest.
The BI solution contains a fact table named Booking, a factless fact table named Booking_Guest and a dimension named Guest.
Booking contains an entry for each booking. Booking_Guest contains an entry for each guest in each booking. Guest contains an entry for each guest.
You need to define the many-to-many relationship in SQL Server Analysis Services (SSAS) for the hotel booking system.
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.
Q5. - (Topic 10)
You are developing a SQL Server Analysis Services (SSAS) multidimensional project.
...
A fact table is related to a dimension table named DimScenario by a column named ScenarioKey.
The dimension table contains three rows for the following scenarios:
Actual
Budget Q1
Budget Q3
You need to create a dimension to allow users to view and compare data by scenario.
What should you do?
A. Use role playing dimensions.
B. Use the Business Intelligence Wizard to define dimension intelligence.
C. Add a measure that uses the Count aggregate function to an existing measure group.
D. Add a measure that uses the DistinctCount aggregate function to an existing measure group.
E. Add a measure that uses the LastNonEmpty aggregate function. Use a regular relationship between the time dimension and the measure group.
F. Add a measure group that has one measure that uses the DistinctCount aggregate function.
G. Add a calculated measure based on an expression that counts members filtered by the Exists and NonEmpty functions.
H. Add a hidden measure that uses the Sum aggregate function. Add a calculated measure aggregating the measure along the time dimension.
I. Create several dimensions. Add each dimension to the cube.
J. Create a dimension. Then add a cube dimension and link it several times to the measure group.
K. Create a dimension. Create regular relationships between the cube dimension and the measure group. Configure the relationships to use different dimension attributes.
L. Create a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a regular relationship between the dimension and measure group.
M. Create a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a many-to-many relationship to link the dimension to the measure group.
N. Create a dimension with one attribute hierarchy. Set the ValueColumn property, set the IsAggregatable property to False, and then set the DefaultMember property. Configure the cube dimension so that it does not have a relationship with the measure group. Add a calculated measure that uses the MemberValue attribute property.
O. Create a new named calculation in the data source view to calculate a rolling sum. Add a measure that uses the Max aggregate function based on the named calculation.
Answer: L
Q6. - (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
Q7. - (Topic 1)
You need to configure the SSRS data source.
What should you do?
A. Store the credentials.
B. In the data source configuration window, select the Prompt for credentials option.
C. Create a .NET form to enable users to enter their credentials.
D. Configure Kerberos authentication.
Answer: A
Q8. - (Topic 9)
You need to design a data warehouse schema to support a multidimensional cube. The fact table will contain multiple columns representing order dates, shipping dates, and billing dates. The cube will contain a measure group based on the fact table.
What is the best design to achieve the goal? More than one answer choice may achieve the goal. Select the BEST answer.
A. In the database, create a time table for each date column in the fact table.
From the data source view, join each date column in the fact table to the corresponding
time table.
Create dimensions for each time table.
B. In the database, create a time table containing a column that corresponds to each date
column in the fact table.
From the data source view, join each date column in the fact table to the corresponding
column in the time table.
Create a dimension for the time table.
C. In the database, create a time table that uses an integer primary key and a datetime
column.
Create a dimension based on the time table.
From the Cube Designer, use the Dimension Usage tab to define a relationship between
the measure group date columns and the datetime column of the time dimension.
D. In the database, create a time table that uses an integer primary key and a datetime
column.
Create a dimension based on the time table.
From the Cube Designer, use the Dimension Usage tab to define a relationship between
the measure group date columns and the key column of the time dimension.
Answer: B
Q9. HOTSPOT - (Topic 9)
A company maintains separate environments for development, test, and production. The company uses the project deployment model for SQL Server Integration Services (SSIS) deployments.
You create an SSIS project to perform a daily refresh of the data warehouse and data models. The project has five packages.
You need to configure the project to ensure that the development, test, and production teams can run each package without manually adding server-specific information.
Which program should you use?
To answer, select the appropriate program in the answer area.
Answer:
Q10. - (Topic 6)
You need to select the appropriate model type for the Finance database. Which model type should you select?
A. Star schema
B. Multidimensional
C. Relational
D. Tabular with PowerPivot
Answer: B